Relational Tables Relational tables have many advantages but If improperly structured table may suffer from Poor performance Inconsistency Redundancy Difficult maintenance This is common because most users do not understand the concepts Normal Forms in relational tables Tables in Non normal Form repeat columns dependent data empty cells by design Normal Forms Are Good Because It reduces total data storage Changing values in the database is easier It insulates information it is easier to retain important data Many operations are easier to code Tables in 1st normal form when there are no repeat columns Advantages easy to code queries can look in only one column Disadvantages slow searches excess storage cumbersome maintenance 2nd Normal Forms in Relational Tables 2NF if it is in 1NF and if every non key attribute is functionally dependent on the primary key What is a key An item or set of items that may be used to uniquely identify every row What is functional dependency If you know an item or items for a row then you automatically know a second set of items for the row this means the second set of items is functionally dependent on the item or items Keys Item s that uniquely identify a row STATE can be a key but not REGION SIZE or POPULATION Keys Item s that uniquely identify a row Sometimes we need 1 column to form a key e g ParcelID and Own ID together may form a key Functional Dependency Knowing the value of an item or items means you know the values of other items in the row e g if we know the person s name then we know the address In our example if we know the Parcel ID we know the Alderman Township name and other Township attributes Parcel ID Alderman Parcel ID Tship ID Parcel ID Tship name Parcel ID Thall add Moving from First Normal Form 1NF to Second Normal Form 2NF we need to Identify functional dependencies Place in separate tables one key per table 3rd Normal Forms in Relational Tables Remove transitive functional dependencies A transitive functional dependency is when A B if we know A then we know B and B C if we know B then we know C So A C if we know A then we know C To be in 3NF we must identify all transitive functional dependencies and remove them typically by splitting the table s that contain them In our example one transitive functional dependency Parcel ID Tship ID Alderman Tship ID Tship name Thall add Bad Things in Relational Tables Repeat or similar variables e g parcel owner 1 owner2 owner3 owner 4 Multiple dependencies per record e g owner name house street city county zipcode state country Repeat records Many blank cells Normal Forms Summary No repeat columns create new records such that there are multiple records per entry Split the tables so that all non key attributes depend on a primary key Split tables further if there are transitive functional dependencies This results in tables with a single primary key per table Normal Forms Are Good Because Reduce total data storage Updating is easier It insulates information it is easier to retain important data Many operations are easier to code What are the functional dependencies What are the candidate keys