Database Project: Game Shop Database Design and Implementation

This project focuses on the design and implementation of a database for a game shop. The assignment begins with an overview of the company's operations, highlighting the need for a database to manage information related to employees, members, and products. Part A provides a general introduction to the company and its database needs. Part B presents the Entity-Relationship Diagram (ERD) of the database, visually representing the relationships between entities such as GameShop, Members, and Employees, along with their attributes. Part C details the creation of tables using SQL, including the GameShop, Members, and Employees tables, complete with screenshots of the table structures. The assignment also includes database testing, demonstrating the insertion of values, altering table structures with foreign keys, and deleting rows. The project concludes with a bibliography referencing relevant database design principles and methodologies. This is a complete database project with ERD, table creation, and SQL implementation.

Game shop Database
Name of the Student:
Name of the University:

Table of Contents
Part A...............................................................................................................................................3
Part B...............................................................................................................................................3
Part C...............................................................................................................................................4

Part A
1. The company in this case study is about the game store who sells game of different genre.
The database and ERD of the company is shown in this report about how the company
deals with all the data in company.
2. There are mainly three types of employees in the company. One employee deal with the
company IT operations at the back end of the office, two shop attendants are there to
attend the customers, and a manager who handles all the operations.
3. To create connectivity among all the functions in company, a database is created to keep
the record of all data that are dealt in the company.
Part B
1. The ERD of the Queens Street Video Game Rental Shop
Figure 1: ERD of Queens Street Video Game Rental Shop

2. Logical Design of the ERD
Entities Attributes
GameShop ProductID, MemberID, EmpID, GameGenre,
GamePrice, ShippedFrom
Foreign key (MemberID) references Members
(MemberID)
Foreign key (EmpID) references Employees (EmpID)
Members MemberID, EmpID, MemberFName, MemberFName,
MemberPhone, MemberAddress, MemberEmailID,
MembershipPlan
Foreign key (EmpID) references Employees (EmpID)
Employees EmpID, EmpFName, EmpLName, EmpAddress, EmpPhone,
EmpEmailID, EmpDesignation
Table 1: Logical Diagram of Queens Street Video Game Rental Shop
Part C
1. Creation of table
GameShop table:
CREATE TABLE `GameShopDatabase`.`GameShop` ( `ProductID` INT(10) NULLAUTO_INC
REMENT , `MemberID` INT(10) NULL , `EmpID` INT(10) NULL , `GameGenre`VARCHAR(

30) NULL , `GamePrice` INT(20) NULL , `ShippedFrom` VARCHAR(30) NULL ,PRIMARY
KEY (`ProductID`)) ENGINE = InnoDB;
Screenshot 1: Screenshot of the table GameShop
Member table:
CREATE TABLE `gameshopdatabase`.`Members` ( `MemberID` INT(10) NULLAUTO_INCR
EMENT , `EmpID` INT(10) NOT NULL , `MemberFName` VARCHAR(20) NOT NULL, `Me
mberLName` VARCHAR(20) NOT NULL , `MemberPhone` INT(10) NOT NULL ,`MemberAd
dress` VARCHAR(50) NOT NULL , `MemberEmailID` VARCHAR(30) NOT NULL ,`Member
shipPlan` VARCHAR(50) NOT NULL , PRIMARY KEY (`MemberID`)) ENGINE =InnoDB;

Screenshot 2: Screenshot of Member table
Employee table:
CREATE TABLE `gameshopdatabase`.`Employees` ( `EmpID` INT(10) NULLAUTO_INCRE
MENT , `EmpFName` VARCHAR(20) NOT NULL , `EmpLName` VARCHAR(20) NOTNUL
L , `EmpAddress` VARCHAR(30) NOT NULL , `EmpPhone` VARCHAR(10) NOT NULL ,`E
mpEmailID` VARCHAR(30) NOT NULL , `EmpDesignation` VARCHAR(20) NOT NULL ,PR
IMARY KEY (`EmpID`)) ENGINE = InnoDB;

Screenshot 3: Screenshot of Employee table
2. Database Testing
Inserting Values in the table:
INSERT INTO `gameshop` (`ProductID`, `MemberID`, `EmpID`, `GameGenre`, `GamePrice`, `
ShippedFrom`) VALUES ('018', '508', '2', 'destiny', '2500', 'UK');
Altering Value in the table:
ALTER TABLE `gameshop` ADD FOREIGN KEY (`MemberID`) REFERENCES`members`(`
MemberID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `members` ADD FOREIGN KEY (`EmpID`) REFERENCES`employees`(`Em
pID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
Deleting rows from a table:

DELETE FROM `members` WHERE `members`.`MemberID` = 508
DELETE FROM `gameshop` WHERE `gameshop`.`ProductID` = 17

Bibliography
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.