Assignment 3 SQL Problem 1 Through 19 Note Please Be Loyal T

Assignment 3sqlproblem 1 Through 19note Please Be Loyal To The Data

Please create SQL table structures based on the provided ERD for the RentalAVideo database, ensuring appropriate data types and constraints. Insert sample data into the tables. Write SQL queries to perform specific updates, retrievals, aggregations, and sorting based on the given scenarios, such as updating movie years, pricing, listing movies with certain criteria, and calculating rental fees. Document your decisions and ensure relationships and constraints are properly enforced. Provide a complete set of SQL statements for table creation, data insertion, and query execution.

Sample Paper For Above instruction

The RentalAVideo database is designed to manage the operation of a video rental store, tracking movies, copies (videos), members, rentals, and related financial transactions. Proper database schema design, data insertion, and complex querying are essential to ensure operational efficiency and accurate reporting.

Database Schema Design and Implementation

Implementing the database begins with designing the tables that mirror the ERD provided. Each entity such as MOVIE, VIDEO, MEMBERSHIP, RENTAL, and DETAILRENTAL requires a table with suitable data types and constraints. The MOVIE table should include attributes like Movie_Num (as primary key), Movie_Name, Movie_Year, Movie_Cost, Movie_Genre, and Price_Code. The VIDEO table acts as a bridge to represent the multiple copies of a single movie, with Vid_Num as primary key and Movie_Num as foreign key referencing MOVIE. The MEMBERSHIP table stores member details with Mem_Num as primary key and relevant personal data fields. RENTAL captures rental transactions, linked to MEMBERSHIP via Mem_Num, with Rent_Num as primary key and Rent_Date. The DETAILRENTAL table handles the many-to-many relationship between RENTAL and VIDEO, including attributes like Rent_Num and Vid_Num as composite primary key, along with other renting details.

Data Type Decisions and Constraints

Choosing data types is critical for efficiency and accuracy. For example, numeric identifiers like Mem_Num, Vid_Num, and Movie_Num should be integers, while dates such as Rent_Date and Detail_Duedate should use DATE data type. Financial figures such as prices and fees should use DECIMAL(5,2) for precision. Character fields like names and addresses should use VARCHAR with appropriate length limits. Constraints such as PRIMARY KEY on ID fields and FOREIGN KEY references establish data integrity, enforcing valid relationships between tables.

Data Insertion and Testing

Post table creation, sample data must be inserted to test system functionalities. The data provided includes member records, rental transactions, video copies, and movie details. Inserting at least two rows per table helps verify relationships and queries. Using INSERT statements with specific data ensures the database is primed for executing the subsequent required queries.

SQL Operations and Queries

The core task involves executing various SQL commands to update, retrieve, and analyze data. For example:

  • Updating the Movie_Year for a specific movie:

    UPDATE MOVIE SET Movie_Year = 2006 WHERE Movie_Num = 1245;

  • Changing the Price_Code for all Action movies:

    UPDATE MOVIE SET Price_Code = 3 WHERE Movie_Genre = 'ACTION';

  • Increasing all price rental fees by 0.50:

    UPDATE Price SET Price_Rentfee = Price_Rentfee + 0.50;

More complex queries involve multi-criteria selections, sorting, aggregations, and calculations. Examples include fetching movies sorted by genre and year, listing movies starting with "R", movies containing the word "hope", and summarizing rental balances.

For example, to list movies sorted by genre and year:

SELECT Movie_Name, Movie_Year, Movie_Genre

FROM MOVIE


ORDER BY Movie_Genre ASC, Movie_Year DESC;

To count the number of movies per genre:

SELECT Movie_Genre, COUNT(*) AS Num_Movies

FROM MOVIE


GROUP BY Movie_Genre;

Similarly, calculating total rental fees per member involves summing the detail fees associated with each member's rentals, taking care to exclude late fees if necessary.

Conclusion

This comprehensive approach to designing, populating, and querying the RentalAVideo database ensures data integrity, supports operational needs, and facilitates detailed analyses. Following best practices with constraints and data types optimizes performance and maintains consistency, supporting the video rental store's management functions effectively.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments