(Solved) Assignment on Database System

Running head: DATABASE SYSTEM
Database System
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM
Logical Database Design
Choosing an application
For the assignment the chosen application used for description is that of Online Game
Shop. This is an online shop in which the gamers can register their details and buys the latest
games from the online store. The name of the games and the developers would be stored in the
database and also there would be staffs that would be processing the purchase of the gamers and
the names and the details of the staffs would also be stored in the system.
Queries
1. List of all the game developers registered in the system.
2. List of all the orders made on 28 April 2015
3. List of names of the Gamers starting with A.
4. List of the games available at the online store.
5. List of all orders for a particular gamer.
6. List of all orders under a particular staff
7. List of all games Created by “Ubisoft”
8. What is Address of Sammy
9. Name of the developer of the game “FIFA 2018”
10. Which is the Game with the highest price?
Assumptions
The only assumption made during the designing of the database is that the staffs would
be accessing the systems manually hence, the data of the staffs would be stored in the system and

2
DATABASE SYSTEM
the data that is provided by the system by the gamers would be automated and the data entered
by the staffs would be handled by the system manually.
ER Diagram
Relational Database Schema
Developer (developerID (pk), Name)
Game (GameID (pk), developerID (fk), GameName, Price)
Gamers (GamerID(pk), GamerName, GamerAddress, Contact)
Orders (OrderID (pk), GameID (fk), GamerID (fk), OrderDate)
Invoice (InvoiceID (pk), OrderID (fk), StaffID (fk), GamerID (fk))
Staff (StaffID (pk), Name, sContact, Salary)
Functional dependencies
developerID Name
GamerID GamerName, GamerAddress, Contact

3
DATABASE SYSTEM
OrderID productID, customerID, OrderDate
productID brandID, pName, Price
InvoiceID OrderID, StaffID, CustomerID
StaffID Name, sContact, Salary
Decomposition
The database is already decomposed to its highest from and can-not be decomposed any
further.
Relational algebra
1 π (developer)
2 π (σ OrderDate (Order) = 28 April 2015)
3 π GamerName (σ GamerName (Gamers) = LIKE ‘%a’)
4 π (game)
5 π (σ GamerID (Order) = n)
6 π (σ StaffID (Invoice) = n)
7 π (σ DeveloperID (Game) = ‘’)
8 π Address (σ Name (Customer) = James)
9 π Contact (σ Name (Staffs) = James)
10 π(σ Price (Product) = maximum)

4
DATABASE SYSTEM
Unanswerable queries
1. The name of the staff who sells the gamers that re developed by “EA Sports”
2. The name of the Gamer who bought products of “EA Sports”
Implementation
Tables
Developers
Game
Gamers

5
DATABASE SYSTEM
Invoice
Orders

6
DATABASE SYSTEM
Staffs
Query
1

7
DATABASE SYSTEM
2
3
4

8
DATABASE SYSTEM
5
6
7

9
DATABASE SYSTEM
8
9
10

10
DATABASE SYSTEM
Bibliography
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Gould, H. (2015). Database Design and Implementation.