CHAPTER 4:
CHAPTER 4:
LOGICAL DATABASE DESIGN AND
LOGICAL DATABASE DESIGN AND
THE RELATIONAL MODEL
THE RELATIONAL MODEL
Copyright © 2014 Pearson Education, Inc. 1
Essentials of Database
Management
Jeffrey A. Hoffer, Heikki Topi, V.
Ramesh
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Guidelines and tips
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Database Slides Full Course, Lecture notes of Database Programming
Hoeffer Slides for database full course
Typology: Lecture notes
2016/2017
1 / 56
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Database Slides Full Course and more Lecture notes Database Programming in PDF only on Docsity!
CHAPTER 4:CHAPTER 4:
LOGICAL DATABASE DESIGN AND LOGICAL DATABASE DESIGN AND
THE RELATIONAL MODEL THE RELATIONAL MODEL
Copyright © 2014 Pearson Education, Inc.
Essentials of Database
Management
Jeffrey A. Hoffer, Heikki Topi, V.
Ramesh
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
OBJECTIVES OBJECTIVES
Define termsDefine terms
List five properties of relationsList five properties of relations
State two properties of candidate keysState two properties of candidate keys
Define first, second, and third normal formDefine first, second, and third normal form
Describe problems from merging relationsDescribe problems from merging relations
Transform E-R and EER diagrams toTransform E-R and EER diagrams to
relations relations
Create tables with entity and relationalCreate tables with entity and relational
integrity constraints integrity constraints
Use normalization to convert anomalousUse normalization to convert anomalous
tables to well-structured relations tables to well-structured relations
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
RELATION RELATION
(^) A relation is a named, two-dimensional table of data.A relation is a named, two-dimensional table of data.
(^) A table consists of rows (records) and columnsA table consists of rows (records) and columns
(attributes or fields). (attributes or fields).
Requirements for a table to qualify as a relation:Requirements for a table to qualify as a relation:
(^) It must have a unique name.It must have a unique name. (^) Every attribute value must be atomic (not multivalued, notEvery attribute value must be atomic (not multivalued, not composite). composite). (^) Every row must be unique (can’t have two rows with exactly theEvery row must be unique (can’t have two rows with exactly the same values for all their fields). same values for all their fields). (^) Attributes (columns) in tables must have unique names.Attributes (columns) in tables must have unique names. (^) The order of the columns must be irrelevant.The order of the columns must be irrelevant. (^) The order of the rows must be irrelevant.The order of the rows must be irrelevant.
NOTE: all relations are in 1 st^ Normal form
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
CORRESPONDENCE WITH E-R CORRESPONDENCE WITH E-R
MODEL MODEL
Relations (tables) correspond with entityRelations (tables) correspond with entity
types and with many-to-many relationship types and with many-to-many relationship
types. types.
Rows correspond with entity instances andRows correspond with entity instances and
with many-to-many relationship instances. with many-to-many relationship instances.
Columns correspond with attributes.Columns correspond with attributes.
NOTE: The wordNOTE: The word relationrelation (in relational(in relational
database) is NOT the same as the word database) is NOT the same as the word
relationship relationship (in E-R model).(in E-R model).
Primary Key Foreign Key (implements 1:N relationship between customer and order)
Combined, these are a composite primary key (uniquely identifies the order line)…individually they are foreign keys (implement M:N relationship between order and product)
Figure 4-3 Schema for four relations (Pine Valley Furniture Comp
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc. 7
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
INTEGRITY CONSTRAINTS INTEGRITY CONSTRAINTS
(^) Domain ConstraintsDomain Constraints
(^) Allowable values for an attribute. SeeAllowable values for an attribute. See
Table 4-1 Table 4-
(^) Entity IntegrityEntity Integrity
(^) No primary key attribute may be null.No primary key attribute may be null.
All primary key fields All primary key fields MUSTMUST have datahave data
(^) Action AssertionsAction Assertions
(^) Business rules. Recall from Chapter 4Business rules. Recall from Chapter 4
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
INTEGRITY CONSTRAINTS INTEGRITY CONSTRAINTS
Referential Integrity –rule states that any foreign
key value (on the relation of the many side) MUST
match a primary key value in the relation of the one
side. (Or the foreign key can be null)
For example: Delete Rules
(^) Restrict –don’t allow delete of “parent” side if related rows exist in “dependent” side (^) Cascade –automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted (^) Set-to-Null –set the foreign key in the dependent side to null if deleting from the parent side not allowed for weak entities
Figure 4- Referential integrity constraints (Pine Valley Furniture)
Referential integrity constraints are drawn via arrows from dependent to parent table
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc. 11
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
TRANSFORMING EER DIAGRAMS TRANSFORMING EER DIAGRAMS
INTO RELATIONS INTO RELATIONS
Mapping Regular Entities to Relations Mapping Regular Entities to Relations
Simple attributes: E-R attributes mapSimple attributes: E-R attributes map
directly onto the relation directly onto the relation
Composite attributes: Use only theirComposite attributes: Use only their
simple, component attributes simple, component attributes
Multivalued Attribute: Becomes aMultivalued Attribute: Becomes a
separate relation with a foreign key separate relation with a foreign key
taken from the superior entity taken from the superior entity
(a) CUSTOMER
entity type with
simple
attributes
Figure 4-8 Mapping a regular entity
(b) CUSTOMER relation
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc. 14
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.^16
Figure 4-10 Mapping an entity with a multivalued attribute
One-to-many relationship between original entity and new relation
(a)
Multivalued attribute becomes a separate relation with foreign key
(b)
Chapter 4 Copyright © 2014 Pearson Education, Inc.Copyright © 2014 Pearson Education, Inc.
TRANSFORMING EER DIAGRAMS TRANSFORMING EER DIAGRAMS
INTO RELATIONS (CONT.) INTO RELATIONS (CONT.)
Mapping Weak Entities Mapping Weak Entities
Becomes a separate relation with aBecomes a separate relation with a
foreign key taken from the superior entity foreign key taken from the superior entity
Primary key composed of:Primary key composed of:
Partial identifier of weak entityPartial identifier of weak entity
Primary key of identifying relation (strongPrimary key of identifying relation (strong
entity) entity)
NOTE: the domain
constraint for the foreign key
should NOT allow null value
if DEPENDENT is a weak
entity
Foreign key
Composite primary key
Figure 4-11 Example of mapping a weak entity (cont.)
b) Relations resulting from weak entity