Chapter, exercise and page numbers refer to the course text Database Management Systems, 3rd edition, by Ramakrishnan and Gehrke
Problem 1:
Part a Chapter 2, exercise 2.2, pg. 52, part 1.
Part b Chapter 2, exercise 2.2, pg. 52, part 2.
Problem 2:
Consider the following informal description of a database that holds
information for a service providing seeing-eye dogs to visually-impaired
clients.
The database records clients who have a seeing-eye dog or are waiting to receive a seeing-eye dog. A client can have at most one seeing-eye dog at a time. The client's name, address, gender, date of birth, and information about the type of visual impairment are recorded. The name and address serve to identify each client.The database keeps track of all the dogs it handles, distinguishing those that are currently trained and in service or ready for service from those that are in training. Each dog has a unique 8-digit ID. The breed and date of birth are recorded. When the dog has been placed with a client, the client is recorded. Only a trained dog can be placed. A dog can be placed with only one client at a time.
The database also records the staff of trainers who work with both seeing-eye dogs and clients. Each trainer is identified by social security number. Also recorded are name, address, years with the service, and licensing information. The database keeps track of with which dogs each trainer has worked and with which clients a trainer has worked. Each trained dog is certified by one trainer as ready for service; one such certifying trainer must be recorded for each dog, also noting the date of certification. Several other trainers may also have been involved in a dog's training.
Draw an ER diagram describing the database. Represent the
constraints on data and the constraints on relationships between data.
Are there any constraints in the informal specification that you cannot
represent in the ER diagram? If so, for each such constraint state why
you cannot represent it.
Problem 3 Chapter 2, exercise 2.6, pg. 53-54.
Problem 4 The subclasses in an "IS A" hierarchy can satisfy each of the two constraints "overlapping" and "covering" independently. Give an example for each of the four possibilities: satisfying both constraints; satisfying neither constraint; or satisfying exactly one constraint (two possibilities). Your examples should be easy to understand and should not be taken from the text or lecture.
Problem 5. For your ER diagram solving Problem 2 above, give the set of relations and constraints defining the relational database schema capturing the ER specification. Explain which constraints represented by the entity-relationship model can be captured in the relational model and how, and which constraints you cannot capture in the the relational model. You may use SQL or the specification style used in lecture.
Problem 6. For your ER diagram solving Problem 3 above, give the set of relations and constraints defining the relational database schema capturing the ER specification. Explain which constraints represented by the entity-relationship model can be captured in the relational model and how, and which constraints you cannot capture in the the relational model. Again, you may use SQL or the specification style used in lecture.