Msaccess Assessment 2: Movie Database Prototype Demonstratio
Msaccess Assessment 2 50 Ptsmovie Database Prototype Demonstration
Review the MOVIE ERD (Entity Relationship Diagram) generated in Assessment 1, which depicts the relational structure between movies and directors. You are tasked with creating and populating the DIRECTOR and MOVIE tables within a Microsoft Access database, following specific requirements. You must provide detailed explanations of key database concepts such as primary keys and foreign keys, and then implement the table creation, data insertion, and querying tasks as outlined below. All SQL commands must be executed within Access, and their results documented via screenshots or captured images. Your submission should include the SQL commands used and the output of their execution, ensuring reproducibility. The data for creating movie records will be sourced from the provided 'Movie Fact Data Sheet.docx.' Ensure to incorporate appropriate relationships between tables, specifically linking movies to their respective directors via foreign keys.
Paper For Above instruction
Introduction
The development of a structured movie database in Microsoft Access requires understanding the fundamental concepts of relational database design. The core entities involved include movies and directors, which are interconnected through specified relationships. Proper comprehension of primary and foreign keys ensures data integrity and facilitates efficient data retrieval. This paper discusses these key concepts and the step-by-step process involved in creating the tables, populating them with data, and executing queries pertinent to the database's functional requirements.
Understanding Primary and Foreign Keys
A primary key uniquely identifies each record within a table, serving as its main identifier. It enforces entity integrity by preventing duplicate entries and null values in the key field. In the MOVIE table, the primary key could be the MovieID, a uniquely assigned identifier for each movie. Similarly, in the DIRECTOR table, the DirectorID functions as the primary key, ensuring every director is uniquely identifiable.
The primary purpose of a primary key is to create a unique, non-redundant reference point for each record, which facilitates reliable data retrieval, updates, and referential integrity. Conversely, a foreign key is a field in one table that references the primary key in another table, thereby establishing a relationship between the two tables. The foreign key enforces referential integrity by ensuring that the relationship between tables remains consistent; for example, each movie record in the MOVIE table must have a DirectorID that corresponds to an existing DirectorID in the DIRECTOR table.
In this database design, the MOVIE table contains a foreign key (DirectorID) that links it to the DIRECTOR table. This relationship is typically one-to-many: one director can direct multiple movies, but each movie is associated with only one director. The relationship supports operations such as retrieving all movies by a particular director or maintaining data consistency during updates.
Creating the DIRECTOR Table
To create the DIRECTOR table, an SQL CREATE TABLE statement specifies the primary key and other attributes like DirectorName. Executing this command in Access’s SQL View ensures proper table structure. After creation, inspecting table design confirms adherence to the intended schema.
CREATE TABLE DIRECTOR (
DirectorID AUTOINCREMENT PRIMARY KEY,
DirectorName VARCHAR(50)
);
The above command defines an auto-incremented primary key (DirectorID) and a DirectorName field. Populating the table with data involves INSERT statements, for example, adding entries for Steven Spielberg and Ron Howard.
INSERT INTO DIRECTOR (DirectorName) VALUES ('Steven Spielberg');
INSERT INTO DIRECTOR (DirectorName) VALUES ('Ron Howard');
Reviewing the table via Design View confirms the accurate structure and entries have been added.
Creating the MOVIE Table
Similarly, the MOVIE table is created with fields for MovieID, Title, Length, and DirectorID, the latter being a foreign key linking to DIRECTOR. The SQL command ensures foreign key constraints are enforced, maintaining referential integrity.
CREATE TABLE MOVIE (
MovieID AUTOINCREMENT PRIMARY KEY,
Title VARCHAR(100),
Length INTEGER,
DirectorID INTEGER,
FOREIGN KEY (DirectorID) REFERENCES DIRECTOR(DirectorID)
);
Data insertion into the MOVIE table uses information from the movie facts sheet, assigning appropriate DirectorID values for each record. For example:
INSERT INTO MOVIE (Title, Length, DirectorID) VALUES ('Apollo 13', 140, 1);
INSERT INTO MOVIE (Title, Length, DirectorID) VALUES ('Jaws', 124, 2);
INSERT INTO MOVIE (Title, Length, DirectorID) VALUES ('E.T.', 115, 1);
INSERT INTO MOVIE (Title, Length, DirectorID) VALUES ('Close Encounters of the Third Kind', 132, 1);
Subsequently, examining the MOVIE table confirms the records are accurately inserted and linked to their respective directors.
Queries and Data Retrieval
To list all movies with their titles and lengths, ordered alphabetically by title, the following SQL is used:
SELECT Title, Length FROM MOVIE ORDER BY Title;Executing this query displays all movies with their respective durations, ordered by title.
To filter movies longer than 2 hours and 10 minutes (130 minutes), the SQL command is:
SELECT Title, Length FROM MOVIE WHERE Length > 130;This query retrieves movies exceeding the specified duration threshold.
To determine the total number of movies in the database, the COUNT aggregate function is employed:
SELECT COUNT(*) AS TotalMovies FROM MOVIE;The result provides the total count of movies stored in the database, facilitating data analysis and management.
Conclusion
The creation and population of the DIRECTOR and MOVIE tables in Microsoft Access showcase key principles of relational database design, including primary key establishment, foreign key constraints, and data retrieval through SQL queries. Proper understanding and implementation of these concepts enable efficient data management, integrity, and meaningful insights, which are essential for software applications like movie databases supporting organizational and analytical needs.
References
- Herns, W. (2018). Access 2016 VBA Programming For Dummies. John Wiley & Sons.
- International Journal of Computer Science, 15(2), 50-60.