Database Design: Dr. Bordoloi

Database Design: Dr. Bordoloi

Homework Assignment: Normalization

Your answers to the assignment must be word-processed. Handwritten answers will be accepted with a penalty of 10%.

Guidelines for your answer

a. Indicate the primary key and the foreign key(s) of each relation clearly in your answer.

b. Non-indication or incorrect indication of PKs and FKs will result in a loss of 1 point for each PK and each FK in

c. each table.

d. Do NOT make up any fields of your own to suit your answer.

Question 1. Assume that you are involved in a database design project for a pharmacy. Your client has identified the following attributes of CUSTOMER that need to be stored in the database. Group those attributes into a relational database structure making sure that each relation in the database is in 3NF.

CUSTOMER (CUST#, DRUG-NAME, DRUG-MANUFACTURER, CUST-NAME, CUST-PHONE, DRUG-UNITS- PURCHASED,

DRUG-COST-PER-UNIT, EMPLOYER-NAME, EMPLOYER-ADDRESS, PURCHASE-DATE)

Semantics:

1. Customer numbers (CUST#) are unique, i.e., no two customers can have the same customer number.

2. Drug names are unique, i.e., no two drugs can have the same name.

3. A given drug manufacturer can manufacture many different drugs but a given drug with a specific name can be made by only one manufacturer.

4. A given customer can purchase more than one drug.

5. The same drug can be purchased by more than one customer.

6. A customer can purchase the same drug on different dates, but not the same drug more than once on the same day.

7. DRUG-UNIT-PURCHASED refers to the quantity of a particular drug purchased by a customer.

8. EMPLOYER-NAME (which is unique for each employer) refers to the employer of a customer, and a given CUST# can be associated with only one employer.

Question 2.

Consider the sample report shown below. The report reflects the activities of a construction company that manages several building projects. Each project has its own (unique) project number, name, employees assigned to the project, and so on. Each employee has an (unique) employee number, name, and job classification such as engineer, computer technician, and so on. The company charges its clients by billing the hours spent on each contract. The hourly billing rate (Charge/hr) is dependent on the employee’s position (e.g., an hour’s worth of computer technician time is billed at a different rate than an hour’s worth of engineer time).

Requirements:

a.As per our class discussions, design a set of 3NF relations to support the generation of the sample report.

b.Underline the primary key of each relation.

c.Circle the foreign key(s) in each relation.

dNon-indication or incorrect indication of PKs and FKs will result in a loss of 1 point for each PK and each FK in each table.

e. Do NOT make up any fields of your own to suit your answer.

f.BE NEAT. If I cannot read your answer, I cannot grade it either.

Sample Report:

Subtotals

Subtotals

Subtotals

Total