Modeling Historical Data

1. In the scenario below, what words or phrases indicate to you the need to track data that changes over time?

Actors at the local theater sign contracts that specify their pay and working conditions. These contracts are renewed annually. Each year the contracts may change through new language and pay rates. In order to resolve labor disputes, the theater must sometimes go back several years in order to see the contract provisions in place on a particular date.

2. Given the scenario below, construct a conceptual model.

The Seville, Spain soccer association is renovating their soccer arena. They are adding luxury boxes that will be offered to companies. These boxes contain varying numbers of seats. Each box is assigned a section number and level. The boxes are leased by the year, and the prices are set individually according to the location and number of seats in the box. The soccer association needs to keep track of the company name, address, phone, and contact person. Sometimes the companies also provide a logo. Companies that lease a box have the right to renew their leases each year, but they often do not. When a lease for a box opens up, the association contacts companies that previously held leases to see if they want to lease one again.

Assumptions:

• ·         If a box is booked say in Nov, it doesn’t mean company gets it only for Nov & Dec, they should get it till next Nov.  “The boxes are leased by the year” only helps here, I need not store end date, just start date of lease is sufficient. End date is automatically same date next year minus 1day.
• ·         Since corporate discounts are also given based on negotiations, price should be stored in lease instance. Some may suggest, keep base price in box and discount % in company instance, but problem here is that sometimes returning customers get extra discounts on lease renewals. So, better keep price decided for next year in Lease instance.
• ·         So each company may have multiple lease instances. Lease is identified by box, company and dateofbooking.
• ·         A lease must be undertaken by a company and must be for a box.
• ·         A box may be booked multiple times in many years.
• ·         Section is identified by level and sectionnumber [with same number every level has a section]
• ·         Box is identified by boxNumber [ with same number every section has a box]