Table of Contents 1 Project Overview 5 2 Project Plan 6 3 Logical Data Model 10 4 Relational Data model 11 5 DDL for tablespaces 11 6 DDL Script and objects 12 7 Calculations for sizing objects or source of sizing 23 7.1 Table 1: Warehouse 23 7.2 Table 2: Supplier 24 7.3 Table 3: Promotions 24 7.4 Table 4: Inventory 25 7.5 Table 5: Shopping Cart 26 7.6 Table 6: Customer 26 7.7 Table 7: Order 27 7.8 Table 8: Transaction 28 7.9 Table 9: Shipping 28 8 Results of query on catalog showing all objects with valid status 30 8.1 EMPLY, ADDRSS, CUST 30 8.2 INVT, ORDER, PROMO 30 8.3 TRANSCTN, WHRHSE & CUST 31 8.4 SHIPNG, SHP_CRT & SUPLR 31 9 DB Installation outputs 32 10 Installation checkout commands and sample output 34 11 Create DBA user script 34 12 Alter users script 36 13 Export command and output 37 13.1 Dbuser export file 37 13.2 Export command and output 37 13.3 Unix Screenshot of exp file 41 14 SQL Loader Control files for 10 table loads 42 14.1 Control Files 42 14.2 Log Files 42 15 Analyze commands for each table 43 15.1 Analyze Command Set 1 43 15.2 Analyze Command Set 2 44 15.3 Analyze Command Set 3 44 16 Query output showing row numbers from catalog 45 16.1 Table: TRANSCTN 45 16.2 Table: ADDRSS 45 16.3 Table: SHP_CRT 46 16.4 Table: EMPLY 46 16.5 Table: CUST 47 16.6 Table: WHRHSE 47 16.7 Table: SUPLR 48 16.8 Table: SHIPNG 48 16.9 Table: PROMO 49 16.10 Table: ORDER 49 16.11 Table: INVT 50

Project Overview
Project Title – Online Clothing Store

The objective of the project is to develop a database that can be accessed by a web application, which allows customers to purchase a variety of products sold online from different vendors from any location.

Project Plan

Figure 1: Online shopping database design and implementation project plan page 1

Figure 2: Online shopping database design and implementation project plan page 2

Figure 3: Online shopping database design and implementation project plan page 3

Figure 4: Online shopping database design and implementation project plan page 4

Logical Data Model

Figure 5: Logical data model for online shopping database

Relational Data model

Figure 6: Relational data model for online shopping database

DDL for tablespaces
CREATE TABLESPACE "USER_DATA1" LOGGING DATAFILE '/u01/app/oracle/DBST670/user_data101.dbf' SIZE 4826K RESUE AUTOEXTEND ON NEXT 512K MAXSIZE 65536k EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "USER_DATA2" LOGGING DATAFILE '/u01/app/oracle/DBST670/user_data201.dbf' SIZE 4134K RESUE AUTOEXTEND ON NEXT 512K MAXSIZE 65536k EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "USER_INDEX" LOGGING DATAFILE '/u01/app/oracle/DBST670/user_INDX01.dbf' SIZE 4210K RESUE AUTOEXTEND ON NEXT 512K MAXSIZE 65536k EXTENT MANAGEMENT LOCAL;

CREATE TEMPORARY TABLESPACE "USER_TEMP" LOGGING TEMPFILE '/u01/app/oracle/DBST670/user_temp01.dbf' SIZE 16384K RESUE AUTOEXTEND ON NEXT 512K MAXSIZE 65536k EXTENT MANAGEMENT LOCAL;

DDL Script and objects
set echo on

-----------------------------------------------------------

-- CREATE ADDRESS TABLE - Common for all entities that require address example customer, shipping, etc.

-----------------------------------------------------------

CREATE TABLE addrss (

addrid NUMBER(7) NOT NULL,

res_no VARCHAR2(255),

street VARCHAR2(300) NOT NULL,

city VARCHAR2(100) NOT NULL,

state CHAR(2),

zip CHAR(10),

addrtype CHAR(1) NOT NULL,

shipng_shpng_id NUMBER(7) NOT NULL,

shipng_order_order_id NUMBER(7) NOT NULL,

shipng_order_transctn_tran_id NUMBER(7) NOT NULL,

shipng_order_promo_prm_id VARCHAR2(20) NOT NULL,

constraint addrss_pk primary key (addrid, shipng_shpng_id, shipng_order_order_id, shipng_order_transctn_tran_id, shipng_order_promo_prm_id)

using index

storage (

initial 25k

next 12k

)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10k

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE CUSTOMER TABLE

--------------------------------

CREATE TABLE cust (

cust_id NUMBER(7) NOT NULL,

fname VARCHAR2(100) NOT NULL,

lname VARCHAR2(100) NOT NULL,

constraint cust_pk primary key (cust_id)

using index

storage (

initial 25k

next 12K

)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE EMPLOYEE TABLE

--------------------------------

CREATE TABLE emply (

empid NUMBER(7) NOT NULL,

fname VARCHAR2(100) NOT NULL,

lname VARCHAR2(100) NOT NULL,

dept VARCHAR2(100) NOT NULL,

desgn VARCHAR2(100) NOT NULL,

addrss_addrid NUMBER(7) NOT NULL,

addrss_shpng_id NUMBER(7) NOT NULL,

addrss_order_id NUMBER(7) NOT NULL,

addrss_tran_id NUMBER(7) NOT NULL,

addrss_prm_id NUMBER(7) NOT NULL,

whrhse_whr_id NUMBER(7) NOT NULL,

whrhse_invt_id NUMBER(7) NOT NULL,

whrhse_sup_id VARCHAR2(10) NOT NULL,

constraint emply_pk primary key (empid, addrss_addrid, addrss_shpng_id, addrss_order_id, addrss_tran_id, addrss_prm_id, whrhse_whr_id,whrhse_invt_id, whrhse_sup_id)

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE INVT TABLE

--------------------------------

CREATE TABLE invt (

invt_id NUMBER(7) NOT NULL,

invt_desc VARCHAR2(300) NOT NULL,

"SIZE" VARCHAR2(50) NOT NULL,

inv_date VARCHAR2(10) NOT NULL,

in_stock NUMBER(7) NOT NULL,

cost VARCHAR2(50) NOT NULL,

suplr_sup_id VARCHAR2(50) NOT NULL,

constraint invt_pk primary key (invt_id, suplr_sup_id)

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE ORDER TABLE

--------------------------------

CREATE TABLE "ORDER" (

order_id NUMBER(7) NOT NULL,

ord_desc VARCHAR2(300) NOT NULL,

transctn_tran_id NUMBER(7) NOT NULL,

promo_prm_id VARCHAR2(10) NOT NULL,

constraint order_pk primary key (order_id, transctn_tran_id, promo_prm_id )

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE PROMOTIONS TABLE

--------------------------------

CREATE TABLE promo (

prm_id NUMBER(7) NOT NULL,

prm_desc VARCHAR2(200),

prm_amt varchar2(20) NOT NULL,

constraint promo_pk primary key (prm_id)

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE SHIPPING TABLE

--------------------------------

CREATE TABLE shipng (

shpng_id NUMBER(7) NOT NULL,

shng_desc VARCHAR2(100),

shpng_status CHAR(1) NOT NULL,

order_order_id NUMBER(7) NOT NULL,

order_transctn_tran_id NUMBER(7) NOT NULL,

order_promo_prm_id VARCHAR2(10) NOT NULL,

constraint shipng_pk primary key (shpng_id, order_order_id, order_transctn_tran_id, order_promo_prm_id )

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE SHOPPING CART TABLE

--------------------------------

CREATE TABLE shp_crt (

shp_id NUMBER(7) NOT NULL,

cost NUMBER(10,2) NOT NULL,

cust_cust_id NUMBER(7) NOT NULL,

invt_invt_id NUMBER(7) NOT NULL,

invt_sup_id NUMBER(7) NOT NULL,

transctn_tran_id varchar2(10) NOT NULL,

constraint shp_crt_pk primary key (shp_id, cust_cust_id, invt_invt_id, invt_sup_id, transctn_tran_id)

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE SUPPLIER TABLE

--------------------------------

CREATE TABLE suplr (

sup_id NUMBER(7) NOT NULL,

sup_name VARCHAR2(300) NOT NULL,

addrid VARCHAR2(10),

constraint suplr_pk primary key (sup_id )

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE TRANSACTION TABLE

--------------------------------

CREATE TABLE transctn (

tran_id NUMBER(7) NOT NULL,

card_num VARCHAR2(50),

constraint transctn_pk primary key (tran_id )

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

--------------------------------

-- CREATE WAREHOUSE TABLE

--------------------------------

CREATE TABLE whrhse (

whr_id NUMBER(7) NOT NULL,

whr_nm VARCHAR2(100) NOT NULL,

invt_invt_id NUMBER(7) NOT NULL,

invt_sup_id VARCHAR2(10) NOT NULL,

constraint whrhse_pk primary key (whr_id, invt_invt_id,invt_sup_id )

using index

storage (

initial 25k

next 12K)

tablespace USER_INDEX

)

pctfree 0

pctused 90

storage (

initial 50k

next 10K

pctincrease 0

maxextents 5

)

tablespace USER_DATA1;

ALTER TABLE addrss

ADD CONSTRAINT addrss_cust_fk FOREIGN KEY ( cust_cust_id )

REFERENCES cust ( cust_id );

ALTER TABLE addrss

ADD CONSTRAINT addrss_shipng_fk FOREIGN KEY ( shipng_shpng_id,

shipng_order_order_id,

shipng_order_transctn_tran_id,

shipng_order_promo_prm_id )

REFERENCES shipng ( shpng_id,

order_order_id,

order_transctn_tran_id,

order_promo_prm_id );

ALTER TABLE addrss

ADD CONSTRAINT addrss_whrhse_fk FOREIGN KEY ( whrhse_whr_id,

whrhse_invt_invt_id,

whrhse_invt_sup_id )

REFERENCES whrhse ( whr_id,

invt_invt_id,

invt_sup_id );

ALTER TABLE emply

ADD CONSTRAINT emply_addrss_fk FOREIGN KEY ( addrss_addrid,

addrss_shpng_id,

addrss_order_id,

addrss_tran_id,

addrss_prm_id,

addrss_whr_id,

addrss_invt_id,

addrss_sup_id,

addrss_cust_cust_id )

REFERENCES addrss ( addrid,

shipng_shpng_id,

shipng_order_order_id,

shipng_order_transctn_tran_id,

shipng_order_promo_prm_id,

whrhse_whr_id,

whrhse_invt_invt_id,

whrhse_invt_sup_id,

cust_cust_id );

ALTER TABLE emply

ADD CONSTRAINT emply_whrhse_fk FOREIGN KEY ( whrhse_whr_id,

whrhse_invt_id,

whrhse_sup_id )

REFERENCES whrhse ( whr_id,

invt_invt_id,

invt_sup_id );

ALTER TABLE invt

ADD CONSTRAINT invt_suplr_fk FOREIGN KEY ( suplr_sup_id )

REFERENCES suplr ( sup_id );

ALTER TABLE "ORDER"

ADD CONSTRAINT order_promo_fk FOREIGN KEY ( promo_prm_id )

REFERENCES promo ( prm_id );

ALTER TABLE "ORDER"

ADD CONSTRAINT order_transctn_fk FOREIGN KEY ( transctn_tran_id )

REFERENCES transctn ( tran_id );

ALTER TABLE shipng

ADD CONSTRAINT shipng_order_fk FOREIGN KEY ( order_order_id,

order_transctn_tran_id,

order_promo_prm_id )

REFERENCES "ORDER" ( order_id,

transctn_tran_id,

promo_prm_id );

ALTER TABLE shp_crt

ADD CONSTRAINT shp_crt_cust_fk FOREIGN KEY ( cust_cust_id )

REFERENCES cust ( cust_id );

ALTER TABLE shp_crt

ADD CONSTRAINT shp_crt_invt_fk FOREIGN KEY ( invt_invt_id,

invt_sup_id )

REFERENCES invt ( invt_id,

suplr_sup_id );

ALTER TABLE shp_crt

ADD CONSTRAINT shp_crt_transctn_fk FOREIGN KEY ( transctn_tran_id )

REFERENCES transctn ( tran_id );

ALTER TABLE whrhse

ADD CONSTRAINT whrhse_invt_fk FOREIGN KEY ( invt_invt_id,

invt_sup_id )

REFERENCES invt ( invt_id,

suplr_sup_id );

Calculations for sizing objects or source of sizing
Table 1: Warehouse

AVG BYTES PER ROW = WHR_ID(3Bytes) + WHR_NM(40Bytes) + INVT_INVT_ID(3Bytes) + INVT_SUPID(3Bytes)= 49 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 49

R = 148.92 - .036R

1.036R = 148.92

R = 143.75

ROUND UP TO 144 per 8192-byte block

blocks = ceiling(10000 / 144) = ceiling(69.44) = 70

70 Blocks or 35840 bytes of Storage for this Table

Table 2: Supplier

AVG BYTES PER ROW = SUP_ID(3Bytes) + SUP_NAME(40Bytes) + ADDRID(3Bytes) = 46 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 46

R = 158.64 - .039R

1.039R = 158.64

R = 152.69

ROUND UP TO 153 rows per 8192-byte block

blocks = ceiling(10000 / 153) = ceiling(65.36) = 66

66 Blocks or 33792 bytes of Storage for this Table

Table 3: Promotions

AVG BYTES PER ROW = PRM_ID(3Bytes) + PRM_DESC(60Bytes) + PRM_AMT(4Bytes) = 67 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 67

R = 108.91 - .027R

1.027R = 108.91

R = 106.05

ROUND UP TO 107 rows per 8192-byte block

blocks = ceiling(10000 / 107) = ceiling(93.46) = 94

94 Blocks or 48128 bytes of Storage for this Table

Table 4: Inventory

AVG BYTES PER ROW = INVT_ID(3Bytes) + INVT_DESC(80Bytes) + SIZE(1Bytes) + INV_DATE(7bytes) + IN_STOCK(3bytes) + Cost(4bytes) + SUPLR_SUP_ID (3Bytes) = 101 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 101

R = 72.25 - .018R

1.018R = 72.25

R = 70.97

ROUND UP TO 71 rows per 8192-byte block

blocks = ceiling(10000 / 71) = ceiling(140.85) = 141

141 Blocks or 72192 bytes of Storage for this Table

Table 5: Shopping Cart

AVG BYTES PER ROW = SHIP_ID(3Bytes) + COST(4Bytes) + CUST_CUST_ID(3Bytes) + INVT_INVT_ID(3bytes) + INVT_SUP_ID(3bytes) + TRANSCTN_TRAN_ID(3bytes = 19 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 19

R = 384.06 - .053R

1.053R = 384.06

R = 364.73

ROUND UP TO 365 rows per 8192-byte block

blocks = ceiling(10000 / 365) = ceiling(27.40) = 28

28 Blocks or 14336 bytes of Storage for this Table

Table 6: Customer

AVG BYTES PER ROW = CUST_ID(3Bytes) + FNAME(40Bytes) + LNAME(3bytes) = 46 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 46

R = 158.64 - .039R

1.039R = 158.64

R = 152.69

ROUND UP TO 153 rows per 8192-byte block

blocks = ceiling(10000 / 153) = ceiling(65.36) = 66

66 Blocks or 33792 bytes of Storage for this Table

Table 7: Order

AVG BYTES PER ROW = ORDER_ID(3Bytes) + ORD_DESC(40Bytes) + TRANSCTN_TRAN_ID(3bytes) + PROMO_PRM_ID(3bytes)= 49 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 49

R = 148.93 - .037R

1.037R = 148.93

R = 143.62

ROUND UP TO 144 rows per 8192-byte block

blocks = ceiling(10000 / 144) = ceiling(69.44) = 70

70 Blocks or 35840 bytes of Storage for this Table

Table 8: Transaction

AVG BYTES PER ROW = TRAN_ID(3Bytes) + CARD_NUM(6Bytes)= 9 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 9

R = 810.8 - .2R

1.2R = 810.8

R = 675.66

ROUND UP TO 676 rows per 8192-byte block

blocks = ceiling(10000 / 676) = ceiling(14.80) = 15

15 Blocks or 7680 bytes of Storage for this Table

Table 9: Shipping

AVG BYTES PER ROW = SHPNG_ID(3Bytes) + SHNG_DESC(40Bytes) + SHPNG_STATUS(1Bytes) + ORDER_ORDER_ID(3Bytes) + ORDER_TRANSCTN_TRAN_ID(3Bytes) + ORDER_PROMO_PRM_ID(3Bytes)= 65 Bytes

NUM ROWS PER BLOCK

R = (7297.2 - 1.8*R) / 65

R = 112.26 - .028R

1.028R = 112.26

R = 109.20

ROUND UP TO 110 rows per 8192-byte block

blocks = ceiling(10000 / 110) = ceiling(90.90) = 91

91 Blocks or 46592 bytes of Storage for this Table

Results of query on catalog showing all objects with valid status
EMPLY, ADDRSS, CUST

INVT, ORDER, PROMO

TRANSCTN, WHRHSE & CUST

SHIPNG, SHP_CRT & SUPLR

DB Installation outputs

Figure 18: Database installed

Figure 19: Putty output

Installation checkout commands and sample output

Create DBA user script
CREATE USER dbst670a identified by mypassword default tablespace user temporary tablespace temp;

GRANT connect, resource TO dbst670a;

CREATE USER dbst670b identified by mypassword default tablespace user temporary tablespace temp;

GRANT connect, resource TO dbst670b;

CREATE USER dbst670c identified by mypassword default tablespace user temporary tablespace temp;

GRANT connect, resource TO dbst670c;

CREATE USER dbauser identified by mypassword default tablespace user temporary tablespace temp;

GRANT CONNECT, RESOURCE TO DBAUSER;

GRANT DBA TO dbauser;

Figure 22: Create User dbst670b and dbst670c

Figure 23: Created users

Alter users script
alter user dbst670a default tablespace user_data1 temporary tablespace user_temp;

alter user dbst670b default tablespace user_data1 temporary tablespace user_temp;

alter user dbst670c default tablespace user_data1 temporary tablespace user_temp;

Figure 24: ALTER USER Script

Export command and output
Dbuser export file
Export command and output
[[email protected] ~]$ exp owner=dbauser file=dbauser.exp

Export: Release 12.2.0.1.0 - Production on Mon Jul 8 21:44:19 2019

Username: dbauser

Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user DBAUSER

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user DBAUSER

About to export DBAUSER's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export DBAUSER's tables via Conventional Path ...

. . exporting table ADDRSS 10000 rows exported