Assignment No.1
Draw an ERD for each of the following situations using Microsoft Visio.
1. A company has a number of employees. The attributes of EMPLOYEE include Employee_ID
(identifier), Name, Address, and Birthdate. The company also has several projects. Attributes
of PROJECT include Project_ID (identifier), Project_ Name, and Start_Date. Each employee
may be assigned to one or more projects, or may not be assigned to a project. A project must
have at least one employee assigned and may have any number of employees assigned. An
employee's billing rate may vary by project, and the company wishes to record the applicable
billing rate (Billing_ Rate) for each employee when assigned to a particular project.
2. A university has a large number of courses in its catalog. Attributes of COURSE include
Course_ Number (identifier), Course_Name, and Units. Each course may have one or more
different courses as prerequisites or may have no prerequisites. Similarly, a particular course
may be a prerequisite for any number of courses or may not be prerequisite for any other
course.
3. A laboratory has several chemists who work on one or more projects. Chemists also may
usc certain kinds of equipment on each project. Attributes of CHEMIST include Employee_ID
(identifier), Name, and Phone_No. Attributes of PROJECT include Project_ID (identifier) and
Start_ Date. Attributes of EQUIPMENT include Serial_No and Cost. The organization wishes to
record Assign_ Date-that is, the date when a given equipment item was assigned to a
particular chemist working on a specified project. A chemist must be assigned to at least one
project and one equipment item. A given equipment item need not be assigned, and a given
project need not be assigned either a chemist or an equipment item. Provide good definitions
for all of the relationships in this situation.
4. A college course may have one or more scheduled sections, or may not have a scheduled
section. Attributes of COURSE include Course_ID, Course_Name, and Units. Attributes of
SECTION include Section_Number and Semester_ID. Semester_ID is composed of two parts:
Semester and Year. Section_Number is an integer (such as "1" or "2") that distinguishes one
section from another for the same course but does not uniquely identifY a section.
5. A hospital has a large number of registered physicians. Attributes of PHYSICIAN include
Physician_ID (the identifier) and Specialty. Patients are admitted to the hospital by physicians.
Attributes of PATIENT include Patient_ID (the identifier) and Patient_Name. Any patient who
is admitted must have exactly one admitting physician. A physician may optionally admit any
number of patients. Once admitted, a given patient must be treated by at least one physician.
A particular physician may treat any number of patients, or may not treat any patients.
Whenever a patient is treated by a physician, the hospital wishes to record the details of the
treatment (Treatment_Detail). Components of Treatment_Detail include Date, Time, and
Results. Did you draw more than one relationship between physician and patient? Why or
why not?
6. The loan office in a bank receives from various parties requests to investigate the credit
stants of a customer. Each credit request is identified by a RequesciD and is described by a
Request_Date and Requesting_Party_Name. The loan office also received result<; of credit
checks. A credit check is identified by a Credit_Check_ID and is described by the
Credit_Check_Date and the C'xedit_Rating. The loan office matches credit requests with
credit check results. A credit request may be recorded before its result arrives; a particular
credit result may be used in support of several credit requests;. Draw an ERD for this situation.
Now, assume that credit results may not be reused for multiple credit requests. Redraw the
ERD for this new sin1ation using two entity types, and then redraw it again using one entity
type. Which of these two versions do you prefer and why?credit results may not be reused
for multiple credit requests. Redraw the ERD for this new situation using two entity types,
and then redraw it again using one entity type.