Lecture3 - Lecture 3 on Data Normalization Study the...

Info iconThis preview shows pages 1–13. Sign up to view the full content.

View Full Document Right Arrow Icon
01/11/12 Study the methods of first, second, third, Boyce-Codd, fourth and fifth normal form for relational database design, in order to eliminate data redundancy and update abnormality. Lecture 3 on Data Normalization
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Normalization Theory Refine database design to eliminate abnormalities (irregularities) of manipulating database
Background image of page 2
01/11/12 1NF, 2NF and 3NF Built around the concept of normal forms Normal form: Contains atomic values only All normalized relations are in 1NF 2NF is the subset of 1NF, 3NF is the subset of 2NF and so on 3NF is more desirable than 2NF, 2NF is more desirable than 1NF
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 BCNF, 4NF and 5NF(PJNF) Boyce-Codd Normal Form A stronger form of 3NF Every BCNF is also 3NF, but some 3NF are not BCNF 4NF and 5NF Defined recently Deal with multi-valued dependency (MVD) and join dependency (JD)
Background image of page 4
01/11/12 Relationship between Normal Forms Universe of relations 1NF relations 2NF relations 3NF relations BCNF relations 4NF relations 5NF/PJNF relations
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 First Normal Form A relation is in 1NF if each attribute contains only one value (not a set of values) The primary key (PK) can not be null
Background image of page 6
01/11/12 Unnormal Form S# S-name Enrollments S1 Brown C1 Math C2 Chem C3 Phys S2 Smith C2 Chem C3 Phys C4 Math S3 Brown C2 Chem C3 Phys Elements in the domain Enrollments are not atomic Could be split into two domains: C# and C- Name Relation STUDENT-B
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 First Normal Form Enrollments is split into C# and C-Name Use S# and C# as a compound PK A student may attend several courses and a course may have several students So S# and C# has a m:n mapping S# S-Name C# C-Name S1 Brown C1 Math S1 Brown C2 Chem S1 Brown C3 Phys S2 Smith C2 Chem S2 Smith C3 Phys S2 Smith C4 Math S3 Brown C2 Chem S3 Brown C3 Phys Relation STUDENT-B
Background image of page 8
01/11/12 Functional Dependency (FD) Attribute Y of relation R is functionally dependent on attribute X of R each value of X is associated with exactly one value of Y Denoted by X Y In the relation STUDENT-B: S# S-Name C# C-Name S#, C# 0
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Anomalies using 1NF 1NF relations require less complicated application to operate as opposed to unnormalized relations Anomalies in insert: Since PK is composed of C# and S#, both details of student and course must be known before inserting a entry Eg: to add a course, at least one student is enrolled
Background image of page 10
01/11/12 Anomalies using 1NF Anomalies in delete: If all students attending a particular course are deleted, the course will not be found in the database Anomalies in update: Redundancy of S-Name and C-Name Increase storage space and effort to modify data item If a course is modified, all tuples containing that course must be updated
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
01/11/12 Second Normal Form A relation is in 2NF if it is in 1NF and every non-
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 01/11/2012 for the course CS CS3462 taught by Professor Drlee during the Spring '11 term at City University of Hong Kong.

Page1 / 42

Lecture3 - Lecture 3 on Data Normalization Study the...

This preview shows document pages 1 - 13. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online