I Am Working On This Homework Using MySQL Database

I Am Working On This Homework Using Mysql Database Can You Help Atta

This assignment involves designing and querying a video rental database system using MySQL. The system consists of several tables: price, movie, video, membership, rental, and detail_rental. The goal is to demonstrate proficiency in creating schema, tables, relationships, inserting data, and executing queries to retrieve and manipulate data within this system.

Paper For Above instruction

The given database schema models a small-scale video rental store, capturing core entities such as movies, videos (physical copies), pricing tiers, members, rentals, and detailed rental transactions. The schema emphasizes normalization, establishing relationships between entities through foreign keys, which enforces data integrity and enables meaningful queries.

The database schema begins with the creation of a schema named TINY_VIDEO, emphasizing its lightweight design suitable for a small rental operation. The schema includes six primary tables:

  1. Price Table: Stores different pricing options available for movies, such as standard, new releases, discounts, and weekly specials. Each price has a unique ID, description, rental fee, and late fee.
  2. Movie Table: Contains records of movies, including title, release year, cost, genre, and a foreign key referencing the price table to determine rental fees.
  3. Video Table: Represents individual physical copies of movies (videos). Each copy has a unique ID, purchase date, and a foreign key linking to the movie it contains.
  4. Membership Table: Holds information about members, including their name, address, and account balance. It supports tracking member activity and fees owed.
  5. Rental Table: Records each rental transaction with rental date, linked to a member via membership ID.
  6. Detail_Rental Table: Provides detailed information on each video rented in a transaction, including fees, due dates, return dates, and late fees. It uses composite primary keys and foreign keys referencing rental and video tables.

The sample data inserts present realistic entries, including multiple movies with associated prices, videos, members, and rental transactions. Notably, some videos have missing purchase or rental dates, simulating ongoing or pending rentals. This setup allows for complex queries, such as calculating late fees, tracking inventory, or analyzing member activity.

Common SQL queries arising from this schema might include:

  • Retrieving all movies along with their current rental status.
  • Calculating total late fees accumulated for a particular member.
  • Identifying which videos are currently rented out and which are available.
  • Listing movies by genre or release year with their pricing.
  • Updating rental or return dates and fees for ongoing rentals.

By understanding and manipulating this schema, students demonstrate essential skills in relational database design, including table creation, data insertion, foreign key constraints, and crafting complex select queries to support business operations of a rental store.

References

  • Coronel, C., & Morris, S. (2019). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • MySQL documentation. (n.d.). Retrieved from https://dev.mysql.com/doc/
  • Fundamentals of Database Systems (7th ed.). Pearson.
    • Communications of the ACM, 13(6), 377-387. Database Design and Relational Theory. O'Reilly Media. Modern Database Management (12th ed.). Pearson. International Journal of Computer Science and Information Security, 18(5). Journal of Data Management, 33(4). IBM Redbooks. Oracle Tutorials.

Related Assignments