Case Sample: Cinema Application, ER to Relational Model mapping and SQL implementation

Cinema App ER Diagrams

We have already covered a topic regarding the design of ER diagrams for a Cinema. We have tried to capture as many relationships as possible. Please, refer to this topic so you can have an idea about our first post regarding the Cinema Application.

http://smartnoob.blogspot.com/2013/03/case-sample-cinema-application-er.html






In this topic we will cover up the conversion from ER to Relational Model and the SQL implementation.

Conversion from ER diagrams to Relational Model

Conversion from ER model to another step which is more advanced logically is crucial in Database Design. And this advanced logical step is called Relational Schema.

Let's start with the conversions.

Movies(MovieID,Name,Genre,Director,Language,ReleaseDate)

Actors(AID,Name,Surname,BirthDate,Address)

The relationship between Movie and Actors transforms in a new relationship because the relationship between them is many to many and at MovieID we discern the NULL value.

Performs(MovieID,AID) 

where MovieID and AID are both Primary Key and Foreign Keys at the Performs Relationship.

Clients(ClientID,ClientType) && HasPrice(MovieID,ClientID,Price) 

where MovieID and ClientID are both primary and foreign keys.

The Entity Hall transforms into a relationship Hall(HallID,Capacity,Type)

Hall is in relationship with the Seat entity where seats have a must participation in the relationship so this transformation happens by incorporating the relationship into the Seat entity and discerning the NULL value.

Seat(SeatID ,Type,Position,HallID)  

The Movie, Hall and Seat entities participate in a relationship and the relationship is many to many so we are obliged to create a new relationship for Plays.

Plays(MovieID,HallID,Start,End)   

The primary keys of Movie and Hall entities become foreign keys at the Plays relationship and meantime Primary Keys. Just because at the Cinema we have prices for static tickets we create another entity Ticket with ticket number and the price where the ticket number is Primary Key.

Reservation(Tno,MovieID,HallID,SeatID)

Relational Schema of ER Diagram

Movie(MovieID,Name,Genre,Language,Director,ReleaseDate)

Clients(ClientID,ClientType)

HasPrice(MovieID,ClientID,Price)

 Actors(AID,Name,Surname,BirthDate,Address)

Performs(MovieID,AID) 

Hall(HallID,Capacity,Type)

Seat(SeatID,Type,Position,HallID)

Plays(MovieID,HallID,Start,End)

Reservation(Tno,MovieID,HallID,SeatID,EID,ClientID)

Employers(EID,Name,Surname,PhoneAddress,Username,Password,Category)

Now, since we mapped the ER diagrams into the relational model we start creating the actual SQL implementation. The implementation was done on MSSQL, but this doesn't matter much since MSSQL uses SQL standard and it has only minimal differences from other DBMS.

CREATE TABLE tblMovies
(
MovieID char(50),
Name char(50),
Genre char(50),
Language char(50),
Director char(50),
ReleaseDate varchar(50),
Cost int,
Picture varchar(50)
PRIMARY KEY(MovieID)
)


CREATE TABLE tblActors
(
AID char(10),
AName char(20),
ASurname char(20),
ABirthdate datetime,
Address char(20),

PRIMARY KEY(AID)
)

CREATE TABLE tblPerforms
(
ShowID char(10),
AID char(10),

PRIMARY KEY(ShowID,AID),
FOREIGN KEY(ShowID) REFERENCES tblShow(ShowID) ,
FOREIGN KEY(AID) REFERENCES tblActors(AID)
)


CREATE TABLE tblHasPrice
(
MovieID varchar(50),
ClientID int,

PRIMARY KEY(MovieID,ClientID),
FOREIGN KEY(MovieID) REFERENCES tblMovies ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(ClientID) REFERENCES tblClients ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE tblHall
(
HallID varchar(50),
Capacity varchar(50),
Type varchar(50),

PRIMARY KEY(HallID)
)


CREATE TABLE tblSeat
(
SeatID char(10),
SType char(10),
SPosition char(10),
HallID char(10) NOT NULL,

PRIMARY KEY(SeatID),
FOREIGN KEY(HallID) REFERENCES tblHall(HallID) ON DELETE CASCADE ON UPDATE CASCADE
)


CREATE TABLE tblPlays
(
PlaysID int identity(1,1) NOT NULL,
ShowID varchar(50),
HallID varchar(50),
StartTime varchar(50),
EndTime varchar,

PRIMARY KEY(ShowID,HallID),
FOREIGN KEY(ShowID) REFERENCES tblShow,

CREATE TABLE tblPlays
(
PlaysID int identity(1,1) NOT NULL,
ShowID varchar(50),
HallID varchar(50),
StartTime varchar(50),
EndTime varchar,

PRIMARY KEY(ShowID,HallID),
FOREIGN KEY(ShowID) REFERENCES tblShow,
)


CREATE TABLE tblEmployers
(
EID int identity(1,1) NOT NULL,
Name varchar(50),
Surname varchar(50),
Phone varchar(50),
Address varchar(50),
Username varchar(50),
Password varchar(50),
Category varchar(50),

PRIMARY KEY(EID)
)

CREATE TABLE tblSeatReservation
(
TicketNumber int identity(1,1) NOT NULL,
PlaysID int NOT NULL,
SeatID int NOT NULL,
ReservedBy varchar(50),
ClientID int,

PRIMARY KEY(PlaysID,SeatID),
FOREIGN KEY(PlaysID) REFERENCES tblPlays,
FOREIGN KEY(SeatID) REFERENCES tblSeats,
FOREIGN KEY(ReservedBy) REFERENCES tblEmployers(Username),
FOREIGN KEY(ClientID) REFERENCES tblClients
)

And, finally this is the snapshot of the Database Diagrams implemented in the MSSQL 2008.

Browse More Articles

Tweet

Written by Leo

Thank you for taking your time to browse through our blog. We try to build as much rich content base as we can. We focus in modern technologies, computer science design and business.