Database Design and Implementation for Game Shop

This assignment involves designing and implementing a database for a game shop. The database includes three tables: GameShop, Members, and Employees. The GameShop table contains information about the games sold, while the Members and Employees tables contain information about customers and employees respectively. The assignment also includes SQL queries to insert values into the tables, alter the values, and delete rows from the tables.

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.