Flights(FlightNo, DeptDateTime, Origin, ArrivalDateTime, Destination, Capacity, SoldSeats) Tickets(FlightNo, Seat, CustID)

Where
- flightNo is the PK of the flights table
- DeptDateTime holds the date and time of the flights departure
- Origin holds the airport code (e.g. RKE) of the flights origin
- ArrivalDateTime holds the date and time of the flights arrival
- Destination holds the airport code (e.g. ATL) of the flights destination
- Capacity is the number of passenger seats the flight can hold
- SoldSeats is the number of seats that are currently sold

- FlightNo+Seat is the PK of the Tickets Table
- FlightNo in the Tickets Table is an FK referencing the Flights Table
- Seat is the seat assigned to that ticket (e.g. 7B)
- CustID is the ID number of the customer that is assigned to that seat

Write a trigger or triggers that:
- increments the SoldSeats field when a ticket is inserted
- raises an exeception when an attempt to insert a ticket occurs
  if the flight is already sold out
- decrements the SoldSeats field when a ticket is deleted

To provide appropriate transaction control your trigger should lock the associated flight row in the flights table before attempting to update the SoldSeats field.

Add sample data and test your triggers.

Have your triggers finished with some sample data entered by the start of class on November 16th.

Posted Date: 2/22/2013 8:16:17 AM | Location : United States

Ask an Expert

Related Discussions:- Trigger , Assignment Help, Ask Question on Trigger , Get Answer, Expert's Help, Trigger Discussions

Write discussion on Trigger
Your posts are moderated

Write your message here..

Related Questions

The average amount of their orders , Make SQL statements for the following ... Make SQL statements for the following scenarios. Your response should contain SQL statement, output and any other assumptions you have made to arrive at the answer. For Colorado cu

What are the various symbols used to draw an e-r diagram, What are the vari... What are the various symbols used to draw an E-R diagram? Explain with the help of an example how weak entity sets are represented in an E-R diagram. Various symbols used to d

Execute sql queries, Consider the relations described below: PHYSICIAN (re... Consider the relations described below: PHYSICIAN (regno, name, telno, city) PATIENT (pname, street, city) VISIT (pname, regno, date_of_visit, fee) Where the regno and pname ide

Illustrate the definition of the classes and operation, The definition of t... The definition of the classes and operation can often be adjusted to increase the amount of inheritance". Justify the above statement.   Adjustment of Inheritance The def

What is derived and stored attribute, What is Derived and stored Attribute?... What is Derived and stored Attribute? Derived and Stored Attribute - In a few cases, two or more attribute values are associated, for example, Age and BirthDate attributes of a

Determine the un-normalized form, Un-Normalized Form (UNF) If a table h... Un-Normalized Form (UNF) If a table having non-atomic values at every row, it is said to be in UNF. An atomic value is something that cannot be further decomposed. A non-atomic

Key to represent relationship between tables is called, Key to represent re... Key to represent relationship amongs tables is called ? A Key that represent the relationship between tables is called Foreign Key.

List the data structures implemented by the storage manager, List the data ... List the data structures implemented by the storage manager. The storage manager executes the following data structure a) Data files b) Data dictionary c) indices

Describe the storage structure of indexed sequential files, Describe the st... Describe the storage structure of indexed sequential files and their access method.  Ans:   Storage structure of Indexed Sequential files and their access: To achieve fast rand

B+ tree, construct a B+ tree for the following set of key value (2,3,5,7,11... construct a B+ tree for the following set of key value (2,3,5,7,11,19,23,29,31) in ascending order where the pointers that will fit in one node is as follows:A. four B. six C. eigh