WI10 CSE200 QUIZ#5

Name _____________________________ Seat# ________ Lab=> Friday ______ 1:30pm______ 3:30pm

Instructions:

· Put away all books, papers, and calculators. Turn off all beepers and cell phones.

· Answers must be legible or they will be marked incorrect.

· NO CALCULATORS ALLOWED. If you are seen with a calculator, you get a zero for the exam.

· Be sure that all answers are SYNTACTICALLY correct i.e. as you would see them prior to running the query. Remember to put quotes on the like and text values and pound signs around dates.

· Aggregate functions: Group By, Sum, Avg, Min, Max, Count, Where, Expression.

· REMEMBER that all the data is not shown. Be sure your queries will work with additional records.

· You can always assume that any data integrity violations have been corrected prior to writing your first query.

The tables given represent the database system for the Excel-lent Ski Lodge and Resort. The following tables are a collection of information about the people who visit the facilities and the fun they have:

The SKIER table is a list of skiers (well a portion of them anyway) which includes a field that uniquely identifies them in addition to their name, gender, and ski level.

The TYPE table defines the details about each of the different levels of skier. Each type as seen in the first column here, has defined Yes/No fields which describe what level is associated with each type. Although not enforced, these associations are recommendations so that skiers can choose the runs that will be fun, enjoyable, and not too dangerous for their skill level.

The RUN table is the list ski runs that the skiers can ski down. The length is given in feet and the type of each run is designated as well.

The RUNFUN table is a list of the skiers and the runs that they skied on.

The COSTS table contains the amount of money spent on lodging, rental car and equipment, if any, that each skier needed for their trip.

This resulting dynaset includes ALL of the skiers with their names, the cost of all the runs they experience at $15 per run, the total of the other costs (lodging, equipment, car rental, etc) and then the total cost for their trip.

Name ___________________________ Seat# ________ Lab=> Friday ______ 1:30pm______ 3:30pm

1. (10 pts) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) and foreign key (s) (if any) for each table AND draw relationship lines between tables (exactly the same as you would see in Access).

COSTS table

Primary Key

Foreign Key(s)

SKIER table

Primary Key

Foreign Key(s)

RUNFUN table

Primary Key

Foreign Key(s)

TYPE table

Primary Key

Foreign Key(s)

RUN table

Primary Key

Foreign Key(s)

2. (5 pts) Just for this problem, let’s pretend that the very first record in the COSTS table has a skier number of 11 instead of 1. Now, I decide to delete skier #5 from the SKIER table. Assuming that cascade delete is checked when it’s allowed to be, what changes occur in the database (if any) in addition to the skier#5 record being deleted from the SKIER table?

3. (5 pts) Given two tables, what is the difference between an inner and an outer join relative to the foreign key table?

4. (7 pts) Create an Access query in the design view given below to determine how many of each different type of run each skier experiences. For instance, skier #1 has 3 type C runs; skier #2 has 1 type A and 1 type B run; skier#3 has 1 type A run, 1 type D run, 2 type E runs and 2 type G runs; etc. Be sure to include the skier number and the type in addition to the number of each different run type per skier.

TABLE(S):
JOIN TYPE:				RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR