Final Project - Part 1 Spring 2025

Collaborative Group Final Project Announcement - Requirements for Part 1

 

Hi all, as I announced at the beginning of the semester, this section of CIS 205 will have a Final Project as opposed to a Final Exam. Since I accidentally scheduled the Due Date for the CREATE TABLE Assignment two weeks from the available date, this provides a little buffer time for us to start discussing some of the requirements for the Final Project this week.

 

As I indicated at the beginning of the semester, I strongly recommend that students work in groups participating in the Final Project.  Students that are unable to participate in the Final Project in a group can work on the provided Final Project topic individually.  However, the expectation is that students will work collaboratively on the Final Project in groups.  Shortly after the next few weeks, I will designate most of the remaining scheduled time on Thursdays for the rest of the semester for students to work collaboratively on the Final Project.

 

 

 

This class announcement is Part 1 of a Two-Part sequence of announcements concerning describing the requirements for the Final Project. This Week Part 1 of the Requirements is published and announced.  Over the next couple of weeks, the requirements for Part II of the Final Project will be published. In general, Part 1 of the Final Project is designed and intended to be a collaborative activity.  Part 2 of the Final Project is intended to be an individual activity. 

 

 Separating the Final Project Announcements into components is an attempt to break a complex sequence of processes and tasks into smaller more manageable components. Towards the end of the semester, before the Final Project is due, there may be a few smaller reminders about submission requirements and coding standards that everyone should be gradually adapting to.

 

As I have been announcing in class, I am strongly recommending that students work in groups of 2 or 3 members per group on the Final Project.  Students may optionally decide to work on the Final Project alone. However, this is not recommended.  Working collaboratively on a shared project is an expected professional development practice in Computer Information Systems. 

 

 There are only a few more Mondays and Wednesday s left in the semester.  The remaining Mondays will be dedicated to completing the remainder of the lecture material for the course.  The remaining Wednesdays will be dedicated to completing the Part 1 Phase of the Final Project.  Students working collaboratively in groups are expected to use class time on Wednesday to meet with group members and complete creating the required artifacts listed in the requirements for Part 1 of the Final Project. I will be present to offer additional consultations and guidance.

 

Group Memberships

This Wednesday, I will begin to finalize the assigned topics for the Final Project. I would also like to confirm some instances of decisions on if students are working individually or collaboratively within a group on the Fina Project.  I would also like to get a preliminary understanding of group membership configurations.   What students are in group memberships and what groups they are connected to?

 

 

 

Database Topic Approval

 All groups and individuals must have an approved topic before starting Part 1 of the Final Project. All students must submit the information described in the assignment titled “Collaborative Group Final Project Group Composition Assignment - Submit Team Name and Group Membership Submissions along with any Topic Requests”.

 

Students Working Individually

Students working alone are still required to submit all of the required files and documents for both Part 1 and Part 2 of the Final Project.

 

PART 1 – Collaborative Group Database Development and Implementation

Part I of the Final Project Assignment is designed as a Group Collaboration Project.  Part 1 of the Final Project Primarily focusses on students working collaboratively to create a Conceptual Model of a database based on an assigned topic.  Part 1 of the project involves the following: creating the database design documentation, creating sample data, creating the database, creating the tables in the database, and inserting the sample data into the tables in the database.  Creating the database involves writing the CREATE DATABASE Statement, writing the USE database statement, writing the CREATE TABLE Statements, and writing the INSERT INTO Statements to load the test data.

Final Topic Group or Individual Topic Assignments

 

All groups or individuals must develop a Relational Database based on an assigned topic.  Final Project submissions not based on the assigned topic will not be accepted.

 

Shortly all students should have received their assignment database topic.  Students can request a topic to get approved or request to have a topic assigned. Assignment topics must be assigned or approved.   Groups or individuals should not start working on a topic until it is assigned or approved.   Groups or individuals attempting to submit an unassigned or unapproved topic will receive a zero for the Final Project

 

Here is a list of the Recommended Processes to Achieve the Best Results

  1. Describe the Requirements for a Database

Describe the requirements for a database that will support a small business.  Remember the three tied architectures.  The business may be online.  You are assigned a small business scenario or a real-life scenario. In this section, you write in your own words your interpretation of the requirements and also try to formulate the queries that you want to write for the database. 

  1. Design the Conceptual Model

Design the conceptual database behind the described requirements.  This is creating the ER Diagram for the small database based on the requirements and the business rules. For the Final Project, students must investigate or invent the Business Rules for the Topic Database.

 

  1. Translate the Conceptual Model to a Physical Schema

Convert the conceptual design to the potential physical design.  This is translating the ER Diagram into the relational schema and metadata describing the tables, attributes, and relationships. At the end of this process, a Relational Schema Document.  A normalized version of the Relational Schema should also be considered.
 

  1. Metadata Documentation should be created.

  2. Database Implementation

 Implement the Physical Database from the Conceptual Design Documentation for the database.  This is writing the SQL Statements to create and use a prototype database. This is transposing the relational schema and metadata into a Database in the DBMS. This is writing a series of CREATE TABLE statements with the appropriate CONSTRAINT modifiers within that database to establish relational connections between the tables. 

  1. Testing the Implementation Part 1

This involves providing a basic framework for testing the physical design.  There should be two basic sub-processes within this process. 

 

The first sub-process is creating sample data that will be used for testing the final physical design of the database. During the course, students have had the opportunity to explore different techniques for loading data into tables in databases. This is an opportunity to test those techniques.  

 

The second sub-process within this process is writing the INSERT INTO Statements to insert the sample data into their appropriate tables in the database. This involves transferring sample data into a series of INSERT INTO Statements. 

 

 

 

 

For Section 6 of inserting data and testing the Implementation phase, two documents should exist. 

 One of the artifacts should be a document containing the planned sample data, during class, an Excel Spreadsheet was used. 

 

The second artifact should be a SQL text file document containing the series of INSERT INTO Statements listed in the proper sequence to maintain referential integrity.  The INSERT INTO Statements should be combined with the CREATE TABLE Statements in the correct order.

  

Here are some Helpful Hints to Get Started with your Part 1 of the Final Project

 

  1. Describing the Requirements for a Database

This is rewriting in your own words your interpretation of the requirements of the database that you are designing and implementing.  If you cannot put into writing what you are doing, you may not understand what you are doing.

 

 Helpful Hints

 You need to describe at least the following aspects of the Database:

What products and/or services will the database hold, the categories of products/services, the approximate number of product titles in each category, what (approximately) is the product information that the customers will be able to access

What information is needed from the database?  This is what queries would you like to create to extract meaningful information from the database.  This is a collection of single and multiple table queries that the group will create to test the basic functionality of the database. This information can be used in the Part II Phase of your Final Project

 

 

  1. Designing the Conceptual Model

In the ER Diagram, show all relations between columns, specify the type of the relationships (one-to-one or one-to-many), and what is the foreign key by which the tables are related. You can do this in the diagram by placing an FK suffix at the end of the attribute that is a foreign key.  

  1. Translating the Conceptual Model to a Physical Schema
    This phase includes writing out the Relational Schema Documentfrom the ER Diagram Model.  Remember, underline the attributes representing the primary key in each of the schemas in the relational schema.

 

  1. The Metadata

 Write the metadata document from the Relational Schema Document.  Summarize the purpose of each table.  Describe the attributes in each of the tables.  Describe data types and constraints on the data.  Identify primary keys and foreign keys. Specify whether the value can be null or not.

  

  1. Implementing the Database

Create a database Container.  Write the SQL statement that will create the database, this is the CREATE DATABASE Statement. Specify that the database should be used.  This is the USE Database Statement

 

 Create the tables in the database in the correct sequences to maintain referential integrity.    Write the SQL statements that will create the tables on one side of 1-M relationships first that are described in the Schema and Metadata. Create the tables that are on the many sides of 1-M relationships after the tables on the one side have been created. Relationship Tables should be created last, they are always dependent on tables on many sides. This involves writing the SQL statements that will create the Foreign Key Tables last.  These are the tables on the many sides of the 1-to-many relationships and the relationship tables are the mapping tables that are a part of the many-to-many relationships.

  1. Implementation of Part 1 of the Testing Phase

Creating the Test Data

This involves using existing sample data or creating sample data to verify that the database design is working effectively with real data.  At this phase, it is important to organize the test data similarly to the order in which the table creation was organized. 

  • The sample data for the tables on one side should be decided on first. 

  • Following that, the sample data for the tables on the many sides should be decided on. 
  • The values for columns representing foreign keys in the tables on the many sides should match values contained in primary key columns in tables on the one side of 1-M relationships. 

 

  1. Insert Test Data. 

This is the fictitious sample data or real sample data that you chose or created that will be used in your INSERT INTO statements to create your sample test data for the database to test the database. The sequence order of the INSERT INTO Statements should follow the same pattern as the CREATE TABLE and Sample Data Creation or Selection Process. The INSERT INTO Statements that insert data into the tables representing loading the sample data into the tables on the one side of the 1-M relationships should be arranged to execute first.  This should be followed by executing the INSERT INTO Statements representing loading the data into the tables on the many sides secondly.

 Following this sequence will assist in revealing any referential integrity errors in your database design.  It should also assist in reinforcing your understanding of the referential integrity checking process in a modern DBMS. Sample data that violates referential integrity will generate logical referential integrity errors when attempting to insert the data out of sequence.

 

 

 

 

Some Additional Advice When Designing Your Project

Do not try to be too ambitious in planning your database, it is only a Prototype Project. As much as you would want to have a lot of features in your database, you must keep in mind that they will be implemented later in the real production-level project.  So please try to create a minimal design that will be easy to implement and test within the schedule of the course.

 

To accomplish this, you may need to reduce the scope of the project to get a working version completed within the remaining time in the schedule of this course. In IT, if you cannot deliver a list of features within a given schedule, you have two choices.  You can either reduce the features to deliver on time, or you can extend the time of the project.  We are at the end of the semester, so we cannot extend the time, we can only reduce the activity in the project.  In a Database Project, reducing the scope means removing tables, data, and queries from the project.

 

Designing the ER Diagram, creating the Relational Schema, creating the Metadata, and inserting the Test Data is a collective component of Part 1 of the Final Project Assignment.  Once the database structure is created and the test data has been inserted, the projects turn into Part II of the assignment.

 

Part Two of the Final Project

 Part two two of the Final Project is an individual component.  No two students should have the same exact or identical SQL Statements for the Part Two segments of the Final Project.  Additionally, more detailed information and requirements for the Part Two segment of the Final Project will be provided shortly.

 

There is no group component in Part II of the Final Project.  Part II of the Final Project is an individual activity.  Individually you are to write queries to test separate segments of the database implementing and utilizing concepts, clauses, and statements studied during the semester. The Requirements for Part II of the Final Project will be published and released in a second-class announcement.  You will not be able to start Part II of the Final Project until Part I has been completed.

 

 I will be available for Individual or Group Consultations if Needed. 

 

 

 

Below is the list of documents that each student must be completed in the Part 1 Component of the Final Project

  1. A description of the Database and its requirements that you are designing and implementing.

  2. ER Diagram of the Database

  3. Relational Schema of the Database

  4. Metadata for the Database

  5. Sample Data that you are planning to use to test the database.

  6. SQL Script to Create the Database

  7. SQL Script to Load the Sample Data

 

The SQL Script to Create and Load the Database with Sample and the SQL Script of your individual SQL Statements Must be combined into a single SQL Text File

 Reminders

All SQL Script must be submitted as a text file with an SQL extension.  Do not submit SQL script in a Word Processor file Format. Word Processors embed special characters into files that may cause syntax errors when (executing/running) the script.    Do not submit SQL Script as text in the Submission Text Area. All SQL scripts must be submitted as a Test File with an SQL extension.  An SQL extension is preferable since it is the standard in the industry for SQL scripts.

 

 Test Your SQL Script

Make sure that you test your SQL script by copying and pasting your SQL script from your text file into the MariaDB command line. While testing your script make sure that all attribute declarations for you create table statements end with a comma.  Make sure that the last constraint declaration for you to create table statements does not end with a comma.  Commas indicate that there is another declaration.  In a CREATE TABLE Statement, if there is not a declaration following the last constraint declaration, do not include a comma in that declaration.

 

Review the Announcements that I published in Canvas on Guidance on testing categories of SQL statements such as CREATE TABLE, INSERT INTO, and SELECT.

  • SQL Script must be submitted as a text editor ASCI Text file, not in a Word Processing format. Do not submit SQL Script in a Word Processor Format, it will not be accepted in Word Processor Format. SQL Script files must have an SQL extension on the file name. Files without SQL extensions will not be assessed.

  • All SQL scripts must run and execute continuously. If you are submitting text that contains syntax errors, you should arrange and comment on your text in order of importance. Any text that generates errors due to errors in the proceeding text will also be marked as incorrect.

 

  Check Referential Integrity

  • Make sure the tables are created in the correct order that follows the rules of referential integrity.  The tables on one side must be created before the tables on the many sides of the relationship. 

 

  • Make sure that Data Insertion occurs in the correct order that follows the rules of referential integrity.  The Primary Key Values on one side must be inserted before the Foreign Key Values on the many sides of the relationship.