Question

db query

Answer 1

CREATE TABLE customer (
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL UNIQUE,
birthdate date,
address text,
phone varchar(20),
PRIMARY KEY (username));


CREATE TABLE media_kind (
id SERIAL NOT NULL,
value varchar(255) NOT NULL UNIQUE,
PRIMARY KEY (id));

CREATE TABLE media (
id SERIAL NOT NULL,
name varchar(255) NOT NULL,
cover bytea,
release_date date,
content_rating int4,
summary text,
media_kind int4 NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (media_kind) REFERENCES media_kind (id));

CREATE TABLE rating (
media_id int4 NOT NULL,
customer_username varchar(255) NOT NULL,
rate int4 NOT NULL,
comment text,
rate_date timestamp NOT NULL,
PRIMARY KEY (media_id, customer_username),
FOREIGN KEY (customer_username) REFERENCES customer (username),
FOREIGN KEY (media_id) REFERENCES media (id));

CREATE TABLE crew (
id SERIAL NOT NULL,
name varchar(255) NOT NULL,
birthdate date,
deathdate date,
picture bytea,
bio text,
PRIMARY KEY (id));

CREATE TABLE crew_role (
id SERIAL NOT NULL,
value varchar(255) NOT NULL UNIQUE,
PRIMARY KEY (id));

CREATE TABLE media_crew (
crew_id int4 NOT NULL,
media_id int4 NOT NULL,
crew_role int4 NOT NULL,
PRIMARY KEY (crew_id, media_id, crew_role),
FOREIGN KEY (crew_id) REFERENCES crew (id),
FOREIGN KEY (media_id) REFERENCES media (id),
FOREIGN KEY (crew_role) REFERENCES crew_role (id));

CREATE TABLE media_type (
id SERIAL NOT NULL,
value varchar(255) NOT NULL UNIQUE,
PRIMARY KEY (id));

CREATE TABLE media_instance (
id SERIAL NOT NULL,
media_id int4 NOT NULL,
media_type int4 NOT NULL,
price money NOT NULL,
available bool NOT NULL,
sellable bool NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (media_type) REFERENCES media_type (id),
FOREIGN KEY (media_id) REFERENCES media (id));

CREATE TABLE rent (
media_instance int4 NOT NULL,
customer varchar(255) NOT NULL,
rent_date timestamp NOT NULL,
appoint_date timestamp NOT NULL,
return_date timestamp,
fine money,
payment money,
PRIMARY KEY (media_instance, customer),
FOREIGN KEY (media_instance) REFERENCES media_instance (id),
FOREIGN KEY (customer) REFERENCES customer (username));

CREATE TABLE purchase (
media_instance int4 NOT NULL,
customer varchar(255) NOT NULL,
purchase_date timestamp NOT NULL,
payment money NOT NULL,
PRIMARY KEY (media_instance, customer),
FOREIGN KEY (media_instance) REFERENCES media_instance (id),
FOREIGN KEY (customer) REFERENCES customer (username));

CREATE TABLE genre (
id SERIAL NOT NULL,
value varchar(255) NOT NULL UNIQUE,
PRIMARY KEY (id));

CREATE TABLE media_genre (
genre_id int4 NOT NULL,
media_id int4 NOT NULL,
PRIMARY KEY (genre_id, media_id),
FOREIGN KEY (genre_id) REFERENCES genre (id),
FOREIGN KEY (media_id) REFERENCES media (id));

Answer 2

a. Check if there is a director who has created a top rated movie in his twenties. (A movie
with rate of 9/10 or above is considered as a top rated movie.)
b. Check if there is a person who has three different casting roles (e.g., actor, director and
producer) in three different top 50 movies.