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 A1, A2, . . . An then they must also agree on all other attributes B1, B2, . . . , Bm. Where the attributes A1, A2, . . . An 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 FirstName LastName Mailbox 132 Jack Smith 5480 184 Sally Green 6113 651 Bob Heart 1245 id Classes 132 CS102 132 CS150 184 CS106 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 CS203 Sadovnik CS202 Xia CS301 Xia CS406 Sadovnik LastName Phone Liew x5537 Sadovnik x5741 Xia x5415

• Prof. Pfaff.

DBMS Howto Part 2

General Rules of Design for Database Usage

1

Never create new tables for new entities. So you would not have a new table for every new user, you would create a user table and add every new user to that table identified with a unique id.

2

Never create new arbitrary columns for a new entity. In a table, growth

• nly occurs through the addition of tuples.
• Prof. Pfaff.

DBMS Howto Part 2

What did you do?

• Prof. Pfaff.

DBMS Howto Part 2