Question

Create three MySQL database tables and write SQL scripts to read, insert, and delete data. The first database table will contain the names of at least four movies. The second table will be a list of actors who appear in the movies. The third table will be an associative table that describes the relationship between the actors and their movies (which actors appear in which movies). Actors and movies have a “many-to-many relationship,” meaning an actor can be in multiple movies and a movie can have multiple actors.

• Use the MySQL Database Instructions listed below to create the three tables.
• Once the three tables are completed, create a “join” statement that joins data from all three tables. Remember that field names used in more than one table (ASIN, ActorID) must be fully qualified (i.e., dvdtitle, ASIN).
• The previous SQL query returns a separate record for each actor. Provide a way to group results from several records into a single row.

General guidelines for this:

• String values in SQL statements are always enclosed within single quotes. ASIN and Title are both strings and require single quotes. For instance, your insert statement will look something like this:
  $sql = “Insert into dvdtitles (asin, title, price) VALUES (‘$asin’, ‘$title’, $price)”;
• SQL debugging tip: check your SQL statement before you execute it. Put the following line of code immediately before the mysql_query($strSQL) statement.
• When retrieving ASIN from the querystring, remember that ASIN is a string, not a number.
• Explain in detail (like im 5) the process on how to group results from several records into a single row.

Table 1

1. Create a MySQL database table named “tbldvdtitles.” You will be adding information about a minimum of four of your favorite movies.

The table should have the following structure:

Field name	Type	Attributes
asin	varchar(15)	primary key
title	varchar(100)
price	double(5,2)

2. Write a SQL statement to add the ASIN, price, and title for your favorite movies.

Table 2

1. Create a second database table named “tbldvdActors.” Your table should have the following structure:

Field	Type	Attributes
actorID	int(5)	auto_increment, primary key
fname	varchar(20)
lname	varchar(20)

2. Write a SQL statement to add at least four actors, one from each of your listed movies.

3. Write a SQL statement to update the last actor fname, and lname information.

4. Write a SQL statement to delete the first actor in the tbldvdActors table.

Table 3

1. Create a third database table of relationships between actors and movie titles. The table should have the following structure:

Field	Type	Attributes
asin	varchar(15)	primary key (composite)
actorID	int(5)	primary key (composite)

1. (Because this table uses a composite key, the delete statement must reference both the asin and actorID fields.)

2. Add data that describes the relationship between your movies and actors.

Answer 1

SOLUTION:

The following solution has been implemented in MySQL. A separate database “DVD” has been created for this purpose.

The following 3 tables have been created

1. tbldvdTitles

2. tbldvdActors

3. tbldvdTitleActors

1. Create a MySQL database table named “tbldvdtitles.”

CREATE TABLE tbldvdTitles(asin VARCHAR(15) PRIMARY KEY, title VARCHAR(100), price DOUBLE);

A. Write a SQL statement to add the ASIN, price, and title for your favorite movies.

INSERT INTO tbldvdTitles VALUES('B079535TSM','The God Father',8.59);
INSERT INTO tbldvdTitles VALUES('B0019L21GA','The God Father Part II',10.27);
INSERT INTO tbldvdTitles VALUES('B00062IVKS','Raging Bull',12.94);
INSERT INTO tbldvdTitles VALUES('B08G9GW94R','The Irishman',22.99);

2. Create a second database table named “tbldvdActors”.

CREATE TABLE tbldvdActors(actorID INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(20), lname VARCHAR(20));

A. Write a SQL statement to add at least four actors, one from each of your listed movies.

INSERT INTO tbldvdActors VALUES('Diane','Keaton');
INSERT INTO tbldvdActors VALUES('Marlon','Brando');
INSERT INTO tbldvdActors VALUES('Al','Pacino');
INSERT INTO tbldvdActors VALUES('Robert','De Niro');
INSERT INTO tbldvdActors VALUES('Joe','Pesci');

B. Write a SQL statement to update the last actor fname, and lname information.

UPDATE tbldvdActors SET fname='Joseph', lname='Frank Pesci' ORDER BY actorID DESC LIMIT 1;

C. Write a SQL statement to delete the first actor in the tbldvdActors table.

DELETE FROM tbldvdActors ORDER BY actorID ASC LIMIT 1;

3. Create a third database table of relationships between actors and movie titles.

CREATE TABLE tbldvdTitleActors(asin VARCHAR(15), actorID INT,
CONSTRAINT pk_dvdTitleActors PRIMARY KEY(asin, actorID),
CONSTRAINT fk_dvd_asin FOREIGN KEY(asin) REFERENCES tbldvdTitles(asin),
CONSTRAINT fk_dvd_actorID FOREIGN KEY(actorID) REFERENCES tbldvdActors(actorID));

A. Add data that describes the relationship between your movies and actors

INSERT INTO tbldvdTitleActors VALUES('B079535TSM',2);
INSERT INTO tbldvdTitleActors VALUES('B0019L21GA',3);
INSERT INTO tbldvdTitleActors VALUES('B00062IVKS',4);
INSERT INTO tbldvdTitleActors VALUES('B08G9GW94R',5);

Query combining Data from the 3 tables created.

SELECT tbldvdTitles.title, CONCAT(tbldvdActors.fname,' ',tbldvdActors.lname) AS "Actor"
FROM tbldvdTitles, tbldvdTitleActors, tbldvdActors
WHERE tbldvdTitles.asin = tbldvdTitleActors.asin
AND tbldvdTitleActors.actorID = tbldvdActors.actorID;

Grouping results from several records into a single row

For the purpose of grouping 2 records have been added.

INSERT INTO tbldvdTitleActors VALUES('B079535TSM',3);
INSERT INTO tbldvdTitleActors VALUES('B0019L21GA',4);

SELECT tbldvdTitles.title, COUNT(tbldvdActors.actorID) AS "Actors"
FROM tbldvdTitles, tbldvdTitleActors, tbldvdActors
WHERE tbldvdTitles.asin = tbldvdTitleActors.asin
AND tbldvdTitleActors.actorID = tbldvdActors.actorID
GROUP BY tbldvdTitles.title;

The above query gives the result of the number of actors in each movie.

Hope this helps.