Question 1: 10pts Design a database for the St. Joseph County Music Festival, including information about Stages, Bands, Musicians, Attendees, Tickets, Volunteers, and Performances. Whether these are entity sets or relationships is up to you.
- Attendees buy tickets for the festival. Some tickets are VIP, others general admission.
- Bands perform on different stages at scheduled times. Each band has multiple musicians.
- Musicians can play in multiple bands, but not at the same time slot.
- Volunteers work at stages, helping with setup, security, or ticketing. Volunteers can work multiple shifts.
- Performances should track the stage, band, time, and setlist.
- Attendees can RSVP for special workshops or meet-and-greets with bands.
Draw the E/R Diagram for this database. Be sure to include constraints and relationship arity where appropriate.
Question 2: 5pts Change your diagram (from Q1) to include one new Entity Set and one new Relationship of your choice. Make it something interesting that adds a new dimension to your festival database. Draw the new entity set and relationship on your diagram, and describe your addition in a few sentences. Explain how it connects to your existing entities and why it makes your database more complete or realistic.
Question 3: 5pts One way to represent “Students” and the grades they get in “Courses” is to use entity sets corresponding to “Students”, “Courses”, and “Enrollments”. Enrollments connect Students and Courses. Enrollments 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 entity sets and the keys for the entity sets.
Question 4: 5pts Consider again the example from Q1/Q2 of the Music Festival database. Performances are typically broken down by type: MainStagePerformance, AcousticStagePerformance, WorkshopPerformance, and VIPPerformance. Reconsider your answer to Q2 and this time use sub-entity sets for these performance types. Redraw relationships in your diagram to make it more faithful to how you see the festival operating.
Hint: Remember that a sub-entity must always have a reason to exist, i.e., it should have at least one attribute or relationship that is not in its parent.
FAQ – You dont need to redraw the whole diagram here, just show the changes you made if you wish.
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.
FAQ – redraw the whole diagram including the changes from Q4
Question 6: 10pts Now let’s consider a banking domain. For the relations Accounts and Customers here:
| acctNo | type | balance |
|---|---|---|
| 12345 | savings | 12000 |
| 23456 | checking | 1000 |
| 34567 | savings | 25 |
| firstName | lastName | ID | account |
|---|---|---|---|
| Robbie | Banks | 901222 | 12345 |
| Linda | Hand | 805333 | 12345 |
| Linda | Hand | 805333 | 23456 |
6a: Suppose string attributes (type, firstName, lastName) are all defined as CHAR(20). How much disk space will the tuples in each relation occupy? Show your work.
6b: Suppose string attributes are all defined as VARCHAR(200). 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: 5pts Here we introduce a relational database schema for a Music Festival Gear Inventory. The database consists of four relations:
- Gear (brand, model, category)
- Speaker (model, power, weight, price)
- Microphone (model, type, frequency_range, price)
- Instrument (model, type, material, price)
The Gear relation gives the manufacturer, model number, and category (Speaker, Microphone, Instrument) of various festival gear items. We assume that model numbers are unique across all manufacturers and categories. The Speaker relation gives, for each speaker model, the power (in watts), the weight (in kilograms), and the price. The Microphone relation gives, for each microphone model, the type (dynamic, condenser, ribbon), the frequency range (in Hz), and the price. The Instrument relation gives, for each instrument model, the type (guitar, keyboard, drums, etc.), the material (wood, metal, plastic, etc.), and the price.
Your task: Write SQL declarations for each relation. Determine primary keys for each relation.
Question 8: 5pts (from Garcia-Molina, p165) Convert this E/R Diagram below to a relational database schema.
Question 9: 5pts (from Garcia-Molina, p165) 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: 5pts Convert your answer to Q4 to a relational database schema.
FAQ — the whole schema, but just the new things from Q4.
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.