Homework 1

Question 1: 10pts Design a database for a public library, including information about patrons and their accounts. Information about a patron includes their name, address, and phone number. Accounts have a card number, and past due amount. Patrons can checkout Books, which have titles, ISBN numbers, and shelving IDs. Books are written by Authors, who have names. Books may also have a single genre. Draw the E/R Diagram for this database. Be sure to include arrows where appropriate, to indicate the arity of a relationship.

Remember to specify keys and indicate appropriate referential integrity constraints

Question 2: 10pts  Modify your solution to Q1 as follows (if needed):

2a: Change your diagram so an account can only have one patron.

2b: Further change your diagram so a patron can only have one account.

2c: Change your original diagram (in Q1) so that a patron can have a set of addresses (which are street-city-state triples) and a set of phones. Remember that we do not allow attribute to have non-primitive types, such as sets, in the E/R model.

2d: Further modify your ER Diagram such that each address can have a set of phone numbers.

Question 3: 10pts    One way to represent “Students” and the grades they get in “Courses” is to use entity sets corresponding to “Students”, “Courses”, and “Enrollments”. Enrollment entities form a connecting entity set between Students and Courses. Enrollment entities 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.

Question 4: 10pts    Given the following short description, draw an ER diagram for the subclassed entities. Be sure to mark the keys, if applicable. You may create attributes.

  • A Novel is a Book, which has one or more protagonists and antagonists, and a single author.
  • A textbook is also a book that has one or more authors. As well as lots of citations.
  • Books, in general, have titles, number of pages, publication date, ISBN.
  • A Novel’s protagonists and antagonists are both People, and they both have names and birthdays.

Note: you also need to show the relationship between the entities.
Hint: you can call authors people too, if you want – There are many correct answers to this question.

Question 5: 5pts   We only had time to talk about the standard (Chen) notation for ER Diagrams, but there exist many other ways to create ER Diagrams. Some are more expressive than others, but they basically do the same thing. Use your Google skills and read up on Crows foot notation. Convert your answer to Q4 into Crows Foot notation.

Question 6: 15pts    For the relations Accounts and Customers here:

Accounts(acctNo, type, Balance)
firstNamelastName idNo account
Customers(firstName, lastName, idNo, account)

5a: Suppose string attributes are all defined as CHAR(25). How much disk space will the tuples in each relation occupy? Show your work.

5b: Suppose string attributes are all defined as VARCHAR(100). How much disk space will the tuples in each relation occupy? Show your work.

5c: When might we choose CHAR instead of VARCHAR? Why?

Question 7: 10pts    In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations, whose schemas are:

Product (maker, model, type)

PC (model, speed, ram, hd, price)

Laptop (model, speed, ram, hd, screen, price)

Printer (model, color, type, price)

The Product relation gives the manufacturer, model number and type (PC, laptop or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types (that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number). The PC relation gives for each model number that is a PC the speed (of the processor, in GHz), the amount of RAM (in MB), the size of the hard disk (in GB), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer products color output (true, if so), the process type (laser or ink-jet, etc), and the price.

Write SQL declarations for each relation.

Note: remember to determine primary keys for each relation

Question 8: 10pts    Convert this E/R Diagram below to a relational database schema.

Question 9: 10pts    This E/R Diagram below represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.

Question 10: 10pts    Convert your answer to Q4 to a relational database schema.

Some exercises are from Garcia-Molina, Ullman, Widom. Database Systems. 2nd Edition.

This is an individual assignment. Discussion is allowed, but students may not write submitted solutions in the presence of a group.

Cite your sources.

Submissions must be uploaded to GradeScope and marked by the due date.