Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

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

Some concept of Database Management Systems are Access Methods Layer, Basic Structure, Common Structures, Designing Systems, Join Processing, Modern Computers, Query Evaluation Techniques. Main points of this lecture are: Database Normalization, Database Design, Table, Fundamental Rules, Database, Create Normalized, Normal Form, Second Normal, Third Normal Form, Problems Exist

Typology: Slides

2012/2013

Uploaded on 04/27/2013

prakash
prakash 🇮🇳

4.6

(10)

63 documents

1 / 98

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Database Management Systems
1
Docsity.com
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29
pf2a
pf2b
pf2c
pf2d
pf2e
pf2f
pf30
pf31
pf32
pf33
pf34
pf35
pf36
pf37
pf38
pf39
pf3a
pf3b
pf3c
pf3d
pf3e
pf3f
pf40
pf41
pf42
pf43
pf44
pf45
pf46
pf47
pf48
pf49
pf4a
pf4b
pf4c
pf4d
pf4e
pf4f
pf50
pf51
pf52
pf53
pf54
pf55
pf56
pf57
pf58
pf59
pf5a
pf5b
pf5c
pf5d
pf5e
pf5f
pf60
pf61
pf62

Partial preview of the text

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.
    1. Each non-key column depends on all of the primary key columns (not

just some of the columns).

    1. 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)