CSC 1035: Databases for Many Majors

Spring, 2020

Team Project: Design and Implementation of a Database


Here are descriptions of the different project topics.
You'll need to refer to these specs in Phases 03 and 04 below (and maybe some other phases as well).


This is the group project, as opposed to the individual one, which is described elsewhere. It's divided into 15 milestones, or phases, each to be submitted by the date indicated in the chart below. Each phase is worth four points towards the project grade, except for Phase 01, which is worth three, and Phases 07 and 15, each of which is worth six. Those three phases are done individually, although this is still the group project, not the individual one.

Other than those three phases, my starting assumption will be that the grade on any phase will go to all members of the team. However, I'll be adjusting this at the end according to my perception and that of the teams themselves. Part of the grade will be determined by your personal familiarity with your team's project, as determined by a written or oral assessment.

So how are the group phases (ie, all phases other than g01, g07 and g15) of the group project to be submitted? This will be by upload to Blackboard. If you have problems with the technology, please contact the TA or me for help with it. Even though each phase only has one submitter (but not all phases need to have the same one), all group members will be able to see that it's been done, and done on time.

Here's a summary of the different phases (the dates may be adjusted later, so watch for modifications); the prefix "g" is to remind us that the phase is part of the group project:

# Task Due date Points worth
g01 Get acquainted
(Individual work)
Jan 30 3
g02 Accessing Access Feb 4 4
g03 Schema, stage 1 Feb 11 4
g04 Schema, stage 2 Feb 24 4
g05 Schema in MS-Access Feb 28 4
g06 CREATE tables in SQL Mar 10 4
g07 Mid writeups
(Individual work)
Mar 17 6
g08 Populate tables in MS-Access Mar 24 4
g09 QBE Queries Mar 31 4
g10 SQL Queries Apr 2 4
g11 Reverse engineering Apr 16 4
g12 Forms Apr 21 4
g13 Reports Apr 21 4
g14 Presentations (instead of final exam, during that slot) May 4-5 4
g15 Final writeups
(Individual work)
May 7 6
T TOTAL
63



Phase 01: Get acquainted with your group

Look at the groups list. Some time before the assignment is due, contact the person in your group whose name appears after yours (or, if your name is the last one, then go to the top). In class on the due date, I'll ask everyone to tell us one fact (e.g. - "is from Washington, DC," or "is an English major") that you've learned about the person whose name you drew. So you'll probably be speaking to two teammates: one who's asking you for your information, and one whom you've asked for theirs.



Phase 02: Find MS-Access:

Where will you be doing your development in MS-Access? Let me know where each individual has access to Access, and which will probably be the "master" copy of your group's database.



Phase 03: Schema, Basic Version

This page, as mentioned earlier, describes the project topics. Here's how it's organized:

Take the basic version's suggestion that applies to your team's topic, and explain why the spreadsheet strategy is problematic. For example, describe a potential update problem that might arise. Next, describe a better strategy, one that involves splitting the original list into two or more smaller tables. For each of those tables, give the table a name, give the column names, and tell why the problem you raised earlier wouldn't occur now. Remember: don't put any rows into the tables, though.

Phase 04: Schema, Extended Version

This phase is still just "on paper." Start with the solution of the last phase. Tweak it a bit in this phase so that the extended version of your topic can be implemented. What you need to deliver here is a list of the tables with their attributes, with the primary keys underlined and with foreign keys indicated by arrows or by a narrative. Again, this is still just at the design (or schema, or metadata) phase, so don't populate any tables.



Phase 05: Implementation of your database in MS-Access

Once the TA or I have approved your phase 04, create an Access database, and create those tables. Implement the primary and foreign key constraints. And still: don't put any rows into the tables. Upload the accdb file (the database) in Blackboard. file.



Phase 06: Creation of tables in SQL

This phase is a paper one only (don't run it in your Access database - it already has the tables). Give the SQL code for creating the same tables as the last phase, with the same properties.



Phase 07: Midterm evaluation/feedback

I'll email you a set of questions to answer regarding your feelings about the projects and course. The grade won't be based on the length of your replies nor on the sentiment (positive or negative), but on how thoughtful, complete and specific they are.



Phase 08: Population of tables in MS-Access

Make up credible (but not necessarily real) data for your tables, and populate your tables with it. Your total database should have at least 60 rows. Make them sort of interesting. In particular, if something is a foreign (but not, on its own, primary) key, some values in that column should appear more than once.



Phase 09: Queries in Access

Make up five queries. They shouldn't be killers, but they shouldn't be trivial, either. Part of the grade on this one will be the "challenge factor." Submit English formulations (in MS-Word or another word processor), and implement them in Access' QBE view as Queries objects. The queries should have names, not just "Query1," etc. Upload the database file as well as the formulations. In fact, note that Phases 10, 12 and 13 also require two submissions each: a narrative as well as their implementation in your database.



Phase 10: Queries in SQL

Take your queries from the last part, and formulate them in SQL. Do them from scratch (don't just switch from the native QBE Design View to "SQL View" - the versions get much too complicated). Let them keep their names, but add "SQL" to them. For example, if the query had been called GoodJobs in phase 09, then call it GoodJobsSQL in this part. Submit the accdb file. Unless previous phases are seriously flawed, the Access files you upload from now on should be cumulative (i.e., include all the previous phases on them, with corrections if applicable). In other words, when you submit your database with the SQL queries in it, it should still have the QBE ones there as well! It's optional to re-submit the formulations, unless you need to correct them from Phase 09.



Phase 11: Reverse engineering

As we've discussed in class, the design stage of database development generally precedes the implementation stage. However, sometimes we can start with a relational database schema (a set of tables) and go "backwards" to recover an ER diagram that would produce it. See if that would happen with your MS-Access schema. In other words, hand in a very legible (there are computer tools that can do this, like MS-Visio; but you don't need to use them) ERD for your database. The submission should also include a narrative that tells us whether that ERD would produce exactly your schema or not, if you were to convert it from ERD to RDM using our conversion algorithm.



Phase 12: Forms

Create two forms. Provide a narrative explanation of them (MS-Word would be good). Part of the grade on this phase will be for aesthetics and creativity. Submit the database as well as the narrative.



Phase 13: Reports

This is the same as the preceding phase, but this time make it two reports. Again, your forms should also still be there. And again, part of your grade will be awarded on the basis of your creativity using design tools. Submit the formulations as well as the database.



Phase 14: Presentations

Prepare a presentation to the class. Figure on 15 minutes per four-person group, 12 minutes per trio. It's important to finish no later (a little earlier is fine) than your allotted time, so if everybody is presenting a portion, make sure the later ones aren't squeezed for time (it doesn't hurt to practice).

Remember, this is happening during the final exam slot for the course, so the presentation time will be 11:30-2:00 PM on Tuesday, May 5.

Since we're meeting by Zoom and not in G90, your presentation should be a PowerPoint file uploaded on Blackboard no later than Monday, May 4. Here's how it will work: I will have your presentation file on my PC and will share it when your group is up. You'll cue me when to advance to the next slide. The group grade on this phase will take into account such factors as how successful you are in engaging your classmates, the presentation materials (there should obviously be a cover slide, with team name and members' names, and a second slide with the outline of your presentation (it may be stepping through the phases, but not necessarily so)), and your success in relating your project to principles discussed in class. Factors such as punctuality and attentiveness to other groups' presentations, of course, will count towards your class participation grade. We'll go in order of the group listings on this page.



Phase 15: Final evaluation/feedback

Like Phase 07, I'll email you a set of questions to answer regarding your feelings about this project. The questions will also be similar to the midterm writeups, and, again, the grade won't be based on the length of your replies nor on the sentiment (positive or negative), but on how thoughtful they are. Unfortunately, because of deadlines this time of year, I won't be able to accept any submissions much beyond the deadline. In order to encourage prompt submissions, I'll add an extra point to ones that come in on May 6. Let me know if you have any questions.


Last updated Apr 29, 2020


Back to CSC 1035 main page