Homework on Conceptual Modeling and ER Diagrams¶

This assignment is about ER diagrams and their connection to DDL to create tables.

ER Diagrams¶

There are three problems. In every case, I want you to figure out the entities and relationships and code it using dbdiagram.io. Think carefully about relationships: are they one-to-one, one-to-many (and which way) or many-to-many. Are their attributes on the relationships?

Time¶

Historically, this takes almost everyone less than 7 hours to complete. If it's taken you that long and you're not done, stop and get some help.

However, this assignment is entirely new, so I expect to make some mistakes in phrasing the problems. If you're confused by something, feel free to ask.

General Notes¶

• Put the authors names in each database diagram as a comment.
• Use commenting judiciously to explain your tables

Library¶

Name the diagram library, all lower case.

Use the ER approach to model the operations of your local community library.

• The library has two kinds of items available for checkout: books and DVDs (of movies), which are lent to library patrons.
• Patrons have accounts, addresses, and so forth.
• Some patrons are minors, so they must have a sponsoring patron who is responsible for them (paying overdue penalties or replacing an item in case of a loss). The sponsor is typically a parent.
• The library needs to keep historical records, so it can tell Patron Patty that the reason Patty is being charged 75 cents is because they borrowed The Joy of Cooking on Oct 1st and didn't return it until Oct 28, five days past its due date of Oct 23.

Freecycle¶

Name the diagram freecycle, all lower case.

Design an ER diagram for the database behind something similar to the Freecycle.org website.

• Each user signs up with an email address, their name, and their zip code.
• The zip code is used to show the user items "in their area" and similarly to determine the audience for their own postings.
• A user can post items that they want to give away. They describe the items with some kind of textual description: "pots and pans", "collection of children's books", "set of Pokemon cards" or whatever.
• Each post can consist of one item or multiple items. The three examples above could be in separate posts or in one big post.
• Other users can respond to postings, indicating interest or asking questions. ("do the pots and pans work on induction stoves"?)
• The poster can reply to the responses ("yes they are stainless steel"), and the two users can, in general, converse via the app.
• Users can also post "wanted" or "seeking" notices, such as "I'm looking for a children's size 12 party dress".
• Other users can respond to a "wanted" post, indicating that they have such an item.

Wedding Planner¶

Name the diagram wedding, all lower case.

Design a database for a wedding planner.

• Clients have names, phone numbers and a wedding date
• Clients also have demands, which are just described in text.
• The database should keep track of whether each demand has been met, like a checklist
• Clients also have wedding guests, each of whom has a name
• The planner needs to keep track of whether the guest has been sent an invitation and whether they have responded. We'll ignore the "+1" issue.
• Seating guests is tricky. The wedding planner needs to keep track of seating constraints:
  • which people should be kept apart (Aunt Anne and Uncle Ulysses need to be at different tables because of the ugly divorce),
  • which should be put together (Cousins Clyde and Cassie haven't seen each other for years)
  • which people it doesn't matter whether they sit together or not

Pair Work¶

The ER diagrams is a pair-work assignment. (You may work solo if you have a strong reason — check with me.) I will assign partners for this assignment; shared in a Google Sheet.

Where to Work¶

At least one person should create a dbdiagram.io account and create the solution there. In the free accounts, these diagrams are world-readable, so please only give the URLs to me and to each other.

Just in case of disaster, export your solution as MySQL code and put them in an er folder in your ~/cs304/assignments folder. Tar up and drop that folder. You can also drop that folder to your partner, so that both of you have the solution.

Getting Help¶

These are design problems, and there's more than one correct solution. So far in the course, I've often given you a way to see if your solution, or at least the output, is correct. Not here. First of all because it's technologically infeasible, and second because there is more than one correct answer.

Therefore, it's natural that you may be uncertain whether your solution is correct. What should you do about that? Here are some suggestions:

• work through particular examples, with realistic data. For example, in the wedding planner, you might imagine a wedding where Harry wants to sit with Ron and doesn't want to sit with Malfoy, etc. Can your solution represent this particular situation?
• Explain the diagram in English to your partner. A good use for partnership!
• State any assumptions you've made. Are they reasonable/plausible?
• Is there data that your representation can't handle?

What I'd rather you not do is come to me (or the tutors) and ask "is this correct?" I'd really rather not "pre-grade" your work. I'm happy to answer questions about the problem or questions about ER diagrams or about using dbdiagram.io. But come to me and the tutors with things that you are confused about.

Turning this in¶

To enhance consistency of grading, I'm going to use Gradescope on this. So, I'll want the usual "dummy" Gradescope item, as we've done in the past.

Since this is a pair assignment, please use the group feature of gradescope: one submission for the group, but the person submitting adds their partner to the submission. See gradescope (below)

In addition, I'll want links to your three diagrams, so you'll need to give me three URLs. Gradescope isn't good at that, so I'm providing a Google Form that we will use this semester. Please put them into the Google Form in the order they are in the assignment, so that when I view the responses, column1 is all the library solutions, column2 is the Freecycle solutions, and column3 is the wedding solutions.

This form is also new, so let me know if anything goes wrong or needs improvement.

submit URL

Gradescope¶

To save paper and to allow online grading, I want you to use Gradescope. Gradescope requires a PDF to be uploaded, so you'll have to generate such a PDF. Here's one way to do it:

• Create an empty Google Doc.
• Write in it a sentence like: Ron Weasley and I worked together on this; dropped from his account: fweasley. If you worked alone, just say that.
• It's helpful to remind me of the account name; I don't always remember the account that goes with your name.
• I can then grade the file dropped by Ron, giving credit to both.
• Use the File menu to download the page as a PDF (or print it and save as PDF).
• Upload the PDF to Gradescope.
• Please remember to enter your partner's name. See below:

Gradescope allows group submissions! If you worked with a partner, one of you should upload the Gradescope submission for both of you. Once you the submission is made, there is an option in the upper right to add group members.

Please be sure to do that.

Helpful screenshots:

This video shows how to submit your assignment. (It also includes info for instructor. The relevant part is from about 0:30 to 1:45.)

Final Checklist¶

• 3 links submitted to Google Form
• SQL code downloaded to three files
• tarfile of er.tar dropped to course account

Time and Work

The following link has been updated for Fall 2025.

Finally, when you have completed the assignment, make sure you fill out the Time and Work Fall 2025 That report is required.