Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Write the CREATE TABLE statements needed to implement the following design in the EX schema: members member_id first_name last_name address city state phone members_groups

Write the CREATE TABLE statements needed to implement the following design in the EX schema: members

Write the CREATE TABLE statements needed to implement the following design in the EX schema: members member_id first_name last_name address city state phone members_groups member_id group_id groups group_id group_name These tables provide for members of an association, and each member can be registered in one or more groups within the association. There should be one row for each member in the Members table and one row for each group in the Groups table. The member ID and group ID columns are the primary keys for the Members and Groups tables. And the Members Groups table relates each member to one or more groups. When you create the tables, be sure to include the key constraints. Also, include any null or default constraints that you think are necessary. 4. Write INSERT statements that add two rows to the Members table for member IDs 1 and 2, two rows to the Groups table for group IDs 1 and 2, and three rows to the Members Groups table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2. Then, write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name. 5. Create sequences that can be used to number the member ID and group ID values starting with 3 (since you already added members and groups for IDs 1 and 2). 6. Write an INSERT statement that adds another row to the Groups table. This statement should use the NEXTVAL pseudo column to get the value for the next group ID from the sequence that you created in exercise 5. Then, write a SELECT statement that gets all of the data for all of the rows in the Groups table to make sure your sequence worked correctly. 7. Write an ALTER TABLE statement that adds two new columns to the Members table: one column for annual dues that provides for three digits to the left of the decimal point and two to the right; and one column for the payment date. The annual dues column should have a default value of 52.50. 8. Write an ALTER TABLE statement that modifies the Groups table so the group name in each row has to be unique. Then, re-run the INSERT statement that you used in exercise 6 to make sure this works.

Step by Step Solution

3.57 Rating (150 Votes )

There are 3 Steps involved in it

Step: 1

Based on the provided task description and schema heres how you would implement the CREATE TABLE statements INSERT statements SELECT statements and AL... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

Students also viewed these Databases questions

Question

What is locking and what does it accomplish?

Answered: 1 week ago

Question

How can splitting a table improve performance?

Answered: 1 week ago