Normal Is At Ion Tutorial

Normalisation Tutorial 1. A college keeps details about a student and the various modules the student studied. These details comprise o regno - registration number o n - student name o a - student address o tno - tutor number o tna - tutor name o dc - diploma code o dn - diploma name o mc - module code o mn - module name o res - module exam result where details(regno,n,a,tno,tna,dc,dn,(mc,mn,res)) dc -> dn tno -> tna mc,mn -> res n -> a mc -> mn

Reduce the relation DETAILS to third normal form. 2. Classify the following relations as either UNNORMALISED, 1NF, 2NF or 3NF. If the relation is not in 3NF, normalise the relation to 3NF. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16.

EMPLOYEE(empno,empname,jobcode) empno -> empname empno -> jobcode EMPLOYEE(empno,empname,(jobcode,years)) empno -> empname empno,jobcode -> years EMPLOYEE(empno,empname,jobcode,jobdesc) empno -> empname,jobcode jobcode -> jobdesc EMPLOYEE(empno,empname,project,hoursworked) empno -> empname empno,project -> hoursworked

2. Identify any repeating groups and functional dependences in the PATIENT relation. Show all the intermediate steps to derive the third normal form for PATIENT. 3. PATIENT(patno,patname,gpno,gpname,appdate,consultant,conaddr,samp le)

patno patname gpno gpname 01027 Grist

919 Robinson

08023 Daniels

818 Seymour

191146 Falken 717 Ibbotson 001239 Burgess 818 Seymour 007249 Lynch 717 Ibbotson

appdate 3/9/2004 20/12/2004 10/10/2004 3/9/2004 3/9/2004 4/10/2004 5/6/2004 9/11/2004

consultant conaddr Farnes Acadia Rd Farnes Acadia Rd Edwards Beech Ave Farnes Acadia Rd Russ Fir St Russ Fir St Russ Fir St Edwards Beach Ave

sample blood none urine none sputum blood sputum none

4. 5. Reduce the following to BCNF, showing all the steps involved. 6. Supplier(sno,sname,saddress,(partno, partdesc,(custid,custname,custaddr,quantity))) 7. sno -> sname,saddr 8. sno,partno -> partdesc 9. sno,partno,custid -> quantity 10. sname -> sno 11. custid -> custname,custaddr

Suppliers supply many parts to many customers. Each customer deals with only one supplier. Supplier names are unique. Customer names are not unique. 12. Normalise the following relation to 3NF showing all the steps involved.

13.GP(gpno,cpname,gpadd,(patno,patname,patadd,patdob,(apptdate,appti me,diagnosis,treatment))) 14. gpno -> gpname,gpadd 15. patno -> patname,patadd, patdob 16. patno,apptdate -> apptime,diagnosis 17. diagnosis -> treatment

18. The table below shows an extract from a tour operator's data on travel agent bookings. Derive the third normal form of the data, showing all the intermediate steps. batchno agentno

agent name

1

76

Bairns travel

2

142

Active Holidays

3

76

Bairns travel

holiday code B563 B248 B428 B563 C930 A270 B728 C930 A430

cost 363 248 322 363 568 972 248 568 279

quantity booked 10 20 18 15 2 1 5 11 15

airport code 1 12 11 1 14 14 12 1 11

airport name Luton Edinburgh Glasgow Luton Newcastle Newcastle Edinburgh Luton Glasgow

19. 20. A software consulting firm wishes to keep the following data for an employee and costing database: o o o o o o o o o o o o o o o o

employee number employee name employee address salary current job code job history (job promotion code + year) office location telephone number project number project name task number task name project budget task expendature to date department number department name

There are none, one or mor ejob promotion code/year entries per employee. The office location uniquely depends on the telephone number, and there may be more than one employee using the same telephone and more than one telephone in the one office. Tasks are numbered uniquely only within each project. An employee may be concurrently assigned to more than one project and task, but belongs to one department. Reduce this data to third normal form.