Bsbmkg609

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

)