DB Develop 2 Oracle 12c, DB2, MYSQL, SQL Anywhere 16

0
Database Development2
Oracle 12c,DB2, MySQL & SQL
Anywhere
By
Sunny U Okoro

1
Contents
Database Systems.........................................................................................................................................7
Applications...................................................................................................................................................9
ERD..............................................................................................................................................................14
Oracle 12C R1..............................................................................................................................................17
TableSpace and User Accounts...............................................................................................................19
Tables......................................................................................................................................................22
Index........................................................................................................................................................36
Triggers....................................................................................................................................................38
Database Altration..................................................................................................................................79
DBA Database Dictionary......................................................................................................................105
Database Physical Model..................................................................................................................106
Database Directory ...........................................................................................................................107
Database Population.............................................................................................................................152
Modifying the Table Column.................................................................................................................166
Database Verifcation- Regular Tables...................................................................................................178
Database Verifcation- Audit Tables ......................................................................................................184
Data Population and Mainpulation.......................................................................................................192
User Account Creations and Privileges ................................................................................................211
DBA Works ............................................................................................................................................286
MYSQL 5.6.................................................................................................................................................294
Database Creation.................................................................................................................................295
Tables....................................................................................................................................................297
Indexes..................................................................................................................................................309
Triggers..................................................................................................................................................311
Database Altration................................................................................................................................316
Data Population ....................................................................................................................................320
Database Dictionary..............................................................................................................................328
Database Physical Diagram..............................................................................................................329
Database Dictinoary..........................................................................................................................330
Data Verification Regular Tables...........................................................................................................477
Data Verification Audit Tables ..............................................................................................................481

2
Data Mainpulation................................................................................................................................491
Revised Database Dictinoary ................................................................................................................563
Revised Database Diagram ...................................................................................................................565
DB2 10.......................................................................................................................................................567
Database Creation.................................................................................................................................569
Database Schema and Objecs Creations...............................................................................................579
Triggers..................................................................................................................................................594
Index......................................................................................................................................................601
Database Alteration..............................................................................................................................602
DBA Database Dictionary......................................................................................................................621
Database Physical Model..................................................................................................................623
Database Dictionary..........................................................................................................................624
Database Verification-Regular Tables...................................................................................................685
Database Verification-Audit Tables ......................................................................................................691
Oracle PLSQL-DB2 .................................................................................................................................738
Revised Database Directinoary.............................................................................................................753
Revised Physical Database Diagram .....................................................................................................760
Sybase SQLAnywhere 16...........................................................................................................................762
Database Creation.....................................................................................................................................763
Tables....................................................................................................................................................770
Index......................................................................................................................................................778
Triggers..................................................................................................................................................779
Database Altration....................................................................................................................................800
DBA Database Dictionary..........................................................................................................................802
Physical Database Diagram...................................................................................................................803
Database Dictinoary..............................................................................................................................804
Data Verification-Regular Tables ..........................................................................................................859
User Accounts Creation ........................................................................................................................871
Table Population and Data Mainpulation...........................................................................................887

3
Materialized View .................................................................................................................................914
Revised Physical Database Dictinoary.......................................................................................................944
Revised Database Physical Diagram .....................................................................................................945
Remote Servers.....................................................................................................................................947

7
Database Systems
Oracle 12c
DB2
MySQL

9
Applications
Microsoft Visio
Microsoft Visual Studios 2010

10
Sybase Central
Quest Toad Data Point

11
Oracle SQL Developer
Oracle JDeveloper

12
IBM DataStudio
MySQL Workbench
Quest TOAD forMYSQL

13
Oracle SQL Developer Data Modeler

14
ERD
Store
PK Store_ID
PK,FK1 Employee_ID
PK,FK2 Store_State_ID
PK,FK1 Store_ManagerID
Store_Name
Store_Type
Store_Address
Store_City
Store_Phone
Store_Status
Management
PK Manager_ID
PK,FK1 Employee_ID
Hire_Date
Hire_EndDate
StateCodes
PK State_ID
PK,FK1 Country_Code
State_Name
State_Abbrv
State_TaxRate
CountryCode
PK Country_Code
Country_Name
Country_Abbrv
Employee
PK Employee_ID
PK,FK1 State_ID
First_Name
Middle_Name
Last_Name
Adress
City
Phone
Gender
HRRecords
PK HR_ID
PK,FK1,FK2 Employee_ID
PK,FK2 Store_ID
PK,FK3 Role_ID
DOB
SSN
Hire_Date
End_Date
Pay_Rate
Education
Ethnicity
Race
Customer
PK Customer_ID
PK,FK1 State_ID
First_Name
Middle_Name
Last_Name
Address
City
Phone
DOB
Gender
Education
Job_Type
Income_Level
HRRoles
PK Role_ID
Role_Title
Role_Status
Product
PK Product_ID
PK,FK1 Product_CategoryID
Product_Name
Product_Color
Product_Size
Poduct_Weighit
Product_Description
ProductCategory
PK Product_CategoryID
Category_Name
Category_Description
Receipt
PK Receipt_ID
PK,FK1 State_ID
PK,FK1,FK3 Employee_ID
PK,FK2 Customer_ID
PK,FK3 Store_ID
PK,FK4 Receipt_StateCode
PK,FK5 Product_ID
Receipt_Amount
Receipt_Tax
Receipt_Date
Receipt_FinalAmt
QTY
FK6 Transaction_ID
Transaction
PK,FK1 Payment_ID
PK Transaction_ID
Transaction_Date
Transaction_Status
Transaction_Amount
Transaction_Tax
Transaction_FinalAmt
Shipment
PK Shippingg_ID
PK,FK1 Shipper_ID
PK,FK2 Receipt_ID
Ship_Date
Ship_EndDate
Receive_Date
Name
Address
City
FK2 State_ID
Shippment_Amount
Shiippment_Type
FK2 Product_ID
ShippingCo
PK Shipper_ID
Shipper_Name
Status
Created_Date
PaymentCop
PK Payment_ID
Payment_Cop_Name
Payment_Type
Payment_Created_Date
Payment_Status
Howard Retails Conceptual
Diagram

16
Customer
PK Customer_ID
PK,FK1 Country_Code
PK,FK1 State_ID
First_Name
Middle_Name
Last_Name
Address
City
Phone
DOB
Employee
PK Employee_ID
PK,FK1 Country_Code
PK,FK1 State_ID
First_Name
Middle_Name
Last_Name
Address
City
Phone
Statecodes
PK State_ID
PK,FK1 Country_Code
State_Name
State_Abbrv
State_TaxRate
CountryCode
PK Country_Code
Country_Name
Country_Abbrv
HRRecords
PK,FK4 Employee_ID
PK HR_ID
PK,FK2 Role_ID
PK,FK3,FK4 Store_ID
PK,FK4 Manager_ID
DOB
Social_SecurityNum
Hire_Date
End_Date
PayRate
Employement_Status
HRROLES
PK Role_iD
Role_Title
Role_Status
Store
PK Store_ID
Store_StateID
Store_Name
Store_Type
Store_Address
Store_City
Store_City
Store_Phone
Store_Status
Management
PK Manager_ID
PK,FK1 Store_ID
PK,FK2 Employee_ID
Hire_Date
Hire_Enddate
Receipt
PK Receipt_ID
PK,FK1 Product_ID
PK,FK2 Employee_ID
PK,FK3 Customer_ID
PK,FK4 Store_ID
Receipt_Amount
Receipt_Tax
QTY
Receipt_FinalAmt
Receipt_Date
Product
PK Product_ID
PK,FK1 Product_CategoryID
Product_Name
Product_Color
Product_Size
Product_Weight
Product_Description
ProductCategory
PK Product_CategoryID
Category_Name
Category_Description
ShippingCo
PK Shipper_ID
Shipper_Name
Status
Created_Date
Shipment
PK Ship_Date
PK,FK1 Receipt_ID
PK,FK2 Shipper_ID
PK,FK3 Store_ID
PK,FK4 Employee_ID
PK,FK5 State_ID
Shipping_ID
Ship_EndDate
Receive_Date
Name
Address
City
Shipping_Amount
Shipping_Type
Ship_QTY
PaymentCop
PK Payment_ID
PK,FK1 Receipt_ID
PK,FK2 Store_ID
Payment_CopName
Payment_Type
Payment_CreatedDate
Payment_Status
PaymentCard_Number
Payment_ExpDate
Authorized_Amount
Howard Retail Logical Database Diagram

19
Note This development part for oracle has not been completed due to database failure but will be
updated later in future relases.
TableSpace and User Accounts
SQL> show user;
USER is "SYSTEM"
SQL> create tablespace HowardRetail
2 datafile'C:Oracle12coradataUwaRetail' size 1G Online;
Tablespace created.
SQL> create temporary tablespace HowardRetail_Temp
2 tempfile 'C:Oracle12coradataUwaRetail_Temp' SIZE 80M autoextend on;
Tablespace created.
SQL>
SQL> create user HowardDBA
2 Identified by howard
3 default tablespace HowardRetail;
User created.
SQL>
SQL> Alter user HowardDBA
2 temporary tablespace HowardRetail_Temp;
User altered.
SQL>
SQL> grant create session to HowardDBA;
Grant succeeded.
SQL> Grant resource to HowardDBA;
Grant succeeded.

20
Applications User Account Creations
Oracle SQLDevelopern User Account Creation
Oracle JDeveloper User Account Creation

21
Microsoft Visual Studio User Account Creation

22
Tables
SQL> Alter table countrycode
2 modify Country_Name constraint country_coname_nn not null;
Table altered.
SQL> Alter table countrycode
2 modify Country_Abbrv constraint country_coabbrv_nn not null;
Table altered.
SQL>
SQL> create table CountryCodesAudit
2 (Country_Code Integer,
3 Country_Name Varchar2(60),
4 Country_Abbrv Varchar2(12),
5 Audit_Date timestamp,
6 Audit_User varchar2(50),
7 old_countrycode integer,
8 old_countryname Varchar2(60),
9 old_countryabbrv Varchar2(12),
10 Audit_ID integer GENERATED ALWAYS AS IDENTITY
11 (START WITH 110 INCREMENT BY
1),
12 Action_Type char(20)
13 );
Table created.
SQL>
SQL>
SQL> Create table statecodes
2 (State_ID integer GENERATED ALWAYS AS IDENTITY
15) ,
4 Country_Code integer,
5 State_Name varchar2(45) ,

23
6 State_Abbrv varchar2(10) ,
7 State_TaxRate integer ,
8 constraint statecodes_statecode_pk primary key(State_ID,
Country_Code),
9 constraint statecodes_statecode_uk unique (State_ID),
10 constraint Statecode_countrycode_fk foreign key (Country_Code)
references
11 CountryCode(Country_Code)
12 );
Table created.
SQL> Alter table statecodes
2 modify State_Name constraint statecode_statename_nn not null
3 modify State_Abbrv constraint statecode_stateabrv_nn not null
4 modify State_TaxRate constraint statecode_statetxrt_nn not null;
Table altered.
SQL> Create table StateCodesAudit
2 (State_ID integer,
4 State_Name Varchar2(45),
5 State_Abbrv Varchar2(10),
6 State_TaxRate integer,
1),
11 Action_Type char(20),
12 Old_State_ID integer,
13 Old_Country_Code integer,
14 Old_State_Name Varchar2(45),
15 Old_State_Abbrv Varchar2(10),
16 Old_State_TaxRate integer );
Table created.
SQL>
SQL>
SQL>
SQL> create table Employee
2 (Employee_ID integer GENERATED ALWAYS AS IDENTITY
3 (START WITH 0443 INCREMENT
BY 25) ,
4 First_Name varchar2(55) ,
5 Middle_Name varchar2(55),
6 Last_Name varchar2(55) ,
7 Address varchar2(55) ,
8 City varchar2(45) ,
9 State_ID Integer ,
10 Phone varchar2(45) ,
11 constraint employee_empid_pk primary key(Employee_ID,State_ID),
12 constraint employee_empid_uk unique (Employee_ID),
13 constraint employee_stateid_fk foreign key(state_id) references
statecodes(state_id)

24
14 );
Table created.
SQL> show user
USER is "HOWARDDBA"
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> Alter table Employee
2 modify First_Name constraint employee_firstname_nn not null
3 modify Last_Name constraint employee_lastname_nn not null
4 modify Address constraint employee_address_nn not null
5 modify city constraint employee_city_nn not null
6 modify phone constraint employee_phone_nn not null;
Table altered.
SQL>
SQL> create table EmployeeAudit
2 (Employee_ID integer,
3 First_Name varchar2(55),
5 Last_Name Varchar2(55),
6 Address varchar2(55),
7 City Varchar2(45),
8 State_ID Integer,
9 Phone Varchar2(45),
1) ,
15 Old_Employee_ID integer,
16 Old_First_Name varchar2(55),
17 Old_Middle_Name varchar2(55),
18 Old_Last_Name Varchar2(55),
19 Old_Address varchar2(55),
20 Old_City Varchar2(45),
21 Old_State_ID Integer,
22 Old_Phone Varchar2(45)
23 );
Table created.
SQL> create table HRRoles
2 (Role_ID integer GENERATED ALWAYS AS IDENTITY
BY 25) ,
4 Role_Title varchar2(45),
5 Role_status varchar2(45),
6 constraint HRRoles_roleid_pk primary key(Role_ID)
7 );

25
Table created.
SQL> Alter table HRRoles
2 modify Role_Title constraint Hrroles_RoleTitle_nn not null
3 modify Role_status constraint Hrroles_Rolestatus_nn not null;
Table altered.
SQL>
SQL> create table HRRolesAudit
2 (Role_ID integer,
3 Role_Title varchar2(45),
4 Role_status varchar2(45),
5 Old_RoleID integer,
6 Old_RoleTitle varchar2(45),
7 Old_Role_status varchar2(45),
1) ,
13 );
Table created.
SQL>
SQL> Create table HRRecords
2 (Employee_ID Integer ,
3 HR_ID Integer GENERATED ALWAYS AS IDENTITY (START WITH 0323
INCREMENT BY 20) ,
4 Role_ID Integer ,
5 Store_ID integer ,
6 DOB Date not null,
7 Social_SecurityNum Integer ,
8 Hire_Date Date ,
9 End_Date Date,
10 PayRate numeric(15,2) ,
11 Employement_Status varchar2(45),
12 constraint HrRecord_hrid_pk primary
key(Employee_ID,HR_ID,Role_ID,Store_ID),
13 constraint HrRecord_hri_uk unique (HR_ID),
14 constraint HrRecord_empid_fk foreign key(Employee_ID) references
Employee(Employee_ID) ,
15 constraint HrRecord_roleid_fk foreign key(Role_ID) references
HRRoles(Role_ID)
16 );
Table created.
SQL>
SQL>
SQL> Create table HRRecordsAudit
2 (Employee_ID Integer,
3 HR_ID Integer,
4 Role_ID Integer,
5 Store_ID integer,

26
6 DOB Date,
7 Social_SecurityNum Integer,
8 Hire_Date Date,
9 End_Date Date,
10 PayRate numeric(15,2),
12 Old_EmployeeID Integer,
13 Old_HRID Integer,
14 Old_RoleID Integer,
15 Old_StoreID integer,
16 Old_DOB Date,
17 Old_SocialSecurityNum Integer,
18 Old_HireDate Date,
19 Old_EndDate Date,
20 Old_PayRate numeric(15,2),
21 Old_EmployementStatus varchar2(45),
1) ,
27 );
Table created.
SQL> create table Management
2 (Manager_ID integer GENERATED ALWAYS AS IDENTITY
BY 21) ,
4 Employee_ID integer ,
5 Hire_Date Date ,
6 Hire_EndDate Date,
7 constraint Management_Magerid_pk primary
key(Manager_ID,Employee_ID),
8 constraint Management_Magerid_uk unique(Manager_ID),
9 constraint Management_empid_fk foreign key(Employee_ID) references
Employee(Employee_ID)
10 );
Table created.
SQL>
SQL> Alter table Management
2 modify Hire_Date constraint HireDate_nn not null;
Table altered.
SQL>
SQL> create table ManagementAudit
2 (Manager_ID integer,
3 Employee_ID integer,
4 Hire_Date Date,
6 Old_ManagerID integer,
7 Old_EmployeeID integer,

27
9 Old_HireEndDate Date,
1) ,
15 );
Table created.
SQL>
SQL>
SQL> create table store
2 (Store_ID Integer GENERATED ALWAYS AS IDENTITY
22) ,
4 Store_StateID Integer,
5 Store_ManagerID Integer ,
6 Store_Name varchar2(65) ,
7 Store_Type varchar2(34) ,
8 Store_Address varchar2(65) ,
9 Store_City varchar2(45) ,
10 Store_Phone varchar2(45),
11 Store_Status varchar2(30) ,
12 constraint store_storeid_pk primary
key(Store_ID,Store_StateID,Store_ManagerID),
13 constraint store_storeiid_uk unique(Store_ID),
14 constraint store_stateid_fk foreign key(Store_StateID) references
statecodes
15 (State_ID),
16 constraint store_storeMagerID_FK Foreign key (Store_ManagerID)
references Management(Manager_ID)
17 );
Table created.
SQL>
SQL> Alter table HRRecords
2 add constraint HrRecord_storeid_fk foreign key(Store_ID) references
store(store_id);
Table altered.
SQL>
SQL> Alter table store
2 modify Store_Name constraint store_storename_nn not null
3 modify Store_Type constraint store_storetype_nn not null
4 modify store_address constraint store_storeadd_nn not null
5 modify store_city constraint store_storecity_nn not null
6 modify store_phone constraint store_storephone_nn not null
7 modify store_status constraint store_storestatus_nn not null;
Table altered.
SQL>
SQL> create table storeAudit

28
2 (Store_ID Integer,
3 Store_StateID Integer,
4 Store_ManagerID Integer,
5 Store_Name Varchar2(65),
6 Store_Type Varchar2(34),
7 Store_Address Varchar2(65),
8 Store_City Varchar2(45),
9 Store_Phone Varchar2(45),
10 Store_Status Varchar2(30),
11 Old_StoreID Integer,
12 Old_StorStateID Integer,
13 Old_StorManagerID Integer,
14 Old_StorName Varchar2(65),
15 Old_StorType Varchar2(34),
16 Old_StorAddress Varchar2(65),
17 Old_StorCity Varchar2(45),
18 old_StorPhone Varchar2(45),
19 old_StorStatus Varchar2(30),
1) ,
25 );
Table created.
SQL>
SQL> create table customer
2 (Customer_ID integer GENERATED ALWAYS AS IDENTITY
3 (START WITH 0643, INCREMENT
BY 15) ,
4 State_ID integer ,
7 Last_Name varchar2(50),
11 DOB Date ,
12 constraint customer_Customerid_pk primary key(customer_id,state_id),
13 constraint customer_customerid_uk unique (customer_id),
14 constraint customer_custstateid_fk foreign key(State_ID) references
statecodes(State_ID)
15 );
(START WITH 0643, INCREMENT BY
15) ,
*
ERROR at line 3:
ORA-02000: missing ) keyword
SQL>
SQL>
SQL>
SQL>

29
SQL> create table customer
2 (Customer_ID integer GENERATED ALWAYS AS IDENTITY
BY 15) ,
7 Last_Name varchar2(50),
11 DOB Date ,
statecodes(State_ID)
15 );
Table created.
SQL>
SQL> Alter table customer
2 modify First_Name constraint customer_firstname_nn not null
3 modify last_Name constraint customer_lastname_nn not null
4 modify address constraint customer_address_nn not null
5 modify city constraint customer_city_nn not null
6 modify phone constraint customer_phone_nn not null
7 modify DOB constraint customer_dob_nn not null;
Table altered.
SQL>
SQL> create table CustomerAudit
2 (Customer_ID integer,
3 State_ID integer,
4 First_Name Varchar2(50),
5 Middle_Name Varchar2(50),
6 Last_Name Varchar2(50),
7 Address Varchar2(50),
8 City Varchar2(45),
9 Phone Varchar2(45),
10 DOB Date,
11 Old_CustomerID integer,
12 Old_StateID integer,
13 Old_FirstName Varchar2(50),
14 Old_MiddleName Varchar2(50),
15 Old_LastName Varchar2(50),
16 Old_Address Varchar2(50),
17 Old_City Varchar2(45),
18 Old_Phone Varchar2(45),
19 Old_DOB Date,
1) ,

30
25 );
Table created.
SQL>
SQL>
SQL> create table ProductCategory
2 (Product_CategoryID Integer GENERATED ALWAYS AS IDENTITY
BY 19),
4 Category_Name varchar2(45) ,
5 Category_Description clob,
6 constraint Prodcategory_prodcategory_pk primary
key(Product_CategoryID)
7 );
Table created.
SQL>
SQL> Alter table ProductCategory
2 modify Category_Name constraint categoryName_nn not null;
Table altered.
SQL>
SQL>
SQL> create table ProductCategoryAudit
2 (Product_CategoryID Integer,
3 Category_Name Varchar2(45),
5 Old_ProdCategoryID Integer,
6 Old_CategoryName Varchar2(45),
7 Old_CategoryDescription clob,
1),
13 );
Table created.
SQL>
SQL> create table product
2 (Product_ID Integer GENERATED ALWAYS AS IDENTITY
BY 15),
4 Product_CategoryID Integer ,
5 Product_Name varchar2(50),
6 Product_Color varchar2(35),
7 Product_Size varchar2(35),
8 Product_Weight varchar2(45),
9 Product_Description Clob,
10 constraint Product_Prodid_pk primary
key(Product_ID,Product_CategoryID)
11 ,constraint product_prodid_uk unique (Product_ID),

31
12 constraint product_prodcategoid_fk foreign key(Product_CategoryID)
references
13 ProductCategory(Product_CategoryID)
14 );
Table created.
SQL> Alter table Product
2 modify Product_Name constraint product_prodname_nn not null;
Table altered.
SQL>
SQL>
SQL> create table productAudit
2 (Product_ID Integer,
3 Product_CategoryID Integer,
4 Product_Name Varchar2(50),
5 Product_Color Varchar2(35),
6 Product_Size Varchar2(35),
7 Product_Weight Varchar2(45),
9 Old_ProductID Integer,
10 Old_ProdCategoryID Integer,
11 Old_ProdName Varchar2(50),
12 Old_ProdColor Varchar2(35),
13 Old_ProdSize Varchar2(35),
14 Old_ProdWeight Varchar2(45),
15 Old_ProdDescription Clob,
1) ,
21 );
Table created.
SQL>
SQL> create table PaymentCop
2 (Payment_id integer GENERATED ALWAYS AS IDENTITY
BY 14) ,
4 Payment_CopName varchar2(50),
5 Payment_Type varchar2(45),
6 Payment_CreatedDare Date,
7 Payment_Status varchar2(25),
8 PaymentCard_Number integer,
9 Payment_ExpDate Date,
10 constraint PaymentCop_paymentid_pk primary key(Payment_id),
11 constraint Paymentcop_PaymentType_ck check (Payment_Type
in('VISA','MasterCard', 'Discovery','American Express','NA',
12 'visa','mastercard','discovery','american express'))
13 );
Table created.

32
SQL>
SQL>
SQL> Alter table PaymentCop
2 modify PaymentCard_Number constraint Paymentcop_Paycardnum_nn not
null
3 modify Payment_CopName constraint Paymentcop_PayCopName_nn not null
4 modify Payment_Type constraint Paymentcop_PaycopType_nn not null;
Table altered.
SQL>
SQL> create table PaymentCopAudit
2 (Payment_id integer,
3 Payment_CopName Varchar2(50),
4 Payment_Type Varchar2(45),
6 Payment_Status varchar2(25),
7 PaymentCard_Number integer,
8 Old_Payid integer,
9 Old_PayCopName Varchar2(50),
10 old_PayType Varchar2(45),
11 Old_PayCreatedDare Date,
12 Old_PayStatus varchar2(25),
13 Old_PayCard_Number integer,
BY 1) ,
19 );
Table created.
SQL>
SQL> create table Receipt
2 (Receipt_ID Integer GENERATED ALWAYS AS IDENTITY
BY 20),
5 Employee_ID integer ,
6 Customer_ID integer ,
8 Receipt_StateCode Integer,
9 Product_ID Integer ,
10 Receipt_Amount decimal (8,2),
11 Receipt_Tax decimal (8,2) ,
12 QTY Integer ,
13 Receipt_FinalAmt decimal (8,2),
14 Payment_ID integer ,
15 constraint Receipt_Receipid_pk primary
key(Receipt_ID,State_ID,Employee_ID,Customer_ID,Store_ID
16 ,Receipt_StateCode,Product_ID, Payment_ID),
17 constraint Receipt_Receiptid_uk unique (Receipt_ID),
18 constraint Receipt_customerid_fk foreign key(Customer_ID) references
Customer(Customer_ID),
19 constraint Receipt_Employeeid_fk foreign key(Employee_ID) references
Employee (Employee_ID),

33
20 constraint Receipt_Stateid_fk foreign key(State_ID) references
statecodes(state_id),
21 constraint Receipt_storeid_fk foreign key(Store_id) references
store(Store_id),
22 constraint Receipt_Productid_fk foreign key(Product_ID) references
Product (Product_ID),
23 constraint Receipt_paymentid_fk foreign key(Payment_ID) references
PaymentCOP(Payment_ID)
24 );
Table created.
SQL>
SQL> Alter table Receipt
2 add Receipt_Date DATE;
Table altered.
SQL> Alter table Receipt
2 modify Receipt_Amount constraint receipt_ReceiptAmount_nn not null
3 modify Receipt_Tax constraint receipt_ReceiptTax_nn not null
4 modify QTY constraint receipt_qty_nn not null
5 modify Receipt_Date constraint Receipt_Date_nn not null
6 modify Receipt_FinalAmt constraint Receipt_FinalAmt_nn not null;
Table altered.
SQL>
SQL>
SQL> create table ReceiptAudit
2 (Receipt_ID Integer,
3 State_ID integer,
5 Customer_ID integer,
6 Store_ID integer,
7 Receipt_StateCode Integer,
8 Product_ID Integer,
9 Receipt_Amount numeric ,
10 Receipt_Tax decimal (8,2),
11 QTY Integer,
12 Receipt_FinalAmt numeric,
13 old_ReceiptID Integer,
14 old_StateID integer,
15 old_EmployeeID integer,
17 old_StoreID integer,
18 Old_RecptStatecd integer,
19 Old_Prodid integer,
20 Old_RecptAmt numeric,
21 Old_Recepttax decimal(8,2),
22 Old_QTY Integer,
23 old_recptFinalAmt numeric,
BY 1) ,

34
29 );
Table created.
SQL> Alter table ReceiptAudit
2 add Receipt_Date DATE;
Table altered.
SQL>
SQL> Alter table ReceiptAudit
2 add Old_ReceiptDate DATE;
Table altered.
SQL>
SQL>
SQL> create table shippingco
2 (shipper_id integer GENERATED ALWAYS AS IDENTITY
BY 25),
4 Shipper_Name varchar2(50) ,
5 status char(25) ,
6 Create_date date,
7 constraint shippingco_shipperid_pk primary key(shipper_id)
8 );
Table created.
SQL>
SQL> Alter table shippingco
2 modify Shipper_Name constraint shipco_shippername_nn not null
3 modify status constraint shipco_status_nn not null;
Table altered.
SQL>
SQL>
SQL>
SQL> create table shippingcoAudit
2 (shipper_id integer,
3 Shipper_Name Varchar2(50),
4 status char(25),
5 Create_date date,
6 Old_shipperid integer,
7 old_ShipperName Varchar2(50),
8 old_status char(25),
9 old_Create_date date,
BY 1),
15 );

35
Table created.
SQL>
SQL> create table shipment
2 (shipping_id integer GENERATED ALWAYS AS IDENTITY
BY 26),
4 shipper_id integer ,
5 state_id integer ,
6 Receipt_ID integer ,
7 ship_date date,
8 ship_enddate date,
9 receive_date date,
10 Name varchar2(55),
12 City varchar2(55),
13 shipping_amount numeric,
14 shipping_type char(35),
15 constraint shipment_shipmentid_pk primary
key(shipping_id,shipper_id,RECEIPT_ID ),
16 constraint shipment_shipermid_uk unique (shipping_id),
17 constraint shipment_shipperid_fk foreign key(shipper_id) references
shippingco(shipper_id),
18 CONSTRAINT SHIPMENT_RECEIPTID_FK FOREIGN KEY(RECEIPT_ID) REFERENCES
RECEIPT(RECEIPT_ID)
19 );
Table created.
SQL>
SQL>
SQL>
SQL> create table shippmentAudit
2 (shipping_id integer,
3 shipper_id integer,
4 state_id integer,
5 Receipt_ID integer,
6 ship_date date,
10 Address Varchar2(55),
12 shipping_amount number,
14 old_shippingid integer,
15 old_shipperid integer,
16 old_stateid integer,
17 old_ReceiptID integer,
18 old_shipdate date,
19 old_shipenddate date,
20 old_receivedate date,
21 old_Name varchar2(55),
22 old_Address Varchar2(55),
23 old_City varchar2(55),
24 old_shippingamt number,

36
25 old_shippingtype char(35),
BY 26),
31 );
Table created.
Index
SQL>
SQL> create index Management_HireDate_indx on Management (Hire_Date);
Index created.
SQL> create index shippment_shippmentname_indx on shipment (Name);
Index created.
SQL> create index country_countryname_idx on countrycode (Country_Name);
Index created.
SQL> create index country_countryabbrv_indx on countrycode (Country_Abbrv)
;
Index created.
SQL> create index statecodes_statename_indx on statecodes (State_Name);
Index created.
SQL> create index statecodes_StateAbbrv_indx on statecodes (State_Abbrv);
Index created.
SQL> create index statecodes_TaxRate_indx on statecodes (State_TaxRate);
Index created.
SQL> create index Employee_FirstName_indx on Employee (First_Name);
Index created.
SQL> set echo off
SQL> spool off

38
Triggers
SQL>
SQL> --This trigger will populate the audit date using the date from
oracle date automatically--
SQL>
SQL>
SQL> create or replace trigger concodeAudit_auditdate_trgg
2 before insert on CountryCodesAudit
3 for each row
4 begin
5 select sysdate into :New.Audit_Date from dual;
6 end concodeAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> --This trigger will populate the CountryCodesAudit table
automatically by selecting the user--
SQL>
SQL> create or replace trigger concodeAudit_audituser_trgg
2 before insert on CountryCodesAudit
3 for each row
4 begin
5 select user into :New.Audit_User from dual;
6 end concodeAudit_audituser_trgg ;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger CountryCodesAudit_del_trigg
2 after delete on countrycode
3 for each row
4 DECLARE
5 OCCD INTEGER;
6 OCCN Varchar2(60);
7 OCAB Varchar2(12);
8 ACT char(20);
9 begin
10 SELECT :OLD.Country_Code INTO OCCD FROM DUAL;
11 SELECT :OLD.Country_Name INTO OCCN FROM DUAL;
12 SELECT :OLD.Country_Abbrv INTO OCAB FROM DUAL;
13 SELECT 'Delete' INTO ACT FROM DUAL;
14
15 INSERT INTO CountryCodesAudit(old_countrycode,
old_countryname,old_countryabbrv, Action_Type )
16 SELECT OCCD, OCCN, OCAB, ACT FROM DUAL;
17
18 end CountryCodesAudit_del_trigg;

39
19 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger CountryCodesAudit_inst_trigg
2 after insert on countrycode
3 for each row
4
5 DECLARE
6 NCCD INTEGER;
7 NCCN Varchar2(60);
8 NCAB Varchar2(12);
9 ACT char(20);
10
11 begin
12
13 SELECT :NEW.Country_Code INTO NCCD FROM DUAL;
14 SELECT :NEW.Country_Name INTO NCCN FROM DUAL;
15 SELECT :NEW.Country_Abbrv INTO NCAB FROM DUAL;
16 SELECT 'Insert' INTO ACT FROM DUAL;
17
18
19 INSERT INTO CountryCodesAudit(Country_Code,
Country_Name,Country_Abbrv, Action_Type )
20 SELECT NCCD, NCCN, NCAB, ACT FROM DUAL;
21
22
23 end CountryCodesAudit_inst_trigg;
24 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger CountryCodesAudit_upd_trigg
2 after update on countrycode
3 for each row
4 DECLARE
5 NCCD INTEGER;
6 NCCN Varchar2(60);
7 NCAB Varchar2(12);
8 OCCD INTEGER;
9 OCCN Varchar2(60);
10 OCAB Varchar2(12);
11 ACT char(20);
12
13 begin
14

40
16 SELECT :NEW.Country_Name INTO NCCN FROM DUAL;
17 SELECT :NEW.Country_Abbrv INTO NCAB FROM DUAL;
19 SELECT :OLD.Country_Name INTO OCCN FROM DUAL;
20 SELECT :OLD.Country_Abbrv INTO OCAB FROM DUAL;
21 SELECT 'Update' INTO ACT FROM DUAL;
22
23
24 INSERT INTO CountryCodesAudit(Country_Code,
Country_Name,Country_Abbrv,old_countrycode, old_countryname,
25 old_countryabbrv, Action_Type )
26 SELECT NCCD, NCCN, NCAB,OCCD, OCCN, OCAB, ACT FROM DUAL;
27
28
29 end CountryCodesAudit_upd_trigg;
30 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger statecodesAudit_auditdate_trgg
2 before insert on StateCodesAudit
3 for each row
4 begin
6 end statecodesAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger statecodesAudit_audituser_trgg
2 before insert on StateCodesAudit
3 for each row
4 begin
6 end statecodesAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>

41
SQL> CREATE OR REPLACE TRIGGER statecodesAdut_del_trigg
2 AFTER DELETE on STATECODES
3 FOR EACH ROW
4 DECLARE
5 OSTID INTEGER;
6 OCCD INTEGER;
7 OSTN Varchar2(45);
8 OSTAB Varchar2(10);
9 OSTRT Integer;
10 ACT char(20);
11 BEGIN
12 SELECT :OLD.State_ID INTO OSTID FROM DUAL;
14 SELECT :OLD.State_Name INTO OSTN FROM DUAL;
15 SELECT :OLD.State_Abbrv INTO OSTAB FROM DUAL;
16 SELECT :OLD.State_TaxRate INTO OSTRT FROM DUAL;
18
19 INSERT INTO STATECODESAUDIT( Old_State_ID
,Old_Country_Code,Old_State_Name,Old_State_Abbrv,Old_State_TaxRate,
20 Action_Type)
21 SELECT OSTID, OCCD, OSTN,OSTAB,OSTRT,ACT FROM DUAL;
22
23
24 END statecodesAdut_del_trigg;
25 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER statecodesAdut_inst_trigg
2 AFTER INSERT on STATECODES
3 FOR EACH ROW
4 DECLARE
5 NSTID INTEGER;
6 NCCD INTEGER;
7 NSTN Varchar2(45);
8 NSTAB Varchar2(10);
9 NSTRT Integer;
10 ACT char(20);
11 BEGIN
12 SELECT :NEW.State_ID INTO NSTID FROM DUAL;
14 SELECT :NEW.State_Name INTO NSTN FROM DUAL;
15 SELECT :NEW.State_Abbrv INTO NSTAB FROM DUAL;
16 SELECT :NEW.State_TaxRate INTO NSTRT FROM DUAL;
17 SELECT 'INSERT' INTO ACT FROM DUAL;
18
19 INSERT INTO STATECODESAUDIT( State_ID
,Country_Code,State_Name,State_Abbrv,State_TaxRate,
20 Action_Type)
21 SELECT NSTID, NCCD, NSTN,NSTAB,NSTRT,ACT FROM DUAL;
22
23
24
25 END statecodesAdut_inst_trigg;

42
26 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER statecodesAdut_upd_trigg
2 AFTER UPDATE on STATECODES
3 FOR EACH ROW
4 DECLARE
5 NSTID INTEGER;
6 NCCD INTEGER;
8 NSTAB Varchar2(10);
9 NSTRT Integer;
10 OSTID INTEGER;
11 OCCD INTEGER;
13 OSTAB Varchar2(10);
14 OSTRT Integer;
15 ACT char(20);
16 BEGIN
19 SELECT :NEW.State_Name INTO NSTN FROM DUAL;
20 SELECT :NEW.State_Abbrv INTO NSTAB FROM DUAL;
21 SELECT :NEW.State_TaxRate INTO NSTRT FROM DUAL;
24 SELECT :OLD.State_Name INTO OSTN FROM DUAL;
25 SELECT :OLD.State_Abbrv INTO OSTAB FROM DUAL;
26 SELECT :OLD.State_TaxRate INTO OSTRT FROM DUAL;
28
29 INSERT INTO STATECODESAUDIT( State_ID
,Country_Code,State_Name,State_Abbrv,State_TaxRate,
30 Old_State_ID
,Old_Country_Code,Old_State_Name,Old_State_Abbrv,Old_State_TaxRate,
Action_Type)
31 SELECT NSTID, NCCD, NSTN,NSTAB,NSTRT,OSTID, OCCD,
OSTN,OSTAB,OSTRT ,ACT FROM DUAL;
32
33
34
35 END statecodesAdut_upd_trigg;
36 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>

43
SQL>
SQL>
SQL> create or replace trigger EmployeeAudit_auditdate_trgg
2 before insert on EmployeeAudit
3 for each row
4 begin
6 end EmployeeAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGEr EmployeeAudit_audituser_trgg
2 BEFORE INSERT ON EMPLOYEEAUDIT
3 FOR EACH ROW
4 BEGIN
5 SELECT USER INTO :NEW.AUDIT_USER FROM DUAL;
6 END EMPLOYEEAUDIT_AUDITUSER_TRGG;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER EmployeeAudit_del_trigg
2 AFTER DELETE ON EMPLOYEE
3 FOR EACH ROW
4
5 DECLARE
6
7 OEMP INTEGER;
8 OFN varchar2(55);
9 OMN varchar2(55) ;
10 OLN Varchar2(55);
11 OAD varchar2(55);
12 OCIT Varchar2(45);
13 OSTD Integer;
14 OPN Varchar2(45);
15 ACT char(20);
16 BEGIN
17 SELECT :OLD.Employee_ID INTO OEMP FROM DUAL;
18 SELECT :OLD.First_Name INTO OFN FROM DUAL;
19 SELECT :OLD.Middle_Name INTO OMN FROM DUAL;
20 SELECT :OLD.Last_Name INTO OLN FROM DUAL;
21 SELECT :OLD.Address INTO OAD FROM DUAL;
22 SELECT :OLD.City INTO OCIT FROM DUAL;
23 SELECT :OLD.State_ID INTO OSTD FROM DUAL;
24 SELECT :OLD.Phone INTO OPN FROM DUAL;
26
27

44
28
29 INSERT INTO EMPLOYEEAUDIT( Old_Employee_ID
,Old_First_Name,Old_Middle_Name,Old_Last_Name,Old_Address,Old_City,
Old_State_ID,
30 Old_Phone , Action_Type)
31 SELECT OEMP,OFN, OMN, OLN ,OAD, OCIT ,OSTD ,OPN ,ACT FROM
DUAL;
32
33
34 end EmployeeAudit_del_trigg;
35 /
Trigger created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER EmployeeAudit_inst_trigg
2 AFTER INSERT ON EMPLOYEE
3 FOR EACH ROW
4 DECLARE
5 NEMP INTEGER;
6 NFN varchar2(55);
7 NMN varchar2(55) ;
8 NLN Varchar2(55);
9 NAD varchar2(55);
10 NCIT Varchar2(45);
11 NSTD Integer;
12 NPN Varchar2(45);
13 ACT char(20);
14 BEGIN
15 SELECT :NEW.Employee_ID INTO NEMP FROM DUAL;
16 SELECT :NEW.First_Name INTO NFN FROM DUAL;
17 SELECT :NEW.Middle_Name INTO NMN FROM DUAL;
18 SELECT :NEW.Last_Name INTO NLN FROM DUAL;
19 SELECT :NEW.Address INTO NAD FROM DUAL;
20 SELECT :NEW.City INTO NCIT FROM DUAL;
21 SELECT :NEW.State_ID INTO NSTD FROM DUAL;
22 SELECT :NEW.Phone INTO NPN FROM DUAL;
24
25 INSERT INTO EMPLOYEEAUDIT( Employee_ID
,First_Name,Middle_Name,Last_Name,Address,City, State_ID,
26 Phone , Action_Type)
27 SELECT NEMP,NFN, NMN, NLN ,NAD, NCIT ,NSTD ,NPN ,ACT FROM
DUAL;
28
29
30 end EmployeeAudit_inst_trigg;
31 /
Trigger created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER EmployeeAudit_updt_trigg

45
2 AFTER UPDATE ON EMPLOYEE
3 FOR EACH ROW
4 DECLARE
5 NEMP INTEGER;
6 NFN varchar2(55);
7 NMN varchar2(55) ;
8 NLN Varchar2(55);
9 NAD varchar2(55);
11 NSTD Integer;
13 OEMP INTEGER;
14 OFN varchar2(55);
15 OMN varchar2(55) ;
17 OAD varchar2(55);
19 OSTD Integer;
21 ACT char(20);
22 BEGIN
25 SELECT :NEW.Middle_Name INTO NMN FROM DUAL;
29 SELECT :NEW.State_ID INTO NSTD FROM DUAL;
33 SELECT :OLD.Middle_Name INTO OMN FROM DUAL;
37 SELECT :OLD.State_ID INTO OSTD FROM DUAL;
40
41
42
43 INSERT INTO EMPLOYEEAUDIT( Employee_ID
,First_Name,Middle_Name,Last_Name,Address,City, State_ID,
44 Phone , Old_Employee_ID
,Old_First_Name,Old_Middle_Name,Old_Last_Name,Old_Address,Old_City,
Old_State_ID,
45 Old_Phone, Action_Type)
46 SELECT NEMP,NFN, NMN, NLN ,NAD, NCIT ,NSTD ,NPN ,OEMP,OFN,
OMN, OLN ,OAD, OCIT ,OSTD ,OPN , ACT FROM DUAL;
47
48 end EmployeeAudit_updt_trigg;
49 /
Trigger created.
SQL>
SQL>

46
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRolesAudit_auditdate_trgg
2 BEFORE INSERT ON HRROLESAUDIT
3 FOR EACH ROW
4 BEGIN
5 SELECT SYSDATE INTO :NEW.AUDIT_DATE FROM DUAL;
6 END HRROLESAUDIT_AUDITDATE_TRGG;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGEr HRRolesAudit_audituser_trgg
2 BEFORE INSERT ON HRROLESAUDIT
3 FOR EACH ROW
4 BEGIN
6 end HRRolesAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRolesAudit_del_trigg
2 AFTER DELETE ON HRRoles
3 FOR EACH ROW
4
5 DECLARE
6 ORID INTEGER;
7 ORTL varchar2(45);
8 ORST varchar2(45) ;
9 ACT char(20);
10 BEGIN
11 SELECT :OLD.Role_ID INTO ORID FROM DUAL;
12 SELECT :OLD.Role_Title INTO ORTL FROM DUAL;
13 SELECT :OLD.Role_status INTO ORST FROM DUAL;
15
16
17 INSERT INTO HRRolesAudit(Old_RoleID,Old_RoleTitle
,Old_Role_status
18 ,Action_Type)
19 SELECT ORID,ORTL, ORST , ACT FROM DUAL;
20

47
21 END HRRolesAudit_del_trigg;
22 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRolesAudit_inst_trigg
2 AFTER INSERT ON HRRoles
3 FOR EACH ROW
4
5 DECLARE
6 NRID INTEGER;
7 NRTL varchar2(45);
8 NRST varchar2(45) ;
9 ACT char(20);
10 BEGIN
11 SELECT :NEW.Role_ID INTO NRID FROM DUAL;
12 SELECT :NEW.Role_Title INTO NRTL FROM DUAL;
13 SELECT :NEW.Role_status INTO NRST FROM DUAL;
15
16
17 INSERT INTO HRRolesAudit(Role_ID,Role_Title ,Role_status
18 ,Action_Type)
19 SELECT NRID,NRTL, NRST , ACT FROM DUAL;
20
21 END HRRolesAudit_inst_trigg;
22 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRolesAudit_upd_trigg
2 AFTER UPDATE ON HRRoles
3 FOR EACH ROW
4
5 DECLARE
6 NRID INTEGER;
7 NRTL varchar2(45);
8 NRST varchar2(45) ;
9 ORID INTEGER;
10 ORTL varchar2(45);
11 ORST varchar2(45) ;
12 ACT char(20);
13 BEGIN
15 SELECT :NEW.Role_Title INTO NRTL FROM DUAL;
16 SELECT :NEW.Role_status INTO NRST FROM DUAL;
18 SELECT :OLD.Role_Title INTO ORTL FROM DUAL;
19 SELECT :OLD.Role_status INTO ORST FROM DUAL;
21
22

48
23 INSERT INTO HRRolesAudit(Role_ID,Role_Title
,Role_status,Old_RoleID,Old_RoleTitle ,Old_Role_status
24 ,Action_Type)
25 SELECT NRID,NRTL, NRST ,ORID,ORTL, ORST, ACT FROM DUAL;
26
27 END HRRolesAudit_upd_trigg;
28 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRecordsAudit_auditdate_trgg
2 BEFORE INSERT ON HRRECORDSAUDIT
3 FOR EACH ROW
4 BEGIN
5 SELECT SYSDATE INTO :NEW.AUDIT_DATE FROM DUAL;
6 end HRRecordsAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRecordsAudit_audituser_trgg
2 BEFORE INSERT ON HRRecordsAudit
3 FOR EACH ROW
4 BEGIN
5 SELECT USER INTO :NEW.AUDIT_USER FROM DUAL;
6 end HRRecordsAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRecordsAudit_del_trigg
2 AFTER DELETE ON HRRecords
3 FOR EACH ROW
4 DECLARE
5 OEMP INTEGER;
6 OHRD INTEGER;
7 ORID INTEGER ;
8 OSTD INTEGER;
9 ODB DATE;
10 OSSN INTEGER;
11 OHD Date;
12 OEND Date;
13 OPR numeric(15,2);
14 OEMS varchar2(45);
15 ACT char(20);

49
16
17 BEGIN
19 SELECT :OLD.HR_ID INTO OHRD FROM DUAL;
21 SELECT :OLD.Store_ID INTO OSTD FROM DUAL;
22 SELECT :OLD.DOB INTO ODB FROM DUAL;
23 SELECT :OLD.Social_SecurityNum INTO OSSN FROM DUAL;
24 SELECT :OLD.Hire_Date INTO OHD FROM DUAL;
25 SELECT :OLD.End_Date INTO OEND FROM DUAL;
26 SELECT :OLD.PayRate INTO OPR FROM DUAL;
27 SELECT :OLD.Employement_Status INTO OEMS FROM DUAL;
29
30 INSERT INTO
HRRecordsAudit(Old_EmployeeID,Old_HRID,Old_RoleID,Old_StoreID,Old_DOB ,
31 Old_SocialSecurityNum,Old_HireDate,
Old_EndDate,Old_PayRate,Old_EmployementStatus,Action_Type)
32 SELECT OEMP, OHRD, ORID, OSTD, ODB,OSSN,OHD, OEND, OPR,OEMS,ACT
FROM DUAL;
33 end HRRecordsAudit_del_trigg;
34 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRecordsAudit_inst_trigg
2 AFTER INSERT ON HRRecords
3 FOR EACH ROW
4 DECLARE
5 NEMP INTEGER;
6 NHRD INTEGER;
7 NRID INTEGER ;
8 NSTD INTEGER;
9 NDB DATE;
10 NSSN INTEGER;
11 NHD Date;
12 NEND Date;
13 NPR numeric(15,2);
14 NEMS varchar2(45);
15 ACT char(20);
16
17 BEGIN
19 SELECT :NEW.HR_ID INTO NHRD FROM DUAL;
21 SELECT :NEW.Store_ID INTO NSTD FROM DUAL;
22 SELECT :NEW.DOB INTO NDB FROM DUAL;
23 SELECT :NEW.Social_SecurityNum INTO NSSN FROM DUAL;
24 SELECT :NEW.Hire_Date INTO NHD FROM DUAL;
25 SELECT :NEW.End_Date INTO NEND FROM DUAL;
26 SELECT :NEW.PayRate INTO NPR FROM DUAL;
27 SELECT :NEW.Employement_Status INTO NEMS FROM DUAL;

50
29
30 INSERT INTO
HRRecordsAudit(Employee_ID,HR_ID,Role_ID,Store_ID,DOB,Social_SecurityNum,H
ire_Date, End_Date,PayRate,
31 Employement_Status,Action_Type)
32 SELECT NEMP, NHRD, NRID, NSTD, NDB,NSSN,NHD, NEND, NPR,NEMS,ACT
FROM DUAL;
33
34 end HRRecordsAudit_inst_trigg;
35 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER HRRecordsAudit_upd_trigg
2 AFTER Update ON HRRecords
3 FOR EACH ROW
4 DECLARE
5 NEMP INTEGER;
6 NHRD INTEGER;
7 NRID INTEGER ;
8 NSTD INTEGER;
9 NDB DATE;
10 NSSN INTEGER;
11 NHD Date;
12 NEND Date;
13 NPR numeric(15,2);
14 NEMS varchar2(45);
15 OEMP INTEGER;
16 OHRD INTEGER;
17 ORID INTEGER ;
18 OSTD INTEGER;
19 ODB DATE;
20 OSSN INTEGER;
21 OHD Date;
22 OEND Date;
23 OPR numeric(15,2);
24 OEMS varchar2(45);
25 ACT char(20);
26
27 BEGIN
29 SELECT :NEW.HR_ID INTO NHRD FROM DUAL;
31 SELECT :NEW.Store_ID INTO NSTD FROM DUAL;
33 SELECT :NEW.Social_SecurityNum INTO NSSN FROM DUAL;
35 SELECT :NEW.End_Date INTO NEND FROM DUAL;
36 SELECT :NEW.PayRate INTO NPR FROM DUAL;
37 SELECT :NEW.Employement_Status INTO NEMS FROM DUAL;

51
39 SELECT :OLD.HR_ID INTO OHRD FROM DUAL;
41 SELECT :OLD.Store_ID INTO OSTD FROM DUAL;
43 SELECT :OLD.Social_SecurityNum INTO OSSN FROM DUAL;
45 SELECT :OLD.End_Date INTO OEND FROM DUAL;
46 SELECT :OLD.PayRate INTO OPR FROM DUAL;
47 SELECT :OLD.Employement_Status INTO OEMS FROM DUAL;
49
50 INSERT INTO
HRRecordsAudit(Employee_ID,HR_ID,Role_ID,Store_ID,DOB,Social_SecurityNum,H
ire_Date, End_Date,PayRate,
51
Employement_Status,Old_EmployeeID,Old_HRID,Old_RoleID,Old_StoreID,Old_DOB
,
52 Old_SocialSecurityNum,Old_HireDate,
Old_EndDate,Old_PayRate,Old_EmployementStatus,Action_Type)
53 SELECT NEMP, NHRD, NRID, NSTD, NDB,NSSN,NHD, NEND,
NPR,NEMS,OEMP, OHRD, ORID, OSTD, ODB,OSSN,OHD, OEND, OPR,OEMS,
54 ACT FROM DUAL;
55
56 end HRRecordsAudit_upd_trigg;
57 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ManagementAudit_auditdate_trgg
2 BEFORE INSERT ON MANAGEMENTAUDIT
3 FOR EACH ROW
4 BEGIN
6 end HRRecordsAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger ManagementAudit_audituser_trgg
2 before insert on ManagementAudit
3 for each row
4 begin
6 end ManagementAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>

52
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ManagementAuditt_del_trigg
2 AFTER DELETE ON Management
3 for each row
4 DECLARE
5 OMID Integer;
6 OEMP Integer;
7 OHD DATE;
8 OEND DATE;
9 ACT char(20);
10 BEGIN
11
12 SELECT :OLD.Manager_ID INTO OMID FROM DUAL;
15 SELECT :OLD.Hire_EndDate INTO OEND FROM DUAL;
17
18 INSERT INTO ManagementAudit(Old_ManagerID,Old_EmployeeID
,Old_HireDate, Old_HireEndDate ,Action_Type)
19 SELECT OMID, OEMP, OHD,OEND,ACT FROM DUAL;
20
21
22 end ManagementAuditt_del_trigg;
23 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ManagementAuditt_inst_trigg
2 AFTER INSERT ON Management
3 for each row
4 DECLARE
5 NMID Integer;
6 NEMP Integer;
7 NHD DATE;
8 NEND DATE;
9 ACT char(20);
10 BEGIN
11
12 SELECT :NEW.Manager_ID INTO NMID FROM DUAL;
15 SELECT :NEW.Hire_EndDate INTO NEND FROM DUAL;
17
18 INSERT INTO ManagementAudit(Manager_ID,Employee_ID ,Hire_Date,
Hire_EndDate ,Action_Type)

53
19 SELECT NMID, NEMP, NHD,NEND,ACT FROM DUAL;
20
21
22 end ManagementAuditt_inst_trigg;
23 /
Trigger created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ManagementAuditt_updt_trigg
2 AFTER UPDATE ON Management
3 for each row
4 DECLARE
5 NMID Integer;
6 NEMP Integer;
7 NHD DATE;
8 NEND DATE;
9 OMID Integer;
10 OEMP Integer;
11 OHD DATE;
12 OEND DATE;
13 ACT char(20);
14 BEGIN
15
25
Hire_EndDate ,Old_ManagerID,Old_EmployeeID ,Old_HireDate, Old_HireEndDate,
27 Action_Type)
28 SELECT NMID, NEMP, NHD,NEND,OMID, OEMP, OHD,OEND,ACT FROM
DUAL;
29
30 end ManagementAuditt_updt_trigg;
31
32 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger storeAudit_auditdate_trgg
2 before insert on storeAudit
3 for each row
4 begin
6 end storeAudit_auditdate_trgg;

54
7 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger storeAudit_audituser_trgg
2 before insert on storeAudit
3 for each row
4 begin
6 end storeAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER storeAudit_del_trigg
2 AFTER DELETE ON STORE
3 FOR EACH ROW
4 DECLARE
5 OSTID Integer;
6 OSSTID Integer;
7 OSMID Integer;
9 OSTY Varchar2(34);
10 OSTA Varchar2(65);
11 OSTC Varchar2(45);
12 OSP Varchar2(45);
13 OSTS Varchar2(30);
14 ACT char(20);
15 BEGIN
16 SELECT :OLD.Store_ID INTO OSTID FROM DUAL;
17 SELECT :OLD.Store_StateID INTO OSSTID FROM DUAL;
18 SELECT :OLD.Store_ManagerID INTO OSMID FROM DUAL;
19 SELECT :OLD.Store_Name INTO OSTN FROM DUAL;
20 SELECT :OLD.Store_Type INTO OSTY FROM DUAL;
21 SELECT :OLD.Store_Address INTO OSTA FROM DUAL;
22 SELECT :OLD.Store_City INTO OSTC FROM DUAL;
23 SELECT :OLD.Store_Phone INTO OSP FROM DUAL;
24 SELECT :OLD.Store_Status INTO OSTS FROM DUAL;
26
27
28
29 insert into
storeAudit(Old_StoreID,Old_StorStateID,Old_StorManagerID,Old_StorName,Old_
StorType,Old_StorAddress,Old_StorCity,old_StorPhone,old_StorStatus,
Action_Type)
30 select OSTID, OSSTID, OSMID, OSTN, OSTY,OSTA,OSTC,OSP,OSTS, ACT
FROM DUAL;
31 end storeAudit_del_trigg;
32 /

55
Trigger created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER storeAudit_inst_trigg
2 AFTER INSERT ON STORE
3 FOR EACH ROW
4 DECLARE
5 NSTID Integer;
6 NSSTID Integer;
7 NSMID Integer;
9 NSTY Varchar2(34);
10 NSTA Varchar2(65);
11 NSTC Varchar2(45);
12 NSP Varchar2(45);
13 NSTS Varchar2(30);
14 ACT char(20);
15 BEGIN
16 SELECT :NEW.Store_ID INTO NSTID FROM DUAL;
17 SELECT :NEW.Store_StateID INTO NSSTID FROM DUAL;
18 SELECT :NEW.Store_ManagerID INTO NSMID FROM DUAL;
19 SELECT :NEW.Store_Name INTO NSTN FROM DUAL;
20 SELECT :NEW.Store_Type INTO NSTY FROM DUAL;
21 SELECT :NEW.Store_Address INTO NSTA FROM DUAL;
22 SELECT :NEW.Store_City INTO NSTC FROM DUAL;
23 SELECT :NEW.Store_Phone INTO NSP FROM DUAL;
24 SELECT :NEW.Store_Status INTO NSTS FROM DUAL;
26
27
28
29 insert into
storeAudit(Store_ID,Store_StateID,Store_ManagerID,Store_Name,Store_Type,St
ore_Address,Store_City,Store_Phone,Store_Status, Action_Type)
30 select NSTID, NSSTID, NSMID, NSTN, NSTY,NSTA,NSTC,NSP,NSTS, ACT
FROM DUAL;
32 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER storeAudit_upd_trigg
2 AFTER update ON STORE
3 FOR EACH ROW
4 DECLARE
5 NSTID Integer;

56
6 NSSTID Integer;
7 NSMID Integer;
14 OSTID Integer;
15 OSSTID Integer;
16 OSMID Integer;
23 ACT char(20);
24 BEGIN
27 SELECT :NEW.Store_ManagerID INTO NSMID FROM DUAL;
36 SELECT :OLD.Store_ManagerID INTO OSMID FROM DUAL;
44
45 insert into
storeAudit(Store_ID,Store_StateID,Store_ManagerID,Store_Name,Store_Type,St
ore_Address,Store_City,Store_Phone,Store_Status,
46
Old_StoreID,Old_StorStateID,Old_StorManagerID,Old_StorName,Old_StorType,Ol
d_StorAddress,Old_StorCity,old_StorPhone,old_StorStatus,
47 Action_Type)
48 select NSTID, NSSTID, NSMID, NSTN, NSTY,NSTA,NSTC,NSP,NSTS,
OSTID, OSSTID, OSMID, OSTN, OSTY,OSTA,OSTC,OSP,OSTS, ACT FROM DUAL;
49 end storeAudit_upd_trigg;
50 /
Trigger created.
SQL>
SQL>
SQL>
SQL>

57
SQL> set echo off
SQL> spool off;
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> show user
USER is "HOWARDDBA"
SQL>
SQL>
SQL>
SQL> ---This trigger will populate the CustomerAudit table audit date
column
SQL>
SQL> create or replace trigger CustomerAudit_auditdate_trgg
2 before insert on CustomerAudit
3 for each row
4 begin
6 end CustomerAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> /*This trigger will populate the storeAudit table audit user column
automatically */
Trigger created.
SQL>
SQL> create or replace trigger CustomerAudit_audituser_trgg
2 before insert on CustomerAudit
3 for each row
4 begin
6 end CustomerAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER CustomerAudit_del_trigg
2 AFTER DELETE ON Customer
3 FOR EACH ROW
4 DECLARE
5 OCID integer;

58
6 OSTID Integer;
7 OFN Varchar2(50);
8 OMDN Varchar2(50);
9 OLN Varchar2(50);
10 OAD Varchar2(50);
13 ODB DATE;
14 ACT CHAR(20);
15 BEGIN
16 SELECT :OLD.Customer_ID INTO OCID FROM DUAL;
19 SELECT :OLD.Middle_Name INTO OMDN FROM DUAL;
26
27 INSERT INTO CUSTOMERAUDIT
28 (Old_CustomerID, Old_StateID ,Old_FirstName , Old_MiddleName,
Old_LastName, Old_Address ,Old_City, Old_Phone, Old_DOB , Action_Type)
29 SELECT OCID,OSTID,OFN,OMDN,OLN,OAD,OCIT,OPN,ODB ,ACT FROM DUAL;
30
31 END CustomerAudit_del_trigg;
32 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER CustomerAudit_Inst_trigg
2 AFTER INSERT ON Customer
3 FOR EACH ROW
4 DECLARE
5 NCID integer;
6 NSTID Integer;
7 NFN Varchar2(50);
8 NMDN Varchar2(50);
9 NLN Varchar2(50);
10 NAD Varchar2(50);
13 NDB DATE;
14 ACT CHAR(20);
15 BEGIN
16 SELECT :NEW.Customer_ID INTO NCID FROM DUAL;
19 SELECT :NEW.Middle_Name INTO NMDN FROM DUAL;

59
26
28 (Customer_ID, State_ID ,First_Name , Middle_Name, Last_Name,
Address ,City, Phone, DOB , Action_Type)
29 SELECT NCID,NSTID,NFN,NMDN,NLN,NAD,NCIT,NPN,NDB ,ACT FROM DUAL;
30
31 END CustomerAudit_Inst_trigg;
32 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER CustomerAudit_Updt_trigg
2 AFTER UPDATE ON Customer
3 FOR EACH ROW
4 DECLARE
5 NCID integer;
6 NSTID Integer;
7 NFN Varchar2(50);
8 NMDN Varchar2(50);
9 NLN Varchar2(50);
10 NAD Varchar2(50);
13 NDB DATE;
14 OCID integer;
15 OSTID Integer;
16 OFN Varchar2(50);
17 OMDN Varchar2(50);
19 OAD Varchar2(50);
22 ODB DATE;
23 ACT CHAR(20);
24 BEGIN
25 SELECT :NEW.Customer_ID INTO NCID FROM DUAL;
28 SELECT :NEW.Middle_Name INTO NMDN FROM DUAL;
34 SELECT :OLD.Customer_ID INTO OCID FROM DUAL;
37 SELECT :OLD.Middle_Name INTO OMDN FROM DUAL;

60
44
46 (Customer_ID, State_ID ,First_Name , Middle_Name, Last_Name,
Address ,City, Phone, DOB , Old_CustomerID, Old_StateID ,Old_FirstName ,
Old_MiddleName, Old_LastName,
47 Old_Address ,Old_City, Old_Phone, Old_DOB ,Action_Type)
48 SELECT NCID,NSTID,NFN,NMDN,NLN,NAD,NCIT,NPN,NDB
,OCID,OSTID,OFN,OMDN,OLN,OAD,OCIT,OPN,ODB,ACT FROM DUAL;
49
50 END CustomerAudit_Updt_trigg;
51 /
Trigger created.
SQL> create or replace trigger ProdCatAudit_auddate_trgg
2 before insert on ProductCategoryAudit
3 for each row
4 begin
6 end ProdCatAudit_auddate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger ProdCatAudit_audituser_trgg
2 before insert on ProductCategoryAudit
3 for each row
4 begin
6 end ProdCatAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductCategoryAudit_del_trigg
2 AFTER DELETE ON ProductCategory
3 FOR EACH ROW
4 DECLARE
5 OPCID Integer;
6 OPCN Varchar2(45);

61
7 OPCD CLOB;
8 ACT Char(20);
9 BEGIN
10 SELECT :OLD.Product_CategoryID INTO OPCID FROM DUAL;
11 SELECT :OLD.Category_Name INTO OPCN FROM DUAL;
12 SELECT :OLD.Category_Description INTO OPCD FROM DUAL;
13 SELECT 'Delete' Into ACT FROM DUAL;
14
15
16 INSERT INTO ProductCategoryAudit
(Old_ProdCategoryID,Old_CategoryName,Old_CategoryDescription , Action_Type
)
17 SELECT OPCID,OPCN,OPCD, ACT
18
19 FROM DUAL;
20 END ProductCategoryAudit_del_trigg;
21 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ProdCategoryAudit_inst_trigg
2 AFTER INSERT ON ProductCategory
3 FOR EACH ROW
4 DECLARE
5 NPCID Integer;
6 NPCN Varchar2(45);
7 NPCD CLOB;
8 ACT Char(20);
9 BEGIN
10 SELECT :NEW.Product_CategoryID INTO NPCID FROM DUAL;
11 SELECT :NEW.Category_Name INTO NPCN FROM DUAL;
12 SELECT :NEW.Category_Description INTO NPCD FROM DUAL;
13 SELECT 'Insert' Into ACT FROM DUAL;
14
15
(Product_CategoryID,Category_Name,Category_Description , Action_Type )
17 SELECT NPCID,NPCN,NPCD, ACT
18
19 FROM DUAL;
20 END ProdCategoryAudit_inst_trigg;
21 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ProdCategoryAudit_upt_trigg
2 AFTER update ON ProductCategory
3 FOR EACH ROW

62
4 DECLARE
5 NPCID Integer;
6 NPCN Varchar2(45);
7 NPCD CLOB;
8 ACT Char(20);
9 OPCID Integer;
10 OPCN Varchar2(45);
11 OPCD CLOB;
12 BEGIN
14 SELECT :NEW.Category_Name INTO NPCN FROM DUAL;
15 SELECT :NEW.Category_Description INTO NPCD FROM DUAL;
17 SELECT :OLD.Category_Name INTO OPCN FROM DUAL;
18 SELECT :OLD.Category_Description INTO OPCD FROM DUAL;
19 SELECT 'Update' Into ACT FROM DUAL;
20
21
(Product_CategoryID,Category_Name,Category_Description ,
23 Old_ProdCategoryID,Old_CategoryName,Old_CategoryDescription,
24 Action_Type )
25 SELECT NPCID,NPCN,NPCD,OPCID,OPCN,OPCD, ACT
26
27 FROM DUAL;
28 END ProdCategoryAudit_upt_trigg;
29 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger productAudit_auditdate_trgg
2 before insert on productAudit
3 for each row
4 begin
6 end productAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger productAudit_audituser_trgg
2 before insert on productAudit
3 for each row
4 begin
6 end productAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>

63
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER productAudit_del_trigg
2 AFTER DELETE ON PRODUCT
3 FOR EACH ROW
4 DECLARE
5 OPID Integer;
6 OPCID Integer;
7 OPN Varchar2(50);
8 OPC Varchar2(35);
9 OPS Varchar2(35);
10 OPW Varchar2(45);
11 OPD CLOB;
12 ACT Char(20);
13
14 BEGIN
15 SELECT :OLD.Product_ID INTO OPID FROM DUAL;
17 SELECT :OLD.Product_Name INTO OPN FROM DUAL;
18 SELECT :OLD.Product_Color INTO OPC FROM DUAL;
19 SELECT :OLD.Product_Size INTO OPS FROM DUAL;
20 SELECT :OLD.Product_Weight INTO OPW FROM DUAL;
21 SELECT :OLD.Product_Description INTO OPD FROM DUAL;
23
24
25 INSERT INTO productAudit
26
(Old_ProductID,Old_ProdCategoryID,Old_ProdName,Old_ProdColor,Old_ProdSize,
Old_ProdWeight,Old_ProdDescription, Action_Type)
27 SELECT OPID,OPCID, OPN, OPC,OPS,OPW,OPD, ACT FROM DUAL;
28 END productAudit_del_trigg;
29 /
Trigger created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER productAudit_Inst_trigg
2 AFTER INSERT ON PRODUCT
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
6 NPCID Integer;
7 NPN Varchar2(50);
8 NPC Varchar2(35);
9 NPS Varchar2(35);
10 NPW Varchar2(45);
11 NPD CLOB;
12 ACT Char(20);
13
14 BEGIN
15 SELECT :NEW.Product_ID INTO NPID FROM DUAL;
17 SELECT :NEW.Product_Name INTO NPN FROM DUAL;
18 SELECT :NEW.Product_Color INTO NPC FROM DUAL;
19 SELECT :NEW.Product_Size INTO NPS FROM DUAL;

64
20 SELECT :NEW.Product_Weight INTO NPW FROM DUAL;
21 SELECT :NEW.Product_Description INTO NPD FROM DUAL;
23
24
26
(Product_ID,Product_CategoryID,Product_Name,Product_Color,Product_Size,Pro
duct_Weight,Product_Description, Action_Type)
27 SELECT NPID,NPCID, NPN, NPC,NPS,NPW,NPD, ACT FROM DUAL;
28 END productAudit_Inst_trigg;
29 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER productAudit_updt_trigg
2 AFTER update ON PRODUCT
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
6 NPCID Integer;
7 NPN Varchar2(50);
8 NPC Varchar2(35);
9 NPS Varchar2(35);
11 NPD CLOB;
12 OPID Integer;
13 OPCID Integer;
15 OPC Varchar2(35);
16 OPS Varchar2(35);
18 OPD CLOB;
19 ACT Char(20);
20
21 BEGIN
37

65
38
40
duct_Weight,Product_Description,
41
Old_ProductID,Old_ProdCategoryID,Old_ProdName,Old_ProdColor,Old_ProdSize,O
ld_ProdWeight,Old_ProdDescription,
42 Action_Type)
43 SELECT NPID,NPCID, NPN, NPC,NPS,NPW,NPD, OPID,OPCID, OPN,
OPC,OPS,OPW,OPD,
44 ACT FROM DUAL;
45 END productAudit_updt_trigg;
46 /
Trigger created.
SQL> set echo off;
SQL> spool off;
SQL>
SQL>
SQL>
SQL> create or replace trigger PaymentCopAudit_auditdate_trgg
2 before insert on PaymentCopAudit
3 for each row
4 begin
6 end PaymentCopAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger PaymentCopAudit_audituser_trgg
3 for each row
4 begin
6 end PaymentCopAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER PayCopAudit_del_trigg

66
2 AFTER DELETE ON PaymentCop
3 FOR EACH ROW
4 DECLARE
5 OPID Integer;
6 OPCNE Varchar2(50);
7 OPPT Varchar2(45);
8 OPCD Date;
9 OPST varchar2(25);
10 OPCN Integer;
11 OPCE Date;
12 ACT Char(20);
13
14 BEGIN
15 SELECT :OLD.PAYMENT_ID INTO OPID FROM DUAL;
16 SELECT :OLD.PAYMENT_COPNAME INTO OPCNE FROM DUAL;
17 SELECT :OLD.PAYMENT_TYPE INTO OPPT FROM DUAL;
18 SELECT :OLD.PAYMENT_CREATEDDATE INTO OPCD FROM DUAL;
19 SELECT :OLD.PAYMENT_STATUS INTO OPST FROM DUAL;
20 SELECT :OLD.PAYMENTCARD_NUMBER INTO OPCN FROM DUAL;
21 SELECT :OLD.PAYMENT_EXPDATE INTO OPCE FROM DUAL;
22 SELECT 'Delete' INTO ACT From DUAL;
23
24 INSERT INTO
PaymentCopAudit(Old_Payid,Old_PayCopName,old_PayType,Old_PayCreatedDate,Ol
d_PayStatus,Old_PayCard_Number,OLD_PCARD_EXPDATE
25 , Action_Type)
26 SELECT OPID,OPCNE,OPPT,OPCD,OPST, OPCN,OPCE, ACT
27
28 FROM DUAL;
29 end PayCopAudit_del_trigg;
30 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER PayCopAudit_Inst_trigg
2 AFTER INSERT ON PaymentCop
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
6 NPCNE Varchar2(50);
7 NPPT Varchar2(45);
8 NPCD Date;
9 NPST varchar2(25);
10 NPCN Integer;
11 NPCE Date;
12 ACT Char(20);
13
14 BEGIN
15 SELECT :NEW.PAYMENT_ID INTO NPID FROM DUAL;
16 SELECT :NEW.PAYMENT_COPNAME INTO NPCNE FROM DUAL;
17 SELECT :NEW.PAYMENT_TYPE INTO NPPT FROM DUAL;
18 SELECT :NEW.PAYMENT_CREATEDDATE INTO NPCD FROM DUAL;
19 SELECT :NEW.PAYMENT_STATUS INTO NPST FROM DUAL;

67
20 SELECT :NEW.PAYMENTCARD_NUMBER INTO NPCN FROM DUAL;
21 SELECT :NEW.PAYMENT_EXPDATE INTO NPCE FROM DUAL;
22 SELECT 'Insert' INTO ACT From DUAL;
23
24 INSERT INTO
PaymentCopAudit(Payment_id,Payment_CopName,Payment_Type,Payment_CreatedDat
e,Payment_Status,PaymentCard_Number,PAYMENT_EXPDATE
25 , Action_Type)
26 SELECT NPID,NPCNE,NPPT,NPCD,NPST, NPCN,NPCE, ACT
27
28 FROM DUAL;
29 end PayCopAudit_Inst_trigg;
30 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER PayCopAudit_upd_trigg
2 AFTER UPDATE ON PaymentCop
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
8 NPCD Date;
10 NPCN Integer;
11 NPCE Date;
12 OPID Integer;
15 OPCD Date;
17 OPCN Integer;
18 OPCE Date;
19 ACT Char(20);
20
21 BEGIN
36 SELECT 'Update' INTO ACT From DUAL;
37
38 INSERT INTO
PaymentCopAudit(Payment_id,Payment_CopName,Payment_Type,Payment_CreatedDat
e,Payment_Status,PaymentCard_Number,PAYMENT_EXPDATE ,

68
39
Old_Payid,Old_PayCopName,old_PayType,Old_PayCreatedDate,Old_PayStatus,Old_
PayCard_Number,OLD_PCARD_EXPDATE
40 , Action_Type)
41 SELECT NPID,NPCNE,NPPT,NPCD,NPST, NPCN,NPCE,
OPID,OPCNE,OPPT,OPCD,OPST, OPCN,OPCE, ACT
42
43 FROM DUAL;
44 end PayCopAudit_upd_trigg;
45 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger ReceiptAudit_auditdate_trgg
2 before insert on ReceiptAudit
3 for each row
4 begin
6 end ReceiptAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger ReceiptAudit_audituser_trgg
2 before insert on ReceiptAudit
3 for each row
4 begin
6 end ReceiptAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ReceiptAudit_delt_trigg
2 AFTER DELETE ON Receipt
3 FOR EACH ROW
4 DECLARE
5 ORCIPD INTEGER;
6 OSTID INTEGER;
7 OEMPD INTEGER;
8 OCUID INTEGER;
9 OSTRID INTEGER;
10 ORPID INTEGER;
11 ORPTX DECIMAL(8,2);
12 OQT INTEGER;
13 OPID Integer;
14 ORFMT DECIMAL(8,2);

69
15 ORAMT DECIMAL(8,2);
16 ORIDT DATE;
17 ORPYID INTEGER;
18 ACT Char(20);
19
20 BEGIN
21 SELECT :OLD.RECEIPT_ID INTO ORCIPD FROM DUAL;
22 SELECT :OLD.STATE_ID INTO OSTID FROM DUAL;
23 SELECT :OLD.EMPLOYEE_ID INTO OEMPD FROM DUAL;
24 SELECT :OLD.CUSTOMER_ID INTO OCUID FROM DUAL;
25 SELECT :OLD.STORE_ID INTO OSTRID FROM DUAL;
26 SELECT :OLD.PRODUCT_ID INTO ORPID FROM DUAL;
27 SELECT :OLD.RECEIPT_TAX INTO ORPTX FROM DUAL;
28 SELECT :OLD.QTY INTO OQT FROM DUAL;
29 SELECT :OLD.RECEIPT_FINALAMT INTO ORFMT FROM DUAL;
30 SELECT :OLD.RECEIPT_AMOUNT INTO ORAMT FROM DUAL;
31 SELECT :OLD.RECEIPT_DATE INTO ORIDT FROM DUAL;
32 SELECT :OLD.Payment_ID INTO ORPYID FROM DUAL;
34
35 insert into ReceiptAudit
36 (OLD_RECEIPTID , OLD_STATEID
,OLD_EMPLOYEEID,OLD_CUSTOMERID,OLD_STOREID, OLD_PRODID , OLD_RECEPTTAX,
37 OLD_QTY, OLD_RECEIPTDATE, OLD_RECPTFINALAMT
,OLD_RECPTAMT,OLD_PAYMENTID,
38 Action_Type)
39 SELECT ORCIPD, OSTID, OEMPD, OCUID, OSTRID,ORPID,ORPTX, OQT,
ORIDT, ORFMT, ORAMT, ORPYID,ACT
40
41
42 from dual;
43
44
45 end ReceiptAudit_delt_trigg;
46 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ReceiptAudit_inst_trigg
2 AFTER insert ON Receipt
3 FOR EACH ROW
4 DECLARE
5 NRCIPD INTEGER;
6 NSTID INTEGER;
7 NEMPD INTEGER;
8 NCUID INTEGER;
9 NSTRID INTEGER;
10 NRPID INTEGER;
11 NRPTX DECIMAL(8,2);
12 NQT INTEGER;
13 NPID Integer;

70
14 NRFMT DECIMAL(8,2);
15 NRAMT DECIMAL(8,2);
16 NRIDT DATE;
17 NRPYID INTEGER;
18 ACT Char(20);
19
20 BEGIN
21 SELECT :NEW.RECEIPT_ID INTO NRCIPD FROM DUAL;
22 SELECT :NEW.STATE_ID INTO NSTID FROM DUAL;
23 SELECT :NEW.EMPLOYEE_ID INTO NEMPD FROM DUAL;
24 SELECT :NEW.CUSTOMER_ID INTO NCUID FROM DUAL;
25 SELECT :NEW.STORE_ID INTO NSTRID FROM DUAL;
26 SELECT :NEW.PRODUCT_ID INTO NRPID FROM DUAL;
27 SELECT :NEW.RECEIPT_TAX INTO NRPTX FROM DUAL;
28 SELECT :NEW.QTY INTO NQT FROM DUAL;
29 SELECT :NEW.RECEIPT_FINALAMT INTO NRFMT FROM DUAL;
30 SELECT :NEW.RECEIPT_AMOUNT INTO NRAMT FROM DUAL;
31 SELECT :NEW.RECEIPT_DATE INTO NRIDT FROM DUAL;
32 SELECT :NEW.Payment_ID INTO NRPYID FROM DUAL;
34
36 (RECEIPT_ID , STATE_ID ,EMPLOYEE_ID,CUSTOMER_ID,STORE_ID,
PRODUCT_ID , RECEIPT_TAX,
37 QTY, RECEIPT_DATE, RECEIPT_FINALAMT ,RECEIPT_AMOUNT,Payment_ID,
38 Action_Type)
39 SELECT NRCIPD, NSTID, NEMPD, NCUID, NSTRID,NRPID,NRPTX, NQT,
NRIDT, NRFMT, NRAMT, NRPYID,ACT
40
41
42 from dual;
43
44
45 end ReceiptAudit_inst_trigg;
46 /
Trigger created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER ReceiptAudit_updt_trigg
2 AFTER update ON Receipt
3 FOR EACH ROW
4 DECLARE
5 NRCIPD INTEGER;
6 NSTID INTEGER;
7 NEMPD INTEGER;
8 NCUID INTEGER;
9 NSTRID INTEGER;
10 NRPID INTEGER;
12 NQT INTEGER;
13 NPID Integer;
16 NRIDT DATE;

71
17 NRPYID INTEGER;
18 ORCIPD INTEGER;
19 OSTID INTEGER;
20 OEMPD INTEGER;
21 OCUID INTEGER;
22 OSTRID INTEGER;
23 ORPID INTEGER;
25 OQT INTEGER;
26 OPID Integer;
29 ORIDT DATE;
30 ORPYID INTEGER;
31 ACT Char(20);
32
33 BEGIN
45 SELECT :NEW.Payment_ID INTO NRPYID FROM DUAL;
57 SELECT :OLD.Payment_ID INTO ORPYID FROM DUAL;
59
61 (RECEIPT_ID , STATE_ID ,EMPLOYEE_ID,CUSTOMER_ID,STORE_ID,
PRODUCT_ID , RECEIPT_TAX,
62 QTY, RECEIPT_DATE, RECEIPT_FINALAMT ,RECEIPT_AMOUNT,Payment_ID,
63 OLD_RECEIPTID , OLD_STATEID
,OLD_EMPLOYEEID,OLD_CUSTOMERID,OLD_STOREID, OLD_PRODID , OLD_RECEPTTAX,
,OLD_RECPTAMT,OLD_PAYMENTID,
65 Action_Type)
66 SELECT NRCIPD, NSTID, NEMPD, NCUID, NSTRID,NRPID,NRPTX, NQT,
NRIDT, NRFMT, NRAMT, NRPYID,
67 ORCIPD, OSTID, OEMPD, OCUID, OSTRID,ORPID,ORPTX, OQT, ORIDT,
ORFMT, ORAMT, ORPYID,
68 ACT

72
69
70
71 from dual;
72
73
74 end ReceiptAudit_updt_trigg;
75 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger shippingcoAudit_auditdate_trgg
2 before insert on shippingcoAudit
3 for each row
4 begin
6 end shippingcoAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger shippingcoAudit_audituser_trgg
2 before insert on shippingcoAudit
3 for each row
4 begin
6 end shippingcoAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger shipcoAudit_del_trigg
2 after delete on shippingco
3 for each row
4 DECLARE
5 OSID integer;
6 OSNM Varchar2(50);
7 OSTU char(25);
8 OCRDT date;
9 ACT CHAR(20);
10 BEGIN
11 SELECT :OLD.SHIPPER_ID INTO OSID FROM DUAL;
12 SELECT :OLD.SHIPPER_NAME INTO OSNM FROM DUAL;
13 SELECT :OLD.STATUS INTO OSTU FROM DUAL;
14 SELECT :OLD.CREATE_DATE INTO OCRDT FROM DUAL;
16

73
17 INSERT INTO
shippingcoAudit(Old_shipperid,old_ShipperName,old_status,old_Create_date,A
ction_Type)
18 SELECT OSID, OSNM, OSTU, OCRDT, ACT FROM DUAL;
19 END shipcoAudit_del_trigg;
20 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger shipcoAudit_inst_trigg
2 after insert on shippingco
3 for each row
4 DECLARE
5 NSID integer;
6 NSNM Varchar2(50);
7 NSTU char(25);
8 NCRDT date;
9 ACT CHAR(20);
10 BEGIN
11 SELECT :NEW.SHIPPER_ID INTO NSID FROM DUAL;
12 SELECT :NEW.SHIPPER_NAME INTO NSNM FROM DUAL;
13 SELECT :NEW.STATUS INTO NSTU FROM DUAL;
14 SELECT :NEW.CREATE_DATE INTO NCRDT FROM DUAL;
16
17 INSERT INTO
shippingcoAudit(SHIPPER_ID,SHIPPER_NAME,STATUS,CREATE_DATE,Action_Type)
18 SELECT NSID, NSNM, NSTU, NCRDT, ACT FROM DUAL;
19 END shipcoAudit_inst_trigg;
20 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger shipcoAudit_updt_trigg
2 after update on shippingco
3 for each row
4 DECLARE
5 NSID integer;
6 NSNM Varchar2(50);
7 NSTU char(25);
8 NCRDT date;
9 OSID integer;
10 OSNM Varchar2(50);
11 OSTU char(25);
12 OCRDT date;
13 ACT CHAR(20);
14 BEGIN
15 SELECT :NEW.SHIPPER_ID INTO NSID FROM DUAL;
16 SELECT :NEW.SHIPPER_NAME INTO NSNM FROM DUAL;

74
17 SELECT :NEW.STATUS INTO NSTU FROM DUAL;
18 SELECT :NEW.CREATE_DATE INTO NCRDT FROM DUAL;
20 SELECT :OLD.SHIPPER_ID INTO OSID FROM DUAL;
21 SELECT :OLD.SHIPPER_NAME INTO OSNM FROM DUAL;
22 SELECT :OLD.STATUS INTO OSTU FROM DUAL;
23 SELECT :OLD.CREATE_DATE INTO OCRDT FROM DUAL;
24 INSERT INTO
shippingcoAudit(SHIPPER_ID,SHIPPER_NAME,STATUS,CREATE_DATE,
25 Old_shipperid,old_ShipperName,old_status,old_Create_date,
26 Action_Type)
27 SELECT NSID, NSNM, NSTU, NCRDT, OSID, OSNM, OSTU, OCRDT,
28 ACT FROM DUAL;
29 END shipcoAudit_updt_trigg;
30 /
Trigger created.
SQL>
SQL> create or replace trigger shippmentAudit_auditdate_trgg
2 before insert on shipmentaudit
3 for each row
4 begin
6 end shippmentAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger shippmentAudit_audituser_trgg
3 for each row
4 begin
6 end shippmentAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger shippmentAudit_del_trigg
2 after delete on shipment
3 for each row
4 DECLARE
5
6 OLSHIPID NUMBER(38);
7 OSHIPPERID NUMBER(38);

75
8 OSTID NUMBER(38);
9 ORPID NUMBER(38);
10 OSHIPDATE DATE;
11 OSHIPENDDATE DATE;
12 ORVEDATE DATE;
13 ONAME VARCHAR2(55);
14 OADSS VARCHAR2(55) ;
15 OCIT VARCHAR2(55) ;
16 OLDSHIPPINGAMT NUMBER(38);
17 OSHIPPINGTYPE CHAR(35) ;
18 ACT CHAR(20);
19
20 begin
21
22 SELECT :OLD.SHIPPING_ID INTO OLSHIPID FROM DUAL;
23 SELECT :OLD.SHIPPER_ID INTO OSHIPPERID FROM DUAL;
25 SELECT :OLD.RECEIPT_ID INTO ORPID FROM DUAL;
26 SELECT :OLD.SHIP_DATE INTO OSHIPDATE FROM DUAL;
27 SELECT :OLD.SHIP_ENDDATE INTO OSHIPENDDATE FROM DUAL;
28 SELECT :OLD.RECEIVE_DATE INTO ORVEDATE FROM DUAL;
29 SELECT :OLD.NAME INTO ONAME FROM DUAL;
30 SELECT :OLD.ADDRESS INTO OADSS FROM DUAL;
31 SELECT :OLD.CITY INTO OCIT FROM DUAL;
32 SELECT :OLD.SHIPPING_AMOUNT INTO OLDSHIPPINGAMT FROM DUAL;
33 SELECT :OLD.SHIPPING_TYPE INTO OSHIPPINGTYPE FROM DUAL;
34 SELECT 'Delete' INTO ACT From Dual;
35
36
37 insert into shipmentaudit
38 (old_shippingid,old_shipperid, old_stateid, old_ReceiptID,
old_shipdate ,old_shipenddate, old_receivedate,
39 old_Name, old_Address, old_City,
old_shippingamt,old_shippingtype,Action_Type)
40 select OLSHIPID, OSHIPPERID, OSTID, ORPID, OSHIPDATE,
OSHIPENDDATE,ORVEDATE,ONAME, OADSS, OCIT,OLDSHIPPINGAMT,
41 OSHIPPINGTYPE, ACT
42 from dual;
43 end shippmentAudit_del_trigg;
44 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger shippmentAudit_inst_trigg
2 after insert on shipment
3 for each row
4 DECLARE
5
6 NLSHIPID NUMBER(38);
7 NSHIPPERID NUMBER(38);
8 NSTID NUMBER(38);

76
9 NRPID NUMBER(38);
10 NSHIPDATE DATE;
11 NSHIPENDDATE DATE;
12 NRVEDATE DATE;
13 NNAME VARCHAR2(55);
14 NADSS VARCHAR2(55) ;
15 NCIT VARCHAR2(55) ;
16 NLDSHIPPINGAMT NUMBER(38);
17 NSHIPPINGTYPE CHAR(35) ;
18 ACT CHAR(20);
19
20 begin
21
22 SELECT :NEW.SHIPPING_ID INTO NLSHIPID FROM DUAL;
23 SELECT :NEW.SHIPPER_ID INTO NSHIPPERID FROM DUAL;
25 SELECT :NEW.RECEIPT_ID INTO NRPID FROM DUAL;
26 SELECT :NEW.SHIP_DATE INTO NSHIPDATE FROM DUAL;
27 SELECT :NEW.SHIP_ENDDATE INTO NSHIPENDDATE FROM DUAL;
28 SELECT :NEW.RECEIVE_DATE INTO NRVEDATE FROM DUAL;
29 SELECT :NEW.NAME INTO NNAME FROM DUAL;
30 SELECT :NEW.ADDRESS INTO NADSS FROM DUAL;
31 SELECT :NEW.CITY INTO NCIT FROM DUAL;
32 SELECT :NEW.SHIPPING_AMOUNT INTO NLDSHIPPINGAMT FROM DUAL;
33 SELECT :NEW.SHIPPING_TYPE INTO NSHIPPINGTYPE FROM DUAL;
34 SELECT 'Insert' INTO ACT From Dual;
35
36
38 (SHIPPING_ID,SHIPPER_ID, STATE_ID, RECEIPT_ID, SHIP_DATE
,SHIP_ENDDATE, RECEIVE_DATE,
39 NAME, ADDRESS, CITY, SHIPPING_AMOUNT,SHIPPING_TYPE,Action_Type)
40 select NLSHIPID, NSHIPPERID, NSTID, NRPID, NSHIPDATE,
NSHIPENDDATE,NRVEDATE,NNAME, NADSS, NCIT,NLDSHIPPINGAMT,
41 NSHIPPINGTYPE, ACT
42 from dual;
43 end shippmentAudit_inst_trigg;
44 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger shippmentAudit_upt_trigg
3 for each row
4 DECLARE
5
8 NSTID NUMBER(38);

77
9 NRPID NUMBER(38);
10 NSHIPDATE DATE;
12 NRVEDATE DATE;
20 OSTID NUMBER(38);
21 ORPID NUMBER(38);
22 OSHIPDATE DATE;
24 ORVEDATE DATE;
30 ACT CHAR(20);
31
32 begin
33
46
59
60
61
62 SELECT 'Update' INTO ACT From Dual;
63
64

78
67 NAME, ADDRESS, CITY,
SHIPPING_AMOUNT,SHIPPING_TYPE,old_shippingid,old_shipperid, old_stateid,
old_ReceiptID,
68 old_shipdate ,old_shipenddate, old_receivedate,
69 old_Name, old_Address, old_City, old_shippingamt,old_shippingtype,
70 Action_Type)
72 NSHIPPINGTYPE,OLSHIPID, OSHIPPERID, OSTID, ORPID, OSHIPDATE,
OSHIPENDDATE,ORVEDATE,ONAME, OADSS,
73 OCIT,OLDSHIPPINGAMT,OSHIPPINGTYPE,ACT
74 from dual;
75 end shippmentAudit_upt_trigg;
76 /
Trigger created.
SQL> set echo off
SQL> spool off;

85
SQL> Alter table receipt
2 drop primary key;
Table altered.
2 drop constraint Receipt_Receiptid_uk cascade;
Table altered.
SQL> Alter table receipt drop column state_id;
Table altered.
SQL> Alter table receipt drop column payment_id;
Table altered.
SQL> Alter table receipt drop constraint Receipt_customerid_fk ;
Table altered.
SQL> Alter table receipt drop constraint Receipt_storeid_fk ;
Table altered.
SQL> Alter table receipt drop constraint Receipt_Productid_fk ;
Table altered.
SQL>
SQL>
SQL>
SQL>
2 modify receipt_finalamt constraint receipt_repfinamt_nn not null
3 modify receipt_amount constraint receipt_repamount_nn not null;
Table altered.
SQL>
SQL>
SQL>
2 add constraint Receipt_Receipid_pk primary
key(Receipt_ID,Employee_ID,Customer_ID,Store_ID
3 ,Product_ID);
Table altered.

86
2 drop constraint Receipt_Receipid_pk;
Table altered.
SQL> alter table receipt
2 add constraint receipt_receiptid_pk primary key(receipt_id,
employee_id, customer_id, product_id, store_id);
Table altered.
SQL>
2 add constraint receipt_receiptid_uk unique (receipt_id);
Table altered.
SQL>
SQL>
SQL>
2 add constraint Receipt_customerid_fk foreign key(Customer_ID)
references Customer(Customer_ID);
Table altered.
2 drop constraint Receipt_Employeeid_fk;
Table altered.
2 add constraint Receipt_Employeeid_fk foreign key(Employee_ID)
references Employee (Employee_ID);
Table altered.
2 add constraint Receipt_storeid_fk foreign key(Store_id) references
store(Store_id);
Table altered.
SQL>
2 add constraint Receipt_Productid_fk foreign key(Product_ID)
references Product (Product_ID);
Table altered.
SQL>
SQL> alter table receipt drop constraint receipt_repfinamt_nn;
Table altered.

87
SQL> alter table receipt drop constraint receipt_ReceiptTax_nn;
Table altered.
SQL> commit;
Commit complete.
SQL> alter table receiptaudit drop column state_id;
Table altered.
SQL> alter table receiptaudit drop column old_stateid;
Table altered.
SQL> alter table receiptaudit drop column old_paymentid;
Table altered.
SQL> alter table receiptaudit drop column payment_id;
Table altered.
SQL> ALTER TABLE PAYMENTCOP
2 ADD RECEIPT_ID INTEGER;
Table altered.
2 ADD Authorized_Amt decimal(8,2);
Table altered.
2 modify payment_createddate constraint paymentcop_createdate_nn not
null
3 modify payment_status constraint paymentcop_paymentstatus_nn not null
4 modify payment_expdate constraint paymentcop_payexpdate_nn not null;
Table altered.
SQL> Alter table PAYMENTCOP
2 drop constraint PaymentCop_paymentid_pk;
Table altered.
2 ADD CONSTRAINT PAYMENTCOP_PAYMENTID_PK PRIMARY KEY(PAYMENT_ID,
RECEIPT_ID);

88
Table altered.
2 ADD CONSTRAINT PAYMENTCOP_PAYMENTID_UK UNIQUE(PAYMENT_ID);
Table altered.
2 ADD CONSTRAINT PAYMENTCOP_RECEIPTID_FK FOREIGN KEY(RECEIPT_ID)
REFERENCES RECEIPT(RECEIPT_ID);
Table altered.
2 modify Authorized_Amt constraint paymentcop_authorizedamt_nn not
null;
Table altered.
SQL> alter table paymentcopaudit add receipt_id integer
2 ;
Table altered.
SQL> alter table paymentcopaudit add old_receiptid integer;
Table altered.
SQL> alter table paymentcopaudit add authorized_amt decimal(8,2);
Table altered.
SQL> alter table paymentcopaudit add Old_AuthorizedAmt decimal(8,2);
Table altered.
SQL> commit;
Commit complete.
SQL> drop table shipment;
SQL> create table shipment
2 (shipping_id integer GENERATED ALWAYS AS IDENTITY
3 (START WITH 0235
INCREMENT BY 26),
4 shipper_id integer ,
5 state_id integer ,
6 Receipt_ID integer ,
7 ship_date date,

89
15 constraint shipment_shipmentid_pk primary
16 constraint shipment_shipermid_uk unique (shipping_id),
17 constraint shipment_shipperid_fk foreign key(shipper_id)
references shippingco(shipper_id),
18 CONSTRAINT SHIPMENT_RECEIPTID_FK FOREIGN KEY(RECEIPT_ID)
REFERENCES RECEIPT(RECEIPT_ID)
19 );
Table created.
SQL>
SQL> alter table shipment
2 modify Name constraint shipment_shipname_nn not null
3 modify Address constraint shipment_address_nn not null
4 modify city constraint shipment_city_nn not null
5 modify shipping_amount constraint shipment_shipamount_nn not null
6 modify shipping_type constraint shipment_shippingtype_nn not null;
Table altered.
SQL> spool off;
SQL> CREATE OR REPLACE TRIGGER ManagementAuditt_del_trigg
2 AFTER DELETE ON Management
3 for each row
4 DECLARE
5 OMID Integer;
6 OEMP Integer;
7 OHD DATE;
8 OEND DATE;
9 OSTID integer;
10 ACT char(20);
11 BEGIN
12
18 SELECT :OLD.STORE_ID INTO OSTID FROM DUAL;
19
20 INSERT INTO ManagementAudit(Old_ManagerID,Old_EmployeeID
,Old_HireDate, Old_HireEndDate ,Action_Type, old_storeid)
21 SELECT OMID, OEMP, OHD,OEND,ACT, OSTID
22 FROM DUAL;
23
24
25 end ManagementAuditt_del_trigg;
26 /

90
Trigger created.
SQL> CREATE OR REPLACE TRIGGER ManagementAuditt_inst_trigg
2 AFTER INSERT ON Management
3 for each row
4 DECLARE
5 NMID Integer;
6 NEMP Integer;
7 NHD DATE;
8 NEND DATE;
9 ACT char(20);
10 NTSID Integer;
11 BEGIN
12
18 SELECT :NEW.STORE_ID INTO NTSID from DUAL;
19
Hire_EndDate ,Action_Type, Store_ID)
21 SELECT NMID, NEMP, NHD,NEND,ACT, NTSID FROM DUAL;
22
23
24 end ManagementAuditt_inst_trigg;
25 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER ManagementAuditt_updt_trigg
2 AFTER UPDATE ON Management
3 for each row
4 DECLARE
5 NMID Integer;
6 NEMP Integer;
7 NHD DATE;
8 NEND DATE;
9 OMID Integer;
10 OEMP Integer;
11 OHD DATE;
12 OEND DATE;
13 ACT char(20);
14 OSTID integer;
15 NTSID Integer;
16 BEGIN
17

91
27 SELECT :OLD.STORE_ID INTO OSTID FROM DUAL;
28 SELECT :NEW.STORE_ID INTO NTSID from DUAL;
29
Hire_EndDate ,Old_ManagerID,Old_EmployeeID ,Old_HireDate, Old_HireEndDate,
31 Action_Type, Store_ID, old_storeid )
32 SELECT NMID, NEMP, NHD,NEND,OMID, OEMP, OHD,OEND,ACT ,
NTSID, OSTID
33 FROM DUAL;
34
35 end ManagementAuditt_updt_trigg;
36
37 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER storeAudit_del_trigg
2 AFTER DELETE ON STORE
3 FOR EACH ROW
4 DECLARE
5 OSTID Integer;
6 OSSTID Integer;
7
14 ACT char(20);
15 BEGIN
25
26
27
28 insert into
storeAudit(Old_StoreID,Old_StorStateID,Old_StorName,Old_StorType,Old_StorA
ddress,Old_StorCity,old_StorPhone,old_StorStatus, Action_Type)
29 select OSTID, OSSTID, OSTN, OSTY,OSTA,OSTC,OSP,OSTS, ACT FROM
DUAL;
31 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER storeAudit_inst_trigg
2 AFTER INSERT ON STORE

92
3 FOR EACH ROW
4 DECLARE
5 NSTID Integer;
6 NSSTID Integer;
13 ACT char(20);
14 BEGIN
24
25
26
27 insert into
storeAudit(Store_ID,Store_StateID,Store_Name,Store_Type,Store_Address,Stor
e_City,Store_Phone,Store_Status, Action_Type)
28 select NSTID, NSSTID, NSTN, NSTY,NSTA,NSTC,NSP,NSTS, ACT FROM
DUAL;
30 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER storeAudit_upd_trigg
2 AFTER update ON STORE
3 FOR EACH ROW
4 DECLARE
5 NSTID Integer;
6 NSSTID Integer;
13 OSTID Integer;
14 OSSTID Integer;
21 ACT char(20);
22 BEGIN

93
40
41 insert into
storeAudit(Store_ID,Store_StateID,Store_Name,Store_Type,Store_Address,Stor
e_City,Store_Phone,Store_Status,
42
Old_StoreID,Old_StorStateID,Old_StorName,Old_StorType,Old_StorAddress,Old_
StorCity,old_StorPhone,old_StorStatus,
43 Action_Type)
44 select NSTID, NSSTID, NSTN, NSTY,NSTA,NSTC,NSP,NSTS, OSTID,
OSSTID, OSTN, OSTY,OSTA,OSTC,OSP,OSTS, ACT FROM DUAL;
45 end storeAudit_upd_trigg;
46 /
Trigger created.
SQL> commit;
Commit complete.
SQL> set echo off;
SQL> spool off;
create or replace trigger PaymentCopAudit_auditdate_trgg
3 for each row
4 begin
6 end PaymentCopAudit_auditdate_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL> create or replace trigger PaymentCopAudit_audituser_trgg
3 for each row
4 begin
6 end PaymentCopAudit_audituser_trgg;

94
7 /
Trigger created.
SQL> create or replace TRIGGER PayCopAudit_del_trigg
2 AFTER DELETE ON PaymentCop
3 FOR EACH ROW
4 DECLARE
5 OPID Integer;
8 OPCD Date;
10 OPCN Integer;
11 OPCE Date;
12 ACT Char(20);
13 ORID INTEGER;
14 OAUT DECIMAL (8,2);
15
16
17 BEGIN
25 SELECT :OLD.Receipt_id INTO ORID FROM DUAL;
26 SELECT :OLD.Authorized_Amount INTO OAUT FROM DUAL;
27
29
30 INSERT INTO
PaymentCopAudit(Old_Payid,Old_PayCopName,old_PayType,Old_PayCreatedDate,Ol
d_PayStatus,Old_PayCard_Number,OLD_PCARD_EXPDATE,
OLD_RECEIPTID,OLD_AUTHAMT, ACTION_TYPE
31 )
32 SELECT OPID,OPCNE,OPPT,OPCD,OPST, OPCN,OPCE, ORID, OAUT,ACT
33
34
35
36 FROM DUAL;
37
38 end PayCopAudit_del_trigg;
39 /
Trigger created.
SQL>
SQL> create or replace TRIGGER PayCopAudit_updt_trigg
2 AFTER update ON PaymentCop
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;

95
8 NPCD Date;
10 NPCN Integer;
11 NPCE Date;
12 ACT Char(20);
13 NRID INTEGER;
14 NAUT DECIMAL (8,2);
15 OPID Integer;
18 OPCD Date;
20 OPCN Integer;
21 OPCE Date;
22 ORID INTEGER;
23 OAUT DECIMAL (8,2);
24
25 BEGIN
33 SELECT :NEW.Receipt_id INTO NRID FROM DUAL;
34 SELECT :NEW.Authorized_Amount INTO NAUT FROM DUAL;
42 SELECT :OLD.Receipt_id INTO ORID FROM DUAL;
43 SELECT :OLD.Authorized_Amount INTO OAUT FROM DUAL;
44 SELECT 'update' INTO ACT From DUAL;
45
46 INSERT INTO
PaymentCopAudit(PAYMENT_ID,PAYMENT_COPNAME,PAYMENT_TYPE,PAYMENT_CREATEDDAT
E,PAYMENT_STATUS,PAYMENTCARD_NUMBER,PAYMENT_EXPDATE,
Receipt_id,Authorized_AmT, ACTION_TYPE,
47
Old_Payid,Old_PayCopName,old_PayType,Old_PayCreatedDate,Old_PayStatus,Old_
PayCard_Number,OLD_PCARD_EXPDATE, OLD_RECEIPTID,OLD_AUTHAMT
48
49 )
50 SELECT NPID,NPCNE,NPPT,NPCD,NPST, NPCN,NPCE, NRID, NAUT,ACT,
OPID,OPCNE,OPPT,OPCD,OPST, OPCN,OPCE, ORID, OAUT
51
52
53
54 FROM DUAL;
55
56 end PayCopAudit_updt_trigg;
57 /

96
Trigger created.
SQL>
SQL> create or replace TRIGGER PayCopAudit_inst_trigg
2 AFTER INSERT ON PaymentCop
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
8 NPCD Date;
10 NPCN Integer;
11 NPCE Date;
12 ACT Char(20);
13 NRID INTEGER;
14 NAUT DECIMAL (8,2);
15
16
17 BEGIN
25 SELECT :NEW.Receipt_id INTO NRID FROM DUAL;
26 SELECT :NEW.Authorized_Amount INTO NAUT FROM DUAL;
27
29
30 INSERT INTO
PaymentCopAudit(PAYMENT_ID,PAYMENT_COPNAME,PAYMENT_TYPE,PAYMENT_CREATEDDAT
E,PAYMENT_STATUS,PAYMENTCARD_NUMBER,PAYMENT_EXPDATE,
Receipt_id,Authorized_AmT, ACTION_TYPE
31 )
32 SELECT NPID,NPCNE,NPPT,NPCD,NPST, NPCN,NPCE, NRID, NAUT,ACT
33
34
35
36 FROM DUAL;
37
38 end PayCopAudit_inst_trigg;
39 /
Trigger created.
SQL>
SQL> create or replace trigger shippmentAudit_auditdate_trgg
3 for each row
4 begin
6 end shippmentAudit_auditdate_trgg;
7 /

97
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger shippmentAudit_audituser_trgg
3 for each row
4 begin
6 end shippmentAudit_audituser_trgg;
7 /
Trigger created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger shippmentAudit_del_trigg
2 after delete on shipment
3 for each row
4 DECLARE
5
8 OSTID NUMBER(38);
9 ORPID NUMBER(38);
10 OSHIPDATE DATE;
12 ORVEDATE DATE;
18 ACT CHAR(20);
19
20 begin
21
34 SELECT 'Delete' INTO ACT From Dual;
35
36

98
38 (old_shippingid,old_shipperid, old_stateid, old_ReceiptID,
old_shipdate ,old_shipenddate, old_receivedate,
39 old_Name, old_Address, old_City,
old_shippingamt,old_shippingtype,Action_Type)
40 select OLSHIPID, OSHIPPERID, OSTID, ORPID, OSHIPDATE,
OSHIPENDDATE,ORVEDATE,ONAME, OADSS, OCIT,OLDSHIPPINGAMT,
41 OSHIPPINGTYPE, ACT
42 from dual;
43 end shippmentAudit_del_trigg;
44 /
Trigger created.
SQL>
SQL> create or replace trigger shippmentAudit_inst_trigg
3 for each row
4 DECLARE
5
8 NSTID NUMBER(38);
9 NRPID NUMBER(38);
10 NSHIPDATE DATE;
12 NRVEDATE DATE;
18 ACT CHAR(20);
19
20 begin
21
34 SELECT 'Insert' INTO ACT From Dual;
35
36
39 NAME, ADDRESS, CITY, SHIPPING_AMOUNT,SHIPPING_TYPE,Action_Type)
41 NSHIPPINGTYPE, ACT

99
42 from dual;
43 end shippmentAudit_inst_trigg;
44 /
Trigger created.
SQL>
SQL> create or replace trigger shippmentAudit_upt_trigg
2 after UPDATE on shipment
3 for each row
4 DECLARE
5
8 NSTID NUMBER(38);
9 NRPID NUMBER(38);
10 NSHIPDATE DATE;
12 NRVEDATE DATE;
20 OSTID NUMBER(38);
21 ORPID NUMBER(38);
22 OSHIPDATE DATE;
24 ORVEDATE DATE;
30 ACT CHAR(20);
31
32 begin
33
46

100
59
60
61
62 SELECT 'Update' INTO ACT From Dual;
63
64
67 NAME, ADDRESS, CITY,
SHIPPING_AMOUNT,SHIPPING_TYPE,old_shippingid,old_shipperid, old_stateid,
old_ReceiptID,
68 old_shipdate ,old_shipenddate, old_receivedate,
69 old_Name, old_Address, old_City, old_shippingamt,old_shippingtype,
70 Action_Type)
72 NSHIPPINGTYPE,OLSHIPID, OSHIPPERID, OSTID, ORPID, OSHIPDATE,
OSHIPENDDATE,ORVEDATE,ONAME, OADSS,
73 OCIT,OLDSHIPPINGAMT,OSHIPPINGTYPE,ACT
74 from dual;
75 end shippmentAudit_upt_trigg;
76 /
Trigger created.
SQL> create or replace TRIGGER ReceiptAudit_delt_trigg
2 AFTER DELETE ON Receipt
3 FOR EACH ROW
4 DECLARE
5 ORCIPD INTEGER;
6 OEMPD INTEGER;
7 OCUID INTEGER;
8 OSTRID INTEGER;
9 ORPID INTEGER;
11 OQT INTEGER;
12 OPID Integer;
15 ORIDT DATE;
16 ACT Char(20);
17
18 BEGIN

101
30
32 (OLD_RECEIPTID ,OLD_EMPLOYEEID,OLD_CUSTOMERID,OLD_STOREID,
OLD_PRODID , OLD_RECEPTTAX,
33 OLD_QTY, OLD_RECEIPTDATE, OLD_RECPTFINALAMT ,OLD_RECPTAMT,
34 Action_Type)
35 SELECT ORCIPD, OEMPD, OCUID, OSTRID,ORPID,ORPTX, OQT, ORIDT,
ORFMT, ORAMT,ACT
36
37
38 from dual;
39
40
41 end ReceiptAudit_delt_trigg;
42
43 /
Trigger created.
SQL> create or replace TRIGGER ReceiptAudit_inst_trigg
2 AFTER insert ON Receipt
3 FOR EACH ROW
4 DECLARE
5 NRCIPD INTEGER;
6 NEMPD INTEGER;
7 NCUID INTEGER;
8 NSTRID INTEGER;
9 NRPID INTEGER;
11 NQT INTEGER;
12 NPID Integer;
15 NRIDT DATE;
16 ACT Char(20);
17
18 BEGIN
19
20
32

102
34 (RECEIPT_ID ,EMPLOYEE_ID,CUSTOMER_ID,STORE_ID, PRODUCT_ID ,
RECEIPT_TAX,
35 QTY, RECEIPT_DATE, RECEIPT_FINALAMT ,RECEIPT_AMOUNT,
36 Action_Type)
37 SELECT NRCIPD, NEMPD, NCUID, NSTRID,NRPID,NRPTX, NQT, NRIDT,
NRFMT, NRAMT,ACT
38
39
40 from dual;
41
42 end ReceiptAudit_inst_trigg;
43
44 /
Trigger created.
SQL> create or replace TRIGGER ReceiptAudit_updt_trigg
2 AFTER UPDATE ON Receipt
3 FOR EACH ROW
4 DECLARE
5 ORCIPD INTEGER;
6 OEMPD INTEGER;
7 OCUID INTEGER;
8 OSTRID INTEGER;
9 ORPID INTEGER;
11 OQT INTEGER;
12 OPID Integer;
15 ORIDT DATE;
16
17 NRCIPD INTEGER;
18 NEMPD INTEGER;
19 NCUID INTEGER;
20 NSTRID INTEGER;
21 NRPID INTEGER;
23 NQT INTEGER;
24 NPID Integer;
27 NRIDT DATE;
28
29
30 ACT Char(20);
31
32 BEGIN

103
43
54
56
58 (OLD_RECEIPTID ,OLD_EMPLOYEEID,OLD_CUSTOMERID,OLD_STOREID,
OLD_PRODID , OLD_RECEPTTAX,
,OLD_RECPTAMT,RECEIPT_ID ,EMPLOYEE_ID,CUSTOMER_ID,STORE_ID, PRODUCT_ID ,
RECEIPT_TAX,
60 QTY, RECEIPT_DATE, RECEIPT_FINALAMT ,RECEIPT_AMOUNT,
61 Action_Type)
62 SELECT ORCIPD, OEMPD, OCUID, OSTRID,ORPID,ORPTX, OQT, ORIDT,
ORFMT, ORAMT,NRCIPD, NEMPD, NCUID, NSTRID,NRPID,NRPTX, NQT, NRIDT, NRFMT,
NRAMT,
63 ACT
64
65
66 from dual;
67
68
69
70
71
72 end ReceiptAudit_updt_trigg;
73 /
Trigger created.
SQL>
SQL>
SQL>
SQL> SET ECHO OFF
SQL> SPOOL OFF

104
SQL> Create table HRRecords
2 (Employee_ID Integer ,
3 HR_ID Integer GENERATED ALWAYS AS IDENTITY (START WITH 0323
INCREMENT BY 20) ,
4 Role_ID Integer ,
7 Social_SecurityNum Integer ,
8 Manager_id integer,
9 Hire_Date Date ,
10 End_Date Date,
11 PayRate numeric(15,2) ,
key(Employee_ID,HR_ID,Role_ID,Store_ID, Manager_id),
Employee(Employee_ID) ,
HRRoles(Role_ID)
17 );
Table created.
SQL> Alter table HRRecords
2 add constraint HrRecord_storeid_fk foreign key(Store_ID) references
store(store_id);
Table altered.
SQL> alter table hrrecordsaudit
2 add manager_id integer;
Table altered.
SQL> alter table hrrecordsaudit
2 add old_managerid integer;
Table altered.
SQL> create index hrrecord_Snn_idx on hrrecords (Social_SecurityNum);
Index created.
SQL> create index hrrecord_hiredate_idx on hrrecords (hire_date)
2 ;
Index created.
SQL> set echo off
SQL> spool off

107
Database Directory
SQL>
SQL> desc countrycode;
Name Null? Type
----------------------------------------- -------- ----------------------
------
COUNTRY_CODE NOT NULL NUMBER(38)
COUNTRY_NAME NOT NULL VARCHAR2(60)
COUNTRY_ABBRV NOT NULL VARCHAR2(12)
SQL> desc countrycodesaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
COUNTRY_CODE NUMBER(38)
COUNTRY_NAME VARCHAR2(60)
COUNTRY_ABBRV VARCHAR2(12)
AUDIT_DATE TIMESTAMP(6)
AUDIT_USER VARCHAR2(50)
OLD_COUNTRYCODE NUMBER(38)
OLD_COUNTRYNAME VARCHAR2(60)
OLD_COUNTRYABBRV VARCHAR2(12)
AUDIT_ID NOT NULL NUMBER(38)
ACTION_TYPE CHAR(20)
SQL> desc statecodes;
Name Null? Type
----------------------------------------- -------- ----------------------
------
STATE_ID NOT NULL NUMBER(38)
COUNTRY_CODE NOT NULL NUMBER(38)
STATE_NAME NOT NULL VARCHAR2(110)
STATE_ABBRV NOT NULL VARCHAR2(10)
STATE_TAXRATE NOT NULL NUMBER(8,2)
SQL> desc statecodesaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
STATE_ID NUMBER(38)
COUNTRY_CODE NUMBER(38)
STATE_NAME VARCHAR2(110)
STATE_ABBRV VARCHAR2(10)
STATE_TAXRATE NUMBER(8,2)
OLD_STATE_ID NUMBER(38)
OLD_COUNTRY_CODE NUMBER(38)
OLD_STATE_NAME VARCHAR2(110)
OLD_STATE_ABBRV VARCHAR2(10)
OLD_STATE_TAXRATE NUMBER(8,2)
SQL> desc store;
Name Null? Type

108
----------------------------------------- -------- ----------------------
------
STORE_ID NOT NULL NUMBER(38)
STORE_STATEID NOT NULL NUMBER(38)
STORE_NAME NOT NULL VARCHAR2(65)
STORE_TYPE NOT NULL VARCHAR2(34)
STORE_ADDRESS NOT NULL VARCHAR2(65)
STORE_CITY NOT NULL VARCHAR2(45)
STORE_PHONE NOT NULL VARCHAR2(45)
STORE_STATUS NOT NULL VARCHAR2(30)
SQL> desc storeaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
STORE_ID NUMBER(38)
STORE_STATEID NUMBER(38)
STORE_NAME VARCHAR2(65)
STORE_TYPE VARCHAR2(34)
STORE_ADDRESS VARCHAR2(65)
STORE_CITY VARCHAR2(45)
STORE_PHONE VARCHAR2(45)
STORE_STATUS VARCHAR2(30)
OLD_STOREID NUMBER(38)
OLD_STORSTATEID NUMBER(38)
OLD_STORNAME VARCHAR2(65)
OLD_STORTYPE VARCHAR2(34)
OLD_STORADDRESS VARCHAR2(65)
OLD_STORCITY VARCHAR2(45)
OLD_STORPHONE VARCHAR2(45)
OLD_STORSTATUS VARCHAR2(30)
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------
------
EMPLOYEE_ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(55)
MIDDLE_NAME VARCHAR2(55)
LAST_NAME NOT NULL VARCHAR2(55)
ADDRESS NOT NULL VARCHAR2(55)
CITY NOT NULL VARCHAR2(45)
PHONE NOT NULL VARCHAR2(45)
SQL> desc employeeaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
EMPLOYEE_ID NUMBER(38)
FIRST_NAME VARCHAR2(55)
MIDDLE_NAME VARCHAR2(55)
LAST_NAME VARCHAR2(55)

109
ADDRESS VARCHAR2(55)
CITY VARCHAR2(45)
STATE_ID NUMBER(38)
PHONE VARCHAR2(45)
OLD_EMPLOYEE_ID NUMBER(38)
OLD_FIRST_NAME VARCHAR2(55)
OLD_MIDDLE_NAME VARCHAR2(55)
OLD_LAST_NAME VARCHAR2(55)
OLD_ADDRESS VARCHAR2(55)
OLD_CITY VARCHAR2(45)
OLD_STATE_ID NUMBER(38)
OLD_PHONE VARCHAR2(45)
SQL> desc management;
Name Null? Type
----------------------------------------- -------- ----------------------
------
MANAGER_ID NOT NULL NUMBER(38)
HIRE_DATE NOT NULL DATE
HIRE_ENDDATE DATE
SQL> desc managementaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
MANAGER_ID NUMBER(38)
HIRE_DATE DATE
HIRE_ENDDATE DATE
OLD_MANAGERID NUMBER(38)
OLD_EMPLOYEEID NUMBER(38)
OLD_HIREDATE DATE
OLD_HIREENDDATE DATE
STORE_ID NUMBER(38)
SQL> desc hrroles;
Name Null? Type
----------------------------------------- -------- ----------------------
------
ROLE_ID NOT NULL NUMBER(38)
ROLE_TITLE NOT NULL VARCHAR2(45)
ROLE_STATUS NOT NULL VARCHAR2(45)
SQL> desc hrrolesaudit;
Name Null? Type

110
----------------------------------------- -------- ----------------------
------
ROLE_ID NUMBER(38)
ROLE_TITLE VARCHAR2(45)
ROLE_STATUS VARCHAR2(45)
OLD_ROLEID NUMBER(38)
OLD_ROLETITLE VARCHAR2(45)
OLD_ROLE_STATUS VARCHAR2(45)
SQL> desc hrrecords;
Name Null? Type
----------------------------------------- -------- ----------------------
------
HR_ID NOT NULL NUMBER(38)
ROLE_ID NOT NULL NUMBER(38)
DOB NOT NULL DATE
SOCIAL_SECURITYNUM NUMBER(38)
MANAGER_ID NOT NULL NUMBER(38)
HIRE_DATE DATE
END_DATE DATE
PAYRATE NUMBER(15,2)
EMPLOYEMENT_STATUS VARCHAR2(45)
SQL> desc hrrecordsaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
HR_ID NUMBER(38)
ROLE_ID NUMBER(38)
STORE_ID NUMBER(38)
DOB DATE
SOCIAL_SECURITYNUM NUMBER(38)
HIRE_DATE DATE
END_DATE DATE
PAYRATE NUMBER(15,2)
EMPLOYEMENT_STATUS VARCHAR2(45)
OLD_HRID NUMBER(38)
OLD_ROLEID NUMBER(38)
OLD_DOB DATE
OLD_SOCIALSECURITYNUM NUMBER(38)
OLD_HIREDATE DATE
OLD_ENDDATE DATE
OLD_PAYRATE NUMBER(15,2)
OLD_EMPLOYEMENTSTATUS VARCHAR2(45)

111
MANAGER_ID NUMBER(38)
OLD_MANAGERID NUMBER(38)
SQL> desc shipment;
Name Null? Type
----------------------------------------- -------- ----------------------
------
SHIPPING_ID NOT NULL NUMBER(38)
SHIPPER_ID NOT NULL NUMBER(38)
RECEIPT_ID NOT NULL NUMBER(38)
SHIP_DATE DATE
SHIP_ENDDATE DATE
RECEIVE_DATE DATE
NAME NOT NULL VARCHAR2(55)
ADDRESS NOT NULL VARCHAR2(55)
CITY NOT NULL VARCHAR2(55)
SHIPPING_AMOUNT NOT NULL NUMBER(38)
SHIPPING_TYPE NOT NULL CHAR(35)
SQL> desc shipmentaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
SHIPPING_ID NUMBER(38)
SHIPPER_ID NUMBER(38)
STATE_ID NUMBER(38)
RECEIPT_ID NUMBER(38)
SHIP_DATE DATE
SHIP_ENDDATE DATE
RECEIVE_DATE DATE
NAME VARCHAR2(55)
ADDRESS VARCHAR2(55)
CITY VARCHAR2(55)
SHIPPING_AMOUNT NUMBER
SHIPPING_TYPE CHAR(35)
OLD_SHIPPINGID NUMBER(38)
OLD_SHIPPERID NUMBER(38)
OLD_STATEID NUMBER(38)
OLD_RECEIPTID NUMBER(38)
OLD_SHIPDATE DATE
OLD_SHIPENDDATE DATE
OLD_RECEIVEDATE DATE
OLD_NAME VARCHAR2(55)
OLD_ADDRESS VARCHAR2(55)
OLD_CITY VARCHAR2(55)
OLD_SHIPPINGAMT NUMBER
OLD_SHIPPINGTYPE CHAR(35)
SQL> desc paymentcop;
Name Null? Type
----------------------------------------- -------- ----------------------
------

112
PAYMENT_ID NOT NULL NUMBER(38)
PAYMENT_COPNAME NOT NULL VARCHAR2(50)
PAYMENT_TYPE NOT NULL VARCHAR2(45)
PAYMENT_CREATEDDATE DATE
PAYMENT_STATUS VARCHAR2(25)
PAYMENTCARD_NUMBER NOT NULL NUMBER(38)
PAYMENT_EXPDATE DATE
AUTHORIZED_AMOUNT NUMBER(8,2)
SQL> desc paymentcopaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
PAYMENT_ID NUMBER(38)
PAYMENT_COPNAME VARCHAR2(50)
PAYMENT_TYPE VARCHAR2(45)
PAYMENT_STATUS VARCHAR2(25)
PAYMENTCARD_NUMBER NUMBER(38)
OLD_PAYID NUMBER(38)
OLD_PAYCOPNAME VARCHAR2(50)
OLD_PAYTYPE VARCHAR2(45)
OLD_PAYCREATEDDATE DATE
OLD_PAYSTATUS VARCHAR2(25)
OLD_PAYCARD_NUMBER NUMBER(38)
OLD_PCARD_EXPDATE DATE
AUTHORIZED_AMT NUMBER(8,2)
OLD_AUTHAMT NUMBER(8,2)
SQL> desc shippingco;
Name Null? Type
----------------------------------------- -------- ----------------------
------
SHIPPER_ID NOT NULL NUMBER(38)
SHIPPER_NAME NOT NULL VARCHAR2(50)
STATUS NOT NULL CHAR(25)
CREATE_DATE DATE
SQL> desc shippingcoaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
SHIPPER_ID NUMBER(38)
SHIPPER_NAME VARCHAR2(50)
STATUS CHAR(25)
CREATE_DATE DATE
OLD_SHIPPERID NUMBER(38)
OLD_SHIPPERNAME VARCHAR2(50)
OLD_STATUS CHAR(25)

113
OLD_CREATE_DATE DATE
SQL> desc receipt;
Name Null? Type
----------------------------------------- -------- ----------------------
------
CUSTOMER_ID NOT NULL NUMBER(38)
PRODUCT_ID NOT NULL NUMBER(38)
RECEIPT_TAX NUMBER(8,2)
QTY NOT NULL NUMBER(38)
RECEIPT_DATE NOT NULL DATE
RECEIPT_FINALAMT NUMBER(8,2)
RECEIPT_AMOUNT NOT NULL NUMBER(8,2)
SQL> desc receiptaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
CUSTOMER_ID NUMBER(38)
STORE_ID NUMBER(38)
PRODUCT_ID NUMBER(38)
RECEIPT_TAX NUMBER(8,2)
QTY NUMBER(38)
OLD_CUSTOMERID NUMBER(38)
OLD_PRODID NUMBER(38)
OLD_RECEPTTAX NUMBER(8,2)
OLD_QTY NUMBER(38)
RECEIPT_DATE DATE
OLD_RECEIPTDATE DATE
RECEIPT_AMOUNT NUMBER(8,2)
RECEIPT_FINALAMT NUMBER(8,2)
OLD_RECPTFINALAMT NUMBER(8,2)
OLD_RECPTAMT NUMBER(8,2)
SQL> desc product;
Name Null? Type
----------------------------------------- -------- ----------------------
------
PRODUCT_ID NOT NULL NUMBER(38)
PRODUCT_CATEGORYID NOT NULL NUMBER(38)
PRODUCT_NAME NOT NULL VARCHAR2(110)

114
PRODUCT_COLOR VARCHAR2(35)
PRODUCT_SIZE VARCHAR2(35)
PRODUCT_WEIGHT VARCHAR2(45)
PRODUCT_DESCRIPTION CLOB
SQL> desc productaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
PRODUCT_ID NUMBER(38)
PRODUCT_CATEGORYID NUMBER(38)
PRODUCT_NAME VARCHAR2(110)
PRODUCT_COLOR VARCHAR2(35)
PRODUCT_SIZE VARCHAR2(35)
PRODUCT_WEIGHT VARCHAR2(45)
PRODUCT_DESCRIPTION CLOB
OLD_PRODUCTID NUMBER(38)
OLD_PRODCATEGORYID NUMBER(38)
OLD_PRODNAME VARCHAR2(110)
OLD_PRODCOLOR VARCHAR2(35)
OLD_PRODSIZE VARCHAR2(35)
OLD_PRODWEIGHT VARCHAR2(45)
OLD_PRODDESCRIPTION CLOB
SQL> desc productcategory;
Name Null? Type
----------------------------------------- -------- ----------------------
------
PRODUCT_CATEGORYID NOT NULL NUMBER(38)
CATEGORY_NAME NOT NULL VARCHAR2(45)
CATEGORY_DESCRIPTION CLOB
SQL> desc productcategoryaudit;
Name Null? Type
----------------------------------------- -------- ----------------------
------
PRODUCT_CATEGORYID NUMBER(38)
CATEGORY_NAME VARCHAR2(45)
CATEGORY_DESCRIPTION CLOB
OLD_PRODCATEGORYID NUMBER(38)
OLD_CATEGORYNAME VARCHAR2(45)
OLD_CATEGORYDESCRIPTION CLOB
SQL> set echo off;
SQL> spool off;

152
Database Population
SQL>
SQL> show user;
USER is "HOWARDDBA"
SQL>
SQL> Insert into statecodes(Country_Code, State_Name, State_Abbrv,
State_TaxRate )
2 values( 105, 'Alabama', 'AL', 5);
1 row created.
SQL> commit;
Commit complete.
State_TaxRate )
2 values( (select Country_Code from countrycode where
Country_Abbrv = 'US'), 'Alaska', 'AK', 4);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Arkansas', 'AR', 55);
1 row created.
State_TaxRate )
Country_Abbrv = 'US'), 'Arizona', 'AZ', 55);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'California', 'CA', 6);
1 row created.

153
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Colorado', 'CO', 3);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Connecticut', 'CT', 59);
1 row created.
SQL> commit;
Commit complete.
State_TaxRate )
Country_Abbrv = 'US'), 'Delaware', 'DE', 4);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Florida', 'FL', 25);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Georgia', 'GA', 7);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Hawaii', 'HI', 5);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Idaho', 'ID', 3);
1 row created.
SQL> commit;

154
Commit complete.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Indiana', 'IN', 55);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Iowa', 'IA', 45);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Kansas', 'KS', 5);
1 row created.
SQL> commit;
Commit complete.
State_TaxRate )
Country_Abbrv = 'US'), 'Kentucky', 'KY', 6);
1 row created.
State_TaxRate )
Country_Abbrv = 'US'), 'Louisiana', 'LA', 59);
1 row created.
State_TaxRate )
Country_Abbrv = 'US'), 'Maine', 'ME', 5);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Maryland', 'MD', 65);

155
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Massachusetts', 'MA', 7);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Mississippi', 'MS', 5);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Minnesota', 'MN', 75);
1 row created.
SQL> commit;
Commit complete.
State_TaxRate )
Country_Abbrv = 'US'), 'Montana', 'MT', 65);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Virginia', 'VA', 55);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Nevada', 'NV', 45);
1 row created.
SQL>
State_TaxRate )

156
Country_Abbrv = 'US'), 'New Hampshire', 'NH', 5);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'New Jersey', 'NJ', 6);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'New Mexico', 'NM', 59);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'New York', 'NY', 8);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'North Carolina', 'NC', 65);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'North Dakota', 'ND', 7);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'Ohio', 'OH', 5);
1 row created.
SQL>
State_TaxRate )

157
Country_Abbrv = 'US'), 'Pennsylvania', 'PA', 75);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'US'), 'District of Columbia', 'DC', 75);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'CA'), 'Alberta', 'AB', 45);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'CA'), 'British Columbia', 'BC', 5);
1 row created.
SQL> commit;
Commit complete.
SQL>
State_TaxRate )
Country_Abbrv = 'CA'), 'Ontario', 'ON', 6);
1 row created.
SQL>
State_TaxRate )
Country_Abbrv = 'CA'), 'Quebec', 'QC', 59);
1 row created.
SQL> commit;
Commit complete.
SQL> SET ECHO OFF;
SQL> SPOOL OFF;

158
SQL> INSERT INTO customer(State_ID, First_Name, Last_Name,Address, City,
Phone, DOB)
2 values( (select state_id from statecodes where state_abbrv =
'CO'), 'Sunny', 'Lionel', '4234 W Peterson Apt 5B', 'Denver', '303-789-
9292',
3 TO_DATE( '10-FEB-1968','DD-MON-YYYY'));
1 row created.
Phone, DOB)
'CO'), 'Mercy', 'Wells', '553 S County RD ', 'Denver', '720-461-1989',
3 TO_DATE( '20-SEP-1950','DD-MON-YYYY'));
1 row created.
SQL> commit;
Commit complete.
Phone, DOB)
'CO'), 'Janet', 'Laurl', '19 E Dupage RD ', 'Castle Rock', '303-689-6565',
3 TO_DATE( '25-NOV-1978','DD-MON-YYYY'));
1 row created.
Phone, DOB)
'CO'), 'Tavin','Michael', '4223 N Ashland ', 'Broomfield ', '720-876-
9279',
3 TO_DATE( '21-JUN-1985','DD-MON-YYYY'));
1 row created.
Phone, DOB)
'CO'), 'Whitney ', 'Jackson', '798 S Austin Suite 25','Aurora ', '303-221-
890',
3 TO_DATE( '11-NOV-1972','DD-MON-YYYY'));
1 row created.
SQL> COMMIT;
Commit complete.

159
Phone, DOB)
'CO'), 'Mary', 'Nelson', '205 E Lakshore Drv Apt 7823B', 'Denver', '303-
235-4040',
3 TO_DATE( '19-APR-1975','DD-MON-YYYY'));
1 row created.
SQL>
SQL>
SQL> commit;
Commit complete.
Phone, DOB)
'CO'), 'Washington' ,'White', '200 N Capitol', 'Denver','303-456-8902',
3 TO_DATE( '21-FEB-1980','DD-MON-YYYY'));
1 row created.
Phone, DOB)
'MD'), 'Rose', 'Hilson', '344 Airport Drv Apt 6C', 'Aurora','302-409-
2221',
3 TO_DATE( '03-MAR-1989','DD-MON-YYYY'));
1 row created.
SQL> COMMIT;
Commit complete.
Phone, DOB)
'MD'), 'Townson', 'Kelly','192 York Rd Apt 7B', 'Broomfield', '720-243-
1989', TO_DATE( '24-JUL-1979','DD-MON-YYYY'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
Phone, DOB)
'PA'), 'Linda', 'Frost', '42 E York Rd Apt 5G' ,'Broomfield','720-980-
9873',
3 TO_DATE( '19-JAN-1992','DD-MON-YYYY'));
1 row created.

160
Phone, DOB)
'DC'), 'Bryson' ,'Brown', '3232 Rocky Parks Ave', 'Castle Rock', '302-
334-432',
3 TO_DATE( '23-APR-1959','DD-MON-YYYY'));
1 row created.
SQL> commit;
Commit complete.
SQL>
Phone, DOB)
'AZ'), 'Ebony','Young','402 Federal Blv Apt 20b', 'PHOENIX',
3 '602-980-9873', TO_DATE( '20-APR-1951','DD-MON-YYYY'));
1 row created.
SQL>
SQL>
SQL> commit;
Commit complete.
Phone, DOB)
'AZ'), 'Diane', 'Yang', '1920 S Apple Drv Apt 12C','PHOENIX',
3 '602-444-9762',TO_DATE( '09-SEP-1982','DD-MON-YYYY'));
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> INSERT INTO customer(State_ID, First_Name,
Middle_Name,Last_Name,Address, City, Phone, DOB)
'AZ'), 'Christy', 'Christy', 'Mark', '20 University Drv Apt 4G',
3 'PHOENIX', '602-444-9762',
4 TO_DATE( '23-SEP-1976','DD-MON-YYYY'));
1 row created.
SQL>
SQL>
SQL> commit;
Commit complete.

161
SQL>
Phone, DOB)
'CA'), 'Robyn','Young','402 Federal Blv Apt 20b', 'Los Angeles',
3 '323-980-9873',TO_DATE( '20-MAR-1973','DD-MON-YYYY'));
1 row created.
SQL>
SQL> commit;
Commit complete.
Phone, DOB)
'CA'), 'Diane', 'Yang', '1920 S Apple Drv Apt 12C','Los Angeles',
3 '323-444-9762',TO_DATE( '23-JUN-1973','DD-MON-YYYY'));
1 row created.
SQL> INSERT INTO customer(State_ID, First_Name,
Middle_Name,Last_Name,Address, City, Phone, DOB)
'CA'), 'Christy', 'Christy', 'Mark', '20 University Drv Apt 4G',
3 'Los Angeles', '323-444-9762',TO_DATE( '23-APR-1986','DD-MON-
YYYY'));
1 row created.
SQL> commit;
Commit complete.
SQL> SET ECHO OFF
SQL> SPOOL OFF;

166
Modifying the Table Column

167
Next I would change the declared variables for the triggers to reflect to the product name
column data size
SQL> create or replace TRIGGER productAudit_del_trigg
2 AFTER DELETE ON PRODUCT
3 FOR EACH ROW
4 DECLARE
5 OPID Integer;
6 OPCID Integer;
8 OPC Varchar2(35);
9 OPS Varchar2(35);
11 OPD CLOB;
12 ACT Char(20);
13
14 BEGIN
23
24
26
(Old_ProductID,Old_ProdCategoryID,Old_ProdName,Old_ProdColor,Old_ProdSize,
Old_ProdWeight,Old_ProdDescription, Action_Type)
27 SELECT OPID,OPCID, OPN, OPC,OPS,OPW,OPD, ACT FROM DUAL;
28 END productAudit_del_trigg;
29
30
31 /
Trigger created.

168
SQL>
SQL> create or replace TRIGGER productAudit_Inst_trigg
2 AFTER INSERT ON PRODUCT
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
6 NPCID Integer;
8 NPC Varchar2(35);
9 NPS Varchar2(35);
11 NPD CLOB;
12 ACT Char(20);
13
14 BEGIN
23
24
26
duct_Weight,Product_Description, Action_Type)
27 SELECT NPID,NPCID, NPN, NPC,NPS,NPW,NPD, ACT FROM DUAL;
28 END productAudit_Inst_trigg;
29 /
Trigger created.
SQL> create or replace TRIGGER productAudit_updt_trigg
2 AFTER update ON PRODUCT
3 FOR EACH ROW
4 DECLARE
5 NPID Integer;
6 NPCID Integer;
8 NPC Varchar2(35);
9 NPS Varchar2(35);
11 NPD CLOB;
12 OPID Integer;
13 OPCID Integer;
15 OPC Varchar2(35);
16 OPS Varchar2(35);
18 OPD CLOB;
19 ACT Char(20);
20
21 BEGIN

169
37
38
40
duct_Weight,Product_Description,
41
Old_ProductID,Old_ProdCategoryID,Old_ProdName,Old_ProdColor,Old_ProdSize,O
ld_ProdWeight,Old_ProdDescription,
42 Action_Type)
43 SELECT NPID,NPCID, NPN, NPC,NPS,NPW,NPD, OPID,OPCID, OPN,
OPC,OPS,OPW,OPD,
44 ACT FROM DUAL;
45 END productAudit_updt_trigg;
46 /
Trigger created.
SQL> insert into Product(Product_CategoryID, Product_Name, Product_Color,
Product_Size,Product_Description,Product_Weight )
2 values ( (select product_categoryid from productcategory where
category_name ='Televsion'),
3 'LG Electronics LN530B 32LN530B LED-lit 720p 60Hz TV
','Black','32-Inch',
4 'Intelligent Sensor, Picture Wizard, Smart Energy Saving,
Simplink, Triple XD Engine, Mobile High-Definition Link (MHL)',
5 '14.8 pounds');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SET ECHO OFF
SQL> SPOOL OFF;

178
Database Verifcation- Regular Tables

184
Database Verifcation- Audit Tables

192
Data Population and Mainpulation

197
In the following demonstrations , Microsoft Visual Studio would be utilized along with SQL Developer JD
Developer to creat various Oracle database objects and scripts.

204
anonymous block completed
EmployeeID:443
First Name:John
Middle Name:NA
Last Name:Martins
City:Denver
StateaID:295
Phone:259-8259
Address:5909 N Clark St Apt 16B2
StateName:Colorado
CountryName:United States
HireDate:2011-JAN-01
DOB:25-AUG-66
EmployeeID:468
First Name:Bryan
Middle Name:NA
Last Name:Sheridan
City:Denver
StateaID:295
Phone:720-9089
Address:174 S Wilson Apt 6B
StateName:Colorado
DOB:19-APR-83
EmployeeID:493
First Name:liz
Middle Name:NA
Last Name:Clapton
City:Denver
StateaID:295
Phone:453-9870
Address:402 S Parker RD Apt 5c
StateName:Colorado
DOB:15-FEB-80
EmployeeID:593
First Name:Michael
Middle Name:NA
Last Name:Wats
City:Forest Lawn
StateaID:280
Phone:453-9870
Address:42 N Empire Pike Apt 76B
StateName:California
DOB:25-MAR-74
EmployeeID:618
First Name:Chantelle
Middle Name:NA
Last Name:Obi

205
City:Los Angeles
StateaID:280
Phone:453-8432
Address:4040 5th Ave Apt 42b
DOB:30-SEP-79
EmployeeID:643
First Name:Michael
Middle Name:NA
Last Name:Harrison
City:Los Angeles
StateaID:280
Phone:453-9437
Address:4040 Oakland Ave Apt 42b
DOB:25-MAR-76
EmployeeID:518
First Name:Paulina
Middle Name:NA
Last Name:Dwanson
City:PHOENIX
StateaID:265
Phone:259-8259
Address: 2424 NW 5th St Apt 2c
StateName:Arizona
DOB:21-JUN-82
EmployeeID:543
First Name:Roland
Middle Name:Scott
Last Name:Dwanson
City:PHOENIX
StateaID:265
Phone:458-8259
Address: 2424 NW 5th St Apt 2c
StateName:Arizona
DOB:21-FEB-89
EmployeeID:568
First Name:Regina
Middle Name:NA
Last Name:Donald
City:PHOENIX
StateaID:265
Phone:112-1134
Address:40 E Circle Ave Apt 7B
StateName:Arizona

206
DOB:22-NOV-80
EmployeeID:568
First Name:Regina
Middle Name:NA
Last Name:Donald
City:PHOENIX
StateaID:265
Phone:112-1134
Address:40 E Circle Ave Apt 7B
StateName:Arizona
DOB:22-NOV-80

211
User Account Creations and Privileges

230
Inorder for the receipt_tax and receipt_finalamt , I have to switch to the DBA account and use
the receipt_dml package since the granted privileges are not working for user sunny.

259
anonymous block completed
1 is the number of rows processed
Customer ID: 1078
First Name:Ebony
Last Name:Young
Receipt ID:1492
Product ID: 1162
Order Date:06-FEB-11
QTY:3
Amount:300
Tax:9
Final Amount:909
Prod Name:Sony HDR-CX220/B High Definition Handycam Camcorde
Prod Color: Silver
Prod Size:
Store Name:Valley of Sun Electronics
Customer ID: 1078
First Name:Ebony
Last Name:Young
Receipt ID:1372
Product ID: 1207
Order Date:12-JAN-11
QTY:4
Amount:200
Tax:9
Final Amount:809
Prod Name:Avaya 1416 Digital Deskphone
Prod Color: Black
Prod Size:

260
Customer ID: 1078
First Name:Ebony
Last Name:Young
Receipt ID:1272
Product ID: 862
QTY:5
Amount:880
Tax:9
Final Amount:4409
Prod Name:Acer C720 Chromebook
Prod Color: Silver
Prod Size:11.6 inch
Customer ID: 1078
First Name:Ebony
Last Name:Young
Receipt ID:1252
Product ID: 502
QTY:8
Amount:600
Tax:9
Final Amount:4809
Prod Name:Samsung UN32EH5300 1080p 60 Hz Smart LED HDTV
Prod Color: Black
Prod Size:40-Inch
Cursor is closed

295
Database Creation
Application Accounts Creation
Oracle SQLDeveloper

296
Microsoft Visual Studio 2010
Oracle JDeveloper

297
Tables
mysql> create table HowardR.countrycode
-> (Country_Code Integer AUTO_INCREMENT,
-> Country_Name Varchar(60) not null,
-> Country_Abbrv Varchar(12) not null,
-> constraint countrycode_countrycode_pk primary key
(Country_Code)
-> ) Engine = InnoDB ;
Query OK, 0 rows affected (0.52 sec)
mysql> create table HowardR.CountryCodesAudit
-> (Country_Code Integer ,
-> Country_Name Varchar(60),
-> Country_Abbrv Varchar(12),
-> Audit_Date timestamp,
-> Audit_User varchar(50),
-> old_countrycode integer,
-> old_countryname Varchar(60),
-> old_countryabbrv Varchar(12),
-> Audit_ID integer AUTO_INCREMENT ,
-> Action_Type char(20),
-> constraint countrycd_AuditID_pk primary key (Audit_ID)
mysql> Create table HowardR.statecodes
-> (State_ID integer AUTO_INCREMENT,
-> Country_Code integer,
-> State_Name ENUM ( 'Alabama', 'Alaska',
-> 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut',
'Delaware', 'Florida'
-> ,'Georgia', 'Hawaii', 'Idaho', 'ILLINOIS ', 'Indiana', 'Iowa',
'Kansas','Kentucky',
-> 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan',
'Minnesota',
-> 'Mississippi', 'Missouri', 'Montana', 'Nebraska', 'Nevada', 'New
Hampshire',
-> 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 'North
Dakota',
-> 'Ohio','Oklahoma', 'Pennsylvania', 'Rhode Island', 'South
Carolina',
-> 'South Dakota','Tennessee', 'Texas', 'Utah',
'Vermont','Virginia', 'Washington',
-> 'West Virginia', 'Wisconsin', 'Wyoming','District of Columbia',
'Alberta',
-> 'British Columbia', 'Manitoba', 'New Brunswick', 'Newfoundland-
Larb',
-> 'Northwest Territories', 'Nova Scotia', 'Nunavut', 'Ontario',
'Prince Edward Island',

298
-> 'Quebec', 'Saskatchewan', 'Yukon'
) not null,
-> State_Abbrv ENUM( 'AL', 'AK','AZ', 'AR',
-> 'CA', 'CO', 'CT','DE','FL',
'GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD', 'MA',
->
'MI','MN','MS','MO','MT','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR'
,'PA','RI','SC','SD','TN',
-> 'TX','UT','VA','WA','WV',
'WI','WY','DC','AB','BC','MB','NB','NL','NT','NS','NU',
-> 'ON', 'PE','QC', 'SK', 'YT' ) not null,
-> State_TaxRate integer not null,
-> constraint statecodes_statecode_pk primary key(State_ID,
Country_Code),
-> constraint statecodes_statecode_uk unique (State_ID),
-> constraint Statecode_countrycode_fk foreign key
(Country_Code) references
-> HowardR.CountryCode(Country_Code)
mysql>
mysql> Create table statecodesAudit
-> (State_ID integer,
-> Country_Code integer,
-> State_Name varchar(110),
-> State_Abbrv Varchar(10),
-> State_TaxRate integer not null,
-> Old_StateID integer ,
-> Old_CountryCode integer,
-> Old_StateName varchar(110),
-> Old_State_Abbrv Varchar(10),
-> Old_StTaxRate integer,
-> constraint statecodesaudit_auditid_pk primary key(Audit_ID)
-> )Engine = InnoDB ;
mysql> create table HowardR.Customer
-> (Customer_ID Integer,
-> State_ID Integer,
-> First_Name Varchar(50) not null,
-> Middle_Name Varchar(50),
-> Last_Name Varchar(50) not null,
-> Address Varchar(50) not null,
-> City Varchar(45) not null,
-> Phone Varchar(25) not null,
-> DOB DATE not null,
-> CONSTRAINT Customer_custid_pk primary key(Customer_ID, State_ID),
-> CONSTRAINT Customer_custid_uk unique(Customer_ID),
-> CONSTRAINT Customer_stateid_pk foreign key(state_id) references
HowardR.statecodes(state_id)

299
mysql> Create table HowardR.CustomerAudit
-> (ustomer_ID Integer,
-> First_Name Varchar(50),
-> Middle_Name Varchar(50),
-> Last_Name Varchar(50),
-> Address Varchar(50),
-> City Varchar(45),
-> Phone Varchar(25),
-> DOB DATE,
-> Old_CustomerID Integer,
-> Old_StateID Integer,
-> Old_FirstName Varchar(50),
-> Old_MiddleName Varchar(50),
-> Old_LastName Varchar(50),
-> Old_Address Varchar(50),
-> Old_City Varchar(45),
-> Old_Phone Varchar(25),
-> Old_DOB DATE,
-> constraint customeraudit_auditid_pk primary key(Audit_ID)
-> )
-> Engine = InnoDB ;
mysql> create table HowardR.Employee
-> (Employee_ID integer AUTO_INCREMENT,
-> First_Name varchar(55) not null,
-> Middle_Name varchar(55),
-> Last_Name Varchar(55) not null,
-> Address varchar(55) not null,
-> City Varchar(45) not null,
-> Phone Varchar(45) not null,
-> constraint employee_empid_pk primary
key(Employee_ID,State_ID),
-> constraint employee_empid_uk unique (Employee_ID),
-> constraint employee_stateid_fk foreign key (State_ID)
references HowardR.statecodes(State_ID)
mysql>
mysql>
mysql> create table HowardR.EmployeeAudit
-> (Employee_ID integer ,
-> First_Name varchar(55),
-> Middle_Name varchar(55),
-> Last_Name Varchar(55),
-> Address varchar(55),
-> City Varchar(45),
-> Phone Varchar(45),

300
-> Audit_ID integer AUTO_INCREMENT,
-> Old_Employee_ID integer,
-> Old_First_Name varchar(55),
-> Old_Middle_Name varchar(55),
-> Old_Last_Name Varchar(55),
-> Old_Address varchar(55),
-> Old_City Varchar(45),
-> Old_State_ID Integer,
-> Old_Phone Varchar(45),
-> constraint employeeaudit_auditid_pk primary key(Audit_ID)
mysql> create table HowardR.HRRoles
-> (Role_ID integer AUTO_INCREMENT,
-> Role_Title varchar(45) not null,
-> Role_status varchar(45) not null,
-> constraint HRRoles_roleid_pk primary key(Role_ID)
mysql>
mysql> create table HowardR.HRRolesAudit
-> (Role_ID integer ,
-> Role_Title varchar(45),
-> Role_status varchar(45),
-> Old_RoleID integer,
-> Old_RoleTitle varchar(45),
-> Old_Role_status varchar(45),
-> constraint HRRoleAdt_AuditID_PK PRIMARY KEY(Audit_ID)
mysql> create table HowardR.store
-> (Store_ID Integer AUTO_INCREMENT,
-> Store_StateID Integer,
-> Store_Name Varchar(65) not null ,
-> Store_Type Varchar(34) not null ,
-> Store_Address Varchar (65) not null ,
-> Store_City Varchar(45) not null ,
-> Store_Phone Varchar (45) not null ,
-> Store_Status Varchar (30) not null ,
-> constraint store_storeid_pk primary
key(Store_ID,Store_StateID),
-> constraint store_storestateid_fk foreign key(store_stateid)
references statecodes(state_id),
-> constraint store_storeid_uk unique (store_id)

301
mysql> create table HowardR.storeAudit
-> (Store_ID Integer ,
-> Store_StateID Integer,
-> Store_Name Varchar(65),
-> Store_Type Varchar(34),
-> Store_Address Varchar(65),
-> Store_City Varchar(45),
-> Store_Phone Varchar(45),
-> Store_Status Varchar(30),
-> Old_StoreID Integer,
-> Old_StorStateID Integer,
-> Old_StorName Varchar(65),
-> Old_StorType Varchar(34),
-> Old_StorAddress Varchar(65),
-> Old_StorCity Varchar(45),
-> old_StorPhone Varchar(45),
-> old_StorStatus Varchar(30),
-> constraint storeaudit_AuditID_pk primary key (Audit_ID)
mysql> create table HowardR.Management
-> (Manager_ID integer AUTO_INCREMENT,
-> Employee_ID integer,
-> Hire_Date Date not null,
-> Hire_EndDate Date,
-> store_id integer not null,
-> constraint Management_Magerid_pk primary
key(Manager_ID,Employee_ID, store_id),
-> constraint Management_Magerid_uk unique(Manager_ID),
-> constraint Management_empid_fk foreign key(Employee_ID)
references Employee(Employee_ID),
-> constraint Management_storeid_fk foreign key (Store_ID)
references store (store_id)
mysql>
mysql> Alter table Management
-> add constraint management_managerid_fk foreign
key(Manager_id) references management(manager_id);
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> create table HowardR.ManagementAudit
-> (Manager_ID integer ,
-> Store_ID integer,
-> Hire_Date Date,
-> Hire_EndDate Date,
-> Old_ManagerID integer,
-> Old_EmployeeID integer,

302
-> Old_HireDate Date,
-> Old_HireEndDate Date,
-> Old_StoreID Integer,
-> constraint managementAudt_AuditID_pk primary key (Audit_ID)
mysql>
mysql>
mysql> Create table HowardR.HRRecords
-> (Employee_ID Integer AUTO_INCREMENT,
-> HR_ID Integer Not null,
-> Role_ID Integer not null,
-> Store_ID integer not null,
-> DOB Date not null,
-> Manager_id INTEGER,
-> Social_SecurityNum Integer,
-> Hire_Date Date ,
-> End_Date Date,
-> PayRate numeric(15,2) not null,
-> Employement_Status varchar(45) not null,
-> constraint HrRecord_hrid_pk primary
key(Employee_ID,HR_ID,Role_ID,Store_ID, Manager_ID),
-> constraint HrRecord_hri_uk unique (HR_ID),
-> constraint HrRecord_empid_fk foreign key(Employee_ID)
references HowardR.Employee(Employee_ID) ,
-> constraint HrRecord_roleid_fk foreign key(Role_ID) references
HowardR.HRRoles(Role_ID),
-> constraint HrRecord_storeid_fk foreign key(store_id)
references HowardR.store(store_id),
-> constraint HrRecords_managerid_fk foreign key(manager_id)
references management(manager_id)
mysql>
mysql>
mysql> Create table HowardR.HRRecordsAudit
-> (Employee_ID Integer ,
-> HR_ID Integer ,
-> Role_ID Integer,
-> DOB Date,
-> Social_SecurityNum Integer,
-> Hire_Date Date,
-> End_Date Date,
-> PayRate numeric(15,2),
-> Manager_ID integer,
-> Old_Managerid integer,
-> Employement_Status varchar(45),
-> Old_EmployeeID Integer,
-> Old_HRID Integer,

303
-> Old_RoleID Integer,
-> Old_StoreID integer,
-> Old_DOB Date,
-> Old_SocialSecurityNum Integer,
-> Old_HireDate Date,
-> Old_EndDate Date,
-> Old_PayRate numeric(15,2),
-> Old_EmployementStatus varchar(45),
-> Constraint HRRecordsAudit_AuditID_pk primary key (Audit_ID)
mysql> create table HowardR.ProductCategory
-> (Product_CategoryID Integer,
-> Category_Name Varchar(45) not null,
-> Category_Description text,
-> constraint Prodcategory_prodcategory_pk primary
-> )Engine = InnoDB
-> ;
mysql> create table HowardR.ProductCategoryAudit
-> (Product_CategoryID Integer,
-> Category_Name Varchar(45) ,
-> Category_Description text,
-> Old_ProdCategoryID Integer,
-> Old_CategoName Varchar(45) ,
-> Old_CategoDescription text,
-> constraint ProdcategoAudt_AuditID_pk primary key
(Audit_ID)
mysql>
mysql> create table HowardR.product
-> (Product_ID Integer AUTO_INCREMENT,
-> Product_CategoryID Integer,
-> Product_Name Varchar(50) not null,
-> Product_Color Varchar(35),
-> Product_Size Varchar(35),
-> Product_Weight Varchar(45),
-> Product_Description text,
-> constraint Product_Prodid_pk primary
key(Product_ID,Product_CategoryID),
-> constraint product_prodid_uk unique (Product_ID),
-> constraint product_prodcategoid_fk foreign
key(Product_CategoryID) references HowardR.ProductCategory
(Product_CategoryID)

304
mysql>
mysql> create table HowardR.Receipt
-> (Receipt_ID Integer AUTO_INCREMENT,
-> Customer_ID integer,
-> Product_ID Integer,
-> Receipt_Amount decimal (8,2) not null ,
-> Receipt_Tax decimal (8,2) not null,
-> QTY Integer not null,
-> Receipt_FinalAmt decimal (8,2) not null,
-> constraint Receipt_Receipid_pk primary
key(Receipt_ID,Employee_ID,Customer_ID,Store_ID
-> ,Product_ID),
-> constraint Receipt_Receiptid_uk unique (Receipt_ID)
mysql>
mysql>
mysql> Alter table receipt
-> add constraint receipt_customerid_fk foreign key(customer_id)
references customer(customer_id);
-> add constraint receipt_Employeeid_fk foreign key(Employee_ID)
References Employee(Employee_id);
-> add constraint receipt_storeid_fk foreign key(store_id)
references store(store_id);
-> add constraint receipt_productid_fk foreign key(product_id)
references product(product_id);
-> add receipt_date date;
mysql> desc receiptaudit;
ERROR 1146 (42S02): Table 'howardr.receiptaudit' doesn't exist

305
mysql> desc receiptaudit;
ERROR 1146 (42S02): Table 'howardr.receiptaudit' doesn't exist
mysql>
mysql>
mysql> create table HowardR.Receiptaudit
-> (Receipt_ID Integer ,
-> Customer_ID integer,
-> Product_ID Integer,
-> Receipt_Amount decimal (8,2) ,
-> Receipt_Tax decimal (8,2) ,
-> QTY Integer ,
-> Receipt_FinalAmt decimal (8,2),
-> Old_ReceiptID Integer,
-> Old_EmployeeID integer,
-> Old_CustomerID integer,
-> Old_Store_ID integer,
-> Old_Product_ID Integer,
-> Old_Receipt_Amount decimal (8,2) ,
-> Old_Receipt_Tax decimal (8,2) ,
-> Old_QTY Integer ,
-> Old_ReceiptFinalAmt decimal (8,2),
-> constraint Receipt_AuditID_pk primary key (Audit_ID)
-> );
mysql> alter table Receiptaudit
-> add receipt_date date;
mysql>
mysql> alter table receiptaudit
-> add old_receiptdate date;
mysql>
mysql> create table HowardR.PaymentCop
-> (Payment_id integer AUTO_INCREMENT,
-> Payment_CopName Varchar(50) not null,
-> Payment_Type ENUM('VISA','MasterCard', 'Discovery','American
Express','NA') not null,
-> Payment_CreatedDate Date,
-> Payment_Status varchar(25),
-> PaymentCard_Number integer not null,
-> Receipt_ID integer Not Null,
-> payment_expdate date,
-> constraint PaymentCop_paymentid_pk primary key(Payment_id,
Receipt_ID)

306
mysql> Alter table Paymentcop
-> add constraint paymentcop_paymentid_uk unique (Payment_ID);
-> add constraint PaymentCop_Receiptid_fk foreign key (receipt_id)
references receipt(receipt_id);
-> add authorized_Amount Decimal(8,2);
mysql>
mysql>
mysql> create table HowardR.PaymentCopAudit
-> (Payment_id integer,
-> Payment_CopName Varchar(50) ,
-> Payment_Type char(35),
-> Payment_CreatedDate Date,
-> Payment_Status varchar(25),
-> PaymentCard_Number integer ,
-> Receipt_ID integer ,
-> payment_expdate date,
-> old_paymentExDate date,
-> authorized_Amount Decimal(8,2),
-> Old_Paymentid integer ,
-> Old_PayCopName Varchar(50),
-> Old_PaynebtType char(35),
-> Old_PayCreatedDate Date,
-> Old_PaymentStatus varchar(25),
-> Old_PayCardNum integer,
-> Old_AuthordAmt Decimal(8,2),
-> old_ReceiptID integer ,
-> constraint PaycopAudit_AuditID_pk primary key (Audit_ID)
mysql>
mysql> create table HowardR.shippingco
-> (shipper_id integer AUTO_INCREMENT,
-> Shipper_Name Varchar(50),
-> status char(25),
-> Create_date date,
-> constraint shippingco_shipperid_pk primary key(shipper_id)

307
mysql>
mysql> create table HowardR.shippingcoAudit
-> (shipper_id integer,
-> Shipper_Name Varchar(50),
-> status char(25),
-> Create_date date,
-> Old_shipperid integer,
-> old_ShipperName Varchar(50),
-> old_status char(25),
-> old_Create_date date,
-> constraint shippingcoAudit_auditid_pk primary key(Audit_ID)
mysql>
mysql> create table HowardR.shipment
-> (shipping_id integer AUTO_INCREMENT,
-> shipper_id integer,
-> state_id integer,
-> Receipt_ID integer,
-> ship_date date,
-> ship_enddate date,
-> receive_date date,
-> Name varchar(55) not null,
-> Address Varchar(55) not null,
-> City varchar(55) not null,
-> shipping_amount NUMERIC not null,
-> shipping_type char(35) not null,
-> constraint shipment_shipmentid_pk primary key
(shipping_id,shipper_id),
-> constraint shipment_shipmentid_uk unique(shipping_id),
-> constraint shipment_shipperid_fk foreign key(shipper_id)
references shippingco(shipper_id)
-> )
-> Engine = InnoDB ;
mysql>
mysql> create table HowardR.shipmentAudit
-> (shipping_id integer,
-> shipper_id integer,
-> state_id integer,
-> Receipt_ID integer,
-> ship_date date,
-> ship_enddate date,
-> receive_date date,
-> Name varchar(55),
-> Address Varchar(55),
-> City varchar(55),
-> shipping_amount NUMERIC,
-> shipping_type char(35),
-> old_shippingid integer,
-> old_shipperid integer,

308
-> old_stateid integer,
-> old_ReceiptID integer,
-> old_shipdate date,
-> old_shipenddate date,
-> old_receivedate date,
-> old_Name varchar(55),
-> old_Address Varchar(55),
-> old_City varchar(55),
-> old_shippingamt NUMERIC,
-> old_shippingtype char(35),
-> CONSTRAINT shipaudit_AUDITID_PK PRIMARY KEY (Audit_ID)
mysql>
mysql>
mysql> create table HowardR.productAudit
-> (Product_ID Integer ,
-> Product_CategoryID Integer,
-> Product_Name Varchar(50) ,
-> Product_Color Varchar(35),
-> Product_Size Varchar(35),
-> Product_Weight Varchar(45),
-> Product_Description text,
-> Old_ProductID Integer ,
-> Old_ProductCategoryID Integer,
-> Old_Product_Name Varchar(50) ,
-> Old_ProductColor Varchar(35),
-> Old_ProductSize Varchar(35),
-> Old_Product_Weight Varchar(45),
-> Old_ProductDescription text,
-> constraint ProductAudit_AuditID_pk primary key (Audit_ID)

309
Indexes
mysql>
mysql>
mysql>
mysql> USE HOWARDR;
Database changed
mysql> create index country_countryname_idx using
-> btree on HowardR.countrycode (Country_Name);
mysql>
mysql> create index country_countryabbrv_indx using btree
-> on HowardR.countrycode (Country_Abbrv);
mysql> create index statecodes_statename_indx using
-> btree on HowardR.statecodes (State_Name);
mysql>
mysql> create index statecodes_StateAbbrv_indx using btree
-> on HowardR.statecodes (State_Abbrv);
mysql>
mysql> create index statecodes_TaxRate_indx using btree
-> on HowardR.statecodes (State_TaxRate)
->
-> ;
mysql> create index Employee_FirstName_indx using
-> btree on HowardR.Employee (First_Name);
mysql>
mysql> create index Employee_city_indx using btree
-> on HowardR.employee (City);
mysql> create index HRRecords_DOB_indx using HASH
-> on HowardR.HRRecords (DOB);
mysql>

310
mysql>
mysql> create index HRRecords_SNN_indx using HASH
-> on HowardR.HRRecords (Social_SecurityNum);
mysql> create index Management_HireDate_indx using BTREE
-> on HowardR.Management (Manager_ID);
mysql>
mysql> create index store_StoreName_indx using BTREE
-> on HowardR.store (Store_Name);
mysql>
mysql>
mysql> create index store_StoreType_indx using BTREE
-> on HowardR.store (Store_Type);
mysql>
mysql>
mysql> create index store_StoreCity_indx using BTREE
-> on HowardR.store (Store_City);
mysql>
mysql>
mysql> create index customer_firstname_indx using BTREE
-> on HowardR.customer (First_Name);
mysql>
mysql> create index customer_lastname_indx using BTREE
-> on HowardR.customer (Last_name);
mysql>
mysql> create index customer_city_indx using BTREE
-> on HowardR.customer (city);
mysql>
mysql> create index customer_address_indx using BTREE
-> on HowardR.customer (address);
mysql> create index ProductCategory_CategoryName_indx using BTREE

311
-> on HowardR.ProductCategory (Category_Name);
mysql> create index product_ProductName_indx using BTREE
-> on HowardR.product (Product_Name);
Triggers

319
The triggers were recreated after the paymentcop, shipment and receipt tables were dropped due
to a database error.

331
*************************** 4. row ***************************
Field: Middle_Name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Last_Name
Type: varchar(50)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Address
Type: varchar(50)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: City
Type: varchar(45)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Phone
Type: varchar(25)
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: DOB
Type: date
Collation: NULL
Null: NO
Key:
Default: NULL

332
Extra:
Comment:
mysql> SHOW INDEXES FROM CUSTOMERG
*************************** 1. row ***************************
Table: customer
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Customer_ID
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: customer
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: State_ID
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: customer
Non_unique: 0
Key_name: Customer_custid_uk
Seq_in_index: 1
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: customer
Non_unique: 1
Key_name: customer_firstname_indx
Seq_in_index: 1
Column_name: First_Name
Collation: A
Cardinality: 23
Sub_part: NULL

333
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: customer
Non_unique: 1
Key_name: customer_lastname_indx
Seq_in_index: 1
Column_name: Last_Name
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: customer
Non_unique: 1
Key_name: customer_city_indx
Seq_in_index: 1
Column_name: City
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: customer
Non_unique: 1
Key_name: customer_address_indx
Seq_in_index: 1
Column_name: Address
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: customer
Non_unique: 1
Key_name: Customer_stateid_pk
Seq_in_index: 1
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL

335
+----------------+-------------+------+-----+-------------------+---------
--------------------+
mysql> SHOW FULL COLUMNS FROM CUSTOMERAUDITG
*************************** 1. row ***************************
Field: Customer_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: State_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: First_Name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Middle_Name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Last_Name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Address

336
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: City
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Phone
Type: varchar(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: DOB
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: Old_CustomerID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Old_StateID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:

337
Comment:
*************************** 12. row ***************************
Field: Old_FirstName
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Old_MiddleName
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 14. row ***************************
Field: Old_LastName
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: Old_Address
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Field: Old_City
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: Old_Phone
Type: varchar(25)
Null: YES
Key:

338
Default: NULL
Extra:
Comment:
*************************** 18. row ***************************
Field: Old_DOB
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 19. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 20. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 21. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
Comment:
*************************** 22. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM CUSTOMERAUDITG

339
*************************** 1. row ***************************
Table: customeraudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Audit_ID
Collation: A
Cardinality: 23
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
mysql> DESCRIBE COUNTRYCODE;
+---------------+-------------+------+-----+---------+----------------+
+---------------+-------------+------+-----+---------+----------------+
| Country_Code | int(11) | NO | PRI | NULL | auto_increment |
| Country_Name | varchar(60) | NO | MUL | NULL | |
| Country_Abbrv | varchar(12) | NO | MUL | NULL | |
+---------------+-------------+------+-----+---------+----------------+
mysql> SHOW FULL COLUMNS FROM COUNTRYCODEG
*************************** 1. row ***************************
Field: Country_Code
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Country_Name
Type: varchar(60)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Country_Abbrv
Type: varchar(12)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:

340
mysql> SHOW INDEXES FROM COUNTRYCODEG
*************************** 1. row ***************************
Table: countrycode
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Country_Code
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: countrycode
Non_unique: 1
Key_name: country_countryname_idx
Seq_in_index: 1
Column_name: Country_Name
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: countrycode
Non_unique: 1
Key_name: country_countryabbrv_indx
Seq_in_index: 1
Column_name: Country_Abbrv
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE COUNTRYCODESAUDIT;
+------------------+-------------+------+-----+-------------------+-------
----------------------+
|
+------------------+-------------+------+-----+-------------------+-------
----------------------+
| Country_Code | int(11) | YES | | NULL |
|

342
Null: NO
Key:
Comment:
*************************** 5. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: old_countrycode
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: old_countryname
Type: varchar(60)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: old_countryabbrv
Type: varchar(12)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 10. row ***************************

344
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Middle_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Last_Name
Type: varchar(55)
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Address
Type: varchar(55)
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: City
Type: varchar(45)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: State_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:

345
*************************** 8. row ***************************
Field: Phone
Type: varchar(45)
Null: NO
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM EMPLOYEE G
*************************** 1. row ***************************
Table: employee
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Employee_ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: employee
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: employee
Non_unique: 0
Key_name: employee_empid_uk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: employee

346
Non_unique: 1
Key_name: employee_stateid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: employee
Non_unique: 1
Key_name: Employee_FirstName_indx
Seq_in_index: 1
Column_name: First_Name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: employee
Non_unique: 1
Key_name: Employee_city_indx
Seq_in_index: 1
Column_name: City
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE EMPLOYEEAUDIT;
+-----------------+-------------+------+-----+-------------------+--------
---------------------+
|
+-----------------+-------------+------+-----+-------------------+--------
---------------------+
| Employee_ID | int(11) | YES | | NULL |
|
| First_Name | varchar(55) | YES | | NULL |
|
| Middle_Name | varchar(55) | YES | | NULL |
|
| Last_Name | varchar(55) | YES | | NULL |
|

348
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: First_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Middle_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Last_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Address
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: City
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:

349
*************************** 7. row ***************************
Field: State_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Phone
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 10. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 12. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL

350
Extra:
Comment:
*************************** 13. row ***************************
Field: Old_Employee_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 14. row ***************************
Field: Old_First_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: Old_Middle_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Field: Old_Last_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: Old_Address
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 18. row ***************************
Field: Old_City
Type: varchar(45)

351
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 19. row ***************************
Field: Old_State_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 20. row ***************************
Field: Old_Phone
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> DESCRIBE HRROLES;
+-------------+-------------+------+-----+---------+----------------+
+-------------+-------------+------+-----+---------+----------------+
| Role_ID | int(11) | NO | PRI | NULL | auto_increment |
| Role_Title | varchar(45) | NO | | NULL | |
| Role_status | varchar(45) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
mysql> SHOW FULL COLUMNS FROM HRROLES G
*************************** 1. row ***************************
Field: Role_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Role_Title
Type: varchar(45)
Null: NO
Key:
Default: NULL
Extra:

353
|
+-----------------+-------------+------+-----+-------------------+--------
---------------------+
mysql> SHOW FULL COLUMNS FROM HRROLESAUDIT G
*************************** 1. row ***************************
Field: Role_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: Role_Title
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Role_status
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Old_RoleID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Old_RoleTitle
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:

354
*************************** 6. row ***************************
Field: Old_Role_status
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 8. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 10. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM HRROLESAUDIT G
*************************** 1. row ***************************
Table: hrrolesaudit
Non_unique: 0
Key_name: PRIMARY

355
Seq_in_index: 1
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE HRRECORDS;
+--------------------+---------------+------+-----+---------+-------------
---+
|
+--------------------+---------------+------+-----+---------+-------------
---+
| Employee_ID | int(11) | NO | PRI | NULL |
auto_increment |
| HR_ID | int(11) | NO | PRI | NULL |
|
| Role_ID | int(11) | NO | PRI | NULL |
|
| Store_ID | int(11) | NO | PRI | NULL |
|
| DOB | date | NO | MUL | NULL |
|
| Manager_id | int(11) | NO | PRI | 0 |
|
| Social_SecurityNum | int(11) | YES | MUL | NULL |
|
| Hire_Date | date | YES | | NULL |
|
| End_Date | date | YES | | NULL |
|
| PayRate | decimal(15,2) | NO | | NULL |
|
| Employement_Status | varchar(45) | NO | | NULL |
|
+--------------------+---------------+------+-----+---------+-------------
---+
mysql> SHOW FULL COLUMNS FROM HRRECORDS G
*************************** 1. row ***************************
Field: Employee_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************

356
Field: HR_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Role_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Store_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: DOB
Type: date
Collation: NULL
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Manager_id
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 7. row ***************************
Field: Social_SecurityNum
Type: int(11)
Collation: NULL
Null: YES
Key: MUL
Default: NULL
Extra:

357
Comment:
*************************** 8. row ***************************
Field: Hire_Date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: End_Date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: PayRate
Type: decimal(15,2)
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Employement_Status
Type: varchar(45)
Null: NO
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM HRRECORDS G
*************************** 1. row ***************************
Table: hrrecords
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE

358
Comment:
Index_comment:
*************************** 2. row ***************************
Table: hrrecords
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: HR_ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: hrrecords
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: hrrecords
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 4
Column_name: Store_ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: hrrecords
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 5
Column_name: Manager_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

359
Index_comment:
*************************** 6. row ***************************
Table: hrrecords
Non_unique: 0
Key_name: HrRecord_hri_uk
Seq_in_index: 1
Column_name: HR_ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: hrrecords
Non_unique: 1
Key_name: HrRecord_roleid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: hrrecords
Non_unique: 1
Key_name: HrRecord_storeid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: hrrecords
Non_unique: 1
Key_name: HrRecords_managerid_fk
Seq_in_index: 1
Column_name: Manager_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

364
Comment:
*************************** 5. row ***************************
Field: DOB
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Social_SecurityNum
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Hire_Date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: End_Date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: PayRate
Type: decimal(15,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: Manager_ID
Type: int(11)
Collation: NULL
Null: YES
Key:

365
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Old_Managerid
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Field: Employement_Status
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Old_EmployeeID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 14. row ***************************
Field: Old_HRID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: Old_RoleID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Field: Old_StoreID
Type: int(11)

366
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: Old_DOB
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 18. row ***************************
Field: Old_SocialSecurityNum
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 19. row ***************************
Field: Old_HireDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 20. row ***************************
Field: Old_EndDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 21. row ***************************
Field: Old_PayRate
Type: decimal(15,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:

367
*************************** 22. row ***************************
Field: Old_EmployementStatus
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 23. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 24. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 25. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 26. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM HRRECORDSAUDITG
*************************** 1. row ***************************
Table: hrrecordsaudit
Non_unique: 0
Key_name: PRIMARY

368
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE MANAGEMENT;
+--------------+---------+------+-----+---------+----------------+
+--------------+---------+------+-----+---------+----------------+
| Manager_ID | int(11) | NO | PRI | NULL | auto_increment |
| Employee_ID | int(11) | NO | PRI | 0 | |
| Hire_Date | date | NO | | NULL | |
| Hire_EndDate | date | YES | | NULL | |
| store_id | int(11) | NO | PRI | NULL | |
+--------------+---------+------+-----+---------+----------------+
mysql> SHOW FULL COLUMNS FROM MANAGEMENTG
*************************** 1. row ***************************
Field: Manager_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Employee_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 3. row ***************************
Field: Hire_Date
Type: date
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Hire_EndDate

369
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: store_id
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM MANAGEMENTG
*************************** 1. row ***************************
Table: management
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Manager_ID
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: management
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: management
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Column_name: store_id
Collation: A
Cardinality: 0

370
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: management
Non_unique: 0
Key_name: Management_Magerid_uk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: management
Non_unique: 1
Key_name: Management_empid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: management
Non_unique: 1
Key_name: Management_storeid_fk
Seq_in_index: 1
Column_name: store_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: management
Non_unique: 1
Key_name: Management_HireDate_indx
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL

372
Comment:
*************************** 2. row ***************************
Field: Employee_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Store_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Hire_Date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Hire_EndDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Old_ManagerID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Type: int(11)
Collation: NULL
Null: YES
Key:

373
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Old_HireDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Old_HireEndDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: Old_StoreID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 12. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Audit_ID
Type: int(11)

374
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 14. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM MANAGEMENTAUDIT G
*************************** 1. row ***************************
Table: managementaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql>
mysql> DESCRIBE PRODUCT;
+---------------------+--------------+------+-----+---------+-------------
---+
|
+---------------------+--------------+------+-----+---------+-------------
---+
| Product_ID | int(11) | NO | PRI | NULL |
auto_increment |
| Product_CategoryID | int(11) | NO | PRI | 0 |
|
| Product_Name | varchar(110) | NO | MUL | NULL |
|
| Product_Color | varchar(35) | YES | | NULL |
|
| Product_Size | varchar(35) | YES | | NULL |
|
| Product_Weight | varchar(45) | YES | | NULL |
|

375
| Product_Description | text | YES | | NULL |
|
+---------------------+--------------+------+-----+---------+-------------
---+
mysql> SHOW FULL COLUMNS FROM PRODUCT G
*************************** 1. row ***************************
Field: Product_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Product_CategoryID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 3. row ***************************
Field: Product_Name
Type: varchar(110)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Product_Color
Type: varchar(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Product_Size
Type: varchar(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:

376
*************************** 6. row ***************************
Field: Product_Weight
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Product_Description
Type: text
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM PRODUCT G
*************************** 1. row ***************************
Table: product
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Product_ID
Collation: A
Cardinality: 49
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: product
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Product_CategoryID
Collation: A
Cardinality: 49
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: product
Non_unique: 0
Key_name: product_prodid_uk
Seq_in_index: 1

377
Collation: A
Cardinality: 49
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: product
Non_unique: 0
Key_name: Product_ID
Seq_in_index: 1
Collation: A
Cardinality: 49
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: product
Non_unique: 1
Key_name: product_ProductName_indx
Seq_in_index: 1
Column_name: Product_Name
Collation: A
Cardinality: 49
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: product
Non_unique: 1
Key_name: product_prodcategoid_fk
Seq_in_index: 1
Collation: A
Cardinality: 12
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE PRODUCTAUDIT;
+------------------------+--------------+------+-----+-------------------
+-----------------------------+
Extra |

379
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Product_Name
Type: varchar(110)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Product_Color
Type: varchar(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Product_Size
Type: varchar(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Product_Weight
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Product_Description
Type: text
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************

380
Field: Old_ProductID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Old_ProductCategoryID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: Old_Product_Name
Type: varchar(110)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Old_ProductColor
Type: varchar(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Field: Old_ProductSize
Type: varchar(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Old_Product_Weight
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:

381
Comment:
*************************** 14. row ***************************
Field: Old_ProductDescription
Type: text
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 16. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 18. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM PRODUCTAUDIT G
*************************** 1. row ***************************
Table: productaudit

382
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 49
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE PRODUCTCATEGORY;
+----------------------+-------------+------+-----+---------+-------------
---+
|
+----------------------+-------------+------+-----+---------+-------------
---+
| Product_CategoryID | int(11) | NO | PRI | NULL |
auto_increment |
| Category_Name | varchar(45) | NO | MUL | NULL |
|
| Category_Description | text | YES | | NULL |
|
+----------------------+-------------+------+-----+---------+-------------
---+
mysql> SHOW FULL COLUMNS FROM PRODUCTCATEGORY G
*************************** 1. row ***************************
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Category_Name
Type: varchar(45)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Category_Description
Type: text
Null: YES

384
|
auto_increment |
|
+-----------------------+-------------+------+-----+-------------------+--
---------------------------+
mysql> SHOW FULL COLUMNS FROM PRODUCTCATEGORYAUDIT G
*************************** 1. row ***************************
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: Category_Name
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Category_Description
Type: text
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Old_ProdCategoryID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Old_CategoName
Type: varchar(45)

385
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Old_CategoDescription
Type: text
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 8. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 10. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:

387
Field: Payment_CopName
Type: varchar(50)
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: Payment_Type
Type: enum('VISA','MasterCard','Discovery','American Express','NA')
Null: NO
Key: MUL
Default: NULL
Extra:
*************************** 4. row ***************************
Field: Payment_CreatedDate
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: Payment_Status
Type: varchar(25)
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: PaymentCard_Number
Type: int(11)
Null: NO
Key: MUL
Default: NULL
Extra:
*************************** 7. row ***************************
Field: Receipt_ID
Type: int(11)
Null: NO
Key: PRI
Default: NULL
Extra:
*************************** 8. row ***************************
Field: payment_expdate
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: authorized_Amount
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:

388
mysql> SHOW FULL COLUMNS FROM PAYMENTCOP G
*************************** 1. row ***************************
Field: Payment_id
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Type: varchar(50)
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Payment_Type
Type: enum('VISA','MasterCard','Discovery','American Express','NA')
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Type: varchar(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Type: int(11)
Collation: NULL
Null: NO
Key: MUL

389
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Receipt_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM PAYMENTCOP G
*************************** 1. row ***************************
Table: paymentcop
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: Payment_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: paymentcop
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Receipt_ID

390
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: paymentcop
Non_unique: 0
Key_name: paymentcop_paymentid_uk
Seq_in_index: 1
Column_name: Payment_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: paymentcop
Non_unique: 1
Key_name: PaymentCop_Receiptid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: paymentcop
Non_unique: 1
Key_name: paycop_cardnumber_idx
Seq_in_index: 1
Column_name: PaymentCard_Number
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: paymentcop
Non_unique: 1
Key_name: paycop_paytype_indx
Seq_in_index: 1
Column_name: Payment_Type
Collation: A

391
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE PAYMENTCOPAUDIT G
*************************** 1. row ***************************
Field: Payment_id
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: Payment_Type
Type: char(35)
Null: YES
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Type: varchar(25)
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: Receipt_ID
Type: int(11)
Null: YES
Key:

392
Default: NULL
Extra:
*************************** 8. row ***************************
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: old_paymentExDate
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 10. row ***************************
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 11. row ***************************
Field: Old_Paymentid
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 12. row ***************************
Field: Old_PayCopName
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
*************************** 13. row ***************************
Field: Old_PaynebtType
Type: char(35)
Null: YES
Key:
Default: NULL
Extra:
*************************** 14. row ***************************
Field: Old_PayCreatedDate
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 15. row ***************************
Field: Old_PaymentStatus
Type: varchar(25)
Null: YES
Key:
Default: NULL

393
Extra:
*************************** 16. row ***************************
Field: Old_PayCardNum
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 17. row ***************************
Field: Old_AuthordAmt
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 18. row ***************************
Field: old_ReceiptID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 19. row ***************************
Field: Audit_Date
Type: timestamp
Null: NO
Key:
*************************** 20. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
*************************** 21. row ***************************
Field: Audit_ID
Type: int(11)
Null: NO
Key: PRI
Default: NULL
*************************** 22. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
mysql> SHOW FULL COLUMNS FROM PAYMENTCOPAUDIT G
*************************** 1. row ***************************
Field: Payment_id
Type: int(11)
Collation: NULL

394
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Payment_Type
Type: char(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Type: varchar(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************

395
Field: Receipt_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: old_paymentExDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Old_Paymentid
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Field: Old_PayCopName
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:

396
Comment:
*************************** 13. row ***************************
Field: Old_PaynebtType
Type: char(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 14. row ***************************
Field: Old_PayCreatedDate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: Old_PaymentStatus
Type: varchar(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Field: Old_PayCardNum
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: Old_AuthordAmt
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 18. row ***************************
Type: int(11)
Collation: NULL
Null: YES

397
Key:
Default: NULL
Extra:
Comment:
*************************** 19. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 20. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 21. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 22. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM PAYMENTCOPAUDIT G
*************************** 1. row ***************************
Table: paymentcopaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL

398
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE SHIPMENT;
+-----------------+---------------+------+-----+---------+----------------
+
|
+-----------------+---------------+------+-----+---------+----------------
+
| shipping_id | int(11) | NO | PRI | NULL | auto_increment
|
| shipper_id | int(11) | NO | PRI | 0 |
|
| state_id | int(11) | YES | | NULL |
|
| Receipt_ID | int(11) | YES | | NULL |
|
| ship_date | date | YES | | NULL |
|
| ship_enddate | date | YES | | NULL |
|
| receive_date | date | YES | | NULL |
|
| Name | varchar(55) | NO | MUL | NULL |
|
| Address | varchar(55) | NO | MUL | NULL |
|
| City | varchar(55) | NO | MUL | NULL |
|
| shipping_amount | decimal(10,0) | NO | | NULL |
|
| shipping_type | char(35) | NO | | NULL |
|
+-----------------+---------------+------+-----+---------+----------------
+
mysql>
mysql> SHOW FULL COLUMNS FROM SHIPMENT G
*************************** 1. row ***************************
Field: shipping_id
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: shipper_id
Type: int(11)

399
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 3. row ***************************
Field: state_id
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Receipt_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: ship_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: ship_enddate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: receive_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:

400
*************************** 8. row ***************************
Field: Name
Type: varchar(55)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Address
Type: varchar(55)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: City
Type: varchar(55)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: shipping_amount
Type: decimal(10,0)
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Field: shipping_type
Type: char(35)
Null: NO
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM SHIPMENT G
*************************** 1. row ***************************
Table: shipment
Non_unique: 0
Key_name: PRIMARY

401
Seq_in_index: 1
Column_name: shipping_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: shipment
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: shipper_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: shipment
Non_unique: 0
Key_name: shipment_shipmentid_uk
Seq_in_index: 1
Column_name: shipping_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: shipment
Non_unique: 1
Key_name: shipment_shipperid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: shipment
Non_unique: 1
Key_name: shipment_shipname_indx
Seq_in_index: 1

402
Column_name: Name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: shipment
Non_unique: 1
Key_name: shipment_address_indx
Seq_in_index: 1
Column_name: Address
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: shipment
Non_unique: 1
Key_name: shipment_city_indx
Seq_in_index: 1
Column_name: City
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE SHIPMENTAUDIT;
+------------------+---------------+------+-----+-------------------+-----
------------------------+
Extra |
+------------------+---------------+------+-----+-------------------+-----
------------------------+
| shipping_id | int(11) | YES | | NULL |
|
| shipper_id | int(11) | YES | | NULL |
|
| state_id | int(11) | YES | | NULL |
|
|
| ship_date | date | YES | | NULL |
|

404
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: shipper_id
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: state_id
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Receipt_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: ship_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: ship_enddate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: receive_date
Type: date

405
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Address
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: City
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: shipping_amount
Type: decimal(10,0)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Field: shipping_type
Type: char(35)
Null: YES
Key:
Default: NULL
Extra:
Comment:

406
*************************** 13. row ***************************
Field: old_shippingid
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 14. row ***************************
Field: old_shipperid
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: old_stateid
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: old_shipdate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 18. row ***************************
Field: old_shipenddate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL

407
Extra:
Comment:
*************************** 19. row ***************************
Field: old_receivedate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 20. row ***************************
Field: old_Name
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 21. row ***************************
Field: old_Address
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 22. row ***************************
Field: old_City
Type: varchar(55)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 23. row ***************************
Field: old_shippingamt
Type: decimal(10,0)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 24. row ***************************
Field: old_shippingtype
Type: char(35)

408
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 25. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 26. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 27. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 28. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM SHIPMENTAUDIT G
*************************** 1. row ***************************
Table: shipmentaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 0

409
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE SHIPPINGCO;
+--------------+-------------+------+-----+---------+----------------+
+--------------+-------------+------+-----+---------+----------------+
| shipper_id | int(11) | NO | PRI | NULL | auto_increment |
| Shipper_Name | varchar(50) | YES | MUL | NULL | |
| status | char(25) | YES | | NULL | |
| Create_date | date | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
mysql> SHOW FULL COLUMNS FROM SHIPPINGCO G
*************************** 1. row ***************************
Field: shipper_id
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Shipper_Name
Type: varchar(50)
Null: YES
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: status
Type: char(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Create_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL

411
| old_Create_date | date | YES | | NULL |
|
|
auto_increment |
|
+-----------------+-------------+------+-----+-------------------+--------
---------------------+
mysql> SHOW FULL COLUMNS FROM SHIPPINGCOAUDIT G
*************************** 1. row ***************************
Field: shipper_id
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: Shipper_Name
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: status
Type: char(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Create_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Old_shipperid

412
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: old_ShipperName
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: old_status
Type: char(25)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: old_Create_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 10. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:

413
Comment:
*************************** 11. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 12. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM SHIPPINGCOAUDIT G
*************************** 1. row ***************************
Table: shippingcoaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> DESCRIBE STORE;
+---------------+-------------+------+-----+---------+----------------+
+---------------+-------------+------+-----+---------+----------------+
| Store_ID | int(11) | NO | PRI | NULL | auto_increment |
| Store_StateID | int(11) | NO | PRI | 0 | |
| Store_Name | varchar(65) | NO | MUL | NULL | |
| Store_Type | varchar(34) | NO | MUL | NULL | |
| Store_Address | varchar(65) | NO | | NULL | |
| Store_City | varchar(45) | NO | MUL | NULL | |
| Store_Phone | varchar(45) | NO | | NULL | |

414
| Store_Status | varchar(30) | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
mysql> SHOW FULL COLUMNS FROM STOREG
*************************** 1. row ***************************
Field: Store_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Store_StateID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 3. row ***************************
Field: Store_Name
Type: varchar(65)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Store_Type
Type: varchar(34)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Store_Address
Type: varchar(65)
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Store_City

415
Type: varchar(45)
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Store_Phone
Type: varchar(45)
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Store_Status
Type: varchar(30)
Null: NO
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM STOREG
*************************** 1. row ***************************
Table: store
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: store
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Store_StateID
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE

416
Comment:
Index_comment:
*************************** 3. row ***************************
Table: store
Non_unique: 0
Key_name: store_storeid_uk
Seq_in_index: 1
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: store
Non_unique: 1
Key_name: store_storestateid_fk
Seq_in_index: 1
Column_name: Store_StateID
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: store
Non_unique: 1
Key_name: store_StoreName_indx
Seq_in_index: 1
Column_name: Store_Name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: store
Non_unique: 1
Key_name: store_StoreType_indx
Seq_in_index: 1
Column_name: Store_Type
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:

417
Index_comment:
*************************** 7. row ***************************
Table: store
Non_unique: 1
Key_name: store_StoreCity_indx
Seq_in_index: 1
Column_name: Store_City
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE STOREAUDITG
*************************** 1. row ***************************
Field: Store_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: Store_Name
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: Store_Type
Type: varchar(34)
Null: YES
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: Store_City
Type: varchar(45)
Null: YES

418
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: Store_Phone
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
*************************** 8. row ***************************
Field: Store_Status
Type: varchar(30)
Null: YES
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: Old_StoreID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 10. row ***************************
Field: Old_StorStateID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 11. row ***************************
Field: Old_StorName
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
*************************** 12. row ***************************
Field: Old_StorType
Type: varchar(34)
Null: YES
Key:
Default: NULL
Extra:
*************************** 13. row ***************************
Field: Old_StorAddress
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
*************************** 14. row ***************************
Field: Old_StorCity
Type: varchar(45)
Null: YES
Key:

419
Default: NULL
Extra:
*************************** 15. row ***************************
Field: old_StorPhone
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
*************************** 16. row ***************************
Field: old_StorStatus
Type: varchar(30)
Null: YES
Key:
Default: NULL
Extra:
*************************** 17. row ***************************
Field: Audit_Date
Type: timestamp
Null: NO
Key:
*************************** 18. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
*************************** 19. row ***************************
Field: Audit_ID
Type: int(11)
Null: NO
Key: PRI
Default: NULL
*************************** 20. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
mysql>
mysql> DESCRIBE STOREAUDIT;
+-----------------+-------------+------+-----+-------------------+--------
---------------------+
|
+-----------------+-------------+------+-----+-------------------+--------
---------------------+
|

421
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Store_Name
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Store_Type
Type: varchar(34)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Store_City
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Store_Phone
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************

422
Field: Store_Status
Type: varchar(30)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Old_StoreID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: Old_StorStateID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Old_StorName
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Field: Old_StorType
Type: varchar(34)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Old_StorAddress
Type: varchar(65)
Null: YES
Key:
Default: NULL
Extra:

423
Comment:
*************************** 14. row ***************************
Field: Old_StorCity
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: old_StorPhone
Type: varchar(45)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Field: old_StorStatus
Type: varchar(30)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 17. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 18. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 19. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO

424
Key: PRI
Default: NULL
Comment:
*************************** 20. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql>
mysql> SHOW INDEXES FROM STOREAUDITG
*************************** 1. row ***************************
Table: storeaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE STATECODES;
+---------------+---------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-----------------------------------------------------------+------+-----+-
--------+----------------+
| Field | Type
| Null | Key | Default | Extra |
+---------------+---------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------

425
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-----------------------------------------------------------+------+-----+-
--------+----------------+
| State_ID | int(11)
| NO | PRI | NULL | auto_increment |
| Country_Code | int(11)
| NO | PRI | 0 | |
| State_Name |
enum('Alabama','Alaska','Arizona','Arkansas','California','Colorado','Conn
ecticut','Delaware','Florida','Georgia','Hawaii','Idaho','ILLINOIS','India
na','Iowa','Kansas','Kentucky','Louisiana','Maine','Maryland','Massachuset
ts','Michigan','Minnesota','Mississippi','Missouri','Montana','Nebraska','
Nevada','New Hampshire','New Jersey','New Mexico','New York','North
Carolina','North Dakota','Ohio','Oklahoma','Pennsylvania','Rhode
Island','South Carolina','South
Dakota','Tennessee','Texas','Utah','Vermont','Virginia','Washington','West
Virginia','Wisconsin','Wyoming','District of Columbia','Alberta','British
Columbia','Manitoba','New Brunswick','Newfoundland-Larb','Northwest
Territories','Nova Scotia','Nunavut','Ontario','Prince Edward
Island','Quebec','Saskatchewan','Yukon') | NO | MUL | NULL |
|
| State_Abbrv |
enum('AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN'
,'IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NV','NH','NJ
','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','V
A','WA','WV','WI','WY','DC','AB','BC','MB','NB','NL','NT','NS','NU','ON','
PE','QC','SK','YT')
| State_TaxRate | int(11)
+---------------+---------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-----------------------------------------------------------+------+-----+-
--------+----------------+
mysql> DESCRIBE STATECODESG
*************************** 1. row ***************************
Field: State_ID
Type: int(11)
Null: NO
Key: PRI
Default: NULL
*************************** 2. row ***************************
Field: Country_Code
Type: int(11)

426
Null: NO
Key: PRI
Default: 0
Extra:
*************************** 3. row ***************************
Field: State_Name
Type:
Island','Quebec','Saskatchewan','Yukon')
Null: NO
Key: MUL
Default: NULL
Extra:
*************************** 4. row ***************************
Field: State_Abbrv
Type:
PE','QC','SK','YT')
Null: NO
Key: MUL
Default: NULL
Extra:
*************************** 5. row ***************************
Field: State_TaxRate
Type: int(11)
Null: NO
Key: MUL
Default: NULL
Extra:
mysql> SHOW FULL COLUMNS FROM STATECODESG
*************************** 1. row ***************************
Field: State_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 2. row ***************************
Field: Country_Code

427
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 3. row ***************************
Field: State_Name
Type:
Island','Quebec','Saskatchewan','Yukon')
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: State_Abbrv
Type:
PE','QC','SK','YT')
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Type: int(11)
Collation: NULL
Null: NO
Key: MUL
Default: NULL
Extra:
Comment:

428
mysql> SHOW INDEXES FROM STATECODESG
*************************** 1. row ***************************
Table: statecodes
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: statecodes
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: statecodes
Non_unique: 0
Key_name: statecodes_statecode_uk
Seq_in_index: 1
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: statecodes
Non_unique: 1
Key_name: Statecode_countrycode_fk
Seq_in_index: 1
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

429
*************************** 5. row ***************************
Table: statecodes
Non_unique: 1
Key_name: statecodes_statename_indx
Seq_in_index: 1
Column_name: State_Name
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: statecodes
Non_unique: 1
Key_name: statecodes_StateAbbrv_indx
Seq_in_index: 1
Column_name: State_Abbrv
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: statecodes
Non_unique: 1
Key_name: statecodes_TaxRate_indx
Seq_in_index: 1
Column_name: State_TaxRate
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE STATECODESAUDIT;
+-----------------+--------------+------+-----+-------------------+-------
----------------------+
|
+-----------------+--------------+------+-----+-------------------+-------
----------------------+
|
| Country_Code | int(11) | YES | | NULL |
|
| State_Name | varchar(110) | YES | | NULL |
|

431
*************************** 4. row ***************************
Field: State_Abbrv
Type: varchar(10)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Type: int(11)
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Field: Old_StateID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Old_CountryCode
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: Old_StateName
Type: varchar(110)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Old_State_Abbrv
Type: varchar(10)
Null: YES
Key:
Default: NULL

432
Extra:
Comment:
*************************** 10. row ***************************
Field: Old_StTaxRate
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 12. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 14. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM STATECODESAUDITG
*************************** 1. row ***************************

433
Table: statecodesaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 39
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE RECEIPT;
+------------------+--------------+------+-----+---------+----------------
+
|
+------------------+--------------+------+-----+---------+----------------
+
| Receipt_ID | int(11) | NO | PRI | NULL | auto_increment
|
| Employee_ID | int(11) | NO | PRI | 0 |
|
| Customer_ID | int(11) | NO | PRI | 0 |
|
| Store_ID | int(11) | NO | PRI | 0 |
|
| Product_ID | int(11) | NO | PRI | 0 |
|
| Receipt_Amount | decimal(8,2) | NO | | NULL |
|
| Receipt_Tax | decimal(8,2) | NO | | NULL |
|
| QTY | int(11) | NO | | NULL |
|
| Receipt_FinalAmt | decimal(8,2) | NO | | NULL |
|
| receipt_date | date | YES | | NULL |
|
+------------------+--------------+------+-----+---------+----------------
+
mysql> SHOW FULL COLUMNS FROM RECEIPTG
*************************** 1. row ***************************
Field: Receipt_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:

434
*************************** 2. row ***************************
Field: Employee_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 3. row ***************************
Field: Customer_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 4. row ***************************
Field: Store_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 5. row ***************************
Field: Product_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: 0
Extra:
Comment:
*************************** 6. row ***************************
Field: Receipt_Amount
Type: decimal(8,2)
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Receipt_Tax
Type: decimal(8,2)
Collation: NULL
Null: NO
Key:
Default: NULL

435
Extra:
Comment:
*************************** 8. row ***************************
Field: QTY
Type: int(11)
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Field: Receipt_FinalAmt
Type: decimal(8,2)
Collation: NULL
Null: NO
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: receipt_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM RECEIPTG
*************************** 1. row ***************************
Table: receipt
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: receipt
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Collation: A

436
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: receipt
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 3
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: receipt
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 4
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: receipt
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 5
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: receipt
Non_unique: 0
Key_name: Receipt_Receiptid_uk
Seq_in_index: 1
Collation: A
Cardinality: 0

437
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: receipt
Non_unique: 1
Key_name: receipt_customerid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: receipt
Non_unique: 1
Key_name: receipt_Employeeid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: receipt
Non_unique: 1
Key_name: receipt_storeid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 10. row ***************************
Table: receipt
Non_unique: 1
Key_name: receipt_productid_fk
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL

438
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
mysql> DESCRIBE RECEIPTAUDITG
*************************** 1. row ***************************
Field: Receipt_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 2. row ***************************
Field: Employee_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: Customer_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: Store_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: Product_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: Receipt_Tax
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:

439
*************************** 8. row ***************************
Field: QTY
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 10. row ***************************
Field: Old_ReceiptID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 11. row ***************************
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 12. row ***************************
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 13. row ***************************
Field: Old_Store_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 14. row ***************************
Field: Old_Product_ID
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 15. row ***************************
Field: Old_Receipt_Amount
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 16. row ***************************

440
Field: Old_Receipt_Tax
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 17. row ***************************
Field: Old_QTY
Type: int(11)
Null: YES
Key:
Default: NULL
Extra:
*************************** 18. row ***************************
Field: Old_ReceiptFinalAmt
Type: decimal(8,2)
Null: YES
Key:
Default: NULL
Extra:
*************************** 19. row ***************************
Field: Audit_Date
Type: timestamp
Null: NO
Key:
*************************** 20. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
*************************** 21. row ***************************
Field: Audit_ID
Type: int(11)
Null: NO
Key: PRI
Default: NULL
*************************** 22. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
*************************** 23. row ***************************
Field: receipt_date
Type: date
Null: YES
Key:
Default: NULL
Extra:
*************************** 24. row ***************************
Field: old_receiptdate

441
Type: date
Null: YES
Key:
Default: NULL
Extra:
mysql> DESCRIBE RECEIPTAUDIT;
+---------------------+--------------+------+-----+-------------------+---
--------------------------+
Extra |
+---------------------+--------------+------+-----+-------------------+---
--------------------------+
|
|
| Customer_ID | int(11) | YES | | NULL |
|
|
| Product_ID | int(11) | YES | | NULL |
|
| Receipt_Amount | decimal(8,2) | YES | | NULL |
|
| Receipt_Tax | decimal(8,2) | YES | | NULL |
|
| QTY | int(11) | YES | | NULL |
|
| Receipt_FinalAmt | decimal(8,2) | YES | | NULL |
|
| Old_ReceiptID | int(11) | YES | | NULL |
|
|
| Old_CustomerID | int(11) | YES | | NULL |
|
| Old_Store_ID | int(11) | YES | | NULL |
|
| Old_Product_ID | int(11) | YES | | NULL |
|
| Old_Receipt_Amount | decimal(8,2) | YES | | NULL |
|
| Old_Receipt_Tax | decimal(8,2) | YES | | NULL |
|
| Old_QTY | int(11) | YES | | NULL |
|
| Old_ReceiptFinalAmt | decimal(8,2) | YES | | NULL |
|
|
auto_increment |

442
|
| receipt_date | date | YES | | NULL |
|
| old_receiptdate | date | YES | | NULL |
|
+---------------------+--------------+------+-----+-------------------+---
--------------------------+
mysql> SHOW FULL COLUMNS FROM RECEIPTAUDITG
*************************** 1. row ***************************
Field: Receipt_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 2. row ***************************
Field: Employee_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 3. row ***************************
Field: Customer_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 4. row ***************************
Field: Store_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 5. row ***************************
Field: Product_ID
Type: int(11)
Collation: NULL
Null: YES
Key:

443
Default: NULL
Extra:
Comment:
*************************** 6. row ***************************
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 7. row ***************************
Field: Receipt_Tax
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 8. row ***************************
Field: QTY
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 9. row ***************************
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 10. row ***************************
Field: Old_ReceiptID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 11. row ***************************
Type: int(11)

444
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 12. row ***************************
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 13. row ***************************
Field: Old_Store_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 14. row ***************************
Field: Old_Product_ID
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 15. row ***************************
Field: Old_Receipt_Amount
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 16. row ***************************
Field: Old_Receipt_Tax
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:

445
*************************** 17. row ***************************
Field: Old_QTY
Type: int(11)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 18. row ***************************
Field: Old_ReceiptFinalAmt
Type: decimal(8,2)
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 19. row ***************************
Field: Audit_Date
Type: timestamp
Collation: NULL
Null: NO
Key:
Comment:
*************************** 20. row ***************************
Field: Audit_User
Type: varchar(50)
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 21. row ***************************
Field: Audit_ID
Type: int(11)
Collation: NULL
Null: NO
Key: PRI
Default: NULL
Comment:
*************************** 22. row ***************************
Field: Action_Type
Type: char(20)
Null: YES
Key:
Default: NULL

446
Extra:
Comment:
*************************** 23. row ***************************
Field: receipt_date
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
*************************** 24. row ***************************
Field: old_receiptdate
Type: date
Collation: NULL
Null: YES
Key:
Default: NULL
Extra:
Comment:
mysql> SHOW INDEXES FROM RECEIPTAUDITG
*************************** 1. row ***************************
Table: receiptaudit
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

477
Data Verification Regular Tables

481
Data Verification Audit Tables

488
Based on the defined privileges, this user will have privileges to select, insert, delete and update records
in any tables in the HowardR Schema including the audit as illustrated above. Privilleges have not yet
been defined.

501
Due to server problems, the auditing triggers were dropped

556
I forget to specify employee id on the uderlaying triggers used to audit the shipment table.

563
Revised Database Dictinoary

570
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table
space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1

571
Type = System managed space
Contents = System Temporary data
State = 0x0000
Normal
Tablespace ID = 2
Name = USERSPACE1
Contents = All permanent data. Large table
space.
State = 0x0000
Normal
Tablespace ID = 3
Name = SYSTOOLSPACE
space.
State = 0x0000
Normal
Tablespaces for Current Database

572
Tablespace ID = 0
Name = SYSCATSPACE
Contents = All permanent data. Regular table
space.
State = 0x0000
Normal
Total pages = 32768
Useable pages = 32764
Used pages = 25916
Free pages = 6848
High water mark (pages) = 25916
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Normal
Total pages = 1
Useable pages = 1
Used pages = 1

573
Free pages = Not applicable
High water mark (pages) = Not applicable
Tablespace ID = 2
Name = USERSPACE1
space.
State = 0x0000
Normal
Total pages = 8192
Used pages = 96
Free pages = 8064
Tablespace ID = 3
Name = SYSTOOLSPACE

574
space.
State = 0x0000
Normal
Total pages = 8192
Used pages = 144
Free pages = 8044

576
Applications Connections Creation

579
Database Schema and Objecs Creations

581
1 create table RETAIL.countrycode
2 (Country_Code Integer not null GENERATED ALWAYS AS IDENTITY(START WITH
100,
INCREMENT BY 5) ,
3 Country_Name Varchar(60) not null ,
4 Country_Abbrv Varchar(12) not null,
5 constraint countrycode_countrycode_pk primary key(Country_Code)
6* );
1 create table RETAIL.countrycode
2 (Country_Code Integer not null GENERATED ALWAYS AS IDENTITY(START WITH
100,
INCREMENT BY 5) ,
3 Country_Name Varchar(60) not null ,
4 Country_Abbrv Varchar(12) not null,
5 constraint countrycode_countrycode_pk primary key(Country_Code)
6* );
DB250000I: The command completed successfully.
1 create table RETAIL.CountryCodesAudit
3 Country_Name Varchar(60),
4 Country_Abbrv Varchar(12),
6 Audit_User varchar(50),
8 old_countryname Varchar(60),
9 old_countryabbrv Varchar(12),
10 Audit_ID integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH
110,
INCREMENT BY 5),
12* );
1 create table RETAIL.CountryCodesAudit
3 Country_Name Varchar(60),
4 Country_Abbrv Varchar(12),
8 old_countryname Varchar(60),
9 old_countryabbrv Varchar(12),
110,
INCREMENT BY 5),
12* );
1 Create table RETAIL.statecodes

582
2 (State_ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH
220,
INCREMENT BY 15) ,
3 Country_Code integer not null,
4 State_Name Varchar(45) not null,
5 State_Abbrv Varchar(10) not null,
6 State_TaxRate integer not null,
Country_Code),
references
10 RETAIL.CountryCode(Country_Code)
11* )
1 Create table RETAIL.statecodes
2 (State_ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH
220,
INCREMENT BY 15) ,
3 Country_Code integer not null,
4 State_Name Varchar(45) not null,
5 State_Abbrv Varchar(10) not null,
6 State_TaxRate integer not null,
Country_Code),
references
10 RETAIL.CountryCode(Country_Code)
11* )
1 Create table RETAIL.StateCodesAudit
4 State_Name Varchar(45),
5 State_Abbrv Varchar(10),
9 Audit_ID integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 210,
INCREMENT BY 15),
13 Old_State_Name Varchar(45),
14 Old_State_Abbrv Varchar(10),
15* Old_State_TaxRate integer )
1 Create table RETAIL.StateCodesAudit
4 State_Name Varchar(45),
5 State_Abbrv Varchar(10),
9 Audit_ID integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 210,

583
INCREMENT BY 15),
13 Old_State_Name Varchar(45),
14 Old_State_Abbrv Varchar(10),
15* Old_State_TaxRate integer )
1 create table RETAIL.HRRoles
2 (Role_ID integer not null GENERATED ALWAYS AS IDENTITY(START WITH
0421,
INCREMENT BY 25) ,
3 Role_Title varchar(45),
4 Role_status varchar(45),
6* )
1 create table RETAIL.HRRoles
2 (Role_ID integer not null GENERATED ALWAYS AS IDENTITY(START WITH
0421,
INCREMENT BY 25) ,
6* )
1 create table RETAIL.HRRolesAudit
2 (Role_ID integer,
5 Old_RoleID integer,
6 Old_RoleTitle varchar(45),
7 Old_Role_status varchar(45),
10 Audit_ID integer NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH
2257,
INCREMENT BY 15) ,
12* )
1 create table RETAIL.Employee
2 (Employee_ID integer not null GENERATED ALWAYS AS IDENTITY(START WITH
0443, INCREMENT BY 25) ,
3 First_Name varchar(55) not null,
4 Middle_Name varchar(55),
5 Last_Name Varchar(55) not null ,
6 Address varchar(55) not null,
7 City Varchar(45) not null,
8 State_ID Integer not null,

584
9 Phone Varchar(45) not null ,
10 constraint employee_empid_pk primary key(Employee_ID,State_ID),
11 constraint employee_empid_uk unique (Employee_ID),
12 constraint employee_stateid_ck check (State_ID in
(‘WA’,’IL’,’IN’,’TX’,’CO’,’NA’)),
13 constraint employee_stateid_fk foreign key(state_id) references
RETAIL.statecodes(state_id)
14* )
1 create table RETAIL.EmployeeAudit
2 (Employee_ID integer,
3 First_Name varchar(55),
4 Middle_Name varchar(55),
5 Last_Name Varchar(55),
6 Address varchar(55),
7 City Varchar(45),
8 State_ID Integer,
9 Phone Varchar(45),
309,
INCREMENT BY 15) ,
14 Old_Employee_ID integer,
15 Old_First_Name varchar(55),
16 Old_Middle_Name varchar(55),
17 Old_Last_Name Varchar(55),
18 Old_Address varchar(55),
19 Old_City Varchar(45),
20 Old_State_ID Integer,
21 Old_Phone Varchar(45)
22* )
1 Create table RETAIL.HRRecords
2 (Employee_ID Integer not null ,
3 HR_ID Integer not null GENERATED ALWAYS AS IDENTITY(START WITH 0323,
INCREMENT BY 20) ,
4 Role_ID Integer not null,
5 Store_ID integer not null,
7 Social_SecurityNum Integer not null,
8 Hire_Date Date not null,
9 End_Date Date,
10 PayRate numeric(15,2) not null,
11 Employement_Status varchar(45) not null,
key(Employee_ID,HR_ID,Role_ID,Store_ID),

585
RETAIL.Employee(Employee_ID) ,
RETAIL.HRRoles(Role_ID)
16* )
1 Create table RETAIL.HRRecordsAudit
2 (Employee_ID Integer,
3 HR_ID Integer,
4 Role_ID Integer,
5 Store_ID integer,
6 DOB Date,
7 Social_SecurityNum Integer,
8 Hire_Date Date,
9 End_Date Date,
10 PayRate numeric(15,2),
11 Employement_Status varchar(45),
12 Old_EmployeeID Integer,
13 Old_HRID Integer,
14 Old_RoleID Integer,
15 Old_StoreID integer,
16 Old_DOB Date,
17 Old_SocialSecurityNum Integer,
19 Old_EndDate Date,
20 Old_PayRate numeric(15,2),
21 Old_EmployementStatus varchar(45),
108,
INCREMENT BY 15) ,
26* )
1 create table RETAIL.Management
2 (Manager_ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH
0123,
INCREMENT BY 21),
3 Employee_ID integer not null,
6 constraint Management_Magerid_pk primary key(Manager_ID,Employee_ID),
RETAIL.Employee(Employee_ID)
9* )
1 create table RETAIL.Management
2 (Manager_ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH
0123,

586
INCREMENT BY 21),
6 constraint Management_Magerid_pk primary key(Manager_ID,Employee_ID),
RETAIL.Employee(Employee_ID)
9* )
1 create table RETAIL.ManagementAudit
2 (Manager_ID integer,
4 Hire_Date Date,
6 Old_ManagerID integer,
7 Old_EmployeeID integer,
9 Old_HireEndDate Date,
12 Audit_ID integer NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 120,
INCREMENT BY 15),
14* )
1 create table RETAIL.store
2 (Store_ID Integer not null GENERATED ALWAYS AS IDENTITY (START WITH
025,
INCREMENT BY 22),
3 Store_StateID Integer not null,
4 Store_ManagerID Integer not null,
5 Store_Name Varchar(65) not null,
6 Store_Type Varchar(34) not null,
7 Store_Address Varchar(65) not null,
8 Store_City Varchar(45) not null,
9 Store_Phone Varchar(45) not null,
10 Store_Status Varchar(30) not null,
11 constraint store_storeid_pk primary
key(Store_ID,Store_StateID,Store_ManagerID),
12 constraint store_storeiid_uk unique(Store_ID),
13 constraint store_stateid_fk foreign key(Store_StateID) references
RETAIL.statecodes
14 (State_ID),
15 constraint store_storeMagerID_FK Foreign key (Store_ManagerID)
references
RETAIL.Management(Manager_ID)
16* )

587
1 create table RETAIL.customer
2 (Customer_ID integer not null GENERATED ALWAYS AS IDENTITY(START WITH
0643,
INCREMENT BY 15),
3 State_ID integer not null,
4 First_Name Varchar(50) not null,
5 Middle_Name Varchar(50),
6 Last_Name Varchar(50) not null,
7 Address Varchar(50) not null,
8 City Varchar(45) not null,
9 Phone Varchar(45) not null,
RETAIL.statecodes(State_ID)
14* )
1 create table RETAIL.CustomerAudit
2 (Customer_ID integer,
3 State_ID integer,
4 First_Name Varchar(50),
5 Middle_Name Varchar(50),
6 Last_Name Varchar(50),
7 Address Varchar(50),
8 City Varchar(45),
9 Phone Varchar(45),
10 DOB Date,
12 Old_StateID integer,
13 Old_FirstName Varchar(50),
14 Old_MiddleName Varchar(50),
15 Old_LastName Varchar(50),
16 Old_Address Varchar(50),
17 Old_City Varchar(45),
18 Old_Phone Varchar(45),
19 Old_DOB Date,
22 Audit_ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH
130,
INCREMENT BY 20) ,
24* )
1 create table RETAIL.ProductCategory
2 (Product_CategoryID Integer not null GENERATED ALWAYS AS IDENTITY(START
WITH
0192, INCREMENT BY 19),
3 Category_Name Varchar(45) not null,

588
6* )
1 create table RETAIL.ProductCategory
2 (Product_CategoryID Integer not null GENERATED ALWAYS AS IDENTITY(START
WITH
0192, INCREMENT BY 19),
3 Category_Name Varchar(45) not null,
6* )
1 create table RETAIL.product
2 (Product_ID Integer not null GENERATED ALWAYS AS IDENTITY (START WITH
0292,
INCREMENT BY 15),
3 Product_CategoryID Integer not null,
4 Product_Name Varchar(50) not null,
5 Product_Color Varchar(35),
6 Product_Size Varchar(35),
7 Product_Weight Varchar(45),
9 constraint Product_Prodid_pk primary
key(Product_ID,Product_CategoryID)
10 ,constraint product_prodid_uk unique (Product_ID),
11 constraint product_prodcategoid_fk foreign key(Product_CategoryID)
references RETAIL.ProductCategory(Product_CategoryID)
12* )
1 create table RETAIL.PaymentCop
2 (Payment_id integer not null GENERATED ALWAYS AS IDENTITY (START WITH
0242, INCREMENT BY 14) ,
3 Payment_CopName Varchar(50) not null,
4 Payment_Type Varchar(45) not null,
6 Payment_Status varchar(25),
7 PaymentCard_Number numeric not null,
8 Payment_ExpDate Date,
9 constraint PaymentCop_paymentid_pk primary key(Payment_id),
10 constraint Paymentcop_PaymentType_ck check (Payment_Type
in(‘VISA’,’MasterCard’, ‘Discovery’,’American
Express’,’NA’,’visa’,’mastercard’,’discovery’,’american express’))
11* )
1 create table RETAIL.Receipt
2 (Receipt_ID Integer not null GENERATED ALWAYS AS IDENTITY (START WITH
0232,
INCREMENT BY 20),

589
3 State_ID integer not null,
5 Customer_ID integer not null,
6 Store_ID integer not null,
7 Receipt_StateCode Integer not null,
8 Product_ID Integer not null,
9 Receipt_Amount decimal (8,2) not null,
10 Receipt_Tax decimal (8,2) not null,
11 QTY Integer not null,
12 Receipt_FinalAmt decimal (8,2) not null,
13 Payment_ID integer not null,
14 constraint Receipt_Receipid_pk primary
key(Receipt_ID,State_ID,Employee_ID,Customer_ID,Store_ID
15 ,Receipt_StateCode,Product_ID, Payment_ID),
16 constraint Receipt_Receiptid_uk unique (Receipt_ID),
17 constraint Receipt_customerid_fk foreign key(Customer_ID) references
RETAIL.Customer(Customer_ID),
18 constraint Receipt_Employeeid_fk foreign key(Employee_ID) references
RETAIL.Employee (Employee_ID),
19 constraint Receipt_Stateid_fk foreign key(State_ID) references
RETAIL.statecodes(state_id),
20 constraint Receipt_storeid_fk foreign key(Store_id) references
RETAIL.store(Store_id),
21 constraint Receipt_Productid_fk foreign key(Product_ID) references
RETAIL.Product (Product_ID),
22 constraint Receipt_paymentid_fk foreign key(Payment_ID) references
RETAIL.PaymentCOP(Payment_ID)
23* )
1 create table RETAIL.shippingco
2 (shipper_id integer not null GENERATED ALWAYS AS IDENTITY(START WITH
0242,
INCREMENT BY 25),
3 Shipper_Name Varchar(50) not null,
4 status char(25) not null,
5 Create_date date,
7* )
1 create table RETAIL.shippingco
2 (shipper_id integer not null GENERATED ALWAYS AS IDENTITY(START WITH
0242,
INCREMENT BY 25),
3 Shipper_Name Varchar(50) not null,
4 status char(25) not null,
5 Create_date date,
7* )
1 create table RETAIL.shipment
2 (shipping_id integer not null GENERATED ALWAYS AS IDENTITY (START WITH
0235,
INCREMENT BY 26),
3 shipper_id integer not null,
4 state_id integer not null,

590
5 Receipt_ID integer not null,
6 ship_date date,
9 Name varchar(55),
10 Address Varchar(55),
11 City varchar(55),
14 constraint 590tated590t_shippmentid_pk primary
15 constraint 590tated590t_shippermid_uk unique (shipping_id),
16 constraint 590tated590t_shipperid_fk foreign key(shipper_id)
references
RETAIL.shippingco(shipper_id),
17 CONSTRAINT SHIPMENT_RECEIPTID_FK FOREIGN KEY(RECEIPT_ID) REFERENCES
RETAIL.RECEIPT(RECEIPT_ID)
18* )

623
Database Physical Model
SHIPPINGCO
PK SHIPPER_ID INTEGER
I1 SHIPPER_NAME VARCHAR(50)
STATUS CHARACTER(25)
CREATE_DATE DATE
COUNTRYCODE
PK COUNTRY_CODE INTEGER
I1 COUNTRY_NAME VARCHAR(60)
I2 COUNTRY_ABBRV VARCHAR(12)
PRODUCTCATEGORY
PK PRODUCT_CATEGORYID INTEGER
I1 CATEGORY_NAME VARCHAR(45)
CATEGORY_DESCRIPTION CLOB (1M)
STATECODES
PK,U1 STATE_ID INTEGER
PK,FK1 COUNTRY_CODE INTEGER
I1 STATE_NAME VARCHAR(110)
I2 STATE_ABBRV VARCHAR(10)
I3 STATE_TAXRATE DECIMAL(8,2)
CUSTOMER
PK,U1 CUSTOMER_ID INTEGER
PK,FK1 STATE_ID INTEGER
I1 FIRST_NAME VARCHAR(50)
MIDDLE_NAME VARCHAR(50)
I2 LAST_NAME VARCHAR(50)
I4 ADDRESS VARCHAR(50)
I3 CITY VARCHAR(45)
PHONE VARCHAR(25)
DOB DATE
RECEIPT
PK,U1 RECEIPT_ID INTEGER
PK,FK1 EMPLOYEE_ID INTEGER
PK,FK4 CUSTOMER_ID INTEGER
PK,FK3 STORE_ID INTEGER
PK,FK2 PRODUCT_ID INTEGER
RECEIPT_AMOUNT DECIMAL(8,2)
RECEIPT_TAX DECIMAL(8,2)
QTY INTEGER
RECEIPT_FINALAMT DECIMAL(8,2)
RECEIPT_DATE DATE
HRROLES
PK ROLE_ID INTEGER
ROLE_TITLE VARCHAR(45)
ROLE_STATUS VARCHAR(45)
MANAGEMENT
PK,U1 MANAGER_ID INTEGER
I1 HIRE_DATE DATE
HIRE_ENDDATE DATE
STORE
PK,U1 STORE_ID INTEGER
PK,FK1 STORE_STATEID INTEGER
I1 STORE_NAME VARCHAR(65)
I2 STORE_TYPE VARCHAR(34)
STORE_ADDRESS VARCHAR(65)
I3 STORE_CITY VARCHAR(45)
STORE_PHONE VARCHAR(45)
STORE_STATUS VARCHAR(30)
PAYMENTCOP
PK,U1 PAYMENT_ID INTEGER
PK,FK1 RECEIPT_ID INTEGER
PAYMENT_COPNAME VARCHAR(50)
PAYMENT_TYPE VARCHAR(45)
PAYMENT_STATUS VARCHAR(25)
I1 PAYMENTCARD_NUMBER DECIMAL(18,0)
AUTHORIZED_AMT DECIMAL(8,2)
EMPLOYEE
PK,U1 EMPLOYEE_ID INTEGER
LAST_NAME VARCHAR(55)
ADDRESS VARCHAR(55)
I2 CITY VARCHAR(45)
PHONE VARCHAR(25)
SHIPMENT
PK,U8,U9,U1,U2,U5,U4,U3,U6,U7 SHIPPING_ID INTEGER
PK,FK1 SHIPPER_ID INTEGER
SHIP_DATE DATE
SHIP_ENDDATE DATE
RECEIVE_DATE DATE
I3 NAME VARCHAR(55)
I2 CITY VARCHAR(55)
SHIPPING_AMOUNT DECIMAL(5,0)
SHIPPING_TYPE CHARACTER(35)
HRRECORDS
PK,U1 HR_ID INTEGER
PK,FK2 ROLE_ID INTEGER
PK,FK4 MANAGER_ID INTEGER
I1 DOB DATE
I2 SOCIAL_SECURITYNUM INTEGER
HIRE_DATE DATE
END_DATE DATE
PAYRATE DECIMAL(15,2)
EMPLOYEMENT_STATUS VARCHAR(45)
PRODUCT
PK,U1 PRODUCT_ID INTEGER
PK,FK1 PRODUCT_CATEGORYID INTEGER
I1 PRODUCT_NAME VARCHAR(110)
PRODUCT_COLOR VARCHAR(35)
PRODUCT_SIZE VARCHAR(35)
PRODUCT_WEIGHT VARCHAR(45)
PRODUCT_DESCRIPTION CLOB (1M)

624
Database Dictionary
desc retail.customer;
TABLE - CUSTOMER
**************************************************************************
******
Name Data Type Type schema Length Scale Nulls
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
CUSTOMER_ID INTEGER SYSIBM 4 0 N Not
STATE_ID INTEGER SYSIBM 4 0 N Not
FIRST_NAME VARCHAR SYSIBM 50 0 N Not
MIDDLE_NAME VARCHAR SYSIBM 50 0 Y Not
LAST_NAME VARCHAR SYSIBM 50 0 N Not
ADDRESS VARCHAR SYSIBM 50 0 N Not
CITY VARCHAR SYSIBM 45 0 N Not
PHONE VARCHAR SYSIBM 25 0 N Not
DOB DATE SYSIBM 4 0 N Not
**************************************************************************
******
Desc retail.customeraudit;
TABLE - CUSTOMERAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
CUSTOMER_ID INTEGER SYSIBM 4 0 Y Not
STATE_ID INTEGER SYSIBM 4 0 Y Not
FIRST_NAME VARCHAR SYSIBM 50 0 Y Not
LAST_NAME VARCHAR SYSIBM 50 0 Y Not
ADDRESS VARCHAR SYSIBM 50 0 Y Not
CITY VARCHAR SYSIBM 45 0 Y Not
PHONE VARCHAR SYSIBM 45 0 Y Not
DOB DATE SYSIBM 4 0 Y Not
OLD_CUSTOMERID INTEGER SYSIBM 4 0 Y Not
OLD_STATEID INTEGER SYSIBM 4 0 Y Not
OLD_FIRSTNAME VARCHAR SYSIBM 50 0 Y Not
OLD_MIDDLENAME VARCHAR SYSIBM 50 0 Y Not
OLD_LASTNAME VARCHAR SYSIBM 50 0 Y Not
OLD_ADDRESS VARCHAR SYSIBM 50 0 Y Not
OLD_CITY VARCHAR SYSIBM 45 0 Y Not
OLD_PHONE VARCHAR SYSIBM 45 0 Y Not
OLD_DOB DATE SYSIBM 4 0 Y Not
AUDIT_DATE TIMESTAMP SYSIBM 10 6 Y Not
AUDIT_USER VARCHAR SYSIBM 50 0 Y Not
AUDIT_ID INTEGER SYSIBM 4 0 N Not
ACTION_TYPE CHARACTER SYSIBM 20 0 Y Not

625
**************************************************************************
******
DESC retail.countrycode;
TABLE - COUNTRYCODE
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
COUNTRY_CODE INTEGER SYSIBM 4 0 N Not
COUNTRY_NAME VARCHAR SYSIBM 60 0 N Not
COUNTRY_ABBRV VARCHAR SYSIBM 12 0 N Not
**************************************************************************
******
TABLE - COUNTRYCODESAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
COUNTRY_CODE INTEGER SYSIBM 4 0 Y Not
COUNTRY_NAME VARCHAR SYSIBM 60 0 Y Not
COUNTRY_ABBRV VARCHAR SYSIBM 12 0 Y Not
OLD_COUNTRYCODE INTEGER SYSIBM 4 0 Y Not
OLD_COUNTRYNAME VARCHAR SYSIBM 60 0 Y Not
OLD_COUNTRYABBRV VARCHAR SYSIBM 12 0 Y Not
**************************************************************************
******
desc retail.statecodes;
TABLE - STATECODES
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----

626
COUNTRY_CODE INTEGER SYSIBM 4 0 N Not
STATE_NAME VARCHAR SYSIBM 110 0 N Not
STATE_ABBRV VARCHAR SYSIBM 10 0 N Not
STATE_TAXRATE DECIMAL SYSIBM 8 2 N Not
**************************************************************************
******
desc retail.statecodesaudit;
TABLE - STATECODESAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
COUNTRY_CODE INTEGER SYSIBM 4 0 Y Not
STATE_NAME VARCHAR SYSIBM 110 0 Y Not
STATE_ABBRV VARCHAR SYSIBM 10 0 Y Not
STATE_TAXRATE INTEGER SYSIBM 4 0 Y Not
OLD_STATE_ID INTEGER SYSIBM 4 0 Y Not
OLD_COUNTRY_CODE INTEGER SYSIBM 4 0 Y Not
OLD_STATE_NAME VARCHAR SYSIBM 45 0 Y Not
OLD_STATE_ABBRV VARCHAR SYSIBM 10 0 Y Not
OLD_STATE_TAXRATE INTEGER SYSIBM 4 0 Y Not
**************************************************************************
******
Desc retail.product;
TABLE - PRODUCT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
PRODUCT_ID INTEGER SYSIBM 4 0 N Not
PRODUCT_CATEGORYID INTEGER SYSIBM 4 0 N Not
PRODUCT_NAME VARCHAR SYSIBM 110 0 N Not
PRODUCT_COLOR VARCHAR SYSIBM 35 0 Y Not
PRODUCT_SIZE VARCHAR SYSIBM 35 0 Y Not
PRODUCT_WEIGHT VARCHAR SYSIBM 45 0 Y Not
PRODUCT_DESCRIPTION CLOB SYSIBM 1048576 0 Y Not
**************************************************************************
******

627
Desc retail.productaudit;
TABLE - PRODUCTAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
PRODUCT_ID INTEGER SYSIBM 4 0 Y Not
PRODUCT_CATEGORYID INTEGER SYSIBM 4 0 Y Not
PRODUCT_NAME VARCHAR SYSIBM 110 0 Y Not
PRODUCT_COLOR VARCHAR SYSIBM 35 0 Y Not
PRODUCT_SIZE VARCHAR SYSIBM 35 0 Y Not
PRODUCT_WEIGHT VARCHAR SYSIBM 45 0 Y Not
PRODUCT_DESCRIPTION CLOB SYSIBM 1048576 0 Y Not
OLD_PRODUCTID INTEGER SYSIBM 4 0 Y Not
OLD_PRODCATEGORYID INTEGER SYSIBM 4 0 Y Not
OLD_PRODNAME VARCHAR SYSIBM 110 0 Y Not
OLD_PRODCOLOR VARCHAR SYSIBM 35 0 Y Not
OLD_PRODSIZE VARCHAR SYSIBM 35 0 Y Not
OLD_PRODWEIGHT VARCHAR SYSIBM 45 0 Y Not
OLD_PRODDESCRIPTION CLOB SYSIBM 1048576 0 Y Not
**************************************************************************
******
DESC retail.productcategory;
TABLE - PRODUCTCATEGORY
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
PRODUCT_CATEGORYID INTEGER SYSIBM 4 0 N Not
CATEGORY_NAME VARCHAR SYSIBM 45 0 N Not
CATEGORY_DESCRIPTIO CLOB SYSIBM 1048576 0 Y Not
N
**************************************************************************
******
DESC retail.productcategoryaudit;
TABLE - PRODUCTCATEGORYAUDIT
**************************************************************************
******

628
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
PRODUCT_CATEGORYID INTEGER SYSIBM 4 0 Y Not
CATEGORY_NAME VARCHAR SYSIBM 45 0 Y Not
CATEGORY_DESCRIPTIO CLOB SYSIBM 1048576 0 Y Not
N
OLD_PRODCATEGORYID INTEGER SYSIBM 4 0 Y Not
OLD_CATEGORYNAME VARCHAR SYSIBM 45 0 Y Not
OLD_CATEGORYDESCRIP CLOB SYSIBM 1048576 0 Y Not
TION
**************************************************************************
******
desc retail.hrroles;
TABLE - HRROLES
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
ROLE_ID INTEGER SYSIBM 4 0 N Not
ROLE_TITLE VARCHAR SYSIBM 45 0 Y Not
ROLE_STATUS VARCHAR SYSIBM 45 0 Y Not
**************************************************************************
******
desc retail.hrrolesaudit;
TABLE - HRROLESAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
ROLE_ID INTEGER SYSIBM 4 0 Y Not
ROLE_TITLE VARCHAR SYSIBM 45 0 Y Not
ROLE_STATUS VARCHAR SYSIBM 45 0 Y Not
OLD_ROLEID INTEGER SYSIBM 4 0 Y Not
OLD_ROLETITLE VARCHAR SYSIBM 45 0 Y Not
OLD_ROLE_STATUS VARCHAR SYSIBM 45 0 Y Not

629
**************************************************************************
******
desc retail.hrrecords;
TABLE - HRRECORDS
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
EMPLOYEE_ID INTEGER SYSIBM 4 0 N Not
HR_ID INTEGER SYSIBM 4 0 N Not
ROLE_ID INTEGER SYSIBM 4 0 N Not
STORE_ID INTEGER SYSIBM 4 0 N Not
DOB DATE SYSIBM 4 0 N Not
SOCIAL_SECURITYNUM INTEGER SYSIBM 4 0 N Not
HIRE_DATE DATE SYSIBM 4 0 N Not
END_DATE DATE SYSIBM 4 0 Y Not
PAYRATE DECIMAL SYSIBM 15 2 N Not
EMPLOYEMENT_STATUS VARCHAR SYSIBM 45 0 N Not
MANAGER_ID INTEGER SYSIBM 4 0 N Not
**************************************************************************
******
desc retail.hrrecordsaudit;
TABLE - HRRECORDSAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
EMPLOYEE_ID INTEGER SYSIBM 4 0 Y Not
HR_ID INTEGER SYSIBM 4 0 Y Not
ROLE_ID INTEGER SYSIBM 4 0 Y Not
STORE_ID INTEGER SYSIBM 4 0 Y Not
DOB DATE SYSIBM 4 0 Y Not
SOCIAL_SECURITYNUM INTEGER SYSIBM 4 0 Y Not
HIRE_DATE DATE SYSIBM 4 0 Y Not
END_DATE DATE SYSIBM 4 0 Y Not
PAYRATE DECIMAL SYSIBM 15 2 Y Not
EMPLOYEMENT_STATUS VARCHAR SYSIBM 45 0 Y Not
MANAGER_ID INTEGER SYSIBM 4 0 Y Not
OLD_MANAGERID INTEGER SYSIBM 4 0 Y Not
OLD_EMPLOYEEID INTEGER SYSIBM 4 0 Y Not
OLD_HRID INTEGER SYSIBM 4 0 Y Not
OLD_ROLEID INTEGER SYSIBM 4 0 Y Not
OLD_STOREID INTEGER SYSIBM 4 0 Y Not

630
OLD_DOB DATE SYSIBM 4 0 Y Not
OLD_SOCIALSECURITYN INTEGER SYSIBM 4 0 Y Not
UM
OLD_HIREDATE DATE SYSIBM 4 0 Y Not
OLD_ENDDATE DATE SYSIBM 4 0 Y Not
OLD_PAYRATE DECIMAL SYSIBM 15 2 Y Not
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
OLD_EMPLOYEMENTSTAT VARCHAR SYSIBM 45 0 Y Not
US
**************************************************************************
******
desc retail.management;
TABLE - MANAGEMENT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
MANAGER_ID INTEGER SYSIBM 4 0 N Not
HIRE_DATE DATE SYSIBM 4 0 N Not
HIRE_ENDDATE DATE SYSIBM 4 0 Y Not
**************************************************************************
******
desc retail.managementaudit;
TABLE - MANAGEMENTAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
MANAGER_ID INTEGER SYSIBM 4 0 Y Not
HIRE_DATE DATE SYSIBM 4 0 Y Not
HIRE_ENDDATE DATE SYSIBM 4 0 Y Not
OLD_MANAGERID INTEGER SYSIBM 4 0 Y Not

631
OLD_HIREDATE DATE SYSIBM 4 0 Y Not
OLD_HIREENDDATE DATE SYSIBM 4 0 Y Not
**************************************************************************
******
desc retail.employee;
TABLE - EMPLOYEE
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
FIRST_NAME VARCHAR SYSIBM 55 0 N Not
LAST_NAME VARCHAR SYSIBM 55 0 N Not
PHONE VARCHAR SYSIBM 25 0 N Not
**************************************************************************
******
desc retail.employeeaudit;
TABLE - EMPLOYEEAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
FIRST_NAME VARCHAR SYSIBM 55 0 Y Not
LAST_NAME VARCHAR SYSIBM 55 0 Y Not
PHONE VARCHAR SYSIBM 45 0 Y Not

632
OLD_EMPLOYEE_ID INTEGER SYSIBM 4 0 Y Not
OLD_FIRST_NAME VARCHAR SYSIBM 55 0 Y Not
OLD_MIDDLE_NAME VARCHAR SYSIBM 55 0 Y Not
OLD_LAST_NAME VARCHAR SYSIBM 55 0 Y Not
OLD_STATE_ID INTEGER SYSIBM 4 0 Y Not
OLD_PHONE VARCHAR SYSIBM 45 0 Y Not
**************************************************************************
******
Desc retail.receipt;
TABLE - RECEIPT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
RECEIPT_ID INTEGER SYSIBM 4 0 N Not
CUSTOMER_ID INTEGER SYSIBM 4 0 N Not
PRODUCT_ID INTEGER SYSIBM 4 0 N Not
RECEIPT_AMOUNT DECIMAL SYSIBM 8 2 N Not
RECEIPT_TAX DECIMAL SYSIBM 8 2 N Not
QTY INTEGER SYSIBM 4 0 N Not
RECEIPT_FINALAMT DECIMAL SYSIBM 8 2 N Not
RECEIPT_DATE DATE SYSIBM 4 0 Y Not
**************************************************************************
******
desc retail.receiptaudit;
TABLE - RECEIPTAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
RECEIPT_ID INTEGER SYSIBM 4 0 Y Not
CUSTOMER_ID INTEGER SYSIBM 4 0 Y Not
PRODUCT_ID INTEGER SYSIBM 4 0 Y Not
RECEIPT_AMOUNT DECIMAL SYSIBM 5 0 Y Not
RECEIPT_TAX DECIMAL SYSIBM 8 2 Y Not
QTY INTEGER SYSIBM 4 0 Y Not
RECEIPT_FINALAMT DECIMAL SYSIBM 5 0 Y Not
OLD_RECEIPTID INTEGER SYSIBM 4 0 Y Not

633
OLD_CUSTOMERID INTEGER SYSIBM 4 0 Y Not
OLD_PRODID INTEGER SYSIBM 4 0 Y Not
OLD_RECPTAMT DECIMAL SYSIBM 5 0 Y Not
OLD_RECEPTTAX DECIMAL SYSIBM 8 2 Y Not
OLD_QTY INTEGER SYSIBM 4 0 Y Not
OLD_RECPTFINALAMT DECIMAL SYSIBM 5 0 Y Not
RECEIPT_DATE DATE SYSIBM 4 0 Y Not
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
OLD_RECEIPTDATE DATE SYSIBM 4 0 Y Not
**************************************************************************
******
DESC retail.paymentcop;
TABLE - PAYMENTCOP
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
PAYMENT_ID INTEGER SYSIBM 4 0 N Not
PAYMENT_COPNAME VARCHAR SYSIBM 50 0 N Not
PAYMENT_TYPE VARCHAR SYSIBM 45 0 N Not
PAYMENT_CREATEDDARE DATE SYSIBM 4 0 Y Not
PAYMENT_STATUS VARCHAR SYSIBM 25 0 N Not
PAYMENTCARD_NUMBER DECIMAL SYSIBM 5 0 N Not
PAYMENT_EXPDATE DATE SYSIBM 4 0 N Not
AUTHORIZED_AMT DECIMAL SYSIBM 8 2 N Not
**************************************************************************
******
DESC retail.paymentcopaudit;
TABLE - PAYMENTCOPAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
PAYMENT_ID INTEGER SYSIBM 4 0 Y Not
PAYMENT_COPNAME VARCHAR SYSIBM 50 0 Y Not

634
PAYMENT_TYPE VARCHAR SYSIBM 45 0 Y Not
PAYMENT_CREATEDDATE DATE SYSIBM 4 0 Y Not
PAYMENT_STATUS VARCHAR SYSIBM 25 0 Y Not
PAYMENTCARD_NUMBER INTEGER SYSIBM 4 0 Y Not
PAYMENT_EXPDATE DATE SYSIBM 4 0 Y Not
OLD_PAYID INTEGER SYSIBM 4 0 Y Not
OLD_PAYCOPNAME VARCHAR SYSIBM 50 0 Y Not
OLD_PAYTYPE VARCHAR SYSIBM 45 0 Y Not
OLD_PAYCREATEDDATE DATE SYSIBM 4 0 Y Not
OLD_PAYSTATUS VARCHAR SYSIBM 25 0 Y Not
OLD_PAYCARD_NUMBER INTEGER SYSIBM 4 0 Y Not
OLDPAYMENT_EXPDATE DATE SYSIBM 4 0 Y Not
AUTHORIZED_AMT DECIMAL SYSIBM 8 2 Y Not
OLD_AUTHORIZEDAMT DECIMAL SYSIBM 8 2 Y Not
**************************************************************************
******
DESC retail.shipment;
TABLE - SHIPMENT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
SHIPPING_ID INTEGER SYSIBM 4 0 N Not
SHIPPER_ID INTEGER SYSIBM 4 0 N Not
SHIP_DATE DATE SYSIBM 4 0 N Not
SHIP_ENDDATE DATE SYSIBM 4 0 Y Not
RECEIVE_DATE DATE SYSIBM 4 0 Y Not
NAME VARCHAR SYSIBM 55 0 N Not
SHIPPING_AMOUNT DECIMAL SYSIBM 5 0 Y Not
SHIPPING_TYPE CHARACTER SYSIBM 35 0 Y Not
**************************************************************************
******
DESC retail.shipmentaudit;
TABLE - SHIPMENTAUDIT
**************************************************************************
******

635
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
SHIPPING_ID INTEGER SYSIBM 4 0 Y Not
SHIPPER_ID INTEGER SYSIBM 4 0 Y Not
SHIP_DATE DATE SYSIBM 4 0 Y Not
SHIP_ENDDATE DATE SYSIBM 4 0 Y Not
RECEIVE_DATE DATE SYSIBM 4 0 Y Not
NAME VARCHAR SYSIBM 55 0 Y Not
SHIPPING_AMOUNT DECIMAL SYSIBM 5 0 Y Not
SHIPPING_TYPE CHARACTER SYSIBM 35 0 Y Not
OLD_SHIPPINGID INTEGER SYSIBM 4 0 Y Not
OLD_SHIPPERID INTEGER SYSIBM 4 0 Y Not
OLD_STATEID INTEGER SYSIBM 4 0 Y Not
OLD_SHIPDATE DATE SYSIBM 4 0 Y Not
OLD_SHIPENDDATE DATE SYSIBM 4 0 Y Not
OLD_RECEIVEDATE DATE SYSIBM 4 0 Y Not
OLD_NAME VARCHAR SYSIBM 55 0 Y Not
OLD_SHIPPINGAMT DECIMAL SYSIBM 5 0 Y Not
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
OLD_SHIPPINGTYPE CHARACTER SYSIBM 35 0 Y Not
**************************************************************************
******
DESC retail.shippingco;
TABLE - SHIPPINGCO
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
SHIPPER_ID INTEGER SYSIBM 4 0 N Not
SHIPPER_NAME VARCHAR SYSIBM 50 0 N Not
STATUS CHARACTER SYSIBM 25 0 N Not
CREATE_DATE DATE SYSIBM 4 0 Y Not

636
**************************************************************************
******
DESC retail.shippingcoaudit;
TABLE - SHIPPINGCOAUDIT
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
SHIPPER_ID INTEGER SYSIBM 4 0 Y Not
SHIPPER_NAME VARCHAR SYSIBM 50 0 Y Not
STATUS CHARACTER SYSIBM 25 0 Y Not
CREATE_DATE DATE SYSIBM 4 0 Y Not
OLD_SHIPPERID INTEGER SYSIBM 4 0 Y Not
OLD_SHIPPERNAME VARCHAR SYSIBM 50 0 Y Not
OLD_STATUS CHARACTER SYSIBM 25 0 Y Not
OLD_CREATE_DATE DATE SYSIBM 4 0 Y Not
**************************************************************************
******
show user;
USER is "db2admin"
desc retail.store;
TABLE - STORE
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
STORE_STATEID INTEGER SYSIBM 4 0 N Not
STORE_NAME VARCHAR SYSIBM 65 0 N Not
STORE_TYPE VARCHAR SYSIBM 34 0 N Not
STORE_ADDRESS VARCHAR SYSIBM 65 0 N Not
STORE_CITY VARCHAR SYSIBM 45 0 N Not
STORE_PHONE VARCHAR SYSIBM 45 0 N Not
STORE_STATUS VARCHAR SYSIBM 30 0 N Not
**************************************************************************
******
desc retail.storeaudit;
TABLE - STOREAUDIT

637
**************************************************************************
******
Hidden
------------------- ------------- ------------- -------- ------- ----- ---
-----
STORE_STATEID INTEGER SYSIBM 4 0 Y Not
STORE_NAME VARCHAR SYSIBM 65 0 Y Not
STORE_TYPE VARCHAR SYSIBM 34 0 Y Not
STORE_ADDRESS VARCHAR SYSIBM 65 0 Y Not
STORE_CITY VARCHAR SYSIBM 45 0 Y Not
STORE_PHONE VARCHAR SYSIBM 45 0 Y Not
STORE_STATUS VARCHAR SYSIBM 30 0 Y Not
OLD_STORSTATEID INTEGER SYSIBM 4 0 Y Not
OLD_STORNAME VARCHAR SYSIBM 65 0 Y Not
OLD_STORTYPE VARCHAR SYSIBM 34 0 Y Not
OLD_STORADDRESS VARCHAR SYSIBM 65 0 Y Not
OLD_STORCITY VARCHAR SYSIBM 45 0 Y Not
OLD_STORPHONE VARCHAR SYSIBM 45 0 Y Not
OLD_STORSTATUS VARCHAR SYSIBM 30 0 Y Not
**************************************************************************
******
set echo off;

685
Database Verification-Regular Tables

691
Database Verification-Audit Tables

698
The store id for employee 518 and 543 belongs to the Chicago store id but the employee address
is in Washington D.C An update will be performed to update that employee store id and later in
the exercise, the city will be updated for the same employees with District of Columbia as their
city of residence instead of Washington using stored procedure.

699
Now I will verify the audit table to ensure the DML statements used to modify the
table were recorded as coded to assit DBA in recovery mode.
The stored procedure llustrated below will be utitlized to update those employee
city information. The employee id would be changed to another employee to
verify if the update statement will update the city of residence of that employee.

701
Instead of supplying the city information (hard coded) in the stored procedure, the city can be
passed through a parameter and employee city can be updated as illustrated.

704
In the following domenstration, I would utitlized Microsoft Visual Studio 2010 as a development
tool to create stored procedures and other scripts

738
Oracle PLSQL-DB2
To execute or run Oracle PLSQL codes in DB2, the oracle compatibility has to be enabled for the PLSQL
code to execute successfully as illustrated above. DB2 also offers TSQL for SYBASE and MYSQL
campatibllity

753
Revised Database Directinoary

760
Revised Physical Database Diagram
COUNTRYCODE
PRODUCTCATEGORY
CATEGORY_DESCRIPTION CLOB (1M)
CUSTOMER
I3 CITY VARCHAR(45)
PHONE VARCHAR(25)
DOB DATE
SHIPPINGCO
STATUS CHARACTER(25)
CREATE_DATE DATE
HRROLES
PK ROLE_ID INTEGER
ROLE_TITLE VARCHAR(45)
RECEIPT
QTY INTEGER
RECEIPT_DATE DATE
MANAGEMENT
PK,FK2,U1 MANAGER_ID INTEGER
I1 HIRE_DATE DATE
HIRE_ENDDATE DATE
STATECODES
I3 STATE_TAXRATE DECIMAL(8,2)
EMPLOYEE
LAST_NAME VARCHAR(55)
ADDRESS VARCHAR(55)
I2 CITY VARCHAR(45)
PHONE VARCHAR(25)
STORE
PAYMENTCOP
PAYMENT_ID INTEGER
FK1 RECEIPT_ID INTEGER
FK2 STORE_ID INTEGER
SHIPMENT
PK,U1 SHIPPING_ID INTEGER
FK2 RECEIPT_ID INTEGER
SHIP_DATE DATE
SHIP_ENDDATE DATE
RECEIVE_DATE DATE
I1 NAME VARCHAR(55)
I3 CITY VARCHAR(55)
SHIPPING_TYPE CHARACTER(35)
SHIP_QTY INTEGER
HRRECORDS
PK,U1 HR_ID INTEGER
I1 DOB DATE
HIRE_DATE DATE
END_DATE DATE
EMPLOYEMENT_STATUS VARCHAR(45)
PRODUCT
PRODUCT_DESCRIPTION CLOB (1M)
Howard Retail DB2 Revised Physical Diagram

768
Aplication User Account Creation
Oracle JDeveloper 12C

769
Microsoft Visual Studio 2010

804
HRRECORDS
PK,U1 HR_ID INTEGER
I1 DOB DATE
HIRE_DATE DATE
END_DATE DATE
EMPLOYEE_STATUS VARCHAR(45)
PRODUCT
PRODUCT_DESCRIPTION LONGVARCHAR
COUNTRYCODE
SHIPPINGCO
STATUS VARCHAR(25)
CREATE_DATE DATE
PRODUCTCATEGORY
Category_description LONGVARCHAR
CUSTOMER
I4 MIDDLE_NAME VARCHAR(50)
ADDRESS VARCHAR(50)
I1 CITY VARCHAR(45)
PHONE VARCHAR(25)
DOB DATE
STATECODES
STATE_TAXRATE DECIMAL(8,2)
HRROLES
PK ROLE_ID INTEGER
I1 ROLE_TITLE VARCHAR(45)
RECEIPT
QTY INTEGER
RECEIPT_DATE DATE
MANAGEMENT
PK,U1 MANAGER_ID INTEGER
I1 HIRE_DATE DATE
HIRE_ENDDATE DATE
STORE
EMPLOYEE
ADDRESS VARCHAR(55)
I1 CITY VARCHAR(45)
PHONE VARCHAR(25)
PAYMENTCOP
SHIPMENT
SHIP_DATE DATE
SHIP_ENDDATE DATE
RECEIVE_DATE DATE
I3 NAME VARCHAR(55)
I2 CITY VARCHAR(55)
SHIPPING_TYPE VARCHAR(35)
Database Dictinoary

855
There were errors with the pervious insert statements that were utitlized to
populate the management table. As result the entire table has to be deleted.

859
Data Verification-Regular Tables

865
Data Verification- Audit Tables

871
User Accounts Creation
Roles
Sybase Central

872
Verification
User Account Creation

887
Table Population and Data Mainpulation

895
Results Abridged
The user sunny was given the authority to change any table structure which he or she did by
executing the previous alter statement to decimal datatype which can be dangerous in a
production enivroment. An alternate was to use the convert and cast functions to change the
output of the money datatypes as illustrated below.
Results Abridged

912
Apparently the group by clause required for aggragate functions are not added automatically by
the application but manuely by the user.

916
For this view, I had to manually refreshed each time to get an accurate data ass illustrated below.

924
Not all receipt data were inserted into paymentcop table

938
I would utilized inreactive SQL to update the paymentcop table using the update statements in
the updatest.sql file as illustrated below.

944
Revised Physical Database Dictinoary

945
Revised Database Physical Diagram

946
HRRECORDS
PK,U1 HR_ID INTEGER
I1 DOB DATE
HIRE_DATE DATE
END_DATE DATE
EMPLOYEE_STATUS VARCHAR(45)
PRODUCT
PRODUCT_DESCRIPTION LONGVARCHAR
COUNTRYCODE
PRODUCTCATEGORY
Category_description LONGVARCHAR
CUSTOMER
I4 MIDDLE_NAME VARCHAR(50)
ADDRESS VARCHAR(50)
I1 CITY VARCHAR(45)
PHONE VARCHAR(25)
DOB DATE
SHIPPINGCO
STATUS VARCHAR(25)
CREATE_DATE DATE
HRROLES
PK ROLE_ID INTEGER
I1 ROLE_TITLE VARCHAR(45)
RECEIPT
QTY INTEGER
RECEIPT_DATE DATE
MANAGEMENT
PK,FK2,U1 MANAGER_ID INTEGER
I1 HIRE_DATE DATE
HIRE_ENDDATE DATE
STATECODES
STATE_TAXRATE DECIMAL(8,2)
EMPLOYEE
ADDRESS VARCHAR(55)
I1 CITY VARCHAR(45)
PHONE VARCHAR(25)
STORE
PAYMENTCOP
SHIPMENT
PK,FK5 store_id INTEGER
PK,FK1 Employee_ID INTEGER
SHIP_DATE DATE
SHIP_ENDDATE DATE
RECEIVE_DATE DATE
I3 NAME VARCHAR(55)
I2 CITY VARCHAR(55)
SHIPPING_TYPE VARCHAR(35)
SHIP_QTY INTEGER
SQL AnyWhere 16 Howard Retail Revised Physical Diagram

947
Remote Servers
Connecting to DB2 HOWARDR

954
Connecting to Oracle HOWARDR

DB Develop 2 Oracle 12c, DB2, MYSQL, SQL Anywhere 16

More Related Content

What's hot

Viewers also liked

Similar to DB Develop 2 Oracle 12c, DB2, MYSQL, SQL Anywhere 16

More from Sunny U Okoro

Recently uploaded

DB Develop 2 Oracle 12c, DB2, MYSQL, SQL Anywhere 16