Database System Assignment: Design, Implementation, & Management

This report provides a comprehensive analysis of a database system designed for an online mobile store. It begins by outlining the requirements, including a list of queries and assumptions. The report details the Entity-Relationship (ER) diagram, relational database schema, and functional dependencies. It also covers the decomposition of the database, relational algebra expressions, and addresses unanswerable queries. Furthermore, the report includes the implementation of tables and provides SQL queries. The report concludes with a bibliography of relevant resources. This assignment showcases the design, implementation, and management of a database system in the context of an online mobile store, providing a detailed overview of database concepts and their practical application.

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
The application that is being chosen here is that of an online mobile store. The database
would store the details of the products available in the system and details of the product purchase
and customers as well.
Queries
1. List of all the brand available in the shop
2. List of all the orders made on 16 May 2018
3. List of names of the customer starting with a.
4. List of the products in the shop
5. List of all orders for a particular customer
6. List of all orders under a particular staff
7. List of all products under the brand “Nokia”.
8. What is Address of James
9. Contact of James
10. Which is the product with the highest price?
Assumptions
It is assumed that the store is online however the staffs of the system can access the
online system and handle the invoices.

2
DATABASE SYSTEM
ER Diagram
Relational Database Schema
Brand (brandID (pk), Name)
Product (prpductID (pk), brandID (fk), pName, Price)
Customer (CustomerID (pk), cName, cAddress, Contact)
Orders (OrderID (pk), productID (fk), customerID (fk), OrderDate)
Invoice (InvoiceID (pk), OrderID (fk), StaffID (fk), CustomerID (fk))
Staff (StaffID (pk), Name, sContact, Salary)
Functional dependencies
brandID Name
CustomerID cName, cAddress, 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 the to its highest from and can-not be
decomposed any further.
Relational algebra
1 π (brand)
2 π (σ OrderDate (Order) = 16 May 2018)
3 π cName (σ cName (Customer) = LIKE ‘%a’)
4 π (product)
5 π (σ CustomerID (Order) = n)
6 π (σ StaffID (Invoice) = n)
7 π (σ BrandName (prodcut) = Nokia)
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 brank NOKIA
2. The name of the person who purchased the brand APPLE
Implementation
Tables
Brand
Customers
Invoice

5
DATABASE SYSTEM
Orders
Product

6
DATABASE SYSTEM
Staffs
Query
1

7
DATABASE SYSTEM
2
3
4

8
DATABASE SYSTEM
5
6

9
DATABASE SYSTEM
7
8

10
DATABASE SYSTEM
9
10

11
DATABASE SYSTEM

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