Group Coursework
INST0001: DATABASE SYSTEMS
Chen, Jiajie/Li,jingwen
Tables of contents
Overview…………………………………2 Concept table……………………………..3 Conceptual model………………………….3 Key-words definition………………………4 Logical ER model…………………………4 Building the database………………………6 Coding queries…………………………….11
Appendix………………………………….14
1
Overview
X is an online shopping website which was opened in 2013 in London. Initially, it was a small, local shopping website with customers only living in London. However, with the development of e-commerce, the size of company X has grown steadily. The number of people living in the UK who purchase on this website has increased exponentially. Therefore, Company X now need to store more customer information and transaction history, moving from paper-based data storage to digital data storage i.e. a functional database. Furthermore, the current system of counting inventory is poor. The website always faces a situation of product shortage due to improper inventory management, preventing the success of the website. Meanwhile, the feedback from customers suggest that there is no valid invoice after purchasing a product. Therefore, the director of the company, Eric, knows that a database could solve all the arising problems and increase business efficiency.
X is a website sells various groceries, taking order online and delivering the product to client within the slot they booked. X sells products over 20 categories. Customers can browse the website and search for the product they want. During this process, customers may request to view one or more products in a specific category or within a fixed price range. Then, customers have to log in to the system before purchasing any product. If they do not have an account, a registration will be taken along with a confirmation email. Once they have logged in, they can start adding products to shopping cart as well as placing orders online and booking a delivery slot. The product they ordered before 3 p.m. can be delivered on the same day, otherwise, a next day delivery service will be provided. Immediately, when customer receives the order, an digital invoice will be sent automatically to their registered email. If customers want to return a product, they can fill a ¡®return¡¯ form and submit online. The administrator will receive their request and giving the customer an address to return the product.
Once the website receives the order, order information and shipping information will be passed to the manager of the warehouse. Staff in the warehouse will prepare ordered products for delivery. The staff who works for the warehouse need to count the inventory daily to ensure all products have enough stocks in the warehouse. The staff will ask for replenishment if any stock is lower than 20. They will contact with suppliers directly with the product name and its quantity. Suppliers will deliver the product within 2 or 3 days after the request for replenishment.
2
Concept Table
Concept Table
Who?
When?
Where?
Why?
How?
1. Customer
Minute
Online
Orders
Purchase Products
2. Customer
Daily
Online
View past orders
Invoice (digital receipt)
3. Customer
Minute
Online
Enquiries
Enquiry form
4. Supplier
Monthly
Warehouse
Replenish stock
Contract with supplier
5. Administrator
Daily
Online
Check stocks
Admin input
Conceptual Model
Utilisation of axis technique
3
screen-shot of conceptual model
Key-words definitions
Account: A way for customers to log in to their personal profiles. Category: A division of all products.
Product: Something customers can purchase on the website. Invoice: A list of products purchased by a customer.
Stock replenishment: fulfil the stocks to prevent product out-of-stock. Inventory: the stock for a particular product.
Item price: the price of a product.
Logical ER model 1NF
4
2NF
3NF
5
Building the database
The SQL code for the database:
6
The designer of the database:
MAIN TABLE
7
Category table
City table
8
Client table
Order table
9
Product table
10
Coding queries
1. A list of products which are below 20 Code:
Supplier table
11
Output:
2. A list of customers that live in UK that bought items above ¡ê20.00
Code:
Output:
3.A List of the products of the company that have sold the most in the last month
4. An invoice for customers
12
Code:
Output:
13
Appendix
Data dictionary:
14
15
16