GameUR Database Project

This project is part of the DATA1025 course, focusing on developing a database solution for GameUR, a startup providing a GAME kiosk service in Fredericton. GameUR rents out copies of games to its members and requires a structured database to manage memberships, rentals, game inventory, and transactional records.

Project Structure

This repository includes:

• SQL files containing DDL and DML statements for setting up the database and populating it with sample data.
• Stored procedures and triggers to implement business rules and handle specific database operations.
• Test scripts to validate the functionality of each SQL command, procedure, and trigger.

Requirements

The project is divided into two main parts:

Part 1: Database Setup and Basic Queries

• Table Creation: Implement table structures for entities such as Membership, Rental, DetailRental, GameCopy, Game, and Price, based on the E-R Diagram.
• Constraints: Define primary keys, foreign keys, and specific constraints:
  • Membership: Balance ≥ 0, no null values in Member Number, First, and Last Names.
  • Rental: All attributes are non-null.
  • DetailRental: All attributes are non-null except Return Date.
  • GameCopy, Game, and Price: Non-null attributes, with business rules on attributes like Rent Fee and Daily Late Fee.
• Data Insertion: Populate tables with sample data provided.
• Data Manipulation: Implement commands for updating and saving records in tables (Game, Price, etc.).
• Queries: Develop complex SQL queries for data retrieval based on conditions (e.g., games with costs above their genre's average cost, games rented on or before the due date, membership information).

Part 2: Advanced Operations

• Table Alterations: Add new attributes such as DAYS_LATE in DetailRental and GA_STATUS in GameCopy, with specific constraints.
• Updates and Constraints: Implement updates using subqueries and enforce value constraints for new columns.
• Stored Procedures:
  • PRC_ADD_GAMECOPY: Adds new game copies, validating existence and status.
  • PRC_MEM_INFO: Displays a member's info based on membership number.
  • PRC_NEW_RENTAL: Creates new rental records.
  • PRC_NEW_DETAIL and PRC_NEW_DETAIL_V2: Manages rentals and rental details, including multi-game rentals.
  • PRC_RETURN_GAME: Records game returns and updates relevant statuses.
• Triggers:
  • TRG_LATE_RETURN: Calculates days late when games are returned, updating DAYS_LATE accordingly.

Setup Instructions

1. Clone this repository:

   git clone https://github.com/RicoRF/SQLProject.git
   cd GameUR-Database-Project

2. Execute the SQL scripts in the following order:

   • part1_setup.sql: Initializes tables, constraints, and inserts sample data.
   • part1_queries.sql: Contains SQL queries specified in Part 1 requirements.
   • part2_operations.sql: Executes table alterations, stored procedures, and triggers.

3. Test the procedures and triggers by running part2_tests.sql, which includes validation for all scenarios specified in the requirements.

Usage

• Setup and Data Insertion: Use part1_setup.sql to create tables and populate data.
• Advanced Functionality: Execute part2_operations.sql for adding business rules, stored procedures, and triggers.
• Testing: Run part2_tests.sql to verify procedures, validate conditions, and simulate sample transactions.

Sample Screens and Results

Refer to the documentation for example output screens for procedures like PRC_MEM_INFO, PRC_NEW_DETAIL, and PRC_RETURN_GAME. These samples will guide you through expected outputs.

Contributing

Feel free to open issues or submit pull requests to improve the project.

License

This project is for educational purposes as part of DATA1025 coursework.