Homework 5

Due: October 31, 2019 by Dr. Fenner

Reading

  • §3.4, and
  • §4.1 – 4.5

To Submit

Written

Required
Graduate Students Only

SQL

Do Exercise 6.3.1 from the textbook, using subqueries as described there and in class. Submit as you did in the last homework assignment.

Reference

Exercise 4.1.1

Design a database for a bank, including information about customers and their accounts. Information about a customer includes their name, address, phone, and Social Security number. Accounts have numbers, types (e.g. savings, checking) and balances. Also record the customer(s) who own an account. Draw the E/R diagram for this database. Be sure to include arrows where appropriate, to indicate the multiplicity of a relationship.

Exercise 4.1.2

Modify your solution to Exercise 4.1.1 as follows:

  1. Change your diagram so an account can have only one customer
  2. Further change your diagram so a customer can have only one account.
  3. Change your original diagram of Exercise 4.1.1 so that a customer can have a set of addresses (which are street-city-state triples) and a set of phones. Remember that we do not allow attributes to have nonprimitive types, such as sets, in the E/R model.
  4. Further modify your diagram so that customers can have a set of addresses, and at each address there is a set of phones.

Exercise 4.1.6

Design a genealogy database with one entity set: People. The information to record about persons includes their name (an attribute), their mother, father, and children.

Exercise 4.1.7

Modify your “people” database design of Exercise 4.1.6 to include the following special types of people:

  1. Females.
  2. Males.
  3. People who are parents.

You may wish to distinguish certain other kinds of people as well, so relationships connect appropriate subclasses of people.

Exercise 4.2.1

In Fig. 4.14 is an E/R diagram for a bank database involving customers and accounts. Since customers may have several accounts, and accounts may be held jointly by several customers, we associate with each customer an “account set,” and accounts are members of one or more account sets. Assuming the meaning of the various relationships and attributes are as expected given their names, criticize the design. What design rules are violated? Why? What modifications would you suggest?

Fig. 4.14: A poor design for a bank database

Exercise 4.2.3

Suppose we delete the attribute address from Studios in Fig. 4.7. Show how we could then replace an entity set by an attribute. Where would that attribute appear?

Fig. 4.7: A relationship with an attribute

Exercise 4.3.1

For your E/R diagrams of

  1. Exercise 4.1.1
  2. Exercise 4.1.6
  1. Select and specify keys, and
  2. Indicate appropriate referential integrity constraints.

Exercise 4.4.1

One way to represent students and the grades they get in courses is to use entity sets corresponding to students, to courses, and to “enrollments.” Enrollment entities form a “connecting” entity set between stuents and courses can be used to represent not only the fact that a student is taking a certain course, but the grade of the student in the course. Draw an E/R diagram for this situation, indicating weak entity sets and the keys for the entity sets. Is the grade part of the key for enrollments?

Exercise 4.5.1

Convert the E/R diagram of Fig. 4.29 to a relational database schema

Fig. 4.29: An E/R diagram about airlines

Exercise 6.3.1

Write the following queries, based on the database schema:

Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)

of Exercise 2.4.1. You should use at least one subquery in each of your answers and write each query in two significantly different ways (e.g. using different sets of the operators EXISTS, IN, ALL, and ANY).

  1. Find the makers of PC’s with a speed of at least 3.0.
  2. Find the printers with the highest price.
  3. Find the model number of the item (PC, laptop, or printer) with the highest price.
  4. Find the maker of the color printer with the lowest price.
  5. Find the maker(s) of the PC(s) with the fastest processor among all those PC’s that have the smallest amount of RAM