Spring 2024 — Assignment 1

Due 12-February, 12pm EST (before class)
Purpose:

For this assignment you may work individually or with at most 4 partners (max group size = 5). We strongly encourage you to work with partners as different designers typically have different perspectives. Besides, you can learn from each other.

Answer the following questions / complete the following tasks. All homeworks are due before class on the due date. Please remember that the UVA Honor Code is in effect.

Important note: Database design is a creative task. It requires divergent thinking. Therefore, it is unlikely that your database design and your peers' designs will be similar. Do NOT use ChatGPT or generative AI to do the creative tasks for you.

To expand your experience, you are required to create a digital illustration of your E-R diagrams. You may use any software to draw your diagrams, no hand-drawing. There are several easy to use web-based software (some are free, some offer free trial) that you can use to draw an E-R diagram:

Alternatively, Visio, PowerPoint, Paint / Preview, and Adobe Photoshop can also be useful.

[Total: 100 points]

  1. [6 points] Indicate the correct cardinality and participation (single or double line) by drawing on the following incomplete ER-diagrams based on the given description
    1. (3 points) A college can accept several (possibly 0) students.
      A student enrolls in at least and at most one college.

      partial E-R diagram for cardinality question #1


    2. (3 points) A train ticket can be purchased by (possibly 0) at most one customer.
      A customer can purchase several (possibly 0) train tickets.

      partial E-R diagram for cardinality question #2


  2. [34 points] The following E-R diagram describes births for the hospital database.

    E-R diagram describing Births (for hw1)

    At a birth, there is one baby (twins would be represented by two births), one mother, any number of nurses, and any number of doctors. Suppose, therefore, that we have entity sets Babies, Mothers, Nurses, and Doctors. Suppose we also use a relationship Births, which connects these four entity sets, as suggested in the diagram below. Note that a tuple of the relationship set for Births has the form/schema (baby, mother, nurse, doctor). If there is more than one nurse and/or doctor attending a birth, then there will be several tuples with the same baby and mother, one for each combination of nurse and doctor.

    You may assume that:

    • For every baby, there is a unique mother.
    • For every combination of a baby, nurse, and doctor, there is a unique mother.

    This current E-R diagram is a poor design and some information is missing or not properly presented.

    1. (10 points) Describe what modifications you would suggest. Justify your suggestion.
    2. (24 points) Redraw the E-R diagram to reflect your suggestions.

    Be sure to write down any assumptions you may have made.


  3. [60 points] Imagine you are hired to design a Diversity, Equity, and Inclusion (DEI) platform to help the DEI committees maintain events. Here is the information that you have gathered.
    • An event has a unique ID and a title.
    • A location has a unique location ID, a building name, a room number, and capacity (indicating the number of people the room can hold).
    • Some events are short (minutes or hours). Some events run the entire day.
    • Some events are one day while some events last multiple (consecutive) days.
    • An event may be held in multiple locations. Each location can serve at most one event at a time.
    • Assume events are held in person only. There is no virtual, online, or Zoom event.
    • The system needs to keep track of where (location) and when (date/time) the event starts and ends. If the event is held in multiple locations, the start and end date/time for each location may vary.
    • An event must have at least one host and at least one attendee. Otherwise, the event is canceled.
    • A user has a unique ID and a name (that consists of first name and last name).
    • Any user can opt-in to become a host. A host has an additional attribute called "bio." Hosts organize events.
    • Any user can register to attend any event. An event attendee has an additional attribute called "status." The status specifies whether the attendee is an undergraduate student, a graduate student, a faculty, a staff, or a non-UVA attendee. Assume the hosts do not register to attend the events they organize.
    • After each event, attendees are asked to provide feedback. Each feedback has a date, event ID, comment, and suggestion. Assume each attendee gives at most one feedback for each event attended; i.e., event ID can be used to access this attendee’s feedback for the event.
    1. (40 points) Create an E-R diagram for the database scenario. Make sure to include an indication of the cardinality of relationships and specify any mandatory relationship (total participation). State any assumptions that you make.
    2. (20 points) Convert the E-R diagram to tables, using schema statements

    Be sure to write down any assumptions you may have made.

Late submission

To grade and record your assignment efficiently, the following details apply to all homework assignments, unless otherwise specified.

Submission

Include all team member names and computingIDs in the report.

Embed all diagrams in a single report. Save your report as a PDF.  No Word document. No handwriting. No hand-drawing.

Each team submits only one copy.

Upload your report as a PDF to the Assignment 1 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.

Making your submission available to the course staff is your responsibility; if we cannot access or open your file, we have to assign a zero grade. Be sure to test access to your file before the due date.


Copyright © 2024 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-02-04 10:28