Answer:
- SQL statement that defines table for Actor
CREATE TABLE Actor(
person_id integer primary key,
name varchar2(40) not null,
birth_year integer check ((birth_year) <= 2019)
);
-
SQL statement that defines table for Play
CREATE TABLE Play(
play_id integer primary key,
title varchar2(60) not null,
author varchar2(60) not null,
year_written integer check ((year_written) <= 2019)
);
-
SQL statement that defines table for Role
CREATE TABLE Role (
person_id integer,
character_name varchar2(60) not null,
play_id integer,
constraint fk_person foreign key (person_id) references actor(person_id),
constraint fk_play foreign key (play_id) references play(play_id),
primary key (person_id, character_name, play_id)
);
Explanation:
Other information that were not added to the question are as below:
The following database contains information about three tables i.e. actors, plays, and roles they performed.
Actor (person_id, name, birth_year)
Play (play_id, title, author, year_written)
Role (person_id, character_name, play_id)
Where: Actor is a table of actors, their names, and the year they were born. Each actor has a unique person_id, which is a key.
Play is a table of plays, giving the title, author, and year written for each play. Each play has a unique play_id, which is a key.
Role records which actors have performed which roles (characters) in which plays.
Attributes person_id and play_id are foreign keys to Actor and Play respectively.
All three attributes make up the key since it is possible for a single actor to play more than one character in the same play
Further Explanation:
In SQL, in order to define relational schema (Tables) for a database, we use CREATE TABLE statement to create a new table in a database. The column parameters specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (varchar, integer, date)
