MULTIPLE CHOICE. Choose the one alternative that best completes the statement or answers the question. (1 point each)
2) The _____ rule specifies that an entity can be a member of only one subtype at a time.
4) For the following diagram, which answer is true?
A) Each employee can supervise 1 to many employees.
B) Each employee can manage many departments.
C) Each employee works in one department.
D) A and B
E) A and C
5) A relationship between the instances of a single entity type is called a(n) _____ relationship.
6) An attribute or attributes that uniquely identify each row in a relation is called a(n)
A) field pointer.
C) primary key.
D) foreign key.
E) duplicate key.
8) Data modeling may be the most important part of the systems development process because
A) data characteristics are important in the design of programs and other systems components.
B) The data in a system are generally more complex than processes, and play a central role in development.
C) Data are less stable than processes.
D) A and B.
E) B and C.
10) A database is an organized collection of _____ related data.
E) none of the above
14) Which of the following is an entity type on which a strong entity depends?
E) none of the above.
15) Subtypes should be used when
A) there are attributes that apply to some, but not all instances of an entity type.
B) supertypes relate to objects outside the business.
C) the instances of a subtype participate in a relationship that is unique to that subtype.
D) A and B
E) A and C
16) In the following diagram, what type of relationship is depicted?
A) unary B) ternary
C) quad D) binary
E) none of the above
17) Disadvantages of traditional file processing systems include
A) reduced data duplication.
B) program-data dependence.
C) limited data sharing.
D) B and C.
E) A and B.
20) In the following diagram, which of the answers below is true?
A) Each patient has one or more patient histories.
B) Each patient has one and only one visit.
C) Each patient history belongs to one and only one patient.
D) A and B
E) A and C
22) A(n) _____ specifies the number of instances of one entity that can be associated with the each instance of another entity.
B) cardinality constraint
D) counter constraint
23) According to the figure, which of the following are subtypes of patient?
A) responsible physician
D) all of the above
E) none of the above
25) Duplication of data in files may cause
A) data integrity problems.
B) data items with the same names used for different purposes.
C) data items with different names in different files.
D) A and C
E) all of the above.
27) Which of the following is NOT an advantage of database systems?
A) better data quality
B) program-data independence
C) redundant data
D) standards enforcement
E) reduced program maintenance
29) Which of the following conditions should exist if an associative entity is to be created?
A) All the relationships for the participating entities are one-to-one.
B) The new associative entity has independent meaning.
C) The new associative entity participates in independent relationships.
D) A and B
E) B and C
31) Which of the following is NOT a function of a DBMS?
A) data storage, retrieval, and update.
B) logical data dependence.
C) recovery services.
D) integrity services.
E) concurrency control.
SHORT ANSWER. Write the word or phrase that best completes each statement or answers the question. (1 point each)
33) The separation of data descriptors from the application program that uses the data is called _____ _____.
34) An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship is called a(n) _____ _____.
35) The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships is called _____.
36) A DBMS that manages data as a collection of tables in which all data relationships are represented by common values in related tables is called a(n) _____ DBMS.
37) The _____ _____ specifies that an entity instance can simultaneously be a member of two or more subtypes.
38) A _____ _____ specifies the number of instances of one entity that can be associated with each instance of another entity.
39) The _____ _____ addresses the question of whether an instance of a supertype must be a member at least one subtype.
40) A simultaneous relationship among the instances of three entity types is called
a _____ relationship.
42) A logical representation of the data for an organization or for a business area is
called a(n) _____.
44) One important rule for relational databases is that lower level languages cannot bypass the constraints of the database. This is known as _____ and was first suggested by _. _. _____.
45) The process of defining a supertype and one or more subtypes by combining previously identified entities is called _____.
SLIGHTLY LONGER ANSWERS. (2 points each)
46) What is wrong with the figure in question #16?
47) What is wrong with the figure in question #20?
48) What should be placed in the circle in question # 23? Why?
49) Give a specific example of a problem that could occur when keeping time dependent data.
50) Explain how a null is different from a zero or empty character string.
51) Why is this difference between a null and a zero or empty character string important?
52) Give examples of three kinds of additional costs that may be incurred as a result of implementing a database management system. (3 points)
3. The following is part of an E-R model for an insurance company. A policy must be one and only one of the four types listed. All attributes are given.
3A. Correct the E-R Model. (5 points)
Below is documentation for a department store's bridal registry system.
A bridal registry is a method where a bride and groom go to a store and select items that they would like to receive as wedding presents. They then notify the people invited to their wedding at which store they have registered. The wedding guests can then go to the store and review the registry in order to select items that are listed on it. The advantage of this is that the bride and groom are given gifts that they would like to receive and the guests are able to get a gift that is desired. In the past, bridal registries have been used for items like fancy china dishware, crystal glasses and stemware, and sterling silverware. Guests could then buy something as inexpensive as a set of salt and pepper shakers or as expensive as several place settings of sterling silver. While still used in the fine china and silver departments of stores, bridal registries are increasingly also being used for things found in other departments of the store, like furniture, hardware, and power tools.
A department store has a bridal registry. This registry keeps information about the bride, the products that the store carries, and the products for which each bride registers. Some products include several related items: for example, dish sets, include plates, specialty dishes, and serving bowls. Brides typically register for a large number of products, and many brides register for the same products. A bride may also register for several of the same product but may register for different colors or types of each product. For example, a bride may register for bath towels, which typically come in sets (bath towel, hand towel, wash cloth), and may register for 4 blue bath towels, 4 blue/white striped bath towels, 8 blue hand towels, and 16 white wash cloths. The registry also must keep track of what registered items have been purchased for the bride so that a current list showing items registered for but not yet purchased can be produced for friends and family who come to the store. Information kept about brides includes name and finance's name, address and phone number. Product information includes product number, description, and store department in which the product can be found.
Brides (and grooms) come into the store and register for items that they would like to receive as wedding gifts, for example, a china pattern or sterling silver dinnerware set. The couple then notifies people who are invited to the wedding that they are "registered" at the store. Individuals wishing to purchase something for the couple then come into the store and are given a copy of the registry that shows items that have not yet been purchased. They can then purchase items on the list which then have to be marked as having been bought. For example, someone may come in and buy only one place setting of the bride's china. If eleven other people do the same thing, the bride will receive twelve full place settings of new china.
Complete the following based on this description.
Holiday Travel Vehicles sells new recreational vehicles and travel trailers. When new vehicles arrive at Holiday Travel, a new vehicle record is created. Included in the new vehicle record is a vehicle serial number, name, model, year, manufacturer, base cost, and manufacturer installed options. Holiday Travel sells different kinds of vehicles and trailers: motor homes, towed travel trailers, and "5th wheels," which require a pickup truck to tow them. When a customer arrives at Holiday Travel, he/she works with a salesperson to negotiate a vehicle purchase. When a purchase has been agreed to, a sales invoice is completed by the salesperson. The invoice summarizes the purchase, including full customer information, information on the trade-in (if any), the trade-in allowance, and information on the purchased vehicle. If the customer requests dealer-installed options, they will be listed with the price for each on the invoice as well. The invoice also summarizes the final negotiated price, plus any applicable taxes and license fees. The transaction concludes with a customer signature on the sales invoice.
Customers are assigned a customer ID when they make their first purchase from Holiday Travel. Name, address, and phone number are recorded for the customer. The trade-in is described by a serial number, make, model, and year. Installed options are described by an options code, description, and price. Options may be installed by either the manufacturer or Holiday Travel. Some options may be installed by either but some may only be installed by the manufacturer. Options installed by the manufacturer are included in the base cost of the vehicle. Each invoice will list just one customer. A person does not become a customer until they purchase a vehicle. Over time, a customer may purchase a number of vehicles from Holiday Travel. Each salesperson also maintains a list of contacts or potential customers who have visited Holiday Travel but have not yet made a purchase. The information kept for contacts is the same as what is kept for customers with two additions, last contact date and salesperson comments. Every invoice must be filled out by only one salesperson. A new salesperson may not have sold any vehicles, but experienced salespeople have sold many vehicles. Each invoice lists one new vehicle. If a new vehicle in inventory has not been sold, there will be no invoice for it. Once the vehicle sells, there will be just one invoice for it. A customer may decide to have no options added to the vehicle, or may choose to add many options. An option may or may not be listed on any invoices. A customer may trade in no more than one vehicle on the purchase of a new vehicle. The trade-in vehicle may be sold to another customer, who later trades it in on another Holiday Travel vehicle. Invoices are created for trade-in vehicles that are later sold in the same way as they are for new vehicles.
1. Build an entity-relationship diagram to document the data needs of Holiday Travel. (You do not have to show attributes on the diagram). (35 points)