Transcript 2/4 - SEAS - University of Pennsylvania

Database Conceptual
and Logical Design
Susan B. Davidson
University of Pennsylvania
CIS330 – Database & Information Systems
Some slide content courtesy of Raghu Ramakrishnan
 We’ve talked about how to query an existing
relational database, but how do we create one for a
new application?
 Typically, this is done using ER modeling and then
relational design theory as a refinement step.
 Today, we’ll talk about the first step: ER modeling
2
Outline
 Modeling a DB application using Entity-Relationship
(ER) diagrams
 Translating basic ER diagrams to a relational schema
 Roles and IS-A relationships and their translation
 Weak entities: tweaking our running example
3
Outline
 Modeling a DB application using EntityRelationship (ER) diagrams
 Translating basic ER diagrams to a relational schema
 Roles and IS-A relationships and their translation
 Weak entities: tweaking our running example
4
Database Design: A 6-Step Program
1. Requirements Analysis: what data, apps, critical
operations
2. Conceptual DB Design: high-level description of
data and constraints – typically using ER model
3. Logical DB Design: conversion into a schema
4. Schema Refinement: normalization (eliminating
redundancy)
5. Physical DB Design: consider workloads, indexes
and clustering of data
6. Application/Security Design
5
Entity-Relationship Diagram
Underlined attributes are keys
fid
entity set
STUDENTS
sid
name
PROFESSORS
relationship set
Teaches
COURSES
Takes
exp-grade
name
cid
subj
sem
attributes (recall these have domains)
6
Conceptual Design Process
 What are the entities being represented?
 What are the relationships?
STUDENTS
Takes
 What info (attributes) do we store about each?
name
exp-grade
 What keys & integrity constraints do we have?
sid
7
Outline
 Modeling a DB application using Entity-Relationship
(ER) diagrams
 Translating basic ER diagrams to a relational
schema
 Roles and IS-A relationships and their translation
 Weak entities: tweaking our running example
8
Logical Schema Design
 Roughly speaking, each entity set or relationship set
becomes a table (not always be the case; we’ll
discuss this later)
 Attributes associated with each entity set or
relationship set become attributes of the relation;
the key is also copied (ditto with foreign keys in a
relationship set)
9
Translating Entity Sets to
Logical Schemas & SQL DDL
Fairly straightforward to generate a schema…
CREATE TABLE STUDENTS
(sid INTEGER,
name VARCHAR(15)
PRIMARY KEY (sid) )
STUDENTS
sid
name
COURSES
cid
subj
sem
CREATE TABLE COURSES
(cid CHAR(15),
subj VARCHAR(30),
sem INTEGER,
PRIMARY KEY (cid, sem) )
10
Translating Relationship Sets
Generate schema with attributes consisting of:
 Key(s) of each associated entity (foreign keys)
 Descriptive attributes
CREATE TABLE Takes
Takes
(sid INTEGER,
cid CHAR(15),
sem INTEGER,
exp-grade
exp-grade CHAR(1),
PRIMARY KEY (?),
FOREIGN KEY (cid, sem) REFERENCES COURSES,
FOREIGN KEY (sid) REFERENCES STUDENTS)
11
… OK, But What about Connectivity
in the E-R Diagram?
 Attributes can only be connected to entities or
relationships
 Entities can only be connected via relationships
 As for the edges, let’s consider kinds of relationships
and integrity constraints…
PROFESSORS
Teaches
COURSES
(warning: the book has a slightly different notation here!)
12
Binary Relationships & Participation
 Binary relationships can be classified as 1:1, 1:Many,
or Many:Many, as in:
1:1
1:n
m:n
13
1:Many (1:n) Relationships
 Placing an arrow in the many  one direction, i.e.
towards the entity that’s ref’d via a foreign key
 Suppose profs teach multiple courses, but may not
have taught yet:
PROFESSORS
Teaches
COURSES
Partial participation (0 or more…)
 Suppose profs must teach to be on the roster:
PROFESSORS
Teaches
COURSES
Total participation (1 or more…)
14
Many-to-Many Relationships
 Many-to-many relationships have no arrows on
edges
 The “relationship set” relation has a key that includes the
foreign keys, plus any other attributes specified as key
STUDENTS
Takes
COURSES
15
Examples
 Suppose courses must be taught to be on the roster
 Suppose students must have enrolled in at least one
course
16
Representing 1:n Relationships
in Tables
CREATE TABLE Teaches(
• Key of relationship fid INTEGER,
cid CHAR(15),
set:
sem INTEGER,
PRIMARY KEY (cid, sem),
FOREIGN KEY (fid) REFERENCES PROFESSORS,
FOREIGN KEY (cid, sem) REFERENCES Courses)
CREATE TABLE Teaches_Course(
• Or embed
relationship in
“many” entity set:
cid CHAR(15),
subj VARCHAR(30),
sem CHAR(4),
fid CHAR(15),
PRIMARY KEY (cid, sem),
FOREIGN KEY (fid) REFERENCES PROFESSORS)
17
1:1 Relationships
If you borrow money or have credit, you might get:
rid
CreditReport
delinquent?
Describes
debt
Borrower
ssn
name
What are the table options?
18
Outline
 Modeling a DB application using Entity-Relationship
(ER) diagrams
 Translating basic ER diagrams to a relational schema
 Roles and IS-A relationships and their
translation
 Weak entities: tweaking our running example
19
Roles: Labeled Edges
Sometimes a relationship connects the same entity,
and the entity has more than one role:
Includes
Assembly
qty
Subpart
id
Parts
name
This often indicates the need for recursive queries
20
DDL for Role Example
CREATE TABLE Parts
(Id INTEGER,
Name CHAR(15),
…
PRIMARY KEY (ID) )
CREATE TABLE Includes
(Assembly INTEGER,
Subpart INTEGER,
Qty INTEGER,
PRIMARY KEY (Assemb, Sub),
FOREIGN KEY (Assembly) REFERENCES Parts,
FOREIGN KEY (Subpart) REFERENCES Parts)
21
Roles vs. Separate Entities
id
Husband
name
name
What is the difference
between these two
representations?
id
Wife
Married
Married
Husband
Wife
id
Person
name
22
ISA Relationships: Subclassing
(Structurally)
 Inheritance states that one entity is a “special kind”
of another entity: “subclass” should be member of
“base class”
id
People
name
ISA
Employees
salary
23
But How Does ISA Translate
into the Relational Model?
Compare these options:
 Two tables, disjoint tuples
 Two tables, disjoint attributes
 One table with NULLs
24
Outline
 Modeling a DB application using Entity-Relationship
(ER) diagrams
 Translating basic ER diagrams to a relational schema
 Roles and IS-A relationships and their translation
 Weak entities: tweaking our running
example
25
Weak Entities
A weak entity can only be identified uniquely using the primary
key of another (owner) entity.
 Owner and weak entity sets in a one-to-many relationship
set, 1 owner : many weak entities
 Weak entity set must have total participation
People
ssn
name
Feeds
weeklyCost
Pets
name
species
26
Translating Weak Entity Sets
Weak entity set and identifying relationship set are translated
into a single table; when the owner entity is deleted, all
owned weak entities must also be deleted
CREATE TABLE Feed_Pets (
name VARCHAR(20),
species INTEGER,
weeklyCost REAL,
ssn CHAR(11) NOT NULL,
PRIMARY KEY (pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ON DELETE CASCADE)
27
Let’s rethink the schema for running
example…
 Problem: currently, information about the subject
is repeated for every offering of the course.
fid
PROFESSORS
name
Teaches
STUDENTS
sid
name
COURSES
Takes
exp-grade
cid
subj
sem
28
Let’s rethink the schema for running
example…
 Solution: Use weak entity
fid
cid
name
PROFESSORS
Teaches
subj
COURSES
OFFERING
Instance-of
sem
29
Final thought: N-ary Relationships
 Relationship sets can relate an arbitrary number of
entity sets:
Student
Project
Indep
Study
Advisor
30
Summary of ER Diagrams
 One of the primary ways of designing logical
schemas
 CASE tools exist built around ER
(e.g. ERWin, PowerBuilder, etc.)
 Translate the design automatically into DDL, XML, UML,
etc.
 Use a slightly different notation that is better suited to
graphical displays
 Some tools support constraints beyond what ER diagrams
can capture
31