1. Create the database, tables, and constraints to create a database for the model created in DMLab3.

2. Write sql statements to insert the data from the unnormalized student relation shown below into the database's tables. This will require entering at least one row into every table. Remember to insert into the parent table of any foreign key constraints before inserting into the child tables that reference it.

Number	Name	Major	Nickname	Email	AdvisorNumber	AdvisorName	Club	ClubCost
1	James Smith	CIS	Jimmy	jsmith@pcc.edu	14	Jones	Sailing	25.00
1	James Smith	CIS	Jimmy	jsmith@pcc.edu	14	Jones	Drama	15.00
2	Barbara Bain	Physics	Babs	bbain@pcc.edu	15	Anderson	Drama	15.00
3	Phillip K Bob	English	Phil	pdick@pcc.edu	14	Jones
3	Phillip K Bob	English	Spook	pdick@pcc.edu	14	Jones
4	Dianne Duval	CIS	Dee	dduval@pcc.edu	15	Anderson	Sailing	25.00
5	Chester Arther	CIS		carther@pcc.edu	16	Fried
6	Jonathan Johnson	Mathematics	Jon	jjohnson@pcc.edu	15	Anderson	Chess	10.00
6	Jonathan Johnson	Mathematics	JJ	jjohnson@pcc.edu	15	Anderson	Chess	10.00
6	Jonathan Johnson	Mathematics	Jon	jjohnson@pcc.edu	15	Anderson	Sailing	25.00
6	Jonathon Johnson	Mathematics	JJ	jjohnson@pcc.edu	15	Anderson	Sailing	25.00
6	Jonathon Johnson	Mathematics	Jon	jjohnson@pcc.edu	15	Anderson	Drama	15.00
6	Jonathan Johnson	Mathematics	JJ	jjohnson@pcc.edu	15	Anderson	Drama	15.00
7	Mary Jensen	Chemistry		mjensen@pcc.edu	16	Fried

3. Write a single select statement with joins that reproduces the above tabe. Use the labels as column aliases. Don't use SELECT * for joined tables as it repeats the joined columns. Formatting is optional. For full credit, you must reproduce all information with a single SELECT.

Note that this assignment requires either SQL Server running locally on your computer (preferred) or using one of the scratch databases (e.g. cis275sandbox) on the school server. If you are running on the school server make sure to include some identifying information in the table name, e.g., YourName_STUDENT to avoid name clashes. Also remember that data is wiped from the scratch databases everyday at midnight, so make sure you save your scripts.

If you have any problems meeting the requirements of the previous paragraph contact me as soon as possible.

Let me know of any inconsistencies in the above data -- it's difficult to insert data consistently in non-normalized relations which is one of the reasons for normalizing in the first place!