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

What is view in sql, What is view in SQL? How is it defined? Any relati... What is view in SQL? How is it defined? Any relation that is not part of the logical model, but is made visible to a user as a virtual relation is known as a view. We descri

What are the different integrity constraints in rdbms, What are the differe... What are the different integrity constraints in RDBMS? The various integrity constraints in RDBMS are as: Primary Key: primary key is a grouping of one or more attributes

Explain view in rdbms, Explain view in RDBMS? View - A view is a relati... Explain view in RDBMS? View - A view is a relation (virtual rather than base) and could be used in query expressions, which is, queries can be written by using the view as a re

State about the activity diagram, State about the Activity Diagram Act... State about the Activity Diagram Activity diagrams show flow from one activity to other. It is an ongoing non atomic execution within a state machine. Activity finally results

Icloud based storage, Advantages and disadvantages of icloud based storage Advantages and disadvantages of icloud based storage

Describe integrity and security, Describe integrity and security? a)   ... Describe integrity and security? a)      Domain constraint b)      Referential integrity A value that appears in one relation for a given set of attributes also appear fo

Partial relation emp-get the details people with min salary, Let consider t... Let consider the given partial relation EMP. Let us make some sub-queries for them EMPNO ENAME JOB SAL DEPTNO 7566

Example of weak entity, Consider the entity type dependent related to emplo... Consider the entity type dependent related to employee entity, which is used to keep track of the dependents of every employee. The attributes of dependents are: name, sex, birth d

Explain in detail about collaboration diagrams, Explain in detail about Col... Explain in detail about Collaboration Diagrams Collaboration diagrams are interaction diagrams which emphasize structural organisation of an object that transmit and receive m

Physical database design issues, Physical Database Design Issues The da... Physical Database Design Issues The database design includes the process of logical design with the use of E-R diagram, normalisation, etc., followed by the physical design.