CMPT 355

Class Project

NOTE: This is a draft of the project requirements that is subject to change up until class time on Nov 2.

Due dates:

There are 3 parts to this project:

Part 1 (a preliminary report on your project) is due on Monday, November 21 at 11:00 a.m.

NOTE: This part is being required early in order for Prof. Carter to identify major problems with your planning before your final parts are due. He will try to have feedback to you by Wednesday, Nov. 23.

Each team must e-mail their complete report as a single doc or docx file directly to Prof. Carter before the due datetime.

It involves producing reports for parts A - D below.
Each of these four components will be marked out of 10 marks.
Be sure that your report is easy to read.
Part 1 is worth 10% of your year's mark.

Part 2 (the final report on your project) is due Monday, December 5 at 8:00 a.m.

Part 2 involves revising your reports for parts A-D (which counts as one component of this part) and producing new reports for parts E - H below.

NOTES:

Be sure that the report is easy to read.
All parts of A-H must be handed in in a single doc or docx file.
Appendices of other materials should not be a part of this file.
Revisions to your reports for parts A-D should be indicated by using track changes on a copy of your original report. Serious attempts should be made to improve areas where problems were identified in Part 1 of your project, otherwise you will receive the same mark for this as the mark you received for Part 1.

Each of the following four components will be marked out of 10 marks

(revised A-D)
E
F
(G and H)

Be sure to explain what you did and why you did it clearly.
Your completed project report should be handed in at the time of your presentation in electronic format on a cd/dvd or USB stick (which will not be returned).
Your completed project report should include:

a set of reports for each of parts A, B, C, D, E, F, G, and H (in a single doc or docx document) as discussed below, and
a separate folder of documents containing copies of the Postgres scripts and other programming that you used in your project, and copies of each of your team members assignments #1, 2, and 3.

NOTE Moodle will not be used for the project.

Part 2 is worth 10% of your year's mark.

Part 3 (a Demonstration of your project), it is due Monday December 5 at 8:00 a.m.

The exact time you do your demonstration for Prof. Carter will be scheduled some time on December 5 or 6. However, since your system is due at 8:00 a.m. on December 5 there is no excuse for not having your system working.
Part 3 involves making a presentation on your new database structure and demonstrating the operations parts F - H below using dbvisualizer to Prof. Carter.

The demonstration will be led by Dr. Carter asking you to demonstrate that your system works by making various queries and updates to it.
Dr. Carter will select the queries and updates and the data to be used in your demonstration, based on his understanding of your presentation on your new database structure.

Each of these four components will be marked out of 10 marks.
Marking will emphasize:

a good easily understandable presentation of a suitable database design and a demonstration that this design has been successfully implemented. The emphasis will be on convincing the instructor that your database correctly does everything that it is supposed to do.

Project Teams will be able to make requests for particular presentation slots starting some time in the week before Dec. 7.
All members of project teams are expected to be present at their team's presentation.

Part 3 is worth 10% of your year's mark.

Topic: merge your database with other similar databases to produce a new, evolved database

Methodology:

You will be assigned to a project team of between 3 and 5 members.

Since your projects are built upon your Assignment#3 it is essential that all students have a suitably complete Assignment#3 as the starting point. The marker will inform you when you have achieved approval of a suitably complete Assignment #3. It is your individual responsibility to achieve a suitably complete Assignment #3 and receive marker approval as soon as possible.

Most students will have received the needed approval in their marking of Assignment#3.
Other students who either did not submit assignments on time or whose assignments failed to achieve marker approval, must submit information describing how they have fixed or completed their Assignment#3 to the marker by e-mail for approval that their assignment is suitable for use in the project. The marker will only give approval once the individual students have an Assignment #3 that meets minimum quality standards.
All students must have achieved approvals prior to November 16. Student who fail to do so may be removed from their project teams by the instructor and/or may have a mark of 0 assigned to them individually for their project. Students must participate successfully in a project team in order to achieve a project mark.

All team members are responsible for the success of all parts of the project (beyond the individual Assignment #3s). [see discussion of teamwork below]
All parts of your project need to work together on the same set of databases. Each team will be assigned a team database to use in this project. Individuals will still have their own databases also to work with this project.
Due to the complexity of the project, marking will focus on results of the success of steps of the project rather than on lines of code. (Students are however required to submit their code as part of their final project. Failure to submit this will result in a deduction of marks.) Thus, it is important to clearly explain the various development issues that are required below.
NOTE: The e-r diagrams from dbvisualizer are not suitable for any parts of this project. You will be expected to produce easy to read e-r diagrams that follow class e-r diagramming conventions, and that can easily be read without the use of any additional magnification.

The Premise

Congratulations, your museums are so successful that they have merged together under the sponsorship a wealthy and enlightened patron. Outside the museum world, he buys small businesses that have the potential to merge together to become large businesses, does the right things to make the transition for them, and then sells off very successful companies. He is hoping to use his skills in helping the museums become more successful via this merger. Remember that he is a senior business person who is not a computer person. Your reports to him need to focus on the information (rather than the data) that your database will support and what can be done with it to server the needs of the museums and their visitors.

A major part of the transition is developing a good information system that meets the needs of the various types of museums being merged together. Because the patron is both enlightened and wealthy he doesn't want you to cut any corners in developing the information systems. Rather he wants you to follow the following (numbered) steps (reporting, presentation, and demonstration requirements are contained in lettered bullets):

start of part 1

Your patron doesn't want to pick between systems (databases) that may work fine for one museum but poorly for others. He wants a new database that will do a good job in meeting the needs of all your museums. (The existing systems can continue to be used until the new system is ready.) This means that you should analyze the differences between your various databases and suggest a new database structure that will meet all of the needs currently being met and be able to contain all of the data currently contained in a single, easily usable structure.

(A) You need to analyze each of your current and design a new database structure. You need to present your findings and suggestions in a well written report that your patron can understand.

This report should be written to focus on the data and information that will meet the museum's needs and NOT on the underlying Postgres code.

e.g. focus on chunks of information that are relevant to the museum before trying to present how they can be combined in a database
e.g. use simplified e-r diagrams (without multiplicity, FK, and other technical notation) to provide an overview of the general architecture of your solution

a high level diagram showing only tables and their relationships provides a good overview
a series of lower level diagrams (with tables, attributes, and relationships) can be developed to explain different important parts of the overall database that each meet some particular information need.

NOTE: Many teams with good technical skills have difficulties in developing a report that is suitably written for the patron (and not for a database professor). It is essential that your report be easily understandable by a person who does not have database training. Failure to achieve this will result in a failing mark.
NOTE: This project requires a suitably completed version of each team member's Assignment #3. If any team members do not have suitably completed Assignment#3's they MUST properly revise them to the satisfaction of the CMPT 355 marker before starting part A, which is a prerequisite for all other parts. Failure to have your assignment #3 approved by the marker before Nov 16 will result in a failing project mark for an individual - regardless of the mark obtained by that individual's team.

Your patron wants to ensure that the new structure does a good job of containing temporal data (as was discussed in your CMPT 355 class). This might require you to evolve the database structure.

(B) You need to analyze the need for temporal data in new database structure (from part 1) and design a new improved structure.

Your analysis should report on

how consideration of possible temporal data could improve the structure from (A), by discussing the existence of and reasons for

(a) existing temporal features in the structure from (A), and

(b) new temporal features that should be added

an improved design (beyond that from (A)) that takes into account these additional needs for temporal data

Again, you need to present your findings and suggestions in a well written report that your patron can understand.

Remember it is important to focus on the data and information that will meet the museum's needs and not on the underlying Postgres code.
A set of revised e-r diagrams (highlighting improvements beyond (A)) can again can be helpful as the basis for your discussions this part

NOTE: You should also be prepared to make a 10 minute presentation (as part of your end of term demonstration in Part 3 of your project) to the instructor that explains your new database structure and what led you to design it in the way you did. This will require a well drawn e-r diagram or set of diagrams that illustrate your complete database structure

Your instructor wants to ensure that you have a good design for the new database structure.

Be sure to use domains properly for each conceptually different type of data
NOTE: The following parts are for your instructor and not for the patron. However, your instructor wants these parts to be easy for him to read and for him to identify the important information. Therefore explain everything in good English using clear formatting. The instructor does not want to see any Postgres code!

You need to clearly explain the database schema that you have developed for your team database.

a complete e-r diagram or set of e-r diagrams according to class e-r diagram standards (the diagram from dbvisualizer is not acceptable),
descriptions of the purposes of and constraints on each of the various database elements

domains,
tables, attributes

NOTE: just because this is for your instructor does not mean that readability and understandability is not important. What it does mean is that this section requires you to go into more detail to explain the design you have created for your team database.
NOTE: DO NOT refer from (C) to (A) or (B). (C) must be fully self contained in that it must present the instructor with all the information that he needs to evaluate your database design without having to look at your code or your database.
NOTE: you should clearly describe both the e-r diagram(s) and the various database elements. It is not sufficient just to name the elements and it is not acceptable to use Postgres code to "describe" them.
NOTE: This must not include the Postgres code. The Postgres code belongs only in the accompanying folder of additional information to be provided with the final report and not as the main contents of the report. Marks will be deducted it any Postgres / SQL code is included in your report

Each of the team members needs to be able to convert their data from its current structure into the new structure. Before actually converting the data, it is important to plan what needs to be done.

(D) Provide a set of reports (1 per individual) that clearly describes what you need to do to transform you data from your existing structure to the new structure within your database. These reports should explain how this will be accomplished for each table and how you will test to see that your new database is correct.

While you must make any transformations using Postgres and not via direct edits to the data files, your report for (D) should only focus on the changes that were made and should not include the Postgres code. The Postgres code belongs only in the accompanying folder of additional information to be provided with the final report and not as the main contents of the report.
NOTE: half of the marks for (D) will be for explaining how you will test that your changes worked and that your new database is correct.

NOTE: Parts A -D do not involve any SQL coding. Rather they focus on doing an excellent job of analysis and design. They do require well written reports that are suitable for their intended recipients.

NOTE: While the marker might have given you good marks for Assignments 1 - 3, the marker had a very limited time to evaluate your assignments. The instructor will be taking much longer to evaluate this report and will be expecting you to have done a good job of implementing the requirements and expectations of this class and of the assignments. This will likely include finding issues that might have been overlooked in the marking of assignments.

end of part 1 - start of part 2

Before starting to implement your design, you might need to improve on it, based on comments from the instructor on Part 1.

Once you have a good design in Part 1 of the project, you need to start working on implementing this design. Remember that the marks for Part 2 are for a well written report and that it should not include any Postgres / SQL code in it.

The starting point is for the team to develop a database schema to handle the needs of this new database. It needs to develop a complete set of Postgres instructions that can be used to create all the domains, tables, attributes, and relationships needed for this new database.
Individual students should then implement this database schema in their individual databased [all names used in this new schema in individual must be prefixed with "ns-" which will avoid any duplications of names with existing elements in your individual databases.]
Because of the limitations of Postgres, converting data to the new schema will be done within each team member's database. In part (E) you will need to add the schema developed in step (C) to each of your individual databases.
The team will then use insert schema (without the "ns-" prefixes) into the team's database to create the new database.

Each individual has to create the appropriate Postgres series of instructions to copy (and modify) all data from the old schema into the new one within their own database based on your plan from Part D. It needs to satisfy all of the temporal needs of the new schema as identified in step (C). Use these instructions in the individual databases so that each database has all of its data in the new schema.
Individual databases need to be queried to get all of the newly formatted data out of them and into text files. You then need to use these text files to insert all your data to the team database. Design and perform this processing.

(E) Provide a report that clearly describes what you have done. This report should explain how you have accomplished creating this unified database (including explanations of how any particularly tricky issues were handled).

The report should have multiple subsections:

one subsection for each team member copying and modifying the data in their own database and
one subsection for the team loading the data into the team database.

Each subsection of the report should address both

what changes were made (including any problems that were encountered in making these changes)

NOTE: it is not acceptable to make changes by exporting the date, making changes outside the database (such as in Excel) and then reimporting the data. All changes must be made using sql. However, like iwth other parts of your report you should explain what logically was done without getting into the sql used to do it.

and how the resulting database was evaluated to ensure that it was correct.

NOTE: half of the marks for (E) will be for explaining how you will test that your changes worked and that your new database is correct.

Your report should not include the Postgres / SQL code. The Postgres code belongs only in the accompanying folder of additional information to be provided with the final report and not as the main contents of the report.

Your patron wants your database to operate as smoothly as possible. This means you should minimize the complexity of updates, ensure the integrity of transactions, and provide a comprehensive set of reporting options. The reports in (F), (G), and (H) are intended for the instructor. However, it is important to ensure that they are easily readable by the instructor and that they focus on the important information that he needs to assess whether or not you have done a good job of analyzing what was needed and designing suitable routines to satisfy the needs. Again, they must not include any Postgres code.

Remember that you will need to be able to demonstrate any of the items in (F), (G), and (H) that the instructor asks you to demonstrate (using the data that he gives you) in Part 3 of the project. Be sure to find out in the tutorials how to set up your transactions and queries so that all you have to do is to call them (changing the appropriate attributes) using dbvisualizer when you are demonstrating your database.

(F) The use of triggers can help to minimize the complexity of transactions and to help ensure their integrity. You should identify a minimum of 3 (and preferably more) significantly different places where triggers can be helpful within your new project database. Create and implement the Postgres to add these triggers to your database. (NOTE: triggers should not be used prior to completing all steps A-E). Be sure to test their operation but to back out any testing related data changes. These triggers will need to be demonstrated to your instructor, using data that he decides upon in your demonstration. Provide a report that clearly describes what you have done. This report should explain why you have chosen these triggers and how they work. The Postgres code should be contained in an appendix that is separate from your report.

NOTE: There is a difference between a trigger where changes to one table are used to trigger changes in another table and a function that is used to implement constraints on changes to one table based on changes to another table. Be sure that what you claim as triggers really are triggers.
Your report should not include the Postgres code.

(G) The combined museum (and its individual member museums) needs a standard set of transactions to update its data. While these transactions should include everything that was done in Assignments 1-3 ( including both single file transactions, and transactions that include multiple updates to accomplish a desired purpose), it is likely that more will be needed. You need to develop (and test) a full set of transaction templates that you can use with dbvisualizer to demonstrate how these transactions will work (including any transaction processing related logic). These transactions will need to be demonstrated to your instructor, using data that he decides upon in your demonstration.

Your report of (G) should clearly explain the purpose for and the operations (including the tables affected) of each of these transactions.
This report should be divided into

existing transactions (from A# 1-3)
new updates.

It is expected that you will have identified at least 3 new multi-table transactions.

Your report should not include the Postgres code.

(H) The combined museum (and its individual member museums) needs a standard set of transactions to perform queries. While these queries should include everything that was needed in assignments 1 - 3, more will be needed to serve other information needs related to using the new database. You need to develop a full set of queries templates that you can use with dbvisualizer to demonstrate how these queries will work (including any processing related logic). These queries will need to be demonstrated to your instructor, using data that he decides upon in your demonstration.

Your report of (H) should clearly explain the purpose for and the operations (including the tables affected) of each of these queries.
This report should be divided into

existing queries (from A# 1-3)
new queries.

It is expected that you will have identified at least 3 new multi-table queries.

Your report should not include the Postgres code.

Marking Structure

The following marking approach will be used for each part of the project::

10/10 - Excellent
9/10 - Outstanding
8/10 - Very Good
7/10 - Good
6/10 - Weak
5/10 - Poor
<5/10 - Very Major Problems

It is expected that most marks will range between 6 and 9 out of 10 for each part (where parts are worth 20 marks, the mark from this approach will be multiplied by 2) and that a few teams may receive marks outside these bounds.

NOTE:

Your technical accomplishments will largely be marked based on demonstration you provide to the instructor.
Your report needs to focus on analysis and design. It needs to concentrate on what you did and why you did without being cluttered with code. The code that belongs in your appendix will only be marked for its presence or absence, since it's effectiveness will already have been evaluated in the demonstration. Reports with excessive code in the main body of the report will have marks deducted.

Teamwork is Essential

While team work often is split up based on the skills and interests of the team members, learning can better be facilitated by rotating duties so that all team members learn each type of work.

If everyone tries to do everything together, you will likely spend an excessive amount of time.

If everyone does a part all on their own, the parts will likely not fit and the systems will likely be a disaster.

Thus you need to do something in between these two extremes:

plan together;
delegate specific tasks and completion dates to individuals or to pairs;
complete delegated tasks/components;
test your results/components; or what's better have someone else test your results/components;
combine the results/components;
have the whole team evaluate the results;
repeat the above steps as required to complete all parts (e.g. part 1 is needed as a basis for all other parts)
iterate to "tune-up" the result.

If you get behind and miss any of these steps, your project will suffer.

It's Your Responsibility

Each of you is responsible for the successful completion of your project. In most cases team members will work well together and help each other to succeed. However, in some cases, problems may occur in a team. The most frequent is someone not pulling their weight and not doing their share of the work (whether by ignoring the team, doing a real quick and dirty job, or through an endless series of excuses). If problems occur for any reason, they should be reported to the instructor as soon as they arise to avoid unnecessary complications and penalties. Failure to report problems will be taken as your agreement that no problems exist. While some account will be made for individuals going above and beyond for the team, ignoring or hiding problems by doing someone else's work does not count as going above and beyond.

Most teams work excellently. Some teams may even lead to life long friendships. However, we are prepared to deal with the various problems that might arise. Teams may fire members only after discussing the grounds for firing with the instructor. These grounds can include, but are not limited to: abusive or other inappropriate behavior by a member or failure of a member to do a fair share of the team's work. In most cases the instructor will attempt to consult the individual involved, before giving approval (however where the individual is not available in two or more successive classes, approval may be given in absentia).

Team Project Mark Adjustments

It is the intention that a single mark will be given for the class project and that all members of the team will receive this mark. The fairness of this marking scheme is contingent on all members participating at a reasonable level in completing the project. All team members are responsible for the successful operation of their team as well as for the successful completion of the project

In the case of team breakdown or problems that may lead to breakdown, the instructor should be advised as soon as this occurs so that steps can be taken to ensure successful completion of the project.

The instructor reserves the right to adjust individual marks in teams where all members do not participate together appropriately.

There will be an opportunity upon completion of the project for each member to comment in writing on how much each of the team members contributed to the overall set of assignments. These comments will be taken into account by the instructor and may be the basis for discussions with the team to determine further the facts of the situation.