advance data modeling

· Upload this document to Blackboard>Course Content>Module 5> Module 5 Exercise 1 as your submission.

Questions and Answers:

1. (10 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 has 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 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).

<<Paste the Diagram Here>>

2. (12 points) Bank account system.

A bank has three types of accounts: checking, saving, and loan. Each bank account must have a type and be exactly one of these types (cannot be two types at the same time). Following are the attributes for each type of account:

· CHECKING: Acct No, Date Opened, Balance, Service Charge

· SAVING: Acct No, Date Opened, Balance, Interest Rate

· LOAN: Acct No, Date Opened, Balance, Interest Rate, Payment

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).

<<Paste the Diagram Here>>

3. (12 points) Non-profit organization system.

A nonprofit organization depend on a number of different types of persons. The system needs to maintain the following data for all persons: Person_ID, Name, Address, and email. There are three types of persons: employees, volunteers, and donors. Employees have a unique Date Hired attribute, and volunteers have a distinct Skill attribute. Donors donate items. Item has attributes like Item ID and Description. A donor may donate multiple items. An item may be associated with no donor or one donor.

Occasionally, there are persons other than the three types (employees, volunteers, and donors). 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 donor). Create the EERD for the system.

Add a textbox with your full name inside the diagram (no point without your name).

<<Paste the Diagram Here>>

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:

2

DEPARTMENTDept_IDPKDept_NameMade by Shuaifu Lin

DEPARTMENT Dept_ID int FK PK Dept_Name int FK PK Made by Shuaifu Lin