DbLocal/mainDB.sql at master · ryanadair009/DbLocal · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
/*
// Author: Matt Wallace
// Last Edited: 04/28/2015
// I promise this is my code.
// Description:
// Resets the database, loads in default data
*/
drop table if exists Reservation;
drop table if exists MovieShowing;
drop table if exists Theater;
drop table if exists Cinema;
drop table if exists Movie;
drop table if exists Member;
drop table if exists Membership;


create table Cinema
(
	ID int(11) NOT NULL auto_increment,
	Name varchar(256) NOT NULL,
	Address varchar(256) NOT NULL,
	PhoneNumber varchar(256) NOT NULL,

	primary key(ID)
)

CHARACTER SET = utf8 engine = InnoDB;

create table Theater
(
	ID int(11) NOT NULL auto_increment,
	CinemaID int(11) NOT NULL,
	TheaterNumber int(11) NOT NULL,
	SeatingRows int(11) NOT NULL,
	SeatingColumns int(11) NOT NULL,
	Capacity int(11) NOT NULL,
	primary key(ID),
	foreign key(CinemaID) references Cinema(ID) ON DELETE CASCADE
)

CHARACTER SET = utf8 engine = InnoDB;

create table Movie
(
	ID int(11) NOT NULL auto_increment,
	Title varchar(256) NOT NULL,
	Stars varchar(1024), /*serialized 1d array */
	Description varchar(256) NOT NULL,
	RunningTimeMinutes int(11) NOT NULL,
	Rating varchar(256) NOT NULL,
	primary key(ID)

)

CHARACTER SET = utf8 engine = InnoDB;

create table MovieShowing
(
	ID int(11) NOT NULL auto_increment,
	CinemaID int(11) NOT NULL,
	TheaterID int(11) NOT NULL,
	MovieID int(11) NOT NULL,
	SeatingChart varchar (65536), /*serialized 2d array */
	ShowDate date NOT NULL,
	ShowTime time NOT NULL,
	SeatsAvailable int(11) NOT NULL,
	primary key(ID),
	foreign key(CinemaID) references Cinema(ID) ON DELETE CASCADE,
	foreign key(TheaterID) references Theater(ID) ON DELETE CASCADE,
	foreign key(MovieID) references Movie(ID) ON DELETE CASCADE
)

CHARACTER SET = utf8 engine = InnoDB;

create table Membership
(
	AcctNum int(11) NOT NULL auto_increment,
	PrimaryMemberID int(11) NOT NULL,
	StartDate date NOT NULL,
	EndDate date NOT NULL,
	primary key(AcctNum)
)

CHARACTER SET = utf8 engine = InnoDB;

create table Member
(
	ID int(11) NOT NULL auto_increment,
	MemberAcctNum int(11),
	MemberAcctOrder int(11),
	Name varchar(256) NOT NULL,
	Address varchar(256) NOT NULL,
	Email varchar(255) NOT NULL,
	PhoneNumber varchar(256) NOT NULL,
	Age int(11) NOT NULL,
	primary key(ID),
	foreign key(MemberAcctNum) references Membership(AcctNum) ON DELETE CASCADE
)

CHARACTER SET = utf8 engine = InnoDB;


create table Reservation
(
	ID int(11) NOT NULL auto_increment,
	MemberID int(11) NOT NULL,
	MembershipID int(11) NOT NULL,
	MovieShowingID int(11) NOT NULL,
	SeatRow int(11) NOT NULL,
	SeatColumn int(11) NOT NULL,
	primary key(ID),
	foreign key(MemberID) references Member(ID) ON DELETE CASCADE,
	foreign key(MovieShowingID) references MovieShowing(ID) ON DELETE CASCADE
)

CHARACTER SET = utf8 engine = InnoDB;

LOAD DATA LOCAL INFILE 'Cinema.data'
REPLACE
INTO TABLE Cinema
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE 'Theater.data'
REPLACE
INTO TABLE Theater
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE 'Movie.data'
REPLACE
INTO TABLE Movie
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE 'MovieShowing.data'
REPLACE
INTO TABLE MovieShowing
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE 'Membership.data'
REPLACE
INTO TABLE Membership
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE 'Member.data'
REPLACE
INTO TABLE Member
FIELDS TERMINATED BY ',';

LOAD DATA LOCAL INFILE 'Reservation.data'
REPLACE
INTO TABLE Reservation
FIELDS TERMINATED BY ',';