School of Computer Science Western Illinois University

IS 342, In-class exercises Amaravadi

ER APPROACH

I. Develop ER Models for the following situations using appropriate notation, [or simply answer the questions if they are theoretical]

  1. Vacation Property Rentals is a company that rents properties to holiday-goers (renters). Renters rent properties from VPR by paying an advanceand signing rental agreements The following information needs to be stored: rental start date, end date, rental fee, advance paid, property id, location and address, renter’s driver license, renter name, property id.
  1. A car dealership is interested in maintaining records about the sale of its cars. It needs the following information: the date the sale was made, the car identification#, make, model, invoice price, selling price, name of salesperson, his/her cumulative sales record, name address and phone# of customer.
  1. Design a report for the problem above that lists a) cumulative sales record for an individual salesperson, b) the customer, customer address, model of the car, invoice price and selling price. What entity classes does the report involve? What does this imply for the design of the database? (How can we get this information from tables in the database?)
  1. As you all know Olympics is considered the biggest international sporting event in the world. There are a number of sports such as swimming, pole vaulting, skiing etc. Sports are designated by a three letter code and the full name. Each sport has a number of events such as qualifier, semi-finals and finals. They are held in blocks of time (such as morning or evening) on certain calendar dates. Some events need multiple time blocks. Each event for each sport has finalists (only 1st, 2nd and 3rd). The finalist’s name, position, points earned and country are recorded. List eclasses and attributes and draw an ER model using the correct notation.
  1. Ingraminc., a book distributor wants to develop a database of its publishers, book authors and book stores. A book distributor buys books from publishers and sells them to book stores. Publishers such as McGraw Hill and Prentice Hall sell books directly or more frequently rely on distributors. They have a contact person in sales, with whom the distributor places orders. Depending on the number of copies ordered [“volume”], publishers give discounts to distributors, ranging from 25%-50 %. There are several titles per order and a given title can be ordered multiple number of times. The distributor needs information on the: Title, ISBN#, Volume Ordered, Qty in stock, List price and Discount. The distributor needs to have information on the publisher code, publisher name, address, contact name and contact phone. Each book can have multiple authors. An author could write several books. The distributor needs to have information on the author name address and phone, so that they may be contacted for promotion purposes. For the stores, the distributor wants information on the contact person, phone#, Store#, name and location.
  2. Represent using mini-max notation, the following ER fragments:
  1. “A person can be unemployed or have multiple jobs.”
  2. “A plane has at least one engine and an engine belongs to one and only one plane.”
  3. “A customer cannot win more than one prize.”
  4. “Every ship has at least one steward.”

II. Figure 1. is a Salesperson Annual Summary Report prepared by the Midwest Automotive Company. Prepare an E-R diagram for this.

Figure 1: SALESPERSON ANNUAL SUMMARY REPORT, 20XX

REGION SALESPERSON / QUARTERLY ACTUAL SALES
FIRST SECOND THIRD FOURTH
NORTHWEST & Hawthorne
MOUNTAIN Baker
Hodges
MIDWEST & Stephenson
MID-ATLANTIC Swenson
Franklin
NEW ENGLAND Brightman
Kennedy / 22000 17500 21300 19800
19500 14600 13300 12000
11000 9500 17000 12000
7500 6600 8000 8000
11000 9800 10000 9000
11000 12000 17000 9000
25000 28000 26000 33000
31000 19000 27000 28000

III.Draw an E-R diagram for the following situation

Fox Construction Inc. is an engineering firm with approximately 500 employees. A database is required to keep track of all employees, their skills and projects assigned and departments worked in. Every employee has a unique id assigned by the firm to store his or her name, date of birth and dt_joined. If an employee is currently married to another employee of Fox Inc., then it is required to store the date of marriage and who is married to whom. However, no record of marriage need be maintained if the spouse of an employee is not an employee of the firm. Each employee is given a job title (for example, engineer, secretary, foreman, and so on). We are interested in collecting more data specific to the following types: engineer and manager. The relevant data to be recorded for engineers is the type of construction they specialize in (office buildings, factories etc.) and for managers it is the length of service. An employee does only one type of job at any given time, and we need to retain information material for only the current job for an employee.

There are eleven different departments, each with a unique name. An employee can report to only one department., while there can be a number of employees in a given department. Each department has a phone number.

To procure various kinds of equipment, each department deals with many vendors. A vendor typically supplies equipment to many departments. It is required to store the name and address of each vendor and the date of the last meeting between a department and a vendor.

Many employees can work on a project. An employee can work on many projects (for example, Southwest Refinery, California Petrochemicals, and so on) but can only be assigned to at most one project in a given city. For each city, we are interested in its state and population. An employee can have many skills (preparing material requisitions, checking drawings, and so on), but he or she may use only a given set of skills on a particular project. (For example, an employee MURPHY may prepare requisitions for Southwest Refinery project and prepare requisitions as well as check drawings for California Petrochemicals.) An employee uses each skill that he or she possesses in at least one project. Each skill is assigned a number. A short description is required to be stored for each skill. Projects are distinguished by project numbers. It is required to store the estimated cost of each project.

IV.DEVELOP AN ER MODEL BASED ON THE FOLLOWING ______.

KLAUS

COMPANIES 2/21/2016 Order# 36577

ORDER DATE: 2/21/16

SHIP TO: 85385FROM: KLAUS COMPANIES

WESTERN ILL UNIVERSITY- REPAIR8400 N. ALLEN ROAD

101 STIPES HALLPEORIA, IL 61615

COLLEGE OF BUSINESS- DEANS OFFICE

MACOMB, IL 61455 USASOLD TO:

WESTERN ILL UNIVERSITY

227 SHERMAN HALL

MACOMB, IL 61455 USA

FILLED BY SALESPERSON
AA / CUSTOMER
LORI BOYER -- REPAIR

CARRIER

UPS FEDEX USPS /

TERMS

Net 30 Days

INSTRUCTIONS

/ADD- WARRANTY REPAIR- ALIGNED MECH CLEANED PATH BC

SEQ.NO

/ ITEM NUMBER /

DESCRIPTION

/ QUANTITY

SHIPPED

/

QUANTITY

ORDERED

/ B/O.
8400 N. ALLEN ROAD
PEORIA, IL 61615
309-691-4840 / 3103 RESEARCH RD.
CHAMPAIGN, IL 61821
217-356-1896 / 2400 W. SCHNEIDMANN QUINCY, IL 62301
217-2237560

PACKING SLIP