CIS 207 Oracle - Database Design and SQL
HOMEWORK: # 4
Paste a copy of each query into this word document or notepad .txt file, save and return/upload in TalonNet:
Complete the following “Try It / Solve It” Exercises:
Section 8-1, Exercise 1 and 2
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.
Section 8-2, Exercises 1 and 2
1. Speeding Tickets
Each police officer may issue speeding tickets to motorists in an assigned area. Originally, the attribute date was modeled as part of the SPEEDING TICKET entity. However, the city police department wants to see if there is a relationship between weather and the frequency of speeding tickets -- do people drive faster on nice sunny days? Are there more tickets in hot weather or cool weather?
Modify the ERD to address this new requirement.
issued
Issued by
2. Examine the ERD that represents classroom assignments for different exams.
(a) Why is start time part of the UID of ASSIGNMENT?
(b) Name at least three time-related constraints. For example: End time must be later than start time. Indicate if the constraint represents conditional non-transferability.
Section 8-3, Exercises 2
2. When a student’s grade is changed, we need to record information on the teacher who changed the grade and the reason for the change.
Start with the ENROLLMENT entity, which is the resolution of the M:M between STUDENT and CLASS.
Section 8-4, Exercises 1
Create an ERD based on the following Summit Sporting Goods scenario. Be sure to follow drawing conventions for readability and clarity.
“I’m a manager of a sporting-goods wholesale company that operates worldwide to fill orders from retail sporting-goods stores. The stores are our customers (some of our people prefer to call them our clients). Right now we have 15 customers worldwide, but we’re trying to expand our customer base by about 10% each year starting this year. Our two biggest customers are Big John’s Sports Emporium in San Francisco and Womansports in Seattle. For each customer, we must track an ID and a name.
We may track an address (including the city, state, zip code, and country) and phone number. We maintain warehouses in different regions to best fill the order of our customers. For each order, we must track an ID. We may track the date ordered, date shipped, and payment type when the information is available.”
Homework_4_Sp17_207_key.doc