Albert Guillermo
Professor Mahmood
Database Design
May 8, 2013
Application Overview
Gaming Cadre first opened in Bristol, CT late 2012 and allows customers to buy and sell new and used video games that would otherwise not be available in other markets. In addition they have available various gaming accessories. Surprisingly, its popularity had risen at a rate faster than expected and it called for an expansion of the store. Due to the rising popularity the store was busier than usual and needed another full-time worker. The owner decided that he would need to keep track of everything with a new database.
This database will keep on file the names of all employees as well as their job title and the products held within the store and the cost of each product new and used. The products themselves will have information pertaining to its price new and used, its condition, its category of product, and its rating. The employees will have their names, age, gaming expertise, position and hometown on file.
Mission Overview
Mission Statement
The purpose of the Gaming Cadre Database is to maintain a directory of the new and used games as well as providing management with employee data.
Mission Objectives
- To maintain (Add, remove, update) data on games
- To maintain (Add, remove, update) data on accessories
- To maintain (Add, remove, update) data on systems
- To maintain (Add, remove, update) data on staff
- To perform searches on games
- To perform searches on accessories
- To perform searches on systems
- To perform searches on staff
Database Scope
The purpose is to allow for Inventory of Games, Systems, and Accessories between the store and Warehouse and Information on the Staff who manage the store. The database does not account for the wages of the Staff or any information on the Customers.
Requirements Specification
Data Requirements
Staff
Gaming Cadre has four staff members, one is a full time Manager while a second is a full time Assistant Manager. The remaining two staff members are part time. The data stored for each member of the staff includes their staff number, last name, first name, date of birth, hometown, gaming expertise, and the shift they cover. The staff number uniquely assigned.
Games
Normally, the number of games varies. In most cases there are multiple copies of the same game. The database will contain information for the product number, game name, rating, condition, genre, system, and the price. They range in ratings between Everyone (E), Teen (T), and Mature (M) in relation to the age groups. All games have a physical condition either New or Used. There may be more than one system a game is made for and each game has a Genre. The product number will be unique.
Systems
The number of systems within a store and warehouse are extremely low compared to the number of games. Each game is played on a particular system and each system will play a many games. The database will hold information for the product number, the system name, the type of system, its condition, and the price. The two system types will be either Handheld or Console. The condition will be either New or Used. The product number will be unique.
Accessories
Accessories range between chairs, headsets, controllers, or guidebooks. The database will contain information for the product number, Accessory name, condition, and price. The product number will be unique.
Transactions Requirements (SAMPLE)
Data Entry
- Add the details of a new Staff Member (such as Peter Griffin)
- Add the details of a new Game (such as Tower Wars)
- Add the details of a new System
- Add the details of a new Accessory
Data Update/Deletion
- Update/Delete the details of a Staff Member
- Update/Delete the details of a Game
- Update/Delete the details of a System
- Update/Delete the details of an Accessory
Data Queries
- List the names of all Staff
- List the names of Games belonging to a system
- List the names of Systems that are new
- List the names of Accessories that belong to a specific System
Data Dictionary
Entity | Description | Aliases | Occurrence |
Staff | Term describing the employees of Gaming Cadre | Associate, Staff | Each staff member has a title, shift, and an area of expertise |
Games | Term describing a product type | Games | Games sold by staff belong to one or many systems |
Systems | Term describing a second product type | System | Each system has a physical type and can play many games |
Accessories | Term describing a third product type | Strategy Guide, Controller, Headset | There are many accessories that may/may not be associated with a specific system |
Entity Relationship Diagram
Relational Model
1. Derivation of Relations from ERD
Staff (StaffNum text, LastName text, FirstName text, Birthdate date, Hometown text, Position text, Expertise text, Shift text)
Games (ProductNum text, GameName text, Rating text, Genre text)
GameCopies (ProductNum text, Condition text, Price currency)
Systems (ProductNum text, SystemName text, SystemType text)
System Copies (ProductNum text, Condition text, price currency)
Accessories (ProductNum text, AccessoryName text, price currency)
GameCompatibility (ProductNum text, SystemName text)
AccessoryCompatibility (ProductNum text, SystemName text)
SQL Queries (sample)
The following queries were selected to reflect a portion of the transaction requirements. They were formed using Structured Query Language (SQL) and are some examples of the queries that I created for my database.
Query Name: Playstation3Games
Data Requirement: List the Games that can be played on the Playstation 3 system.
SELECT Games.GameName, GameCompatibility.SystemName
FROM Games, GameCompatibility
WHERE Games.ProductNum=GameCompatibility.ProductNum
AND GameCompatibility.SystemName="Playstation 3";
Query Name: Xbox360Accessory
Data Requirement: List the Accessories that are used with the Xbox 360 System.
SELECT Accessories.AccessoryName, AccessoryCompatibility.SystemName
FROM Accessories, AccessoryCompatibility
WHERE Accessories.ProductNum=AccessoryCompatibility.ProductNum
AND AccessoryCompatibility.SystemName="Xbox 360";
Query Name: StaffExpertise
Data Requirement: List the Staff Number, last name, first name, and expertise of each staff member. Order by Staff Number.
SELECT StaffNum, LastName, FirstName, Expertise
FROM Staff
ORDER BY StaffNum;
Query Name: UsedSystemPrice
Data Requirement: List the Product number, System name, System type, Condition, and Price of all systems that have the “Used” Condition.
SELECT Systems.ProductNum, Systems.SystemName, Systems.SystemType, SystemCopies.Condition, SystemCopies.Price
FROM Systems, SystemCopies
WHERE Systems.ProductNum=SystemCopies.ProductNum
AND SystemCopies.Condition="Used";
Database Use Documentation
This database is intended for use by all Gaming Cadre Staff. When you first enter the database, you are presented with a login page to document all use by each staff member.
Manager Entrance
Logging in as a store Manager will allow you to view all aspects of the database, along with the logged use of all staff members and their individual sales. All entries can be altered with notation.
Assistant Manager Entrance
Logging in as an Assistant Manager will allow you all the same capabilities as Manager except for the ability to alter entries
Staff Entrance
Logging in as a regular Staff Member will allow you to search the system for products and perform sale transactions.
Exit Database
After a brief pause to save all logs, the database will shut down after this button is pressed.