Trigger , Database Management System

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







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

Write discussion on Trigger
Your posts are moderated
Related Questions

Ask questioYou are the database administrator in a university. You have been tasked to come up with a new database for the University. Using the stages in Database Life Cycle come

Saving Derived Attributes to Avoid Re-computation   As discussed, "redundancy means duplication of exact data". But, if multiple copies of same data are present in the system,

Define the Query optimisation The query parser commonly generates a standard initial tree to correspond to an SQL query, with no doing any optimization. Such type of a canonica

Define weak and strong entity sets? Weak entity set: entity set that do not have key attribute of their own are known as weak entity sets. Strong entity set: Entity set that

Normalize the given relation to 3NF, justifying each step

This constraint shows that the key attribute value in each tuple must be unique, i.e., no two tuples have the similar value for the key attribute. This is because the value of the

What is serialization? Where it is used and why?  Serialization is a basic term used for mechanisms which enable objects and object structures to be converted into portable for

You are required to design and build a data mart/data warehouse using Oracle. A national bank allocates funding every year for its local branches to provide temporary staff cove

Problem: ‘Users and Information system specialists need to spend inordinate amounts of time locating and gathering data' (Watson and Hal, 1998) Recent developments in the ar