University of Cebu
College of Computer Studies
DBSys32 - Database Systems 2
1st Semester, S.Y. 2023-2024
BOOKHUB (ONLINE BOOK STORE SYSTEM)
Submitted by:
Aliganga, Jeff Kirk
Alisaca, Carl Timothy
Bengcolita, Cliff Bryann
Boyles, Alexandra Concepcion
Montecillo, Jian Gabriel
Submitted to:
Ms. Beverly Lahaylahay
TABLE OF CONTENTS
Business Rules………………………………………………………………………………1
Entity Relationship Diagram………………………………………………………………..2
Data Dictionary………………………………………………………………………………3
Wireframe…………………………………………………………………………………….4
Class/Database Diagram…………………………………………………………………...5
CRUD Matrix…………………………………………………………………………………6
Database Design…………………………………………………………………………….7
BUSINESS RULES
Users
-Users must be registered in order to place an order.
-Each user must have a unique username and email address.
-Users must provide a valid contact number and address.
-Users must agree to the no refund no exchange policy and cancelling orders policy
before placing an order.
Books
-Books must be listed in the database before they can be ordered.
-Each book must have a unique title and ISBN number.
-Books must have a valid price.
-Out-of-stock books can be displayed but cannot be ordered.
Orders
-Orders must be placed by a registered user.
-Orders must have a valid order status.
-The total amount of an order must be greater than 0.
-All sales are final. There are no refunds or exchanges.
-Orders that have been placed and shipped cannot be cancelled.
Order items
-Order items must be associated with a valid order.
-Order items must be associated with a valid book.
-The quantity of an order item must be greater than 0.
-The price of an order item must be greater than 0.
Payments
-Payments must be associated with a valid order.
-Payments must have a valid payment date.
-The amount paid must be greater than 0.
-The amount paid must be equal to or greater than the total amount of the order.
ENTITY RELATIONSHIP DIAGRAM
DATA DICTIONARY
WIREFRAME
Login Page
Registration Page
Home Page
Shop Page
Account Settings Page
Cart Page
Checkout Order Page
Order Successful Page
CLASS / DATABASE DIAGRAM
CRUD MATRIX
Crud Matrix
TABLES
Calling Item Item Type USERS BOOKS ORDERS ORDER PAYMENTS
_ITEMS
sp_populateUSERS Procedure CRU
USER_DELETE Procedure RD
sp_populateBOOKS Procedure CRU
BOOKS_DELETE Procedure RD
sp_populateORDERS Procedure R CRU
ORDERS_DELETE Procedure D RD
sp_populateORDERITEMS Procedure R R CRU
ORDERITEMS_DELETE Procedure D D RD
sp_populatePAYMENTS Procedure R CRU
PAYMENTS_DELETE Procedure D RD
SEND_ORDER_NOTIFICATION Trigger R R
CHECK_ORDER_AMOUNT Trigger R CR
UPDATE_BOOK_INVENTORY Trigger R R U
UPDATE_ORDER_TOTAL Trigger R U R
DATABASE DESIGN
CREATE DATABASE ONLINE_BOOKSTORE
USE ONLINE_BOOKSTORE
GO
CREATE TABLE USERS
[USER_ID] INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, USERNAME VARCHAR(50) NOT NULL
, USER_EMAIL VARCHAR(50) NOT NULL
, USER_PASSWORD VARCHAR(50) NOT NULL
, CONTACT_NUMBER VARCHAR(11) NOT NULL
, ADDRESSES VARCHAR(100) NOT NULL
CREATE INDEX IDX_USERS
ON USERS (USERNAME)
CREATE TABLE BOOKS
BOOK_ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, TITLE VARCHAR(50) NOT NULL
, AUTHOR VARCHAR(50) NOT NULL
, PRICE DECIMAL(10, 2) NOT NULL
, ISBN VARCHAR(50) NOT NULL
, PUBLISHER VARCHAR(50) NOT NULL
, PUBLICATION_DATE DATE NOT NULL
, BOOK_LANGUAGE VARCHAR(50) NOT NULL
, BOOK_GENRE VARCHAR(50) NOT NULL
CREATE INDEX IDX_BOOKS
ON BOOKS (TITLE)
CREATE TABLE ORDERS
ORDER_ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, [USER_ID] INT REFERENCES USERS NOT NULL
, ORDERS_DATE DATETIME NOT NULL
, TOTAL_AMOUNT DECIMAL(10, 2) NOT NULL
, ORDER_STATUS VARCHAR(50) NOT NULL
CREATE INDEX IDX_ORDERS
ON ORDERS (ORDER_STATUS)
CREATE TABLE ORDER_ITEMS
ORDER_ITEMS_ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, ORDER_ID INT REFERENCES ORDERS NOT NULL
, BOOK_ID INT REFERENCES BOOKS NOT NULL
, QUANTITY SMALLINT NOT NULL
, PRICE MONEY NOT NULL
, SUBTOTAL AS ISNULL((QUANTITY * PRICE),0.00)
CREATE INDEX IDX_ORDER_ITEMS
ON ORDER_ITEMS (ORDER_ID)
CREATE TABLE PAYMENTS
PAYMENT_ID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
, ORDER_ID INT REFERENCES ORDERS NOT NULL
, PAYMENT_DATE DATE NOT NULL
, AMOUNT_PAID MONEY DEFAULT 0.00 NOT NULL
, PAYMENT_METHOD VARCHAR(50) NOT NULL
CREATE INDEX IDX_PAYMENTS
ON PAYMENTS (PAYMENT_METHOD)
