Database Design and Implementation Project for ICT701

Desklib provides solved assignments and past papers for students.

ICT701
Relational Database Systems
Task - 2

Table of Contents
Part A.........................................................................................................................................3
Crows Foot Notation..............................................................................................................3
Relational Schema..................................................................................................................4
3NF.........................................................................................................................................5
Assumptions:..........................................................................................................................5
Part B..........................................................................................................................................5
Description of the tables:.......................................................................................................5
Insertion data:.........................................................................................................................9
Select query1:.......................................................................................................................12
Select Query2:......................................................................................................................12
Function:..............................................................................................................................12
Trigger1:...............................................................................................................................12
Trigger2................................................................................................................................13
Trigger3................................................................................................................................13
References................................................................................................................................14
2

Part A
Crows Foot Notation:
Figure 1 ERD
3

Relational Schema
Figure 2 Relational Schema
4

3NF
Customer (Cust_no, Cust_name, Cust_cntct_no, Cust_address, Cust_postcode)
Inventory (Item_code, Description, RRP, Price, InStock,)
Suppliers (Supplier_id, Supplier_name, Type, Fax_no, Tel_no, Address)
Delivery_method (Method_id, Method, Extra_charge)
Salesman (Salesman_id, Salesman_name, Salesman_cntct_no, Comission_percentage,
Salesman_address)
Orders (Order_no, Cust_no, Supplier_id, Order_date, Required_date)
order_items ( Order_no, Item_code, Quantity, Total_exGST, GST, Total)
Invoice (Invoice_no, Order_no, Salesman_id, Delivery_method, Invoice_date,
Invoice_total)
Customer_balance (Cust_no, Invoice_no, Owing, Debt, Credit, Closing_balance, )
Assumptions:
 We have introduced Order_items table in order to remove the many to many to the
relationship between the Inventory and Orders entities.
 We have declared Order_no and Item_code as a composite primary in the Order_item
entity to uniquely identify each row.
 We have declared Cust_no and Invoice_no as a composite primary key in order to
uniquely identify each row of the Customer_balance table.
 We have taken Order_no, Salesman_id, Delivery_method as foreign keys in the
Invoice table.
 We have taken Cust_no, Supplier_id as foreign keys in the Orders table.
 We have taken Order_no and Item_code asa foreign key in the Order_items table.
We have taken one to one relationship between the orders and the Invoice table because only
one invoice can be created for one order (Link & Prade, 2019).
Part B
Description of the tables:
Customer Table:
5

Figure 3 customer table
Inventory table:
Figure 4 Inventory table
Suppliers table:
Figure 5 Suppliers table
Delivery_method table:
6

Figure 6 Delivery_method table
Orders table:
Figure 7 Orders table
Order_items table:
Figure 8 Order_items table
Salesman table:
7

Figure 9 Salesman table
Invoice table:
Figure 10 Invoice table
Customer_balance table:
Figure 11 Customer_balance table
8

Insertion data:
Customer Table:
Figure 12 Customer data
Salesman table:
Figure 13 Salesman data
Suppliers table:
Figure 14 Suppliers data
Orders table:
Figure 15 Orders data
Inventory table:
9

Figure 16 Inventory data
Order_items table:
Figure 17 Order_items data
Invoice table:
Figure 18 Invoice data
Customer_balance table:
Figure 19 Customer_balance data
10

Delivery_method table:
Figure 20 Delivery_method data
11

Select query1:
Figure 21 Query1
Select Query2:
Figure 22 Query2
Function:
Figure 23 function
Trigger1:
Figure 24 Trigger1
12