Given 3 tables listed below for operating diving lessons, there is a business rule about the lesson
Question:
Given 3 tables listed below for operating diving lessons, there is a business rule about the lesson price.
Here is the rule: a) The first customer that signs up for a lesson can attend the lesson for free b) After the first attendant, the next 9 attendants (from the 2nd to the 10th) only need to pay 60% of the regular price c) All other attendants after the 10th attendant will pay the regular price
The reservation is processed one attendant at a time. You don't need to consider cancellation. Only an active lesson can accept reservation. An active lesson has the value "Active" in the status column.
the answer will be an AFTER trigger to accept reservation and implement the business rule. */
CREATE TABLE Customer (CustomerID INT PRIMARY KEY, LastName VARCHAR(50), FirsName VARCHAR(50), Email VARCHAR(30), Phone VARCHAR(20));
CREATE TABLE DivingLession (LessonID INT PRIMARY KEY, Name VARCHAR(50), Description VARCHAR(500), Time DATETIME, Price MONEY, Status VARCHAR(10));
CREATE TABLE Reservation (CustomerID INT REFERENCES Customer(CustomerID), LessonID INT REFERENCES DivingLesson(LessionID), PaidPrice MONEY, Notes VARCHAR(1000) PRIMARY KEY (CustomerID, LessonID));
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill