Introduction to Databases for Business Analytics
Week 5 Normalisation Part 2
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.
Acknowledgement of Country
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f
❑Review normalisation and its role in the database design process
❑Identify and describe each of the normal forms: 1NF, 2NF, 3NF, and BCNF.
❑Explain how normal forms can be transformed from lower normal forms to higher normal forms (and vice versa – denormalisation)
❑Apply normalisation rules to evaluate and correct table structures
❑Identify situations that require denormalisation to generate information efficiently
Review (Normal Forms)
Normal Form
Characteristic
First normal form (1NF)
PK identified and no repeating groups
Second normal form (2NF)
1NF and no partial dependencies
Third normal form (3NF)
2NF and no transitive dependencies
Boyce- F (BCNF)
Every determinant is a candidate key
❑ Create a valid primary key and resolve multi-valued attributes. • First Normal Form (1NF)
❑ Draw partial functional dependency diagrams and resolve them. • Second Normal Form (2NF)
❑ Draw transitive functional dependency diagrams and resolve them. • Third Normal Form (3NF)
❑ Resolve cases where non-key attributes determine primary key attributes. (Special case of 3NF) • Boyce- ormal Form (BCNF)
Review (Functional Dependencies)
❑ Functional Dependencies
• Inclusion (or reflexive) rule
• Augmentation rule
• Transitivity rule, …
❑ Partial dependency: functional dependence in which the determinant is only part of the primary key
• Assumption: one candidate key
• Straight forward
• Easy to identify
❑ Transitive dependency: attribute is dependent on another attribute that is not part of the primary key
• More difficult to identify among a set of data
• Occur only when a functional dependence exists among nonprime attributes
Inference Rules for Functional Dependencies (FDs) – Armstrong’s Axioms Primary Rules
A → B: Attribute B “functionally depends” on an attribute A; or Attribute A determines attribute B; or
“If I know the value of A, then I know the value of B”.
1) Inclusion (Reflexive) rule: if 𝒀 ⊆ 𝑿, then 𝑿 → 𝒀 (⊆: the notation of subset)
“If zID ⊆ {zID, LastName}, then {zID, LastName} → zID” (“If zID is a part of the attribute set {zID, LastName}, then {zID, LastName} determines zID”)
2) Augmentation rule: if 𝑿 → 𝒀, then 𝑾, 𝑿 → {𝑾, 𝒀}
“If zID → LastName, then {zID, FirstName} → {LastName, FirstName}”
3) Transitivityrule:if𝑿→𝒀and𝒀→𝒁,then𝑿→𝒁
“If zID → MobileNumber and MobileNumber → LastName, then zID → LastName”.
If𝑋→𝑌and𝑋→𝑍,then𝑋→ 𝑌,𝑍 .
𝑋 → 𝑌 … (1) (Given)
𝑋,𝑍 → 𝑌,𝑍 … (2) (Augmentation of (1) & 𝑍)
𝑋 → 𝑍 … (3) (Given)
Augmentation of (3) & 𝑋
𝑋, 𝑋 → 𝑋, 𝑍 , and 𝑋, 𝑋 is just 𝑋
𝑋 → 𝑋, 𝑍 … (4) (Augmentation of (3) & 𝑋)
𝑋 → {𝑌, 𝑍} … (5) (Transitivity of (4) and (2))
Armstrong’s Axioms Primary Rules
i. Inclusion (Reflexive) rule: If 𝑌 ⊆ 𝑋, then 𝑋 → 𝑌.
ii. Augmentation rule: If 𝑋 → 𝑌, then 𝑊,𝑋 → 𝑊,𝑌 .
iii. Transitivityrule:If𝑋→𝑌and𝑌→𝑍,then𝑋→𝑍.
Pseudo-Transitivity
If𝑋→𝑌and 𝑌,𝑍 →𝑊,then 𝑋,𝑍 →𝑊.
𝑋 → 𝑌 … (1) (Given)
Required in your Assessment
𝑋,𝑍 𝑌,𝑍 𝑋, 𝑍
→ 𝑌,𝑍 … (2) (Augmentation of (1) & 𝑍) → 𝑊 … (3) (Given)
→ 𝑊 … (4) (Transitivity of (2) and (3))
Armstrong’s Axioms Primary Rules
i. Inclusion (Reflexive) rule: If 𝑌 ⊆ 𝑋, then 𝑋 → 𝑌.
ii. Augmentation rule: If 𝑋 → 𝑌, then 𝑊,𝑋 → 𝑊,𝑌 .
iii. Transitivityrule:If𝑋→𝑌and𝑌→𝑍,then𝑋→𝑍.
Review – Denormalisation
❑Structural point of view of normal forms
• Higher normal forms are better than lower normal forms
❑Denormalisation: produces a lower normal form • Resultsinincreasedperformanceandgreaterdataredundancy
Demonstration of Normalisation (Exercises)
Source: weightwatchers.com
Demonstration (Exercise 0)
We are supposed to create 1NF, 2NF and 3NF as well as to create an ER diagram from this table. To do this, we need to draw functional, partial and transitive dependency diagrams.
Dependents
Base Salary
E, MSc, PhD
Information Systems
Senior Lecturer
Marge (wife), Bart (son), Lisa (daughter)
Level-1, Level-2
Handling Multi-Valued Attributes
Problem 1: the table has several multi-valued attributes and some attributes are not atomic…
BE, MSc, PhD
Information Systems
Mr. Class
Senior Lecturer
Dependents
Marge (wife), Bart (son), Lisa (daughter)
Level-1, Level-2
Base Salary
Handling Multi-Valued Attributes
Multi-valued
R (Emp#, Emp. Name, Education, Dep. Code, Dep. Name, Dep. Mgmt., Job Class, Title, Dependents, DOB, Hire Date, Training, Base Salary)
Multi-valued Multi-valued
Dependency diagrams cannot handle multi-valued attributes.
Handling Multi-Valued Attributes
We split the multivalued attributes apart, using our ER/conceptual modelling knowledge. We replace with appropriate single-value attributes.
• Delete Education➔Add Education ID (Edu#), Education Description, Graduate Date
• Delete Dependents➔Add Dependent ID (Depd#), Dependent Name
• Delete Training➔Add Training ID (Train#), Training Description, Certificate Date
❑ For the case of Job Class, we associate with other variables, we can change to:
• Delete Job Class➔Add Job ID (Job#), Title, Base Salary
R(Emp#,EmployeeName,DOB,HireDate,Edu#, EducationDesc.,GraduateDate, Dept. Code, Dept. Name, Dept. Mgmt., Job#, Title, Base Salary, Depd#, Dependent Name, Train#, Training Desc. Certification Date)
Bottom-Up Approach
Start with existing data structure/tables > then try to derive the 3NF from there. Identify the candidate keys – from there you can identify the PKs
(Hint: this is what we do here)
❑ You can see Emp# and Educ# could be candidate keys. Other Employee attributes associate with Emp#.
❑ Likewise, a few attributes are associated with Educ#.
Normalisation: Weak Entity There seems there is a “weak entity” in the table.
BE, MSc, PhD
Information Systems
Mr. Class
Senior Lecturer
Dependents
Marge (wife), Bart (son), Lisa (daughter)
Level-1, Level-2
Base Salary
Original R:
R (Emp#, Emp. Name, Education, Dep. Code, Dep. Name, Dep. Mgmt., Job Class, Title, Dependents, DOB, Hire Date, Training, Base Salary)
Modified R:
After all the changes, now the updated R is 1NF because: no multivalued attributes +
valid primary key.
R (Emp#, Emp. Name, DOB, Hire Date, Edu#, Education Desc., Graduate Date, Dept#, Dept Name, Dept Mgmt., Job#, Title, Base Salary, Depd#, Depd. Name, Train#, Training Desc., Certification Date)
From 1NF to 2NF Via Dependency Diagrams
Using Dependency Diagrams
1) Emp#+Dep.#+Job#+Edu#+Train#+Depd#>primarykeyfunctionaldependency>OK/no
2) Dep.#isthekeyforDep.NameandDep.Mgmt(partialfunc.dependency)>newrelation Department required
3) Job#isthekeyforTitle,BaseSalary(partialfunc.dependency)>newrelationJobrequired
4) Edu#isthekeyforEduc.Desc.(partialfunc.dependency)>newrelationEducationrequired
5) Emp#ANDEdu#arethekeysforGrad.Date(partialfunc.dependency)>newrelationEmp.-Edu. required (Composite/Bridge entity)
6) Train#isthekeyforTrain.Desc.(partialfunc.dependency)>newrelationTrainingrequired
7) Emp#ANDTrain#arethekeysforCert.Date(partialfunc.dependency)>newrelationEmp.-
Train. required (Composite/Bridge entity)
8) Emp#ANDDepd#arethekeyforDepn.Name(partialfunc.dependency)>newrelation Dependent required (weak entity)
1. Employee (Emp#, Emp. Name, DOB, Hire Date, Dept. Code, Job#)
2. Department (Dept.#, Dept Name, Dept Mgmt)
3. Job (Job #, Title, Base Salary)
4. Education (Edu#, Edu. Desc.)
5. Emp.-Edu. (Emp#, Edu#, Grad. Date)
6. Training (Train#, Train. Desc.)
7. Emp.-Train. (Emp#, Train#, Cert. Date)
8. Dependent (Emp#, Depd#, Dependent Name)
No transitive dependencies → 3NF
ER Diagram
Normalisation Exercises
Source: weightwatchers.com
Exercise 1
Consider the following relational schema R (A, B, C, D, E, F, G, H) and the following functional dependencies:
• A,B➔C,D,E,F,G,H • A➔C,D,G,H
❑ Create functional dependency, partial dependency and transitive dependency diagrams. ❑ Normalise to 1NF, 2NF and 3NF.
❑ Draw the ER diagram from the 3NF (Optional)
Solution to Exercise 1
Exercise 2
For the following relations:
❑ Indicate the normal form (1NF, 2NF or 3NF) for the relation.
❑ Decompose into the 3NF (if not already in 3NF)
❑ Note: Functional dependencies (FDs) – other than those implied by the primary keys (PKs)– areshown.
1) CLASS (Course_No, Section_No)
2) CLASS (Course_No, Section_No, Room)
3) CLASS (Course_No, Section_No, Room, Capacity), with FD: Room➔Capacity
4) CLASS (Course_No, Section_No, Course_Name, Room, Capacity), with FDs: Course_No➔Course_Name; Room➔Capacity
Solution to Exercise 2
1) CLASS(Course_No,Section_No)
3NF: only key fields, automatically in 3NF.
CLASS (Course_No, Section_No, Room)
3NF: all attributes depending on entire PK.
CLASS (Course_No, Section_No, Room, Capacity), with FD: Room➔Capacity 2NF: has transitive dependency.
To 3NF: CLASS(Course_No, Section_No, Room)
ROOM(Room, Capacity)
CLASS (Course_No, Section_No, Course_Name, Room, Capacity), with FDs: Course_No ➔ Course_Name; Room➔Capacity
1NF: has partial dependency
To 3NF: CLASS(Course_No, Section_No, Room)
COURSE(Course_No, Course_Name)
ROOM(Room, Capacity)
Exercise 3
A librarian has created the above table in an effort to create a “database”. However, there are several issues with the design.
1. Argue what potential problems there are with the table design.
2. Identify the PK(s) and draw the dependencies diagrams.
3. Normalise the relational model the 3NF.
4. Draw the ER diagram based on the 3NF.
Solution to Exercise 3
1. Potential problems with the table are:
• Not in 1NF, hence cannot be used in relational DBMS.
• PK not completely defined, could let to identical tuples.
• Order of rows matters (cannot be sorted in different order).
• Has redundant data.
• Invites inconsistencies/anomalies…
Solution to Exercise 3
2. PK(s) and dependencies diagrams
Functional dependency diagram
Partial dependency
Transitive dependency
Solution to Exercise 3
3. Normalise it to the 3NF (Step 1) MEMBER(Member_ID, Member_Name)
BOOK (Call_ID, Copy_ID, Book_Title, Author_ID, Author Name)
Transitive dependency
BORROW (Member_ID, Call_ID, Copy_ID, Borrow_Date, Return_Date)
Note: Borrow Date should be modelled as part of PK to handle multiple borrowing for the same book by the same member.
Solution to Exercise 3
3. Normalise it to the 3NF (Step 2)
MEMBER(Member_ID, Member_Name)
BOOK (Call_ID, Copy_ID, Book_Title, Author_ID)
AUTHOR (Author_ID, Author_Name)
BORROW (Member_ID, Call_ID, Copy_ID, Borrow_Date, Return_Date)
Solution to Exercise 3
3. Normalise it to the 3NF, and show the dependency diagram for each table MEMBER(Member_ID, Member_Name)
BOOK (Call_ID, Copy_ID, Book_Title, Author_ID) AUTHOR (Author_ID, Author_Name)
BORROW (Member_ID, Call_ID, Copy_ID, Borrow_Date, Return_Date)
Required in your Assessment
Solution to Exercise 3
4. Draw the ERM (based on the 3NF):
Required in your Assessment
Exercise 4
Joe is the manager of a dinner club would like to create a database to email event invitations to the club’s members, to plan the meals, to keep track of who attends the dinners etc. He explains the following business rules:
▪ Eachdinnerisjoinedbymanymembersandeachmembermayattend many dinners.
▪ Amemberreceivesmanyinvitationsandeachinvitationisemailedtomany members.
▪ Adinnerisbasedonasingleentrée,butanentréemaybeusedasthe basis for many dinners. For example, a dinner may be composed of a fish entrée, mushroom risotto and panna cotta. Or, a dinner may be composed of a fish entrée, wagyu beef and tiramisu. The same goes for the other dishes…
Because the manager is not a database expert, his first attempts at a “database” have resulted in the following, not very functional structure (on the right). Can you help Joe?
1. Draw functional, partial and transitive dependency diagrams.
2. Create the 1NF, the 2NF and the 3NF.
3. Draw the ER diagram from the 3NF.
Sample Value
. Van der Voort
325 Murkywaters
MEMBER_ZIPCODE
INVITE_NUM
INVITE_DATE
ACCEPT_DATE
DINNER_DATE
DINNER_ATTEND
DINNER_CODE
DINNER_DESCRIPTION
ENTRÉE_CODE
ENTRÉE_DESCRIPTION
DESSERT_CODE
DESSERT_DESCRIPTION Chocolate Mousse
Attribute Name
MEMBER_NUM MEMBER_NAME MEMBER_ADDR MEMBER_CITY
12345 8 1/8/12 9/8/12 23/8/12 Y
Sea Delight 3
Stuffed Crab 8
Solution to Exercise 4
Solution to Exercise 4
Transitive dependency
Solution to Exercise 4
Solution to Exercise 4
Source: petcare.com.au
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com