AAA Property Rentals is an organization that rents beach or mountain properties in several states. The owner of AAA Properties wants a database system to track information about customers, rental properties, and rental agreements. They have also provided property characteristics. For example for renters of beach properties want to know the distance from the beach to the property and for mountain properties the available recreational activities is important.
Business Rules:
1. The owner wants to retain renter information
2. The owner wants to record the rental transaction. The owner also explained that when somebody rents a property, that property is reserved for a particular period of time for that customer.
3. The owner also wants to have access to the rental history showing who rented each property and for what period. 4. A property is considered under agreement if is currently rented or rented in the future. The owners have provided the following information about customers and properties.
Customer Information:
First name 15 characters - Middle Initial 1 character - Last name 20 characters - Street Address 25 characters City 20 characters State 2 characters Zip 5 characters Phone number 12 characters Email address 25 characters Property Information: Street address 25 characters City 20 characters State 2 characters Zip 5 characters Number of bed rooms 99 (2 integers) Number of blocks to the beach 99 (2 integer) Weekly rental rate 999.99 (double)
Mountain Property Activities: Alpine Skiing Cross Country Skiing Hiking Swimming Skating Snowmobiling
The deliverables are (in suggested order of completion):
1. A diagram illustrating the functional dependencies for each item. See page 178- 182 in your text. Note this can be hand drawn if necessary.
2. Ensure all relations are in 3rd normal form. If necessary draw a revised relational schema showing all relations in 3rd normal form.
3. An enterprise level ER diagram (can be derived from the ER diagram created in step #2). See page 11 or 12 in your text.
4. Scripts to build and populate the database. Note the script should contain the following: o Drop tables o Create tables o Create Primary keys o Create foreign keys if necessary o Describe all tables o Insert the data contained in the attached spreadsheet o Select * from all tables. Immediately following each select statement, the output should be captured and pasted into the script file for printing.