CSCI 260 Chapter Five Database Design Chapter Objectives

CSCI 260 Chapter Five Database Design

Chapter Objectives • Learn how to transform E-R data models into relationa • • 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 1: 1, 1: N, and N: M binary relationships 2

Chapter Objectives (continued) • Know how to represent 1: 1, 1: N, and N: M recursive • • relationships Learn SQL statements for creating joins over binary and recursive relationships Understand the nature and background of normalization 3

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 4

Representing the ITEM entity • ITEM (Itemnumber, Description, Cost, List. Price, Quantity. On. Hand) 5

Representing the CUSTOMER entity • CUSTOMER (Customer. Number, Customer. Name, Address, City, State, Zip, Contact, Phone. Number) 6

Representing the CUSTOMER entity • Normalization process… check for functional dependencies: • Zip -> (City, State) • Contact -> Phone. Number 7

Representing the CUSTOMER entity • CUSTOMER (Customer. Number, Customer. Name, Address, Zip, Contact) • ZIP (Zip, City, State) • CONTACT (Contact, Phone. Number) Zip in CUSTOMER must exist in Zip in ZIP Contact in CUSTOMER must exist in Contact in CONTACT 8

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 9

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

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 11

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 12

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

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

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 15

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 customer’s address 16

Denormalization Issues • Inserts – with zip as separate table, can’t insert address without having entry in Zip table. That’s 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? 17

SALES-COMMISSION example • 1 – Construct relation with all the entity’s attributes as columns • 2 – Relation is Normalized • 3 – If Normalization is too pure, Relation is denormalized 18

SALES-COMMISSION example 19

SALES-COMMISSION example • SALES-COMMISSION( Salesperson. Number, Salesperson. Name, Phone, Check. Number, Check. Date, Commission. Period, Total. Commission. Sales, Commission. Amount, Budget. Category) 20

SALES-COMMISSION example Functional Dependencies: • Salesperson. Number -> (Salesperson. Name, Phone, Budget. Category) • (Salesperson. Number, Comission. Period) -> (Total. Commission. Sales, Commission. Amount) • 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. 21

SALES-COMMISSION example SALES-COMMISSION (Sales. Person. Number, Check. Date, Commission. Period) SALESPERSON (Salesperson. Number, Salesperson. Name, Phone) SALES (Salesperson. Number, Commission. Period, Total. Commission. Sales, Commission. Amount) 22

SALES-COMMISSION example Referential Integrity Contstraints: Salesperson. Number in SALES-COMMISSION must exist in Salesperson. Number in SALESPERSON (Salesperson. Number, Commission. Period) in SALES-COMMISSION must exist in (Salesperson. Number, Commission. Period) in SALES 23

SALES-COMMISSION example Denormalization possibilities: • Any reason to not create SALESPERSON or SALES relations? 24

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

• (b) Line. Number not complete key – needs to have Invoice. Number since it is IDDependant • (c) 26