ADS-507-Project/project .session.sql at main · amyou518/ADS-507-Project · 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
USE imdb;

Drop TABLE Movies_Directory;

CREATE TABLE Movies_Directory (
    `index` INT,
    tconst VARCHAR(15) PRIMARY KEY ,
    titleType VARCHAR(50),
    primaryTitle VARCHAR(255),
    startYear INT,
    genres VARCHAR(100),
    averageRating DECIMAL(3,1)
);


DROP TABLE IF EXISTS Director;

CREATE TABLE Director (
    `index` INT PRIMARY KEY,
    nconst VARCHAR(15),
    primaryName VARCHAR(255),
    primaryProfession VARCHAR(255),
    tconst VARCHAR(15) NOT NULL,
    FOREIGN KEY (tconst) REFERENCES Movies_Directory(tconst)
);

DROP TABLE IF EXISTS Star;

CREATE TABLE Star (
    `index` INT PRIMARY KEY,
    nconst VARCHAR(15),
    primaryName VARCHAR(255),
    primaryProfession VARCHAR(255),
    tconst VARCHAR(15) NOT NULL,
    FOREIGN KEY (tconst) REFERENCES Movies_Directory(tconst)
);

DROP TABLE IF EXISTS Writer;

CREATE TABLE Writer (
    `index` INT PRIMARY KEY,
    nconst VARCHAR(15),
    primaryName VARCHAR(255),
    primaryProfession VARCHAR(255),
    tconst VARCHAR(15) NOT NULL,
    FOREIGN KEY (tconst) REFERENCES Movies_Directory(tconst)
);

Drop TABLE If EXISTS Movies_Box_office;

CREATE TABLE Movies_Box_office (
    `index` INT PRIMARY KEY,
    name VARCHAR(255),
    rating VARCHAR(50),
    genre VARCHAR(100),
    year INT,
    released VARCHAR(100),
    score DECIMAL(3, 1),
    votes BIGINT,
    director VARCHAR(255),
    writer VARCHAR(255),
    star VARCHAR(255),
    country VARCHAR(100),
    budget BIGINT,
    gross BIGINT,
    company VARCHAR(255),
    runtime INT,
    tconst VARCHAR(15),
    FOREIGN KEY (tconst) REFERENCES Movies_Directory(tconst)
);

DROP TABLE IF EXISTS Streaming;

CREATE TABLE Streaming (
    `index` INT,
    Unnamed_0 INT,
    ID INT PRIMARY KEY,
    Title VARCHAR(255),
    Year INT,
    Age VARCHAR(50),
    Rotten_Tomatoes DECIMAL(4,1),
    Netflix BOOLEAN,
    Hulu BOOLEAN,
    Prime_Video BOOLEAN,
    Disney_Plus BOOLEAN,
    Type INT,
    tconst VARCHAR(15),
    FOREIGN KEY (tconst) REFERENCES Movies_Directory(tconst)
);