Solved: Star Trek Games Rental Shop

Analysis and Database Design of Star Trek Games Shop

Using the Business Description below:

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 Entity- Relationship 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.

You should also consider:

- If there are any other (possible) entities, attributes or relationships for which you require more information

- Any additional investigations you wish to carry out to validate your final model to support the assumptions you have made

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.

Sample Data

Some examples of the output for enquiries that can be made using the current system are provided below. There are data requirements for the new system that are not included in the

current system such as records of rentals that have been returned and more information about the Games for the catalogue. Suggestions for this data are provided but you should consider carefully if these are adequate and make any further additions you find necessary. Explain any such additions.

Stock Enquiry Screen from the old system.

Membership & Rentals Enquiry Screen from the old system. The current system does not handle charges and payments for rentals.

Reservation Enquiry Screen from the old system. Includes copies held and current rentals.

Rental History Data

No records of completed rentals exist in the current system. This should be included in the new system. The data required by the shop manager for each completed rental is as follows:

Stock No. Catalogue No. Platform

Title/Short Description

Member (Borrower) ID Member Name

Date issued

Date due - date game should have been returned

Date returned - date game was actually returned

Overdue (yes or no) - indicates if game was late being returned

Total rental charges paid - requires charges for current rentals to be recoded

Catalogue Data - available for borrowers (as well as staff) to search.

This is not included in the current system but the new system should provide some details about contents of the games. The following suggestions for the required information were

made by the Shop Manager but you should decide what needs to be included. You may include other attributes not listed below.

Platform, Catalogue No. and Title

Number of disks

Synopsis and Review(s?)

Category, Genre, Age Rating, Release Date

Screenshots

User Rating

Further examples of the information that could be held for each game can be found by looking by visiting suitable web sites selling or renting games.

Submitting your work

To be submitted online on Blackboard. Please keep a copy of your work.

As with any piece of coursework, there may be a temptation to take �short cuts�, especially if the topic is new, unfamiliar or difficult. �Copying the work of another, or allowing someone to copy your work is an academic offence. The temptation is particularly strong if the work is individual. Remember that if you are having problems with any piece of academic work (whether assessed or not) you should always consult your module tutor first. �Please check out the regulations on Examination and Assessment Offences at DMU.