Relational TablesRelational tables have many advantages, butIf improperly structured, table may suffer from:Poor performanceInconsistencyRedundancyDifficult maintenanceThis is common because most users do not understand the concepts Normal Forms in relational tables.Tables in Non-normal Formrepeat columns, “dependent” data, empty cells by designNormal Forms Are Good Because:It reduces total data storageChanging values in the database is easierIt “insulates” information – it is easier to retain important dataMany operations are easier to codeTables in 1st normal form when there are no repeat columnsAdvantages: easy to code queries (can look in only one column)Disadvantages: slow searches, excess storage, cumbersome maintenance2nd Normal Forms in Relational Tables2NF if: it is in 1NF and if every non-key attribute is functionally dependent on the primary keyWhat is a key?An item or set of items that may be used to uniquely identify every rowWhat 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)KeysItem(s) that uniquely identify a rowSTATE can be a key, but not REGION, SIZE, or POPULATIONSometimes we need >1 column to form a key, e.g., Parcel-ID and Own-ID together may form a keyKeysItem(s) that uniquely identify a rowFunctional DependencyKnowing the value of an item (or items) means you know the values of other items in the rowe.g., if we know the person’s name, then we know the addressIn our example, if we know the Parcel-ID, we know the Alderman, Township name, and other Township attributes:Parcel-ID - > Alderman Parcel-ID - > Thall_addParcel-ID - > Tship-IDParcel-ID - > Tship_nameMoving from First Normal Form (1NF to Second Normal Form (2NF), we need to:Identify functional dependenciesPlace in separate tables, one key per table3rd Normal Forms in Relational TablesRemove transitive functional dependenciesA transitive functional dependency is when A -> B (if we know A, then we know B)andB -> C (if we know B, then we know C)SoA -> 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 themIn our example, one transitive functional dependency:Parcel-ID -> Tship-ID, AldermanTship-ID -> Tship_name, Thall_addBad Things in Relational Tables:Repeat (or similar) variables e.g., parcel #, owner 1, owner2, owner3, owner 4Multiple dependencies per record e.g., owner name, house#, street, city, county, zipcode, state, countryRepeat recordsMany blank cellsNormal Forms SummaryNo 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 storageUpdating is easierIt “insulates” information – it is easier to retain important dataMany operations are easier to codeWhat are the functional dependencies?What are the candidate