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

Optimistic concurrency control, Optimistic Concurrency Control Is locki... Optimistic Concurrency Control Is locking the only way to stop concurrency related problems? There exist some other ways too. One such way is known as an Optimistic Concurrency

single products ordered, For Colorado customers calculate the number of si... For Colorado customers calculate the number of single products ordered. If a product is purchased on multiple orders, it should be counted only single time. The result should conta

Process to find the non-concurrent objects, Process to find the non-concurr... Process to find the non-concurrent objects Once we find out non-concurrent (mutually exclusive) objects, we can fold all objects together in one thread of process or control. O

Dealing with constraints violation, If the deletion violates referential in... If the deletion violates referential integrity constraint, then three alternatives are available: Default option: - refuse the deletion. It is the job of the DBMS to describ

What is relationship instance, What is relationship instance ? Relation... What is relationship instance ? Relationship Instance - A relationship is a relation between two or more entities. An instance of relationship set is a set of relationships.

Explain domain calculus, Explain Domain calculus Domain calculus is one... Explain Domain calculus Domain calculus is one of the types of the relational calculus. The formal specification of the domain calculus was planned after the development of the

Write short notes on schema diagram, Write short notes on Schema diagram.  ... Write short notes on Schema diagram.  A database schema along with primary key and foreign key dependencies can be depicted pictorially by schema  diagram. Every relation appea

Describe file organisation, Describe file organisation? A file is organ... Describe file organisation? A file is organized logically as a sequence of records. These records are mapped onto disk blocks. A) Fixed-Length Records Type deposit=record

What are the two types of indices, What are the two types of indices? T... What are the two types of indices? The two types of indices are:- A)     Dense index B)      Sparse index

What is recovery management component, What is recovery management componen... What is recovery management component? Ensuring durability is the responsibility of a software component of the base system known as the recovery management component.