ITM Assignment

I have an assignment in ITM university class. This homework is about Microsoft Access, and table and date things. 

ITM 315 Individual Assignment 1

20.5 points possible

Due Friday, February 2 at 1pm

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 2 BELOW 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 your last 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.

Grade sheet: Points: Incorrect Correct

Part 1 7 pts

Grade sheet included and above model ____ _____

Data model ____ ____

Appropriately named files ____ ____

Data in table/appropriate data types ____ ____

Primary key in table ____ ____

Part 2 13.5 points

Query 1 ____ ____

Query 2 ____ ____

Query 3 ____ ____

Query 4 ____ ____

Query 5 ____ ____

Query 6 ____ ____

Query 7 ____ ____

1

/2

/1

/1/15

1

0

1 2

/15

1 2

100

1 2

/16

206

0

1 2

206

0

1 5

110

1 6

100

1 7

206

1 7

1 8 2/1/15

200

1 9 2/1/15 2/6/15

75

2 3

200

2 4 2/6/15

2 8

200 160

2 8

200

2 9

2/6/15

3 1

/16

212 80

3 3

100

3 3

3 4

3/15/16

160

3 7 2/3/15 2/4/15 212

3 7 2/5/15 2/8/15 308

4 9 4/15/16 4/18/16

4 10

4 10

312 200

5 2

5 11

308

5 13 5/12/14

130

6 9

6 10

6 10

401 100

6 11

222

6 12

222

		hotelNo		guestNo	dateFrom	dateTo		roomNo	ratebooked
																					1																	2				8										5																3							10						9
3/5/15	3/					7				20												6											100
3/10/15	3/15/15		206
3/											4	3/6/16			12
3/9/16	3/14/16					11
		2/1/15	2/2/15										200
	2/4/15				2/6/15		500
	2/3/15		2/8/15	85
4/1/15	4/4/15						212								160
2/7/15	1	75
101
3/22/16	3/25/16		410
2/10/15		411	1	80
4/1/16	4/4/16
	4/15/16		4/18/16		150
	2/5/15		412			175
3/		13		3/15/16
1/31/14	2/4/14					308
3/26/16	3/31/16			210					130
3/11/16		214
	90
135
	321		190
3/12/14	3/13/14			312	220
2/22/15	2/24/15
5/18/15	5/23/15		219	115
5/11/14		5/12/14	140
5/13/14		421
8/3/16	8/4/16			401	72
8/9/14	8/10/14				222	84
9/8/15	9/15/15
5/31/14	6/4/14	91
7/3/16	7/5/16	88

guestNo

1

main

2

3

4

234 main

Smith

5

OK

6

ID

7

8

KS

9

LA

10

Boise ID

11

Stillwater OK

9

12

LA

13

OK

guestFirst	guestStreet	guestCity	guestState	guestPhone	guestEmail	guestLast
Robert		123		Boise			ID	2085641456	rcroft@yahoo.com	Croft
Howard		234 main			Tulsa				OK	4057441144	hsmith@gmail.com		Smith
Mike	345 main	Hays		KS	7856245511	mrose@yahoo.com	Rose
Matt	Monroe			LA	3185978417	matt4432@yahoo.com
Jennifer	2526 Chatham Way				Stillwater	4057775413	brattonj@outlook.com	Bratton
Bryan	35 West Liberty	Meridian	2085553262	bryanhedrick@gmail.com	Hedrick
Raj	145 E. Highland	Fargo	ND	2105557487	rajrama@outlook.com	Ramanathan
Marie	35 N. Perkins Road	Kansas City	9137325541	marie23james@outlook.com	James
Tony	2545 Brighton Lane	Shreveport	3182674419	tony.chang@centurylink.com	Chang
John	345 Apple St	2086624517	john.jones@cableone.net	Jones
Jason	1123 15th Street	4052385	95	jasonwokstate@suddenlink.net	Williamson
Martin	3478 Trenton St.	Ruston	3182339084	martinj@gmail.com	Johnson
Susan	1211 West Oak			Oklahoma City	4053321167	susanellis@outlook.com	Ellis

hotelNo

1

Tulsa

2

Stillwater

3

Stillwater

4

Oklahoma City

5

Tulsa

6

Oklahoma City

hotelName	hotelCity	hotelPhone
Holiday Inn	4057415555
Hampton Inn	4058749963
Atherton	4053654412
Marriott-OKC	4057748514
Marriott-Tulsa	4057145562
Hyatt	4057325412

roomNo hotelNo

101 1

90

123 6

200 1 double

200 2 double 160
206 1 double 120
210 3 double 150
210 6 single 100
212 1

160

212 3 single 100
212 4 single 200
214 3 double 160
219 5 single 130
222 6 single 100
308 3 single 100
308 5 double 160
312 4 single 200

4 double

321 4 single 190

1 single 95

401 6 single 100
410 2 family 175
411 2 family

412 2 family 175
421 5 single 130
500 1 single 100

5 single 130

1 family 160

type	standardprice
													single
							double	125
	120
				family
315	225
333
180
521
999