ASSIGNMENT 1 LOGICAL DATABASE DESIGN (CPT307)

APRIL 5, 2015

ASSIGNMENT 1 LOGICAL DATABASE DESIGN (CPT307)

ASADHU SHUJAAU (33475) FACULTY OF SCIENCE

ASSIGNMENT 1

April 5, 2015

Table of Contents Database Design.............................................................................................................................. 2 Entity Relationship Diagram ....................................................................................................... 2 Business Rules: ........................................................................................................................ 3 Database and Table Creation Queries ......................................................................................... 4 Database Creation .................................................................................................................... 4 Branch Table............................................................................................................................ 4 Shop Table ............................................................................................................................... 4 Supplier Table.......................................................................................................................... 4 Sales Table ............................................................................................................................... 4 Product Table ........................................................................................................................... 5 Stock Table .............................................................................................................................. 5 Products Sold By Shops Table ................................................................................................ 5 Database Results ............................................................................................................................. 6 Daily Sales................................................................................................................................... 6 Managing Goods ......................................................................................................................... 8 Good and Amount in Stock ......................................................................................................... 9 Number of Items in Stock ......................................................................................................... 10 Cash Received Per Day ............................................................................................................. 11

P a g e 1 | 11

ASSIGNMENT 1

April 5, 2015

Database Design Entity Relationship Diagram

Figure 1: Supermarket ERD

P a g e 2 | 11

ASSIGNMENT 1

April 5, 2015

Supermarket database used in this assignment will consist of 7 tables as shown above. Including a table for Branch and Shop will help cater for future expansion of the supermarket chain. As shops increase new shops can be included in the shops table and branch table will help identify where it operates and which branch controls the shop. Also, the database is created with a table showing products sold by each shop (ProductSoldByShop). This will help to gain knowledge of the performance of each shop. The following are the business rules taken into consideration while creating the database. Business Rules: 

There are four branches and each shop will fall under any of the four, no other branch.



Each branch consists of many shops and each shop belongs to one branch.



Each supplier supplies many products and no two suppliers will supply same product.



Each shop has its own inventory.



Many sales can occur in same day hence sale date and a separate table for sale details are used.



Same product can be sold by many shops on same day.

Next section will show the database and table creation queries used for this database. The following queries and other queries has been provided in the CD attached with this document. Queries should be run in the ascending order with which it have been numbered.

P a g e 3 | 11

ASSIGNMENT 1

April 5, 2015

Database and Table Creation Queries Database Creation CREATE DATABASE Supermarket;

Branch Table CREATE TABLE Branch( BranchID NVARCHAR(3) NOT NULL PRIMARY KEY, BranchName NVARCHAR(20) NOT NULL, BranchStreet NVARCHAR(20) NOT NULL, BranchCity NVARCHAR(20) NOT NULL, BranchCountry NVARCHAR(20) NOT NULL );

Shop Table CREATE TABLE Shop( ShopID NVARCHAR(4) NOT NULL PRIMARY KEY, BranchID NVARCHAR(3) NOT NULL FOREIGN KEY REFERENCES Branch(BranchID) ON DELETE CASCADE ON UPDATE CASCADE, ShopName NVARCHAR(20) NOT NULL, ShopStreet NVARCHAR(20) NOT NULL );

Supplier Table CREATE TABLE Supplier( SupplierID NVARCHAR(4) NOT NULL PRIMARY KEY, SupplierName NVARCHAR(20) NOT NULL, SupplierEmail NVARCHAR(50) NOT NULL, SupplierPhone NVARCHAR(20) );

Sales Table CREATE TABLE Sale( SaleID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, SaleDate DATE NOT NULL, Revenue MONEY NOT NULL );

P a g e 4 | 11

ASSIGNMENT 1

April 5, 2015

Product Table CREATE TABLE Product( ProductID NVARCHAR(5) NOT NULL PRIMARY KEY, SupplierID NVARCHAR(4) NOT NULL FOREIGN KEY REFERENCES Supplier(SupplierID) ON DELETE CASCADE ON UPDATE CASCADE, ProductName NVARCHAR(20) NOT NULL, ProductDescription NVARCHAR(50) NOT NULL, Price MONEY NOT NULL, ReOrderLevel INT NOT NULL, ReOrderQty INT NOT NULL );

Stock Table CREATE TABLE Stock( StockID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ShopID NVARCHAR(4) NOT NULL FOREIGN KEY REFERENCES Shop(ShopID) ON DELETE CASCADE ON UPDATE CASCADE, ProductID NVARCHAR(5) NOT NULL FOREIGN KEY REFERENCES Product(ProductID) ON DELETE CASCADE ON UPDATE CASCADE, StockLevel INT NOT NULL );

Products Sold By Shops Table CREATE TABLE ProductSoldByShop( PSSID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ShopID NVARCHAR(4) NOT NULL FOREIGN KEY REFERENCES Shop(ShopID) ON DELETE CASCADE ON UPDATE CASCADE, SaleID INT NOT NULL FOREIGN KEY REFERENCES Sale(SaleID) ON DELETE CASCADE ON UPDATE CASCADE, ProductID NVARCHAR(5) NOT NULL FOREIGN KEY REFERENCES Product(ProductID) ON DELETE CASCADE ON UPDATE CASCADE, QtySold INT NOT NULL );

P a g e 5 | 11

ASSIGNMENT 1

April 5, 2015

Database Results Daily Sales When user inputs sales for each transaction, firstly, the stock gets reduced by the amount sold for the selected product. Then it calculated total price and enters into the sale table. Following images show these tasks.

Figure 2: Before Insertion Stock Level

Figure 3: Insertion Query Run Successfully

P a g e 6 | 11

ASSIGNMENT 1

April 5, 2015

Figure 4: StockLevel Reduced After Insertion

Next the total prices are calculated and inserted to Sales tables. Below are the results.

Figure 5: Sale Transaction Table

The above table shows the totals for each sale transaction with the date that transaction took place. Table below shows from which stock the products were sold from. From this, the related shop can be found along with the products as Stock table is linked to Shop and Product table.

P a g e 7 | 11

ASSIGNMENT 1

April 5, 2015

Figure 6: Sales Description Showing Totals and Quantity for Each Stock Item

Managing Goods All the goods or products are managed using Product Table. To help this table additional stock table is used which identifies the product along with which shop it belongs to. Also, these 2 tables show stock re-order levels and current stock level for the product. Email setup to send for suppliers could not be created hence email server cannot be installed into SQL server 2012 express addition. Below are screen shots for the tables.

Figure 7: Product Table For Managing Products

P a g e 8 | 11

ASSIGNMENT 1

April 5, 2015

Figure 8: Stock table showing stock for each Shop with Product and Stock Level

Good and Amount in Stock A join query was used to gain the required output. It is a join between Product and Stock which shows product description along with its name and stock level. Queries for this and aforementioned parts and upcoming parts are provided in the CD attached with this document. The query output could not fit in this page. So it has been moved to next page.

P a g e 9 | 11

ASSIGNMENT 1

April 5, 2015

Figure 9: SQL INNER JOIN Showing Product and Stock Level

Number of Items in Stock A simple query was enough to get the number of items in the stock.

Figure 10: Number of Items in Stock

P a g e 10 | 11

ASSIGNMENT 1

April 5, 2015

Cash Received Per Day A select query was used to generate the total revenue generated on each day. The following table has been ordered first by day then month then year.

Figure 11: Sale per Day

Other materials related to this Assignment are provided in the CD.

P a g e 11 | 11