[go: up one dir, main page]
Scribd
Upload
49 views10 pages

ER Example

The document provides an example of converting an ER diagram into relational database tables using SQL statements. It includes the SQL to create tables for entities like Musicians, Instruments, and relationships like Plays. It also notes some constraints that cannot be captured in SQL.

Uploaded by

Dinesh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
49 views10 pages

ER Example

The document provides an example of converting an ER diagram into relational database tables using SQL statements. It includes the SQL to create tables for entities like Musicians, Instruments, and relationships like Plays. It also notes some constraints that cannot be captured in SQL.

Uploaded by

Dinesh
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

ER Design Example

Murat Kantarcioglu
ER Example
ER Example
address Phone-no

Place Home Telephone


name
ssn title
Album_id
Lives Date
Musician

Producer Album

Format

Plays
Perform Appears

Instrument Song

Inst-id key Name Song-id title Author


Converting ER Diagrams to
Relational Database Tables
• Consider the Notown database from Exercise
2.5. You have decided to recommend that
Notown use a relational database system to
store company data. Show the SQL statements
for creating relations corresponding to the entity
sets and relationship sets in your design. Identify
any constraints in the ER diagram that you are
unable to capture in the SQL statements and
briefly explain why you could not express them.
Relational Tables
• CREATE TABLE Musicians (
ssn CHAR(10),
name CHAR(30),
PRIMARY KEY (ssn))

• CREATE TABLE Instruments ( instrId


CHAR(10),
dname CHAR(30),
key CHAR(5),
PRIMARY KEY (instrId))
Relational Tables
• CREATE TABLE Plays ( ssn CHAR(10),
instrId INTEGER,
PRIMARY KEY (ssn, instrId),
FOREIGN KEY (ssn) REFERENCES Musicians,
FOREIGN KEY (instrId) REFERENCES Instruments )

• CREATE TABLE Songs-Appears ( songId INTEGER,


author CHAR(30),
title CHAR(30),
albumIdentifier INTEGER NOT NULL,
PRIMARY KEY (songId),
FOREIGN KEY (albumIdentifier) References Album
Producer),
Relational Tables
• CREATE TABLE Telephone Home (
phone CHAR(11),
address CHAR(30),
PRIMARY KEY (phone),
FOREIGN KEY (address) REFERENCES
Place)
Relational Tables
• CREATE TABLE Lives ( ssn CHAR(10),
phone CHAR(11),
FOREIGN KEY (phone)
References TelephoneHome,
FOREIGN KEY (ssn) REFERENCES Musicians )

• CREATE TABLE Place ( address CHAR(30),


PRIMARY KEY (address) )
Relational Tables
• CREATE TABLE Perform ( songId INTEGER,
ssn CHAR(10),
PRIMARY KEY (ssn, songId),
FOREIGN KEY (songId) REFERENCES Songs,
FOREIGN KEY (ssn) REFERENCES Musicians )

• CREATE TABLE Album Producer ( albumIdentifier INTEGER,


ssn CHAR(10),
copyrightDate DATE,
speed INTEGER,
title CHAR(30),
PRIMARY KEY (albumIdentifier),
FOREIGN KEY (ssn) REFERENCES Musicians )

You might also like