Chapter 2: Database Design Process

Behind any successful database application there are many phases which have to be developed by the
database designer or database administrator. The term database application refers to a particular
database & the associated programs that implement the database queries & updates. Main two
activities involved in design process are:
 Database design: To design conceptual schema for a database application
 Application design: It focuses the application programs and interfaces to access databases.
The phases of database design process are given in below diagram in detail.
 Entity-Relationship Model
The entity relationship model is based on the perception of a real world that consists of basic objects,
called entities & relationship among entities. The E-R model is high level conceptual data model
developed by Chen in 1976 to facilitate database design. The conceptual data models describe the
structure of the database. This model is established on entities attributes of each entity, relationship
among entities. To understand E-R model, lets understand the components of the model.

Entity, Entity types, Entity set:

Entity is the real-world object which is easily distinguishable from other real-world objects. An entity
is viewed as an atomic real-world item. Consider the example, Employee table is an entity because it
differentiates one employee from other employee by some set of properties. The properties are-
1. EID- employee id
2. Ename- Employee name
3. Eadd- Employee address
4. Sal- Employee’s salary
5. Phnno- Employee’s Phnno
Entities are classified into different entity sets. An entity set is a set of entity instances or entity
occurrences of the same type. For example, all employees of a company may constitute an entity set
employee.

Attributes:
Each entity is distinguishable in real world by its unique features or characteristics. These
characteristics or features are represented by attributes. For example, an employee is represented by its
empid, address, salary, age, empname etc. So empid, empname, address, salary, age are the attributes
of an employee entity.

Types of attributes:
1) Simple attribute: The attribute that can not be divided into substrates i.e attributes that have atomic
values are called as simple attributes.
For example, Name, age, id, etc.
2) Composite Attributes: The attributes that can be divided in sub-attributes are called as composite
attributes.
For example, address= {street, house_no, street_no, area, pin}, name= {first_name, middle_name,
last_name}
3) Single valued: Attributes that have only one value for each entity e.g. Name, age for employee.
4) Multivalued attribute: Attributes that have a set of values for each entity e.g. contact nos of a
person: office_no, home_no, mobile_no.
5) Derived attributes: Attributes contain values that are calculated from other attributes e.g. age can
be derived from attribute date_of_birth and current_date.

Keys in E-R Diagram:

Key is an attribute or a group of attributes to distinguish each entity from other entities.

Types of Keys:
(i) Super key: A superkey is a set of one or more attributes that taken collectively to identify uniquely
an entity in the entity set. For example, for the entity Employee ={Eid, Name, Address, Age, Phnno,
Salary}.This entity has many possible superkeys such as {Eid}, {Phno, Name} , {Eid, Name}.
(ii) Candidate key: Minimal super key or a smallest possible superkey is candidate key. From the
above {Eid, Name} is one of the superkeys where Eid itself is a superkey. So here minimal super is
Eid, so it’s a candidate key.
(iii) Primary key: The candidate key actually used for identifying entities & accessing records. For
example, for the entity employee={Eid, Name, Address, Age, Salary, Phnno}. In this entity Eid is a
primary key. The selection criteria of primary key.
 Unique value for each entity
 Not null value
 Contains the values that are present in primary
(iv) Foreign key: A Foreign Key(FK) is a field or group of fields in a database record that points to a
key field or group of fields forming a key of another database record in some other table. Usually
foreign key in one table refers to the primary key of another table.
Employee
Eid – primary key

Dept
Eid-Foreign key
Deptid- Primary key

In the above example Eid is the primary key of employee table, but Eid in dept table is foreign key.
That is, Eid of the dept table refers Eid (primary key) of the employee table. The employees who
belong to some departments, are present in employee table.

Relationship, Relationship set & Cardinality ratio:

Relationship is associated between two entities or among more than two entities. For example, works
on is relationship between an employee and a project.
Relationship set is a set of relationships of same type. If e1, e2, e3 ………en are the entity sets then
relationship set R consists of {i1, i2, i3……..in | i1Єe1, i2Єe2, inЄen} in is an entity instance in entity e n.
Each tuple { e1, e2, ………en} is a relationship instance.

Relationship:

Employee Works Project

on

Degree of relationship is the number of entities associated with relationship. In the above example
degree of relationship is 2.
There are different types of relationship –
1) Unary:
Relationship exists when an association is maintained within single entity. In the diagram, manages
relationship is unary type of relationship.
Employee

Manages
Hence, in a company there are different levels of supervision. Executive officer manages department
managers. Department manager handles department employees. But as they all are employees of same
company. So, employee manages employees.

2) Binary:
Relationship exists when two are associated. In below diagram two entities are involved like Teacher
teaches Students. So, teaches relationship between teacher and student is binary relation.

Teacher Teaches Student

Teaches

3) Ternary: This relationship exists when three entities are associated. As already seen teaches relation
has been used as binary relation but also teaches relation can be used as ternary relation. Let’s explain
with below example.

Teacher Teaches Student

Subject

In any institution teacher must teaches some subjects/ courses. So here three kinds of entities are
involved in teaches relation. So above example of ternary relationship.
4) Quaternary:
This relationship involves association of four entities. In one diagram, it shows studies as quaternary
relation as four entities like student, teacher, subject and Study_materials are involved.

Study_materials

Teacher Learns Student

from

subject
Mapping cardinalities:
Mapping cardinality describes the maximum number of entities that a given entity can be associated
with via a relationship. The possible cardinality for binary relationship types are:
i) One-to-One(1-1/1:1)
ii) One-to-Many(1-N/1:N)
iii) Many-to-one(N-1/N:1)
iv) Many-to-Many(M-N/M:N)

i) One-to-One (1:1)
An entity in A is associated with at most one entity in B, and an entity in B is associated with at most
one entity in A.
Example:
1 1
employee manages Dept.

In above relation, a company can have many departments but for each department there will be only
one manager who is also an employee of that company. At a time one employee can manage only one
department. Below diagram is the set representation on One-to-One cardinality mapping where an
entity in employee is associated with at most one entity in dept., and an entity in dept. is associated
with at most one entity in employee.

e1 d1
e2 d2
e3 d3
. .
. .
. .
en dn

Employee Dept.

ii) One-to-Many(1:N):
An entity in A is associated with any number of entities in B. An entity in B can be associated with at
most one entity in A.
Example:

1 N
University College
has

Has is the relationship between University and College in One-to-Many mapping. Under one
university there can be many colleges but one college is affiliated by only one university. It is shown
in below set diagram.
 u1 c1
u2 c2
u3 c3
. .
. .
. .
un cn

University College

iii) Many-to-One:
An entity in A is associated with at most one entity in B. An entity in B can be associated with any
number of entities in A.
Example:

N 1
Works
Employee on
Dept.

Each department has many employees or many employees can work on same dept. One employee can
not work on many different departments. This can be presented with below set representation.

e1 d1
e2 d2
e3 d3
. .
. .
. .
en dn

Employee Dept.

(iv) Many-to-Many: An entity in A is associated with any number of entities in B, and an entity in B is
associated with any number of entities in A.

join Courses
Student
 u1 c1
u2 c2
u3 c3
. .
. .
. .
un cn

student Courses

Other notations used in ERD model are as below-

Strong and Weak entity sets:

An entity set that does not have a primary key is referred to as a weak entity set. The existence of a
weak entity set depends on the existence of strong entity set. Each weak set must be a part of one-
to-many relationship set.
A member of a strong entity set is called dominant entity and member of weak entity set is
called as subordinate entity. A weak entity set does not have a primary key but we need a
means of distinguishing among all those entries in the entity set that depend on one
particular strong entity set. The discriminator of a weak entity set is a set of attributes that
allows this distinction be made. For example, payment_number acts as discriminator for
payment entity set. It is also called as the Partial key of the entity set.
The primary key of a weak entity set is formed by the primary key of the strong entity set on
which the weak entity set is existence dependent plus the weak entity sets discriminator. In
the above example {loan_number, payment_number} acts as primary key for payment entity
set.
The relationship between weak entity and strong entity set is called as Identifying
Relationship. In example, loan-payment is the identifying relationship for payment entity. A
weak entity set is represented by doubly outlined box .and corresponding identifying relation
by a doubly outlined diamond as shown in figure. Here double lines indicate total
participation of weak entity in strong entity set it means that every payment must be related
via loan-payment to some account. The arrow from loan-payment to loan indicates that each
payment is for a single loan.

The discriminator of a weak entity set is underlined with dashed lines rather than solid line.
Let us consider another scenario, where we want to store the information of employees and
their dependents. Every employee may have zero to n number of dependents. Every
dependent has an id number and name
Diagrammatical notations of ERD

Important tips to draw an effective ERD:

 1. Identify all the relevant entities in a given system and determine the relationships among these
entities.
2. An entity should appear only once in a particular diagram.
3. Provide a precise and appropriate name for each entity, attribute, and relationship in the
diagram. Terms that are simple and familiar always beats vague, technical-sounding words. In
naming entities, remember to use singular nouns. However, adjectives may be used to
distinguish entities belonging to the same class (part-time employee and full time employee,
for example). Meanwhile attribute names must be meaningful, unique, system-independent,
and easily understandable.
4. Remove vague, redundant or unnecessary relationships between entities.
5. Never connect a relationship to another relationship.
6. Make effective use of colours. You can use colours to classify similar entities or to highlight
key areas in your diagrams.

A SIMPLE EXAMPLE

A company has several departments. Each department has a supervisor and at least one employee.
Employees must be assigned to at least one, but possibly more departments. At least one employee is
assigned to a project, but an employee may be on vacation and not assigned to any projects. The
important data fields are the names of the departments, projects, supervisors and employees, as well as
the supervisor and employee number and a unique project number.

1. Identify Entities

The entities in this system are Department, Employee, Supervisor and Project. One is tempted to make
Company an entity, but it is a false entity because it has only one instance in this problem. True
entities must have more than one instance.

2. Find Relationships

We construct the following Entity Relationship Matrix:

Department Employee Supervisor Project

Department is assigned run by
Employee belongs to works on
Supervisor runs
Project uses

3. Draw Rough ERD

We connect the entities whenever a relationship is shown in the entity Relationship Matrix.
4. Fill in Cardinality

From the description of the problem we see that:

 Each department has exactly one supervisor.

 A supervisor is in charge of one and only one department.
 Each department is assigned at least one employee.
 Each employee works for at least one department.
 Each project has at least one employee working on it.
 An employee is assigned to 0 or more projects.

5. Define Primary Keys

The primary keys are Department Name, Supervisor Number, Employee Number, Project Number.

6. Draw Key-Based ERD

There are two many-to-many relationships in the rough ERD above, between Department and
Employee and between Employee and Project. Thus we need the associative entities Department-
Employee and Employee-Project. The primary key for Department-Employee is the concatenated key
Department Name and Employee Number. The primary key for Employee-Project is the concatenated
key Employee Number and Project Number.

7. Identify Attributes

The only attributes indicated are the names of the departments, projects, supervisors and employees, as
well as the supervisor and employee NUMBER and a unique project number.

8. Map Attributes

Attribute Entity Attribute Entity

Department Department Supervisor Supervisor
Name Number
Employee Employee Supervisor Supervisor
Number Name
Employee Employee Project Name Project
Name
Project Project
Number

9. Draw Fully Attributed ERD

10. Check Results

The final ERD appears to model the data in this system well.

Enhanced E-R Diagram features:

In this section we will discuss some enhanced features of E-R Diagram. The enhanced features are-
1) Generalization 2) Specialization 3)Union types.
Before discussing the above features, we need to know about subclass and superclass. Superclass
includes distinct subclasses that require to be represented in data model. A subclass is an entity type
that is the part of a subclass which has attributes that are distinct from those in other subgroupings.
Example:
 Vehicle Superclass

Motorbike Car Truck Subclass

In the above diagram it clearly shows that Vehicle can be categorize mainly three categories.
Motorbike has its own unique characteristics and vehicle properties.
A superclass/subclass relationship is represented by ISA (is a) relationship in ER diagram.

person

ISA

Student Employee Customer

Merge

ISA
Under_graduate Post-graduate

Registrar Lecture IT-officer

Advantages of Superclass/Subclass:
a) It avoids redundancy means there is no need to describe similar concepts more than once.
b) This relationship add more semantic content & data to the design.

1)Specialization:
This is top-down approach means process of forming one or more subclasses from superclass. From
the above example, person class is divided into subclasses – student, employee customer and further.
2) Generalization:
This is bottom-up approach means process of forming a superclass. Generalization describes the
similar or general properties among subclasses. Generalization is opposite of specialization.
Generalization/specialization is represented by ISA in ERD. From the above example, considering
similar properties from employee, student and customer, person super class can be made.
3) Union types(categorization):
In some cases need to model a single superclass/subclass relationship with more than one superclass.
Superclasses represent different entity types. Such a subclass is called a union types.
 cname
Bank name Baddress caddress
Address
Name

Person Bank Company

Driving liscence
ISA

Owner

Purchase details
Owns

Registered vehicle Liscence

ISA
id
style

Car Truck

model

id Model color type

Specialization / Generalization constraints:

Constraints in specialization and generalization allow us to capture some of the important business
rules that apply to the relationships. The constraints that are applicable to specialization and
generalization disjoint constraints & participation constraint.

Steps for Converting an E-R Diagram into a Relational Database Schema

These steps are adapted from the sections on Relational Database Design Using ER-to-Relational
Mapping .
STEP 1: For each non-weak entity, create a relation (or table) that includes all of the simple attributes
of that entity. Do not include multivalued attributes or derived attributes at this time. If you have a
composite attribute, include only the component attributes. (For example, if you had a composite
attribute ADDRESS made up of the component attributes STREET, CITY, STATE, and ZIP, you
would include the 4 components and not the composite as fields.) Choose one of the candidate keys to
be the primary key of the table. If the candidate key you choose to be the primary key is a composite
attribute, then all of its component attributes together will become the primary key.

EXAMPLE: From the Company E-R diagram of the text, you would get the following table
definitions by following step 1:

EMPLOYEE

Fname Minit Lname SSN Bdate Address Sex Salary

DEPARTMENT

Name Number

PROJECT

Name Number Location

STEP 2: For each weak entity, create a relation that includes all simple attributes (or simple
components of composite attributes) of the weak entity. In addition, include as a foreign key attribute
the primary key of the owning entity. The primary key of this relation will be the combination of the
primary key of the owning entity and the partial key of the weak entity.

EXAMPLE: The relation for the weak entity DEPENDENT would look like this after step 2:

DEPENDENT

FK
Name Sex Birthdate Relationship Emp_SSN

STEP 3: For each binary 1:1 relationship, identify the two entities that participate in that relationship.
Choose one of the entities -- preferably the one with total participation in the relationship -- and think
of it as E1. The other entity is E2. Take the primary key from E2 and include it as a foreign key in E1.
If the relationship has simple attributes, include those in the relation for E1.

EXAMPLE: Manages is a 1:1 relationship between EMPLOYEE and DEPARTMENT. We choose

DEPARTMENT as E1, because it participates totally in the relationship. So after step 3, the
EMPLOYEE and DEPARTMENT relations would look like this:

DEPARTMENT (E1)

FK
Name Number Mgr_SSN Mgr_Startdate

EMPLOYEE (E2) -- NOTE: The EMPLOYEE relation is unchanged from step 1

Fname Minit Lname SSN Bdate Address Sex Salary

STEP 4: For each non-weak binary 1:N relationship, identify the entity E1 that is at the N-side (the
"many" side) of the relationship. The other entity in the relationship is E2. Include as a foreign key in
E1 the primary key of E2. Include any simple attributes (or simple components of composite
attributes) of the relationship as attributes of E1.

EXAMPLE: We have three 1:N relationships: Works_for, Controls, and Supervision.

Works_for: EMPLOYEE is on the N-side of the relationship, so after doing step 4 for Works_for, it
will look like the following:

EMPLOYEE

FK
Fname Minit Lname SSN Bdate Address Sex Salary Dept_Num

Controls: PROJECT is on the N-side of the relationship, so after doing step 4 for Controls, it will look
like the following:
PROJECT

FK
Name Number Location Dept_Num

Supervision: EMPLOYEE is on the N-side of the relationship in the supervisee role, so after doing
step 4 for Supervision, it will look like the following:

EMPLOYEE

FK FK
Fname Minit Lname SSN Bdate Address Sex Salary Dept_Num Super_SSN

Note that in this case the Foreign Key Super_SSN has actually come from the SSN in the
EMPLOYEE relation, since the EMPLOYEE entity is acting in two different roles in the Supervision
relationship.

Step 5: For each binary M:N relationship, create a new relation to represent the relationship. Include
in this relation as foreign keys the primary keys of each of the entities that participates in the
relationship. The combination of these foreign keys will make up the primary key for the relation.
Also include any simple attributes (or simple components of composite attributes) of the relationship.

EXAMPLE: We have one M:N relationship, Works_On. After step 5, we will have a relation for
WORKS_ON that looks like this:

WORKS_ON

FK FK
Emp_SSN Proj_Num Hours

Step 6: For each multivalued attribute, create a new relation that includes that attribute, plus the
primary key of the entity to whom that attribute belongs as a foreign key. The primary key of this new
relation will be the combination of the foreign key and the attribute itself. If the multivalued attribute
is also composite, we include only its simple components.

EXAMPLE: We have only one multivalued attribute, the Locations attribute of DEPARTMENT. We
create a relation called DEPT_LOCATIONS that will look like this:

DEPT_LOCATIONS

FK
Location Dept_Num

Step 7: For each relationship with 3 or more participating entities, create a new relation to represent
the relationship. Include the primary keys of each of the participating entities in the new relation as
foreign keys. Also include any simple attributes (or simple components of composite attributes) of the
relationship. The primary key will usually be a combination of all of the foreign keys that represent the
entities that participate in the relationship. However, if any of the participating entities are on the 1-
side of the relationship, then the primary key of the relation should not include the foreign key from
that entity.

EXAMPLE: Remember back to our Supplier - Part - Project ternary relationship. After following the
above steps, the relations would look like the following:

SUPPLIER

SName <other fields>

PART

PartNo <other fields>

PROJECT

ProjName <other fields>

SUPPLY

FK FK FK
SName PartNo ProjName Quantity

Step 8: If you have a superclass/subclass structure, there are four options for how to translate it into
relations. Note that each of these options is mutually exclusive.

Option 8a: Can be used with any combination of disjoint vs. overlapping, total vs. partial. Create a
new relation for the superclass entity and include the attributes of the superclass entity. The primary
key will be the primary key of the superclass entity. Create a new relation for each of the subclass
entitites, including the attributes of the particular subclass and the primary key of the superclass entity
as a foreign key. The primary key of each subclass relation will be the foreign key from the superclass
relation.

Option 8b: Can be used only with disjoint subclasses with total participation. Create a new relation
for each subclass entity that includes all of the attributes of the particular subclass and all of the
attributes of the superclass. The primary key will be the primary key of the superclass entity.

Option 8c: Can only be used for disjoint subclasses. Not recommended if there are many
attributes defined at the subclass level. Create one new relation with all of the attributes from the
superclass entity and all of the attributes from each of the subclass entities. Also include a "type"
attribute that will indicate the subclass to which each instance belongs.

Option 8d: Can only be used for overlapping subclasses. Not recommended if there are many
attributes defined at the subclass level. Create one new relation with all of the attributes from the
superclass entity and all of the attributes from each of the subclass entities. For each subclass, there
must be a True/False (Boolean) attribute for each whose value will depend on whether or not a
particular instance belongs to that subclass.
If, at the end of these steps, you find you have made a table that has only one attribute, discard that
table but be sure that the attribute exists in one of your other tables. This is an issue primarily in
situations where you are converting super classes/subclasses to relations using Step 8.

Database Entities and Entity Relationship

Explain the difference between a weak and a strong entity set.
Answer: A strong entity set has a primary key. All tuples(rows) in the set are distinguishable by that key. A
weak entity set has no primary key unless attributes of the strong entity set on which it depends are included.
Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity.

We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then,
do we have weak entity sets?
Answer: We have weak entities for several reasons:
 We want to avoid the data duplication and consequent possible inconsistencies caused by duplicating the key
of the strong entity.
 Weak entities reflect the logical structure of an entity being dependent on another entity.
 Weak entities can be deleted automatically when their strong entity is deleted.
 Weak entities can be stored physically with their strong entities.

When designing an E-R diagram for a particular enterprise, you have several alternatives from which to
choose. What criteria should you consider in making the appropriate choice?
Answer: The criteria to use are intuitive design, accurate expression of the real-world concept and efficiency. A
model which clearly outlines the objects and relationships in an intuitive manner is better than one which does
not, because it is easier to use and easier to change. Deciding between an attribute and an entity set to represent
an object, and deciding between an entity set and relationship set, influence the accuracy with which the real-
world concept is expressed. If the right design choice is not made, inconsistency and/or loss of information will
result. A model which can be implemented in an efficient manner is to be preferred for obvious reasons.

Entity Relationship Diagram Exercises

For each of the following narratives, draw an ERD diagram which indicates the entities and their attributes (if
noted). Identify and label significant relationships between pairs of entities.

Exercise 1
Create a ERD for each of the following descriptions.
a) Each of the Swinby Corporation’s divisions is composed of many departments. Each of the departments
has many employees assigned to it, but each employee works for only one department. Each department is
managed by one employee, and each of these managers can manage only one department at a time.
b) During some period of time, a customer can rent many videotapes from the BigVid store. Each of the
BigVid’s videotapes can be rented to many customers during that period of time.
c) An airline can be assigned to fly many flights, but each flight is flown by only one airline.
d) The KwikTite Corporation operates many factories. Each factory is located in a region. Each region can be
"home" to many of KwikTite’s factories. Each factory employs many employees, but each of these
employees is employed by only one factory.
e) An employee may have earned many degrees, and each degree may have been earned by many employees.

Exercise 2
Pick and Shovel Construction Company is a multi-state building contractor specializing in medium-priced town
homes. Assume that Pick and Shovel’s main entities are its customers, employees, projects and equipment. A
customer can hire the company for more than one project, and employees sometimes work on more than one
project at a time. Equipment, however, is assigned to only one project at a time. Draw an ERD showing those
entities.

Exercise 3
FastFlight Airlines is a small air carrier operating in three north-eastern states. FastFlight is in the process of
computerising its passenger reservation system. The following data items have been identified: reservation
code, flight number, flight date, origin, destination, departure time, arrival time, passenger name, seat
number, reservation agent number, and reservation agent name. For example, flight number 303, which is
scheduled every Tuesday and Thursday, leaves Augusta, Maine, at 9:23am and arrives in Nashua, New
Hampshire at 10:17am. You can assume that the Fast Freight reservation system will detect automatically
whether empty seats are available. Draw the Entity Relationship Diagram for this system.

Exercise 4
Prepare an Entity Relationship Diagram for Patty’s Daycare Centre. For each entity indicate the primary key by
underlining attribute(s) making up primary key and also specify 1 additional significant attribute. Identify and
label significant relationships between pairs of entities (either 1:1, 1 : M or M:N).
Patty’s Playschool is a child daycare centre. A parent registers their child or children at the school using a
registration form. A parent can submit more than one registration form. Each room in the daycare is assigned an
age group. For example an infant is under 1 yearof age and toddlers are from 1 to 3 years of age. A child is
assigned to a room based on their age and availability of space. A room may be assigned one or more
employees. An employee can only be assigned to one room. The minimum number of employees required for a
room is determined by the number of children assigned to the room and the child:staff ratio identified by the
government. For example one employee can care for 5 infants or 8 toddlers.

More exercises:
1. A large bank operates several divisions. Information Technology (IT) is operated as one of these divisions.
Within the IT division are many departments that are managed by one manager, and all IT employees
belong to one of these departments. The IT division assigns it's employees to one or more on going projects
in the bank. A project may be planned, but not have any employees assigned to it for several months. Each
project will have a single employee assigned who acts as a project leader.

2. A hardware store sells several home workshop products to the public (such as power saws and sanders).
Each product has several different manufacturers who manufacture it, and prices are different for products
made by different manufacurers. Each time one or more products are sold to a customer, an invoice is
created which lists the date, items purchased and their prices, and then the total purchase and tax amounts.

3. The Ministry of Transportation (MOT) supplies department keeps track of all the items (furniture and
equipment such as a chair or printer) in the Ministry offices. There are several MOT buildings and each one
is given a different name to identify it. Each item is assigned a unique ID when it is purchased. This ID is
used to keep track of the item, which is assigned to a room within a building. Each room within a building
is assigned to a department, and each department has a single employee as it's manager.

4. A cooking club organizes several dinners for it's members. The purpose of the club is to allow several
members to get together and prepare a dinner for the other members. The club president maintains a
database that plans each meal and tracks which members attends each dinner, and also keeps track of which
members creates each dinner. Each dinner serves many members and any member is allowed to attend.
Each dinner has an invitation. This invitation is mailed to each member. The invitation includes the date of
the dinner and location. Each dinner is based on a single entrée and a single dessert. This entrée and dessert
can be used again for other dinners.
5. ABC Consulting is a small-sized consulting firm in the IT industry. ABC’s business is managing several
Systems Development projects by assigning staff consultants to these projects as their skills are needed.
Each employee is designated to have one primary skill, but there may be other employees with the same
primary skill. A consultant may work on one or more projects, or may not yet be assigned to a project. The
company charges for each project by billing each consultant's hours worked by the billing rate. The hourly
billing rate is dependant on the employee’s primary job skill.

6. A company purchases products and sells them to it's customers. Each time a sale occurs, an invoice is
created listing the customer name, and a list of purchase product descriptions, the supplier name for the
products, and the price of each product. The product number identifies each product and will appear again if
another customer purchases the same product. Each supplier can supply many products which we can sell,
but each product has only one supplier.

7. You are asked to create a database to produce a report of customer details. The report is to list the cutomer
name, account balance, credit limit, and other customer details. The report will also list the customer
account rep (one of our sales employees). Our sales reps manage many customers each, but each customer
will be managed by only one account rep at any one time. However, your design should allow for
customer's being managed by many account reps as it is possible that some of our employees may leave the
company - thus, requiring new account reps for a customer.

8. A company operates a warehouse parts supply business. The company has several warehouses located in
Toronto which each store several hundreds of automotive parts. We need to keep a record of how many
parts are "on hand" - meaning inventory levels that tell us how many we have for each part. To help us
organize our parts, each part is assigned a specific classification. There are 4 classifications that we use to
organize hundreds of parts.

9. To keep track of office furniture, computers, printers, and so on, the FOUNDIT company requires the
creation of a simple database. Each piece of office furniture, computer or printer is given an identification
number. Each item is then placed in a room of one of three buildings. The building manager is responsible
for the items in their building.
More Exercises (cont)
1. Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each
car has associated with it zero to any number of recorded accidents.

2. Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with
each patient a log of the various tests and examinations conducted.

3. A university registrars office maintains data about the following entities: (a) courses, including number,
title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester,
section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and
program; and (d) instructors, including identi_cation number, name, department, and title. Further, the
enrollment of students in courses and grades awarded to students in each course they are enrolled for must
be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions
that you make about the mapping constraints.

Recommended answers:
1. Car insurance tables:
person (driver-id, name, address)
car (license, year,model)
accident (report-number, date, location)
participated(driver-id, license, report-number, damage-amount)

2. Hospital tables:
patients (patient-id, name, insurance, date-admitted, date-checked-out)
doctors (doctor-id, name, specialization)
test (testid, testname, date, time, result)
doctor-patient (patient-id, doctor-id)
test-log (testid, patient-id) performed-by (testid, doctor-id)

3. University registrar.s tables:

student (student-id, name, program)
course (courseno, title, syllabus, credits)
course-offering (courseno, secno, year, semester, time, room)
instructor (instructor-id, name, dept, title)
enrols (student-id, courseno, secno, semester, year, grade)
teaches (courseno, secno, semester, year, instructor-id)
requires (maincourse, prerequisite)

4. Design an E-R diagram for keeping track of the exploits of your favourite sports team. You should store the
matches played, the scores in each match, the players in each match and individual player statistics for each
match. Summary statistics should be modeled as derived attributes.
5. Extend the E-R diagram of the previous question to track the same information for all teams in a league.