Discussion Questions: 75 to 150 words
Referential integrity â€œrulesâ€ trigger some cleanup of your data when insertion, update, or deletion events would typically cause data anomalies. But the word â€œtriggerâ€ has its own place in database design and implementation. Research the concept of a â€œtriggerâ€ and compare and contrast â€œtriggerâ€ events with referential integrity rules.
Discuss the concept of the intersection table. Why is it needed in many to many relationships? Why is intersection data needed?
Page 154 exercises 1 and 2
Be sure to normalize the data tables for both databases to 3NF
You may submit a single Word document with your models pasted in or as separate PDF files representing each model.
1. Leslieâ€™s Auto Sales has a relational database with which it maintains data on its salespersons, its customers, and the automobiles it sells. Each of these three entity types has a unique attribute identifier.
The attributes that it stores are as follows:
â€¢ Salesperson Number (unique), Salesperson Name, Salesperson Telephone, Years with Company
â€¢ Customer Number (unique), Customer Name, Customer Address, Value of Last Purchase From Us
â€¢ Vehicle Identification Number (unique), Manufacturer, Model, Year, Sticker Price Leslieâ€™s also wants to keep track of which salesperson sold which car to which customer, including the date of the sale and the negotiated price. Construct a relational database for Leslieâ€™s Auto Sales.
2. The State of New York certifies firefighters throughout the state and must keep track of all of them, as well as of the stateâ€™s fire departments. Each fire department has a unique department number, a name that also identifies its locale (city, county, etc.), the year it was established, and its main telephone number. Each certified firefighter has a unique firefighter number, a name, year of certification, home
telephone number, and a rank (firefighter, fire lieutenant, fire captain, etc.) The state wants to record the fire department for which each firefighter currently works and each firefighterâ€™s supervisor. Supervisors are always higher-ranking certified firefighters.
Construct a relational database for New Yorkâ€™s fire departments and firefighters.