University Database Design Project: IMAT2428 Star Trek Games Shop

This document provides a comprehensive solution to the IMAT2428 individual assignment, focusing on the analysis and database design for a Star Trek Games shop. The solution begins by identifying the functional requirements of the system, including features like customer and employee ID assignment, game information search, rental management, overdue fine calculation, and stock level tracking. An initial Entity-Relationship Diagram (ERD) is presented, followed by the normalization of key tables such as Rental, Game, and Supplier tables, progressing through 1NF, 2NF, and 3NF. The document compares the initial and 3NF ERDs, highlighting the reduction of data redundancy and improved query efficiency. Assumptions made during the database design are also stated. The assignment concludes with a bibliography of referenced sources.

Running head: IMAT2428: DATABASE MANAGEMENT SYSTEM
IMAT2428: Database Management System
Name of the Student
Name of the University
Authors note

1IMAT2428: DATABASE MANAGEMENT SYSTEM
Functional Requirements for Star trek Games
Proposed system will be able to assign unique IDs for the customers as well as to the
employees of the organization to track their activities.
The proposed information system shall allow different members in order to search game
information.
The system shall allow members to rent games and record the issue and return date for
specific rentals.
Proposed system prompt notice for the rented Games that exceeded the assigned return
date.
It shall allow the employees to calculate the overdue fines as well as record the payment
of the overdues.
Employees of the organization will be able to process returned games in order to
maintain the stock with the real time data.
The system shall be able to manage the stock level of the specific games in the database
that will be used as the back end of the system.
The system should be able to track the number of rentals for a member id as stored
previously.

2IMAT2428: DATABASE MANAGEMENT SYSTEM
Initial ERD Design

3IMAT2428: DATABASE MANAGEMENT SYSTEM
Normalization of the Tables
Rental Table
1NF 2NF 3NF
RentalID RentalID
MemberID
51456
51231
45632
StockNumber
51456
51231
RentalID
MemberID
51456
51231
45632
StockNumber
51456
51231
StockNumber
6623 StockNumber
Issue date
ReturnDate
RenewDate
MemberID
51456
55347
MemberName
DateCreated
ActiveStatus
MemberID
51456
51231
45632
StockNumber
Issue date
ReturnDate
RenewDate

4IMAT2428: DATABASE MANAGEMENT SYSTEM
Issue date FirtsName
Drummer
Great
ReturnDate
15/12/13
11/12/13
Phone
01752633212
RnewalDate
16/12/13
Phone
01752633212
Address
Plymouth PL1 6TY
Game table normalization
1NF 2NF 3NF
StockNumber
6623
StockNumber
6623
GameName
Marvel Lego
NumDisks
2
4
Number_of_Copies
2
Release Date
25/12/12
Game
StockNumber
6623
GameName
Marvel Lego
NumDisks
2
4
Number_of_Copies
2

5IMAT2428: DATABASE MANAGEMENT SYSTEM
Age_rating
18 GameDetails Table
StockNumber
GameRating
Genre
Platform
Supplier
Games
Amazon
Supplier table
SupplierID
SupplierName
CatalogueID
GameName
Marvel Lego
Heroes
Jewel Link –
Arctic Quest
Angry Birds
Trilogy
Catalogue Number
1254
Catalogue Title
Arcade Gems
Catalogue creation
Date
12/08/12
Supplier
Games
Amazon

6IMAT2428: DATABASE MANAGEMENT SYSTEM
NumDisks
1
4
1
Number_of_Co
pies
2
Release Date
25/12/12
Age_rating
18
Catalogue
Number
1254
Genre
Arcade
Car Race
Platform
Xbox
3DS

7IMAT2428: DATABASE MANAGEMENT SYSTEM
Playstation
1NF 2NF 3NF
RentalID RentalID
customerID
StockNumber
Issue Date
ReturnDate
Renewal Staus
RentalID
customerID
StockNumber
Issue Date
ReturnDate
customerID Renewal table
RenewalID
RenewalDate
OverduePaid
StockNumber Renewal table
RenewalID
RenewalDate
OverduePaid
RenewalStatus
Issue Date
ReturnDate
RenewalDate

8IMAT2428: DATABASE MANAGEMENT SYSTEM
1NF 2NF 3NF
ReservationRequestID ReservationRequestID
MemeberID
StiockNumber
ReservationRequestID
MemeberID
StiockNumber
MemeberID
StiockNumber ReservationRequestID
CopiesHeld
ResevationStatus
CopiesHeld ReservationRequestID
CopiesHeld
ResevationStatus
StockNumber
RentalPrice
ResevationStatus

9IMAT2428: DATABASE MANAGEMENT SYSTEM
ERD diagram after normalization

10IMAT2428: DATABASE MANAGEMENT SYSTEM
Comparison of the initial and 3NF ERD
At the initial stage the ERD is designed in order to get the idea about the required tables
for the database. In that stage, there are possibilities of data redundancy and inconsistency.
Therefore, through the normalization process to reduce the minimize the data redundancy. In
addition to that, in the normalized ERD the null values in them can be minimized, development
of more compact database. Through this process it will be possible to get the required data from
simplified queries.
Assumptions made for the Database design
The system shall be able to generate reports according to the criteria specified by the
user.
As the rental of a Game should not exceed the time limit of three weeks thus while
assigning return date for a rental the system should prompt error against the rental to the
user/employee.
After a game is returned, before recording any rental the employee should check if there
is any reservation request from any other member.

11IMAT2428: DATABASE MANAGEMENT SYSTEM
Bibliography
Asayag, M. and Kenigsberg, D., Red Hat Israel Ltd, 2015. Normalized management network.
U.S. Patent Application 14/188,205.
Brodt, A., Martin, D., Mueller, J.P., Schiller, O. and Stolze, K., International Business Machines
Corp, 2019. Consistent execution of partial queries in hybrid DBMS. U.S. Patent Application
10/262,002.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Hoffer, J., Venkataraman, R. and Topi, H., 2015. Modern database management. Prentice Hall
Press.
Tomoda, A., Isoda, Y. and Ushijima, K., Hitachi Ltd, 2016. Database management system and
method. U.S. Patent Application 14/894,042.