home / study / engineering / computer science / questions and answers / 1)a) consider a simple airline reservation database … Question: 1)a) Consider a simple airline reservation databas… Bookmarked 1)a) Consider a simple airline reservation database handling (only) outgoing flights from one airline terminal. We need to keep track of passengers, flights, and tickets. We could get almost arbitrarily complex in a real design, since a ?flight? actually brings together a flight crew and an airplane, serviced by a ground crew. But for simplicity, we will limit our scope of modeling. Specifically, we will include only three entities and two relationships. The three entities are: Flights, Passengers, and Tickets. Two relationships are: has-ticket between Flights and Tickets; buy-ticket between Tickets and Passengers. The cardinality of the relationship has-ticket is one-many from Flights to Tickets. The cardinality of the relationship buy-ticket is one-one from Tickets to Passengers. The entity Flights models flights the terminal has. It contains an identifying attribute of flightno (unique identifier values, we can devise a coding scheme so that each flight will have a unique flightno. One such scheme is to use an integer ?represent a particular route–concatenating with the date), a composite attribute depart-time (actually made up of date and time), and a gate attribute. The entity Passengers models a person who buys an airplane ticket for a flight. This entity has a primary identifier attribute social security number SSN, a name attribute, a birth date bdate attribute, and a phone number phone attribute. The entity Tickets is a weak entity that has the ticket number ticketno as its partial key. It also has seat number seatno attribute. Tickets? existence is depended on the entity Flights. Tickets entity (when it is transformed to a table) needs its strong entity?s primary key in forming its key (in our case, its key will be more than one attributes (flightno, ticketno)). In the space below, draw an ER diagram using the above specification. b)Construct an ER diagram for a car insurance company whose customers own one or more cars each. Each car has associated with zero to many numbers of recorded accidents. For simplicity, we will include only three entities and two relationships. The three entities are: Customers, Cars, and Accidents. Two relationships are: owns between Customers and Cars; participated among Customers, Cars, and Accidents. The relationship participated is a ternary relationship. The cardinality of the relationship owns is one-many from Customers to Cars. The cardinality of the relationship participated is many on Customers side, on Cars side, and is one on Accidents side. There should be one attribute damage-amount attach to this relationship. The entity Customers models persons who have insurance policies with the company. It contains an identifying attribute of driver-id, a name attribute, and an address attribute. The entity Cars models a car that is registered for insurance policy. This entity has a primary identifier attribute licenseno, a model attribute, and a year attribute that indicates the year that the car is built. The entity Accidents models events of accidents. This entity has a primary identifier attribute report-number, a location attribute, and a date attribute that indicates the data that the accident happened. In the space below, draw an ER diagram using the above specification.
Comments are closed.