· 8 years ago · Aug 12, 2018, 02:40 PM
1USE master
2GO
3if exists (select * from sysdatabases where name='movies')
4 DROP DATABASE movies
5GO
6
7CREATE DATABASE movies
8GO
9USE movies
10GO
11
12----- Create Tables -----
13
14CREATE TABLE MOVIE (
15 TITLE VARCHAR(255) NOT NULL,
16 YEAR INTEGER NOT NULL,
17 LENGTH INTEGER,
18 INCOLOR CHAR(1),
19 STUDIONAME CHAR(50),
20 PRODUCERC# INTEGER
21);
22
23CREATE TABLE MOVIESTAR (
24 NAME CHAR(30) NOT NULL,
25 ADDRESS VARCHAR(255),
26 GENDER CHAR(1),
27 BIRTHDATE DATETIME
28);
29
30CREATE TABLE STARSIN (
31 MOVIETITLE VARCHAR(255) NOT NULL,
32 MOVIEYEAR INTEGER NOT NULL,
33 STARNAME CHAR(30) NOT NULL
34);
35
36CREATE TABLE MOVIEEXEC (
37 CERT# INTEGER NOT NULL,
38 NAME CHAR(30),
39 ADDRESS VARCHAR(255),
40 NETWORTH INTEGER
41);
42
43CREATE TABLE STUDIO (
44 NAME CHAR(50) NOT NULL,
45 ADDRESS VARCHAR(255),
46 PRESC# INTEGER
47);
48
49----- Create Constraints -----
50ALTER TABLE MOVIE ADD CONSTRAINT PK_MOVIE PRIMARY KEY(TITLE,YEAR);
51
52ALTER TABLE MOVIESTAR ADD CONSTRAINT PK_MOVIESTAR PRIMARY KEY(NAME);
53
54ALTER TABLE MOVIEEXEC ADD CONSTRAINT PK_MOVIEEXEC PRIMARY KEY(CERT#);
55
56ALTER TABLE STUDIO ADD CONSTRAINT PK_STUDIO PRIMARY KEY(NAME);
57
58ALTER TABLE STARSIN ADD CONSTRAINT PK_STARSIN PRIMARY KEY(MOVIETITLE,MOVIEYEAR,STARNAME);
59
60ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_MOVIEEXEC FOREIGN KEY(PRODUCERC#) REFERENCES MOVIEEXEC(CERT#);
61
62ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_STUDIO FOREIGN KEY(STUDIONAME) REFERENCES STUDIO(NAME);
63
64ALTER TABLE STARSIN ADD CONSTRAINT FK_STARSIN_MOVIE FOREIGN KEY(MOVIETITLE, MOVIEYEAR) REFERENCES MOVIE(TITLE, YEAR);
65
66ALTER TABLE STARSIN ADD CONSTRAINT FK_STARSIN_MOVIESTAR FOREIGN KEY(STARNAME) REFERENCES MOVIESTAR(NAME);
67
68------- Insert Studio ------------
69INSERT INTO STUDIO
70 VALUES ('Disney','500 S. Buena Vista Street',1);
71
72INSERT INTO STUDIO
73 VALUES ('USA Entertainm.','', 2);
74
75INSERT INTO STUDIO
76 VALUES ('Fox','10201 Pico Boulevard', 3);
77
78INSERT INTO STUDIO
79 VALUES ('Paramount','5555 Melrose Ave', 4);
80
81INSERT INTO STUDIO
82 VALUES ('MGM','MGM Boulevard', 5);
83
84------- Insert MovieExec ------------
85INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT#, NETWORTH)
86 VALUES ('George Lucas', 'Oak Rd.', 555, 200000000);
87
88INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT#, NETWORTH)
89 VALUES ('Ted Turner', 'Turner Av.', 333, 125000000);
90
91INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT#, NETWORTH)
92 VALUES ('Stephen Spielberg', '123 ET road', 222, 100000000);
93
94INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT#, NETWORTH)
95 VALUES ('Merv Griffin', 'Riot Rd.', 199, 112000000);
96
97INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT#, NETWORTH)
98 VALUES ('Calvin Coolidge', 'Fast Lane', 123, 20000000);
99
100------- Insert Movie ------------
101INSERT INTO MOVIE
102 VALUES ('Pretty Woman', 1990, 119, 'Y', 'Disney', 199);
103
104INSERT INTO MOVIE
105 VALUES ('The Man Who Wasn''t There', 2001, 116, 'N', 'USA Entertainm.',
106 555);
107
108INSERT INTO MOVIE
109 VALUES ('Logan''s run', 1976, NULL, 'Y', 'Fox', 333);
110
111INSERT INTO MOVIE
112 VALUES ('Star Wars', 1977, 124, 'Y', 'Fox', 555);
113
114INSERT INTO MOVIE
115 VALUES ('Empire Strikes Back', 1980, 111, 'Y', 'Fox', 555);
116
117INSERT INTO MOVIE
118 VALUES ('Star Trek', 1979, 132, 'Y', 'Paramount', 222);
119
120INSERT INTO MOVIE
121 VALUES ('Star Trek: Nemesis', 2002, 116, 'Y', 'Paramount', 123);
122
123INSERT INTO MOVIE
124 VALUES ('Terms of Endearment', 1983, 132, 'Y', 'MGM', 123);
125
126INSERT INTO MOVIE
127 VALUES ('The Usual Suspects', 1995, 106, 'Y', 'MGM', 199);
128
129INSERT INTO MOVIE
130 VALUES ('Gone With the Wind', 1938, 238, 'Y', 'MGM', 123);
131
132------- Insert MovieStar ------------
133
134INSERT INTO MOVIESTAR
135 VALUES ('Jane Fonda', 'Turner Av.', 'F', '1977-07-07');
136
137INSERT INTO MOVIESTAR
138 VALUES ('Alec Baldwin', 'Baldwin Av.', 'M', '1977-07-06');
139
140INSERT INTO MOVIESTAR
141 VALUES ('Kim Basinger', 'Baldwin Av.', 'F', '1979-07-05');
142
143INSERT INTO MOVIESTAR
144 VALUES ('Harrison Ford', 'Prefect Rd.', 'M', '1955-05-05');
145
146INSERT INTO MOVIESTAR
147 VALUES ('Debra Winger', 'A way', 'F', '1978-06-05');
148
149INSERT INTO MOVIESTAR
150 VALUES ('Jack Nicholson', 'X path', 'M', '1949-05-05');
151
152INSERT INTO MOVIESTAR
153 VALUES ('Sandra Bullock', 'X path', 'F', '1948-12-05');
154
155------- Insert StarsIn ------------
156INSERT INTO STARSIN
157 VALUES ('Star Wars', 1977, 'Kim Basinger');
158
159INSERT INTO STARSIN
160 VALUES ('Star Wars', 1977, 'Alec Baldwin');
161
162INSERT INTO STARSIN
163 VALUES ('Star Wars', 1977, 'Harrison Ford');
164
165INSERT INTO STARSIN
166 VALUES ('Empire Strikes Back', 1980, 'Harrison Ford');
167
168INSERT INTO STARSIN
169 VALUES ('The Usual Suspects', 1995, 'Jack Nicholson');
170
171INSERT INTO STARSIN
172 VALUES ('Terms of Endearment', 1983, 'Jane Fonda');
173
174INSERT INTO STARSIN
175 VALUES ('Terms of Endearment', 1983, 'Jack Nicholson');
176
177INSERT INTO STARSIN
178 VALUES ('The Usual Suspects', 1995, 'Sandra Bullock')