1 / 11

SQL Normalization Database Design

SQL Normalization Database Design. Lecture 5. SQL Normalization Database Design. 1 st Normal Form 2 nd Normal Form 3 rd Normal Form Boyce-Codd Normal Form (BCNF) 4 th Normal Form 5 th Normal Form 6 th Normal Form Domain Key Normal Form (DKNF).

gibson
Download Presentation

SQL Normalization Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL NormalizationDatabase Design Lecture 5

  2. SQL NormalizationDatabase Design • 1st Normal Form • 2nd Normal Form • 3rd Normal Form • Boyce-Codd Normal Form (BCNF) • 4th Normal Form • 5th Normal Form • 6th Normal Form • Domain Key Normal Form (DKNF)

  3. SQL NormalizationDatabase Design: 1st Normal Form • 1st Normal Form has 8 rules: • Rows contain data for instances of an entity. • Columns contain attributes that describe the data of an entity or its domain. • Attributes in a given row, also known as a cell in spreadsheets, contain a single value. • All attributes define data by a single data type. • All attributes have a unique name in an entity. • Attributes are arbitrarily ordered. • Rows of data have no implicit or explicit ordering to access or use. • Two rows may not contain duplicate data.

  4. SQL NormalizationDatabase Design: 1st Normal Form • 1st Normal Form: • Summary: A list of columns that (a) contain only an instance of a data type; and (b) define a collectively unique row. • Design Key #1: A single instance means that delimited values are not stored into a column, like a comma separated values (CSV) list. • Design Key #2: All columns taken together define a unique key that identifies a data structure.

  5. SQL NormalizationDatabase Design: 2nd Normal Form • 2nd Normal Form: • Summary: A row is in 2nd normal form when the list of all non-key columns (or attributes) are fully dependent on the list of key columns, and the row meets the definition of 1st normal form. • Design Key #1: The list of key columns defines the natural key that identifies a unique row. • Design Key #2: A surrogate key (e.g., an automated numbering sequence) may replace the natural key.

  6. SQL NormalizationDatabase Design: 3rd Normal Form • 3rd Normal Form: • Summary: A row is in 2nd normal form when (a) there is no transitive dependency, which is a functional dependency; and (b) the row meets the definition of 2nd normal form. • Design Key #1: A functional dependency means that the domain is a super domain of two domains, and deleting a row from the base domain removes an instance from the functionally dependent domain. • Design Key #2: Functional dependencies can cause deletion anomalies.

  7. SQL NormalizationDatabase Design: 3rd Normal Form • Functional Dependency: • A 2nd normal table exists when the domain contains both: • A RENTAL_ID that identifies two domains – the item rented and its rental price. • A deletion of RENTAL_ID 991 results in deleting the rental and the item rental price. • The ITEM_ID column is a functional dependency or transitive dependency. • Create two 3rd normal form tables by dividing the table into its separate domains. • The ITEM_ID becomes the primary key in one table and foreign key in the other.

  8. SQL NormalizationDatabase Design: 3rd Normal Form • Functional Dependency Resolution: • The ITEM_ID column becomes a primary key and unique description of any row in a 3rd normal form table. • The CHARGE column becomes wholly dependent on the new primary key. • The RENTAL_ID column remains the primary key of a reduced domain and the ITEM_ID becomes a foreign key to the new 3rd normal form table. • The functional dependency of RENTAL_IDCHARGE is eliminated.

  9. SQL NormalizationDatabase Design: BCNF • Boyce-Codd Normal Form (BCNF): • Summary: A row is in BCNF when (a) relations have a single determinant as a candidate key; and (b) the row meets the definition of 3rd normal form. • Design Key #1: A determinant lets you identify a row by combining it with one or more other columns to find a unique row in a relation, which is also known as a super key. • Design Key #2: A determinant is one or more attribute that does not qualify as a candidate key for a table but defines an internally or externally unique relation for a table.

  10. SQL NormalizationDatabase Design: BCNF • Determinant Problem: • A 3rd normal table exists when the domain contains both: • A RENTAL_ID that identifies two domains – the item rented and its type – DVD or VHS. • A deletion of RENTAL_ID 991 results in deleting the rental and the VHS item TYPE of a TITLE. • The TYPE and TITLE column are only inserted when there is a RENTAL_ID, which means the type of rental cannot be inserted before it is rented. • Create two BCNF tables by dividing the table into its separate domains.

  11. Summary • 1st Normal Form • 2nd Normal Form • 3rd Normal Form • Boyce-Codd Normal Form • 4th Normal Form • 5th Normal Form • 6th Normal Form • Domain Key Normal Form (DKNF)

More Related