Database Homework6

Normalization Exercise
Due Oct. 7

1. A database is to be designed to keep track of various information about people working for different companies. In particular, each person ( P-NAME) lives on a particular street ( STREET) in a particular city ( P-CITY). That person may work for several companies ( C-NAME) earning a salary ( SALARY) at each company. Each company, in turn, is located in one city ( C-CITY). Finally there is a certain status ( STATUS) associated with different salaries. In doing thie exercise use ONLY the attributes already defined, do not add any new ones.

This information could be represented in the relation

People( P-NAME, STREET, P-CITY, C-NAME, C-CITY, SALARY, STATUS)

The functional dependencies are:

  • NAMESTREET
  • P-NAMEP-CITY
  • C-NAMEC-CITY
  • P-NAME, C-NAMESALARY
  • SALARYSTATUS
a. Explain each of the functional dependencies in English. Do not use the words "determine, depends on or function" in your explanation.

b. What is the key of the relation People?

c. Transform the relation People to a set of fifth normal form relations, identifying the key of each of these relations.

d. Use Visio to create a ER diagram. Export and paste image into you soilution document.

2. Consider the following relation:

R = {A, B, C, D, E, F, G, H, I, J}

And the following functional dependencies

  • A, B → C
  • A → D, E
  • B → F
  • F → G, H
  • D → I, J
a. What is the key for R?

b. Decompose R into 2NF.

c. Decompose R into 3NF

d. Use Visio to create a ER diagram. Export and paste image into you soilution document.

Topic revision: r3 - 2011-10-26 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback