Yurttas/PL/DBL/mssql/F/01/Movies/C-all.sql

 1/*
 2REM
 3REM C-all.sql
 4REM
 5REM Create Movie database tables.
 6REM
 7*/
 8
 9
10/*
11REM
12REM
13REM MovieExec-cre.sql
14REM
15REM
16*/
17
18CREATE TABLE MovieExec(name VARCHAR(24),
19                       address VARCHAR(24),
20                       cn INTEGER NOT NULL,
21                       networth INTEGER,
22           PRIMARY KEY(cn));
23
24/*
25REM
26REM
27REM Studio-cre.sql
28REM
29REM
30*/
31
32CREATE TABLE Studio(name VARCHAR(36) NOT NULL,
33                    address VARCHAR(24),
34                    prescn INTEGER NOT NULL,
35        PRIMARY KEY(name),
36        FOREIGN KEY(prescn) REFERENCES MovieExec(cn));
37
38/*
39REM
40REM
41REM MovieStar-cre.sql
42REM
43REM
44*/
45
46CREATE TABLE MovieStar(name VARCHAR(24) NOT NULL,
47                       address VARCHAR(24),
48                       gender CHAR,
49                       birthdate CHAR(4),
50           PRIMARY KEY(name));
51
52/*
53REM
54REM
55REM Movie-cre.sql
56REM
57REM
58*/
59
60CREATE TABLE Movie(title VARCHAR(24) NOT NULL,
61                   year INTEGER NOT NULL,
62                   length INTEGER,
63                   filmtype VARCHAR(5),
64                   studioname VARCHAR(36) NOT NULL,
65                   producercn INTEGER NOT NULL,
66       PRIMARY KEY(title, year),
67       FOREIGN KEY(studioname) REFERENCES Studio(name),
68       FOREIGN KEY(producercn) REFERENCES MovieExec(cn));
69
70/*
71REM
72REM
73REM StarsIn-cre.sql
74REM
75REM
76*/
77
78CREATE TABLE StarsIn(title VARCHAR(24) NOT NULL,
79                     year INTEGER NOT NULL,
80                     name VARCHAR(24) NOT NULL,
81         PRIMARY KEY(title, year, name),
82         FOREIGN KEY(title, year) REFERENCES Movie(title, year),
83         FOREIGN KEY(name) REFERENCES MovieStar(name));