leta_stories.markdown

Axe World

Your happy hour is boring. What better way to spend time with your coworkers then throwing axes?

We've got a bustling business where small groups book appointments then come to us to throw axes at the wall. But managing the paperwork is becoming a huge pain. Please help us with your database skills!!

I0: Getting Going

I think we should have a database called axeworld just like our business.

The first things I'm worried about tracking are our clients and reservations. Clients have a first name, last name, and phone number. Reservations have a date, a start time, and a number of people in the party.

Would you make database tables to track all that? To test things out, below are a few sample clients and reservations that you should insert into the database.

Data

Clients

This data is the first name, last name, and phone number:

Jarrod,Sauer,3035554400
Tamica,Morissette,3035559558
Delma,Weber,3035556239

Reservations

This data is the date, start time, and number of people in the party:

9/5/18,14:00,7
9/19/18,14:15,2
9/3/18,17:15,3
9/29/18,10:15,4
9/6/18,18:15,4

Analysis

1. (Easy) Can you write code that finds the first name of the client with the phone number 3035559558?
2. (Medium) Can you write code that displays all the reservations that have a party size of 4?
3. (Difficult) Can you write code that displays all the reservations that happened in the days 9/2 to 9/7?

I1: Connecting the Dots

Ok, thanks for getting the database project moving. I took a look at what you built and realized that there isn't any connection between the clients and the reservations.

Can you fix the database tables so that one client can connect with many reservations? I heard this is called a foreign key.

We also realized that reservations have an end time since they can go anywhere from 30 to 90 minutes in 15 minute increments. Maybe you could somehow change that table to add the new column?

Once you've got that fixed, go ahead and delete the rows of data in the reservations table.

Data

Inserting data into your tables now will be a little trickier. Can you figure out how to get them in there? Each reservation below is the date, start time, end time, and number of people.

Jarrod Sauer made these reservations:

9/30/18,13:00,13:45,4
9/2/18,18:30,19:30,4
9/5/18,11:30,12:00,4

Tamica Morissette made these reservations:

9/29/18,15:00,15:30,8
9/19/18,12:45,13:15,6

And Delma Webber made these reservations:

9/8/18,19:15,20:30,6
9/19/18,11:30,12:15,2
9/5/18,14:30,15:30,1

Analysis

1. (Easy) Can you write code that finds the most recent reservation, then outputs the phone number for the client associated with that reservation?

2. (Medium) Can you write code to output how many reservations each client in our system has made?

The output should look like this:

Jarrod Sauer (3)
Tamica Morissette (2)
Delma Webber (3)

3. (Hard) Can you write code to calculate how many total visitors we had on each date in order?

The output should look start out like this:

9/2/18: 4
9/5/18: 5
9/8/19: 6

And continue for all the known dates.

I2: Lanes

Obviously customers are banging down the doors to throw axes. We started with just three lanes, but now we're building more. We'd better keep track of them.

A lane has a name (like "Wolf"), a maximum number of people (like 4), and a difficulty (either "easy", "medium", or "hard"). Each reservations is for a specific lane.

I think that means you'll need a new table to keep track of lanes. You'll also need to change your reservations table so that it has one of those foreign keys pointing back to the lanes table.

Data

Lanes

Put these lanes into your database:

Wolf,easy,8
Fox,medium,4
Bobcat,medium,6
Eagle,hard,2
Falcon,hard,4

Connections

Now we need to update our existing reservations:

• Jarrod always wants to be on the Fox lane
• Tamica books the smalles lane that will accomodate her group size
• When Delma comes by herself or with her partner, she plays Eagle. But when she brings friends, it's Wolf.

Analysis

1. (Easy) Write code to output the name of the only lane that was used on 9/29

2. (Medium-Hard) Write code to rank the lanes by their number of usages

Your output would look something like:

1. Wolf (4)
2. Eagle (2)
3. Bobcat, Falcon (1)
4. Fox (0)

But note that the actual data is different. As you can see in (3), ties should get merged together.

3. (Quite Hard) Write code that outputs each reservation in date order with the client name and any alternate possible lanes that are big enough to accomodate the party.

Each line of the output should look like this:

9/8/18 -- Delma Webber is booked 19:15 to 20:30 with 6 people on Wolf (alternates: Bobcat)

Don't worry about collisions across reservations unless you are really, really fancy.