· 7 years ago · Jan 21, 2019, 07:16 PM
1DROP TABLE IF EXISTS Awarded_Movies
2DROP TABLE IF EXISTS Awards
3DROP TABLE IF EXISTS Nominated
4
5CREATE TABLE Nominated (
6 nominatedid char(1) NOT NULL,
7 nominated varchar(255) NOT NULL,
8 CONSTRAINT pk_Nominated PRIMARY KEY (nominatedid)
9)
10
11CREATE TABLE Awards (
12 award varchar(255) NOT NULL,
13 year integer NOT NULL,
14 country varchar(50) NOT NULL,
15 CONSTRAINT pk_Awards PRIMARY KEY (award),
16 CONSTRAINT ak_Awards UNIQUE (award, year),
17 CONSTRAINT fk_Awards_Country FOREIGN KEY (country)
18 REFERENCES Country(country_name)
19)
20
21CREATE TABLE Awarded_Movies (
22 award varchar(255) NOT NULL,
23 year integer NOT NULL,
24 category varchar(255) NOT NULL,
25 movie_id integer NOT NULL,
26 person_id integer NOT NULL,
27 nominatedid char(1) NOT NULL,
28 CONSTRAINT pk_Awarded_Movies PRIMARY KEY (award, year, category, movie_id, person_id),
29 CONSTRAINT fk_award FOREIGN KEY (award)
30 REFERENCES Awards(award),
31 CONSTRAINT fk_Award_Movie_Id FOREIGN KEY (movie_id)
32 REFERENCES Movie(movie_id),
33 CONSTRAINT fk_Award_Person_Id FOREIGN KEY (person_id)
34 REFERENCES Person(person_id),
35 CONSTRAINT fk_Award_NominatedId FOREIGN KEY (nominatedid)
36 REFERENCES Nominated(nominatedid)
37)
38
39INSERT INTO Person(person_id, lastname, firstname)
40 VALUES (950001, 'Rubel', 'Paul'),
41 (950002, 'Hymns', 'Richard'),
42 (950003, 'Davis', 'Dane' ),
43 (950004, 'Gaeta', 'John'),
44 (950005, 'Courley', 'Steve'),
45 (950006, 'Thum', 'Jon'),
46 (950007, 'Lee', 'David'),
47 (950008, 'Reitz' ,'John'),
48 (950009, 'Rudloff', 'Gregg'),
49 (950010, 'Campbell', 'David'),
50 (950011, 'Davis', 'Dane' ),
51 (950012, 'Bellfort', 'Tom' ),
52 (950013, 'Beemer', 'Bob'),
53 (950014, 'Pope','Bill'),
54 (950015, 'Hall', 'Conrad'),
55 (950016, 'Anwar', 'Tariq')
56
57
58INSERT INTO Nominated VALUES ('N', 'Nominated'),
59('W', 'Won')
60
61INSERT INTO Awards VALUES ('Academy Awards', 2000, 'Netherlands'),
62('BAFTA Awards', 2000, 'New Zealand')
63
64INSERT INTO Awarded_Movies VALUES ('Academy Awards', 2000, 'Best Film Editing', 207992, 550813, 'W'),
65('Academy Awards', 2000, 'Best Film Editing', 160492, 276884, 'N'),
66('Academy Awards', 2000, 'Best Film Editing', 160492, 950001, 'N'),
67('Academy Awards', 2000, 'Best Sound Effects Editing', 160492, 68042, 'N'),
68('Academy Awards', 2000, 'Best Sound Effects Editing', 303564, 54796, 'N'),
69('Academy Awards', 2000, 'Best Sound Effects Editing', 112290, 351397, 'N'),
70('Academy Awards', 2000, 'Best Visual Effects', 112290, 950002, 'N'),
71('Academy Awards', 2000, 'Best Visual Effects', 207992, 950003, 'W'),
72('Academy Awards', 2000, 'Best Visual Effects', 313474, 165453, 'N'),
73('Academy Awards', 2000, 'Best Visual Effects', 313474, 351981, 'N'),
74('Academy Awards', 2000, 'Best Visual Effects', 313474, 191712, 'N'),
75('Academy Awards', 2000, 'Best Visual Effects', 207992, 950004, 'W'),
76('Academy Awards', 2000, 'Best Visual Effects', 207992, 540853, 'W'),
77('Academy Awards', 2000, 'Best Visual Effects', 207992, 950005, 'W'),
78('Academy Awards', 2000, 'Best Visual Effects', 207992, 950006, 'W'),
79('BAFTA Awards', 2000, 'Best Sound', 207992, 950007, 'W'),
80('BAFTA Awards', 2000, 'Best Sound', 207992, 950008, 'W'),
81('BAFTA Awards', 2000, 'Best Sound', 207992, 950009, 'W'),
82('BAFTA Awards', 2000, 'Best Sound', 207992, 950010, 'W'),
83('BAFTA Awards', 2000, 'Best Sound', 207992, 950011, 'W'),
84('BAFTA Awards', 2000, 'Best Sound', 313474, 165453, 'N'),
85('BAFTA Awards', 2000, 'Best Sound', 313474, 950012, 'N'),
86('BAFTA Awards', 2000, 'Best Sound', 13789, 950013, 'N'),
87('BAFTA Awards', 2000, 'Best Cinematography', 207992, 950014, 'N'),
88('BAFTA Awards', 2000, 'Best Cinematography', 13789, 950015, 'W'),
89('BAFTA Awards', 2000, 'Best Production Design', 207992, 464716, 'N'),
90('BAFTA Awards', 2000, 'Best Production Design', 304862, 303143, 'W'),
91('BAFTA Awards', 2000, 'Best Editing', 207992, 550813, 'N'),
92('BAFTA Awards', 2000, 'Best Editing', 13789, 950016, 'W'),
93('BAFTA Awards', 2000, 'Best Editing', 13789, 283753, 'W')