FD/Normalization exercize

Normalization results must be stated in relation schema format, identifying primary and foriegn keys where necessary.

1. Given the relational schema R(A,B,C,D), A -----> B and B -----> C, determine which of the following dependencies are implied by the inference axioms. State the appropriate axioms if the dependency is implied.


1. A -----> C Transitivity
   
2. A -----> BC Transitivity (without b you can't have c)
   
3. ACD -----> B Augmentation (you don't need C and D to get B)
   
4. AC -----> C Augmentation (you don't need the C to get C)
   
5. AD -----> BC Transitivity



2. Inventory Control:



A company wishes to create a database to control it's inventory, which consists of a number of products divided into a number of categories, such as clothing, food, and stationary. An employee raises a purchase order when a product has to be ordered from a supplier. The tracking records supplies recieved, units sold, and any wastage.

Draw the ERD for the inventory control system.







3. An agency called Instant Cover supplies part time/temporary staff to hotels within Scotland. The table below lists the time spent by agency staff working at various hotels. The National Insurance Number (NIN) is unique for each member of staff.
   
   
   
1. This table is susceptible to update anomalies. Provide examples of insertion, deletion, and update anomalies.
   
   insertion:
   
   • in order to add a contract you need an employee
   • in order to add a new hotel you need an employee
   deletion:
   
   • in deleting a contract info about an employee will be deleted
   update:
   
   • in order to update a hotel location you must update every one in the table
   
   info: grussel.org normalization article
   
   
   
   
2. Normalize this table to 3rd normal form. State any assumptions.

Functional Dependence (Fd)

Student_Activity
SID       Activity         Fee
100       Skiing           200
150       Swimming          50
175       Squash            50
200       Swimming          50

Overview

The secret of normal forms is to realize that some attribute values determine other attribute values. In the case above, the activity fee was dependent on the activity, not the student. If a determinant (activity in the above case) cannot be a key in the relation, those attributes that depend on the determinant will repeat each time the value for the determinant repeats (see swimming in the original relation above). To prevent determinant values from repeating it is best to create a separate relation where the determinant is a key (remember keys can't repeat). This is what we did when we split the original relation into two relations.

--oldweb normalization article

Student
SID       Activity
100       Skiing
150       Swimming
175       Squash
200       Swimming

ActivityFees
Activity  Fee
Skiing    200
Swimming  50
Squash    50