Trigger , Database Management System

Assignment Help:

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.


Related Discussions:- Trigger

What is predictive modelling, Question : (a) Data mining is one of the...

Question : (a) Data mining is one of the best ways to analyse data and using software method, hidden and unexpected patterns and relationships in sets of data can be extracte

What is called as an n-way merge, What is called as an N-way merge? The...

What is called as an N-way merge? The merge operation is a generalization of the two-way merge used by the standard in-memory sort-merge algorithm. It merges N runs, so it is k

Illustrate the class diagram for class room scheduling, Illustrate the clas...

Illustrate the class diagram for class room scheduling system If in any college, the number of classrooms are limited which have to be allocated to various classes and instruct

What is the use of keyword isa, What is the use of keyword ISA? The use...

What is the use of keyword ISA? The use of keyword ISA is to show that a class is a specialization of another class.

MS Access 2010 - Form Updates Table, Hi there, Have an issue with my Databa...

Hi there, Have an issue with my Database. I have: TblOrderDetails and FmOrderDetails TblCircuit and FmCircuit Whenever I add data into the Form, it will update the table as expect

Explain heap file with advantages, Explain heap file with advantages? H...

Explain heap file with advantages? Heap File is an unordered set or a group of records, stored on a set of pages. This class gives basic support for inserting, updating, select

Prepare heart failure registry database, Prepare Heart Failure Registry dat...

Prepare Heart Failure Registry database How are you today? Please I would like you to design a Heart Failure registry where doctors will log in to enter information about their

State the meaning of object representation, State the meaning of Object Rep...

State the meaning of Object Representation  The term object representation means "to represent object with the help of objects model symbols". Implementing objects is too simp

Proposed information analysis, Add generation capabilities and/or tailor th...

Add generation capabilities and/or tailor the built-in functionality of generator. This enables you to produce "all" the test data for the MySQL tables you produced in the previous

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd