Homework 1

Question 1: 10pts Design a database for the South Bend Symphony Orchestra, including information about the musicians, instruments, concerts, sheet-music, and composers. Information about a musician includes their name, tenure, and phone number. Instruments have a type, price, and condition. Concerts occur on a date and have a location and attendance. Sheet-Music, i.e., the pieces of paper with music written on them, come from a publisher with several parts per instrument; they are assigned to musicians and are written by a composer.

Draw the E/R Diagram for this database. Be sure to include constraints and arity where appropriate.

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

2a: Change your diagram so that multiple pieces of music can be assigned to the same musician.

2b: Change your original diagram (in Q1, not 2a) so that a concert can only have a single composer.

2c: Change your original diagram (in Q1, not 2a or 2b) to a new Entity Set and Relationship of your choice. Describe this addition in your own words.

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 the entity sets and sub-entity-sets. Be sure to mark the keys, if applicable. You will need to create/invent attributes.

  • Employees at Notre Dame have all sorts of official titles. A Professor is an Employee, and so is an Administrator. Professors have different tracks: TeachingProfessors, and TenureTrackProfessors.
  • All Professors and GraduateStudents teach Courses, but only TenureTrackProfessors can advise GraduateStudents.
  • Of course, any Professor can write a Paper with or without a GraduateStudent. But only TenureTrackProfessors can serve on a dissertation committee of a GraduateStudent. In fact there must be at least three TenureTrackProfessors on each dissertation committee.
  • The dissertation committee has a date, dissertation title, and pass/fail vote for each committee member.

Hint: 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 ID account
Customers(firstName, lastName, idNo, account)

6a: Suppose string attributes (type, firstName, lastName) are all defined as CHAR(15). How much disk space will the tuples in each relation occupy? Show your work.

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

6c: 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.