Based on Hair Salon ERD
1 Drop statements for all objects in the lab project (drop existing objects first so that you can rerun your script without error).
/* Drop tables */
DROP SEQUENCE table_Employess ;
Drop table_Service;
Drop table_Client;
Drop table_Payment;
Drop table_Inventory;
Drop table_Salon Booking;
2 Create statements for all tables and keys of your project.
/* Create 5 tables */
CREATE TABLE Employee
( Emp_ID int NOT NULL,
Emp_Titlevarchar(20) NOT NULL,
Emp_Addressvarchar(30),
Emp_City varchar(20),
Emp_State varchar(2),
Emp_Zip int NOT NULL,
Emp_FirstName varchar(30)
Emp_Last varchar(20)
Emp_Phone int NOT NULL
Emp_Email varchar(30)
Emp_Liscense Number nchar(20)
Hire_Date date NOT NULL
CONSTRAINT Employee_pk PRIMARY KEY (EMP_ID)
);
CREATE TABLE Client
( Client_ID int NOT NULL,
Client_FirstNamevarchar(20) NOT NULL,
Client_LastName varchar(30),
Client_Phone int NOT NULL,
Client_Email varchar(20)
Client_Address nchar(20)
City varchar(20)
State varchar(2)
Zip numeric NOT NULL,
Client_PaymentRecord varchar(100) NOT NULL,
CONSTRAINT Client PRIMARY KEY (Client_ID)
);
CREATE TABLE Inventory
( Product_ID int NOT NULL,
ProductCostPerCasedecimal NOT NULL,
Product_Name varchar(50),
Amt_Onhand numeric(10)
Last Date_Chk date NOT NULL,
LastTime_Chk timestamp with time zone NOT NULL,
CONSTRAINT Inventory_pk PRIMARY KEY (Product_ID),
CONSTRAINT Inventory_pk PRIMARY KEY (Product_Name)
);
CREATE TABLE Service
( Service_ID int NOT NULL,
Service_Name varchar(20) NOT NULL,
Service_fee decimal(10) NOT NULL,
CONSTRAINT Service pk PRIMARY KEY (Service_ID)
);
CREATE TABLE Payment
( Payment_Record verchar(40) NOT NULL,
Payment_Credit numeric(10) NOT NULL,
Payment_Cash decimal(50) NOT NULL,
CONSTRAINT Payment_pk PRIMARY KEY (Payment_Record)
);
CREATE TABLE Salon Booking
( Booking_No numeric NOT NULL,
Client_ID int NOT NULL,
Emp_ID int NOT NULL,
Service_ID int NOT NULL,
Est_Fee decimal NOT NULL,
Date date NOT NULL,
Time time with the zone NOT NULL,
CONSTRAINT Salon Booking_pk PRIMARY KEY (Booking_No)
CONSTRAINT fk_Salon Booking
FOREIGN KEY (Client_ID)
REFERENCES supplier(Booking_No)
);
3 Create indexes on natural key columns, foreign keys, and other columns that will be frequently used as query filters (i.e., Columns in the “WHERE” clause).
CREATE INDEX IDX__Employee_Employee_Name
ON Employee (Emp_ID,Emp_Title,Emp_FirstName, Emp_Last);
SELECT "column_name"
FROM "Employee"
WHERE Employee_ID = Employee_ID(Employee)
AND Emp_Title=’IDX__Employee_Employee_Name’
AND Emp_FirstName=’IDX__Employee_Employee_Name’
AND Emp_Last=’IDX__Employee_Employee_Name’;
CREATE INDEX IDX__Client_Client_Name
ON Client (Client_ID,Client_FirstName, Client_LastName,Client_Email);
CREATE INDEX IDX__Inventory_Product_ID
ON Inventory (Product_ID,Product_Name);
CREATE INDEX IDX__Service_Service_ID
ON Service (Service_ID,Service_Name);
CREATE INDEX IDX__Payment_Payment_Information
ON Payment (Payment_Record,Payee_Name)
CREATE INDEX IDX__Salon_Booking_Booking_Information
ON Salon Booking (Booking_No,Client_ID,Employee_ID,Service_ID);
4 Create at least two views.
5 Create at least two sequences.
6 Create at least two triggers.
7.Check content of the catalog/data dictionary.
Item 7 (above) should contain SQL SELECT statements to query DBMS catalog/data dictionary (such as user_objects or user_tables) to demonstrate all objects are created successfully.
