Write an alter table statement

Assignment Help Database Management System
Reference no: EM131235547

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:

1344_Figure.png

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

Reference no: EM131235547

Questions Cloud

Negative outcomes on multinationals and other stakeholders : Free-floating currency can have positive and negative outcomes on multinationals and other stakeholders. Should global decision makers be able to manipulate currency rates? If yes, when is it appropriate to do so and what global criteria should be..
What is the organization current business model : What is the organization's current business model? Did you make any financial adjustments that go against the way the organization planned its finances in the past? If so, what were they, and why did you make the changes?
What was the marginal investors tax rate : If your tax rate is 40%, what interest rate do you earn in after-tax terms if the before-tax interest rate is 6%? - What was the marginal investor's tax rate?
Current interest rate for newly issued bonds : Determine the yield to maturity on a 10-year 6% bond selling at par if the going rate (current interest rate for newly issued bonds of the same quality rating) is 6%? This is a think question; not a calculation question. Briefly explain how you re..
Write an alter table statement : 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 defa..
Prevent the bankruptcy of a customer : Cash application should be done as quickly as  possible by a seller because:
Different type of customers involved in the process : Think of a “supply chain” for filling a doctor’s prescription. Please describe the following; Describe the different type of customers involved in the process. Build a strategy for determining the Voice of the customer and tracking how well their nee..
Difference in the obligation of one with a long position : What is the major difference in the obligation of one with a long position in a futures (or forward) contract in comparison to an options contract? Do they have any risk for the investors?
Nonprofit organizations contribute to society : How can you find some middle ground in this debate to show that both businesspeople and those who work for nonprofit organizations contribute to society and need to work together more closely to help people

Reviews

Write a Review

 

Database Management System Questions & Answers

  Activity-based costing to assign overhead costs to products

How much overhead cost would be assigned to each of the two products using the company's activity-based costing system

  The cio asks you to describe why you believe it is

you are the information security officer at a medium-sized company 1500 employees. the cio asks you to explain why you

  Consider the scenario from exercise 2.4 where you designed

Consider the scenario from Exercise 2.4, where you designed an ER diagram for a company database. Write SQL statements to create the corresponding relations and capture as many of the constraints as possible. If you cannot capture some constraints, e..

  Produce different final database state than using trigger

Assume that relation Employee has no tuples in it initially. You are to show the simplest example you can think of where using trigger T1 will produce a different final database state than using trigger T2.

  Describe the purpose of the database

Describe the purpose of the database. Based on your research and / or common knowledge of a college and the information tracked by a college, describe the entities of the database and the attributes of each entity.

  How much indirect factory wages and factory equipment

How much indirect factory wages and factory equipment depreciation cost would be assigned to the Customer Orders activity cost pool

  Imagine that you work for a consulting firm that offers

imagine that you work for a consulting firm that offers information technology and database services. part of its core

  If those were for me wed keep them all in two to three

if those were for me wed keep them all in two to three minutes longer he says. but thats not my call-its the

  Imagine that you have been hired as a consultant to assist

imagine that you have been hired as a consultant to assist in streamlining the data processing of an international

  Create a schema that supports the company business

Create a schema that supports the company's business and processes. Explain and support the database schema with relevant arguments that support the rationale for the structure

  Developing an online database

When a member logs onto the database, all session information, such as IP address, terminal, and user session information, must be recorded for future analysis.

  What means are available to control integrity of fields

Research and provide two to three pages covering the following: How do you decide on how a specification for a field will be determined? Why do you select a specific field type? Why are field values coded or compressed

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd