Improving Electronic Store Database Design - Essay Example

Improving Electronic Store Database Design Insert Insert Improving Database Design through Normalization The tables are already in 1, 2, and 3rd Normal Forms. They are as follows: Customer Table (Customer ID, Customer Name, Address, Residence) Sales Order Table (Sale ID, Date, Units Sold, Customer ID, Social_Security_Number) Employees Table (Social_ Security_ Number, First_Name, Last_Name, Address, City, State, Zip_Code, Birth_Date, Full_Time, Part_Time, Salary, Date_Hire) The tables exist in the three normalization forms because they meet the criteria to existing in those states.

That is to say, in the first normal form, there is no more than one row of data that has a repeating group of information (Date, 2005). A different table is created for every set of data that is related as well as identifying every set of related data with a primary key. In the second normal form, sets of values containing multiple records should exist in varied tables. The tables are then related using a foreign key. In third normal form, all fields depend on the primary key otherwise they should be eliminated.

SQL Statements for the tables in Oracle According to (Kyte, 2010), CREATE TABLE Employees ( Social_ Security_ Number number(50) NOT NULL, First_Name varchar2(50) NOT NULL, Last_Name varchar2(50) NOT NULL, Address varchar2(50) NOT NULL, City varchar2(50) NOT NULL, State varchar2(50) NOT NULL, Zip_Code varchar2(50) NOT NULL, Birth_Date Date NOT NULL, Full_Time varchar2(50) NOT NULL, Part_Time varchar2(50) NOT NULL, Salary varchar2(50) NOT NULL, Date_Hire Date NOT NULL, CONSTRAINT social_pk PRIMARY KEY (Social_ Security_ Number) ); CREATE TABLE Customer (Customer_ID number (10) NOT NULL, Customer Name varchar2 (50) NOT NULL, Address varchar2 (50) NOT NULL, Residence varchar2 (50) NOT NULL, CONSTRAINT customer_pk PRIMARY KEY (Customer_ID) ); CREATE TABLE Sales_Order (Sale_ID number (10) NOT NULL, Date Name Date (50) NOT NULL, Units_Sold varchar2 (50) NOT NULL, Customer_ID number (10) NOT NULL, Social_Security_Number number (10) NOT NULL, CONSTRAINT Sales_pk PRIMARY KEY (Sale_ID) ); SQL Statement to drop the tables in Oracle DROP TABLE Employees PURGE; DROP TABLE Customer PURGE; DROP TABLE Sales_Order PURGE; The use of PURGE is to ensure that the space covered by the tables are released and completely removed from the recycle bin so that there is no option to recover it.

References Date, C. (2005). Database in depth. Sebastopol, CA: OReilly. Kyte, T. (2010). Expert Oracle database architecture. [Berkeley, Calif.]: Apress.