Assignment 3

CSC-217 Intro to Database Systems

Spring 2025

Page 1 of 19
 Assignment 3
Complex Computing Problem
Semester : Spring 2025
Program : BS CS
Course Title and Name : CSC-217 Intro to Database Systems
Faculty : Noor-ul-Huda
Student Name : Abdur Rafay Ali Khan
Abdur Rahman
Muhammad Abbas
Student ID : 20231-34594
20231-35231
20231-34578
Project Title : Optician’s Management System
Submitted Date : 21-05-2025
Total Marks : 09
Obtained Marks :
 Table of Contents
1. Introduction ............................................................................................................... 3
2. Methodology ............................................................................................................... 3
3. System Design ............................................................................................................ 4
4. Implementation Details ............................................................................................. 7
5. Results and Discussion ............................................................................................ 16
6. Conclusion ................................................................................................................ 18
1. Introduction

Problem Statement and Motivation

This project was motivated by the need of an optician’s shop to have a complete
database system for the management of their store to transition from the current
hybrid paper and flat file spreadsheet-based approach that they employ to track
customer data, orders and prescriptions. Such a system will allow them to centralize
and organize their data and to analyze it for trends and make inferences as to the
health of their business, reduce gaps in communication and deal with clients promptly
and efficiently.

Scope and Objectives of the Project

The scope of this project involves the development of a database system and its
implementation in MySQL Workbench to allow shop employees to manage their
customer, product and prescriptions data in a simple and efficient solution for both
opticians and sales staff.

Core feature of the system will include:

• Customer Information Tracking: a centralized repository of customer data from

personal details to contact information linked to their prescriptions, orders and
appointments.
• Order and Inventory Management: The system will allow users to log and track all
customer orders and have live inventory updates for new orders.
• Appointments: Tracking customer appointments for fittings and eye tests.

2. Methodology

Requirement Analysis

Initially it was observed that the optician’s employees were using a flat file
spreadsheet to hold the data of each customer per row of the spreadsheet along with
only their latest prescription details. All orders were being tracked solely on paper
receipts, and no strict information of employees was being tracked or logged.
Products and suppliers were also tracked only in a paper notebook with no proper live
inventory of product stock on hand. By analyzing these ad hoc methods we outlined
the essential needs of our system, from customer management to prescription and
order management to allow the optician’s shop to operate fluidly and with
transparency.

Design

After requirements gathering and analysis a preliminary system was designed using
an Entity-Relationship Diagram to map out key entities such as customers,
 employees, orders, prescriptions, products, appointments, and suppliers. This ERD
then formed the basis of our schema in MySQL Workbench. Using primary keys and
foreign keys to create relationships we were able to arrive at a normalized schema that
eliminates data redundancy and maintains data consistency.

Implementation

The system was implemented in MySQL Workbench using Data Design Language
SQL commands. Further we inserted sample data into tables to create database
instance to represent a live and fully functional state.

Tools and Technologies Used

• MySQL Workbench
• MySQL Server

3. System Design

Relational Schema

We developed a schema with the following entities:

• Customers: to represent individual customers and their data.

• Employees: to represent shop employees such as salespersons, opticians etc.
• Appointments: to represent customer appointments for fittings and eye tests.
• Orders: to represent customer orders.
• Order Items: to represent individual items that comprise a customer order.
• Prescriptions
• Products: to represent products for purchase such as frames and lens blanks.
• Suppliers: to represent the suppliers for the products the shop stocks.

Relationships

• Customer places order (one to many)

• Employee processes Order (one to many)
• Customer books Appointment (one to many)
• Appointment generates Prescription (one to many)
• Customer has Prescription
• Employee handles Appointment
• Order contains Order Item
• Prescription attached to Order Item
• Supplier supplies Product
• Product included in Order Item
Entity-Relationship Diagram
Reverse Engineering with MySQL Workbench

Normalization Process

Our database system was normalized to Third Normal Form (3NF) to eliminate data
redundancy and maintain consistency of all related data.

First Normal Form (1NF)

 • At this stage all our tables have purely atomic values i.e. no multivalued attributes.
• Each record in each table was identified by a unique primary key

Second Normal Form (2NF)

• From 1NF to 2NF we aimed to eliminate partial dependencies and thus for each
product’s supplier who were initially only in the product table we moved them to an
independent table of their own linking them to the products table with a foreign key
of supplier ID.

Third Normal Form (3NF)

• We now moved to eliminate any transitive dependencies, since the orders table
contained duplicated data for each order item, we split this off into an order items
table where each order item along with a quantity references a specific order in the
orders table.

4. Implementation Details

SQL Schema Creation with DDL Script

CREATE DATABASE dbs_project;
USE dbs_project;

-- CUSTOMERS
CREATE TABLE customers (
customer_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
dob DATE,
phone VARCHAR(20),
email VARCHAR(200),
address VARCHAR(400),
PRIMARY KEY (customer_id)
);

-- EMPLOYEES
CREATE TABLE employees (
employee_id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
role VARCHAR(50),
email VARCHAR(200),
phone VARCHAR(20),
PRIMARY KEY (employee_id)
);

-- SUPPLIERS
CREATE TABLE suppliers (
supplier_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
contact_name VARCHAR(100),
phone VARCHAR(20),
email VARCHAR(200),
address VARCHAR(400),
PRIMARY KEY (supplier_id)
);

-- PRODUCTS (FRAMES & LENS BLANKS)

CREATE TABLE products (
product_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
category VARCHAR(20) NOT NULL
CHECK (category IN ('FRAME','LENS_BLANK')),
supplier_id INT NOT NULL,
cost_price DECIMAL(10,2) NOT NULL,
sale_price DECIMAL(10,2) NOT NULL,
stock_qty INT NOT NULL DEFAULT 0,
description VARCHAR(1000),
PRIMARY KEY (product_id),
FOREIGN KEY (supplier_id)
REFERENCES suppliers(supplier_id)
ON DELETE CASCADE
);

-- APPOINTMENTS
CREATE TABLE appointments (
appointment_id INT NOT NULL AUTO_INCREMENT,
customer_id INT NOT NULL,
employee_id INT NOT NULL,
appt_datetime DATETIME NOT NULL,
type VARCHAR(50),
notes VARCHAR(2000),
PRIMARY KEY (appointment_id),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE,
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON DELETE CASCADE
);

-- PRESCRIPTIONS
CREATE TABLE prescriptions (
prescription_id INT NOT NULL AUTO_INCREMENT,
customer_id INT NOT NULL,
appointment_id INT NOT NULL,
issued_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 valid_until DATE,
sphere_r DECIMAL(5,2),
cylinder_r DECIMAL(5,2),
axis_r SMALLINT,
add_r DECIMAL(4,2),
sphere_l DECIMAL(5,2),
cylinder_l DECIMAL(5,2),
axis_l SMALLINT,
add_l DECIMAL(4,2),
notes VARCHAR(2000),
PRIMARY KEY (prescription_id),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE,
FOREIGN KEY (appointment_id)
REFERENCES appointments(appointment_id)
ON DELETE CASCADE
);

-- ORDERS
CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT,
customer_id INT NOT NULL,
employee_id INT NOT NULL,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(12,2),
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE,
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
ON DELETE CASCADE
);

-- ORDER ITEMS
CREATE TABLE order_items (
order_item_id INT NOT NULL AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
prescription_id INT,
eye CHAR(1) NOT NULL
CHECK (eye IN ('L','R')),
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_item_id),
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE,
FOREIGN KEY (product_id)
 REFERENCES products(product_id)
ON DELETE CASCADE,
FOREIGN KEY (prescription_id)
REFERENCES prescriptions(prescription_id)
ON DELETE CASCADE
);

SQL Data Insertion with DML Script

-- 10 sample customers
INSERT INTO customers (customer_id, first_name, last_name, dob,
phone, email, address) VALUES
(1, 'John', 'Doe', '1980-01-15', '555-0101', '[Link]@[Link]',
'123 Elm St, Springfield, IL'),
(2, 'Jane', 'Smith', '1975-06-20', '555-0102',
'[Link]@[Link]', '456 Oak Ave, Shelbyville, IL'),
(3, 'Bob', 'Johnson', '1990-09-10', '555-0103',
'[Link]@[Link]', '789 Maple Rd, Ogdenville, IL'),
(4, 'Alice', 'Williams', '1985-12-05', '555-0104',
'[Link]@[Link]', '321 Pine St, Capital City, IL'),
(5, 'Michael', 'Brown', '1978-03-22', '555-0105',
'[Link]@[Link]', '654 Birch Blvd, North Haverbrook,
IL'),
(6, 'Linda', 'Davis', '1992-07-30', '555-0106',
'[Link]@[Link]', '987 Cedar Ln, West Springfield, IL'),
(7, 'David', 'Miller', '1983-11-17', '555-0107',
'[Link]@[Link]', '147 Spruce Dr, Brockway, IL'),
(8, 'Susan', 'Wilson', '1988-04-02', '555-0108',
'[Link]@[Link]', '258 Walnut St, Cypress Creek, IL'),
(9, 'Robert', 'Moore', '1970-08-25', '555-0109',
'[Link]@[Link]', '369 Chestnut Rd, Monorail Town, IL'),
(10,'Karen', 'Taylor', '1995-11-05', '555-0110',
'[Link]@[Link]', '1010 Poplar Rd, Waverly Hills, IL');

-- 10 sample employees
INSERT INTO employees (employee_id, first_name, last_name, role,
email, phone) VALUES
(1, 'Emily', 'Clark', 'Optometrist', '[Link]@[Link]', '555-
0201'),
(2, 'Michael','Nguyen', 'Optician', '[Link]@[Link]', '555-
0202'),
(3, 'Sarah', 'Johnson', 'Sales Rep', '[Link]@[Link]', '555-
0203'),
(4, 'David', 'Lee', 'Lab Technician','[Link]@[Link]', '555-
0204'),
(5, 'Laura', 'Kim', 'Manager', '[Link]@[Link]', '555-0205'),
(6, 'James', 'Patel', 'Optometrist', '[Link]@[Link]', '555-
0206'),
(7, 'Anna', 'Martinez', 'Optician', '[Link]@[Link]', '555-
0207'),
(8, 'William','Chen', 'Sales Rep', '[Link]@[Link]', '555-0208'),
(9, 'Olivia', 'Davis', 'Lab Technician','[Link]@[Link]', '555-
0209'),
(10, 'Robert', 'Wilson', 'Manager', '[Link]@[Link]', '555-
0210');

-- 10 sample suppliers
INSERT INTO suppliers (supplier_id, name, contact_name, phone,
email, address) VALUES
(1, 'Vision Supplies Inc', 'Alice Brown', '555-0301',
'[Link]@[Link]', '500 Oak Ave, Metropolis, NY'),
(2, 'LensMakers Co', 'Bob Smith', '555-0302',
'[Link]@[Link]', '100 Maple St, Gotham, NY'),
(3, 'FrameWorks Ltd', 'Carol White', '555-0303',
'[Link]@[Link]', '200 Pine Rd, Star City, CA'),
(4, 'OpticLabs Corp', 'Daniel Green', '555-0304',
'[Link]@[Link]', '300 Cedar Blvd, Central City, MO'),
(5, 'ClearView LLC', 'Eva Black', '555-0305',
'[Link]@[Link]', '400 Birch Lane, Coast City, OR'),
(6, 'UltraLens Inc', 'Frank Gray', '555-0306',
'[Link]@[Link]', '600 Spruce Dr, Keystone, PA'),
(7, 'Titanium Frames Co', 'Grace Lee', '555-0307',
'[Link]@[Link]', '700 Chestnut St, Smallville, KS'),
(8, 'SportOptix', 'Henry King', '555-0308',
'[Link]@[Link]', '800 Walnut Ave, Hill Valley, CA'),
(9, 'PhotoChromics Ltd', 'Irene Scott', '555-0309',
'[Link]@[Link]', '900 Ash St, Twin Peaks, WA'),
(10, 'PolarEyes', 'Jack Turner', '555-0310',
'[Link]@[Link]', '1000 Elm St, Emerald City, KS');

-- 10 sample products
INSERT INTO products (product_id, name, category, supplier_id,
cost_price, sale_price, stock_qty, description) VALUES
(1, 'Classic Round Frames', 'FRAME', 1, 15.00, 50.00, 100, 'Timeless
round glasses frame'),
(2, 'Aviator Metal Frames', 'FRAME', 2, 20.00, 60.00, 80, 'Stylish
aviator-style metal frame'),
(3, 'Plastic Rectangle Frames', 'FRAME', 3, 10.00, 40.00, 120,
'Durable plastic rectangle frame'),
(4, 'Lens Blank 1.50 Index', 'LENS_BLANK', 4, 5.00, 15.00, 200,
'Standard 1.50 index lens blank'),
(5, 'Lens Blank 1.60 Index', 'LENS_BLANK', 5, 7.00, 20.00, 150,
'Thin 1.60 index lens blank'),
(6, 'Lens Blank 1.67 Index', 'LENS_BLANK', 6, 8.00, 25.00, 100,
'Ultra-thin 1.67 index lens blank'),
(7, 'Titanium Rimless Frames', 'FRAME', 7, 30.00, 75.00, 60,
'Lightweight titanium rimless frame'),
(8, 'Sport Wrap Frames', 'FRAME', 8, 25.00, 65.00, 70, 'Wraparound
sport frame'),
(9, 'Lens Blank Photochromic', 'LENS_BLANK', 9, 10.00, 30.00, 90,
'Photochromic lens blank'),
(10, 'Lens Blank Polarized', 'LENS_BLANK', 10, 12.00, 35.00, 110,
'Polarized lens blank');

-- 10 sample appointments
INSERT INTO appointments (appointment_id, customer_id, employee_id,
appt_datetime, type, notes) VALUES
(1, 1, 1, '2025-04-01 10:00:00', 'Routine Check', 'Annual eye
exam'),
(2, 2, 2, '2025-04-02 11:30:00', 'Follow-up', 'Check adjustment
progress'),
(3, 3, 3, '2025-04-03 09:15:00', 'New Patient', 'First-time eye
exam'),
(4, 4, 4, '2025-04-04 14:00:00', 'Contact Lens Fitting', 'Trial
fitting session'),
(5, 5, 5, '2025-04-05 15:45:00', 'Routine Check', 'Bi-annual exam'),
(6, 6, 6, '2025-04-06 13:20:00', 'Follow-up', 'Review
prescription'),
(7, 7, 7, '2025-04-07 10:30:00', 'New Patient', 'Initial
consultation'),
(8, 8, 8, '2025-04-08 12:00:00', 'Routine Check', 'Vision
screening'),
(9, 9, 9, '2025-04-09 16:15:00', 'Contact Lens Fitting', 'Contacts
trial'),
(10, 10, 10,'2025-04-10 09:00:00', 'Routine Check', 'Annual check-
up');

-- 10 sample prescriptions
INSERT INTO prescriptions (
prescription_id, customer_id, appointment_id, issued_date,
valid_until,
sphere_r, cylinder_r, axis_r, add_r,
sphere_l, cylinder_l, axis_l, add_l,
notes
) VALUES
(1, 1, 1, '2025-04-01 10:30:00', '2026-04-01', -1.25, -0.75, 90,
1.00, -1.00, -0.50, 85, 1.00, 'Use for reading'),
(2, 2, 2, '2025-04-02 12:00:00', '2026-04-02', -2.00, -1.00, 80,
1.25, -1.75, -0.75, 95, 1.25, 'No special notes'),
(3, 3, 3, '2025-04-03 09:45:00', '2026-04-03', 0.50, 0.00, 0, 0.75,
0.75, 0.00, 0, 0.75, 'Hyperopia'),
(4, 4, 4, '2025-04-04 14:30:00', '2026-04-04', -3.00, -1.25,100,
1.50, -2.50, -1.00, 95, 1.50, 'Astigmatism'),
(5, 5, 5, '2025-04-05 16:15:00', '2026-04-05', -0.75, -0.50,105,
1.00, -0.50, -0.25,110, 1.00, ''),
(6, 6, 6, '2025-04-06 13:50:00', '2026-04-06', 1.00, 0.25, 10, 0.75,
1.25, 0.50, 15, 0.75, 'Presbyopia'),
(7, 7, 7, '2025-04-07 11:00:00', '2026-04-07', -4.00, -1.75,120,
2.00, -3.50, -1.50,115, 2.00, 'High myopia'),
(8, 8, 8, '2025-04-08 12:30:00', '2026-04-08', -1.00, 0.00, 0, 0.50,
-1.25, 0.00, 0, 0.50, 'Simple myopia'),
(9, 9, 9, '2025-04-09 16:45:00', '2026-04-09', -2.25, -0.75, 85,
1.25, -2.00, -0.50, 80, 1.25, 'Astigmatism with presbyopia'),
(10,10, 10,'2025-04-10 09:30:00', '2026-04-10', 0.00, 0.00, 0, 0.00,
0.00, 0.00, 0, 0.00, 'Perfect vision');

-- 10 sample orders
INSERT INTO orders (order_id, customer_id, employee_id, order_date,
total_amount) VALUES
(1, 1, 2, '2025-05-05 09:20:00', 50.00),
(2, 2, 3, '2025-05-06 10:15:00', 60.00),
(3, 3, 1, '2025-05-07 11:00:00', 40.00),
(4, 4, 2, '2025-05-08 13:30:00', 15.00),
(5, 5, 3, '2025-05-09 14:45:00', 20.00),
(6, 6, 4, '2025-05-10 16:00:00', 25.00),
(7, 7, 5, '2025-05-11 09:00:00', 75.00),
(8, 8, 6, '2025-05-12 10:30:00', 65.00),
(9, 9, 7, '2025-05-13 11:15:00', 30.00),
(10,10, 8,'2025-05-14 12:00:00', 35.00);

-- 10 sample order items

INSERT INTO order_items (order_item_id, order_id, product_id,
prescription_id, eye, quantity, unit_price) VALUES
(1, 1, 1, NULL, 'L', 1, 50.00),
(2, 2, 2, NULL, 'L', 1, 60.00),
(3, 3, 3, NULL, 'L', 1, 40.00),
(4, 4, 4, 4, 'R', 1, 15.00),
(5, 5, 5, 5, 'R', 1, 20.00),
(6, 6, 6, 6, 'R', 1, 25.00),
(7, 7, 7, NULL, 'L', 1, 75.00),
(8, 8, 8, NULL, 'L', 1, 65.00),
(9, 9, 9, 9, 'R', 1, 30.00),
(10,10, 10, 10, 'R', 1, 35.00);

Views

View Customer Orders

CREATE VIEW view_customer_orders AS

SELECT
o.order_id,
o.order_date,
c.customer_id,
CONCAT(c.first_name,' ', c.last_name) AS customer_name,
oi.order_item_id,
[Link] AS product_name,
 [Link],
oi.unit_price,
([Link] * oi.unit_price) AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

View Inventory Status

CREATE VIEW view_inventory_status AS

SELECT
p.product_id,
[Link] AS product_name,
[Link],
p.stock_qty,
p.cost_price,
p.sale_price,
s.supplier_id,
[Link] AS supplier_name,
[Link] AS supplier_phone,
[Link] AS supplier_email
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id;

View Monthly Sales Summary

CREATE VIEW view_monthly_sales_summary AS

SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS 'year_month',
COUNT(DISTINCT o.order_id) AS total_orders,
SUM([Link]) AS total_items_sold,
SUM([Link] * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m');

Triggers

Decrement stock on new order item insert

DELIMITER $$
CREATE TRIGGER trg_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
UPDATE products
SET stock_qty = stock_qty - [Link]
WHERE product_id = NEW.product_id;
END$$
DELIMITER ;

Restore stock on new order item delete

DELIMITER $$
CREATE TRIGGER trg_order_item_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
UPDATE products
SET stock_qty = stock_qty + [Link]
WHERE product_id = OLD.product_id;
END$$
DELIMITER ;

Adjust stock on order item quantity update

DELIMITER $$
CREATE TRIGGER trg_order_item_update
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
UPDATE products
SET stock_qty = stock_qty + ([Link] - [Link])
WHERE product_id = NEW.product_id;
 END$$
DELIMITER ;

5. Results and Discussion

Screenshots of Select Queries on Tables

Appointments

Customers

Employees

Order Items
Orders

Prescriptions

Products
 Suppliers

6. Conclusion

The optician’s shop database system addresses the needs of the business successfully,
allowing their data to be managed and tracked in a systematic and centralized manner.
Various aspects of the business from customer management, order logging and
inventory management were addressed. By analyzing and developing directly for
their needs we ensured they received a final system that meets their requirements and
helps them manage all domains relevant to their business from employees, products,
customers, inventory, orders and suppliers.