CS4750 - Database Systems

Fall 2026 — Assignment 1

Due 16-September-2026, 10am (before class)

Purpose:

Reinforce knowledge and concept in database design
Develop an entity-relationship (E-R) diagram for small systems
Convert E-R diagrams into tables
Get ready to work on a project and prepare for exam 1

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

You are required to create visual representations of your E-R diagrams. Submit visual representations of your E-R diagrams. Do not submit a text describing your E-R diagrams.

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]

[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 university has at least and at most one president.
  A president is appointed to at most one university.
2. (3 points) A project can have several (possibly 0) engineers.
  An engineer can be involved in several (possible 0) projects.
[34 points] Imagine you are hired to improve a database design for a university's course management system. 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.
- As soon as instructors are hired by the university, they are assigned to at least one department. Some instructors work in one department, while some work in multiple departments.
- 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, Fall 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. Some TAs assist in one course, while some assist in multiple courses.
You may assume that we are not dealing with hiring, employment, human resources, etc.
The current E-R diagram (shown below) contains several design flaws, including missing and poorly represented information.
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
  - (-24 points) for not submitting a visual representation of your E-R diagram, submitting a text describing your E-R diagram instead of a visual representation, or not using the E-R notation introduce in this course.
Be sure to write down any assumptions you may have made. Your assumptions must not conflict with the given database scenario.
[60 points] Imagine you are hired to design a Conference Management platform to help the DB committees manage conferences. Here is the information that you have gathered.
- A conference has a unique ID, a title, and a location for the session.
- Some conferences are short (one or more hours). Some conferences run the entire day.
- Some conferences are one day, while some conferences last multiple (consecutive) days. The system needs to track when the conference starts and ends (date/time).
- A conference must have at least one host, at least one presenter (who is usually an author of a paper being presented at the conference), and at least one attendee. Otherwise, the conference is canceled.
- A user has a unique ID and a name (that consists of a first name and a last name).
- Any user can opt in to become a host. A host has an additional attribute called "bio." Hosts organize conferences.
- Any user who is an author of a paper being presented at a conference must register as a presenter. A presenter has an additional attribute called "paperTitle," which identifies the title of the paper being presented. A presenter presents the paper at a conference. For simplicity, you may assume that each paper can be presented in at most one conference.
- Any user can register to attend any conference. A conference attendee has an additional attribute called "status." The status specifies whether the attendee is an undergraduate student, a graduate student, a faculty member, a staff member, or a non-UVA attendee. Assume the hosts do not register to attend the conferences they organize.
- After each conference, attendees are asked to provide feedback. Each feedback has a date, conference ID, comment, and suggestion. Assume each attendee gives at most one feedback for each conference attended; i.e., the conference ID can be used to access this attendee’s feedback for the conference.
Make sure that your E-R diagram properly includes and represents all the above information.

Note that this question is open-ended. There are many decisions you have to make; for example, information needs to be maintained, the 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. Feel free to add any other information you deem appropriate.

Your assumptions and the added information 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.
  - (-40 points) for not submitting a visual representation of your E-R diagram, submitting a text describing your E-R diagram instead of a visual representation, or not using the E-R notation introduced in this course.
2. (20 points) Convert the E-R diagram to tables, using schema statements
  You are required to submit schema statements. Do not submit SQL code to create tables.
  
  For example, your submission must look like this (this is a schema statement):
  Student (computing_id, name)
  
  Do not submit something that looks like this (This is SQL code):
  CREATE TABLE Student ( computing_id VARCHAR(10) PRIMARY KEY, name VARCHAR(100) );

Late submission

(-25 points) for 24 hours late (submitted after 16-Sep 10am, by 17-Sep 10am)
(-50 points) for 48 hours late (submitted after 17-Sep 10am, by 18-Sep 10am)

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

(-10 points) — For submitting a report in a Word document, handwriting, or hand drawing

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.

CC-BY-NC-SA 4.0 license.
Last updated 2026-08-29 9:34