FabFlix/create_table.sql at main · Ykhan799/FabFlix · 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
CREATE TABLE movies(
    id varchar(10) NOT NULL,
    title varchar(100) NOT NULL,
    year int NOT NULL,
    director varchar(100) NOT NULL,
    PRIMARY KEY(id)
);


CREATE TABLE stars(
    id varchar(10) NOT NULL,
    name varchar(100) NOT NULL,
    birthYear int,
    PRIMARY KEY(id)
);


CREATE TABLE stars_in_movies(
    starId varchar(10) NOT NULL,
    movieId varchar(10) NOT NULL,
    FOREIGN KEY (starId) REFERENCES stars(id),
    FOREIGN KEY (movieId) REFERENCES movies(id)
);


CREATE TABLE genres(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(32) NOT NULL,
    PRIMARY KEY(id)
);


CREATE TABLE genres_in_movies(
    genreId int NOT NULL,
    movieId varchar(10),
    FOREIGN KEY (movieId) REFERENCES movies(id),
    FOREIGN KEY (genreId) REFERENCES genres(id)
);


CREATE TABLE creditcards(
    id varchar(20) NOT NULL,
    firstName varchar(50) NOT NULL,
    lastName varchar(50) NOT NULL,
    expiration date NOT NULL,
    PRIMARY KEY(id)
);


CREATE TABLE customers(
    id int NOT NULL AUTO_INCREMENT,
    firstName varchar(50) NOT NULL,
    lastName varchar(50) NOT NULL,
    ccId varchar(20) NOT NULL,
    address varchar(200) NOT NULL,
    email varchar(50) NOT NULL,
    password varchar(20) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (ccId) REFERENCES creditcards(id)
);


CREATE TABLE sales(
    id int NOT NULL AUTO_INCREMENT,
    customerId int NOT NULL,
    movieId varchar(10) NOT NULL,
    saleDate date NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (customerId) REFERENCES customers(id),
    FOREIGN KEY (movieId) REFERENCES movies(id)
);


CREATE TABLE ratings(
    movieId varchar(10) NOT NULL,
    rating float NOT NULL,
    numVotes int NOT NULL,
    FOREIGN KEY (movieId) REFERENCES movies(id)
);