Module 5: ch 7: normalization *EXAM*

how to fix 2NF violation in univesity database

-goal is to eliminate all dependents, so have three tables (enrollment for enrGrade, stdno, offerno)

identifying FDs what kind of relationships

1-M relationship from child to parent

2NF violations in university Database

1. Combined PK (StdNo,OfferNo) columns are dependent on subset of keys and not whole key -All FDs w/ either key alone in LHS violate 2NF

identifying FDs: difficult identification when 2

1. LHS is not a PK/CK in converted table 2. LHS is part of a combined primary/candidate key

normalization: apply normal forms

1. identify FDs 2. determine whether FD meets normal form 3. split table to meet normal form if there is a violation

modification anomalies

1. unexpected side effect 2. insert, modify, and delete more data than desired 3. caused by excessive redundancies

Relationship of normal forms

1NF -> 2NF -> 3NF/BCNF

BCNF define

Every determinant is a candidate key -special cases not covered by 3NF -use simple synthesis procedure

identifying FDs: must ensure minimality of

LHS

functional dependency definition

Notation X --> Y X: functionally determines Y X: left (LHS) or determinant Y: RHS/determinee/dependent -similar to candidate keys

Identifying Fds :easy identification

Pks and Cks result from ERD conversion

stdNo, OfferNo -> EnrGrade OfferNo, StdEmail --> enrGrade StdNo --> stdEmail StdEmail --> StdNO

Table1 (OfferNo,StdNo, EnrNo) FK StdNo Ref Table 2 Table 2 (StdNo, StdEmail) UNIQUE StdEmail

StdNo, AdvisorNo --> Status StdNo, Major --> Status AdvisorNo --> Major

Table1(StdNo, AdvisorNo, Status) FK AdvisorNo Ref Table2 Table2(AdvisorNo, Major)

combined definition of 2NF/3NF: every non key column depends on

all candidate keys, whole candidate keys

functional dependency definition: for each X value there is

at most one Y value

combined definition of 2NF/3NF: key column

candidate key/part of candidate key

update example

change multiple rows to change one fact -must change two rows to update stdClass of S1

functional dependencies

constraint on(and within) a table -many to one mapping from a set of attributed to another in a table

3NF violation in university table 2 -how to fix

courseNo --> crsDesc both are nonkey -split table again

deletion example

deleting a row causes other facts to disappear -deleting enrollment of S2 in offering O3 causes loss of info about offering O3 and C3

BCNF rule

every determinant is a candidate key (not part of one, must be whole) the only one that fit was stdno, offerno-> enrgrade

2NF

every nonkey column depends on all candidate keys, not a subset of candidate keys

3NF

every nonkey column depends only on candidate keys, not on nonkey columns

example X=Offerterm Y = StdNo

for each x, there must be one Y for each offerterm there must be one stdNo -does not apply so this is not a FD -X = StdNo and Y =StdClass is one

insertion example

insert more columns than desired -must know PK (StdNo & OfferNo) to insert a new course

modification anomaly examples

insertion update delete

well structured table has

minimal amount of redundancy -allow modifications with few chance of inconsistencies

functional dependencies value

neutral *like FK and Pk -not specific values

3NF alt formulation

no transitive FDs A-->B B-->C then A---> C

3NF violation

nonkey--> nonkey

normalization: apply

normal forms

what do you need to start with

one big universal table

modification anomalies- strive for

one fact in place

normalization

process of designing a database with well structured tables by eliminating redundancy causing anomalies

normalization is the process of

process removing unwanted redundancies

FD Diagram and lists

start with PK and everything that seems connected (one to many relationships or one to one)

1NF

starting point for most relational DBMS -no repeating groups -flat rows

falsification requires

two rows with the same LHS but different RHS