Database Normalization - Database Management Systems - Lecture Slides, Slides of Introduction to Database Management Systems

Download Database Normalization - Database Management Systems - Lecture Slides and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

Objectives

• Why is database design important?
• What is a table and how do you choose keys?
• What are the fundamental rules of database normalization?
• How do you begin analyzing a form to create normalized tables?
• How do you create a design in first normal form?
• What is second normal form?
• What is third normal form?
• What problems exist beyond third normal form?
• How do business rules change the database design?
• What problems arise when converting a class diagram to normalized

tables?

• What tables are needed for the Sally’s Pet Store?
• How do you combine tables from multiple forms and many developers?
• How do you record the details for all of the columns and tables?

Definitions

4

 Relational database: A collection of tables.  Table: A collection of columns (attributes) describing an entity. Individual objects are stored as rows of data in the table.  Property (attribute): a characteristic or descriptor of a class or entity.  Every table has a primary key.  The smallest set of columns that uniquely identifies any row  Primary keys can span more than one column (concatenated keys)  We often create a primary key to insure uniqueness (e.g., CustomerID, Product#,.. .) called a surrogate key.

EmployeeID TaxpayerID LastName FirstName HomePhone Address 12512 888-22-5552 Cartom Abdul (603) 323-9893 252 South Street 15293 222-55-3737 Venetiaan Roland (804) 888-6667 937 Paramaribo Lane 22343 293-87-4343 Johnson John (703) 222-9384 234 Main Street 29387 837-36-2933 Stenheim Susan (410) 330-9837 8934 W. Maple

Employee

Properties Rows/Objects

Primary key Class: Employee

Keys

• Primary key

– Every table (object) must have a primary key

– Uniquely identifies a row (one-to-one)

• Concatenated (or composite) key

– Multiple columns needed for primary key

– Identify repeating relationships (1 : M or M : N)

• Key columns are underlined

• First step

– Collect user documents

– Identify possible keys: unique or repeating

relationships

Identifying Key Columns

7

Orders

OrderItems

OrderID Date Customer 8367 5-5-04 6794 8368 5-6-04 9263

OrderID Item Quantity 8367 229 2 8367 253 4 8367 876 1 8368 555 4 8368 229 1

Each order has

only one customer.

So Customer is not

part of the key.

Each order has

many items.

Each item can appear

on many orders.

So OrderID and Item

are both part of the key.

Surrogate Keys

• Real world keys sometimes cause problems in a database.
• Example: Customer
  • Avoid phone numbers: people may not notify you when numbers change.
  • Avoid SSN (privacy and most businesses are not authorized to ask for verification, so you could end up with duplicate values)
• Often best to let the DBMS generate unique values
  • Access: AutoNumber
  • SQL Server: Identity
  • Oracle: Sequences (but require additional programming)
• Drawback: Numbers are not related to any business data, so the

application needs to hide them and provide other look up

mechanisms.

Database Normalization Rules

• 1. Each cell in a table contains atomic (single-valued) data.
• 2. Each non-key column depends on all of the primary key columns (not

just some of the columns).

• 3. Each non-key column depends on nothing outside of the key

columns.

Atomic Values for Phone Numbers

CustomerID LastName FirstName Phone Fax CellPhone

15023 Jones Mary 222-3034 222-4094 223- 63478 Sanchez Miguel 030-9693 403-

94552 O’Reilly Madelline 849-4948 292-3332 139-

45791 Stein Marta 294-

49004 Brise Mer 764-

11

Repeating Values for Phone

Numbers

CustomerID LastName FirstName 15023 Jones Mary 63478 Sanchez Miguel 94552 O’Reilly Madeline 45791 Stein Marta 49004 Brise Mer

CustomerID PhoneType Phone 15023 Land 222- 15023 Fax 222- 15023 Cell 223- 63478 Land 030- 63478 Fax 403- 94552 Land 849- 94552 Fax 292- 94552 Cell 139- 94552 Laptop 339- 45791 Land 294- 49004 Land 764-

Simple Form

Customer ID Company Name

City

Contact LastName, FirstName

Phone

14

Sample Database for Sales

Sale ID Date

Customer First Name Last Name Address City, State ZIPCode ItemID Description List Price Quantity QOH Value

Total

19

Initial Objects

Initial Object Key Sample Properties

Customer Assign CustomerID Name Address Phone

Item Assign ItemID Description List Price Quantity On Hand

Sale Assign SaleID Sale Date

SaleItems SaleID + ItemID Quantity

20

Problems with Repeating Sections

22

SaleForm(SaleID, SaleDate, CustomerID, FirstName, LastName, Address, City, State, ZIPCode, (ItemID, Description, ListPrice, Quantity, QuantityOnHand) )

SaleID Date CID FirstName LastName Address City State ZIP ItemID Description ListPrice Quantity QOH 11851 7/15 15023 Mary Jones 111 Elm Chicago IL 60601 15 27 32

Air Tank Regulator Mask 1557

2 1 1

15 5 6 11852 7/15 63478 Miguel Sanchez 222 Oro Madrid 15 33

Air Tank Mask 2020

4 1

15 3 11853 7/16 15023 Mary Jones 111 Elm Chicago IL 60601 41 75

Snorkel 71 Wet suit-S

2 1

15 3 11854 7/17 94552 Madeline O’Reilly 333 Tam Dublin 75 32 57

Wet suit-S Mask 1557 Snorkel 95

2 1 1

3 6 17

Repeating section Duplication Not atomic

First Normal Form

23

SaleForm(SaleID, SaleDate, CustomerID, FirstName, LastName, Address, City, State, ZIPCode, (ItemID, Description, ListPrice, Quantity, QuantityOnHand) )

SaleForm2(SaleID, SaleDate, CustomerID, FirstName, LastName, Address, City, State, ZIPCode)

SaleLine(SaleID, ItemID, Description, ListPrice, Quantity, QuantityOnHand)