-
Notifications
You must be signed in to change notification settings - Fork 0
/
PurpleBoxDVD.sql
133 lines (119 loc) · 3.36 KB
/
PurpleBoxDVD.sql
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
Use master;
-- drop PurpleBoxDVD database if it exists
If EXISTS (SELECT * from sys.sysdatabases where name = 'PurpleBoxDVD')
Drop DATABASE PurpleBoxDVD;
--create PurpleBoxDVD database
Create [PurpleBoxDVD]
On Primary
(NAME = N'PurpleBoxDVD' , FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\PurpleBoxDVD.mdf',
SIZE = 5120KB, FILEGROWTH = 1024KB)
LOG ON
(NAME = N'PurpleBoxDVDLog' , FILENAME =
N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\PurpleBoxDVDLog.ldf'
SIZE = 2048KB, FILEGROWTH = 10%);
Go
--Attach to New Database
Use PurpleBoxDVD;
----------------Drop all tables if they exist------------------------
If EXISTS (SELECT * FROM sys.tables WHERE NAME = N'PbUser')
DROP TABLE PbUser;
-- We do this for all tables in the database
------------------------Create ALL TABLES----------------------------
CREATE TABLE PbUser
(
pbUser_id int IDENTITY(1,1) NOT NULL
pbUserID nvarchar(25) NOT NULL,
userFirstName nvarchar(25) NOT NULL,
userLastName nvarchar(25) NOT NULL,
userType nvarchar(1) NOT NULL,
userPassword nvarchar(20) NOT NULL,
secQuestion nvarchar(255) NOT NULL,
secAnswer nvarchar(255) NOT NULL,
fees double(4,2) NULL,
userStatus nvarchar(1) NOT NULL,
userPrimaryPhone nvarchar(12) NOT NULL,
userOtherPhone nvarchar(12) NULL,
custType nvarchar(1) NOT NULL
);
Create TABLE PbMovie
(
pbMovie_id int IDENTITY(1,1) NOT NULL,
pbMovieID nvarchar(25) NOT NULL,
movieTitle nvarchar(255) NOT NULL,
keywords nvarchar(255) NULL,
genre_id int NOT NULL,
lastChangedBy nvarchar(25) NOT NULL,
lastChangedOn nvarchar(25) NOT NULL
);
Create TABLE PbMovieGenre
(
pbMovie_id int NOT NULL,
pbGenre_id int NOT NULL
);
Create TABLE PbGenres
(
pbGenre_id int IDENTITY (1,1) NOT NULL,
genre nvarchar(20) NOT NULL
);
Create TABLE PbMovieActor
(
pbMovie_id int NOT NULL,
pbActor_id int NOT NULL
);
Create TABLE PbMovieDirector
(
pbMovie_id int NOT NULL,
pbDirector_id int NOT NULL
);
Create TABLE PbActor
(
pbActor_id int IDENTITY (1,1) NOT NULL,
actorFirstName nvarchar (25) NOT NULL,
actorMiddleName nvarchar (25) NULL,
actorLastName nvarchar (25) NOT NULL
);
Create TABLE PbDirector
(
pbDirector_id int IDENTITY (1,1) NOT NULL,
directorFirstName nvarchar (25) NOT NULL,
directorMiddleName nvarchar (25) NULL,
directorLastName nvarchar (25) NOT NULL
);
Create TABLE PbMovieRequest
(
pbMovieRequest_id int IDENTITY (1,1) NOT NULL,
pbUser_id int NOT NULL,
pbMovie_id int NOT NULL,
MovieRequestDate DateTime NOT NULL,
MovieFormat varchar(1) NOT NULL
);
Create TABLE PbMovieItem
(
pbMovie_id int NOT NULL,
pbCopyNum int IDENTITY (1,1) NOT NULL,
MovieFormat varchar(1) NOT NULL
);
Create TABLE PbRental
(
pbRental_id int IDENTITY (1,1) NOT NULL,
pbUser_id int NOT NULL,
pbMovie_id int NOT NULL,
pbCopyNum int NOT NULL,
rentalDate DateTime NOT NULL,
returnDate DateTime NULL
);
-----------------SET PK, AK, FK Conctraints---------------------
---PbUser Table---
--Primary Key
ALTER TABLE PbUser
ADD CONSTRAINT PK_PbUser
Primary KEY CLUSTERED (pbUser_id);
---Altername Key
ALTER TABLE PbUser
ADD CONSTRAINT AK_PbUser_pbUserID
UNIQUE(pbUserID);
----- Foreign Key ----------
ALTER TABLE pbMovieItem
ADD CONSTRAINT FK_PbMovieItem_pbMovie_id
FOREIGN KEY (pbMovie_id) REFERENCES PbMovie(pbMovie_id);