Normalisation-exercises

Normalisation - Exercises Exercise 1: An unnormalised staff relation in a software company has the following structure. Some data entries have been given as illustrations. Each programmer works at his own workstation computer on a number of software programs. Each particular program is written in one language. Staff

Programmer

Programmer Workstation

Make

Program Number Language

Smith Smith Smith Patel Patel

Number 0016 0016 0016 1005 1005

IBM IBM IBM Siemens Siemens

CS01 CS43 CS213 HS57 HS82

X194 X194 X194 L29 L29

Java Java V Basic VBasic Java

1. What are the problems likely to arise in this structure? 2. Indicate the functional dependencies in the Staff relation. If you need to make further assumptions you should state them clearly. 3. Obtain a set of Second Normal Form relations from Staff. What problems remain with these Second Normal Form relations? Exercise 2. A software company has constructed a relational database to keep track of its computers. Most of the computers remain on the company’s premises, in which case a list of projects that are authorised to use the computers is maintained. (Some specialised computers may be shared by several projects. A project typically requires access to several computers). The database must also record the members of staff assigned to each project. A member of staff is assigned to only one project at a time. Some computers are loaned to members of staff for use at home. Faculty equipment is sent to the supplier for repairs. Unfortunately, the database was designed by someone with very little knowledge of relational theory. This person decided to hold all information in a single table with the following schema:

Equipment(Mach_Num, Type, Manf, Mod_Num, On_Prem, Location, {P_ID, P_Name, Mgr_ID, Start_Date, End_Date, Auth_Period, {Staff_ID, Staff_Name, Staff_Room}}, On_loan?, Staff_ID, Staff_Name, Staff_Room, For_Repair?, Fault, Supp_ID, Supp_Name, Supp_Add, Supp_Phone)

Repeated groups are enclosed in {}. The meaning to be attached to individual attributes is given below: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13.

Mach_Num A unique identifier for each computer. Type PC, Mac, Workstation etc Manf, Mod_Num The name of the manufacturer and the model number. On_Prem? Yes if on premises; No otherwise. Location Room Number, if on premises. P_ID, P_Name ID and the name of the project Mgr_ID Staff ID of the project manager Start_Date, End_Date Start and end dates of the project. Auth_Period The period during which a project is authorised to use the equipment. Note that this may or may not coincide with the entire duration of the project {Staff_ID, Staff_Name, Staff_Room } The ID, name and room number of the staff member to whom the equipment is loaned. For_Repair? Yes, if the machine is away for repair; No otherwise. Fault Fault description, if the computer is away for repair. Supp_ID, Supp_Name, Supp_Add, Supp_Phone ID, name, address and telephone number of the supplier.

Decompose the above relation into 3NF relations, taking care to identify primary and foreign keys in all relations. Exercise 3. The Computer department of a large university has decided to provide students with a document that lists the various types of programming skills they have picked up on the course and the level they have reached in each. The table below shows the attributes that need to be stored about each student along with some sample entries.

Student_Email Name

Address

D96abc D96abc D96cde D96xyz

6 Old Manor 6 Old Manor 4 Coach Rd 1 New Row

Jones Jones Stewart Rodgers

Course

Course Skill_id Director Computing Paul 22 Computing Paul 23 Business Thompson 33 Business Thompson 16

Skill_Name Skill_date Skill_Level Prolog Java Prolog Pascal

2/4/99 1/3/99 3/5/99 1/3/99

The Skill_id is a number that is unique for each skill, but the skill name is not. The Student_Email is also unique for each student.

6 5 8 4

Students taking computing modules may come from outside courses such as Business. This information along with the course director responsible is also recorded. There is a single course director for each course, but a member of staff can be course director for several courses. Each programming skill that a student has been tested on is recorded with the date that the test took place and the skill level reached. The test may be repeated, but only the latest result is to be stored. Thus if Jones retakes the Prolog test then only the date and level attributes would be updated. 1. Explain, using the above example, the problems that may occur when insertions deletions and modifications are made. 2. Identify all functional dependencies among attributes. If you decide that the information given is not sufficient for determining all functional dependencies, make whatever assumptions you think are necessary but state them clearly. 3. Define first, second and third normal forms 4. Decompose the above example into 3NF, taking care to identify primary and foreign keys in all relations.