-Database Systems.pptVIP

Database Systems – SQL Database Systems – SQL Database Systems – SQL Database Systems – SQL Database Systems – SQL Database Systems – SQL Database Systems – SQL Database Systems – SQL Database Systems – SQL * * NORMALIZATION How you organize your data has a profound effect on how efficiently the data is stored, updated and selected. Traditionally, there are 5 forms of normalization. Neatly named 1st, 2nd, 3rd, 4th, and 5th normal form. In practice, only 1st through 3rd are used. 4th and 5th normal form are too restrictive for real use. Even 3rd normal form is too restrictive, and therefore we will learn Jeff Normal Form, which is a less restrictive form of 3rd normal form. In all normal forms each row of a table is uniquely identified by a primary key. A primary key may be a single field or a combination of keys. NORMALIZATION 1st Normal Form The goal of 1st Normal Form is to break data into the smallest units possible. Observe the following table (888) YAN-KEES 1977 Championship Lane, Bronx, NY 11002 Thurman Munson (800) YAN-KEES 26 World Series Drive, NYC, NY 11152 Derek Jeter (555) 555-1212 7 Mahotma Way, Fletchville, NY 11572 Jeff Salvage Phone Address Name Therefore, any field that is non-atomic should be broken into separate fields. NY NY NY State Bronx NYC Fletchville City 1977 Championship Lane 26 World Series Drive 7 Mahotma Way Address Thurman Derek Jeff FirstName (888) YAN-KEES 11002 Munson (800) YAN-KEES 11152 Jeter (555) 555-1212 11572 Salvage Phone Zip LastName NORMALIZATION 1st Normal Form Additionally, data should not be stored with repetitious groups of fields: Observe the following table Flash Gordon Bret Meyers Phillies Mike Mussina Mariano Rivera Yankees Pitcher2 Pitcher1 Team When data is set up in this manner unused fields are wasted. In addition, a set number of values for the repeated field is hard coded into the table instead of allowing an arbitrary number of values. Instead you should create a record for each value a