Rental System Er Diagram Example

Information Resources Management

Examination # 1

*****ANSWER KEY *****

70-455

2/20/2001

100 points

This exam is comprised of four sections:

(1) multiple choice (17 points)

(2) "short" answer (29 points)

(3) complete the table, diagram, etc. (34 points)

(4) build an entity-relationship diagram (20 points)

Section 1 - Multiple Choice

For each of the following, circle all correct answers.

More than one answer may be correct.

1. ___________ are responsible for ensuring that the developed system meets the needs of the organization.

**A) users

**B) project manager

**C) database analysts and designers

**D) other technical experts

E) none of the above

2. A strong entity depends on the following:

A) weak entity

B) attribute

C) owner

D) associative entity

**E) none of the above

3. The following methodologies involve repeating the development phases:

**A) RAD without prototyping

**B) RAD with prototyping

**C) Spiral

D) Waterfall

E) none of the above

4. Disadvantages of traditional file processing systems include:

A) reduced data duplication

**B) program-data dependence

C) program-data independence

**D) limited data sharing

E) none of the above

5. Generalization and specialization are useful because

**A) different relationships may be applied to specific subtypes

B) the supertype may have unique attributes

C) the E-R model is more complex and impressive looking

**D) different relationships may be applied to the supertype

E) none of the above

6. Benefits of the waterfall system development lifecycle include:

A) requirements are finalized early

B) the delay before system delivery allows for greater customization

C) sequential dependencies free people to work on other projects

D) the linear, sequential approach closely matches most projects

**E) none of the above

7. On an E-R diagram attributes may correctly be attached to

**A) other attributes

B) relationships

**C) associative entities

**D) strong or weak entities

E) none of the above

8. Data modeling is an important part of system development because

**A) data design influences the design of programs and other system components

B) data is less stable than processes

C) changes are easier and cheaper to make once more development is completed

D) data is generally less complex than processes

E) none of the above

9. Which of the following decisions must be made when developing a personal computer database?

**A) make or buy?

**B) database design

**C) who is responsible for data accuracy?

**D) how to synchronize the database with other databases?

E) none of the above

10. The planning phase of Information Engineering includes:

**A) identification of strategic planning factors

B) development of logical data model

**C) identification of corporate planning objectives

**D) development of enterprise data model

E) none of the above

11. Which of the following criteria should be considered when selecting an identifier (key)?

**A) It should uniquely identify each instance of the entity

B) It should change often

**C) It must always have a value

**D) It should not be a many part composite attribute

E) none of the above

12. An entity that associates the instances of one or more entity types and contains attributes specific to that relationship is called a(n)

A) binary relationship

**B) associative entity

C) connecting entity

D) associative relationship

E) none of the above

13. The "address" attribute for a customer consists of two lines of address, city, state, and zip code. A customer may have more than one "address". The "address" attribute is an example of a ____________ attribute.

A) simple

**B) composite

**C) multivalued

D) derived

E) none of the above

14. A database is:

**A) an organized collection of logically related data

**B) a shared collection of interrelated data designed to meet the informational needs of multiple users

C) data storage that is dependent on program and user views

**D) data storage with physical and logical independence

E) none of the above

15. Disadvantages of database processing systems include:

**A) cost

B) viewing data as a corporate resource

C) program-data dependence

D) program-data independence

E) none of the above

Section 2 - "Short" Answer

1. In developing the E-R diagram for a video rental store, Mary has decided to model the relationship between customer, video, and rental using an associative entity to avoid having to determine ternary relationship cardinality. Customer is related to rental which is related to video. Give a specific example of a "business rule" which would demonstrates that this may not be the best approach. (5 points)

The important thing to remember about creating an associative entity instead of using an ternary relationship is that the entity (rental in this case) might have to act as a separate entity in other relationships. An example of this would be that a rental could have "late fee" or "store branch" related to it.

2. A view or model of a database that is created during development is called a schema. Database development involves three different, but related, types of schemas. What are they? Why are different schemas developed? What is the relationship between them? (10 points)

What are they? (3 points)

conceptual - E-R Diagram

user view (external) - portion of an E-R Diagram for a specific purpose

physical (internal) - actual database

Why are they developed? (3 points)

conceptual - model of data requirements; easy to review and change

external - focus on specific user or program needs; used to build conceptual

internal - actual implementation of database

What is the relationship between them? (4 points)

see figure 2-8, p. 54 in the book

3. Rather than have a single unified database for the entire organization, many organizations have many separate databases, each for a specific function. Identify three of the advantages of database systems that may be lost in this situation.

(6 points)

Any three of the following (2 points each):

reduced duplication/redundancy

improved data sharing

improved data consistency/integrity

flexibility

viewing data as a corporate resource

security

performance

Acceptable only if written assumption that different DBMS are used:

programming productivity

standards

reduced program maintenance

4. Compare and contrast the "lifecycle" of data models developed during waterfall and RAD development methodologies. (8 points)

The key point is to discuss the impact on data models and not just describe each of the methodologies.

Differences (need at least 2, 2 points each)

Waterfall: document requirements, create data model, review and approve, after approval, model is frozen

RAD: iterative process, data model may change with each iteration, not really finalized

Similarities (need at least 2, 2 points each)

same data modeling technique

data model drives actual database implemented

high level data model developed first (for RAD, before 1^st iteration)

Section 3 - Complete the Table, Diagram

1. Complete the given E-R diagram fragment. (4 points each)

1A. Hospital employees must be either medical or non-medical personnel. Medical personnel must be either doctors or nurses.

1B. Movies can be action, sci-fi, comedy, drama, and/or some other genre.

2. Specify the sequence for the database development lifecycle. (6 points)

2	Conceptual Data Modeling
6	Database Maintenance
1	Enterprise Modeling
3	Logical Database Design
5	Database Implementation
4	Physical Database Design and Creation

3. The range of database applications begins at "personal". Identify the other categories and give an example, advantage, and disadvantage of each. (8 points)

Category	Example	Advantage	Disadvantage
personal	Any reasonable example is acceptable	scoring for this question is 1/2 point per cell. See 1/16 slides for list of advantages and disadvantages for each category of database.
workgroup (team)
department (division)
enterprise

4. Below are fragments of entity-relationship diagrams. For the relationship shown, identify its degree, connectivity, cardinality, and existence constraints. If you cannot identify something from the diagram, explain what you would need to identify the constraint. (6 points each)

4A.

Degree	binary (1 point)
Connectivity	one-to-one (1 point)
Cardinality	zero or one employee per parking space (1 point) exactly one parking space per employee (1 point)
Existence	parking space required (1 point) employee optional (1 point)

4B.

Degree	binary (1 point)
Connectivity	one-to-many (1 point)
Cardinality	zero to one professor per course (1 point) zero to three courses per professor (1 point)
Existence	professor is optional (1 point) course is optional (1 point)

Section 4 - Entity-Relationship Diagram

Draw an E-R diagram including attributes for the following. Document any additional assumptions you make. (20 points)

A rental car company classifies the vehicles it rents into four categories: compact, mid-size, full-size, and sport utility. The company wants to record the following data for all vehicles: VID, make, model, year, color, and license plate information (state and number). There are no unique attributes or relationships for any of the four classes of vehicle. The entity type vehicle has a relationship (rents) with a customer entity type. Attributes of a rental include: start date, end date, number of days, rental rate, and insurance selected. Customer has appropriate attributes.

Scoring:

1/2 point per missing attribute

1 point for missing key and derived attributes

2 points for all other errors

cardinalities (each end is 2 points - 1 pt cardinality; 1 pt existence)

entities

associative entity

Note that generalization/specialization is not needed for this diagram.