Functional Requirements and Data Modeling for Star Trek Games Rental Shop

Business Description (Scenario) for the Assignment

You have been asked by the manager of the Star Trek Games rental shop to replace their existing system for recording rentals. Information about the shop is provided below

Note: the following information was collected during an investigation of the Star Trek Games Rental Shop

Star Trek Games Rental shop holds numerous games from a number of different developer companies. Previously the shop kept records of the rentals using a system originally intended just for PC games but this is no longer commercially supported. The manager of the shop wants to replace the old system with a more efficient and useful one. The new system needs to deal with games of different types such as Wii, Xbox, PlayStation, etc.

There are two ‘aspects’ (these are complementary sub-systems that make up the system) required for the new system: the rentals and the catalogue. The rental aspect should keep track of the shop’s membership and games rented to them. The catalogue aspect should hold product details of the games including developers as well as any reviews and highlights

The distribution companies which produce the games give their own unique catalogue number to each different game. Some games are in sets containing more than one disk. Many of the games are popular and there is often more than one copy of the same game held by the shop. The number of copies depends on popularity and demand. Every individual copy of a game is identified by a unique stock number, which is allocated by the manager of the shop when the item comes into stock. A set of disks for one game counts as a single stock item.

Anyone who wants to rent a game must first register their details to become a member. Each rental is for a specified period agreed at the time of rental (up to a maximum of 2 weeks), after which it can be renewed provided that there is no outstanding reservation for the same item. Normally a maximum of 3 games can be rented at the same time by one member. The issuing, renewal and return of games is carried out at the shop counter by a shop assistant. 

The cost of each rental is based on a scale of daily rates. Very new games are usually charged at the highest rate and there are a number of rates for other games. Renewals are charged at the same daily rates as the initial loan. If a member wants to rent a game and all the copies are already on loan, then they may make a reservation. If rentals are returned late then additional charges will be made for the extra days at twice the normal daily rate for the item. 

In order to ensure that additional copies are obtained for popular items, the manager wants to keep track of the rental history of games and will require various reports on the rentals. For audit purposes it will also be necessary to keep a record of each rental by a member after the game has been returned.

The manager of the shop wants to provide members with access to an electronic catalogue of the game collection via a terminal in the shop. This should allow a member to browse by genre, or to look for a specific game, or to search in different ways such as by release date, age rating or category. To make this into a useful resource the manager would like to hold brief product details as well as reviews of games where appropriate. The catalogue may eventually be made available to borrowers via the Internet but this is not part of the current requirements.

Using the Business Description

1. You are required to identify the functional Requirements for the Star Trek Games Shop.

2. You are required to derive a top-down Initial Entity-Relationship Diagram (Logical Data Model) for the Star Trek Games Shop.

3. Refer to the sample data given below. Normalise separately the Stock Enquiry, Membership & Rentals Enquiry, Reservations Enquiry, Rental History Data and Catalogue Data showing all appropriate Normal Forms for each normalisation task. You may include any additional attributes required for the new system that are not included in the examples given. State any assumptions you make.

4. Merge and optimise the sets of fully normalised relations that you have produced, giving them names and listing their attributes. Produce a Third Normal Form Entity Relationship Diagram derived from these merged relations.

5. Compare your Initial Data Model (question 1) and the Third Normal Form Entity Relationship Diagram you have just produced. Identify and explain any differences between these two diagrams and then produce a revised version of your EntityRelationship Model taking account of these differences. You do not need to label relationships that are clearly self-evident. State any new or changed assumptions. You should attempt to validate any assumptions that you make. You may request further information about any aspect of the case study. This additional information should be documented and included with your submission.