Answers to Review Questions
1. Name and discuss the different levels of data abstraction as defined by ANSI/SPARC.
2. What are the main building modules of the Entity Relationship model? Discuss each one.
3. What is a composite entity, and when is it used?
4. Why is data modeling considered a "communication tool?"
5. Suppose you are working within the framework of the conceptual model in Figure Q3.5:
6. Figure Q3.5 The Conceptual Model for Question 5
Given the conceptual model in Figure Q3.5, create two external models.
6. How are database models related to the level of data abstraction? (Hint: Why do database designers find it so much easier to develop designs based on the relational model?)
7. How would you (graphically) identify each of the following E-R model components?
8. The Hudson Engineering Group (HEG) has contacted you to create a conceptual model whose application will meet the expected database requirements for its training program. The HEG administrator gives you the following description of the training group's operating environment:
The HEG has 12 instructors and can handle up to 30 trainees per class. HEG offers five "advanced technology" courses, each of which may generate several classes. If a class has fewer than 10 trainees in it, it will be canceled. It is, therefore, possible for a course not to generate any classes during a session. Each class is taught by one instructor. Each instructor may teach up to two classes or may be assigned to do research. Each trainee may take up to two classes per session.
Given this information, do the following:
a. Draw the E R diagram for HEG.
b. Describe the relationship between instructor and course in terms of connectivity, cardinality, and existence dependence.
d. Discuss the difference between a composite key and a composite attribute. How would each be indicated in an E-R diagram?
e. What two courses of action are available to a designer when a multivalued attribute is encountered?
f. What is a derived attribute? Give an example.
g. How is a relationship between entities indicated in an E-R diagram? Give an example, using the Chen and Crow's foot data models.
13. What is a weak entity, and how is it represented in an E-R diagram? Give an example, using the Chen and Crow's foot data models
14. How is a composite entity represented in an E-R diagram, and what is its function? (Illustrate both the Chen and the Crow's Foot models.)
15. Given the following business rules, create the appropriate Chen and Crow's Foot E-R diagram for each of the specified relationships:
a. A company operates four departments.
b. b. Each department in part (a) employs employees.
c. c. Each of the employees in part b may or may not have one or more dependents.
d. Each employee in part (c) may or may not have an employment history.
16. Using the E-R diagram components developed in question 15, create Chen and Crow's Foot E-R diagrams that includes all the components.
17. What three (often conflicting) database requirements must be addressed in database design?
18. Briefly, but precisely, explain the difference between single-valued attributes and simple attributes. Give an example of each.
19. What are multivalued attributes, and how can they be handled within the database design?
The final four questions are based on the E-R diagram in Figure Q3.20.
20. Write the proper cardinalities for (a,b)___(0,N)___ (c,d)___(1,1)___
(g,h)___(1,1)___ (i,j)___(1,1)___ (k,l)___(0,N)__ (m,n) __0,N___ (o,p) __1,1___
21. Write the proper connectivities for W__1__ X__M___ Y__M___ Z__1___
22. What two attributes must be contained in the composite entity? Use proper terminology in your answer
23. Describe precisely the composition of the weak entity's primary key. Use proper terminology in your answer.
24. Convert the Chen ERD in Figure Q3.20 to a Crow's Foot ERD.
Please see file 3-24.gif and file 3-24.vsd on the CD and website for the Crow's foot diagram for number 24.Answers to Problems
The first three problems are based on the E-R model in Figure P3.1.
Figure P3.1 The ERD for Problems 1-3
1. Use the following business rules to write all appropriate connectivities in the E-R diagram:
a. A department employs many employees, but each employee is employed by one department.b. Some employees, known as "rovers," are not assigned to any department.
c. A division operates many departments, but each department is operated by one division
d. An employee may be assigned to many projects, and a project may have many employees assigned to it.
e. A project must have at least one employee assigned to it.
f. One of the employees manages each department, and each department is managed by only one employee.
g. One of the employees runs each division, and each division is run by one employee.
2. Write all the cardinalities into the model.
3. Modify the E-R model by splitting the M:N relationship into two 1:M relationships that are connected through a composite entity. Then rewrite the connectivities and cardinalities to match the changes you have made.
4. Convert the Chen model you have developed in problems 1-3 to a Crow's Foot model. Include at least the minimum number of attributes required to implement the model.5. Temporary Employment Corporation (TEC) places temporary workers in companies during peak periods. TEC's manager gives you the following description of the business:
• TEC has a file of candidates who are willing to work.
• If the candidate has worked before, that candidate has a specific job history. (Naturally, no job history exists if the candidate has never worked. Each time the candidate worked, one additional job history record was created.)
• Each candidate has several qualifications. Each qualification may be earned by more than one candidate. (For example, it is possible for more than one candidate to have earned a BBA degree or a Microsoft Network Certification. And clearly a candidate may have earned a BBA and a Microsoft Network Certification.)
• TEC also has a list of companies that request temporaries.
• Each time a company requests a temporary employee, TEC makes an entry in the openings folder. This folder contains an opening number, company name, required qualifications, starting date, anticipated ending date, and hourly pay.
• Each opening requires only one specific or main qualification.
• When a candidate matches the qualification, (s)he is given the job, and an entry is made in the Placement Record folder. This folder contains an opening number, candidate number, total hours worked, and so on. In addition, an entry is made in the job history for the candidate.
• TEC uses special codes to describe a candidate's qualifications for an opening. The list of codes includes:
Code Description
SEC-45 Secretarial work, at least 45 words per minute
SEC-60 Secretarial work, at least 60 words per minute
CLERK General clerking work
PRG-VB Programmer, Visual Basic
PRG-C++ Programmer, C++
DBA-OR Database Administrator, ORACLE
DBA-DB2 Database Administrator, DB2
SYS-1 Systems Analyst, level 1
SYS-2 Systems Analyst, level 2
NW-NOV Network administrator, Novell experience
TEC's management wants to keep track of the following entities:
COMPANY
OPENING
QUALIFICATION
CANDIDATE
JOB_HISTORY
PLACEMENT
Given this information, do the following:
a. Draw the Crow's Foot E-R diagram for this enterprise.
b. Identify all possible relationships.
c. Identify the connectivity for each relationship.
d. Identify the mandatory/optional dependencies for the relationships.
e. Resolve all M:N relationships.6. The Jonesburgh County Basketball Conference (JCBC) is an amateur basketball association. Each city in the county has one team that represents it. Each team has a maximum of 12 players and a minimum of nine players. Each team also has up to three coaches (offensive, defensive, and PT coaches.) Each team plays two games (home and visitor) against each of the other teams during the season.
Given these conditions, do the following:
a. Identify the connectivity of each relationship.
b. Identify the type of dependency that exists between CITY and TEAM.
c. Identify the cardinality between teams and players, and between teams and city.
d. Identify the dependency between coach and team, and between team and player.
e. Draw the Chen and Crow's Foot E R diagram to represent the JCBC database7. Automata Inc. produces specialty vehicles by contract. The company operates several departments, each one of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV.
When a new vehicle is built, the department places an order with the Purchasing Department to request specific components. Automata's Purchasing Department is interested in creating a database to keep track of orders and to accelerate the process of delivering materials.
The order received by the Purchasing Department can contain several different items. An inventory is maintained so that the most frequently requested items are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item(s) is (are) in inventory. If an item is not in inventory, it must be ordered from a supplier. Each item may have several suppliers.
Given this functional description of the processes encountered at Automata's Purchasing Department, do the following:
a. Identify all the main entities.
b. Identify all the relations and connectivities among entities.
c. Identify the type of existence dependency in all relations
d. Give some examples of the types of reports that can be obtained from the database.
8. Create an ERD based on the Crow's Foot model, using the following requirements.
• An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative.
• The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices.
• An INVOICE may include many detail lines (LINE) which describe the products bought by the customer.
• The product information is stored in a PRODUCT entity.
• The product's vendor information is found in a VENDOR entity.
9. Given the following brief summary of business rules for the ROBCOR catering service, and using the Crow's Foot E-R methodology, draw the fully-labeled ERD. Make sure to include all appropriate entities, relationships, connectivities, and cardinalities.
Each dinner is based on a single entree, but each entree can be served at many dinners. A guest can attend many dinners, and each dinner can be attended by many guests. Each dinner invitation can be mailed to many guests, and each guest can receive many invitations.10. Using the Crow's Foot methodology, create an ERD that can be implemented for a medical clinic, using at least the following business rules:
• A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor, and each appointment references a single patient.
• Emergency cases do not require an appointment. However, an emergency is entered into the appointment book as "unscheduled" for appointment management purposes.
• If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment.
• Each visit updates the patient's records to provide a medical history.
• Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can bill many patients.
• Each bill must be paid. However, a bill may be paid off in many installments, and a payment may cover more than one bill.
• A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company.
• If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.11. Tiny College is so pleased with your design and implementation of its student registration/ tracking system that it wants you to expand the design to include its motor pool. A brief description of operations follows:
Faculty members may use the Tiny College-owned vehicles for officially-sanctioned travel. For example, its vehicles may be used by faculty members to travel to off-campus learning centers, to travel to locations at which research papers are presented, to transport students to officially sanctioned locations, and to travel for public service purposes. The vehicles used for such purposes are managed by Tiny College's TFBS (Travel Far But Slow) Center.
Using reservation forms, each department may reserve vehicles for its faculty, who are responsible for filling out the appropriate trip completion form at the end of each trip. The reservation form includes the expected departure date, vehicle type required, destination, and the authorized faculty member. When the faculty member arrives to pick up the vehicle, (s)he must sign a check-out form to log the vehicle out and to pick up a trip completion form. (The TFBS employee who releases the vehicle for use also signs the check-out form.) The faculty member's trip completion form includes the faculty member's identification code, the vehicle's identification, the odometer readings at the start and end of the trip, maintenance complaints, if any, gallons of fuel purchased, if any, and the Learnwell College credit card used to pay for such fuel. If fuel has been purchased, the credit card receipt must be stapled to the trip completion form. Upon receipt of the Faculty Trip Completion form, the faculty member's department is billed at a mileage rate based on the vehicle type (sedan, station wagon, panel truck, minivan, minibus) used. HINT: Do NOT use more entities than are necessary. Remember the difference between attributes and entities!
All vehicle maintenance is performed by TFBS. Each time a vehicle requires maintenance, a maintenance log entry is completed on a pre-numbered maintenance log form. The maintenance log form includes the vehicle identification, a brief description of the type of maintenance required, the initial log entry date, the date on which the maintenance was completed, and the identification of the mechanic who released the vehicle back into service. (Only mechanics who have an inspection authorization may release the vehicle back into service.)
As soon as the log form has been initiated, the log form's number is transferred to a maintenance detail form; the log form's number is also forwarded to the parts department manager, who fills out a parts usage form on which the maintenance log number is recorded. The maintenance detail form contains separate lines for each maintenance item performed, the parts used, and the identification of the mechanic who performed the maintenance item. When all the maintenance items have been completed, the maintenance detail form is stapled to the maintenance log form, the maintenance log form's completion date is filled out, and the mechanic who releases the vehicle back to service signs the form. The stapled forms are then filed, to be used later as the source for various maintenance reports.
TBFS maintains a parts inventory, including oil, oil filters, air filters, belts of various types, and so on. The parts inventory is monitored daily to monitor parts usage and to re-order parts that reach the "minimum quantity on hand" level. To track parts usage, the parts manager requires each mechanic to sign out the parts that are used to perform each vehicle's maintenance; the parts manager records the maintenance log number under which the part is used.
Each month, TFBS issues a set of reports. These reports include the mileage driven by vehicle, by department, and by faculty members within the department. In addition, various "revenue" reports are generated by vehicle and department. A detailed parts usage report is also filed each month. Finally, a vehicle maintenance summary is created each month.
Given this brief summary of operations, draw the appropriate (and fully-labeled!) E-R diagram. Use the Chen methodology to indicate entities, relationships, connectivities, and cardinalities.12. Given the following information, produce an ERD - based on the Crow's Foot model - that can be implemented. Make sure to include all appropriate entities, relationships, connectivities and cardinalities.
EverFail company is in the quick oil & lube business. Although customers bring in their cars for what is described as quick oil changes, EverFail also replaces windshield wipers, oil filters, and air filters, subject to customer approval. The invoice contains the charges for the oil used, all parts used, and a standard labor charge. When the invoice is presented, customers pay cash, use a credit card, or write a check. EverFail does not extend credit. EverFail's database is to be designed to keep track of all components in all transactions.
Given the high parts usage of the business operations, EverFail must maintain careful control of its parts (oil, wipers, oil filters, air filters) inventory. Therefore, if parts reach their minimum on hand quantity, the part in question must be reordered from an appropriate vendor. EverFail maintains a vendor list which contains both the vendors actually used as well as potential vendors.
Periodically, EverFail mails updates to customers, based on the date of the car's service. EverFail also tracks each customer's car mileage.
Points. Use the following descriptions of the operations of RC_Models Company to complete this exercise.• A customer may generate many invoices
• Each invoice is generated by only one customer
• Some customers have not (yet) generated an invoice
• Some customers come from FineScale Modeler's magazine (? I only put this in here because I suspect that the company will want to know how many people from FineScale Modeler's magazine became customers)
• A customer has one address
• A customer can ship to many addresses
• A billing address and shipping address can be different
• A product belongs to at least one product category (?? The description does not indicate more than two categories, model and decals, but a better design would include the possibility for multiple overlapping categories - ie. Not only model and decals but WWII, SciFi, Railroad, etc.)
• A product is made by at least one manufacture
• A product can be made by many manufactures
• A product is a product at a particular scale (the description claims that a product has many scales, but things like units in stock, reorder #, manufacturer part number etc work better if a product has a single scale and then are grouped by product group - I also tossed around the idea of having something called an ‘item' that was a product at a particular scale but it degenerated into a product that is part of a product group).
• A product has n units in stock
• A product has a retail (customer) price
• A product has a low water mark (reorder product when stock drops below this value)
• A product has a last reorder date
• A product has a last sold date (last time this product was sold to a customer)
• An invoice has 1 or more products
• An invoice has a shipping charge
• An invoice has sales tax
• An invoice must be in one and only one state (‘order created', ‘back ordered', ‘checking credit', ‘waiting for packing', ‘billed', ‘waiting for shipping', and ‘shipped')
• An invoice has a timestamp for each state change
• All items in a ‘waiting for packing' order must be in stock
• An invoice has a payment voucher for the correct amount
• An invoice has a shipping address
• A payment voucher has a card number, billing address, expiration date, holder name, ...? (NOTE: a better design would have a payment voucher abstraction so the database could handle ‘store credit' for returned items, gift cards, and coupons)
• A product request has a customer
• A product request has a date
• A product request has one or more products that information was requested about
• A manufacturer has an address
• A manufacturer has a order web site
• A manufacturer has a minimum order size requirement by product
• A reorder slip has an ordered timestamp and an order received timestamp
• A reorder slip has a list of products, quantities, and price
• A manufacture/product pair has a whole sale (RC_Models) price
Simplifications of the model
• Remove source code from customer
• Remove product category and category map tables and replace with a category type field in the products table
• Remove invoice state table and replace with code or text in invoice table
Points. Use the following description of the operations of the RC_Charter2 Company to complete this exercise. A.
• A customer may request many charter trips
• A charter trip is requested by only one customer
• Some customers have not (yet) requested a charter trip
• An employee may be assigned to many charter trips
• Each charter trip may have many employees assigned to it
• Each charter trip has one and only one plane assigned to it
• Each charter trip may have zero or more passengers
• Each charter trip may have zero or more items of cargo
• Each charter trip may have zero or more associated crew expenses
• Each charter trip has two or more legs
• Each charter trip may have zero or more customer special charges
• Each customer has a line or credit for zero dollars (US) or more
• Each customer has used zero dollars (US) or more of their credit on prior trips
• Each customer may have made zero or more payments to their account
• Each customer may have made one or more special requests resulting in one or more special charges on one or more trips
• A special charge applies to one and only one customer and trip
• Each leg of a trip may have incurred a wait time
• Each leg of a trip consumes fuel
• Each leg of a trip has a departure and arrival time
• Each leg of a trip belongs to one and only one trip
• Each plane can be assigned to zero or more trips (but NOT at the same time!)
• Each plane is of one and only one type of plane
• Their does not have to be a plane of a particular plane type in the fleet
• There can be more than one plane of a particular plane type in the fleet
• Each type of plane can hold up to n passengers
• Each type of plane can hold up to n tons of cargo
• Each type of plane can hold up to m cubic feet of cargo
• Each type of plane can fly up to n miles on one leg of a trip
• Each type of plane requires a crew with a specific set of functional requirements (functional requirements are things like ‘load master', ‘instrument only pilot', ...)
• Each type of requirements can be specified zero or more times for each type of plane
• A single crew member can fulfill zero or more functions
• A job function has a per mile rate and an hourly rate (the rate charged is determined if the function is required by law or by the customer).
• A customer may require zero or more additional job functions (possibly new crew members, possibly upgraded crew members).
• Each job function has one or more tests that are required to be taken and passed at regular and irregular intervals to ensure regulatory compliance
• A single test may be taken by the same employee zero or more times
• A single employee can take zero or more different tests
• A single employee can take the same test zero or more times
• A log of all tests taken by the employee while at the company and just prior to the company will be kept in the database
• A single employee can fill out zero or one expense report per trip
• A single employee can fill out zero or more expense reports over time
• An expense report can contain one or more expense items
• An expense item can only be in one and only one expense report
• Each expense item has a cost and a date
• Each expense item is associated with one and only one expense type (food, hotel, local transportation, ...)
• Each expense type can be associate with zero or more expense items
Complete all the answer of the questions properly.