BussinessRulesForTimber

Programmers: Jerome Corpuz, Zeus Estrella, Sergio Valencia, Emmett Sparrow

Purpose:
Architecting an Optimized Oracle Database Solution and Generating Reports for an Ecommerce Platform bussiness requirements

Conceputal Model

1. Identifying relationships between entities

Physical Model

1. Breaking all many-to-many relationships into normalized one-to-many relationships by adding bridging table for improved data integrity and efficiency.

2. Determining Primary, Foreign Keys, attribues and attriute values, and constraints

Reports

Report 1: Customer report based on a specified city name

The following steps are performed in the code:

COLUMN ADDRESS FORMAT A30;
COLUMN CITY FORMAT A20;

accept cityInput char prompt 'Enter a City Name: ';

select customer_id, address, city, province, postal_code, phone_number, email_address
from brt_customer
WHERE city = '&input';

Code Analysis:

A SQL query is executed to retrieve customer information from brt_customer table based on the user's input regarding the city where customers reside

Report 2: Calculateing the average, minimum, and maximum prices for each category

The following output are performed in the code:

SET LINESIZE 100
SET PAGESIZE 50

COLUMN "category_name" FORMAT A30
COLUMN "Average Price" FORMAT $9, 999.99
COLUMN "Min Price" FORMAT $9, 999.99
COLUMN "Max Price" FORMAT $9, 999.99

SELECT
    C.CATEGORY_NAME,
    AVG(P.PRICE) AS "Average Price",
    MIN(P.PRICE) AS "Min Price",
    MAX(P.PRICE) AS "Max Price"
FROM
    BRT_CATEGORY C
    JOIN BRT_PRODUCT P
    ON C.CATEGORY_ID = P.CATEGORY_ID
GROUP BY
    C.CATEGORY_NAME
ORDER BY
    "Average Price";

Code Analysis:

1. The display format settings are configured using SET commands, such as LINESIZE, PAGESIZE, and COLUMN formats.

2. A SQL query is executed to retrieve the required attributes category name from BRT_Category table and its respective prices from the brt_product table.

3. SQL aggregate functions AVG, MIN, MAX are used to find the average, minimum, and maximum prices for each category by joining the BRT_CATEGORY and BRT_PRODUCT tables and grouping the results by category.