Previous Page Up One Level

# Normalisation Tutorial

1. A college keeps details about a student and the various modules the student studied. These details comprise
• regno - registration number
• n - student name
• tno - tutor number
• tna - tutor name
• dc - diploma code
• dn - diploma name
• mc - module code
• mn - module name
• 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. ```   EMPLOYEE(empno,empname,jobcode)
empno -> empname
empno -> jobcode
```
2. ```   EMPLOYEE(empno,empname,(jobcode,years))
empno -> empname
empno,jobcode -> years
```
3. ```   EMPLOYEE(empno,empname,jobcode,jobdesc)
empno -> empname,jobcode
jobcode -> jobdesc
```
4. ```   EMPLOYEE(empno,empname,project,hoursworked)
empno -> empname
empno,project -> hoursworked
```

3. Identify any repeating groups and functional dependences in the PATIENT relation. Show all the intermediate steps to derive the third normal form for PATIENT.
```PATIENT(patno,patname,gpno,gpname,appdate,consultant,conaddr,sample)
```
10/10/2004EdwardsBeech Aveurine
3/9/2004RussFir Stsputum
191146Falken717Ibbotson4/10/2004RussFir Stblood
001239Burgess818Seymour5/6/2004RussFir Stsputum
007249Lynch717Ibbotson9/11/2004EdwardsBeach Avenone

4. Reduce the following to BCNF, showing all the steps involved.
```Supplier(sno,sname,saddress,(partno, partdesc,(custid,custname,custaddr,quantity)))
sno,partno -> partdesc
sno,partno,custid -> quantity
sname -> sno
```
Suppliers supply many parts to many customers. Each customer deals with only one supplier. Supplier names are unique. Customer names are not unique.

5. Normalise the following relation to 3NF showing all the steps involved.
```GP(gpno,cpname,gpadd,(patno,patname,patadd,patdob,(apptdate,apptime,diagnosis,treatment)))
patno,apptdate -> apptime,diagnosis
diagnosis -> treatment
```

6. 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.
batchnoagentnoagent nameholiday codecostquantity bookedairport codeairport name
176Bairns travel B563363101Luton
B2482482012Edinburgh
B4283221811Glasgow
2142Active Holidays B563363151Luton
C930568214Newcastle
A270972114Newcastle
B728248512Edinburgh
376Bairns travel C930568111Luton
A4302791511Glasgow

7. A software consulting firm wishes to keep the following data for an employee and costing database:
• employee number
• employee name
• salary
• current job code
• job history (job promotion code + year)
• office location
• telephone number
• project number
• project name