Database Design

1
Database Design
CSCI260

• Chapter Five

2
Chapter Objectives

• Learn how to transform E-R data models into
  relational designs
• Practice the normalization process from Chapter 2
• Understand the need for denormalization
• Learn how to represent weak entities with the
  relational model
• Know how to represent 11, 1N, and NM binary
  relationships

3
Chapter Objectives (continued)

• Know how to represent 11, 1N, and NM recursive
  relationships
• Learn SQL statements for creating joins over
  binary and recursive relationships
• Understand the nature and background of
  normalization

4
Representing Entities with the Relational Model

• Create a relation for each entity
• Give it same name as Entity
• Create column for each attribute
• Set primary key as the identifier
• The relation is then analyzed using the
  normalization rules
• As normalization issues arise, the initial
  relation design may need to change

5
Representing the ITEM entity

• ITEM (Itemnumber, Description, Cost, ListPrice,
  QuantityOnHand)

6
Representing the CUSTOMER entity

• CUSTOMER (CustomerNumber, CustomerName, Address,
  City, State, Zip, Contact, PhoneNumber)

7
Representing the CUSTOMER entity

• Normalization process check for functional
  dependencies
• Zip -gt (City, State)
• Contact -gt PhoneNumber

8
Representing the CUSTOMER entity

• CUSTOMER (CustomerNumber, CustomerName, Address,
  Zip, Contact)
• ZIP (Zip, City, State)
• CONTACT (Contact, PhoneNumber)
• Zip in CUSTOMER must exist in Zip in ZIP
• Contact in CUSTOMER must exist in Contact in
  CONTACT

9
Anomalies

• Relations that are not normalized will experience
  issues known as anomalies
• Insertion anomaly
• Difficulties inserting data into a relation
• Modification anomaly
• Difficulties modifying data into a relation
• Deletion anomaly
• Difficulties deleting data from a relation

10
Solving Anomalies

• Most anomalies are solved by breaking an existing
  relation into two or more relations through a
  process known as normalization

11
Definition Review

• Functional dependency
• The relationship (within the relation) that
  describes how the value of a one attribute may be
  used to find the value of another attribute
• Determinant
• The attribute that can be used to find the value
  of another attribute in the relation
• The right-hand side of a functional dependency

12
Definition Review

• Candidate key
• The value of a candidate key can be used to find
  the value of every other attribute in the
  relation
• A simple candidate key consists of only one
  attribute
• A composite candidate key consists of more than
  one attribute

13
Normal Forms

• There are many defined normal forms
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
• Domain/Key Normal Form (DK/NF)

14
Normalization

• For our purposes, a relation is considered
  normalized when
• Every determinant is a candidate key
• Technically, this is Boyce-Codd Normal Form
  (BCNF)

15
Denormalization

• Normalizing relations (or breaking them apart
  into many component relations) may significantly
  increase the complexity of the data structure
• The question is one of balance
• Trading complexity for anomalies
• There are situations where denormalized relations
  are preferred

16
Denormalization Example

• Breaking Zip into own table makes CUSTOMER
  normalized
• However it makes it more difficult to use
• People are used to entering City, State and Zip
  together
• DBMS has to read two tables just to get
  customers address

17
Denormalization Issues

• Inserts with zip as separate table, cant
  insert address without having entry in Zip table.
  Thats ok we want to just enter the City,
  State, Zip with the rest of the address.
• Updates how often does a city change its Zip
  code?

18
SALES-COMMISSION example

• 1 Construct relation with all the entitys
  attributes as columns
• 2 Relation is Normalized
• 3 If Normalization is too pure, Relation is
  denormalized

19
SALES-COMMISSION example
20
SALES-COMMISSION example

• SALES-COMMISSION( SalespersonNumber,
  SalespersonName, Phone, CheckNumber, CheckDate,
  CommissionPeriod, TotalCommissionSales,
  CommissionAmount, BudgetCategory)

21
SALES-COMMISSION example

• Functional Dependencies
• SalespersonNumber -gt (SalespersonName, Phone,
  BudgetCategory)
• (SalespersonNumber, ComissionPeriod) -gt
  (TotalCommissionSales, CommissionAmount)
• Extract attributes of the functional dependencies
  from original table and make the determinants the
  keys of new table.
• Leave copy of determinants in original table as
  foreign key.

22
SALES-COMMISSION example

• SALES-COMMISSION (SalesPersonNumber, CheckNumber,
  CheckDate, CommissionPeriod)
• SALESPERSON (SalespersonNumber, SalespersonName,
  Phone)
• SALES (SalespersonNumber, CommissionPeriod,
  TotalCommissionSales, CommissionAmount)

23
SALES-COMMISSION example

• Referential Integrity Contstraints
• SalespersonNumber in SALES-COMMISSION must exist
  in SalespersonNumber in SALESPERSON
• (SalespersonNumber, CommissionPeriod) in
  SALES-COMMISSION must exist in (SalespersonNumber,
  CommissionPeriod) in SALES

24
SALES-COMMISSION example

• Denormalization possibilities
• Any reason to not create SALESPERSON or SALES
  relations?

25
Weak Entities

• For an ID-dependent weak entity, the key of the
  parent becomes part of the key of the weak entity

26

• (b) LineNumber not complete key needs to have
  InvoiceNumber since it is ID-Dependant
• (c)