Download - Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray [email protected]
![Page 2: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/2.jpg)
The Normalization Question How Many Tables?
Should we store these two tables as they are, or should we combine them into one table in our new database?
![Page 3: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/3.jpg)
Let’s Do Another Example
• What Form?
• http://www.phpbuilder.com/columns/barry20000731.php3
![Page 4: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/4.jpg)
1NF• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
![Page 5: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/5.jpg)
2NF• Create separate tables for sets of values that apply to multiple
records.• Relate these tables with a foreign key.
![Page 6: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/6.jpg)
3NF
• Eliminate fields that do not depend on the key.
![Page 7: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/7.jpg)
BCNF
• Are all determinant keys also candidate keys?
![Page 8: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/8.jpg)
The Question:Normalize or Denormalize
![Page 9: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/9.jpg)
Type of Database
• Updateable database or read-only database?
• If updateable database, we normally want tables in 3NF or BCNF
• If read-only database, we may not use normalized tables
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 10: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/10.jpg)
Normalization:Advantages and Disadvantages
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 11: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/11.jpg)
Choosing Not to Normalize
• Normalization is used to control anomalies from functional dependencies
• There are times when normalization is not desirable
• The classic example is ZIP codes:– ZIP codes almost never change– Any anomalies are likely to be caught by normal
business practices– Not having to use two tables speeds up application
processing
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 12: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/12.jpg)
Normalize ZipCode
• Is this table in 3NF?
![Page 13: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/13.jpg)
Normalize ZipCode
Functional Dependencies:
CustomerID->(Name, Street, City, State, ZipPostalCode)
Zip->(City, State)
• Would you normalize this?
![Page 14: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/14.jpg)
Normalize ZipCode
CUSTOMER(CustomerNumber, CustomerName, CustomerAddress, Zip, ContactName)
ZIP_CODE(Zip, City, State)
• Is this practical? KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hallvvvvv
![Page 15: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/15.jpg)
Designing Read-Only Databases
![Page 16: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/16.jpg)
Read-Only Databases
• Read-only databases are non-operational databases using data extracted from operational databases
• They are used for querying, reporting and data mining applications
• They are never updated (in the operational database sense – they may have new data imported form time-to-time)
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 17: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/17.jpg)
Denormalization
• For read-only databases, normalization is seldom an advantage– Application processing speed is more
important
• Denormalization is the joining of data in normalized tables prior to storing the data
• The data is then stored in non-normalized tables
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 18: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/18.jpg)
Denormalization
• Normalizing relations (or breaking them apart into many component relations) may significantly increase the complexity of the data structure
• The question is one of balance– Trading complexity for modification problems
• There are situations where denormalized relations are preferred – Google uses denormalized tables
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 19: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/19.jpg)
Normalized Tables
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 20: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/20.jpg)
Denormalizing the Data
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 21: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/21.jpg)
Customized Tables
• Read-only databases are often designed with many copies of the same data, but with each copy customized for a specific application
• Consider the PRODUCT table:
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall
![Page 22: Database Lecture Notes Normalization 3 – Denormalization Dr. Meg Murray mcmurray@kennesaw.edu](https://reader036.vdocuments.us/reader036/viewer/2022062320/56649d765503460f94a578e1/html5/thumbnails/22.jpg)
Customized Tables
PRODUCT_PURCHASING (SKU, SKU_Description, VendorNumber, VendorName, VendorContact_1, VendorContact_2, VendorStreet, VendorCity, VendorState, VendorZip)
PRODUCT_USAGE (SKU, SKU_Description, QuantitySoldPastYear, QuantitySoldPastQuarter, QuantitySoldPastMonth)
PRODUCT_WEB (SKU, DetailPicture, ThumbnailPicture, MarketingShortDescription, MarketingLongDescription, PartColor)
PRODUCT_INVENTORY (SKU, PartNumber, SKU_Description, UnitsCode, BinNumber, ProductionKeyCode)
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)© 2008 Pearson Prentice Hall