Topic: Database Software: SQLite Schema Table: CREATE TABLE name_basics (nconst VARCHAR(45) NOT NULL, primaryName VARCHAR(45) NULL, birthYear YEAR(4) NULL, deathYear YEAR(4) NULL, age INT NULL, movieNum INT NULL, PRIMARY KEY (nconst)); CREATE TABLE attributes ( attributeID INT NOT NULL, attribute VARCHAR(45) NOT NULL, PRIMARY KEY (attributeID) ); CREATE TABLE attributes_collection ( akasID INT NOT NULL, attributeID INT NOT NULL, PRIMARY KEY (akasID, attributeID), CONSTRAINT attributeID FOREIGN KEY (attributeID) REFERENCES attributes (attributeID) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE crew_directors ( crewID INT NOT NULL, nconst VARCHAR(45) NOT NULL, PRIMARY KEY (crewID, nconst), CONSTRAINT crewID FOREIGN KEY (crewID) REFERENCES title_crew (crewID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT nconst FOREIGN KEY (nconst) REFERENCES name_basics (nconst) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE crew_writers ( crewID INT NOT NULL, nconst VARCHAR(45) NOT NULL, PRIMARY KEY (crewID, nconst), CONSTRAINT crewID FOREIGN KEY (crewID) REFERENCES title_crew (crewID) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT nconst FOREIGN KEY (nconst) REFERENCES name_basics (nconst) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE genre ( genreID INT NOT NULL, genre VARCHAR(45) NOT NULL, PRIMARY KEY (genreID)); CREATE TABLE genre_collection ( tconst VARCHAR(45) NOT NULL, genre INT NOT NULL,
Topic:
Software: SQLite
Schema Table:
CREATE TABLE name_basics (nconst VARCHAR(45) NOT NULL, primaryName VARCHAR(45) NULL, birthYear YEAR(4) NULL, deathYear YEAR(4) NULL, age INT NULL, movieNum INT NULL, PRIMARY KEY (nconst));
CREATE TABLE attributes (
attributeID INT NOT NULL,
attribute VARCHAR(45) NOT NULL,
PRIMARY KEY (attributeID)
);
CREATE TABLE attributes_collection (
akasID INT NOT NULL,
attributeID INT NOT NULL,
PRIMARY KEY (akasID, attributeID),
CONSTRAINT attributeID
FOREIGN KEY (attributeID)
REFERENCES attributes (attributeID)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE crew_directors (
crewID INT NOT NULL,
nconst VARCHAR(45) NOT NULL,
PRIMARY KEY (crewID, nconst),
CONSTRAINT crewID
FOREIGN KEY (crewID)
REFERENCES title_crew (crewID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT nconst
FOREIGN KEY (nconst)
REFERENCES name_basics (nconst)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE crew_writers (
crewID INT NOT NULL,
nconst VARCHAR(45) NOT NULL,
PRIMARY KEY (crewID, nconst),
CONSTRAINT crewID
FOREIGN KEY (crewID)
REFERENCES title_crew (crewID)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT nconst
FOREIGN KEY (nconst)
REFERENCES name_basics (nconst)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE genre (
genreID INT NOT NULL,
genre VARCHAR(45) NOT NULL,
PRIMARY KEY (genreID));
CREATE TABLE genre_collection (
tconst VARCHAR(45) NOT NULL,
genre INT NOT NULL,
PRIMARY KEY (tconst, genre),
CONSTRAINT genre
FOREIGN KEY (genre)
REFERENCES genre (genreID)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TABLE known_collection (
tconst VARCHAR(45) NOT NULL,
nconst VARCHAR(45) NOT NULL,
PRIMARY KEY (tconst, nconst),
CONSTRAINT tconst
FOREIGN KEY (tconst)
REFERENCES title_basics (tconst)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT nconst
FOREIGN KEY (nconst)
REFERENCES name_basics (nconst)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Prompt: Add triggers to the above schema table that automatically updated when new data in inserted.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
