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
• a - student address
• 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)
```
patnopatnamegpnogpnameappdateconsultantconaddrsample
01027Grist919Robinson3/9/2004FarnesAcadia Rdblood
20/12/2004FarnesAcadia Rdnone
10/10/2004EdwardsBeech Aveurine
08023Daniels818Seymour3/9/2004FarnesAcadia Rdnone
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 -> sname,saddr
sno,partno -> partdesc
sno,partno,custid -> quantity
sname -> sno
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.

5. Normalise the following relation to 3NF showing all the steps involved.
```GP(gpno,cpname,gpadd,(patno,patname,patadd,patdob,(apptdate,apptime,diagnosis,treatment)))
gpno -> gpname,gpadd
patno -> patname,patadd, patdob
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
• 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.

 Previous Page Up One Level