Write the CREATE TABLE statements needed to implement the following design in the EX schema: members...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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. 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.
Expert Answer:
Answer rating: 100% (QA)
Based on the provided task description and schema heres how you would implement the CREATE TABLE statements INSERT statements SELECT statements and AL... View the full answer
Related Book For
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last
Posted Date:
Students also viewed these databases questions
-
a) Find the set of solutions of each of the linear congruence: i. 8x = 12 (mod 28). ii. 7x = 1 (mod 9). b) Hashing Function is used to assign a memory location for the records in the computer (For...
-
1 ) Describe the key differences between equity and debt 2 ) What is preferred stock? 3 ) Describe the concept of Market Efficiency?
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Read the case study of Statistics regarding the Lawler Grocery Store chain. The use of the sign test is demonstrated in this case study. In your opinion, what is the biggest benefit of the sign test?...
-
The 'Peal life describes some of the steps that BP took to reduce costs. Describe four root-cause cost drivers that a petroleum company like BP might identify to help them reduce costs.
-
Jean Miller purchased a $1,000 corporate bond for $880. The bond paid 3 percent annual interest. Three years later, she sold the bond for $960. Calculate the total return for Ms. Millers bond...
-
What are examples of direct and indirect real estate investments?
-
At December 31, 2010, Grinkov Corporation had the following account balances. Installment Accounts Receivable, 2009........................$ 65,000 Installment Accounts Receivable,...
-
Assume a firm has EBAT of $520,000, and no amortization. It is in a 35 percent tax bracket. a. Compute its cash flow. Cash flow $ b. Assume it has $520,000 in amortiz...
-
Lu Ltd. has experienced the following accounting earnings and taxable income: The differences between accounting and taxable income are caused by differences between accounting and tax expenses that...
-
Which of the following tasks can you perform using the Fixed Asset Item List? Please select the best answer. A. Calculate depreciation and post automatically B. Post general journal entries C. Enter...
-
Create 'professor_audit_trail' table. Use the code below: use ; create table professor_audit_trail ( id int unsigned primary key auto_increment not null, ProfessorName VARCHAR(255) not null,...
-
How are the competitive forces within industrial automation product industry shaping its profitablity using porter's five forces framework
-
Technological , political, and competitive influences are categorized as what type of major influence on business buyer behavior? Explain
-
Discuss Four (4) traditional competitive criteria and explain implication of each criteria in business operations.
-
transformation potential for the processes, customer service delivery, competitive strategy, and operational and supply chain performance of Walmart explain briefly. and also give references.
-
price of the property is $1,950,000 down payment - should be 20% interest rate - use the current 30-year Fixed Rate Mortgage which is 2.90 PMT frequency - monthly Address the following: What was the...
-
Tell whether the angles or sides are corresponding angles, corresponding sides, or neither. AC and JK
-
What is locking and what does it accomplish?
-
In Example 23 on page 58, would you get the same result if you performed the PROJECT command before the JOIN command? Why or why not?
-
How can splitting a table improve performance?
-
How would you factor in the absence of liquidity into your valuation?
-
An analyst who looks at real estate decides to apply the capital asset pricing model to estimate the risk (beta) for real estate. He regresses returns on a real estate index (based on appraised...
-
An alternative way of estimating risk for real estate is to use prices on traded REITs to compute returns, and to regress these returns against a stock index to arrive at a beta estimate. Would this...
Study smarter with the SolutionInn App