Databases/week3.sql at main · Karam19/Databases · 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
CREATE TABLE "Movies" (
  "title" varchar,
  "year_of_release" datetime,
  "length" timestamp,
  "genres_ID" int,
  "plot_outline" varchar,
  PRIMARY KEY ("title", "year_of_release")
);

CREATE TABLE "Production_company" (
  "name" varchar,
  "address" varchar,
  PRIMARY KEY ("name", "address")
);

CREATE TABLE "Movie_prod_com" (
  "ID" int PRIMARY KEY,
  "com_name" varchar,
  "com_add" varchar,
  "mov_title" varchar,
  "mov_yor" datetime
);

CREATE TABLE "Actor" (
  "name" varchar,
  "date_of_birth" date,
  PRIMARY KEY ("name", "date_of_birth")
);

CREATE TABLE "Actor_Movie" (
  "ID" int PRIMARY KEY,
  "ac_name" varchar,
  "ac_dob" date,
  "mov_title" varchar,
  "mov_yor" datetime
);

CREATE TABLE "Director" (
  "name" varchar,
  "date_of_birth" date,
  PRIMARY KEY ("name", "date_of_birth")
);

CREATE TABLE "Director_Movie" (
  "ID" int PRIMARY KEY,
  "ac_name" varchar,
  "ac_dob" date,
  "mov_title" varchar,
  "mov_yor" datetime
);

CREATE TABLE "Person" (
  "name" varchar,
  "date_of_birth" date
);

CREATE TABLE "Quotes" (
  "ID" int PRIMARY KEY,
  "mov_title" varchar,
  "mov_yor" datetime,
  "ac_name" varchar,
  "ac_dob" date,
  "Quote" varchar
);

CREATE TABLE "Genre" (
  "genres_ID" int PRIMARY KEY,
  "genre" varchar
);

ALTER TABLE "Genre" ADD FOREIGN KEY ("genres_ID") REFERENCES "Movies" ("genres_ID");

ALTER TABLE "Movie_prod_com" ADD FOREIGN KEY ("com_name") REFERENCES "Production_company" ("name");

ALTER TABLE "Movie_prod_com" ADD FOREIGN KEY ("com_add") REFERENCES "Production_company" ("address");

ALTER TABLE "Movies" ADD FOREIGN KEY ("title") REFERENCES "Movie_prod_com" ("mov_title");

ALTER TABLE "Movies" ADD FOREIGN KEY ("year_of_release") REFERENCES "Movie_prod_com" ("mov_yor");

ALTER TABLE "Actor_Movie" ADD FOREIGN KEY ("ac_name") REFERENCES "Actor" ("name");

ALTER TABLE "Actor_Movie" ADD FOREIGN KEY ("ac_dob") REFERENCES "Actor" ("date_of_birth");

ALTER TABLE "Actor_Movie" ADD FOREIGN KEY ("mov_title") REFERENCES "Movies" ("title");

ALTER TABLE "Actor_Movie" ADD FOREIGN KEY ("mov_yor") REFERENCES "Movies" ("year_of_release");

ALTER TABLE "Director_Movie" ADD FOREIGN KEY ("ac_name") REFERENCES "Director" ("name");

ALTER TABLE "Director_Movie" ADD FOREIGN KEY ("ac_dob") REFERENCES "Director" ("date_of_birth");

ALTER TABLE "Director_Movie" ADD FOREIGN KEY ("mov_title") REFERENCES "Movies" ("title");

ALTER TABLE "Director_Movie" ADD FOREIGN KEY ("mov_yor") REFERENCES "Movies" ("year_of_release");

ALTER TABLE "Person" ADD FOREIGN KEY ("name") REFERENCES "Actor" ("name");

ALTER TABLE "Person" ADD FOREIGN KEY ("name") REFERENCES "Director" ("name");

ALTER TABLE "Person" ADD FOREIGN KEY ("date_of_birth") REFERENCES "Actor" ("date_of_birth");

ALTER TABLE "Person" ADD FOREIGN KEY ("date_of_birth") REFERENCES "Director" ("date_of_birth");

ALTER TABLE "Quotes" ADD FOREIGN KEY ("mov_title") REFERENCES "Movies" ("title");

ALTER TABLE "Quotes" ADD FOREIGN KEY ("mov_yor") REFERENCES "Movies" ("year_of_release");

ALTER TABLE "Quotes" ADD FOREIGN KEY ("ac_name") REFERENCES "Actor" ("name");

ALTER TABLE "Quotes" ADD FOREIGN KEY ("ac_dob") REFERENCES "Actor" ("date_of_birth");