A Sample Database Application

In this section we describe a sample database application, called COMPANY, which serves to
illustrate the basic ER model concepts and their use in schema design. We list the data
requirements for the database here, and then create its conceptual schema step-by-step as we
introduce the modeling concepts of the ER model. The COMPANY database keeps track of a
company’s employees, departments, and projects. Suppose that after the requirements collection
and analysis phase, the database designers provide the following description of the miniworld the
part of the company that will be represented in the database.
Section 1
Description of miniworld or database system requirements
The company is organized into departments. Each department has a unique name, a unique
number, and a particular employee who manages the department. We keep track of the
start date when that employee began man-aging the department. A department may have
several locations.
A department controls a number of projects, each of which has a unique name, a unique
number, and a single location.
We store each employee’s name, Social Security number, address, salary, sex (gender),
and birth date. An employee is assigned to one department, but may work on several
projects, which are not necessarily controlled by the same department. We keep track of
the current number of hours per week that an employee works on each project. We also
keep track of the direct supervisor of each employee (who is another employee).
We want to keep track of the dependents of each employee for insurance purposes. We keep
each dependent’s first name, sex, birth date, and relation-ship to the employee.
Section 2
Initial Conceptual Design of the COMPANY Database
We can now define the entity types for the COMPANY database, based on the requirements
described in Section 1. According to the requirements listed in Section 1, we can identify four
entity types one corresponding to each of the four items in the specification. After defining several
entity types and their attributes here, we refine our design after we introduce the concept of a
relationship.
An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and
Manager_start_date. Locations is the only multivalued attribute. We can specify that
both Name and Number are (separate) key attributes because each was specified to be unique.
An entity type PROJECT with attributes Name, Number, Location, and Controlling_department.
Both Name and Number are (separate) key attributes.
An entity type EMPLOYEE with attributes Name, Ssn, Sex, Address, Salary, Birth_date,
Department, and Supervisor. Both Name and Address may be composite attributes; however, this
was not specified in the requirements. We must go back to the users to see if any of them will
refer to the individual components of Name—First_name, Middle_initial, Last_name—or of Address.
An entity type DEPENDENT with attributes Employee, Dependent_name, Sex, Birth_date,
and Relationship (to the employee).
So far, we have not represented the fact that an employee can work on several projects, nor have
we represented the number of hours per week an employee works on each project. This
characteristic is listed as part of the third requirement in Section 1, and it can be represented by a
multivalued composite attribute of EMPLOYEE called Works_on with the simple components
(Project, Hours). Alternatively, it can be represented as a multivalued composite attribute
of PROJECT called Workers with the simple components (Employee, Hours). We choose the first
alternative in Figure 7.8, which shows each of the entity types just described. The Name attribute
of EMPLOYEE is shown as a composite attribute, presumably after consultation with the users.

Relationship Types
In Figure 7.8 there are several implicit relationships among the various entity types. In fact,
whenever an attribute of one entity type refers to another entity type, some relationship exists. For
example, the attribute Manager of DEPARTMENT refers to an employee who manages the
department; the attribute Controlling_department of PROJECT refers to the department that
controls the project; the attribute Supervisor of EMPLOYEE refers to another employee (the one
who supervises this employee); the attribute Department of EMPLOYEE refers to the department
for which the employee works; and so on.
In the ER model, these references should not be represented as attributes but as relationships,
which are discussed in this section. The COMPANY database schema will be refined in Section 3
to represent relationships explicitly. In the initial design of entity types, relationships are typically
captured in the form of attributes. As the design is refined, these attributes get converted into
relationships between entity types.

Section 3
Refining the ER Design for the COMPANY Database
We can now refine the database design in Figure 7.8 by changing the attributes that represent
relationships into relationship types. The cardinality ratio and participation constraint of each
relationship type are determined from the requirements listed in Section 1. If some cardinality ratio
or dependency cannot be determined from the requirements, the users must be questioned further
to determine these structural constraints.
In our example, we specify the following relationship types:

 MANAGES,
 A 1:1 relationship type between EMPLOYEE and DEPARTMENT -EMPLOYEE
participation is partial. DEPARTMENT participation is not clear from the requirements.
We question the users, who say that a department must have a manager at all times, which
implies total participation. The attribute Start_date is assigned to this relationship type.
 WORKS_FOR,
 A 1: N relationship type between DEPARTMENT and EMPLOYEE. Both participations
are total.
 CONTROLS,
 A 1:N relationship type between DEPARTMENT and PROJECT. The participation
of PROJECT is total, whereas that of DEPARTMENT is determined to be partial, after
consultation with the users indicates that some departments may control no projects.
 SUPERVISION,
 A 1:N relationship type between EMPLOYEE (in the supervi-sor role)
and EMPLOYEE(in the supervisee role). Both participations are determined to be partial,
after the users indicate that not every employee is a supervisor and not every employee
has a supervisor.
 WORKS_ON,
 A determined to be an M:N relationship type with attribute Hours, after the users indicate
that a project can have several employees working on it. Both participations are
determined to be total.
 DEPENDENTS_OF,
 A 1:N relationship type between EMPLOYEE and DEPENDENT, which is also the
identifying relationship for the weak entity type DEPENDENT. The participation
of EMPLOYEE is partial, whereas that of DEPENDENT is total.

After specifying the above six relationship types, we remove from the entity types in Figure 7.8
all attributes that have been refined into relationships. These include

 Manager and Manager_start_date from DEPARTMENT;

It is important to have the least possible redundancy when we