Software Modeling and Design: Database Queries and ERD

This project focuses on software modeling and database design, presenting a comprehensive solution for creating and managing databases. The assignment begins with an overview of user interaction, setting the stage for database design. The core of the project involves creating SQL queries to define tables for an order management system, including tables for staff, customers, placed orders, suppliers, products, order lines, order returns, and invoices. Each table's structure is meticulously defined, including primary and foreign key relationships to ensure data integrity and efficient retrieval. The project also includes a bibliography with relevant sources. This project demonstrates the practical application of database design principles and SQL queries to build a functional and efficient database system. The solution is available on Desklib, a platform providing AI-based study tools and resources for students.

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.