Entity Relationship Problems
Draw the entities and the minimum and maximum cardinalities for the two entities described in each problem. Some problems specify the entities that you should draw. Read each problem carefully. You might find the database design REA data model power point slides useful.
Problem 1:
ABC Inc. would like to have a database for recording:
1. all purchases
2. all cash-disbursements
3. the links (relationship) between purchases and cash-disbursements
It is important to know that:
• we pay all purchases at the end of the month
• the cash-disbursement table will also be used to record payments for employee services
Problem 2:
'Loud,' a shop selling CD players, radios, etc. accurately traces orders and sales:
• Loud generates an 'Open Orders' list daily.
• There is exactly one order for each sale.
• An order can't result in more than one sale.
Problem 3:
• "Disaster" is an insurance company.
• Customers buy a contract from Disaster. There is at most one contract for a customer.
• When a disaster happens, Disaster has to pay a customer.
• Customers make monthly payments.
• A customer makes her/his first payment one or two weeks after she/he signs the contract.
• There is at most one contract for each cash receipt.
• In general, Disaster uses 6, 12 or 24 payments to pay the customer.
• There is at most one customer for a cash disbursement.
• Last week Disaster bought furniture from Pinko. Disaster does not have to pay Pinko until the beginning of next year. A great deal!
What are the cardinalities for the contract-cash disbursement relationship?
Problem 4:
The Abmis Orchestra would like to build a database that records information about instruments and musicians:
• More than one instrument can be recorded for the same musician.
• It is possible to include instruments without having any actual or potential musician that can play the instrument.
• There can be many musicians available for the same instrument.
• They would like to include the actual musicians as well as potential musicians.
• At least one instrument must be recorded for each musician.
Problem 5:
Xilebo is a company selling kitchenware to restaurants. Xilebo uses the following status values for customers: flea, fly, frog, fox and lion. A new customer gets status fly. When the customer remains creditworthy he can improve his status; that is: become a frog, become a fox, etc. When a customer does not pay on a regular basis he becomes a flea. Xilebo does not keep the history of changes in status and only records the most recent status. Currently, nobody is a lion. More than 500 customers have status fox. Only five customers have status flea.
Problem 6
A quotation list provides information about the prices asked per vendor per item. For example, Vendor Lagoon offers a tomato for $2 and a banana for $1 and Vendor Circus offers a banana for $2 and a tomato for $1. Potential vendors are included in the database. Only items for which a vendor is known are included in the database.
Problem 7
Monica owns a stuffed animals store in Lansing. Her business is going extremely well since she started selling exclusive items such as "Seven Dwarfs" Limited Edition, the "Cinderalla" collection, the ultimate "TinTin" collection, etc. Currently, Monica has 500 different types of stuffed animals, including Sleepy-small, Sleepy-big, Garfield-yellow, Garfield-gold, etc. She has a large stock for most of them. For her 25 most important customers ("Club 25"), she keeps a list of their five most favorite items. She uses that information to send gifts (birthday, wedding, etc.) to her customers. An item can be the favorite item for more than one "Club 25" member.
Problem 8
When you buy optional equipment for a car you can buy equipment as a single item or as a package. In general, packages are cheaper. An example of an optional equipment package is Volvo's Grand Touring Package. The Grand Touring Package includes an adjustable power front seat, an in-dash single CD player and burled walnut wood trim on instrument panel and center floor console. The Grand Touring Package is available for all V70 models (V70 GT, V70 GLT) except for the V70 T5 model. The package is standard on the V70 T5 model. No optional equipment packages are available for the V70 T5 model. Other packages are available for the V70 GT. Examples of other optional equipment packages available for the V70 GT are the Sport Package and the TRACS/Cold Weather Package. There is at least one car model for each package.
Problem 9
The Belgian Chocolate Company (BBC) has specific rules for carrier selection. All shipments that are not within a 50-mile radius of East Lansing are shipped by a long distance carrier. These are the 'Long Distance' shipments. There are three different categories of Long Distance (LD) shipments - normal LDS, express LDS and delicate LDS. Express LDS delivers the chocolates within two working days. A delicate LDS is used in summer or to warm places such as New Mexico, Texas and Arizona. There are only two types of local shipments (LS), normal LS and delicate LS. Only carriers that offer at least one of these shipment categories are considered. The UPS strike led to the decision to record at least two different carriers for each shipment category. Categories and authorized carriers change over time. BCC would like to have these rules stored into the relational database.