Assignment:
Deliverables:
1. Word document with grade sheet (see p 2)and data model (part 1a).
2. Access database with PARK table.
3. hotel spring 18 database modified to include queries 1-7.
1. A small city government would like your help developing a data model and database for local parks.
a. Draw a data model (ERD) for a PARK entity with the attributes: ParkID, ParkName, Park Address, and ParkEstablishDate. Add 2 more attributes that you think the city practice might like to record for each park. Copy this entity into the grade sheet on page 2BELOW the grading sheet and Submit this as a Word document named using the following convention LastNameHMWK1Model (fullerHMWK1Model).
b. Create a table (in a new database) in MS ACCESS for the PARK entity and attributes described above. Make sure to choose an appropriate data type for each field. Save your database using the convention LastNameHMWK1DB (e.g., FullerHMWK1DB). Make sure to designate the primary key.
c. Add data to your table. Add 3 records (rows) to your database. You should incorporate yourlast name in the name of one of the parks . Aside from your name, the rest of the data should be fictitious.
2. Using Access and the hotelSpring18 database, code the SQL for the following queries. You will create and save each query separately and name the queries query 1 through query 7. Please be sure to save each query under the appropriate name. The query you save under query 1 will be graded as query 1, etc. Each problem must be answered with a single query om a single table as demonstrated in Chapter 3. Do not change any of the data in the hotel spring 18 database. Rename this database to include your last name (e.g., hotel spring 18 Fuller) and submit.
Query1: List the first name, last name and street addresses of any guests that have a street name that includes "Main"
Query 2: Which guests (by number-do not add additional columns to your answer), booked rooms for less than $100. Order the result in ascending order by guest number. If any guest has booked more than 1 room for less than $100, only list that guest one time.
Query 3:
List all of the information for rooms in hotel 4.
Query 4: List the bookings (by hotelNo and roomNo) in hotel 5 that began during may 2014
Query 5: How many times has guest 8 booked rooms in hotel 2?
Query 6: What is the average standard price of rooms in hotel 2? Label the resulting column as "Hotel2Average"
Query 7:List the hotels (by name and location) that are not located in Stillwater or tulsa.