Object-Relational and Relational Database Design: OGL & SH

This assignment presents the design and implementation of two database systems: Oil and Grease Limited (OGL) and Sales History (SH). Part 1 focuses on OGL, demonstrating both object-relational and relational database versions. It includes the creation of object types (INDUSTRIALUNIT, PRODUCT, EMPLOYEE, WORKORDER, LABOURUSAGE) and corresponding tables, along with SQL queries to retrieve data from these tables. Part 2 addresses the Sales History (SH) data warehouse, also showcasing object types and relational tables (TIMES, COSTS, SALES, CHANNELS, PRODUCTS, CUSTOMERS, COUNTRIES) designed to store sales-related data. The assignment highlights the creation of database schemas, including primary and foreign key constraints, as well as PL/SQL procedures to query the databases. The OGL section also mentions new features such as XML replication and support for large VARCHAR data types. This comprehensive approach allows for a comparison between object-oriented and relational database designs, emphasizing the importance of efficient data management and retrieval.

PART 1: OIL AND GREASE LIMITED (OGL) DATABASE SYSTEM

OBJECT-RELATIONAL VERSION OF THE OGL DATABASE
CREATE OR REPLACE TYPE INDUSTRIALUNIT AS OBJECT
(
UNITID NUMBER(4),
LOCATION VARCHAR2(50),
COUNTRY VARCHAR2(50)
);
CREATE OR REPLACE TYPE PRODUCT AS OBJECT
(
PRODID NUMBER(4),
DESCP NUMBER(4),
STDCOSTPERUNIT NUMBER(12,2),
STDLABCOSTPERUNIT NUMBER(12,2)
);
CREATE OR REPLACE TYPE EMPLOYEE AS OBJECT
(EMPID NUMBER(4),
EMPNAME VARCHAR2(100),
YTDEARNINGS VARCHAR2(12),
YTDTAX VARCHAR2(4),
YTDNICONT VARCHAR2(4),
UNITID NUMBER(4),
TAXCODE NUMBER(4)
);
CREATE OR REPLACE TYPE WORKORDER AS OBJECT
(WORKORDERID NUMBER(4),
QTY NUMBER(4),
UNITPRICE NUMBER(12,2),
ORDERDATE DATE,
PROMISEDATE DATE,
COMPDATE DATE,
SHIPDATE DATE,
STATUS VARCHAR2(4),
PRODID NUMBER(4)
);

CREATE OR REPLACE TYPE LABOURUSAGE AS OBJECT
(EMPID NUMBER(4),
WORKORDERID NUMBER(4),
WORKDATE DATE,
HOURS NUMBER(2),
HOURLYRATE NUMBER(4),
POSTED CHAR(1)
);
CREATE TABLE INDUNITPRODUCT
(UNITID INDUSTRIALUNIT,
PRODID PRODUCT
);
INSERT INTO INDUNITPRODUCT VALUES (
INDUSTRIALUNIT (1, 'NY', 'USA'),PRODUCT ((1, 'IPHONE', '$5000',
'$4000'));
INSERT INTO INDUNITPRODUCT VALUES (
INDUSTRIALUNIT (2, 'NY', 'USA'),PRODUCT ((2, 'TV', '$6000',
'$4000'));
INSERT INTO INDUNITPRODUCT VALUES (
INDUSTRIALUNIT (3, 'NY', 'USA'),PRODUCT ((3, 'CELL', '$3000',
'$2000'));
INSERT INTO INDUNITPRODUCT VALUES (
INDUSTRIALUNIT (4, 'NY', 'USA'),PRODUCT ((4, 'DISH', '$1000',
'$800'));
INSERT INTO INDUNITPRODUCT VALUES (
INDUSTRIALUNIT (5, 'NY', 'USA'),PRODUCT ((5, 'FAN', '$500',
'$400'));
CREATE INDEX I_UNITID

ON INDUSTRIALUNIT (UNITID);
CREATE INDEX I_PRODID
ON PRODUCT (PRODID);
CREATE INDEX I_EMPID
ON EMPLOYEE(EMPID);
CREATE INDEX I_WORKORDERID
ON WORKORDER(WORKORDERID);

PL/SQL PROCEDURES TO QUERY
(A)
SELECT W.WORKORDERID, W.QTY, W.UNITPRICE, W.ORDERDATE,
W.PROMISEDATE, W.COMPDATE, W.SHIPDATE, W.STATUS, L.WORKDATE,
L.HOURS, L.HOURLYRATE, L.POSTED
FROM WORKORDER W, LABOURUSAGE L

WHERE W. WORKORDERID = L. WORKORDERID;
(B)
SELECT P.PRODID, P.DESC, P.STDCOSTPERUNIT, P.STDLABCOSTPERUNIT,
I.UNITID, I.LOCATION, E. EMPID, E.EMPNAME, E.YTDEARNINGS,
E.YTDTAX, E.YTDNICONT, E.UNITID, E.TAXCODE
FROM PRODUCT P, INDUSTRIALUNIT I, EMPLOYEE E, INDUNITPRODUCT D
WHERE P.PRODID = D.PRODID
AND I.UNITID = D..UNITID
AND I.UNITID = E. I.UNITID;
RELATIONAL VERSION OF THE OGL DATABASE
CREATE TABLE INDUSTRIALUNIT
(
UNITID NUMBER(4) PRIMARY KEY,
LOCATION VARCHAR2(50),
COUNTRY VARCHAR2(50)
);
CREATE TABLE PRODUCT
(
PRODID NUMBER(4) PRIMARY KEY,
DESC NUMBER(4),
STDCOSTPERUNIT NUMBER(12,2),
STDLABCOSTPERUNIT NUMBER(12,2)
);
CREATE TABLE INDUNITPRODUCT
(UNITID NUMBER(4),
PRODID NUMBER(4)
);
CREATE TABLE EMPLOYEE

(EMPID NUMBER(4) PRIMARY KEY,
EMPNAME VARCHAR2(100),
YTDEARNINGS VARCHAR2(12,2),
YTDTAX VARCHAR2(4),
YTDNICONT VARCHAR2(4),
UNITID NUMBER(4),
TAXCODE NUMBER(4)
);
CREATE TABLE WORKORDER
(WORKORDERID NUMBER(4) PRIMARY KEY,
QTY NUMBER(4),
UNITPRICE NUMBER(12,2),
ORDERDATE DATE,
PROMISEDATE DATE,
COMPDATE DATE,
SHIPDATE DATE,
STATUS VARCHAR2(4),
PRODID NUMBER(4)
);
CREATE TABLE LABOURUSAGE
(EMPID NUMBER(4),
WORKORDERID NUMBER(4),
WORKDATE DATE,
HOURS NUMBER(2),
HOURLYRATE NUMBER(4),
POSTED CHAR(1)
);
ALTER TABLE INDUNITPRODUCT
ADD CONSTRAINT FK_UNITID
FOREIGN KEY (UNITID) REFERENCES INDUSTRIALUNIT(UNITID);