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

Basics

• Process of efficiently organizing data in a

database.

• Goals
  • Eliminate redundant data
  • Ensure data dependency sensibility

Brief Description of Normal Forms

• 1 st^ Normal Form
  • No repeating groups
• 2 nd^ Normal Form
  • Each column depends entirely on the primary key.
• 3 rd^ Normal Form
  • Each column depends directly on the primary key.
• 4 th^ Normal Form
  • Isolate independent multiple relationships.
• 5 th^ Normal Form
  • Isolate Semantically related multiple relationships.
• Boyce-Codd Normal Form
  • Non Trivial dependencies between candidate keys.
• Optimal Normal Form
  • Limited to simple Facts
• Domain-Key Normal Form
  • No modification anomolies

Additional Information

• 1 st^ Normal Form
  • Addresses the structure of an isolated table.
• 2 nd^ , 3rd^ and Boyce Codd Normal Form
  • Addresses 1-1 and 1-many relationships.
• 4 th^ and 5th^ Normal Form
  • Many-Many relationships.
• These forms are cumulative.

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

Additional Tips

• Records are free, new fields are expensive.
• Know when data requires duplication
• Use referential integrity
• The higher normal form generally results in

faster data retrieval.

Examples of Bad Databases

• 0015 CS166 10:30 MH226 SID Class Time Location Prof_ID
• 1205 CS146 12:00 MH225
• 0025 CS166 1:30 MH222
• 0015 CS151 8:00 MH222
• 2531 CS156 9:00 MH422
• 1205 CS120 10:30 DH251
• 2522 CS046 8:00 MH224
• 1523 CS140 5:00 MH422
• 0015 CS140 3:00 MH422 34215 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.

Overall topics

• Normalization helps organization and speed of

organizing a database, which can help a

company produce a database system that is

quick and easily accessible.

• The higher the normal form, the less chance

anomalies will arise.

• In case of performance issues, de-

normalization can be done in order to increase

performance.

Final Motto:

Strive for Single Themed Tables.