Data Management System Assignment: ER Diagram, Tables and Queries

This document presents a comprehensive solution for an advanced data management system assignment. It begins with an Entity Relationship (ER) Diagram illustrating the database structure, followed by detailed tables showcasing the data organization. Sample data is provided for each table, demonstrating how the database is populated. The core of the assignment focuses on various SQL queries, including pivot table queries, along with their corresponding SQL code. These queries demonstrate data retrieval, aggregation, and analysis across multiple tables, showcasing the practical application of SQL in data management. The queries cover diverse scenarios, such as summarizing data by gender, payment method, genre, and date ranges. This assignment effectively demonstrates a practical understanding of database design, SQL querying, and data analysis techniques.

0
ADVANCED DATA MANAGEMENT SYSTEM

Table of Contents
1. ER Diagram.................................................................................................................................2
2. Tables............................................................................................................................................2
3. Sample Data.................................................................................................................................5
4. Queries........................................................................................................................................10

1. ER Diagram
The Entity Relationship Diagram is shown in below.
2. Tables
The tables are listed below.

3. Sample Data
The Sample data’s are inserted in the each table. It is shown in below.
tblCinema
CinemaID Address Date HallID
CI100 USA 08-09-
2011
HL100
CI101 US 17-11- HL101

2014
CI102 USA 18-10-
2013
HL102
CI103 Chennai 25-10-
2015
HL103
CI104 Sydney 04-11-
2015
HL104
CI105 North Sydney 19-11-
2015
HL105
tblCustomer
CustomerI
D
Name DOB Address Gende
r
TransactionID
CU100 Sam Joel 12-08-2014 USA Male TR100
CU101 John Sam 17-04-2013 US Male TR101
CU102 Joseph Raj 15-01-2014 Sydney Male TR102
CU103 Jenniffer 22-01-2015 USA Female TR103
CU104 Mirachlin 18-02-2015 Sydney Female TR104
CU105 Mickle 17-07-2014 US Male TR105
tblDirector
DirectorID Name DOB Gender
DI100 Sam Joel 12-06-1960 Male
DI101 Jennifer 13-04-1980 Female
DI102 Mackle 03-07-1981 Male
DI103 Joseph 23-04-1978 Male
DI104 John 16-04-1972 Male
DI105 Maclin 13-07-1980 Female
tblHall
HallID Size ShowingID

HL100 100 SH100
HL101 100 SH101
HL102 100 SH102
HL103 100 SH103
HL104 100 SH104
HL105 100 SH105
tblMovie
MovieID ReleaseDate Languag
e
Cost Country Price StarID DirectorID
MV100 02-12-2011 English ₹
100.00
US ₹
300.00
ST100 DI100
MV101 03-12-2013 English ₹
180.00
USA ₹
500.00
ST101 DI101
MV102 18-11-2013 Hindi ₹
200.00
Sydney ₹
400.00
ST102 DI102
MV103 30-11-2015 English ₹
250.00
US ₹
500.00
ST103 DI103
MV104 22-11-2014 Hindi ₹
300.00
USA ₹
600.00
ST104 DI104
MV105 23-11-2012 English ₹
500.00
US ₹
800.00
ST105 DI105
MV106 30-11-2015 English ₹
300.00
Sydney ₹
500.00
ST105 DI101
tblOfflineTransaction
OfflineTransactionID CinemaID
OF100 CI100
OF101 CI101
OF102 CI102
OF103 CI103

OF104 CI104
OF105 CI105
tblOnlineTransaction
OnlineTransactionI
D
System Browser
ON100 Yes No
ON101 No Yes
ON102 Yes No
ON103 No Yes
ON104 Yes No
ON105 Yes No
tblPromotion
PromotionID Description Discount StartDate EndDate
PR100 Satisfied 40% 05-10-2013 05-11-2013
PR101 Good 40% 12-10-2011 10-11-2011
PR102 Not Bad 40% 11-10-2012 11-11-2012
PR104 Best 40% 18-10-2014 19-11-2015
PR105 Satisfied 40% 16-11-2012 19-11-2013
tblShowing
ShowingID Date Time MovieID
SH100 05-11-
2013
11:30:00 MV100
SH101 11-11-
2011
10:30:00 MV101
SH102 18-11-
2012
09:30:00 MV102
SH103 24-11-
2013
08:30:00 MV103

SH104 25-11-
2014
11:30:00 MV104
SH105 30-11-
2015
10:30:00 MV105
tblStar
StarID Name DOB Gender
ST100 Sam Joel 12-05-1980 Male
ST101 John Sam 12-04-1967 Male
ST102 Joseph Raj 09-03-1970 Male
ST103 Jenniffer 05-04-1965 Female
ST104 Mirachlin 09-08-1980 Female
ST105 Mickle 09-03-1967 Male
tblTicket
TicketID Ro
w
Seat Price ShowingID
TC100 H 15 ₹
500.00
SH100
TC101 G 23 ₹
300.00
SH101
TC102 K 20 ₹
200.00
SH102
TC103 A 19 ₹
250.00
SH103
TC104 B 21 ₹
300.00
SH104
TC105 C 20 ₹
450.00
SH105
tblTransaction

Transac
tionID
D
at
e
Tim
e
Total
Price
PayM
ethod
OnlineTrans
actionID
OfflineTran
sactionID
Tick
etID
Promot
ionID
TR100 05
-
10
-
20
17
11:5
0:00
₹
800.0
0
Online ON100 OF100 TC1
00
PR100
TR101 12
-
11
-
20
17
10:3
0:00
₹
500.0
0
Offline ON101 OF101 TC1
01
PR101
TR102 19
-
11
-
20
17
12:4
5:00
₹
300.0
0
Online ON102 OF102 TC1
02
PR102
TR103 16
-
11
-
20
17
03:4
5:00
₹
900.0
0
Online ON103 OF103 TC1
03
PR104
TR104 23
-
11
-
20
17
12:5
0:00
₹
500.0
0
Offline ON104 OF104 TC1
04
PR105
TR105 30
-
11
-
20
17
12:1
0:00
₹
400.0
0
Offline ON105 OF105 TC1
05
PR102

tblGenre
GenreI
D
GenreName MovieID
GR100 Comedy MV100
GR101 Adventure MV101
GR102 Action MV102
GR103 Crime MV103
GR104 Drama MV104
GR105 Fantasy MV105
4. Queries
The Queries are listed below.
Query – 1
Pivot Table
Query- 1
Date Gender SumOfTotalPrice
05-10-2017 Male 800.00₹
12-11-2017 Male 500.00₹
16-11-2017 Female 900.00₹
19-11-2017 Male 300.00₹
23-11-2017 Female 500.00₹
30-11-2017 Male 400.00₹
3,400.00₹
Output