CSIS 325
LAB: ENTITY-RELATIONSHIP (ER) DIAGRAMS TEMPLATE
1. Using this completed instance diagram fill in the cardinality symbols for the
EntityRelationship Diagram (ERD) below. You will probably need to use a basic drawing
tool within Word to draw the cardinalities.
2. In the space provided below, draw an ER diagram containing Order and Customer entities
connected by a 1-M relationship from Customer to Order. The relationship should be
called “Places”. Define minimum cardinalities so that an order is optional for a customer
and a customer is mandatory for an Order. For the Customer entity type, define
attributes as follows: CustNo (primary key), CustName, CustBalance. For the Order entity
type, define attributes as follows: OrdNo (primary key), OrderDate, OrdAddress.
Use ER Assistant to draw your diagram. Copy and paste the ERD from ER Assistant into
the space provided below. [Note: To receive credit for this problem, you must use ER
Assistant as your drawing tool.]
Page 1 of 8
CSIS 325
3. provided below the following ERD, transform the following M-N relationship into an
associative entity type and two identifying 1-M relationships.
Use ER Assistant to draw your diagram. Copy and paste the ERD from ER Assistant into
the space provided below. [Note: To receive credit for this problem, you must use ER
Assistant as your drawing tool.]
Page 2 of 8
Places
Order
OrdNo
OrderDate
OrdAddress
Customer
CustNo
CustName
CustBalance
In the
space
CSIS 325
4. In the space provided below, draw a self-referencing entity type for the Employee entity
type for which an employee is supervised by a maximum of one other employee and
doesn’t have to be supervised by any employee at all. Conversely, an employee can
supervise zero or many other employees. The attributes of the Employee entity are as
follows: EmpNo (primary key), EmpName, EmpAddress.
Use ER Assistant to draw your diagram. Copy and paste the ERD from ER Assistant into
the space provided below. [Note: To receive credit for this problem, you must use ER
Assistant as your drawing tool.]
5. In the space provided below, draw a generalization hierarchy containing Employee,
Administrator, and Faculty entities. The Employee entity is the supertype, and the
Administrator and Faculty entities are the subtypes.
The Employee entity has attributes as follows: EmpNo (primary key), EmpName, and
EmpAddress.
The Administrator entity has attributes as follows: AdmTitle, AdmContractLength The
Faculty entity has attributes as follows: FacTenure, FacRank
The generalization hierarchy should be disjoint but not complete.
Use ER Assistant to draw your diagram. Copy and paste the ERD from ER Assistant into
the space provided below. [Note: To receive credit for this problem, you must use ER
Assistant as your drawing tool.]
Page 3 of 8
Supervise
Employee
EmpNo
EmpName
EmpAddress
CSIS 325
6. In the space provided below, draw an ER diagram that includes entities for Clients,
Contracts, and Contacts.
Clients have the following attributes: ClientID (primary key), ClientName, ClientAddress.
Contracts have the following attributes: ContractID (primary key), ContractType,
LicenseFee.
Contacts have the following attributes: ContactID (primary key), ContactName,
ContactAddress.
Draw a relationship between Clients and Contracts such that a Client can be assigned to
many contracts, but it can also exist without having any contracts at all in the database.
Conversely, a contract can be assigned to only one client but must be assigned to a client
in order to exist. Name your relationship “Signs”.
Draw a relationship between Clients and Contacts such that a client must be assigned to
one and only one contact. A contact can exist in the database without being assigned to
a client, but a contact can be assigned to more than one client. Name your relationship
“Has”
Use ER Assistant to draw your diagram. Copy and paste the ERD from ER Assistant into
the space provided below. [Note: To receive credit for this problem, you must use ER
Assistant as your drawing tool.]
Page 4 of 8
D
Faculty
FacTenure
FacRank
Administrator
AdmTitle
AdmContractLen
Employee
EmpNo
EmpName
EmpAddress
Has
Signs
Contracts
ContractID
ContractType
LicenseFee
Contacts
ContactID
ContactName
ContactAddress
Clients
ClientID
ClientName
ClientAddress
CSIS 325
7. Identify two problems with the ER diagram below:
Two Entities are missing their Primary Keys, (Student and Faculty).
For the Faculty Entity, FacHireHate has a typo could be FacHireRate
Use the following ERD to answer the next 2 questions:
Page 5 of 8
CSIS 325
8. What is the primary key of the WorkHours table?
Answer: According to the ERD Day, Month and Year are the primary ket of the table.
9. Indicate the redundant field in the Employees table.
Answer: Office ID, as it could be the same as EmployeeID___________________
10. Short Answer:
Page 6 of 8
CSIS 325
When converting the ERD to a table design, how should you handle the AssignedTo
relationship? (Be sure to include a discussion of primary keys, foreign keys, and tables in
your answer.)
Answer: While each Entity will become a table, with its own primary key, that primary key
will become a foreign key in the table that I’m making a relationship with..
11. True/False
ProjectID will be a foreign key in the Employees table to reflect the “Manages”
relationship.
Answer: ______True______________________________________________________
12. After converting the ERD to a table design, what fields will be present in the Contracts
table? Indicate the primary key of the table by underlining the appropriate field(s).
Answer: ContractID, Contract, ContractTypeID
13. Short Answer:
WorkHours is a weak entity.
What would you do to transform it into a strong entity?
Answer: To change WorkHous from a weak to a strong Entitiy, I woud add a new attribute
to function as the primary key.
14. Short Answer:
Would it be desirable to change WorkHours into a strong entity? Why or why not?
Answer: No It wouldn’t, WorkHours is mainly derived from the Employee Entity it isn’t
able to exist without that relaitonship so having it remains as an weak enitity is the best
choice.
Page 7 of 8
CSIS 325
8 of 8
