1
1
Chapter 4
Logical Database Design and
the Relational Model
2
Objectives
• Define terms for the relational data model
• Transform EE-R diagrams to relations
• Create tables with entity and relational integrity
constraints
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
Transforming ER Diagrams into Relations: Logical Database Design with Relational Model, Lecture notes of Design
An overview of logical database design using the relational model, focusing on transforming ER diagrams into relations. It covers the components of the relational model, the correspondence between ER and relational models, and the process of transforming ER diagrams into relations, including one-to-many, one-to-one, many-to-many, weak entities, associative entities, and unary relationships.
Typology: Lecture notes
2021/2022
1 / 32
This page cannot be seen from the preview
Don't miss anything!
Related documents
Partial preview of the text
Download Transforming ER Diagrams into Relations: Logical Database Design with Relational Model and more Lecture notes Design in PDF only on Docsity!
1
Chapter 4
Logical Database Design and
the Relational Model
2
Objectives
- Define terms for the relational data model
- Transform EE-R diagrams to relations
- Create tables with entity and relational integrity
constraints
3
Steps in Database Problem Solving
Business Problem Study and Analyze w/Team Conceptual Model (EE-R) Logical Model (Relations) Logical Model (3NF Relations) Interviews & Integrated Model Normalization (Three Steps) Transformation (Six Cases) IMPLEMENTATION 4
Logical Model: Relational Model
- Can represent all kinds of information
- Based on Math (relations)
- Natural to people
- Relatively simple
- We know how to implement it fast
7
Motivating Example
- Make a list of students in the class, keeping their ID,
name and phone number
- You’d probably come up with something like this:
- This is the basic structure of the relational model, a
table or relation
ID Name Phone xx Mike 111 yy Elisa 222
Tuple
(Record, Row)
8
Extra Assumptions
- You would not repeat the same row twice
- No two rows have the same ID, but they may have
the same name and phone number
- ID would be the PRIMARY KEY (PK). ID Name Phone xx Mike 111 yy Elisa 222
SET
(no duplicates)
9
Now add emails … (many!)
- Now you need to add the emails of each student, but
you do not know how many emails
- Can you come up with a solution? Try it … 10
Many Fields
- Could come up with something like this
- Above would not work very well. How many fields?
- Wasted space
- What if a student has more emails?
- How to access the emails? ID Name Phone Email1 Email xx Mike 111 bad idea J yy Elisa 222 bad idea J
13
Formalizing: Relations
- Definition: A relation is a named table of data
- Table is made up of rows (records or tuples), and columns (attributes or fields)
- Requirements for a table to be a relation:
- Has a unique name.
- Every attribute value is atomic (not multivalued or composite)
- Every row is unique
- Attributes (columns) in tables have unique names
- The order of the columns is irrelevant
- The order of the rows is irrelevant
By definition, all relations are in 1 st^ Normal Form (1NF).
14
Correspondence with ER Model
- Relations (tables) correspond to entity types and to
many-to-many relationship types
- Rows correspond to entity instances and to many-to-
many relationship instances
- Columns correspond to attributes
• NOTE: The word relation (in relational database) is
NOT the same as the word relationship (in ER
model)
15
Formalizing Key Fields
- Primary key (PK)
- Minimal set of attributes that uniquely identifies a row, chosen for referencing
- This is how we can guarantee that all rows are unique
- Foreign key (FK)
- Set of attributes in a table that serves as a reference to the primary key of another table
- Keys can be simple or composite
- Used as indexes to speed up queries 16
Primary Key
Foreign Key
(implements 1:M 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 Company)
19 Figure 4-6 SQL table definitions
Referential
integrity
constraints are
implemented with
foreign key to
primary key
references.
20
Key Constraints – 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
21
From E-R Diagrams
to
Relations (Tables)
22
Transforming E-R Into Relations
- Use a rectangle for each entity (table), with attributes
inside rectangles, too
- Can be vertical or horizontal
- Primary key is underlined
- Use arrows from Foreign key to Primary key ID Name Phone StudentID Email ID Name Phone StudentID Email Student Student^ Email Email
25
1. Mapping Regular Entities
EMPLOYEE SSN Name (First, Middle, Last) {Emails} Date of Birth [Age] SSN First Middle Last DoB SSN Email Employee Email
- Create a new table for each entity
- Remember to underline the identifier
- For composite attributes, map only the basic pieces
- Derived attributes disappear
- For multivalued attributes we need a new table
- We may need to create several tables for independent multivalued attributes 26
You Try …
BOOK ISBN Title {Authors} Format (Binding, NumPages, Dimensions, [Weight])
29
Six Cases of Transforming E-R
Diagrams into Relations
1. Map Regular Entities
2. Map Binary Relationships
3. Map Weak Entities
4. Map Associative Entities
5. Map Unary Relationships
6. Map Ternary (and n-ary) Relationships
30
Mapping Binary Relationships
- One-to-Many
- Primary key on the one side becomes a foreign key on the many side (Fig. 4-12).
- One-to-One
- Primary key on the mandatory side becomes a foreign key on the optional side (Fig. 4-14).
- Many-to-Many
- Create a new relation with the primary keys of the two entities as its primary key (Fig. 4-13).
34 Figure 4-14 Example of mapping a binary 1:1 relationship Often in 1:1 relationships, one direction is optional. Foreign key goes in the relation on the optional side, matching the primary key on the mandatory side 35
Many-to-Many Relationship
ID Name
- For a many-to-many, we need a new table representing the relationship.
- This table has Foreign Keys to both entities. STUDENT ID Name PROGRAM ID Name
Majors in Program ID Name Student Student Program MajorsIn
36 Figure 4-13 Example of mapping an M:N relationship a) Completes relationship (M:N) The Completes relationship will need to become a separate relation 37 Figure 4-13 Example of mapping an M:N relationship (cont.) b) Three resulting relations Foreign key (^) Foreign key Composite primary key (CPK)
41
3. Mapping Weak Entities
- A weak entity becomes a separate relation with
a foreign key taken from the strong entity
- Primary key composed of:
- Partial identifier of weak entity
- Primary key of identifying relation (strong entity) 42
Weak Entities
Employee Name DoB
- Transform the strong entity normally
- For the weak entity, the PK becomes the identifier, plus the PK of the identifying entity EMPLOYEE SSN Name DEPENDENT Name Date of Birth Has Dependent SSN Name Employee
43
You Try …
BOOK ISBN Title Edition CHAPTER Number Title Has 45