The project is divided into 17 milestones, or phases, numbered 00 to 16, to be submitted by the dates indicated. Phases 00, 08 and 16 are to be done individually.

Each submission (other than 00, of course) must have the team name, the date and the phase on it. Also, it must be submitted via Blackboard before midnight (part of the grade depends on punctuality) on the due date. One exception is Phase 14, which is due by 5:00 PM on that day. Please also do the following:

• Have just one submitter for any given phase.
• Include the phase number and team name in the file name, if you're sending the work as an attachment. Best is if you name the file 02ABC.docx, for example, if your team's abbreviated nickname is ABC, this is Phase 02, and it's an MS-Word file. This is in addition to having that information as a header on the file itself.

It's probably a good idea to keep a log of who has done what on which phases; it will come in handy for phases 08 and 16.

Finally, I expect everything to be typed/word processed. The only exception would be ER, EER, and UML class diagrams (and even in those files, the portions that are text should be word-processed). There are software products (Visio and various freeware as well) for creating them, and even MS-Word and MS-PowerPoint work OK; but it's also OK if you produce them by hand, if they're extremely neat and legible, and the text portions at least are typed.

Phase	Title	Due Date	Points worth
00	status report	Sep 01	3
01	teams, topics, software, roles	Sep 04	8
02	requirements	Sep 11	5
03	ERD	Sep 18	13
04	relational database schema	Sep 25	5
05	schema implementation	Oct 02	6
06	populating the database	Oct 23	9
07	midterm report of team meetings	Oct 23	5
08	midterm report of individuals	Oct 30	9
09	other conceptual schemas	Oct 30	14
10	query implementations	Nov 06	12
11	database programming	Nov 13	15
12	FD's and NF's	Nov 20	12
13	ODM	Dec 04	10
14	presentation files	Dec 18	12
15	final report of group meetings	Dec 18	5
16	final report by individuals	Dec 19	7
TOTAL			150

We need to form the project teams rather quickly, so that the first phases can be completed on time. Here is the current lineup of groups and topics.

Let's figure that each group will consist of three or four members. Your job on this phase is to send me the list of your group members, by September 1, even thought this is not a class day. (Of course if you prefer not to be doing this on a week end, you may submit the phase earlier.) The list can have four names, in which case it'll be pretty permanent; or three, in which case I may add another member to it; or two, in which case I'll add another one or two; or one (just you - in other words, you haven't been able to hook up with a group), in which case I'll assign you (probably randomly) to a group.

A couple of notes:

• Everybody must submit this phase, even if your name already appears on someone else's list. There'll be penalties if your list of names is inconsistent with someone else's on your group!
• Make sure all names on your list are spelled correctly.

Here are some sources for topics:

a) R. Sunderraman, Oracle10g Programming: A Primer, 2008, describes many different projects. Other editions (for other releases of Oracle) are pretty similar in their project descriptions.

b)  The database course at Humboldt State University has many good ideas. They might be listed under the heading "Edited and approved project scenarios." Don't lift one verbatim, but they might inspire some ideas.

c) Think up one of your own.

Give your topic and its source (a particular reference, or a real-world situation you're basing your enterprise on, or just fiction). Also, choose a team name. A few recent ones have been SQL Rights, Hotel SQLifornia and The Seg Faults. There's no need to submit a description of your enterprise yet (see the next phase).

Also due with this phase: the role of each group member. Include this information:

• Who is the group leader?
• Who is responsible for recording what goes on at meetings? This person will be the one who furnishes me with information on time, place, attendance, and decisions made (see phases 07 and 15).
• Do you have access to other (e.g., personal copies) of database software besides the ones in the Villanova labs, Oracle on csdb2.csc.villanova.edu and MySQL on csdb2.csc.villanova.edu? In phase 05 you'll be asked to report on which product you'll be using for the implementation phases of your project.

Phase 02) Requirements

Describe the ingredients of the enterprise. The scope should be somewhere in the middle: not a killer situation (many inital ideas are too complex for a term project), but at least as complex as the "COMPANY" example in our text. The resulting ER (see the next phase) should have relationships that represent varied cardinality and participation constraints.

Remember, appropriate requirements at this stage will help minimize later changes. 

Phase 03) ER model

The deliverable here is the diagram (in standard, or "Chen," notation). Indicate keys and classify relationships in the usual ways. Include, too, some narrative describing any additional assumptions you've made and any information which the ER diagram may not be rich enough to convey. Like the previous phase, the more care you take here, the fewer revisions necessary later. And again, the ER should have relationships that represent varied cardinalities and participation constraints.

Finally, we're requesting that you send the ERD as a doc or docx or pdf or jpg or png file. If you use a different drawing tool to create it, you should be able to save it in one of the formats above.

Phase 04) RDB schema diagram

Convert your ER model algorithmically to a relational database schema. Submit this schema (with key and referential integrity constraints indicated in the usual way). Note: this isn't actually to be implemented - it's just on "paper."

Phase 05) Schema implementation

Oracle (and most likely MySQL as well) is available for you on the departmental UNIX cluster. Both of those DBMS's are also available from the vendors for download to your laptop or PC. Get well enough acquainted with at least one of them so that you can choose one as the DBMS for implementing your team project. See the links near the bottom of this web page as a start. Oracle has better departmental support (the TA should be able to field your questions), but if you're sure you prefer to take your chances with MySQL, I'll permit it. Report on your choice and reasons, and explain how all team members will have access to this common database. If you're in Oracle on csdb2, for example, you shouldn't be using the common "SCOTT" account. The tables should be in one of the team member's accounts, with the appropriate GRANT commands allowing the teammates to access them, as well as the TA (username DBTA) and me (GOELMAN, for Oracle). Set up your database schema on this software, submit the SQL scripts and screen shots to illustrate the schema implementation, and explain where and how referential integrity constraints have been incorporated.

By this point, you should have been successful in logging into your UNIX account, and your Oracle one as well. It's not necessary to send any email message to show me that you've made it to csdb2, but I will check the system log. If it shows that you have indeed logged into csdb2, this will account for two of the nine points on phase 08, since it's an "individual" submission (but this part is due now).

Phase 06) Database instance

Populate your database.   The instance should be nontrivial (it too should be at least the order of magnitude of the COMPANY database of our text).  Hand in

• a description of how you did the loading (e.g., did you use a loader utility?), plus
• copies of the contents of all your tables.

Phase 07) Midterm report of group meetings

On behalf of the group, the recorder submits her/his accounts of the group meetings up to this point. Include a description of how meetings took place (in person? Skype? other technology? a mix of technologies?), and who participated.

Phase 08) Midterm report of individuals

I'll distribute a survey asking each individual some questions about the experience within the group. It's meant to be confidential, so submit this phase by email, and only to me (no cc to the TA).

Phase 09) Other conceptual schemas

There are four parts to this phase:

• Extend your specs somewhat so that you have at least two subclass hierarchies. Write them up and indicate the changes to your original miniworld (Phase 02).
• Represent this new model by an EER diagram.
• Convert the diagram to a relational database schema, and provide SQL code for at least two checks or assertions. Don't do any implementation in your actual database here. You may also refer to your original schema for any ingredients that don't change.
• Represent it by UML as well. Provide a class diagram. You'll be going forward on this thread in Phase 13.

Phase 10) Query implementations

Phrase four queries, at least three of them fairly challenging (eg., some combination of grouping, aggregates, subqueries, negatives) logically, in English.  Give SQL translations of them, and indicate their implementation and solutions (screen shots would be appropriate).

Phase 11) Database programming

Incorporate some (but not all!) of these features: JDBC; front ends; PL/SQL; sqlplus formatting; forms; reports; triggers, and other helpful or spiffy features. There should be some interactivity between your program and a user. If someone on the team has Web programming experience, a GUI would be nice. The deliverables here are

• a roadmap (this is important!): a list or table describing each extra, the language or environment used, the names and roles of the relevant files, the listings or screen dumps to be submitted (Here's a good model from a previous term. Again, you don't need to include all these environments.)
• source listings and sample screen dumps.

Phase 12) FD's and normal forms

Consider your relational database schema diagram (Phase 04). For each table in your schema, list all the functional dependencies.  Identify all candidate keys and normal forms.  If the NF is less than 3 (this is rare, but it does happen occasionally), describe some potential anomalies and provide and discuss a decomposition that is LJD and FDP into 3NF or BCNF.

Phase 13) Object Data Model

Consider what the conversion of your UML class diagram from phase 10 into ODL would entail. If the result would be more than eight classes or so, then feel free to "crop" the diagram (but keep at least one of the specializations). Submit the original or cropped diagram, together with the appropriate ODL.

Phase 14) Presentations.

Click here for the details.

Phases 15 and 16) Final experience reports

These are the counterparts to phases 07 and 08. The recorder will submit minutes on behalf of the group of meetings that took place since the midterm. The individual surveys (which I'll be distributing, like the midterm ones), though, will reflect the whole semester experience.

Learning about Oracle and how to access it on csdb2.csc.villanova.edu) 

Accounts should be ready shortly. When the time comes, you can also find further information here on using the UNIX cluster and here on using Oracle there. Those two links, as well as the one in the next paragraph, may need to be updated for the current semester.

Learning about MySQL at Villanova)

These accounts will also be ready shortly. Some details can be found here.

Individuals and groups)

Remember, your group is depending on you. It's important that everyone contribute time and energy. Your grades on the individual phases (19 points from 00, 08 and 16) will be credited towards your course grade, but the total of the group phases (the remaining 131 points) may be "tweaked," depending on my perception (and your teammates') of your contribution.

Back to CSC8490 main page