Lecture Database concepts (3rd Edition): Chapter 5 - David M. Kroenke, David J. Auer

Learning objectives of this chapter include: 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 1:1, 1:N, and N:M binary relationships.
Nội dung trích xuất từ tài liệu:
Lecture Database concepts (3rd Edition): Chapter 5 - David M. Kroenke, David J. AuerDAVIDM.KROENKEandDAVIDJ.AUERDATABASECONCEPTS,3rdEdition Chapter Five Database Design 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 1:1, 1:N, and N:M binary relationshipsKROENKE and AUER - DATABASE 5-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 normalizationKROENKE and AUER - DATABASE 5-3Transforming a Data Model into a Relational DesignKROENKE and AUER - DATABASE 5-4 Representing Entities with the Relational Model• Create a relation for each entity – A relation has a descriptive name and a set of attributes that describe the entity• Specify a primary key• Specify column properties – Data type – Null status – Default values (if any) – Data constraints (if any)• The relation is then analyzed using the normalization rules• As normalization issues arise, the initial relation design may need to changeKROENKE and AUER - DATABASE 5-5Representing an Entity as a Table ITEM (ItemNumber, Description, Cost, ListPrice, QuantityOnHand)KROENKE and AUER - DATABASE 5-6 Normalization Review: Modification Problems• Tables that are not normalized will experience issues known as modification problems – Insertion problems • Difficulties inserting data into a relation – Modification problems • Difficulties modifying data into a relation – Deletion problems • Difficulties deleting data from a relationKROENKE and AUER - DATABASE 5-7 Normalization Review: Solving Modification Problems• Most modification problems are solved by breaking an existing table into two or more tables through a process known as normalizationKROENKE and AUER - DATABASE 5-8 Normalization Review: 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 dependencyKROENKE and AUER - DATABASE 5-9 Normalization Review: Definition Review II• Candidate key – The value of a candidate key can be used to find the value of every other attribute in the table – A simple candidate key consists of only one attribute – A composite candidate key consists of more than one attributeKROENKE and AUER - DATABASE 5-10 Normalization Review: 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)KROENKE and AUER - DATABASE 5-11 Normalization Review: Normalization• For our purposes, a relation is considered normalized when: Every determinant is a candidate key [Technically, this is Boyce-Codd Normal Form (BCNF)]KROENKE and AUER - DATABASE 5-12 The CUSTOMER Table CUSTOMER (CustomerNumber, CustomerName, StreetAddress, City, State, ZIP, ContactName, Phone) ZIP→(City, State) ContactName→PhoneKROENKE and AUER - DATABASE 5-13 The CUSTOMER Entity: The Normalized Set of TablesCUSTOMER (CustomerNumber, CustomerName, StreetAddress, ZIP, ContactName)ZIP (ZIP, City, State)CONTACT (ContactName, Phone)KROENKE and AUER - DATABASE 5-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 modification problems• There are situations where denormalized relations are preferredKROENKE and AUER - DATABASE 5-15 The CUSTOMER Entity: The Denormalized Set of TablesCUSTOMER (CustomerNumber, CustomerName, StreetAddress, City, State, ZIP, ContactName)CONTACT (ContactName, Phone)KROENKE and AUER - DATABASE ...