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).
FK FK FK FK
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?
The two skier #5 records from the RUNFUN
table will be deleted… this is the only actual change.
OK if also say none of the records from the
COSTS table are deleted
3.
(5 pts) Given two tables, what is the difference
between an inner and an outer join relative to the foreign key table?
Data integrity violations,
if any, will show up on the outer join, but otherwise, they will be the same.
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.
The “skier” field can also come from the RUNFUN table
(means don’t have to list SKIER table) Any field from any listed table will work for the COUNT
option. INNER JOINS
5.
(8 pts)
Create an Access query in the design view given below to list ALL of the runs
that skiers can ski on as well as the total number of times that run is used by
the skiers. NOTE: run#1 is used 4 times, run #2 is used 2 times, run #3 is used
1 time, etc.
OUTER JOIN Relative To: RUN The “run” field must come from RUN table. Any field from the RUNFUN table can be used for the COUNT
option; but must be from the RUNFUN table not the RUN table.
6.
(15 pts)
Create an Access query in the design view given below that will generate the
given resulting dynaset for Q5Query6C (see the tables on the first page for the
resulting dynaset and further instruction).
Can put expression in the last query if want to… since
it’s calculated per skier… instead of here. Optional NZ function on [countofrun] field. Make sure table name on calculated field is left blank. Make sure show all fields. OUTER JOIN Relative To: SKIER
OPTIONAL Q5Query6A if using Q5Query4 instead of RUNFUN
table IF using this option, MUST have an NZ either here or on
last query (i.e. not optional). OUTER JOIN Relative To: SKIER
************** notice 2 options above
for Q5Query6A
Could put 3rd field called “othercost” with
[sumofcost] as the calculated expression
and the word “expression” in the total line with a blank table name. CANNOT put NZ on the field if you put it here, though,
because the resulting dynaset has null values in the “othercost” column. Make sure show all fields. OUTER JOIN Relative To: SKIER
Should be INNER JOINS but okay if outer joins; however,
if outer join, must fill in “relative to” but will need to list two tables
since there are two joined lines to put all 3 tables together
The skiname field
could have been added to one of the first two queries with a GroupBy on it,
then the SKIER table would not have been needed here. HOWEVER, technically,
based on the given resulting dynaset, the ski number (field “skier”) should
come from the SKIER table. Optional table
name on the “othercost” field since technically an expression – could have
the table name on the field, too, if wanted to with [ ] brackets around. Make sure all fields and order is as listed since given
the resulting dynaset. OTHER OPTION: inner for first query, inner for second
query, then outer with 3 tables i.e. first two queries and skier table
where both query tables are outer joined with skier relative to skier.