Furniture Tables

<PRE>

--Furniture Data Base System

--

--The following code drops and then creates five tables:

-- crremp - a table of employees for Alexander's Furniture

-- crrcust - a table of customers for Alexander's Furniture

-- crritem - a table of the inventory for Alexander's Furniture

-- crrpur - a table of purchases for Alexander's Furniture

-- crrsales - a table of sales for Alexander's Furniture

--

drop table crrpur;

drop table crrsales;

drop table crrcust;

drop table crremp;

drop table crritem;

--

--The employee table includes empid as a primary key.

--

create table crremp (Lastname Varchar2(15),

Firstname Varchar2(10),

Address Varchar2(20),

City Varchar2(15),

State Char(2),

Zip Char(5),

Age Number(3),

phone varchar2(12),

empid number(4),

gender char(1),

hire_date date,

salary number(8,2),

constraint empid_pk Primary Key (empid));

--

--The item table includes item_code as a primary key.

--

create table crritem (item_code char(2),

item_name varchar2(15),

item_cost number(7,2),

item_price number(7,2),

item_quantity number(8),

constraint item_code_pk Primary Key (item_code));

--

--The customer table includes cnum as a primary key.

--It also includes empid as a foreign key related to the employee table.

--

create table crrcust (cnum number(4),

lname varchar2(15),

fname varchar2(10),

birthday date,

address varchar2(20),

City Varchar2(10),

State Char(2),

Zip Char(5),

empid number(4),

rep varchar2(15),

phone varchar2(12),

constraint cnum_pk Primary Key (cnum),

constraint empid_fk Foreign Key (empid) References crremp(empid));

--

--The sales table includes invoice_no as a primary key.

--It also includes empid as a foreign key related to the employee table

--and item_code as a foreign key related to the item table.

--

create table crrsales (item_code char(2),

invoice_no number(4),

sales_date date,

quantity number(4),

item_price number(7,2),

tot_price number(7,2),

empid number(4),

constraint inv_pk Primary Key (invoice_no),

constraint emp_fk Foreign Key (empid) References crremp(empid),

constraint item_fk Foreign Key (item_code) References crritem(item_code));

--

--The purchases table includes po_no as a primary key.

--It also includes item_code as a foreign key related to the item table.

--

create table crrpur (po_no number(4),

arr_date date,

item_code char(2),

quantity number(4),

tot_cost number(9,2),

constraint po_pk Primary Key (po_no),

constraint it_fk Foreign Key (item_code) References crritem(item_code));

--

--The program also creates four sequences to identify the empid

--customer number, item code, invoice number and purchase order number.

--

drop sequence empno;

drop sequence cust;

drop sequence inv;

drop sequence po;

create sequence empno start with 1000;

create sequence cust start with 5000;

create sequence inv start with 1;

create sequence po start with 1;

</PRE>