A hairdresser needs a database with information about customers, services, reservations, employees and their shifts. Based on this information we are supposed be able to:

2. Program PL/SQL procedure for the following task:

2.1.insert a new customer; 2.2.make a customer reservation to order a service to a particular hairdresser; 2.3.check how many times a given person has been a customer during the last two years; 2.4.give a given customer a discount; 2.5.prepare a receipt for the service; 2.6.calculate salary for all employees (based on the fulfilled services); 2.7. One of the hairdressers is sick. For each of the services assigned to him or her find a replacement - first we should look for those hairdressers that have a shift scheduled for that day.

3. Tasks related to triggers

-- Table: Customer CREATE TABLE Customer ( CustomerID integer NOT NULL, Name varchar2(50) NOT NULL, PhoneNumber integer NOT NULL, Reservation_ReservationID integer NOT NULL, CONSTRAINT Customer_pk PRIMARY KEY (CustomerID) ) ;

-- Table: Discount CREATE TABLE Discount ( DiscountID integer NOT NULL, DiscountAmount integer NOT NULL, DiscountStartTime timestamp NOT NULL, DiscountEndTime timestamp NOT NULL, Customer_CustomerID integer NOT NULL, CONSTRAINT Discount_pk PRIMARY KEY (DiscountID) ) ;

-- Table: Employee CREATE TABLE Employee ( EmployeeID integer NOT NULL, Name varchar2(50) NOT NULL, "Number" integer NOT NULL, CONSTRAINT Employee_pk PRIMARY KEY (EmployeeID) ) ;

-- Table: Receipts CREATE TABLE Receipts ( ReceiptsID integer NOT NULL, Amount integer NOT NULL, "Date" date NOT NULL, Services_ServicesID integer NOT NULL, Customer_CustomerID integer NOT NULL, CONSTRAINT Receipts_pk PRIMARY KEY (ReceiptsID) ) ;

-- Table: Reservation CREATE TABLE Reservation ( ReservationID integer NOT NULL, Day varchar2(50) NOT NULL, "Date" date NOT NULL, Time integer NOT NULL, Customer_CustomerID integer NOT NULL, Employee_EmployeeID integer NOT NULL, CONSTRAINT Reservation_pk PRIMARY KEY (ReservationID) ) ;

-- Table: Services CREATE TABLE Services ( ServicesID integer NOT NULL, ServicesName varchar2(50) NOT NULL, ChargedAmount integer NOT NULL, Customer_CustomerID integer NOT NULL, CONSTRAINT Services_pk PRIMARY KEY (ServicesID) ) ;

-- Table: Shifts CREATE TABLE Shifts ( ShiftsID integer NOT NULL, StartTime timestamp NOT NULL, EndTime timestamp NOT NULL, Employee_EmployeeID integer NOT NULL, CONSTRAINT Shifts_pk PRIMARY KEY (ShiftsID) ) ;

-- foreign keys -- Reference: Customer_Reservation (table: Customer) ALTER TABLE Customer ADD CONSTRAINT Customer_Reservation FOREIGN KEY (Reservation_ReservationID) REFERENCES Reservation (ReservationID);

-- Reference: Discount_Customer (table: Discount) ALTER TABLE Discount ADD CONSTRAINT Discount_Customer FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID);

-- Reference: Receipts_Customer (table: Receipts) ALTER TABLE Receipts ADD CONSTRAINT Receipts_Customer FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID);

-- Reference: Receipts_Services (table: Receipts) ALTER TABLE Receipts ADD CONSTRAINT Receipts_Services FOREIGN KEY (Services_ServicesID) REFERENCES Services (ServicesID);

-- Reference: Reservation_Customer (table: Reservation) ALTER TABLE Reservation ADD CONSTRAINT Reservation_Customer FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID);

-- Reference: Reservation_Employee (table: Reservation) ALTER TABLE Reservation ADD CONSTRAINT Reservation_Employee FOREIGN KEY (Employee_EmployeeID) REFERENCES Employee (EmployeeID);

-- Reference: Services_Customer (table: Services) ALTER TABLE Services ADD CONSTRAINT Services_Customer FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID);

-- Reference: Shifts_Employee (table: Shifts) ALTER TABLE Shifts ADD CONSTRAINT Shifts_Employee FOREIGN KEY (Employee_EmployeeID) REFERENCES Employee (EmployeeID);

DATABASE