1. Create the database, tables, and constraints to create a database for the model:

STUDENT(Name, Major, Nickname, EmailAddress, AdvisorNumber, AdvisorName, Club, ClubCost)

A student has exactly one name (one mandatory relation)

A student has exactly on major (though the major can be undeclared).(one mandatory)

A student may have zero or more nicknames (one to many optional relation)

A student must have at least one email address but may have multiple email addresses. (many mandatory)

A student must have an advisor. An advisor may have zero or more students to advise. Advisors are identified by their number and have an additional attribute, name.(Many to optional)

A student may belong to zero or more clubs. All members of a specific club pay the same club cost, but a different clubs may have different costs. For example, all members of the sailing club pay the same cost, but the cost to be a sailing club member could be different than, say, the tennis club.

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 Dick	English	Phil	pdick@pcc.edu	14	Jones
3	Phillip K Dick	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.