Consider the following scenario: A company has a number of customers who have addresses and phone...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Consider the following scenario: A company has a number of customers who have addresses and phone numbers, and we assume that while there may be multiple customers with the same address, no two customers with the same name have the same address or the same phone number. Each customer has a customer id, which is unique to that customer. Suppose that this information is stored as a relational database relation or table. Answer the following: a. What is the schema for this relation (the attributes or columns and their domains or types)? b. What are possible superkeys for this relation? C. Which of the superkeys identified above are the candidate keys for the relation? d. What is the best choice from the candidate keys for the primary key of the relation? 2. Consider the previous scenario. Suppose now that the company wants to also build relations that describe its inventory and sales transactions. For the inventory, the store keeps track of the following: • The product manufacturer name • The product model number The number in stock • The wholesale price • The retail price A manufacturer has many products, each of which has a product number that is unique for that manufacturer. However, two manufacturers could have the same product numbers for some of their products. For the sales transactions, the company tracks the following • The customer id of the customer making the sale • The date of the sale • The product purchased model number • The product purchased manufacturer name • The price paid including tax Assume that a customer makes only one order on any given day. Develop the schemas for each of the above relations, including primary keys and foreign keys, using only attributes representing the information described above. Note that for the sales transaction relation, you must develop foreign keys that refer to the customer table and the inventory table. 3. Consider the following set of tables describing students and school clubs: Student SID Name Gender Status GPA 100 Bill M Fr 3.3 101 John M So 2.1 102 Sue F Jr 3.2 103 Mary F Sr 3.6 105 Jane F Fr 2.9 Clubs CID 1 2 Name MtgRm Chess 144 Debate 145 ClubMemberships SID 100 200 300 300 400 (3,200) CID 1 I 1 2 2 a. Assuming that SID and CID are unique identifiers, what are likely primary keys for the Student and Clubs tables? b. What is the primary key for the ClubMemberships table? c. What are foreign keys necessary for this set of tables in order to ensure that only registered students can be in clubs and students can only join registered clubs? 4. Can you add the following tuple to the ClubMemberships table based on your foreign keys? Why or why not? 5. Can you add the following tuple to the ClubMemberships table based on your foreign keys? (1, 101) Why or why not? 6. Consider the following scenario from your textbook on page 118 dealing with a chain of hotels that need to manage the bookings of their guests into rooms at specific hotels in the chain. The hotel chain maintains four tables in order to manage this information: Hotel/hotelNo, hotelName, city) Room(roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest(guestNo. guestName, guestAddress, guestPhoneNo, guestEmail) The underlined attribute(s) of each relation form the primary key of the relation. a. Identify foreign key relations in the tables that will allow the appropriate referential integrity rules to be applied. For each foreign key relation, specify the referring table, the primary table, and the attributes that form the foreign key in the referring table. For example, a guest should not be booked into a non-existing room at a hotel. A guest should not be booked into a room unless there is information about the guest. Page 2 of 4 For the remaining questions, use the following set of relational database tables to the results for the relational algebra expressions. 7. Og <12(R2) 8. AZ3A(C<9VD=) (R1) 9. AD (R1) 10. TAD (₁=3(R1)) 11. R1XR3 RI ABCD 1 e 4 e 2 g 9 g 3 x 13 f 12. OR1.C=R2.CAR1.D=R2.D (R1XR2) 13. nc (R1)-nc (R2) 14. nc (R1) Unc (R2) 15. nc (R1) 0nc (R2) 16. R2 |><| R3 R2 CDEF 2 s 11 t 4e8 d 9 h 15 k R3 FGH li ly li st f tfs xbo da w S SID Name Gender Status GPA 100 Bill M Fr 3.3 101 John M So 2.1 102 Sue F Jr 3.2 103 Mary F Sr 3.6 105 Jane F Fr 2.9 с CID 1 2 Name MtgRm Chess 144 Debate 145 CM CID 1 1 1 2 2 SID 100 200 300 300 400 Write the relational algebra expressions that will produce tables with the following information, using the tables S (containing information on students), C (containing information on school clubs), CM (containing information on student membership in school clubs; each row with a cid/sid pair indicates that the student with student id sid is a member of the club with id cid) shown above. 17. List student names. 18. List students' names with GPAS greater than 3.0. 19. List student names who are male or juniors or seniors using only sigma selection and projection operators. 20. List the names of the students who are in the chess club. (Use natural joins.) Consider the following scenario: A company has a number of customers who have addresses and phone numbers, and we assume that while there may be multiple customers with the same address, no two customers with the same name have the same address or the same phone number. Each customer has a customer id, which is unique to that customer. Suppose that this information is stored as a relational database relation or table. Answer the following: a. What is the schema for this relation (the attributes or columns and their domains or types)? b. What are possible superkeys for this relation? C. Which of the superkeys identified above are the candidate keys for the relation? d. What is the best choice from the candidate keys for the primary key of the relation? 2. Consider the previous scenario. Suppose now that the company wants to also build relations that describe its inventory and sales transactions. For the inventory, the store keeps track of the following: • The product manufacturer name • The product model number The number in stock • The wholesale price • The retail price A manufacturer has many products, each of which has a product number that is unique for that manufacturer. However, two manufacturers could have the same product numbers for some of their products. For the sales transactions, the company tracks the following • The customer id of the customer making the sale • The date of the sale • The product purchased model number • The product purchased manufacturer name • The price paid including tax Assume that a customer makes only one order on any given day. Develop the schemas for each of the above relations, including primary keys and foreign keys, using only attributes representing the information described above. Note that for the sales transaction relation, you must develop foreign keys that refer to the customer table and the inventory table. 3. Consider the following set of tables describing students and school clubs: Student SID Name Gender Status GPA 100 Bill M Fr 3.3 101 John M So 2.1 102 Sue F Jr 3.2 103 Mary F Sr 3.6 105 Jane F Fr 2.9 Clubs CID 1 2 Name MtgRm Chess 144 Debate 145 ClubMemberships SID 100 200 300 300 400 (3,200) CID 1 I 1 2 2 a. Assuming that SID and CID are unique identifiers, what are likely primary keys for the Student and Clubs tables? b. What is the primary key for the ClubMemberships table? c. What are foreign keys necessary for this set of tables in order to ensure that only registered students can be in clubs and students can only join registered clubs? 4. Can you add the following tuple to the ClubMemberships table based on your foreign keys? Why or why not? 5. Can you add the following tuple to the ClubMemberships table based on your foreign keys? (1, 101) Why or why not? 6. Consider the following scenario from your textbook on page 118 dealing with a chain of hotels that need to manage the bookings of their guests into rooms at specific hotels in the chain. The hotel chain maintains four tables in order to manage this information: Hotel/hotelNo, hotelName, city) Room(roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest(guestNo. guestName, guestAddress, guestPhoneNo, guestEmail) The underlined attribute(s) of each relation form the primary key of the relation. a. Identify foreign key relations in the tables that will allow the appropriate referential integrity rules to be applied. For each foreign key relation, specify the referring table, the primary table, and the attributes that form the foreign key in the referring table. For example, a guest should not be booked into a non-existing room at a hotel. A guest should not be booked into a room unless there is information about the guest. Page 2 of 4 For the remaining questions, use the following set of relational database tables to the results for the relational algebra expressions. 7. Og <12(R2) 8. AZ3A(C<9VD=) (R1) 9. AD (R1) 10. TAD (₁=3(R1)) 11. R1XR3 RI ABCD 1 e 4 e 2 g 9 g 3 x 13 f 12. OR1.C=R2.CAR1.D=R2.D (R1XR2) 13. nc (R1)-nc (R2) 14. nc (R1) Unc (R2) 15. nc (R1) 0nc (R2) 16. R2 |><| R3 R2 CDEF 2 s 11 t 4e8 d 9 h 15 k R3 FGH li ly li st f tfs xbo da w S SID Name Gender Status GPA 100 Bill M Fr 3.3 101 John M So 2.1 102 Sue F Jr 3.2 103 Mary F Sr 3.6 105 Jane F Fr 2.9 с CID 1 2 Name MtgRm Chess 144 Debate 145 CM CID 1 1 1 2 2 SID 100 200 300 300 400 Write the relational algebra expressions that will produce tables with the following information, using the tables S (containing information on students), C (containing information on school clubs), CM (containing information on student membership in school clubs; each row with a cid/sid pair indicates that the student with student id sid is a member of the club with id cid) shown above. 17. List student names. 18. List students' names with GPAS greater than 3.0. 19. List student names who are male or juniors or seniors using only sigma selection and projection operators. 20. List the names of the students who are in the chess club. (Use natural joins.)
Expert Answer:
Answer rating: 100% (QA)
PageNo01 Solution Given data Consider the Following sunario A company has a number of ... View the full answer
Related Book For
Modern Database Management
ISBN: 978-0133544619
12th edition
Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi
Posted Date:
Students also viewed these databases questions
-
A taxpayer expenses out $ 1 5 0 , 0 0 0 of tangible property under IRC Section 1 7 9 when they file their Federal return. The taxpayer will need to apportion the Section 1 7 9 between California...
-
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...
-
List three specific parts of the Case Guide, Objectives and Strategy Section (See below) that you had the most difficulty understanding. Describe your current understanding of these parts. Provide...
-
Every real number is either a/an number or a/an_______ number.
-
Grant, Inc., acquired 30% of South Co.'s voting stock for $200,000 on January 2, Year 1, and did not elect the fair value option. The price equaled the carrying amount and the fair value of the...
-
Which principles should be articulated to guide product development within a destination?
-
210 Use divisibility rules to determine if each of the following is divisible by 9 .
-
On January 1, 2008, Wilke Corp. had 480,000 shares of common stock outstanding. During 2008, it had the following transactions that affected the common stock account. February 1 ..........Issued...
-
i need help Using the Diffusion of Innovation, provide examples of two products that you have recently purchased that would fall into each of these stages. What type of consumer do you typicall...
-
What is each shareholder's realized gain or loss? b. What is each shareholder's recognized gain or loss? c. What is each shareholder's basis in their stock? When does their holding period begin? d....
-
BUSN 488T - BBA Outbound Accounting - Financial Statement Analysis If a firm has a positive tax rate, a positive ROA, and the interest rate on debt is the same as the ROA, then ROA will be
-
Your friend Jose is trying to decide whether to buy or lease his next vehicle. He has gathered information about each option but is not sure how to compare the alternatives. Purchasing a new vehicle...
-
In general, how adequate is the law in terms of being able to prosecute someone who undertakes computer abuse?
-
Should we be any more concerned about computer fraud and embezzlement versus other forms of business fraud and embezzlement?
-
How can inadequate controls in a computer system lead to incorrect decision making?
-
What are the implications of a company losing its: a. Personnel master file b. Inventory master file
-
Part A.) For this question, and the next one (assume that the one-factor CAPM holds...) A portfolio (D) has a beta = 1.1 while the market expected return is 25% and short-term treasury bills...
-
Find the cross product a x b and verify that it is orthogonal to both a and b. a = (t, 1, 1/t), b = (t 2 , t 2 , 1)
-
Customers may have relationships with one another (e.g., spouses, parents and children). Redesign your answer to Problem and Exercise 9-40 to accommodate these relationships. Fitchwood Insurance...
-
There is a bulleted list associated with Figure 2-22 that describes the entities and their relationships in Pine Valley Furniture. For each of the 10 points in the list, identify the subset of Figure...
-
How would Problems and Exercises 4-50 and 4-51 change if a type of fish could be supplied by multiple suppliers? Exercises 4-50 StoreName, PetName, Pet Description, Price, Cost, SupplierName,...
-
The rules of conduct of CAs, CGAs, and CMAs require them to report a breach of the rules of conduct by a member to their profession's disciplinary body. What should they do before making such a...
-
The auditor's working papers usually can be pro vided to someone else only with the permission of the client. What is the rationale for such a rule?
-
Many people believe that a public accountant cannot be truly independent when payment of fees is dependent on the management of the client. Explain a way of reducing this appearance of lack of...
Study smarter with the SolutionInn App