Efficiently Organizing Data - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Efficiently Organizing Data - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity! Normalization Docsity.com Basics • Process of efficiently organizing data in a database. • Goals – Eliminate redundant data – Ensure data dependency sensibility 2 Docsity.com Additional Information • 1st Normal Form – Addresses the structure of an isolated table. • 2nd, 3rd and Boyce Codd Normal Form – Addresses 1-1 and 1-many relationships. • 4th and 5th Normal Form – Many-Many relationships. • These forms are cumulative. 5 Docsity.com Anomalies • Insertion Anomalies – Duplication of data enforced – impossible due to entity integrity • Deletion Anomalies – Leads to undesired loss of data. • Modification Anomalies – Modification of multiple rows can lead to inconsistencies. • Databases that are sufficiently normalized can reduce anomalies from occurring. 6 Docsity.com Tips • Make a table for each list. • Use non-meaningful primary keys • Eliminate Repeating Groups • Eliminate Columns not dependent on Primary Key • Each table should have an identifier • Should only store data for a single type of entity. • Avoid null columns 7 Docsity.com Examples of Bad Databases SID Class Time Location Prof_ID 0015 CS166 10:30 MH226 34215 1205 CS146 12:00 MH225 25123 0025 CS166 1:30 MH222 34215 0015 CS151 8:00 MH222 24215 2531 CS156 9:00 MH422 25124 1205 CS120 10:30 DH251 12412 2522 CS046 8:00 MH224 34215 1523 CS140 5:00 MH422 25123 0015 CS140 3:00 MH422 34215 10 Docsity.com Problems with the database • In a large scale database, information would be repeated numerous times, resulting in redundant data • Retrieval of data would be difficult and long • Index creation would be difficult. 11 Docsity.com In Practice • Many databases are “de-normalized” to some degree. • This is due to performance issues. • It may require fewer joins and result in faster retrievals. • However, before doing “de-normalization” performance issues must exist and de-normalization must dramatically improve it before introducing a suboptimal design • A de-normalized table can be harder to update. 12 Docsity.com