Submit a series of SQL statements that when executed in sequence yields a functional database with tables constraints, relationships and data.

We are creating the physical model for this Crows Foot EERD (that is at least 3NF) for the following business processes:

A university club wants to create a database to keep track of the members attending for dinner and their menu selection for each dinner. They will also like to keep track of the people that said that they are not attending.

The club has a list of members and sets out every year to construct and plan the years events. The member list contain name, address and phone.

Prior to each event, invitations are always sent to each member.

Members may RSVP with two choices, one that they will attend and another that they will not attend.

Members that decide to attend the event must RSVP with the number of total guests coming.

A member may decide not to attend even after sending a positive RSVP.

Each event has a menu and the same menu could be offered for another event.

A menu has of a list of dinner choices (dishes).

A dish is always composed of at least one and only one entre and two or many side possibilities.

The combination of entre and side possibilities are never repeated, they are given a dish name and no two sets of entres and sides are the same.

Once the guests arrive, the invitation card most be presented. All attending members always present their invitations.

During the event a receiving person at the club collects the invitations and records names of guests along with their selection for dinner. One guest or member can only select one dinner selection for dinner.

Please include the following information in your tables:

1. Sides should have a unique name, with a description and should include if it is salty or sweet. Add the basic cost of ingredients and cost of preparation. You should add at least 7 records for sides.

2. Entre should have a unique name, with a description. Add the basic cost of ingredients and cost of preparation. Include a preferred type of wine to be served with this entre. Type of wines are red, white and sparkly. You should add at least 5 records for entre.

3. For the member, we want to collect address, phone, if active, full name with middle initial. You should add at least 12 records for member.

4. For the event, we also want to keep track of allowable number of guests. Event name should be unique. You should add at least 3 records for event.

5. For invitations, we want to know when they were printed.

6. For RSVP we want to know when they were received.

You are welcome to add fields that work for you in all the tables.

The exercise will help you identify the sequence a website must present to the user, given the dependencies you are imbedding into the database structure.

All of the symbols you created in your EERD must be transferred to the CREATE statement. Primary Keys, Foreign Keys, UNIQUE constraints, NOT NULL constraints, dependencies, etc.

Write the CREATE statements for the database and tables.

This file should be able to run without errors. Also, should be able to re-run the file without errors.

DROP the database before starting. For example:

DROP DATABASE ab12345 ;

CREATE DATABASE IF NOT EXISTS ab12345 ;

USE ab12345 ;

CREATE TABLE IF NOT EXISTS side (

side_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

side_name VARCHAR(100) NOT NULL UNIQUE KEY,

side_description TEXT NOT NULL,

is_salty BOOLEAN NOT NULL DEFAULT 1

)

;

After the entire structure of the database is complete, at the bottom of the original CREATE DATABASE script, add on to the procedure the following:

Add rows of inserts that simulates the invitation process. Write the insert statements for 100% invitations for one event.

Then, add rows of inserts that simulate the return of only 10 RSVPs. You can decide which 10 people RSVP.

Of those 10 RSVPs two are not attending.

Of those attending, 50% will have guests.

Also, write the inserts for those attending and their dish selection.