Database Management Systems (DBMS) Prof. Pfaff. Lafayette College - PowerPoint PPT Presentation

Database Management Systems (DBMS) Prof. Pfaff. Lafayette College February 22, 2018 Prof. Pfaff. DBMS Howto Part 2

Functional Dependencies If some number of tuples agree on all attributes A 1 , A 2 , . . . A n then they must also agree on all other attributes B 1 , B 2 , . . . , B m . Where the attributes A 1 , A 2 , . . . A n can be considered a key for this presentation. title year length genre startname star wars 1977 124 scifi Fisher star wars 1977 124 scifi Ford star wars 1977 124 scifi Hamill This is a Functional Dependency: title, year → length, genre This is not a Functional Dependency: title, year → starname Prof. Pfaff. DBMS Howto Part 2

Avoiding Design Anomalies Redundancy Repeating unnecessary data. Update Anomalies Updating the same information in one place but leaving another unchanged. Deletion Anomalies If a set of values becomes empty, we may loose information someplace else. We can do decompositions of the relations to remove the potential for these anomalies, these allow us to place the database into different normal forms. Normal Form :a standard structure or format in which all propositions in a (usually symbolic) language can be expressed. Prof. Pfaff. DBMS Howto Part 2

First Normal Form id FirstName LastName Classes Mailbox 132 Jack Smith CS102, CS150 5480 184 Sally Green CS106 6113 651 Bob Heart CS202, CS203 1245 A relation is in 1st NF if and only if the domain of each attribute contains only atomic values. Prof. Pfaff. DBMS Howto Part 2

First Normal Form id FirstName LastName Classes Mailbox 132 Jack Smith CS102, CS150 5480 184 Sally Green CS106 6113 651 Bob Heart CS202, CS203 1245 A relation is in 1st NF if and only if the domain of each attribute contains only atomic values. id FirstName LastName Mailbox Classes 132 Jack Smith 5480 CS102 132 Jack Smith 5480 CS150 184 Sally Green 6113 CS106 651 Bob Heart 1245 CS202 651 Bob Heart 1245 CS203 Prof. Pfaff. DBMS Howto Part 2

Second Normal Form id FirstName LastName Mailbox Classes 132 Jack Smith 5480 CS102 132 Jack Smith 5480 CS150 184 Sally Green 6113 CS106 651 Bob Heart 1245 CS202 651 Bob Heart 1245 CS203 A relation is in 1st NF; no non-prime attribute is dependent on any proper subset of any candidate key of a relation. Prof. Pfaff. DBMS Howto Part 2

Second Normal Form id FirstName LastName Mailbox Classes 132 Jack Smith 5480 CS102 132 Jack Smith 5480 CS150 184 Sally Green 6113 CS106 651 Bob Heart 1245 CS202 651 Bob Heart 1245 CS203 A relation is in 1st NF; no non-prime attribute is dependent on any proper subset of any candidate key of a relation. id Classes id FirstName LastName Mailbox 132 CS102 132 Jack Smith 5480 132 CS150 184 Sally Green 6113 184 CS106 651 Bob Heart 1245 651 CS202 651 CS203 Prof. Pfaff. DBMS Howto Part 2

Third Normal Form id LastName Phone CS150 Liew x5537 CS203 Sadovnik x5741 CS202 Xia x5415 CS301 Xia x5415 CS406 Sadovnik x5741 A relation is in 2nd NF; every non-prime attribute of R is non-transitively dependent on every key of R. Prof. Pfaff. DBMS Howto Part 2

Third Normal Form id LastName Phone CS150 Liew x5537 CS203 Sadovnik x5741 CS202 Xia x5415 CS301 Xia x5415 CS406 Sadovnik x5741 A relation is in 2nd NF; every non-prime attribute of R is non-transitively dependent on every key of R. id LastName CS150 Liew LastName Phone CS203 Sadovnik Liew x5537 CS202 Xia Sadovnik x5741 CS301 Xia Xia x5415 CS406 Sadovnik Prof. Pfaff. DBMS Howto Part 2

General Rules of Design for Database Usage Never create new tables for new entities. So you would not have a new 1 table for every new user, you would create a user table and add every new user to that table identified with a unique id. Never create new arbitrary columns for a new entity. In a table, growth 2 only occurs through the addition of tuples. Prof. Pfaff. DBMS Howto Part 2

What did you do? Prof. Pfaff. DBMS Howto Part 2