CS计算机代考程序代写 SQL Functional Dependencies database information retrieval flex Database Fundamentals

Database Fundamentals

Database Fundamentals
Normalisation Concepts

Normalisation
Database designs can be generated by decomposing “Mega” relational schemas into smaller schemas

Based on knowledge of the attributes and relationships between them called Functional Dependencies (FDs)

These smaller relations satisfy some normal form in that they contain fewer anomalies and that there is no loss of information

Rather than using ER/UML, database designs can be generated by decomposing “Mega” relational schemas into smaller schemas based on knowledge of the attributes and properties of the data

Normalisation
The normalisation process takes a relational schema through a series of tests to “certify” whether or not it belongs to a certain normal form

Separating Functional Dependencies into new relational schemas will generate tables in a specific normal form
1NF, 2NF, 3NF or even Boyce-Codd normal form (BCNF)

Each increasing normal form provides greater protection against data anomalies from developing

Normalisation of data takes unsatisfactory relational schemas and decomposes them by breaking up their attributes into smaller relational schemas with more desirable properties.
Normalisation involves distributing data across multiple distinct tables. The higher the normal form the harder the database is to query and insert records but the safer it is from developing data anomalies.
Normalisation is the process of evaluating and correcting relational table structures to minimise data redundancies. This in turn helps reduce the possibility of data anomalies developing. Eg, if you have a customer’s address in 5 different places and the customer moves, it needs to be updated in 5 different places

Normalisation
Example Mega Schema
empID empName dob address deptNo deptName deptMgr
01 Danielle 88 1 abc st 3 Research 02
02 Fredrich 87 13 def rd 3 Research 02
03 Jonathan 65 22 Wilson Ave 2 Admin 04
04 Stacy 73 7a Rose Tc 2 Admin 04
05 Bev 91 386 NE Rd 1 IT 05
06 Ashleigh 95 14 Bull Tce 1 IT 05
07 Peter 95 18 Corn St 4 Comedy 07

Deletion Anomaly:
Deleting a record for a department with only one employee will lose info about the department

Insertion Anomaly:
Creating a new employee requires all the department information to be filled in correctly

Creating a new department requires a bogus empID number

Update Anomaly:
If the deptMgr is changed, it needs to be changed for ALL records
Flexibility Issue:
What if an employee ends up working across multiple departments?

Null Values:
A good design should avoid NULL values because of their side-effects in some Queries
4

To insert a new employee we must include either arbitrary values for the dept that the employee works for or NULL values. For example if we create a new employee and they work for dept 3, all values must be filled in correctly to ensure that they are consistent with the other research department listings.
It is also difficult to insert a new department that has no employees since the PK is empID. 2ndly when the first employee of a new department is assigned, the empty original row for the department only record would no longer be needed.

Normalisation
Normalisation when done correctly:
Is lossless
Lossless: When the resulting normalised tables are joined using SQL queries, the original data/schema can be re-created without spurious tuples

Retains all the Functional Dependencies identified
They may be represented in some of the individual normalised relations that result
No data is lost
No “information” contained in the data identified by FDs should be lost
5

Normalisation
Example spurious tuples
Assumption 1: a specific car (ie plateNo) can be only one colour, plateNo –> colour
Assumption 2: colours are associated with specific makes of car, colour + make are a key
plateNo make colour
abc-123 kia black
abc-567 subaru black
abc-890 cherri red

Normalise
plateNo colour
abc-123 black
abc-567 black
abc-890 red

make colour
kia black
subaru black
cherri red

Cars(plateNo, make, colour)
Cars(plateNo, colour)
CarMakes(make, colour)
What Happens with Cars JOIN CarMakes?
6

Normalisation
What Happens with Cars JOIN CarMakes?
plateNo make colour
abc-123 kia black
abc-567 subaru black
abc-890 cherri red

Normalise
plateNo colour
abc-123 black
abc-567 black
abc-890 red

make colour
kia black
subaru black
cherri red

Cars(plateNo, make, colour)
Cars(plateNo, colour)
CarMakes(make, colour)
plateNo make colour
abc-123 kia black
abc-123 subaru black
abc-567 subaru black
abc-567 kia black
abc-890 cherri red

SELECT * FROM Cars AS C
JOIN CarMakes AS CM
ON C.colour = CM.colour

Normalisation – Functional Dependencies
A Functional Dependency (FD) is a property that describes how the attributes in a relation relate to one another

A student number is always associated with the same student name
A student’s ID number uniquely determines the student name
studentID –> studentName

A course number is always associated with the same course name
A course number uniquely determines a course name
courseNo –> courseName

Functional Dependences can be used to help determine candidate keys in a relation

A functional dependency is a constraint between two sets of attributes in the data and is denoted X -> Y
For a relation R if X -> Y then for any two tuples t1 and t2 in R such that t1[X] = t2[X] we must also have t1[Y] = t2[Y]. Thus Y is functionally dependent on X.

Thus, X functionally determines Y in a relational Schema R if and only if whenever two tuples of R agree on their X-value(s) they must also agree on their Y-value(s)

Normalisation – Functional Dependencies
Example:
Suppose a student’s grade is determined by their mark
FD: Mark –> Grade

mark grade
>= 85 HD
>= 75 D
>= 65 C
>= 50 P
< 50 F student mark grade Mary 82 D Ben 92 HD James 82 D Peter 81 D Stacy 65 C If this relationship is guaranteed in the data then any two tuples with the same Mark will ALWAYS have the same Grade. This would be an example of a Functional Dependency where the grade is determined by the mark A FD is based on real world knowledge of the data (not necessarily patterns observed in the data) If we specify an FD then all instances of the data must adhere to the FD Violations of FDs result in redundant information being stored in a table = potential anomalies 9 It’s really the real world constraints that drive which functional dependencies hold for the relation. These constraints on the data can then be translated into functional dependencies which can then be used to drive good relational design 9 Functional Dependencies - Examples A Functional Dependency may or may not hold true for a given instance Data can be checked to see if it violates a Functional Dependency Dept --> {Dname, DeptMgr}
EName EmpID BDate Dept Dname DeptMgr
Tom 1 20-Dec CS Comp Sci 3
John 2 21-Dec CS Comp Sci 3
Mary 3 22-Dec CS Comp Sci 3
Tom 4 21-Dec Math Mathematics 4

Employee_Dept

EmpID –> {Ename, Bdate, Dept}

Functional Dependencies – Examples
A Functional Dependency may or may not hold true for a given instance
Data can be checked to see if it violates a Functional Dependency

11
employeeID name phone room
1001 Chris 8375 D1-11
1002 Connie 8221 Front Office
1003 Sarah 8221 Front Office
1004 Mary 8375 D2-15

Room –> Phone
The Room functionally determines the phone extension number

The “Front Office” is always associated with the Phone no. 8221

Functional Dependencies – Examples
12
Phone –> Room
The Phone number DOES NOT determine the Room number

The same phone number is not always associated with the same room
employeeID name phone room
1001 Chris 8375 D1-11
1002 Connie 8221 Front Office
1003 Sarah 8221 Front Office
1004 Mary 8375 D2-15

A Functional Dependency may or may not hold true for a given instance
Data can be checked to see if it violates a Functional Dependency

Functional Dependencies – Examples
A Functional Dependency may or may not hold true for a given instance
Data can be checked to see if it violates a Functional Dependency

EmployeeID –> Name, Phone, Room is a reasonable FD to recommend
Room –> Phone number may be an unsafe FD

– What if using VoIP and the phone number follows the staff member?
?
employeeID name phone room
1001 Chris 8375 D1-11
1002 Connie 8221 Front Office
1003 Sarah 8221 Front Office
1004 Mary 8375 D2-15

Functional Dependencies and Keys
Functional Dependencies generalise the notion of keys
Suppose we have a relation R(A, B) with no duplicate tuples
If A –> B
Then for any two or more tuples with the same value(s) for “A”, the values for attribute “B” must also be the same.
This would result in duplicate tuples (ie violating the primary key)
“A” must be a primary key of the relation

“A” is called the Determinant
“B” is called the Dependent
Determinant –> Dependent
If my bike has license plate XYZ-123 and I’m speeding past a speed camera the government can send me a fine
They can determine me (the dependent) via my license plate (the determinant)

Only I have this license plate

XYZ-123 –> Me!

Rules for Functional Dependencies
Splitting Rule
A good functional dependency cannot be broken down further by splitting it into smaller components

CourseMarks(studentID, courseID, mark)
FD: {studentID, courseID} –> mark

Splitting the FD, do the following still hold true?
FD1: studentID –> mark
FD2: courseID –> mark

Rules for Functional Dependencies
Combining Rule
A good functional dependency can be added to other like dependencies without changing the meaning

Student(studentID, studentName, phoneNumber, address)
FD1: studentID –> studentName
FD2: studentID –> phoneNumber
FD3: studentID –> address

Combining the FDs, do they still hold or does it change the meaning?
FDall studentID –> {studentName, phoneNumber, address}

Rules for Functional Dependencies
Transitive Rule
If A –> B and B –> C then technically A –> C

Student(studentID, suburb, postcode, state)
FD1: studentID –> suburb, postcode
FD2: {suburb, postcode} –> state
Transitively: studentID –> state
Called a Transitive Dependency (TD)

Rules for Functional Dependencies
Closure of Attributes
A relation may have numerous FDs that hold across all tuples in the relation
The set of all FDs is called the closure and is denoted R+ or [R]+

Lets suppose we are given a relation R, a set of Functional Dependencies for the relation ∑ and a set of attributes “A” that determine the values of all other attributes
Then we should be able to find ALL attributes in the relation R using “A” and the functional dependencies contained in ∑
“A” would be a candidate key for the relation R
If this is the case then the normalisation has been Lossless
The original data can be re-created by re-combining the FDs

Rules for Functional Dependencies
Calculating Closure
The closure of a FD helps us to find the primary candidate keys of a relation
If the closure [A]+ contains all the available attributes via their FDs, then “A” is a candidate key

Student(studentID, studentName, address, mark, grade)
FD1: studentID –> studentName, address, mark
FD2: mark –> grade

Closure [mark]+

Closure [studentID] +

∑ = FD1 + FD2
19

Closure Example

Closure Example
Given the relation R(ABCDEFGH) and the functional dependencies:

Determine the available candidate keys

R(A,B,C,D,E,F,G,H)
———-
CH –> G
A –> BC
B –> CFH
E –> A
F –> EG
21

Closure Example
Determine which attributes are always on the LHS (the determinants)
Look for attributes whose values are not dependent on other attributes (i.e. attributes that DON’T appear on the RHS of the –>)
These are usually not the dependent attributes

Work out the closure for those attribute(s)
D+
If un-successful, calculate other closures…

Work out the closure of determinants + other attributes
Look for attributes that feature rarely on the RHS but also appear on the LHS
DA +, DB +, DE +, DF +, …
R(A,B,C,D,E,F,G,H)
———-
CH –> G
A –> BC
B –> CFH
E –> A
F –> EG
22

Closure Example
Closure of D+

DA +

DB +
R(A,B,C,D,E,F,G,H)
———-
CH –> G
A –> BC
B –> CFH
E –> A
F –> EG
23

[D]+ -> D // there are no dependents of D. Thus D cannot be a candidate key
[DA]+ -> A,D
A -> BC so ADBC,
B -> CFH so ADBCFH
F -> EG so ADBCFHEG
// DA is a candidate KEY

[DB]+ -> D,B
B -> CFH so DBCFH
F -> EF so DBCFHEG
E -> A SO DBCFHEGA
// DB is a candidate key

[DE]+ -> DE
E -> A so DEA
BUT DA or AD is already a candidate key so DE is a candidate

[DF]+ -> DF
F -> EG so DFEG but DE is already a candidate key so DF is also a candidate

Closure Example
Roriginal(C1, C2, C3, C4, C5, C6)

Closure of [C1, C2]+

Closure of [C2]+

R(C1, C2, C3, C4, C5, C6)
———-
{C1, C2} –> C5
C1 –> C3
C2 –> C4
C5 –> C6
24

Normalisation
Summary of the Normal Forms
25

Normalisation – Overview
Normalisation is the process of efficiently organising table schemas based on relationships within data
Eliminate redundant data
Prevent storing the same data in more than one table
Prevents anomalies and data inconsistencies

Creates smaller schemas that contain only relevant information
Functional Dependencies are separated into their own tables that are readily updated
Only store related data in a table

Retain the original information captured
Normalisation should not result in a loss of data or information
No Spurious tuples

Works well where data is updated frequently but at the expense of information retrieval
26

Normalisation – Normal Forms Overview
1st Normal Form (1NF)
All tables are flat and hold atomic values
One cell, one value
One column one domain (data type)

2nd Normal Form (2NF)
1NF must hold true
All tables must have a valid PK and all values must be “functionally” dependent on the whole key
All non-key attributes (dependents) must provide a fact about the key (ie, have data relating to the key)
There are no partial dependencies
No attribute should be dependent on only a portion of the PK
At this point many anomalies will have been eliminated
27

2NF is only violated when a non-key attribute is a fact about a part of a composite key and not ALL of the key
Partial Dependency: It is only relevant for tables with composite keys (a key with several attributes)

Normalisation – Normal Forms Overview
3rd Normal Form (3NF)
2NF must hold true
No attribute must be transitively dependent on any other non-key (dependent) attribute
Sufficient to guard against anomalies

Boyce-Codd Normal Form (BCNF)
Commonly used in preference to 3rd normal form
Is a more specific extension of 3rd Normal Form
For any dependency, A → B, A must be a super-key
It is concerned with tables that contain multiple overlapping candidate keys

Other Normal Forms:
4th, and 5th Normal Forms and beyond

Normalisation
Summary the Steps
29

Normalisation – Steps
Eliminate repeating Groups.
Present data in a tabular format, where each cell has a single value and there are no repeating groups
Identify the Primary Key
The primary key must uniquely identify any given row
If necessary a surrogate key can be used if no key exists
Identify all Dependencies
These can be depicted using a dependency diagram to help visualise the relationship between values
Break apart the relation
Separate out the various functional dependencies:
Remove Partial Dependencies (PDs) into their own relation
Remove Transitive Dependencies (TDs) into their own relation

A Dependency Diagram depicts all the FDs within the relational schema and can help with the process

Student(studentID, studentName, address, mark, grade)
FD1: studentID -> {studentName, address, mark}
FD2: mark -> grade
Student(studentID, studentName, address, mark, grade)

If you have a ER/UML database diagram that has been correctly created for the database design then it will nearly always be automatically in 3rd NF. In which case the above steps can be used as a check of the proposed design if necessary.

Break apart the relation
Breaking apart relations based on FDs
For every functional dependency, write its determinant as a PK for a new table
Name the table to reflect its contents
Determinant: any attribute whose value determines other values within a row

Identify the dependent attributes
Dependent: any attribute whose value is dependent on a determinant
Determinant –> Dependent (ie, A –> B)

Remove the dependent attributes from the partial/transitive dependencies and place them in the new table with the determinant

Check the new tables and modified table to ensure that none contain other partial/transitive dependencies

Normalisation – Normal Forms Overview

1st NF
2nd NF
3rd NF
4th+ NF
Roriginal
Break apart multi-value attributes
Remove repeating groups
Identify all the FDs
Identify a suitable PK

Break apart partial dependencies

Roriginal
C1
C2
C3
C4
C5
C6

Break apart transitive dependencies

C2
C4
R2
C1
C3
R3
C1
C2
Roriginal
C5
C6

C1
C2
C3
C4
C5
C6

Break apart multi-value dependencies

C2
C4
R2

C1
C3
R3

C5
C6
R4

C1
C2
Roriginal
C5
32

Normalisation Steps:
Eliminate the repeating Groups.
Present data in a tabular format, where each cell has a single value and there are no repeating groups.
Eliminate repeating groups by replacing nulls, making sure that each repeating group attribute contains an appropriate data value relating to the original record. Involves just filling in missing values

Identify the Primary Key
The primary key must uniquely identify any given row
If necessary a new key can be composed if no key exists

Identify all Dependencies
Dependencies can be depicted using a dependency diagram to help visualise the relationship between values.
The dependency diagram:

Depicts ALL dependencies found within a given table structure
Is helpful in providing an overview of all the relationships among a tables attributes
Make it less likely that an important dependency will be overlooked
If you have a ER/UML database diagram that has been correctly created for the database design then it will nearly always be automatically in 3rd NF. In which case the above steps can be used as a check of the proposed design if necessary.

Normalisation – Normal Forms Overview

1st NF
2nd NF
3rd NF
4th+ NF
Roriginal
C1
C2
C3
Roriginal
C4
C5
C6
C1
C2
Roriginal
C5
C6
C1
C2
Roriginal
C5
C2
C4
R2
C1
C3
R3
C2
C4
R2
C1
C3
R3
C5
C6
R4

Rorig(c1, c2, c3, c4, c5, c6)
PK(c1, c2)
Rorig(c1, c2, c5, c6)
PK(c1, c2)
FK(c2) -> R2(c2)
FK(c1) -> R3(c1)
R2(c2, c4)
PK(c2)
R3(c1, c3)
PK(c1)
R4(c5, c6)
PK(c5)
Rorig(c1, c2, c5)
PK(c1, c2)
FK(c2) -> R2(c2)
FK(c1) -> R3(c1)
FK(c5) -> R4(c5)
C1
C2
C3
C4
C5
C6

Normalisation
Examples
34

Normalisation – Example 1
1st Normal Form
All tables should be “flat”
All occurrences of a record must contain the same number of fields
All values in a given column must be of the same data type (what does this mean?)
A value should NOT be composed of multiple values
Atomic values
student courses
Mary (1001) {CS145,CS229}
Joe (1002) {CS145,CS106}
… …

student studentID course
Mary 1001 CS145
Mary 1001 CS229
Joe 1002 CS145
Joe 1002 CS106

Not in 1st NF
1st NF Equivalent
http://web.stanford.edu/class/cs145/
35

A table in which the intersection of every column and record contains only one value. It prohibits nesting or repeating groups in table.

Normalisation – 2nd Normal Form
Is this in 2nd NF?
studentID courseID name postcode suburb courseName room mark
1001 CS145 Mary 5000 Adel FIT B01 D
1001 CS229 Mary 5000 Adel PF B01 C
1003 CS106 Jane 5092 Modbury Nth DBF B02 HD
1002 CS145 Joe 5092 Modbury FIT B01 P
1002 CS106 Joe 5092 Modbury DBF B02 HD

StudentEnrolments

studentID  name, postcode, suburb
courseID  courseName, room
Partial Dependencies
studentID, courseID  mark
36

Normalisation – 2nd Normal Form
Is this in 2nd NF?

courseID courseName room
CS145 FIT R01
CS229 PF R01
CS106 DBF R02

studentID name … mark
1001 Mary … D
1001 Mary … C
1003 Jane … HD
1002 Joe … P
1002 Joe … HD

2nd NF Equivalent
1st NF
2nd NF Equivalent
Students
Courses

StudentEnrolments
studentID name postcode suburb
1001 Mary 5000 Adel
1001 Mary 5000 Adel
1003 Jane 5092 Modbury Nth
1002 Joe 5092 Modbury
1002 Joe 5092 Modbury

Normalisation – 2nd Normal Form
Is this in 2nd NF?

1st NF
Original Schema:
Enrolment(studentID, courseID, name, suburb, postcode, courseName, room, mark)
PK(studentID, courseID)
FD: studentID –> name, suburb, postcode
FD: courseID –> courseName, room
FD: {studentID, courseID} –> mark
studentID courseID mark
1001 CS145 D
1001 CS229 C
1003 CS106 HD
1002 CS145 P
1002 CS106 HD

2nd NF Equivalent
StudentEnrolments
A Better Schema:
Enrolments(studentID, courseID, mark)
Students (studentID, name, postcode, suburb)
Courses (courseID, courseName, room)

StudentEnrolments
studentID name … mark
1001 Mary … D
1001 Mary … C
1003 Jane … HD
1002 Joe … P
1002 Joe … HD

Normalisation – 3rd Normal Form
Is this in 3rd Normal Form?

courseID courseName room
CS145 FIT R01
CS229 PF R01
CS106 DBF R02

2nd NF
2nd NF
Students
Courses
studentID name postcode suburb
1001 Mary 5000 Adel
1001 Mary 5000 Adel
1003 Jane 5092 Modbury Nth
1002 Joe 5092 Modbury
1002 Joe 5092 Modbury

studentID courseID mark
1001 CS145 D
1001 CS229 C
1003 CS106 HD
1002 CS145 P
1002 CS106 HD

2nd NF
StudentEnrolments
Are there any values that depend on an attribute other than the PK?

3rd NF

Normalisation – 3rd Normal Form
Is this in 3rd Normal Form?

CourseID CourseName Building Room
CS145 FIT Reid R1-01
CS229 PF Reid R2-01
CS106 DBF Planetarium P1-13

2nd NF
Courses
Are there any values that depend on an attribute other than the PK?

The Room functionally determines the Building the course is held in.

The Building can be identified by Room No as well as the CourseID. Neither Building nor Room are a primary key attribute.

CourseID → Room → Building, so there exists transitive dependency.

Normalisation – 3rd Normal Form
Is this in 3rd Normal Form?

CourseID CourseName Room
CS145 FIT R1-01
CS229 PF R2-01
CS106 DBF P1-13

3rd NF?
Courses
Room Building
R1-01 Reid
R2-01 Reid
P1-13 Planetarium

Rooms
3rd NF?
Now:
CourseID –> CourseName, Room
Room –> Building
All values depend on the key and nothing but the key!
A Better Schema:
Courses(CourseID, CourseName, Room)
PK(CourseID)

Rooms(Room, Building)
PK(Room)

Normalisation
Examples
42

Normalisation – 1st Normal Form
Is this in 1st Normal Form?

Multi-valued attribute/Repeating Group
43

Normalisation – 1st Normal Form
Is this in 1st Normal Form?

1st Normal Form:
Transform repeating values and groups of values into individual rows of data and duplicate any missing data as necessary

Normalisation – 2nd Normal Form
Is this in 2nd Normal Form?
A table of characters, the actor that played the character, the episode and date the character 1st aired

NO
Only the Character determines the Episode and originalAirDate
Character  Episode, originalAirDate
Actor = irrelevant (using the provided data)
45

Normalisation – 2nd Normal Form
Is this in 2nd Normal Form?
PK(Character, Actor) and PK(Character)

YES
The table only consists of a Composite PK – No partial dependencies can exist

YES
The table has a single value PK – No partial dependencies can exist

These Duplicate records are not needed
46

Normalisation – 2nd Normal Form
Is this in 2nd Normal Form?
PK(Character, Actor) and PK(Character)

YES
The table only consists of a Composite PK – No partial dependencies can exist
YES
The table has a single value PK – No partial dependencies can exist
The advantage is no duplicates

Normalisation – 3rd Normal Form

YES
The table only consists of a only a key and thus no transitive dependencies can exist
NO
The Episode determines the original AirDate and neither is the PK
Episode -> originalAirDate

Is this in 2nd Normal Form?
PK(Character, Actor) and PK(Character)
48

Normalisation – 3rd Normal Form
Is this in 3rd Normal Form?
PK(Character) and PK(Character)
YES
The table only consists of a only a key and one other attribute – no transitive dependencies can exist
Yes
The Episode always determines the same original AirDate and the episode is the PK. There is also only one non-key attribute so no transitive dependency can exist
Episode -> originalAirDate

Normalisation – 3rd Normal Form
These are all in 3rd Normal Form

Characters(Character, Actor)
PK(Character, Actor)

Episodes(Episode, OriginalAirDate)
PK(Episode)
CharacterFirstEpsiode(Character, Episode)
PK(Character)
A Better Schema
50

Note that although this design works fine for the Wikipedia data. What happens if we want wo record which episode an actor first played a character?
The original un-normalised form of the table would capture this. However, if we migrated the data to this new 3rd Normal form Schema, it would not be possible to add this information in at a later date. The database table schemas would need to be re-designed and the existing data migrated to the new tables.
50

Normalisation
Simpsons character database:

R(Character, Actor, Role, Episode, DateAired)
Our Original Simpsons DB

FD1 Character  Role, Episode
FD2 Episode  DateAired

Partial Dependency (PD)

Transitive Dependency (TD)
Take each Functional Dependency and place it into a new relation with ALL of its dependents

Normalisation
R(Character, Actor, Role, Episode, DateAired)
Our Simpsons DB
FD1 Character  Role, Episode
FD2 Episode  DateAired

Partial Dependency (PD)

Transitive Dependency (TD)
Take each Functional Dependency and place it into a new primary relation
R1(Character, Role, Episode, DateAired)
R2(Episode, DateAired)
R1(Character, Role, Episode)
R(Character, Actor)
Functional dependencies removed

Normalisation
R(Character, Actor, Role, Episode, DateAired)
Our Simpsons DB
R2(Episode, DateAired)
R1(Character, Role, Episode)
R(Character, Actor)
3NF

3NF
3NF
53

Normalisation – 2nd Normal Form
Is this in 2nd Normal Form?
PK(CharacterID) a surrogate key

YES
By definition a table with a surrogate/single column PK will be in 2nd Normal Form as there will be NO attribute(s) that rely on only part of the PK

[mark]+ = mark, grade

[studentID]+ = studentID, studentName,
address, mark, grade

[D]+ = D // no go

[DA]+ = D, A, B, C, F, H, E, G // good CK

[DB]+ = D, B, C, F, H, G, E, A, // good CK

[C1, C2]+ = C1, C2, C5, C6, C4, C3 // good closure

[C2]+ = C2, C4

// FDs?
PD1: studentID –> name, postcode, suburb,
PD2: courseID –> courseName, room
studentID, courseID –> mark
// PK? // studentID, courseID
// Closure?

SELECT DISTINCT sutdentID, name
FD: studentID ->> name postcode, suburb, // 2nF

CharacterVoice actor(s)Character’s roleEpisode debutOriginal air
date
Homer SimpsonDan Castellaneta[2]Husband of Marge; father of
Bart, Lisa, and Maggie.
“Good Night” (The Simpsons shorts)[3]19/04/1987
Marge SimpsonJulie Kavner[2]Wife of Homer; mother of Bart,
Lisa, and Maggie.
“Good Night”[3]19/04/1987
Bart SimpsonNancy Cartwright[2]Eldest child and only son of
Homer and Marge; brother of
Lisa and Maggie.
“Good Night”[3]19/04/1987
Lisa SimpsonYeardley Smith[2]Middle child and eldest
daughter of Homer and Marge;
sister of Bart and Maggie.
“Good Night”[3]19/04/1987
Liz Georges
Gábor Csupó
Harry Shearer
Yeardley Smith
Nancy Cartwright
Elizabeth Taylor
James Earl Jones
Jodie Foster
AkiraGeorge Takei,[4]
Hank Azaria[2]
Waiter at The Happy Sumo;[5]
karate teacher.[6]
“One Fish, Two Fish, Blowfish, Blue Fish”24/01/1991
Maggie SimpsonYoungest child and daughter of
Homer and Marge; sister of Bart
and Lisa.
“Good Night”[3]19/04/1987

CharacterVoice actor(s)Character’s roleEpisode debutOriginal air date
Homer SimpsonDan Castellaneta
Husband of Marge;
father of Bart, Lisa, and
Maggie.Good Night19/04/1987
Marge SimpsonJulie Kavner
Wife of Homer; mother
of Bart, Lisa, and
Maggie.Good Night19/04/1987
Bart SimpsonNancy Cartwright
Eldest child and only
son of Homer and
Marge; brother of Lisa
and Maggie.Good Night19/04/1987
Lisa SimpsonYeardley Smith
Middle child and
eldest daughter of
Homer and Marge;
sister of Bart and
Maggie.
Good Night19/04/1987
Maggie SimpsonLiz Georges
Youngest child and
daughter of Homer and
Marge; sister of Bart
and Lisa.Good Night19/04/1987
Maggie SimpsonGábor Csupó…Good Night19/04/1987
Maggie SimpsonHarry Shearer…Good Night19/04/1987
Maggie SimpsonYeardley Smith…Good Night19/04/1987
Maggie SimpsonNancy Cartwright…Good Night19/04/1987
Maggie SimpsonElizabeth Taylor…Good Night19/04/1987
Maggie SimpsonJames Earl Jones…Good Night19/04/1987
Maggie SimpsonJodie Foster…Good Night19/04/1987
AkiraGeorge Takei
Waiter at The Happy
Sumo;[5] karate
teacher.[6]One Fish, Two Fish, Blowfish24/01/1991
AkiraHank Azaria
Waiter at The Happy
Sumo;[5] karate
teacher.[6]One Fish, Two Fish, Blowfish24/01/1991

CharacterActorEpisodeoriginalAirDate
Homer SimpsonDan CastellanetaGood Night19/04/1987
Marge SimpsonJulie KavnerGood Night19/04/1987
Bart SimpsonNancy CartwrightGood Night19/04/1987
Lisa SimpsonYeardley SmithGood Night19/04/1987
Maggie SimpsonLiz GeorgesGood Night19/04/1987
Maggie SimpsonGábor CsupóGood Night19/04/1987
Maggie SimpsonHarry ShearerGood Night19/04/1987
Maggie SimpsonYeardley SmithGood Night19/04/1987
Maggie SimpsonNancy CartwrightGood Night19/04/1987
Maggie SimpsonElizabeth TaylorGood Night19/04/1987
Maggie SimpsonJames Earl JonesGood Night19/04/1987
Maggie SimpsonJodie FosterGood Night19/04/1987
AkiraGeorge TakeiOne Fish, Two Fish, Blowfish24/01/1991
AkiraHank AzariaOne Fish, Two Fish, Blowfish24/01/1991

CharacterActor
Homer SimpsonDan Castellaneta
Marge SimpsonJulie Kavner
Bart SimpsonNancy Cartwright
Lisa SimpsonYeardley Smith
Maggie SimpsonLiz Georges
Maggie SimpsonGábor Csupó
Maggie SimpsonHarry Shearer
Maggie SimpsonYeardley Smith
Maggie SimpsonNancy Cartwright
Maggie SimpsonElizabeth Taylor
Maggie SimpsonJames Earl Jones
Maggie SimpsonJodie Foster
AkiraGeorge Takei
AkiraHank Azaria

CharacterEpisodeoriginalAirDate
Homer SimpsonGood Night19/04/1987
Marge SimpsonGood Night19/04/1987
Bart SimpsonGood Night19/04/1987
Lisa SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
Maggie SimpsonGood Night19/04/1987
AkiraOne Fish, Two Fish, Blowfish24/01/1991
AkiraOne Fish, Two Fish, Blowfish24/01/1991

CharacterEpisode
Homer SimpsonGood Night
Marge SimpsonGood Night
Bart SimpsonGood Night
Lisa SimpsonGood Night
Maggie SimpsonGood Night
AkiraOne Fish, Two Fish, Blowfish

EpisodeoriginalAirDate
Good Night19/04/1987
Good Night19/04/1987
Good Night19/04/1987
Good Night19/04/1987
Good Night19/04/1987
One Fish, Two Fish, Blowfish24/01/1991

EpisodeoriginalAirDate
Good Night19/04/1987
One Fish, Two Fish, Blowfish24/01/1991

CharacterIDCharacteractorsEpisodeoriginalAirDate
1Homer SimpsonDan CastellanetaGood Night19/04/1987
2Marge SimpsonJulie KavnerGood Night19/04/1987
3Bart SimpsonNancy CartwrightGood Night19/04/1987
4Lisa SimpsonYeardley SmithGood Night19/04/1987
5Maggie SimpsonLiz GeorgesGood Night19/04/1987
6Maggie SimpsonGábor CsupóGood Night19/04/1987
7Maggie SimpsonHarry ShearerGood Night19/04/1987
8Maggie SimpsonYeardley SmithGood Night19/04/1987
9Maggie SimpsonNancy CartwrightGood Night19/04/1987
10Maggie SimpsonElizabeth TaylorGood Night19/04/1987
11Maggie SimpsonJames Earl JonesGood Night19/04/1987
12Maggie SimpsonJodie FosterGood Night19/04/1987
13AkiraGeorge TakeiOne Fish, Two Fish, Blowfish24/01/1991
14AkiraHank AzariaOne Fish, Two Fish, Blowfish24/01/1991

/docProps/thumbnail.jpeg

Related Posts