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