ER Modeling
CIS 404- Spring 2015
ABSTRACTThis documents describes the process of drawing an ER diagram and implementing the corresponding databases in SQL Server.
Trent Tucker
1
ER – Entity Relationship Diagram
1. Entity is an object in the user’s world. An entity can be physical (Student) or conceptual (course). Entities have attributes, including a key attribute.
2. Entities are related to each other. There are three types of relationships: 1 to 1, 1 to M, and Many to Many.
3. ER Diagram is a data model which is used to capture the user’s requirements.
Rules for converting ER Diagram into Relational Diagram
1. Every entity becomes a table, every attribute becomes a field, and the key attribute becomes a primary key.
2. For every 1:1 and 1: N Relationships add the primary key of the parent entity to the child table as a foreign key.
3. For every N: M relationships create an intersection table. The keys of the two participating entities combined becomes the key of this table. The relationship of the relationships if aby becomes a field in this table.
1.
Tucker [Date]
2
The ER Diagram for the sales order case
This case has three entities.
1. Sales Order (SO_Number)2. Item(Item_Number and Item_Name)3. Customer(Cust_Code and Cust_Name)
Tucker [Date]
3
ER Diagram in SQL Data Modeler
Relational Models drawn in SQL Data Modeler
Tucker [Date]
This relationship has an attribute Qty Ordered
4
Relational Modeler in SQL Server
Tucker [Date]
5
DDL (Data Definition Language) Statements for Creating the Database in SQL Server
-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840
-- at: 2015-03-09 10:20:05 CDT
-- site: SQL Server 2008
-- type: SQL Server 2008
CREATE
TABLE Customer
(
Cust_Code VARCHAR (25) NOT NULL ,
Cust_Name VARCHAR (25) NOT NULL ,
CONSTRAINT Customer_PK PRIMARY KEY CLUSTERED (Cust_Code)
WITH
(
ALLOW_PAGE_LOCKS = ON ,
ALLOW_ROW_LOCKS = ON
)
ON "default"
)
ON "default"
GO
CREATE
TABLE Item
Tucker [Date]
6
(
Item_Number VARCHAR (25) NOT NULL ,
Item_Name VARCHAR (25) NOT NULL ,
CONSTRAINT Item_PK PRIMARY KEY CLUSTERED (Item_Number)
WITH
(
ALLOW_PAGE_LOCKS = ON ,
ALLOW_ROW_LOCKS = ON
)
ON "default"
)
ON "default"
GO
CREATE
TABLE Item_SO
(
SO_Order_SO_Number NUMERIC (6) NOT NULL ,
Item_Item_Number VARCHAR (25) NOT NULL ,
CONSTRAINT Relation_2__IDX PRIMARY KEY CLUSTERED (SO_Order_SO_Number,
Item_Item_Number)
WITH
(
ALLOW_PAGE_LOCKS = ON ,
ALLOW_ROW_LOCKS = ON
)
ON "default"
)
ON "default"
GO
CREATE
Tucker [Date]
7
TABLE SO_Order
(
SO_Number NUMERIC (6) NOT NULL ,
Customer_Cust_Code VARCHAR (25) NOT NULL ,
CONSTRAINT SO_Order_PK PRIMARY KEY CLUSTERED (SO_Number)
WITH
(
ALLOW_PAGE_LOCKS = ON ,
ALLOW_ROW_LOCKS = ON
)
ON "default"
)
ON "default"
GO
ALTER TABLE Item_SO
ADD CONSTRAINT FK_ASS_2 FOREIGN KEY
(
SO_Order_SO_Number
)
REFERENCES SO_Order
(
SO_Number
)
ON
DELETE
NO ACTION ON
UPDATE NO ACTION
GO
ALTER TABLE Item_SO
ADD CONSTRAINT FK_ASS_3 FOREIGN KEY
Tucker [Date]
8
(
Item_Item_Number
)
REFERENCES Item
(
Item_Number
)
ON
DELETE
NO ACTION ON
UPDATE NO ACTION
GO
ALTER TABLE SO_Order
ADD CONSTRAINT SO_Order_Customer_FK FOREIGN KEY
(
Customer_Cust_Code
)
REFERENCES Customer
(
Cust_Code
)
ON
DELETE
NO ACTION ON
UPDATE NO ACTION
GO
-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE 4
Tucker [Date]
9
-- CREATE INDEX 0
-- ALTER TABLE 3
-- CREATE VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 0
-- ALTER TRIGGER 0
-- CREATE DATABASE 0
-- CREATE DEFAULT 0
-- CREATE INDEX ON VIEW 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE ROLE 0
-- CREATE RULE 0
-- CREATE PARTITION FUNCTION 0
-- CREATE PARTITION SCHEME 0
--
-- DROP DATABASE 0
--
-- ERRORS 0
-- WARNINGS 0
Tucker [Date]
10
USERS’ REQUIREMENTS
THE MOVIE DATABASE
“I’m the owner of a small movie rental store. We have over 3,000 movies that we need to keep track of. Each of our movies has a DVD or VHS tape number. For each movie, we need to know its title and category (e.g., comedy, suspense, drama, action, war, or sci-fi).
Yes, we do have multiple copies of many of our movies. We give each movie a specific ID, and
then track which DVD or VHS contains the movie. A movie can be either DVD or VHS format.
We always have at least one DVD or VHS tape for each movie we track, and each DVD or VHS
tape is always a copy of a single, specific movie.
Our DVDs and VHS tapes are very long. We don’t have any movies that require multiple DVDs or VHS tapes."
"We are frequently asked for movies starring specific actors. John Wayne and Julia Roberts are always popular. So we’d like to keep track of the star actors appearing in each movie. Not all of our movies have star actors. Customers like to know each actor’s “real” birth name and date of birth. We track only actors who appear in the movies in our inventory.
We have lots of customers. We rent videos only to people who have joined our 'video club.' To belong to our club, they must have good credit. For each club member, we’d like to keep their first and last name, current phone number, and current address. And, of course, each club member has a membership number.
Then we need to keep track of what video tapes each customer currently has checked out. A customer may check out multiple video tapes at any given time. We just track current rentals. We don’t keep track of any rental histories.”
We would like to keep the rental date/time and the return date/time. All our tapes are due back the next day, so we don’t need to keep a due date. Keeping this rental history will allow us to analyze the pattern of our rentals. We will be able to determine how many tapes each customer rents and how many times a customer has returned a tape late. We will also know how many times a particular tape has been used and will then know when to retire each tape. We will also be able to analyze our customers’ movie preferences.”
Tucker [Date]
11
Entities and their attributes
1. Movie (Movie Id, title, category)2. Membership ( Customer ID, First Name, Last name, Address, phone number)3. Rental(Rental Id, date and time issued, date and time retured)4. Copies (copy id, types)5. Actors ( ID, Actor Name, Date of Birth, Birth Name,)
Tucker [Date]
12
ER Diagram and Data Modeler
Tucker [Date]
13
Relational Modeler
Tucker [Date]
14
DDL Statements for Creating the database in SQL Server
-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840-- at: 2015-03-11 10:26:53 CDT
-- site: SQL Server 2008-- type: SQL Server 2008
CREATE TABLE Actors
( Actors_ID NUMERIC (6) NOT NULL ,
Actor_Name VARCHAR (25) NOT NULL , Birthname VARCHAR (25) ,
Birthdate DATETIME , CONSTRAINT Actors_PK PRIMARY KEY CLUSTERED (Actors_ID)
WITH (
ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
) ON "default"
GO
CREATE TABLE Copies
( Copy_ID NUMERIC (6) NOT NULL ,
Type CHAR (3) NOT NULL , Movie_Movie_ID NUMERIC (6) NOT NULL ,
CONSTRAINT Copies_PK PRIMARY KEY CLUSTERED (Copy_ID, Movie_Movie_ID)
WITH (
ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
Tucker [Date]
15
) ON "default"
GO
CREATE TABLE Member
( Member_ID NUMERIC (6) NOT NULL , F_Name VARCHAR (25) NOT NULL , L_Name VARCHAR (25) NOT NULL ,
Member_Address VARCHAR (30) NOT NULL , Phone CHAR (10) NOT NULL ,
CONSTRAINT Member_PK PRIMARY KEY CLUSTERED (Member_ID)WITH
( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
) ON "default"
GO
CREATE TABLE Moves_Actors
( Movie_Movie_ID NUMERIC (6) NOT NULL , Actors_Actors_ID NUMERIC (6) NOT NULL ,
CONSTRAINT Relation_5__IDX PRIMARY KEY CLUSTERED (Movie_Movie_ID,
Actors_Actors_ID)WITH
( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
) ON "default"
GO
CREATE TABLE Movie
Tucker [Date]
16
( Movie_ID NUMERIC (6) NOT NULL ,
Movie_Title VARCHAR (50) NOT NULL , Movie_Category VARCHAR (15) NOT NULL ,
CONSTRAINT Movie_PK PRIMARY KEY CLUSTERED (Movie_ID)WITH
( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
) ON "default"
GO
CREATE TABLE Rental
( Rental_ID NUMERIC (6) NOT NULL , DateIssued DATETIME NOT NULL ,
DateReturned DATETIME , Member_Member_ID NUMERIC (6) NOT NULL ,
CONSTRAINT Rental_PK PRIMARY KEY CLUSTERED (Rental_ID)WITH
( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
) ON "default"
GO
CREATE TABLE Rental_Copies
( Rental_Rental_ID NUMERIC (6) NOT NULL , Copies_Copy_ID NUMERIC (6) NOT NULL , Copies_Movie_ID NUMERIC (6) NOT NULL ,
CONSTRAINT Relation_2__IDX PRIMARY KEY CLUSTERED (Rental_Rental_ID,
Copies_Copy_ID, Copies_Movie_ID)WITH
Tucker [Date]
17
( ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON
) ON "default"
) ON "default"
GO
ALTER TABLE CopiesADD CONSTRAINT Copies_Movie_FK FOREIGN KEY
(Movie_Movie_ID
)REFERENCES Movie
(Movie_ID
)ON
DELETE NO ACTION ON
UPDATE NO ACTIONGO
ALTER TABLE Rental_CopiesADD CONSTRAINT FK_ASS_2 FOREIGN KEY
(Rental_Rental_ID
)REFERENCES Rental
(Rental_ID
)ON
DELETE NO ACTION ON
UPDATE NO ACTIONGO
ALTER TABLE Rental_CopiesADD CONSTRAINT FK_ASS_3 FOREIGN KEY
(Copies_Copy_ID,
Tucker [Date]
18
Copies_Movie_ID)
REFERENCES Copies(
Copy_ID ,Movie_Movie_ID
)ON
DELETE NO ACTION ON
UPDATE NO ACTIONGO
ALTER TABLE Moves_ActorsADD CONSTRAINT FK_ASS_5 FOREIGN KEY
(Movie_Movie_ID
)REFERENCES Movie
(Movie_ID
)ON
DELETE NO ACTION ON
UPDATE NO ACTIONGO
ALTER TABLE Moves_ActorsADD CONSTRAINT FK_ASS_6 FOREIGN KEY
(Actors_Actors_ID
)REFERENCES Actors
(Actors_ID
)ON
DELETE NO ACTION ON
UPDATE NO ACTIONGO
Tucker [Date]
19
ALTER TABLE RentalADD CONSTRAINT Rental_Member_FK FOREIGN KEY
(Member_Member_ID
)REFERENCES Member
(Member_ID
)ON
DELETE NO ACTION ON
UPDATE NO ACTIONGO
-- Oracle SQL Developer Data Modeler Summary Report: --
-- CREATE TABLE 7-- CREATE INDEX 0-- ALTER TABLE 6-- CREATE VIEW 0
-- CREATE PACKAGE 0-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0-- CREATE FUNCTION 0-- CREATE TRIGGER 0-- ALTER TRIGGER 0
-- CREATE DATABASE 0-- CREATE DEFAULT 0
-- CREATE INDEX ON VIEW 0-- CREATE ROLLBACK SEGMENT 0
-- CREATE ROLE 0-- CREATE RULE 0
-- CREATE PARTITION FUNCTION 0-- CREATE PARTITION SCHEME 0
-- -- DROP DATABASE 0
-- -- ERRORS 0
-- WARNINGS 0
Tucker [Date]
20
Relational Modeler in SQL Server
Tucker [Date]