CSE200 SP06 KREEVES QUIZ#4 SEAT# ____________

NAME ___________________KEY_____________________ Lecture: MW 8:30-10:18am

Lab section (check one): _______ R 9:30-11:18am ________ R 11:30-1:18pm

Instructions:

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

· Read each question carefully and fill in the answer in the space provided. Answers must be legible or they will be marked incorrect.

ToothCollection

ChildName	Date	MoneyLeft
Michael	8/17/00	$0.25
Michael	12/10/00	$0.50
Steve	1/8/01	$1.00
Vignesh	2/18/01	$0.25
Michael	3/14/01	$5.00
Vignesh	4/20/01	$10.00
Hilary	9/6/01	$0.50
Rob	12/22/01	$1.50
Hilary	2/5/02	$3.00
Stephanie	3/14/02	$1.00
Gao	3/18/02	$5.00

Children

Name	Birthdate	Gender	Address	City	State	Country
Emma	12/22/91	F	664 Miles Ave.	Seattle	WA	USA
Gao	2/14/96	M	18 Hui Dr.	Beijing	XG	China
Hilary	8/4/95	F	9882 Pike Rd.	Columbus	OH	USA
Jenna	8/18/96	F	245 Lincoln St.	Chicago	IL	USA
Michael	9/6/92	M	53 Center St.	Columbus	OH	USA
Rob	3/18/95	M	212 Peacock Lane	Columbus	OH	USA
Stephanie	5/22/94	F	100 E. Main St.	Celeryville	OH	USA
Steve	9/12/94	M	2385 Oakwood Ave.	Cincinnati	OH	USA
Vignesh	12/10/93	M	1485 Kannappa St.	Madras	TN	India

In many countries, whenever a child loses a tooth, the Tooth Fairy takes the tooth and gives the child some money in return. The tables above represent the Tooth Fairy’s database system so that she can keep track of all the children in the world, the teeth they have lost, and the money she has given them.

· The Children table lists the names of all the children in the world along with their birthdate, gender, and address. You can assume that no two children will have the same name.

· The ToothCollection table keeps track of the dates that each child lost a tooth and the amount of money that the Tooth Fairy gave for each tooth. Since each child will lose many baby teeth, children can appear multiple times on the ToothCollection table.

Note that only partial tables are listed above. The database contains much more data.

1.

(6 pts) Database Relationships. Set up the relationship(s) of this database. Using the boxes below, fill in the primary key and foreign key(s) - if any - for each table and draw lines between the tables to indicate relationships.

No arrows necessary in the answer; just a line

2. (4 pts) What field type (Text, Number, Currency, Date/Time, Yes/No, AutoNumber) is best suited for each of the following fields:

ChildName (ToothCollection Table) _______________________Text

Birthdate (Children Table) _______________________ Date/Time

MoneyLeft (ToothCollection Table) _______________________Currency

Address (Children Table) _______________________Text

3. (4 pts) Note that we only identify children by their names. What sort of problem might this cause, and how should the database be changed to prevent this problem?

• The problem it would cause would be that their could be multiple kids with the same name and then you wouldn’t know which records to relate together
• Autonumber is best solution to add as the primary/foreign key

4. (4 pts) List the names of all children who lost a tooth after January 1, 2002, and the dates those teeth were lost. Sort the list so that the children who lost teeth most recently are listed first.

Tables Used ____ToothCollection______________________________

Field	ChildName	Date
Table	ToothCollection	ToothCollection
Total
Show	x	x
Sort		Descending
Criteria		> #1/1/02#
OR
OR

Additional room for expressions if needed:

5. (4 pts) List the dynaset created from the query in question #4 based solely on the data shown. NOTE: Don’t forget the field names.

ChildName	Date
Gao	3/18/02
Stephanie	3/14/02
Hilary	2/5/02

6. (5 pts) List the name, gender, and birthdate of all Columbus and Cincinnati residents.

Tables Used _____Children_____________________________

Field	Name	Gender	Birthdate	City
Table	Children	Children	Children	Children
Total
Show	x	x	x
Sort
Criteria				“Columbus”
OR				“Cincinnati”
OR

Additional room for expressions if needed:

could also put “Columbus” or “Cincinnati” on one line

7. (8 pts) For each child, list his or her name, age in years, gender, and home country. Sort the list first by country, then by age.

Tables Used _____Children_____________________________

Field	Country	Name	Age: SEE BELOW	Gender
Table	Children	Children	No table	Children
Total
Show	x	x	x	x
Sort	Ascending		Ascending
Criteria
OR
OR

Additional room for expressions if needed:

Age: (#today’s date# - [Birthdate])/365

Can use ascending or descending for sorts

Order of fields not required except that country MUST be to the left of age.

8. (6 pts) List the names and birthdates for all girls with August birthdays and all boys with September birthdays.

Tables Used ____Children______________________________

Field	Name	Birthdate	Gender
Table	Children	Children	Children
Total
Show	x	x
Sort
Criteria		Like “8*”	“F”
OR		Like “9*”	“M”
OR

Additional room for expressions if needed:

9. (6 pts) For each child summarize by name, the number of teeth he or she has lost, the total amount of money received, and the average amount received per tooth.

Tables Used ____ToothCollection______________________________

Field	ChildName	?anyfield?	MoneyLeft	MoneyLeft
Table	ToothCollection	ToothCollection	ToothCollection	ToothCollection
Total	Group by	Count	Sum	Avg
Show	x	x	x	x
Sort
Criteria
OR
OR

Additional room for expressions if needed:

10. (3 pts) In the resulting dynaset, what is the field name for the total amount of money received?

_____________________________________________ SumOfMoneyLeft

SCORE __________/50