CSC 1035: Databases for Beginners
Spring, 2020

Descriptions of Project Topics and Initial Schemas

We have six teams working on six different topics:

You can click here to see what your group's topic is. Here's how the part below is organized: If you have questions about any of the semantics, ask Yamini and me. We will be playing the role of the client who's hired your team to construct a database.

VUflix - Basic Version

This business (loosely modeled on Netflix (that's the old, non-streaming Netflix: the one that sent physical DVD's to its members upon request (each envelope contains one DVD: no two-disc sets)). Each DVD has an identification number, a title, a year and a director. Each member has an email address, name, date joined and balance on her/his account. Each mailing involves a member, a DVD, the date sent, the date returned, and the rating (one through five stars, given by the borrower when s/he returns the DVD). A borrower can re-borrow the same DVD at a later date (and assign a new rating). If we implemented this setup as a list, the columns might be the following: In Phase 03 of the project, you'll be discussing the problems with that arrangement.

VUflix - Extended Version

In this version, the one you'll be going forward with after phase 03 has been done, we classify each DVD as being of a particular genre. Each genre has an id and a description (eg, "slacker movie" or "soap opera" or "adult" or ...). A movie may be classifiable as more than one genre. And, of course, there may be many movies of any given genre.

VUgrants - Basic Version

This topic relates to several expensive pieces of equipment at Villanova and the many faculty members who use them, with their usage billed to research grants. Each grant has a unique PI (principal investigator), although some PI's may have multiple grants. Note that, similarly, the same piece of equipment may be reused by the same grant on several different occasions, and by different grants as well. We track the usage by a list with the following 11 columns in each row: In Phase 03 of the project, you'll be discussing the problems with this arrangement.

VUgrants - Extended Version

Each grant may have other associates working on it, besides the PI. We need their names, email addresses (they're identified by that email address), and the date they began working on that grant. Like the PI's, they may be associated with multiple grants. This is the database you'll be continuing with after Phase 03.

VUgreen - Basic Version

The VUgreen enterprise was founded by a group of Villanova students; it involves properties in the surrounding commmunities and gardening services provided to them by these students. Each property has a unique ID. It also has a size, street address and ZIP code. We keep track of which services (they have unique descriptions) were performed on which properties, the general charge for such a service, the ID of the work order containing that service (and possibly others), the date the work was ordered, and the date it was scheduled for. A particular service (like cleaning the chimney) can be repeated for any property, of course. Again, it's possible for more than one service to be written up on the same work order (but it means they've been scheduled for the same day, like mowing the lawn and cleaning the chimney). We could try to implement this setup with a list that has nine columns: In Phase 03 of the project, you'll be discussing the problems with this arrangement.

VUgreen - Extended Version

We also keep track of each payment made: the workOrderId it applies to, the date it was received and the new balance for this job.

VUlib - Basic Version

This enterprise runs a lending library for the Villanova community. We keep track of the books we have, of our subscribers and of loans of those books to our subscribers. A given book copy can only go out once on any given day. We distinguish between books and copies of them. For example, book 2421 might be Mark Twain's Tom Sawyer, and we might have three copies of it (copy #1, copy #2 and copy #3).

We might try keeping track of this in a single table with ten columns:

In Phase 03 of the project, you'll be discussing the problems with this arrangement.

VUlib - Extended Version

This escalation has author information as well. Each author is identified by an ID. We also note author names and years of birth. Don't forget: a book could have multiple authors.

VUmart - Basic Version

This enterprise sells items, each of which has an ID, a description (like "NovaNation T-shirt, Medium"), a department (the one that carries this item) and a price. There are also salespersons, each with an ID, a name and a startdate when they started working at VUmart. Now a given item may appear on several different purchases, and a purchase may include several different items. Each purchase has a purchase ID, and for each purchase we keep track of the time and date, the salesperson given credit for it, each item on that purchase, and the quantity of that item.

One way to track this information would be to use a list with ten columns in each row, as below:

In Phase 03 of the project, you'll be discussing the problems with that arrangement.

VUmart - Extended Version

Here's the version you'll be using once Phase 03 has been completed. Our mart has departments, each of which is identified by its deptID. We also note the name of the department and the number of employees it has. So for each item we record which department carries it (in our mart, only one department will carry any given item).

VUmed - Basic Version

This topic is concerned with administering a health-care system. It tracks member doctors and subscribers, along with their appointments.

We could track this information with a list with nine columns in this way:

In Phase 03 of the project, you'll be discussing the problems with that arrangement.

VUmed - Extended Version

This variation adds the capability to track conditions and when they are diagnosed. Each condition has a particular condition code that identifies it, as well as a description (like "sinus infection"). Keeping in mind that a given patient might be diagnosed with the same condition by various doctors, we also note, for any subscriber, when a particular physician may have diagnosed her/him with a particular condition. Finally, we note the "seriousness" rating the physician assigned at that time to our subscriber for their condition (1 would be the least serious; 10 would be most).

Back to CSC 1035 project page