程序代写代做代考 database ER SQL The Entity-Relationship Model

The Entity-Relationship Model

Conceptual Design.

The Entity-Relationship (ER) Model

CS430/630
Lecture 12

Slides based on “Database Management Systems” 3rd ed, Ramakrishnan and Gehrke

Database Design Overview

2

 Conceptual design

 The Entity-Relationship (ER) Model, UML

 High-level, close to human thinking

 Semantic model, intuitive, rich constructs

 Not directly implementable

 Logical Design

 The relational data model

 Machine-implementable, fewer and more basic constructs

 Logical design translates ER into relational model (SQL)

 Physical Design

 Storage and indexing details

(not in this course)

Conceptual Design – ER Model

 What are the entities and relationships in a typical
application?

 What information about these entities and relationships should
we store in the database?

 What are the integrity constraints or business rules

 Key constraints

 Participation constraints

 Representation through ER diagrams

 ER diagrams are then mapped into relational schemas

 Conversion is fairly mechanical

3

Entities and Entity Sets

 Entity: represents a real-world object
 Characterized using set of attributes

 Each attribute has a domain – similar to variable types

 Entity Set: represents collection of similar entities
 E.g., all employees in an organization

 All entities in an entity set share same set of attributes

4

Keys

 Each entity set has a key

 Set of attributes that uniquely identify an entity

 Multiple candidate keys may exist

 Primary key selected among them

5

Entity Set Representation

Employees

ssn
name

lot

Representation Convention:

– Entity sets: rectangles

– Attributes: ovals, with key attributes underlined

– Edges connect entity sets to attributes

6

Relationships and Relationship Sets

 Relationship: Association among two (or more) entities

 “Gabriel works in CS department”

 Can have descriptive attributes: e.g., “since 9/1/2011”

 But relationship must be fully determined by entities!

 Binary, ternary or multi-way (n-way) relationships

 Relationship Set: Collection of similar relationships

 Contains n-tuples (e1, …, en), where ei belongs to entity set Ei

 Instance: “snapshot” of relationship set at some point in time

7

Visualizing Relationships and Rel. Sets

Edge = Relationship

Set of Edges = Relationship Set

8

A

C

B

D

1

3

2

(A, 1)

(B, 1)

(B, 2)

(D, 3)

Relationship Set Representation

lot

dname
budget did

since
name

Works_In Departments Employees

ssn

9

Representation Convention:

– Relationship sets: diamonds

– Edges connect relationship sets to entity sets, and

relationship sets to relationship set attributes

A Special Case of Relationship

 An entity set can participate in a relationship set with itself

 Entities in same set play different roles in the relationship

 Role indicators express the role

Reports_To

lot

name

Employees

subordinate supervisor

ssn

Role

indicator

Role

indicator

10

Key Constraints

Many-to-Many 1-to-1 1-to-Many Many-to-1

 How many other entities can an entity have a
relationship with?

 Also referred to as relationship multiplicity

11

Example 1

 Works_In relationship: an employee can work in many
departments; a dept can have many employees.

many-to-many

dname

budget did

since

lot

name

ssn

Works_In Employees Departments

12

Example 2

 Manages relationship: each dept has at most one manager

one-to-many

from Employees to Departments , or

many-to-one

from Departments to Employees

dname

budget did

since

lot

name

ssn

Manages Employees Departments

13

Participation Constraints

 Total vs Partial Participation

 Total: every department must have a manager

 “Departments” entity set has total participation in relationship

 Represented as thickened line (there is a key constraint as well)

 Partial: not every employee is a manager

 “Employees” entity set has partial participation

lot

name dname

budget did

since
name dname

budget did

since

Manages Departments Employees

ssn

14

Participation Constraints

Partial

Participation

15

Total

Participation

Example

Design a database for a bank, including information about customers

and their accounts. Information about customers includes their

name, address, phone and SSN. Accounts have numbers, types (e.g.,

savings/checking) and balances.

1. Draw the E/R diagram for this database.

2. Modify the E/R diagram such that each customer must have at

least one account.

3. Modify the E/R diagram further such that an account can have at

most one customer.

16

Mapping ER to Relational Schemas

 For most part, process is mechanical

 Some special cases arise in the presence of constraints

 Translation from ER to SQL requires:

 Mapping entity sets to tables

 Mapping relationship sets to tables

 Capturing key constraints

 Capturing participation constraints

17

Entity Sets to Tables

CREATE TABLE Employees
(ssn CHAR(11),
name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))

Employees

ssn
name

lot

18

Relationship Sets to Tables

 “No-constraints” case follows simple rules

 Relationship set becomes a relation, attributes include:

 Keys for each participating entity set (as foreign keys pointing to

respective entity table)

 All descriptive attributes for relationship

 Primary key of relationship set table is the concatenation of

primary keys for the entity sets

19

Relationship Sets to Tables

20

CREATE TABLE Works_In(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (ssn, did),
FOREIGN KEY (ssn)
REFERENCES Employees,
FOREIGN KEY (did)
REFERENCES Departments)

lot

dname
budget did

since
name

Works_In Departments Employees

ssn

What if there are Key Constraints?

 Each department has at most one manager, according to the

key constraint on Manages

dname

budget did

since

lot

name

ssn

Manages Employees Departments

21

Variant 1

 Map relationship to a table:

 Note that did is the key now!

 Separate table for Manages relationship.

CREATE TABLE Manages(
ssn CHAR(11),
did INTEGER,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees,
FOREIGN KEY (did) REFERENCES Departments)

22

Variant 2

 Since each department has a unique manager, we could instead
combine Manages and Departments.

CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget INTEGER,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees)

23

Review: Participation Constraints

 Does every department have a manager?

 If yes, the participation of Departments in Manages is total

 Every did value in Departments table must appear in a row of

the Manages table (with a non-null ssn value!), but this cannot be

controlled in SQL (unless we use complex constraints)

 Turns out that it is NOT possible to capture this with the

two-tables mapping

 Foreign key mechanism does not allow to check if there is a

reference to every tuple in the referenced table

 The Dept_Mgr variant is the only way!

24

Participation Constraints in SQL

CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget INTEGER,
ssn CHAR(11) NOT NULL,
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees
ON DELETE NO ACTION)

25

lot

name dname

budget did

since
name dname

budget did

since

Manages Departments Employees

ssn

Participation Constraints Summary

 General case

 Total participation cannot be enforced unless we use complex

constraints

 What if there is also a key constraint in place?

 If the entity set with total participation also has a key constraint,

then it is possible to capture total participation

 But only if “combined” table construction is used!

26

Design Choices in the ER Model

 Should a concept be modeled as an entity or an attribute?

 Should a concept be modeled as an entity or a relationship?

 Considers hierarchies and inheritance

 Outside the scope of this class

27

Entity vs. Attribute

 Should address be an attribute of Employees or an entity

(connected to Employees by a relationship)?

28

Employees

ssn
name

address

Entity vs. Attribute

 Sometimes address may have to be an entity:

 If we have several addresses per employee (since attributes

cannot be set-valued)

 If the structure (city, street, etc.) is important, e.g., retrieve

employees in a given city (attribute values are atomic!)

29

Employee

ssn
name street

Address

city zip

Lives

At