Question
CREATE TABLE Movie (title CHAR(20), year INT, imdb FLOAT, director CHAR(20), PRIMARY KEY (title, year), CHECK(imdb >= 0 AND imdb
CREATE TABLE Movie (title CHAR(20), year INT, imdb FLOAT, director CHAR(20), PRIMARY KEY (title, year), CHECK(imdb >= 0 AND imdb <= 10)); CREATE TABLE Cast (title CHAR(20), year INT, actor CHAR(20), role CHAR(20), FOREIGN KEY (title, year) REFERENCES Movie(title, year);
The above schema can be transformed into the following column-oriented storage with each column's original name plus an additional id as their primary identifiers.
CREATE TABLE movieTitle(id int NOT NULL, title char (20) NOT NULL); CREATE TABLE movieYear(id int NOT NULL, year INT NOT NULL); CREATE TABLE movieImdb(id int NOT NULL, imdb FLOAT, CHECK (imdb>=0 AND imdb<=10)); CREATE TABLE movieDirector(id int NOT NULL, director char (20)); CREATE TABLE castTitle(id int NOT NULL, title char (20) NOT NULL); CREATE TABLE castYear(id int NOT NULL, year INT NOT NULL); CREATE TABLE castActor(id int NOT NULL, actor char(20)); CREATE TABLE castRole(id int NOT NULL, role char(20));
Question: Rewrite the primary key and the foreign key constraints of the original schema as triggers over the single-column schema.
Note: we may only need to modify five columns: (title, year) of both tables, and imdb of movies table.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started