Chapter 5 Notes

Normalization is usually done in stages with each

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: s rules to the types of information which can be stored in a table. Normal Form Edgar F. Codd originally established three normal forms: 1NF, 2NF and 3NF. There are now others that are generally accepted, but 3NF is widely considered to be sufficient for most applications. Most tables when reaching 3NF are also in BCNF (Boyce-Codd Normal Form). Table 1 Title Author1 Author 2 ISBN Subject Pages Publisher Database System Concepts Abraham Silberschatz Henry F. Korth 0072958863 MySQL, Computers 1168 McGraw-Hill Operating System Concepts Abraham Silberschatz Henry F. Korth 0471694665 Computers 944 McGraw-Hill First Normal Form In our Table 1, we have two violations of First Normal Form: First, we have more than one author field, Second, our subject field contains more than one piece of information. With more than one value in a single field, it would be very difficult to search for all books on a given subject. First Normal Table Table 2 Title Author ISBN Subject Pages Publisher Database System Concepts Abraham Silberschatz 0072958863 MySQL 1168 McGraw-Hill Database System Concepts Henry F. Korth 0072958863 Computers 1168 McGraw-Hill Operating System Concepts Henry F. Korth 0471694665 Computers 944 McGraw-Hill Operating System Concepts Abraham Silberschatz 0471694665 Computers 944 McGraw-Hill We now have two rows for a single book. Additionally, we would be violating the Second Normal Form… A better solution to our problem would be to separate the data into separate tables- an Author table and a Subject table to store our information, removing that information from the Book table: Second Normal Form As the First Normal Form deals with redundancy of data across a horizontal row, Second Normal Form (or 2NF) deals with redundancy of data in vertical columns. As stated earlier, the normal forms are progressive, so to achieve Second Normal Form, the tables must already be in First Normal Form. The Book Table will be used for the 2NF example 2NF Table Publis he r Table Publisher_ID Publisher Name 1 McGraw-Hill Bo o k Table ISBN Title Pages Publisher_ID 0072958863 Database System Concepts 1168 1 0471694665 Operating System Concepts 944 1 2NF Here we have a one-to-many relationship between the book table and the publisher. A book has only one publisher, and a publisher will publish many books. When we have a one-tomany relationship, we place a foreign key in the Book Table, pointing to the primary key of the Publisher Table. The other requirement for Second Normal Form is that you cannot have any data in a table with a composite key that does not relate to all portions of the composite key. Third Normal Form Third normal form (3NF) requires that there are no functional dependencies of non-key attributes on something other than a candidate key. A table is in 3NF if all of the non-primary key attributes are mutually independent There should not be transitive dependencies S ubje c t Table Subject_ID Subject 1 MySQL 2 Computers Autho r Table Author_ID 1 Silberschatz Abraham 2 Bo o k Table Last Name Korth ISBN Title Pages Publisher 0072958863 Database System Concepts 1168 McGraw-Hill 0471694665 Operating System Concepts 944 McGraw-Hill First Name Henry Creating a database… Step1: Creating a Data Model Step 2: Defining an Attribute Model Step 3: Identify & Capture Business Rules Step 4: Define Physical Model Step 5: Review Final Design Principles of Information Systems, Fifth Edition Step1: Creating a Data Model Identify Candidate Entities...
View Full Document

Ask a homework question - tutors are online