DATABASE AND DESIGN - need help creating the ERD using this question and information attached TABLE P6.7ATTRIBUTE NAMESAMPLE VALUESAMPLE VALUESAMPLE VALUE214Alice B. VanderVoort325 Meadow ParkMurkywaterMEMBER_NUM214235Gerald M. Gallega123 Rose CourtHighlightMEMBER_NAMEAlice B. VanderVoortMEMBER_ADDRESS325 Meadow ParkMEMBER_CITYMurkywaterMEMBER_ZIPCODE123451234912345INVITE_NUMINVITE_DATEACCEPT_DATEDINNER_DATE891023-Feb-201827-Feb-201812-Mar-201823-Feb-201815-Mar-201827-Feb-201815-Mar-201817-Mar-201815-Mar-2018DINNER_ATTENDEDYesYesNoDINNER_CODEDISDI5DI2DINNER_DESCRIPTION Glowing Sea DelightEN3ENTREE_DESCRIPTION Stuffed crabDE8Glowing Sea Delight Ranch SuperbENSENTREE_CODEEN3Stuffed crabMarinated steakDESSERT_CODEDESDE2DESSERT_DESCRIPTION Chocolate mousse with raspberry sauce Cheries jubileeApple pie with honey crust 7. Suppose you are given the following business rules to form the basis for a databasedesign. The database must enable the manager of a company dinner club to mailinvitations to the club's members, to plan the meals, to keep track of who attends thedinners, and so on.• Each dinner serves many members, and each member may attend many dinners.• A member receives many invitations, and each invitation is mailed to manymembers.• A dinner is based on a single entree, but an entree may be used as the basis for manydinners. For example, a dinner may be composed of a fish entree, rice, and corn, orthe dinner may be composed of a fish entree, a baked potato, and string beans.Because the manager is not a database expert, the first attempt at creating the data-base uses the structure shown in Table P6.7.a. Given the table structure illustrated in Table P6.7, write the relational schemaand draw its dependency diagram. Label all transitive and/or partial dependen-cies. (Hint: This structure uses a composite primary key.)

The relational schema may be written as follows:MEMBER(MEMBER_NUM, MEMBER_NAME, MEMBER_ADDRESS,…

TABLE P6.7 ATTRIBUTE NAME SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE MEMBER NUM MEMBER_NAME 214 Alice B. VanderVoort 325 Meadow Park Murkywater 235 Gerald M. Gallega 123 Rose Court Highlight 214 Alice B. VanderVoort 325 Meadow Park Murkywater MEMBER_ADDRESS MEMBER_CITY MEMBER_ZIPCODE INVITE_NUM 12345 12349 12345 8 9. 10 INVITE_DATE 23-Feb-2018 12-Mar-2018 23-Feb-2018 ACCEPT_DATE 27-Feb-2018 15-Mar-2018 27-Feb-2018 DINNER DATE 15-Mar-2018 17-Mar-2018 15-Mar-2018 Yes DINNER_ATTENDED DINNER_CODE Yes No DIS Glowing Sea Delight Ranch Superb EN3 Stuffed crab DIS DI2 DINNER DESCRIPTION Glowing Sea Delight ENTREE_CODE ENTREE_DESCRIPTION Stuffed crab DESSERT_CODE DESSERT_DESCRIPTION Chocolate mousse with raspberry sauce Cherries jubilee EN3 EN5 Marinated steak DE2 Apple pie with honey crust DE8 DES

TABLE P6.7 ATTRIBUTE NAME SAMPLE VALUE SAMPLE VALUE SAMPLE VALUE MEMBER NUM MEMBER_NAME 214 Alice B. VanderVoort 325 Meadow Park Murkywater 235 Gerald M. Gallega 123 Rose Court Highlight 214 Alice B. VanderVoort 325 Meadow Park Murkywater MEMBER_ADDRESS MEMBER_CITY MEMBER_ZIPCODE INVITE_NUM 12345 12349 12345 8 9. 10 INVITE_DATE 23-Feb-2018 12-Mar-2018 23-Feb-2018 ACCEPT_DATE 27-Feb-2018 15-Mar-2018 27-Feb-2018 DINNER DATE 15-Mar-2018 17-Mar-2018 15-Mar-2018 Yes DINNER_ATTENDED DINNER_CODE Yes No DIS Glowing Sea Delight Ranch Superb EN3 Stuffed crab DIS DI2 DINNER DESCRIPTION Glowing Sea Delight ENTREE_CODE ENTREE_DESCRIPTION Stuffed crab DESSERT_CODE DESSERT_DESCRIPTION Chocolate mousse with raspberry sauce Cherries jubilee EN3 EN5 Marinated steak DE2 Apple pie with honey crust DE8 DES

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

Here's a situation to mull over: two students are interested in enrolling in the same class, but there's only room for one of them. What is it about a database that makes it so they can't sit together?
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1. Given the preceding information, answer Questions a through e. How many database requests can you identify for an inventory update for both PRODUCT and PART? Using SQL, write each database request you identified in Step a. Write the complete transaction(s). Write the transaction log, using Table 10.1 as your template. Using the transaction log you created in Step d, trace its use in database recovery.
Suppose that you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product ABC is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. Also, each time the product is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1.Given the preceding information, answer Questions a through e.a. How many database requests can you identify for an inventory update for both PRODUCT and PART?b. Using SQL, write each database request you identified in Step a.c. Write the complete transaction(s).d. Write the transaction log, using Table 10.1 as your template.e. Using the transaction log you created in Step d, trace its use in database recovery.
Following on your BIG break to make a database for an… organization that organizes table tennis tournaments (also known as “Tourney”s) at their premises on behalf of clubs, you’ve written down the business rules as follows: One Match belongs to One Tourney, and not none. One Tourney has Many Matches, at least one and a maximum of 128. One Match takes place on One Table, and not none. One Table is used in Many Matches, and possibly none. One Match belongs to one Category, and not none. One Category appears in Many Matches, and possibly none and a max of 32. One Match involves Many Players, minimum 2 and maximum 4. One Player plays Many Matches, and possibly none. You have also been given a small sample of their records (which is not in the least in relational format), which may prove useful. It is below. Match Number Tourney Tourney Start Date Day Time Slot In Day Player Names(Team 1) Player Names(Team 2) Player IDs (Team 1) Player IDs(Team 2)…
Suppose that you have been tasked with cataloging the information found in books. Either more books need to be added, or the ones now stacked on one side of the shelf need to be rotated. Find the optimal data structure and explain why it works here. Provide a flexible method of stocking the bookcase. What happens, meanwhile, if fresh information must be added to a database but there isn't enough space for it?
Imagine for a minute that a financial institution's database is safeguarded by a technique known as snapshot isolation database.Do you offer an example of how a nonserializable execution may be detrimental to the bank? Please share it if so.
Consider the following information about a patient database: A patient is identified by patient id, and admission date. A patient must be either an Emergency patient or a resident patient. We must record the checkback date for Emergency patient. Only resident patient is assigned to one room. Room has a unique number, degree, and floor number. For each room there are a number of beds each of which has a bed number, specification. If a room is deleted, you need not keep track of its beds any longer. Both emergency patient and resident are cared for by a responsible Doctor. Doctor is identified by Doctor number. For each Doctor, the name, several specialty, and years of experience must be recorded. Doctors prescribe medicine for patients. Medicine is identified by trade name that has scientific name and formula. The medicine depends on another medicine to be taking before. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from…
Consider the following information about a patient database: • A patient is identified by patient id, and admission date. • A patient must be either an Emergency patient or a resident patient. • We must record the checkback date for Emergency patient. • Only resident patient is assigned to one room. Room has a unique number, degree, and floor number. • For each room there are a number of beds each of which has a bed number, specification. If a room is deleted, you need not keep track of its beds any longer. • Both emergency patient and resident are cared for by a responsible Doctor. Doctor is identified by Doctor number. For each Doctor, the name, several specialty, and years of experience must be recorded. • Doctors prescribe medicine for patients. Medicine is identified by trade name that has scientific name and formula. The medicine depends on another medicine to be taking before. • A doctor could prescribe one or more drugs for several patients, and a patient could obtain…
What are the benefits of using a transaction to group database operations together that need to either all complete or be rolled back so none of them complete? Here is an example of when a transaction should definitely be used to be helpful: When any bank / financial institution processes a transfer of money from one account to another account, the operations involved are always (1) to subtract a given amount of money from the source account and (2) add that same amount of money to the destination account. They have to be sure the software doing the transfer groups the above 2 required database operations in a transaction to ensure that if there is any system error/problem during these operations there is no way that money could be accidentally subtracted from an account but not added to the account it is supposed to be transferring to or customers will be very upset at the loss of money!
Can you explain what an outlier is? Were there three outliers in a database that caused concern? Provide an analysis of the issues caused by the anomalies you've discovered and a proposed remedy.
Let's pretend there are two students who want to enroll in the same class but there's only room for one of them. Which aspect of a database system makes it impossible for both of them to snag the last open seat?
Consider the following scenario: there is only room for one student in the class that two students are trying to enroll in. What aspect of the database system makes it impossible for both students to claim the last open seat?

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning