Trigger , Database Management System

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

- 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
Define Shared locks? Ans: Shared lock: Throughout concurrent execution of transactions, earlier than a transaction can access a data item, it has to obtain a lock on it. Now he

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

Describe Data Manipulation language ? Data Manipulation Language (DML) - A data manipulation language is a language which enables users to access or manipulate data as organize

Explain the Object-oriented decomposition Object-oriented decomposition targets at identifying individual sovereign objects that encapsulate both a state and certain behavio

Define Deadlock avoidance These protocols as well ensure that the system will never enter a deadlock state although the way it is done is different from deadlock prevention. I

Give an example of Operations from the functions of a bank. For creation of a saving account, you can write: bank::: create -savings-account (customer)->account. For oper

In the first unit of this block, you have been given with the details of the Database Management System, its structure, advantages etc. This unit is an attempt to give you informat

Consider the E/R schema with weak entity sets shown below. Come up with the SQL CREATE TABLE statements (with appropriate primary key, unique and foreign key constraints). Include

Describe Interfaces for the DBA? Interfaces for the DBA - Most database systems holds privileged commands which could be used only through the DBA's staff. These involves com

What is the need for triggers? Triggers are useful mechanisms for alerting humans or for starting particular tasks automatically when particular conditions are met.