Task Description: A List Of Movies Needed
Task description A list with information about movies needs to be o
Develop a comprehensive database for managing detailed film information, including movies, directors, and actors, as specified below. Populate it with provided data, and create various queries to extract specific lists, identify individuals with multiple roles, and generate views that summarize this information. Address questions regarding the updatability of views and provide an example of an updatable view and a correlated nested query, explaining their significance within the database context.
Paper For Above instruction
Introduction
The development of an effective relational database system for movie information requires careful modeling of entities and their relationships. The core entities include movies, directors, and actors, each with relevant attributes. The database must accommodate complex queries, including those involving role overlap, nationality considerations, and multilingual film listings. Structuring these aspects appropriately ensures data integrity, efficient retrieval, and flexibility for analytical purposes.
Entity-Relationship (ER) Model and Mapping
The ER model encompasses entities: Movie, Director, and Actor. The relationships include directed by (between Movie and Director), acted in (between Movie and Actor), and dual role (actors who are also directors). Attributes include:
- Movie: title, year of release, country, runtime
- Director: name, surname, year of birth, nationality
- Actor: name, surname, year of birth, nationality
Associative tables map many-to-many relationships:
- Movie_Director (movie_id, director_id)
- Movie_Actor (movie_id, actor_id)
Dual roles are represented through roles in both tables or through a combined Person table if desired. The ER diagram should reflect these relationships with appropriate cardinalities.
Table Creation in SQL
CREATE TABLE Person (
person_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
surname VARCHAR(50),
birth_year INT,
nationality VARCHAR(50)
);
CREATE TABLE Movie (
movie_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
year_of_release INT,
country VARCHAR(50),
runtime INT
);
CREATE TABLE Movie_Director (
movie_id INT,
director_id INT,
PRIMARY KEY (movie_id, director_id),
FOREIGN KEY (movie_id) REFERENCES Movie(movie_id),
FOREIGN KEY (director_id) REFERENCES Person(person_id)
);
CREATE TABLE Movie_Actor (
movie_id INT,
actor_id INT,
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES Movie(movie_id),
FOREIGN KEY (actor_id) REFERENCES Person(person_id)
);
Populating Tables with Data
-- Insert Persons (Directors and Actors)
INSERT INTO Person (name, surname, birth_year, nationality) VALUES
('Jonathan', 'Demme', 1944, 'USA'),
('Michael', 'Mann', 1943, 'USA'),
('Roberto', 'Benigni', 1952, 'Italian'),
('Sergio', 'Leone', 1929, 'Italian'),
('Stanley', 'Kubrick', 1928, 'USA'),
('Donald', 'Siegel', 1912, 'USA'),
('Tom', 'Cruise', 1962, 'USA'),
('Nicole', 'Kidman', 1967, 'USA'),
('Kevin', 'Spacey', 1959, 'USA'),
('Anthony', 'Hopkins', 1937, 'Welsh'),
('Jodie', 'Foster', 1962, 'USA'),
('Daniel', 'Day-Lewis', 1957, 'English'),
('Lee', 'Van Cleef', 1925, 'USA');
-- Insert Movies
INSERT INTO Movie (title, year_of_release, country, runtime) VALUES
('Silence of the Lambs', 1991, 'USA', 118),
('Last of the Mohicans', 1992, 'USA', 122),
('Life is Beautiful', 1997, 'Italian', 124),
('The Good, The Bad and The Ugly', 1966, 'Italian', 180),
('Dr. Strangelove', 1964, 'English', 93),
('Escape from Alcatraz', 1979, 'USA', 112),
('Eyes Wide Shut', 1999, 'USA', 160),
('Midnight in the Garden of Good and Evil', 1997, 'USA', 155),
('American Beauty', 1999, 'USA', 121);
-- Map Directors to Movies
INSERT INTO Movie_Director VALUES
(1, (SELECT person_id FROM Person WHERE name='Jonathan' AND surname='Demme')),
(2, (SELECT person_id FROM Person WHERE name='Michael' AND surname='Mann')),
(3, (SELECT person_id FROM Person WHERE name='Roberto' AND surname='Benigni')),
(4, (SELECT person_id FROM Person WHERE name='Sergio' AND surname='Leone')),
(5, (SELECT person_id FROM Person WHERE name='Stanley' AND surname='Kubrick')),
(6, (SELECT person_id FROM Person WHERE name='Donald' AND surname='Siegel')),
(7, (SELECT person_id FROM Person WHERE name='Stanley' AND surname='Kubrick')),
(8, (SELECT person_id FROM Person WHERE name='Clint' AND surname='Eastwood')),
(9, (SELECT person_id FROM Person WHERE name='Kevin' AND surname='Spacey')),
(10, (SELECT person_id FROM Person WHERE name='Anthony' AND surname='Hopkins')),
(11, (SELECT person_id FROM Person WHERE name='Jodie' AND surname='Foster')),
(12, (SELECT person_id FROM Person WHERE name='Daniel' AND surname='Day-Lewis'));
-- Map Actors to Movies
INSERT INTO Movie_Actor VALUES
(1, (SELECT person_id FROM Person WHERE name='Anthony' AND surname='Hopkins')),
(1, (SELECT person_id FROM Person WHERE name='Jodie' AND surname='Foster')),
(2, (SELECT person_id FROM Person WHERE name='Daniel' AND surname='Day-Lewis')),
(3, (SELECT person_id FROM Person WHERE name='Roberto' AND surname='Benigni')),
(4, (SELECT person_id FROM Person WHERE name='Clint' AND surname='Eastwood')),
(4, (SELECT person_id FROM Person WHERE name='Lee' AND surname='Van Cleef')),
(5, (SELECT person_id FROM Person WHERE name='Stanley' AND surname='Kubrick')),
(6, (SELECT person_id FROM Person WHERE name='Clint' AND surname='Eastwood')),
(7, (SELECT person_id FROM Person WHERE name='Tom' AND surname='Cruise')),
(8, (SELECT person_id FROM Person WHERE name='Kevin' AND surname='Spacey'));
SQL Queries for Extracting Data
1. List name and surname of all actors in alphabetical order
SELECT name, surname FROM Person
WHERE person_id IN (
SELECT actor_id FROM Movie_Actor
)
ORDER BY surname, name;
2. List name and surname of all directors in alphabetical order
SELECT name, surname FROM Person
WHERE person_id IN (
SELECT director_id FROM Movie_Director
)
ORDER BY surname, name;
3. List titles of all English movies in descending order of publication year
SELECT title FROM Movie
WHERE country = 'English'
ORDER BY year_of_release DESC;
4. List name and surname of all people who have had a role of both director and actor in the same film
SELECT DISTINCT p.name, p.surname
FROM Person p
JOIN Movie_Director md ON p.person_id = md.director_id
JOIN Movie_Actor ma ON p.person_id = ma.actor_id
WHERE md.movie_id = ma.movie_id;
5. List name and surname of all people who acted in a film produced in their nationality, ordered by year of birth ascending
SELECT DISTINCT p.name, p.surname
FROM Person p
JOIN Movie_Actor ma ON p.person_id = ma.actor_id
JOIN Movie m ON ma.movie_id = m.movie_id
WHERE p.nationality = m.country
ORDER BY p.birth_year ASC;
6. List name, surname, and year of birth of all people who are both actors and directors
SELECT name, surname, birth_year FROM Person
WHERE person_id IN (
SELECT director_id FROM Movie_Director
)
AND person_id IN (
SELECT actor_id FROM Movie_Actor
);
7. Create a view for listing name and surname of all people who have acted in more than one movie, with count
CREATE VIEW ActorsMultipleMovies AS
SELECT p.name, p.surname, COUNT(*) AS movie_count
FROM Person p
JOIN Movie_Actor ma ON p.person_id = ma.actor_id
GROUP BY p.person_id
HAVING COUNT(*) > 1;
8. Create a view showing all people and titles of movies they directed, with null for those who directed none
CREATE VIEW PeopleDirectedMovies AS
SELECT p.name, p.surname, m.title
FROM Person p
LEFT JOIN Movie_Director md ON p.person_id = md.director_id
LEFT JOIN Movie m ON md.movie_id = m.movie_id;
9 and 10. Updatability of views and example of correlated nested query
Views in (7) and (8) are generally non-updatable due to aggregation and use of joins, respectively. An example of an updatable view could be a simple view of a single table, such as:
CREATE VIEW SingleTableView AS
SELECT * FROM Person;
This view is updatable if the underlying table permits direct modifications, allowing insert, update, and delete operations that reflect on the base table.
A correlated nested query involves inner queries that depend on outer query variables. For example:
SELECT p.name, p.surname
FROM Person p
WHERE EXISTS (
SELECT 1 FROM Movie_Actor ma
WHERE ma.actor_id = p.person_id AND ma.movie_id IN (
SELECT movie_id FROM Movie WHERE country = p.nationality
)
);
This query is correlated because the inner SELECT depends on a value from the outer row p.person_id and p.nationality, linking the subquery’s execution context to each row of the outer query.
Conclusion
This database design effectively captures the complexity of movie data, including multiple roles, nationality considerations, and multilingual films. The SQL queries meet the specified requirements, enabling detailed analysis and reporting. Understanding the updatability of views and the nature of correlated queries helps with maintaining data consistency and optimizing database operations for comprehensive movie data management.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Communications of the ACM, 13(6), 377-387.
Database Design and Relational Theory: Normal Forms and All That. O'Reilly Media. Database System Concepts (6th ed.). McGraw-Hill.