1

Translating E/R Diagrams into Relational Schemas

2

Translating Entity Sets

Include all attributes of the entity set and use the entity set’s key as the primary key.

Example: The Author entity set

CREATE TABLE Authors (name CHAR(30), phone CHAR(20), address CHAR(20),

DOB DATE, PRIMARY KEY (name, phone))

3

Translating M-N Relationship Sets

Attributes of the relation must include:– Keys for each

participating entity set (as foreign keys).

This set of attributes forms a key for the relation.

– All descriptive attributes. Ignore participation

constraints for now!!

CREATE TABLE Wrote( isbn CHAR(11), name CHAR(30), phone CHAR(20), done DATE, PRIMARY KEY (isbn, name,

phone), FOREIGN KEY (isbn) REFERENCES Books (isbn), FOREIGN KEY (name, phone) REFERENCES Authors (name,

phone))

4

Translating M-1 or 1-M Relationship Sets

Key of unconstrained entity goes into relation for entity with the key constraint

Descriptive attributes of relationship also go into relation for entity with the key constraint

Example: Books table and “publish” relationship:

CREATE TABLE Books ( isbn CHAR(11), title CHAR(20), pubname CHAR(25), pubdate DATE, PRIMARY KEY (isbn), FOREIGN KEY (pubname) REFERENCES Publishers (name))

5

Translating 1-1 Relationship Sets

Key of one entity goes into relation for other entity, along with the descriptive attributes of relationship

Choice is up to DBA and is application-dependent

Example: Publishers, Editors tables and “edits” relationship:CREATE TABLE Publishers

( name CHAR(25), address CHAR(20), URL CHAR(50), PRIMARY KEY (name) )

CREATE TABLE Editors ( name CHAR(30), phone CHAR(20), pubname CHAR(25), hours INTEGER, PRIMARY KEY (name), FOREIGN KEY (pubname) REFERENCES Publishers (name) )

6

Participation Constraints in SQL

Capture using NOT NULL if:– Entity with the participation constraint also has a key

constraint for the same relationship AND– Relation for the constrained entity contains a foreign key

for the related entity. Examples:

– Books-Publish participation constraint: CREATE TABLE Books (….pubname CHAR(25) NOT NULL, ….)

– Authors-Wrote participation constraint: Needs an ASSERTION or TRIGGER (later in course… needs SQL!)

– Publisher-Edits participation constraint: Re-design Publishers/Editors and use NOT NULL OR use an

ASSERTION or TRIGGER

7

Translating Weak Entity Sets Weak entity set and identifying

relationship set are translated into a single table.– When the owner (strong) entity is deleted,

all owned weak entities must also be deleted.CREATE TABLE Dependents (

aname CHAR(30), dname CHAR(20), age INTEGER, PRIMARY KEY (aname, dname), FOREIGN KEY (aname) REFERENCES Authors(name), ON DELETE CASCADE)

8

Relational Query Languages

A major strength of the relational model: supports simple, powerful querying of data.

Queries can be written intuitively, and the DBMS is responsible for efficient evaluation.– The key: precise semantics for relational

queries.– Allows the optimizer to extensively re-order

operations, and still ensure that the answer does not change.

9

The SQL Query Language

To find all 18 year old students, we can write:SELECT *FROM StudentsWHERE age=18

•To find just names and logins, replace the first line:

SELECT name, login

sid name login age gpa

53666 Jones jones@cs 18 3.4

53650 Smith smith@ee 18 3.2

10

Querying Multiple Relations

What does the following query compute?SELECT S.name, E.cid

FROM Students S, Enrolled EWHERE S.sid=E.sid AND E.grade=“A”

S.name E.cid

Smith Topology112

sid cid grade53831 Carnatic101 C53831 Reggae203 B53650 Topology112 A53666 History105 B

Given the following instance of Enrolled:

we get:

11

E/R to Relations: Summary

Entity set Relation M-N Relationship Relation (keys of related entities

plus relationship attributes) 1-M or M-1 Relationship: Table for entity with key

constraint gets key of other table plus relationship attributes

1-1 Relationship: Table for one entity gets key of the other plus relationship attributes

Powerful and natural relational query languages exist. The resultant relational schema may have some nasty

properties…. We’ll soon learn how to fix these.