Create a database \'SHOWTIME\' in MySQL and enforce basic constraints such as: R

Question

Create a database 'SHOWTIME' in MySQL and enforce basic constraints such as:

Referential integrity constraints

Domain constraints

Key and Entity constraints

Semantic constraints

Populate with sample data from these sites (these are not the only sites that need to be used)

www.thetvdb.com
www.themoviedb.org
www.imdb.com

Show(Sid, title, synopsis, genre, language, dateOfRelease) Users(login, password, country, DOB, name, email) Review(Rid, DOB, text, rating, Sid, login) castAndCrew(Pid, name, DOB, placeOfBirth, Sid, T1, T2) Episodes(Sid, Snum, duration, releaseDate, SeasonNum) Roles(Pid, Sid, character) ReviewRating(Rid, login, rating) ShowRating(Sid, login, rating) ShowTags(Sid, tags) Movies(Sid, rating, duration) TVShows(Sid, status, network) Vi deoBlogs(Sid, duration) Review(Sid) references Show(Sid) Review (login) references Users(login) castAndCrew(Sid) references Show(Sid) Episodes (Sid) references Show(Sid) Roles (Pid) references castAndCrew (Pid) Roles (Sid) references Show(Sid) ReviewRating(Rid) references Review(Rid) ReviewRating(login) references Users(login) ShowRating(Sid) references Show (Sid) ShowRating(login) references Users(login) ShowTags(Sid) references Show(Sid) Movies(Sid) references Show(Sid) TVShows (Sid) references Show(Sid) VideoBlogs(Sid) references Show(Sid)

Explanation / Answer

Creation of database SHOWTIME:

CREATE DATABASE SHOWTIME;

Creation of tables:

Show table:
................

CREATE TABLE Show(
Sid int INT NOT NULL,
title VARCHAR(100) NOT NULL,
synopsis VARCHAR(100),
genre VARCHAR(100),
language VARCHAR(100),
dateOfRelease DATE,
PRIMARY KEY (Sid)
);

Users table:
...........

CREATE TABLE Users(
login int INT NOT NULL,
password VARCHAR(100) NOT NULL,
country VARCHAR(100),
name VARCHAR(100),
email VARCHAR(100),
DOB DATE,
PRIMARY KEY (login)
);

Review table:
...........

CREATE TABLE Review(
Rid int INT NOT NULL,
Sid int INT,
login int INT,
text VARCHAR(100) NOT NULL,
rating NUMBER,
DOB DATE,
PRIMARY KEY (Rid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
FOREIGN KEY(login) REFERENCES Show(login)
);

castAndCrew table:
...........

CREATE TABLE castAndCrew(
Pid int INT NOT NULL,
Sid int INT,
name VARCHAR(100),
placeOfBiirth VARCHAR(100),
DOB DATE,
T1 VARCHAR(100),
T2 VARCHAR(100),
PRIMARY KEY (Rid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
);

Episodes table:
...........

CREATE TABLE Episodes(
Snum int INT,
Sid int INT NOT NULL,
ReleaseDate DATE,
Duration TIME,
seasonNum NUMBER,
PRIMARY KEY (Sid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
);

Roles table:
...........

CREATE TABLE Roles(
Pid int INT NOT NULL
Sid int INT NOT NULL,
character VARCHAR(100),
PRIMARY KEY (Sid)
PRIMARY KEY (Pid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
FOREIGN KEY(Pid) REFERENCES castAndCrew(pid),
);

ReviewRating table:
...........

CREATE TABLE ReviewRating(
Rid int INT NOT NULL
login int INT NOT NULL,
Rating NUMBER,
PRIMARY KEY (Sid)
PRIMARY KEY (login)
FOREIGN KEY(Rid) REFERENCES Review(Rid),
FOREIGN KEY(login) REFERENCES Users(login),
);

ShowRating table:
...........

CREATE TABLE ShowRating(
Sid int INT NOT NULL
login int INT NOT NULL,
Rating NUMBER,
PRIMARY KEY (Sid)
PRIMARY KEY (login)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
FOREIGN KEY(login) REFERENCES Users(login),
);

ShowTags table:
...........

CREATE TABLE ShowTags(
Sid int INT NOT NULL
tags int INT NOT NULL
PRIMARY KEY (Sid)
PRIMARY KEY (tags)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
);

Movies table:
...........

CREATE TABLE Movies(
Sid int INT NOT NULL
rating NUMBER,
Duration TIME,
PRIMARY KEY (Sid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
);

TVShows table:
...........

CREATE TABLE TVShows(
Sid int INT NOT NULL
status VARCHAR(100),
network VARCHAR(100),
PRIMARY KEY (Sid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
);

VideoBlogs table:
...........

CREATE TABLE VideoBlogs(
Sid int INT NOT NULL
duration TIME,
PRIMARY KEY (Sid)
FOREIGN KEY(Sid) REFERENCES Show(Sid),
);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.