Question

Below are the table schema for Table actor, movie, and actor_role:

Table 1: actor
(AID varchar(8), name varchar(30), salary numeric(8,2))
AID is the primary key
Table 2: movie
(MID varchar(8), title varchar(50))
MID is the primary key
Table 3: actor_role
(MID varchar(8), AID varchar(8), rolename varchar(20))
MID and AID together are the primary key
AID is a foreign key
MID is a foreign key
1. Write a sql statement to create a database called movieData. (1 point)
2. Write a sql statement to switch to database movieData. (1 point)
3. Please specify the order of table creation (2 points)
4. Please specify the order of data population of the tables (2 points)
5. Write sql statements to create table actor_role. (3 points)

Answer 1

**1) Create database movieData:

CREATE DATABASE movieData;

**2) Switch to database movieData:

USE movieData;

**3) The order of table creation would be:

1. actor

2. movie

3. actor_role

**4) The order of data population of the tables would be:

1. actor

2. movie

3. actor_role

**5) SQL statement to create table actor_role:

CREATE TABLE actor_role(

MID varcha (8) NOT NULL,

AID varchar (8) NOT NULL,

rolename varchar (20),

PRIMARY KEY(MID, AID),

FOREIGN KEY(AID) REFERENCES actor(AID),

FOREIGN KEY(MID) REFERENCES movie(MID)

);

Step-by-step explanation:

1. To create a database called movieData, you can use the following SQL statement:

```

CREATE DATABASE movieData;

```

This statement creates a new database with the name "movieData".

2. To switch to the movieData database, you can use the following SQL statement:

```

USE movieData;

```

This statement selects the movieData database, allowing you to perform operations within that database.

3. The order of table creation should be as follows:

• - actor table: Since it does not have any foreign key constraints, it can be created first.
• - movie table: It has a foreign key constraint on MID in the actor_role table, so it should be created after the actor table.
• - actor_role table: It has foreign key constraints on both MID and AID, so it should be created last, after both the actor and movie tables.

4. The order of data population for the tables should be as follows:

• - actor table: You can populate the actor table first with the relevant data, including AID, name, and salary.
• - movie table: Next, you can populate the movie table with the relevant data, including MID and title.
• - actor_role table: Finally, you can populate the actor_role table with the relevant data, including MID, AID, and rolename. Make sure the data in the actor_role table corresponds to the existing data in the actor and movie tables.

5. To create the table actor_role, you can use the following SQL statement:

```

CREATE TABLE actor_role (

MID varchar (8),

AID varchar (8),

rolename varchar (20),

PRIMARY KEY (MID, AID),

FOREIGN KEY (MID) REFERENCES movie(MID),

FOREIGN KEY (AID) REFERENCES actor(AID)

);

```

This statement creates the table actor_role with columns for MID, AID, and rolename. It also defines the primary key as the combination of MID and AID, and sets foreign key constraints on both MID and AID referencing the movie and actor tables, respectively. This ensures the integrity of the data in the actor_role table by linking it to the corresponding records in the movie and actor tables.