Project

[Nuno Picareta]

Introduction to Data Analysis

I need a sql database from a rental game shop in Ireland. Where can I find it??

Project 2016

• The main objective of this project is the development of a database for a computer game rental shop.

• The project is composed of multiple sections or deliverables. • You may use any diagramming tool (Visio, Gliffy, Creately, Smartdraw etc.) to carry out the diagramming but the final work must be presented on paper.

• The construction portion of the project must be completed using MySQL.


Problem Description Our problem domain is an information system / database for a game rental store. The following are simplifying assumptions and details: ü It is a stand-alone shop, not part of a larger organization (the normalisation is an exception to this point). ü Rents only games, not movies or other items. ü A "game", can be for any platform: XBox, PlayStation, and so on. ü The rental charges may vary by platform or by title. For example, PS3 rentals are less expensive than PS4s. ü All transactions are rentals. ü The store does not sell anything. For example, there are no sales of games or food. ü The input medium by which membership and game rentals are captured is not important. ü Cash-only payments – no credit or cheques. ü Each renter has a separate membership. ü On completion of a rental, the customer receives a transaction report (a receipt) with 'typical' information on it - use your own judgment. ü Late returns incur extra fees.
ü For each deliverable, make a note of any assumptions or clarifications you make.
Introduction to Data Analysis Game Rental Project Page | 3

PART 1 – Relational Data Analysis Deliverable 1 (25%) - ERD • List the main entities in the above system. • Create an Entity Relationship Diagram using the Crow’s Foot notation. o Include Optionality & Cardinality. o Include at least one operational master.

Deliverable 2 (10%) - Normalisation • Normalise (to 3NF) the Overdue Games Record shown in Appendix 1. Give the fields good names (e.g. Game_Title).
PART 2 – Construct the system using MySQL (30%)
• Use MySQL to create a functioning system for the Game Shop based on relational database architecture. • There should be a very close relationship between the Tables & Relationships in your MySQL model & the Entity Relationship Diagram (deliverable 1). • Be careful to record complete transactions in the system o renting a game leads to returning a game; o deal with late returns o etc. • Create all the necessary tables and the relationships between them. Be careful to have appropriate Primary Keys, Foreign Keys and Constraints. • Populate the system with sufficient data in each table (at least 10 records, but more in some cases. o The use of a game dataset sourced online is encouraged. • Ensure all attribute names make sense.
Introduction to Data Analysis Game Rental Project Page | 4

PART 3 – Reports (15%) • Include the following reports: (Provide both the SQL statements and the resulting output.) 1. Show all rental transactions for a given week. 2. List all games & their platforms in order of Publisher. 3. List all games which came out in 2015. 4. List all the games currently on loan. 5. Detail and total all the late payments for the month-to-date. 6. Detail and total all revenue for the year-to-date. 7. Provide two different methods to list all PS4 games in stock (i.e. not currently out on a rental). § Which method is more efficient? How do you know?

PART 4 – Documentation and Analysis
• Include a report (2 pages max.) stating how you tested the database. (10%) • Include a report (2 pages max.) stating any design choices you made in developing the database. (10%)
Introduction to Data Analysis Game Rental Project