Objectives:
This tutorial will cover:
INFO20003 Tutorial – Week 9
(Tutorial: Normalisation)
Copyright By PowCoder代写 加微信 powcoder
I. Review of normalisation concepts – 15 mins
II. Normalisation exercises – 35 mins
Key Concepts:
NOTE for students: This is a brief summary of some of the concepts taught in lectures. The lectures contain detailed content related to these and many more concepts. These notes should be considered quick revision instead of a sole resource for the course material.
• Anomalies
Consider the following instance of the relation Allocation:
CourseNumber
INFO20003 COMP10001 INFO30005 COMP20005
Farah Seats
Alice Hoy 109 30 EDS 6 25 G09 20 G09 20
• Functional dependency
• Determinants
• Key and non-key attributes
• Partial functional dependency
• Transitive functional dependency
• Armstrong’s Axioms
• Normalisation and normal forms
Exercises:
1. Consider the relation Diagnosis with the schema Diagnosis (DoctorID, DocName, PatientID, DiagnosisClass) and the following functional dependencies:
DoctorID → DocName
DoctorID, PatientID → DiagnosisClass
Consider the following instance of Diagnosis:
DoctorID DocName PatientID
DiagnosisClass
Lactose intolerance Flu
Identify different anomalies that can arise from this schema using the above instance.
INFO20003 Tutorial – Week 9 1
2. Consider a relation R (A, B, C, D) with the following FDs:
AB → C, AC → B, BC → A, B → D
The possible candidate keys of R are AB, AC, and BC, since each of those combinations is sufficient to uniquely identify each record. Let’s consider AB for instance. From AB → C we see that AB uniquely identifies C, and since B alone uniquely identifies D, AB together have covered CD, i.e. the entire set of attributes.
List all the functional dependencies that violate 3NF. If any, decompose R accordingly. After decomposition, check if the resulting relations are in 3NF, if not decompose further.
3. Consider the following relation StaffPropertyInspection:
StaffPropertyInspection (propertyNo, pAddress, iDate, iTime, comments, staffNo, sName)
The FDs stated below hold for this relation:
propertyNo, iDate → iTime, comments, staffNo, sName propertyNo → pAddress
staffNo → sName
From these FDs, it is safe to assume that propertyNo and iDate can serve as a primary key. Your task is to normalise this relation to 3NF. Remember in order to achieve 3NF, you first need to achieve 1NF and 2NF.
Take-Home Exercises (not covered in tutes):
4. The following Report table is used by a publishing house to keep track of the editing and design of books by a number of authors:
report_no editor dept_no dept_name
dept_addr author_id auth_name auth_addr
4216 woolf 4216 woolf 4216 woolf 5789 koenig 5789 koenig 5789 koenig
15 design 15 design 15 design 27 analysis 27 analysis 27 analysis
argus1 argus1 argus1 argus2 argus2 argus2
53 mantel 44 bolton 71 koenig 26 fry
38 umar 71 koenig
cs-tor mathrev mathrev folkstone prise mathrev
By looking at the data, we see that functional dependencies in the Report table are the following:
report_no → editor, dept_no
dept_no → dept_name, dept_addr author_id → auth_name, author_addr
The candidate key for this relation is (report_no, author_id) since we need these two attributes to uniquely identify each record. Thus we have:
Report (report_no, editor, dept_no, dept_name, dept_addr, author_id, auth_name, auth_addr)
a. Is the Report table in 2NF? If not, put the table in 2NF.
b. Are there any insert, update or delete anomalies with these 2NF relations?
5. Consider the following relation:
INFO20003 Tutorial – Week 9 2
Class (courseNumber, roomNumber, instructorName, studentNumber, workshopNumber, grade, tutor)
The following functional dependencies hold for this relation:
workshopNumber → tutor
studentNumber, courseNumber → grade, workshopNumber courseNumber → roomNumber, instructorName
Normalise this relation into 3NF.
END OF TUTORIAL
INFO20003 Tutorial – Week 9 3
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com