MIS3306 Database Management Systems
Module 5 Exercise 1
Required for Grading:
I am
(type your full name) and I complete this assignment following the UHD
academic integrity policy.
Instructions:
•
Use Microsoft Visio or Draw.io to draw the diagram. Diagrams made by any other tools
will not be graded.
•
•
Use IE (crow’s foot) notation. Diagrams with any other notations will not be graded.
The diagram should specify entities, keys, attributes, relationship types (identifying or
non-identifying), and cardinalities (MAX and min on each side). Divide an attribute
further into simple attributes when appropriate. All many-to-many relationships, if any,
should be converted into associative entities (the answer should not have many to
many relationship). Relationship labels are not needed. Use a specialization hierarchy
when appropriate. Grading is based on the completeness of the diagram.
•
Add a textbox with your full name inside the diagram (no point without your name).
The figure below is an example.
Made by Shuaifu Lin
DEPARTMENT
PK
Dept_ID
Dept_Name
•
Upload this document to Blackboard>Course Content>Module 5> Module 5 Exercise 1
as your submission.
Questions and Answers:
1. (15 points) The Granite Sales Company
Create a Crow’s Foot ERD using the following scenario. Granite Sales Company keeps
data about departments and the employees working for the department. A department
has many employees, and each employee works for only one department. Each
department has data about the department name, mail box number, and office phone
extension.
All employees have an employee number, the employee’s name, and his/her address.
Each employee must be one of the following three types (cannot be two types
1
simultaneously): hourly, salaried, or contract. For hourly employees, hourly wages and
target weekly hours are kept in the system. For all salaried employees, the yearly salary
is recorded in the system. Some salaried employees are salespeople who can earn a
commission in addition to their base salary. Salespeople’s sales commission and profit
commission are stored in the system. For contract employees, the beginning date and
end date of their contracts are stored along with the billing rate for their hours.
A diagram is sufficient for the answer.
Add a textbox with your full name inside the diagram (no point without your name).
2. (13 points) Gym system.
A gym database keeps records about three types of persons: employees, members, and
value members. While all persons share some common data such as PersonID, Name,
and address, each person type has its own specific data. In addition to the attributes
above, employees have a UserID attribute and a certification attribute; (normal)
members have an email attribute; and value members have several special attributes
including email and discount rate. Value members also enjoy their own locker (one
locker) in the gym. One locker is assigned to one value member. Each locker should have
data including locker number, size, and location. Normal members do not have their own
locker. Occasionally, there are persons other than these three types (employees,
members, and value members). In other words, a person may not belong to any of these
three types. Also, a person may belong to two or more of these types (e.g., a person may
be an employee and also a member). Create the EERD for the system.
Add a textbox with your full name inside the diagram (no point without your name).
3. (10 points) Parking Pass System.
A parking garage has two types of parking pass: daily pass and monthly pass. A customer
who purchases a daily pass should deposit/prepaid parking fee in the pass. A customer
who purchases a monthly pass should prepaid monthly fee but enjoys his/her own
reserved parking lot. Each pass must have a type and be exactly one of these two types
(cannot be two types at the same time). Following are the attributes for each type of
parking passes
▪
DailyPass: PassNum, PassName, BenefitDescription, FeeBalance
2
▪
MonthlyPass: PassNum, PassName, BenefitDescription, BeginDate,
EndDate, ReservedLot, CarBrand, CarModel, CarColor, CarLicensePlate
You should move the common attributes into the supertype and leave the unique
attributes in the corresponding subtypes. Create the EERD for the system.
Add a textbox with your full name inside the diagram (no point without your name).
Review
4. (2 points) Choose among the learning objectives of this module listed below and answer
the following questions.
•
Describe the main extended entity relationship (EER) model constructs and how
they are represented in ERDs and EERDs
•
Use entity clusters to represent multiple entities and relationships in an entity
relationship diagram (ERD)
•
Describe the characteristics of good primary keys and how to select them
a. Which learning objective have you learned the best? Why?
Answer:
b. What hands-on skill(s) have you learned? (answer like: I can use ______ to ______. )
Answer:
3
