Fall 2025 — Assignment 1

Due 22-September-2025, 1pm 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.

For grading purposes, you are required to use the ER notations introduced in this course, Fall 2025 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) 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 a database about students, projects, and the courses they take:

    E-R diagram describing students, projects, and the courses they take (for hw1)

    Here is the information that you have gathered.
    • Some courses have group projects.
    • Some courses have individual projects.
    • Some courses do not have a project.
    • Each instance of a project is part of exactly one course.
    • No project is shared between courses.
    • Multiple courses can have projects with the same names. For example, a database course has a project named "web project" and a capstone course has a project named "web project."
    • Students cannot reuse the same project across multiple courses.
    • Students cannot reuse other students' projects.
    • The course instructor designs a project but no student has completed it yet (e.g., at the start of the semester).
    • Once a student completes a project, a project grade is assigned.
    • After a student take a course, a student receive a course grade.

    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 self-paced practice platform where educators create practice problems and practice sets, and learners solve the practice sets to help them understand and reinforce some course materials/concepts. Here is the information that you have gathered.
    • A user has a unique ID, a name, an email address, and a password.
    • Some users are educators. Some users are learners.
    • An educator has an additional attribute called "area of expertise." An educator is required to specify at least one area of expertise. Some educators have one area of expertise. Some educators have multiple areas of expertise.
    • A learner has an additional attribute called "status." The status specifies whether the learner is an undergraduate student, a graduate student, a faculty, or a staff.
    • Educators create practice problems and later use the problems to develop practice sets.
    • A practice set has a unique ID, a title, and an associated topic (i.e., the concept students will practice). Each practice set consists of at least 1 practice problem. A practice set can have many practice problems.
    • A practice set is developed by at least one educator. Multiple educators can contribute and work on the same practice set.
    • A practice problem has a unique ID, a type (which identifies the kinds of problem; for example, multiple choices, true or false, essay, or coding), a question, and a solution.
    • A practice problem is developed by at least one educator. Multiple educators can contribute and work on the same practice problem.
    • A practice problem may appear in multiple practice sets.

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

    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. Your assumptions must not conflict with the given database scenario.

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 © 2025 Upsorn Praphamontripong

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

Last updated 2025-08-31 21:23