8/10/2019 UML BD Problema Rezolvata

1/4

1

Ex. 1. Design and draw an ER diagram for a hotel company reservation system with the followingrequirements:

1. There are many hotels in the chain. Each hotel has a name, a street address (which is made upof a street number, street name, city, state, and postal code), a home page URL (Web address),and a primary phone number.

2. Each hotel consists of a set of rooms arranged on various floors. Each room has an identifierwhich is unique within that hotel. Most of the time, rooms are numbered (e.g. 690), but theymay be given a name (e.g. Presidential Suite) instead, so long as the name or number is uniquewithin the hotel. Floors are numbered, and it's necessary, for each room, to know what floor it'son, since some customers prefer rooms on lower floors or higher floors. For simplicity, assumethat each room is on only one floor. (Some real hotels have suites that span multiple floors.)

3. For each room, it's also necessary to keep track of how many beds it has, as well as whethersmoking is allowed in the room. This information is used to help match guests to rooms withdesired characteristics.

4. When a guest plans to stay at a hotel in the future, he or she makes a room reservation at the

desired hotel. Each reservation indicates information about the guest, the desired arrival anddeparture dates, as well as preferences that aid in selecting the right kind of room for that guest:whether the room should be smoking or non-smoking, whether the room should have one bedsor two, and whether the room should be on a high floor or a low floor. These room preferencesare optional, and are not included with every reservation; some guests are willing to take anyavailable room, while some only care about some preferences but not others.

5. Also required with each reservation is information about a credit card that's used to secure thereservation; credit cards are indicated by a credit card number (which is a sequence of up to 16digits) and an expiration date (a month and a year, such as "January 2007").

6. At any given time, a guest may have multiple reservations; reservation information is removedfrom the database after the guest's reservation is used to put them into an actual room, or whenthe guest cancels the reservation prematurely.

7. The actual information about a guest's stay is stored historically. The database tracks historical

information about every guest's stay in any room in any hotel. At minimum, it's necessary toknow what day the stay began, what day it ended, what room it was, what hotel it was, theoriginal reservation request, and who the guest was. For each guest who has ever reserved orstayed in a room, the database must store the guest's first, middle, and last names, streetaddress, email address, and three phone numbers (home, work, cell). Email addresses and thephone numbers are optional, while the other information is required. Note that if multiple peoplestay in a room, we are only storing information about the one person who reserved and stayed inthe room.

8. A single invoice is generated during a guest's stay at the hotel, detailing the individual chargesaccrued by the guest. These charges include not only the regular room rate, but also applicabletaxes, as well as charges at the hotel's restaurants, bars, spas, shops, and so on. An invoice isdisplayed either in printed or Web-based form as a sequence of line items, with each line itemconsisting of a description and an amount, such as "Hotel Cafe $29.75". Note that the databasedoes not keep track of, say, the costs of items on the restaurant's menu or the cost of rentingeach room at various times throughout the year; it is assumed that another software systemprovides this information to our database, since our system only handles reservations and billing.

9. When a guest pays his or her bill or a portion of his or her bill a line item is added to the invoicethat indicates how much was paid, and in what form the payment was made (e.g. "Visa $-500.00", in the case of a $500 payment made using a Visa credit card).

10.At the bottom of each invoice is a total balance, which is the sum of the amounts in each of theline items, including both charges and payments. An invoice is considered paid if the amount is$0.00.

8/10/2019 UML BD Problema Rezolvata

2/4

2

8/10/2019 UML BD Problema Rezolvata

3/4

3

Ex. 2. The logistics manager at your company wants to design a database to store the followinginformation about warehouses. Each warehouse has a unique warehouse name. Also, store the city and

state of the warehouse. Products have a unique product id, name, and price. Store the volume(inventory) of each product at each warehouse. Not all products are stored at each warehouse and awarehouse may not store all products. A shipment identified by a unique shipment number has a shippername and contact. A shipment is filled from only one warehouse. Each shipment contains at least oneproduct. Each product shipped has a quantity. A shipment goes to a single customer where eachcustomer is identified by a customer number, name, and address. Draw the ER diagram for a warehousedatabase.

8/10/2019 UML BD Problema Rezolvata

4/4

4

Ex.3.Design an ER diagram for a university. The database needs to keep track of each Instructor with id,name, and address. Each instructor works for one department and each department has at least oneinstructor. The departments have a unique id and a name. Courses are offered by a single departmentand have a number unique to each department. Store the course name, credits, and description. Each

course has at least one section. A section is identified using its associated course number, sectionnumber, year, and semester. Also store the size of the section. Students have student ids and names.Each student has a single instructor as an advisor. Students enroll in one or more sections. A sectionmust have five students or it is cancelled. A section is taught by at least one instructor. State clearlyany assumptions.