DRAFT: This module has unpublished changes.

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.

DRAFT: This module has unpublished changes.