Exercises

1. Use MySQL Workbench to create an EER diagram for a database that stores information about products.

Each product must have a product name, description, and price.

Each product must belong to one category.

Each category must have a category name and description.

Each category can include multiple products.

2. Use MySQL Workbench to create an EER diagram for a database that stores information about customers.

Each customer must have an email address, first name, and last name.

Each customer can have two or more addresses.

Each customer can have a default billing address and a default shipping address.

Each address must have a street address, city, state, postal code, and country.

Each country name should be stored in one place only. In other words, you shouldn't store the name of the country, which may be many characters, in the address.

3. Use MySQL Workbench to create an EER diagram for a database that tracks the memberships for an association and for the groups within the association.

Each member must have an email address, first name, and last name.

Each member can belong to any number of groups.

Each group must have a name.

Each group can have any number of members.

Exercises

1. Write a script that adds an index to the AP database for the zip code field in the Vendors table.

2. Write a script that contains the CREATE TABLE statements needed to implement the following design in the EX database:

These tables provide for members of an association, and each member can be re6stered in one or more groups within the association.

The member_id and group_id columns are the primary keys of the Members and Groups tables, and these columns are foreign keys in the Niembers_Groups table.

Include any constraints or default values that you think are necessary. Include statements to drop the tables if they already exist.

3. Write INSERT statements that add rows to the tables that are created in exercise 2.

Add two rows to the Members table for the first two member IDs.

Add two rows to the Groups table for the first two group IDs.

Add three rows to the Group_Membership table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2.

Write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name. Sort the results by the group name, member last name, and member first name.

4. Write an ALTER TABLE statement that adds two new columns to the Members table created in exercise 2.

Add one column for annual dues that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 52.50.

Add one column for the payment date.

5. Write an ALTER TABLE statement that modifies the Groups table created in exercise 2 so the group name in each row has to be unique. Then, use an INSERT statement to attempt to insert a duplicate name. This statement should fail due to the unique constraint.

Attachment:- Assignment.rar