Spring 2026 — Assignment 1

Due 6-February-2026, 1pm EST (before class)
Purpose:

For this assignment you may work individually or with at most 3 partners (max group size = 4). 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.

For grading purposes, you are required to use the ER notations introduced in this course, Spring 2026 CS 4750.

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) Every magazine must have at least one article.
      An article can be printed in (possibly 0) at most one magazine.

      partial E-R diagram for cardinality question #1

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

      partial E-R diagram for cardinality question #2


  2. [34 points] The following E/R diagram describes a database scenario for an Advising Website:

    E-R diagram describing advising session (for hw1)


    The Advising Center at a University would like an "Advising Website" to be created so they can better manage the student requests for advising appointments. The Advising Center would like to assign appropriate academic or industry advisors to students based on their expertise.

    • Every advisor has an area (or areas) of expertise, that is, they are knowledgeable in providing career advice for particular career areas (e.g. Arts, Education, Engineering, Healthcare, Marketing, etc...)
    • Students come to the Advising Center to get advice in a particular career area. Students who log into the system will have a profile with their basic information – first name, last name, computingID, phone number, email, and a career area which they select from a list of career areas.
    • Both academic and industry advisors have their basic information – first name, last name, advisorID, office location, phone, email, and area(s) of expertise (area of expertise taken from the same list of career areas the students have).
    • Let's assume each student is interested in only one career area (e.g. Engineering) whereas advisors can have more than one career area listed as their areas of expertise.
    • Students can request an advising session and, based on their career area, an appropriate advisor will be assigned.
    • An advising session will be scheduled between the student and an advisor at a specific date and time, in a particular location (either Rice 103, Rice 204, or Rice 304), and for a specific career area (for example, this advising session is to discuss the Engineering career area). At the end of the advising session, the student has the opportunity to rate their experience with a simple numeric rating (e.g. 1 to 10) and a free-form text box.

    At various times, the Advising Center would want to gather information about the number of advising sessions that took place per career area, the total number of unique students who requested advising sessions (students can request multiple advising sessions during an academic year), the average rating given, or the advisor who was rated the highest, etc.

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

    1. (10 points) There are many possible ways to improve this E-R diagram. 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. Your assumptions must not conflict with the given database scenario.


  3. [60 points] Imagine you are hired to design a database for a university registrar.

    For simplicity, let's focus on information about (1) students, (2) departments, (3) instructors, (4) courses, (5) which students enrolled in which courses and what grades students received, (6) which instructors are teaching which courses, (7) TA's for a course, (8) which courses a department offers, (9) and any other information you deem appropriate.

    You may assume that we are not dealing with hiring, employment, human resources, etc.

    Here is some basic information that you have gathered.
    • An instructor has a unique ID, a name, an email address, and a password.
    • Some instructors have one area of expertise, while others have multiple areas of expertise.
    • An instructor only works in at most one department.
    • A student has a unique ID, a name, an email address, and a password.
    • A course is uniquely identified by the course number, section number, year, and semester (e.g., CS 4750, section 1, Spring 2026).
    • A course is offered by at most one department.
    • A course can be taught by one or more instructors.
    • Some courses are offered every semester, while some courses are not.
    • Some courses do not have any TAs while some courses have one or more TAs.
    • TAs are students.

    Make sure that your E-R diagram properly includes and represents all the above information.

    Feel free to add any other information you deem appropriate.


    Note that this question is open-ended. There are many decisions you have to make; for example, information needed to be maintained, cardinality of relationships, constraints on whether the relationship is mandatory, etc.

    The database design requires real-world facts, prior knowledge/experience, perceptions, and divergent thinking. The decisions you make may differ from those of other students and the graders. As a result, your E-R diagram will most likely be different from others. Be sure to include any assumptions you may have made.

    Your assumptions must not conflict with the given database scenario.

    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
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 © 2026 Upsorn Praphamontripong
Released under the Creative Commons License CC-BY-NC-SA 4.0 license.
Last updated 2026-01-27 17:23