ITEC 340
Homework 3
Spring 2006
Examine the tables at the bottom of this assignment and construct the following script:
H3A.SQL This script should do all of the following:
· Delete any of the three tables that may already exist.
· Create and constrain the three tables.
· Populate the three tables with data.
Use appropriate data types and reasonable column widths for your table columns. In addition to all necessary primary and foreign key constraints add not null constraints to the camperID, category, cdate, and amount columns of the charges table. Use check constraints to limit the gender to M and F and to limit the cdate to 2006 dates. Also add one unique key constraint on the Campers table to prevent the entry of two campers with the same first name and home phone number.
DO NOT use the sample data provided. Make up your own similar sample data. Your sample data MUST articulate the one-to-many nature of the entity relationships. For example, the relationship between campers and charges is one-to-many, therefore your sample data should include multiple charges for most campers.
|
Campers |
|||||||||
|
camperID |
last |
first |
Gender |
address |
city |
state |
zip |
homephone |
budget |
|
1 |
Jones |
David |
M |
|
|
VA |
24060 |
(540)
333-9866 |
$
100.00 |
|
2 |
|
Billy |
M |
2
Eastern Ave. |
|
VA |
23454 |
(804)
222-6789 |
$
200.00 |
|
3 |
Smith |
Sally |
F |
|
Radford |
VA |
24142 |
(540)
673-8894 |
$
150.00 |
|
Charges |
|
Categories |
||||
|
ChargeNum |
camperID |
Category |
cdate |
Amount |
|
description |
|
1 |
1 |
candy |
21-Oct-99 |
$2.00 |
|
candy |
|
2 |
1 |
clothing |
22-Oct-99 |
$12.00 |
|
clothing |
|
3 |
2 |
candy |
25-Oct-99 |
$1.00 |
|
gifts |
|
4 |
3 |
gifts |
27-Oct-99 |
$20.00 |
|
misc |
|
5 |
2 |
misc |
30-Oct-99 |
$4.00 |
|
|
|
6 |
1 |
candy |
30-Oct-99 |
$3.00 |
|
|
|
7 |
1 |
candy |
1-Nov-99 |
$2.00 |
|
|
Create a folder named RU03 in your student folder on Neelix. Place your script in that folder by 11:30pm, Tuesday, February 14th.