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 manufacturers of motorcycles must produce axles that meet specified dimensions. In particular, the diameters of the axles must be 8.50 centimeters. The axles in a sample of n = 64 axles have a...
-
For the following exercises, find the area of the triangle. A satellite calculates the distances and angle shown in Figure 15 (not to scale). Find the distance between the two cities. Round answers...
-
8. An electronics conglomerate intends to divest its high-growth energy division, which develops and manufactures solar panels, windmills, and other green energy products. The division has an...
-
Sysco, formed in 1969, is North Americas largest marketer and distributor of food service products, serving approximately 400,000 restaurants, hotels, schools, hospitals, and other institutions. The...
-
Q#3 Quickie Grocery acquired the following five bottles of Corporate-Cola soft drink: Tabanan CoSTO 52.00 53.00 54.00 55.00 A January 31 inventory count revealed that two bottles remained on the...
-
The Purple Company This project will give you an opportunity to apply your knowledge of accounting principles and procedures to a corporation. You will handle the accounting work of The Purple...
-
Use the information given below to find the exact value of the indicated trigonometric function. a) Draw the angle e in the corresponding quadrant in an x-y coordinate plane. b) Show the double-angle...
-
Convert the following information into: a) a semantic net b) a frame-based representation A Ford is a type of car. Bob owns two cars. Bob parks his car at home.His house is in California, which is a...
-
Visit www.pearsonglobaleditions.com/malhotra to read the video case and view the accompanying video. Marriott: Marketing Research Leads to Expanded Offerings highlights Marriotts success in using...
-
The water level in a tank is \(20 \mathrm{~m}\) above the ground. A hose is connected to the bottom of the tank, and the nozzle at the end of the hose is pointed straight up. The tank cover is...
-
A simple experiment has long been used to demonstrate how negative pressure prevents water from being spilled out of an inverted glass. A glass that is fully filled by water and covered with a thin...
-
A golf ball is hit on a level fairway. When it lands, its velocity vector has rotated through an angle of 90. What was the launch angle of the golf ball? Pyo By Dyz =0 Uso Range R x max dya
-
Custom Fabrications (Stand-Alone PC-Based Accounting System) (Prepared by Will Richens and Michael Catchpole, Lehigh University) CUSTOM FABRICATIONS Inc. is a bicycle manufacturing firm founded in...
-
Drainee purchases direct materials each month. Its payment history shows that 65% is paid in the month of purchase with the remaining balance paid the month after purchase. Prepare a cash payment...
-
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?
-
30. On January 1,2008, the City of Hastings created a solid waste landfill that it expects to reach capac ity gradually over the next 20 years. If the landfill were to be closed at the current time,...
-
34. On January 1, 2008, a city pays $60,000 for a work of art to display in the local library. The city will take appropriate measures to protect and preserve the piece. However, if the work is ever...
-
32. Mary T. Lincoln works for the City of Columbus. She volunteered to work over the 2008 Christmas break to earn a short vacation during the first week of January 2009. She earns three vacation days...
![Mobile App Logo](https://dsd5zvtm8ll6.cloudfront.net/includes/images/mobile/finalLogo.png)
Study smarter with the SolutionInn App