[2022] CMPT 354 Database Systems I - Group Project - Database Application
CMPT 354 Group Project
Prepared by Hazra Imran
Updated: May 6, 2022
Students in teams of 3 are required to select an application that would benefit from a database and build a database application from start to finish.
Philosophy
Building a full database application from scratch allows you to control the process; instead of having the pieces decided for you, you must make all of the decisions yourself. Part of this process is to see how design decisions made at the beginning will affect your final project.
Schedule
There are several intermediate deadlines that you must meet in order to ensure a successful project. Our Advice: Please read the instructions carefully to ensure that you don't miss anything. Start early; don't wait for a few days before the deadline. Ensure that the cover page is turned in with all the milestones. The template of the cover page is available on Canvas. The project has the following milestones:
Milestone | Due date |
Milestone 0 – Group Formation | May 13 |
Milestone 1 – Project proposals | May 25 |
Milestone 2 – ERD and Schema | June 8 |
Milestone 3 – Normalization | June 29 |
Milestone 4a – Implementation and 4b – Peer Assessment | July 29 |
Milestone 5 – Demos | Aug 4-5 |
Background Information
Project Platforms
You may use any legal platform that you like, as long as:
- ● The final application uses a relational database to manage the data.
- ● You don't use an automated system to generate the code and SQL (we want you to write these on your own).
- ● You do not use a GUI generating tool (e.g., Netbeans).
- ● All other requirements are met. Ask, if in doubt.
We are providing support for Oracle with PHP/web. Please note that if you choose to implement with anything other than the provided and recommended infrastructure (e.g., running PHP on your own server), you may be asked to submit additional information or additional checkpoints. Furthermore, no support for anything other than the recommended software will be provided. Note that problems with non- supported platforms will NOT be accepted as an excuse for late project submission; use them at your own risk! That said, we've had quite a few good projects delivered on non-supported platforms.
You will be required to use some form of version control for your code (sending zip files across messenger services with names like v1, v2, etc., does not count as version control). Using version control is a skill that will be useful to you in future courses and in the industry. Version control also makes it easier for the teaching team to check in on your progress and help if required. We recommend that you use Git for version control.
Milestone Submission Rules
Milestones 1-3:
- ● Will not be accepted 48 hours after the due date and time. A grade of zero will be assigned.
- ● Will be subject to a 25% deduction per portion of a day (i.e., the deduction applies whether the
delay is 2 mins late or 23 hours late).
- ● The 25% deduction is calculated based on the full value of the project. For example, if a deliverable
is worth 20 points and is handed in a day late, a 5-point deduction would be applied.
Milestone 4-5:
- ● The project check-in meeting is mandatory. If a student misses the check-in without documentation supporting the absence, we reserve the right to apply a penalty of 25% to their milestone 4.
- ● No late submission will be accepted.
- ● Students absent for milestone 5 will be given a zero unless documentation supporting the absence is
provided within two days of the demo date.
- ● If the provided documentation is accepted, the implementation portion of the project will be
re-weighed to include the value of the project demo.
Milestone 0 (Form a Group)
The term project will be completed in groups of 3.
General Advice: Before you commit to being in the same group, we suggest that all team members ensure they are on the same page regarding:
- ● The minimum acceptable mark they wish to receive on the project.
- ● Your working style. For example: Do you like to start early, submit early, and work on things in
smaller chunks throughout the weeks? Or, do you prefer to start working on it in larger chunks the week before something is due? Do you like to meet in the mornings? Afternoons? Evenings? (This may be time-zone dependent.) etc.
- ● How you wish to communicate (e.g., through email, through Zoom, in person).
- ● The application domain (business, industry, hobby, non-profit organization, etc.) that you might
be interested in using for your project.
- ● How much time do you wish to dedicate to this project? It's fine if you say not a lot, but you need to make this clear to your teammates and get their approval. They will need to agree and be able to pick up the slack. Your own project grade will be reduced proportionately. For example, if you tell your teammates that you only want to do about 15% of the work (instead of approximately 33%, give or take), and you want the other 2 group members to do about 85% of the work, then as long as: (a) you're happy with getting about 50% of the project grade that your 2 partners get, and (b) your 2 teammates are OK with this arrangement, then we will be OK with it, too.
● Of course, if your teammates are not happy with doing the rest of the work, then obviously, this partnership/group isn't going to work; so, you shouldn't become a group of 3 in the first place.
The term project will be completed in groups of 3. Once you have found a team, click on the People page (listed in the sidebar) and then the Groups tab. All team members must join the same group. By default, Canvas loads 10 project groups at a time. If the listed groups are all full, keep scrolling down the page to force Canvas to fetch the next 10 groups until you find a free group to join.
Once you find an empty group to join, click "JOIN". Be sure to take note of your group number as you will need to tell your teammates. All teammates must join the same group.
Groups of 2 are only allowed in extraordinary circumstances subject to prior approval from the instructor. Note that groups of 2 will not receive extra concessions (e.g., extra credit, extra time, smaller project scope, fewer deliverables, etc.)
Deliverable
All team members should enroll themselves in the self-enrolled groups.
FAQs:
- ● I don't know anyone in the course. How can I find a team?
Please use the "Find a teammate" post on Piazza to find a team. You can also talk to people in your lecture section. Ultimately, it is your responsibility to find a team. - ● Another student (who we do not know) joined our group on Canvas.
Let us know which group you are in and who the unfamiliar student is and we will remove them for you.
Before you put your group members' names down in a Project Group, make sure that that group number is not already being used by some other group. If so, keep scrolling down, and find an empty group. Then, after saving your names in the group, verify that the names present are your expected group of 3. If you have only two names, and are looking for a third, you can enter your two names, for the time being. Once you find a third member, invite them to join your group number.
Milestone 1 (Project Proposal)
An important goal of the project is to allow you to have the freedom to design your own application. We want to let you have the freedom to concentrate on some aspects and not on others. A consequence of this is that we won't be telling you exactly what to do.
We don't expect you to read our minds, so this is one purpose of the checkpoints. We will give you feedback on the project and tell you if you're on track or not. Here are some rough guidelines. We expect each project to have:
- ● At least 7 different entities and 7 relationships.
- ● A variety of rows of data--at least 10 rows per table, but preferably more. You can use a
script or program to generate thousands of rows if you like.
- ● A variety of simple and complex queries, including aggregated queries, and joins among tables. These queries will be part of your application code, that is, you'll perform SQL calls via an application interface.
● SQL statements to perform inserts, updates (with a WHERE-clause, and deletes (with a WHERE-clause).
Just a reminder: Don't use any examples that you've worked with before. For example, it can't be something from another course, the Internet, previous students' work, your co-op term, etc. It must be your team's original work.
Blacklist – Some projects that you may not use are:
● Any data that has been used in previous CMPT 354 classes or the textbook including, but not limited to:
o Employee supervision (projects, managers, employees)
o A bookstore /library (provided as an example- Books, authors checkout etc)
o MP3 storage (used in some previous classes) (song, album, artist)
o Hospital (patient, doctor, Surgeries)
o Drug Store (Drugs, patient, nurse, ingredients)
o Pokemon
o A school setting (students, instructors, departments, courses, etc.) -- we're going to
use this as a case study in various parts of the course
o Banking (accounts, customers)
o Airlines (flights, planes, passengers, destinations)
o Retail sales (customers, products, orders, line orders) -- instead focus on other aspects of
retail, like maybe inventory tracking, shipping, etc.
- ● Any projects given to you in class/tryout/clicker activities.
- ● Any pre-existing project available on Google.
If you are unsure, ask on Piazza/instructor/project TAs!
Project Platforms
Refer back to the beginning of this section for some ideas about RDBMSs (Oracle, MySQL, Postgres, etc.) and programming languages (Java/JDBC, PHP, C++, Python, etc.) Your group may use any legal platform that you like, as long as:
- ● The final application uses an RDBMS
- ● All other requirements are met
Deliverables
What to Turn In
All of the following items must be put together into a single PDF file. There is a docx and pdf version of the template available on Canvas. If you are unsure about how to incorporate the cover page into your milestone, please see your TA before the assignment due date. You need to provide:
- A completed cover page
- A brief (half-page or paragraph) project description answering these questions:
o What is the domain of the application? Describe it.
o What aspects of the domain are modelled by the database? 3. Database specifications:
o What benefit does the database provides to the application?
o What functionality will the database provide? 4. Description of the application platform:
o What platform will the final project be on?
o What is your expected application technology stack? 5. Other comments, as appropriate, to explain your project
Milestone 2 (ERD and Schema)
The aim is to do modelling using ER diagram and Schema for your database. While working, you may go through several refinements but, you must only submit the final version. Each group must provide the following:
- A cover page
- An E/R diagram for the database the application will use. It is OK to hand-draw it, or to use software to draw your diagram (e.g., draw.io, GoogleDraw, Microsoft Visio, Gliffy, maybe Word, etc.) The result should be a legible PDF or PNG document. Note that your ER diagram must use the conventions we have discussed in class (e.g., no crow's feet notation). If your chosen platform does not support the conventions we discussed in class, you must find another platform to use.
Your E/R diagram should include:
- ● At least 7 different entity sets excluding weak entities and ISAs.
- ● At least one weak entity
- ● At least one ISA relationship
- ● At least 7 different relationships. For each relationship, identify the cardinality
constraint and other constraints, such as participation constraints.
- ● All entities must have their keys clearly identified.
- The schema-derived by translating from your ER diagram (above). For the translation of the ER diagram to the relational model, follow the same instructions as for your lectures. The process should be reasonably straightforward. For each table:
- ● List the table definition e.g., Table1(attr1: domain1, attr2: domain2...)
- ● Specify the primary key, candidate key, foreign keys, and other constraints that the
table must maintain.
- ● NO SQL DDL required.
Rubric : Check the Milestone 2 assignment on Canvas
Milestone 3 (FD and Normalization)
Each group must provide the following:
- A cover page
- Functional Dependencies (FDs)
o Identify the functional dependencies in your relations, including the ones involving
the primary keys.
o List all candidate keys.
3. Normalization
o Normalize each of your tables, if necessary, to be in 3NF or BCNF. Give the list of tables,
their primary keys, and foreign keys after normalization.
- The SQL DDL to create all the tables in SQL. All primary keys and foreign keys must be declared appropriately. Code the SQL CREATE TABLE statements with the appropriate foreign keys, primary keys, UNIQUE constraint, etc.
- Populate each table with at least 5 tuples. Show the instance of each relation after inserting the tuples.
Milestone 4(a) - Implementation
The goal of this milestone is to build on what you have accomplished in the other project checkpoints. This document will show both what you are expected to turn in, and what functionality you should have for your project to be successful. Another goal of this project milestone is for you to tell us what to expect at your demo.
Graphical User Interfaces (GUIs) normally require a lot of time and multiple tries to get them right. We expect you to produce a simple but adequate interface; but it doesn't have to be fancy. You should not spend a lot of time on the interface itself. You should concentrate on the coding of the functionality of your project rather than the GUI. The goal of the project is to give you some experience with SQL and JDBC or PHP. We're more concerned with the application programming (interfacing with a DBMS), SQL DDL, SQL DML, business requirements, design, working effectively as a group, etc. What we are looking for here is a series of buttons where each button will run a query. I.e., we do not want this to just be a text based user interface. Historically the usage of JSwing for Java based projects UI and CSS+HTML for PHP based projects UI have been popular but you are free to use other UI tech stacks.
Note that while you can see what we expect you to show us at the demo in the notes below, it is not enough to hard code the application to just run that set of specific queries - we will be looking at your files as well to show that you've implemented the whole application that you have said that you have. We also expect that you write queries that give you the data required without any extraneous information. I.e., we do not want you to have your application parse through query results. Databases are designed to be efficient at processing queries and fetching information. Having the client fetch and process extra tuples is an unnecessary waste of time and resources.
You will hand in all your work on the project including your codebase and sample screenshots that demonstrate the functionality of your application. We will freeze the deliverables at this point so that groups that demo later than others do not gain an advantage. You should provide some screenshots and sample output of your work to give your project TA a heads-up about your upcoming demo. What you've uploaded to Canvas will be the frozen application and final product. You cannot change it between the time that it is due and your demo.
Milestone 4(b) – Peer Assessment
This task is individual (not to be done in the group). It is expected that all team members should participate equally in all group activities. To ensure this, each student should submit an online report. The report contains:
- ● Individual Assessment - What were your contributions to the project? Be specific while answering this.
- ● Peer Assessment: Student should provide the assessment of other students in the group. Provide the percentage of contribution of each student in your group. If you feel all group members did fair and complete share of work, please put 100 for all students. Each member's contribution should be out of 100. For example, 80 means the member did roughly 80% of what is expected of the member in quality and quantity of work E.g.
