MoviMuse/createtable.sql at main · Williamsj1444/MoviMuse · 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
CREATE DATABASE IF NOT EXISTS moviedb;

USE moviedb;

CREATE TABLE movies (
                        id VARCHAR(10) DEFAULT '' NOT NULL PRIMARY KEY,
                        title VARCHAR(100) DEFAULT '' NOT NULL,
                        year INTEGER NOT NULL,
                        director VARCHAR(100) DEFAULT '' NOT NULL
);

CREATE TABLE stars (
                       id VARCHAR(10) DEFAULT '' NOT NULL PRIMARY KEY,
                       name VARCHAR(100) DEFAULT '' NOT NULL,
                       birthYear INTEGER
);

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

CREATE TABLE genres(
                       id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       name VARCHAR(32) DEFAULT '' NOT NULL
);

CREATE TABLE genres_in_movies(
                                 genreId INTEGER NOT NULL,
                                 movieId VARCHAR(10) DEFAULT ''  NOT NULL,
                                 FOREIGN KEY (genreId) REFERENCES genres(id),
                                 FOREIGN KEY (movieId) REFERENCES movies(id)
);

-- create creditcards table before customers table
CREATE TABLE creditcards(
                            id VARCHAR(20) DEFAULT '' NOT NULL PRIMARY KEY,
                            firstName VARCHAR(50) DEFAULT '' NOT NULL,
                            lastName VARCHAR(50) DEFAULT '' NOT NULL,
                            expiration DATE NOT NULL
);

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

CREATE TABLE sales(
                      id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                      customerId INTEGER NOT NULL,
                      movieId VARCHAR(10) DEFAULT '' NOT NULL,
                      saleDate DATE NOT NULL,
                      FOREIGN KEY (customerId) REFERENCES customers(id),
                      FOREIGN KEY (movieId) REFERENCES movies(id)
);

CREATE TABLE ratings(
                        movieId VARCHAR(10) DEFAULT '' NOT NULL,
                        rating FLOAT,
                        numVotes INTEGER,
                        FOREIGN KEY (movieId) REFERENCES movies(id)
);