COURSE NAME:
INTERNATIONAL TAXATION - 1
COURSE ID: MBA233F3
SUBMITTED ON: 14.12.2024
STUDENT NAME: MOHAMMED ASHYAM
STUDENT USN: 1MS23BA043
ASSIGNMENT-1
CREATING DATABASE AND GENERATING
DESIRED RESULTS
Contents
I. 3
II. 4
III. 8
BRIEF INTRODUCTION ABOUT THE DATABASE
(WRITE NOT MORE THAN 300 WORDS)
This database is structured to store and manage essential information about movies,
directors, actors, and their interconnections. Its primary entities include Movies,
Directors, and Actors, supplemented by an Acts table that represents the many-to-
many relationship between actors and movies.
The Movies table records details such as movie titles, release years, ratings, and the
directors responsible for each film. The Directors table captures information about
directors, including their names and birth years, establishing a direct link between
movies and their creators. The Actors table maintains records of actors' names and
birth years, while the Acts table connects actors to the movies they have appeared in
capturing these collaborative relationships.
This relational structure enables a variety of queries, such as identifying all movies by
a specific director, listing the actors featured in a particular film, or filtering data based
on criteria like ratings or birth years. An Entity-Relationship (ER) Diagram illustrates
these relationships, enhancing understanding and aiding in the creation of efficient
queries while minimising data redundancy. By defining explicit connections between
entities, such as linking movies to directors and actors, the diagram ensures data
integrity and supports complex queries.
This design facilitates insightful analyses, such as determining which directors worked
on movies released after a certain year, identifying collaborations between actors and
directors, or pinpointing top-rated films. The database serves as a valuable tool for
analysing trends in the film industry, uncovering significant collaborations, and
exploring patterns in successful movies. Overall, it organises movie-related data
systematically and enables efficient querying for actionable insights.
II. a. CREATE THE ER DIAGRAM IN ACCESS
WRITE ACCESS QUERIES TO CREATE FOUR TABLES
Table 1: Movies
CREATE TABLE Movies
(
title VARCHAR(100) NOT NULL,
director VARCHAR(50) NOT NULL,
myear INT NOT NULL,
rating FLOAT NOT NULL,
CONSTRAINT movies_pk PRIMARY KEY (title)
);
Table 2: Directors
CREATE TABLE Directors
(
director VARCHAR(50) NOT NULL,
dyear INT NOT NULL,
CONSTRAINT directors_pk PRIMARY KEY (director)
);
Table 3: Actors
CREATE TABLE Actors
(
actor VARCHAR(50) NOT NULL,
ayear INT NOT NULL,
CONSTRAINT actors_pk PRIMARY KEY (actor)
);
Table 4: Acts
CREATE TABLE Acts
(
actor VARCHAR(50) NOT NULL,
title VARCHAR(100) NOT NULL,
CONSTRAINT acts_pk PRIMARY KEY (actor, title),
CONSTRAINT acts_actor_fk FOREIGN KEY (actor) REFERENCES
Actors(actor), CONSTRAINT acts_title_fk FOREIGN KEY (title)REFERENCES
Movies(title) 5
);
b. WRITE ACCESS QUERIES TO JOIN ALL FOUR TABLES [EXPLAIN THE
JOIN
RELATIONSHIPS]
1. Join Movies and Directors:
SELECT [Link] AS Movie_Title, [Link] AS Director_Name
FROM Movies
INNER JOIN Directors ON [Link] = [Link] ;
This query links the Movies table with the Directors table using the director field. It
retrieves movie titles along with the names of their respective directors. This
establishes a one-to-many relationship, where each director may have directed
multiple movies.
2. Join Movies, Directors, and Acts:
SELECT [Link] AS Movie_Title, [Link] AS Director_Name,
[Link] AS Actor_Name
FROM ((Movies
INNER JOIN Directors ON [Link] = [Link])
INNER JOIN Acts ON [Link] = [Link])
INNER JOIN Actors ON [Link] = [Link] ;
This query expands the relationship by introducing the Acts table, which connects the
Movies and Actors tables. It links [Link] with [Link], allowing us to identify
which actors appeared in specific movies. Then, it connects [Link] with
[Link], retrieving the names of the actors associated with those movies. By
doing so, this query helps us see not only the directors of movies but also the actors
who starred in them.
3. Complete Join for All Four Tables:
SELECT [Link] AS Movie_Title, [Link] AS Director_Name,
[Link] AS Actor_Name, [Link] AS Release_Year,
[Link] AS Movie_Rating, [Link] AS Actor_Birth_Year
FROM ((Movies
INNER JOIN Directors ON [Link] = [Link])
INNER JOIN Acts ON [Link] = [Link])
INNER JOIN Actors ON [Link] = [Link]
This query combines all four tables (Movies, Directors, Acts, and Actors) to provide
complete information. It includes movie titles, release years, and ratings from the
Movies table, director names from the Directors table, and actor names and birth
years from the Actors table. The Acts table bridges the connection between movies
and actors, enabling a many-to-many relationship. This query captures all
relationships and provides detailed insights about the movies, their directors, and the
actors who appeared in them in a single result set.
c. PASTE THE PICTURE OF THE ER DIAGRAM
III. WRITE THE QUERIES FOR EACH QUESTION ASKED SEPARATELY AND
WRITE THE OUTPUT OBTAINED
Find directors and movie titles where rating was greater than 7.5
Query:
SELECT Director, Title, Rating
FROM Movies
WHERE Rating > 7.5 ;
Output:
Find the directors and actors who were born before 1960.
Query:
SELECT DISTINCT [Link], [Link]
FROM Directors, Actors
WHERE [Link] < 1960
AND [Link] < 1960 ;
Output:
Find the minimum ratings obtained for a movie.
Query:
SELECT MIN([Link]) AS Min_Rating
FROM Movies ;
Output:
Find the maximum ratings obtained for a movie
Query:
SELECT MAX([Link]) AS Max_Rating
FROM Movies ;
Output:
Find movies made after 1997
Query:
SELECT title
FROM Movies
WHERE myear > 1997 ;
Output:
Find movies made by Hanson after 1997
Query:
SELECT [Link]
FROM Movies, Directors
WHERE [Link] = [Link]
AND [Link] = 'Hanson'
AND [Link] > 1997 ;
Output:
Find all movies and their ratings
Query:
SELECT title, rating
FROM Movies ;
Output:
Find all actors and directors
Query:
SELECT [Link], [Link]
FROM Actors, Directors ;
Output:
Find Coen’s movies with McDormand
Query:
SELECT [Link]
FROM Movies
JOIN Directors ON [Link] = [Link]
JOIN Acts ON [Link] = [Link]
JOIN Actors ON [Link] = [Link]
WHERE [Link] = 'Coen'
AND [Link] = 'McDormand' ;
Output: