SOFTWARE MODELING AND DESIGN.

Running head: SOFTWARE MODELING AND DESIGN
Software Modeling and Design
Name of the Student
Name of the University
Author’s note:

1SOFTWARE MODELING AND DESIGN
Task 1: User Interaction

2SOFTWARE MODELING AND DESIGN
Task 2: Database
SQL Queries to Create Tables:
CREATE TABLE Staff (

3SOFTWARE MODELING AND DESIGN
staff_id int PRIMARY KEY,
name varchar(200),
address varchar(200),
contact_number int,
email varchar(200),
join_date Date,
qualification varchar(200),
designation varchar(200),
salary Decimal(12,2),
status varchar(200)
);
CREATE TABLE Customer (
customer_id int PRIMARY KEY,
business_name varchar(200),
postal_address varchar(200),
contact_number int,
email varchar(200),
contact_name varchar(200)
);
CREATE TABLE Placed_Order (
order_id int PRIMARY KEY,
staff_id int,
customer_id int,
date_of_order Date,
date_of_required_order Date,
FOREIGN KEY (staff_id) REFERENCES staff(staff_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);
CREATE TABLE Suppliers (
supplier_id int PRIMARY KEY,

4SOFTWARE MODELING AND DESIGN
supplier_name varchar(200),
postal_address varchar(200),
contact_number int,
fax_number int
);
CREATE TABLE Product (
product_id int PRIMARY KEY,
supplier_id int,
product_name varchar(200),
quantity_per_case int,
case_price Decimal(12,2),
cases_in_stock int,
FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)
);
CREATE TABLE Order_Line (
order_id int,
product_id int,
quantity int,
FOREIGN KEY (order_id) REFERENCES Placed_Order(order_id),
FOREIGN KEY (product_id) REFERENCES Product(product_id),
PRIMARY KEY(order_id, product_id)
);
CREATE TABLE Order_Return (
order_id int,
product_id int,
quantity int,
FOREIGN KEY (order_id) REFERENCES Order_Line(order_id),
FOREIGN KEY (product_id) REFERENCES Order_Line(product_id),
PRIMARY KEY(order_id, product_id)
);

5SOFTWARE MODELING AND DESIGN
CREATE TABLE Invoice (
invoice_id int PRIMARY KEY,
order_id int,
sub_total Decimal(12,2),
vat int,
total_cost Decimal(12,2),
FOREIGN KEY (order_id) REFERENCES Placed_Order(order_id)
);

6SOFTWARE MODELING AND DESIGN
Bibliography:
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Jagtap, S., Gawde, V., Pawar, R., Shendge, S., & Avhad, P. (2016). Generate Test Cases From
UML Use Case and State Chart Diagrams. International Research Journal of
Engineering and Technology (IRJET), 3, 873-881.