Instructions:

This is the final step, in which you submit a working website URL, DDL and DML files, server-side and client-side code, and the PDF. This Step has no peer or group review. You will format your report professionally, making changes to previous assignments as needed.

This assignment is a "portfolio assignment" for the program. It is encouraged that you make it public as part of your portfolio. Remember that your school-hosted database will be deleted at the end of the term, so think about what you'll do after that. A video of a working website, posting your final project PDF on LinkedIn, or hosting the site (MySQL database, HTML pages, and code) on a third-party hosting platform are all examples of how to make your portfolio assignment available after the term is over.

Deliverable:

A ZIP archive containing FIVE THINGS, as detailed below.

The zip file's name should be projectgroupX_stepX_FINAL.zip. e.g., ProjectGroup42_Step6_FINAL.zip.

1. All of your Project's source code: This includes CSS, HTML, JS, and the code for the server-side platform that you chose to use. To credit originality, code should include citations that clearly credit non-original work in a readme file and/or at the top of each file. Include package.json (or the equivalent) to document any external libraries. Do not include all library files, such as node_modules (or equivalent), which add unnecessary size (e.g. 20MB+). Please empty the dbcon (or equivalent) file of your username and password.
2. A PDF file containing (in the following order): a one-page (maximum) summary of feedback and changes made to this Project from its inception to the present.
3. A revised version of your Project Outline, Database Outline, ERD, Schema, Sample Data, and screen captures of each of your website's UI pages. Add a title directly above each Screen capture that describes the CRUD step. "READ/BROWSE/DISPLAY Customers page," for example, or "CREATE/INSERT/ADD NEW Invoice page."
4. On the first page of the PDF, include the URL of your hosted, operational project website, as well as a comment on your submission. You must prefix the URL with http:// or https://. If you want your web app to be tested only on a specific browser, make that clear to the graders by indicating it on: a) the first page of the PDF in a prominent place AND b) every page of your website.
5. The .SQL file with your Data Definition Queries and the sample data.
6. The .SQL file with your Data Manipulation Queries.

Each of these criteria will be evaluated based on the rubric below. Please double check the rubric to avoid missing points carelessly or spending time on tasks which are not evaluated.

The project website should be operational until the end of the term.

How do I work on this assignment?

Working on and reviewing the steps for CRUD functionalities should have given you a good head start, if not actually bringing your website to a finished state.

If you continue to have problems, post them on Ed Discussions or in office hours, or request a class meeting session.

Always include the following when asking a question:

1. What is the unexpected behavior/error message that you are encountering?
2. What are you attempting to accomplish?
3. Use a website like pastebin.com to link to your code. Ensure that all related code files are visible to others.
4. What have you already attempted? This ensures that anyone attempting to assist you will dismiss the obvious.

Instructions

As a Group, you turn in the Final Version of the Step 3 Draft: Design HTML Interface + DML SQL #4. It is expected that you have made changes to your submission in response to the peer review(s) you received for the Draft version.

Along with everything described in the Project Step 3 Draft (Group on Ed Discussion), you must also include the following sections at the top of your PDF file:

The peer reviewer's feedback

Include any Group Review reviews that your Draft submission received verbatim. Mention that you did not receive any reviews. You would incorporate feedback from both Project Group members.

Actions taken in response to feedback

List briefly the actions you chose to take in response to the above feedback. If you decided not to act on a specific suggestion, you must explain why in detail.

Improvements to the Draft version

If you are making changes to the files as a result of your own design decisions, they should be listed in this section.

How should I get started?

Use the feedback from your classmates to improve your Draft version. You can continue to use the Project Review group to solicit feedback and bounce ideas off your classmates. Finally, if you have any questions, please post them on Ed Discussion or ask them during TA or Instructor office hours.

Instructions

I want you to work in a Project Group to develop a proposal for the CS 340 Project that is no more than 5 pages long. This assignment provides a framework the for your Project and the design of your database. After seeing the lectures this week and previous, you should have a good concept of what databases are used for, as well as the fundamental definitions of entities, relationships, attributes, and other types of constraints.

According to the CS 340 Project, you should have at least four entity tables (4 separate 'things' in your database) and four different relationships, one of which must be a many-to-many relationship. Your proposal should explain the project in depth and identify the four entities and four relationships listed below.

This assignment also gets you started right away on your project by having you create an ER diagram. That diagram may be used to forward engineer a database, as shown in the Exploration - Creating ER Diagram MySQL Workbench, for later project phases that develop a website in NodeJS or Flask to communicate with this database.

Database design is an iterative process, and we don't expect you to nail it on the first go. However, this will allow you to create something and receive feedback from others on your effort. Later phases will include making modifications to your design and going through the process again.

Deliverable

1. A single PDF containing:

   • Team members name and project title

   a) Overview

   Describe briefly the problem that a website with a database back end will address. One excellent paragraph may enough. Be clear (and offer many figures) so the reader understands what type of system is required. A company with 200 sales per year will have a completely different system than one with 20,000 sales per year. For example:

   Adventure Bikes sells $20 million in bicycles annually. A database driven website will record SalesOrders of Products to Customers.

   b) Database Outline, in Words

   Describe each entity in detail, including its properties and any relationships between entities, using a bullet list. Describe the function of each entity. Provide some information about each characteristic, such as the data type and any limitations. Describe any connections between entities in this section as well. For example:

   • Customers: records the details of Customers we do business with
     • customerID: int, auto_increment, unique, not NULL, PK
     • email: varchar, not NULL
     • ...
     • Relationship: a 1:M relationship between Customers and Orders is implemented with customerID as a FK inside of Orders

   All subsequent phases in the Project development will be rated depending on whether or not they correspond to your description of how the entities and connections function. If you do not provide enough information, the grader will apply their best judgment, which will be final. For example, if we believe that a consumer should be able to purchase many goods on a sales order and you do not clearly articulate this assumption in this assignment, you will most likely lose points in the project's subsequent phases if your website does not enable multiple products on an order.

   Maintain consistency in the naming of entities and attributes across your document; this will benefit you later when writing code. For example, if the overview mentions Customers, we should anticipate an object called Customers. If you use (or do not use) initial capitalization for entity names, we expect all entities to follow this norm. Entities should be plural (e.g. Customers) and attributes should be single (e.g. customerID). It's a good idea to differentiate when two words are merged (e.g., customerID or customer_id), but avoid using spaces in names (e.g., NOT customer id).

   c) Entity-Relationship Diagram:

   An ER diagram that corresponds to the structure of your database. Anything that does not correspond to the Database outline or utilizes inappropriate notation will result in a point deduction in the Final Version of this assignment.

   Note — ER diagrams are a logical description of databases that frequently leave out certain features. Entities, for example, might be represented without any properties. M:N connections can be shown without the need of intersection tables. For the sake of brevity and simplicity, ER diagrams may omit certain information. It is not always incorrect to opt to make your ER diagram more complete by including most characteristics and also presenting the intersection tables in your ERD. However, we recommend that you keep certain implementation details for the schema that you will construct in a later Project Step.

   We recommend that you design the ERD using Visio, Draw.io, or MySQL Workbench, as illustrated in the Exploration - Creating ER Diagram MySQL Workbench. You may also sketch by hand and scan the results. Please use the notation provided in the learning materials.

   Note that you do not have to include all attributes in the ERD. We also do not want you to provide the schema, DDL, or sample data for the time being. These will be included in subsequent project phases.

How do I turn in this assignment?

Follow the steps below to turn in your Project Step Draft and review the work of others:

1. Create a post in Ed Discussion called Group XX Project Step 1 Draft. Put your post in the Project Step 1 Drafts & Reviews category. Communicate with your team to find out who is submitting the draft. Include both members' names as well as your Group number.

2. After that, copy the URL of your Ed post and paste it into Canvas. For the group, only one teammate must submit.

   You have two options for copying your post URL:

   1. Navigate to the thread list, right-click your post, and then choose copy link.
   2. Scroll down to the bottom of your post. To access the menu, click the three dots at the bottom [...]. Copy the link.

3. Each team member should then review two additional drafts - reviews are an important part of the process because you are helping others while learning. Make an effort to make thoughtful and substantial suggestions.

Instructions

In this step, you implement the CREATE, READ, UPDATE, and DELETE functionalities in your web app for ONE entity (table), as described in the CS340 Project Guide. Each entity in this project must have have an insert and select. For a M:M relationship, at least one update and one delete are required. Although using an intersection table is a good idea, spreading these four CRUD operations across multiple entities this is also acceptable. The end result will be a working HTML page(s) that connect to your database implement the logic to perform the four CRUD operations. Ideally you are implementing the HTML forms, and SQL queries that you submitted in the previous steps in the web technology of your choice.

What should I submit for this assignment?

1. Post the deliverables described below (the ZIP file and the URL to your website) in the Discussions section of the Project Step 4 Draft Version Ed Discussion post. Only one member of the group should post it.

   This draft post will differ slightly from previous steps. Please include the following with your deliverables (the ZIP file and the URL of your website):

   1. What technology you are using (e.g. NodeJS/Flask Python).
   2. What is working.
   3. What is not working.
   4. Where and/or why are you blocked?

   The post can be very brief—just enough to let us know how you are doing and how we can help. If you have completed all of the requirements for this step, please respond by saying so and let us know if you would like feedback or suggestions.

2. Copy the URL of your Ed post and paste it into Canvas. For the Group, only one teammate must submit.

3. Each team member should then review two additional drafts — reviews are an important part of the process because you are helping others while learning. Make an effort to make thoughtful and substantial suggestions.

Deliverable (TWO):

1. An index.html URL that links to your website's pages that implement the CRUD functionalities. Put this URL on the first page of the PDF file as well.

2. A ZIP archive containing these files

   • A .PDF document.
   • A .SQL file that contains Data Definition Queries.
   • A .SQL file with Data Manipulation Queries.
   • The current version of your project's code.

   Make sure your zip file is named ​projectgroupX_stepX_DRAFT/FINAL.zip, e.g. ProjectGroup42_Step4_DRAFT.zip.

Details

1. A URL to an index.html
   You must submit the URL of an index page that links to pages on your website that implement the CRUD step.

   At this point, we only expect one page to function properly.

   • To implement the CRUD steps, your server-side code should use the DML queries you wrote in #4 and #5.
   • The READ step displays data from the database table (via SELECT). If you use a dynamic drop down or a search criterion, these will also run SELECT queries on your database.
   • The CREATE step accepts input static HTML pages and allows users to insert new records into the entity using INSERT (this is not to be confused with CREATE TABLE).
   • Using an UPDATE query, the UPDATE step is used to modify existing data. It should be noted that having the user enter IDs for foreign keys is not acceptable because it may result in incorrect IDs being entered. You can use a drop-down menu or something similar, but you should not require the user to enter the ID manually.
   • The DELETE step is used to delete a record, which is typically done with a DELETE and a primary key.

   This URL must be included on the first page of your PDF.

   Still confused ? Want to know where to begin? Look at the section below titled How do I work on this assignment?.

2. PDF File
   a) Updated Project Outline and Database Outline, ERD, Schema, and Sample Data

   This section should include the revised outline, ERD, Schema, and sample data based on feedback from the grader and peers, as well as any design decisions you made on your own. It is required that you use the various design tools you have learned up to this point (such as Normalization steps, ON DELETE CASCADE, and so on) to review and fix your ERD and Schema. The application of these concepts will be graded in your Final Project submission.

   b) Fixes based on Previous Step Feedback

   This section of your PDF should detail everything you were told to fix in the feedback from the grader and peer reviewers in previous steps, as well as how you fixed it. If you chose not to fix things based on the feedback, this section would explain why you believe they should remain as they are. Please mention if you did not receive feedback from a grader or reviewer on a specific step.

3. Data Definition Queries in a .SQL File
   In a separate SQL file, submit an updated version of the Data Definition Queries you submitted in the previous Step, with fixes based on any feedback (which you are required to list and describe in the PDF section above). As with the previous step, this file should be easily importable using PHPMyAdmin on the college's database server. Though not required, it is recommended that you review and fix your Data Definition Queries using the various concepts you learn in this module, such as Database Normalization. Your application of these concepts will be graded in your Final Project submission.

4. Data Manipulation Queries in a .SQL File
   In a separate SQL file, submit an updated version of the DML queries you submitted in the previous Step, with fixes based on any feedback (which you are required to list and describe in the PDF section above).

5. Current version of your project's code.
   We only need the HTML and server-side code needed for your project. Please do not include common library folders as this will result in a very large zip file. It is expected that your project is well-organized, with self-documenting folders and file names. Your programming code should contain meaningful variable names as well as brief comments that explain logic. Any non-original code should be accompanied by a citation that clearly credits the source.

How do I work on this assignment?

If you have never created a web-based application before, I recommend using one of the Node.JS or Flask class starter guides to help you build the back-end programming that connects your HTML pages to the database.

You should start with the READ functionality because it is the simplest to implement. In the previous Step, you must have decided what data your HTML pages will display. Now all you have to do is ensure that a query containing that data is properly executed on the database and that your server-side code receives the results and can make them available in a format that your front end can display. If you're using a templating engine, such as handlebars or Jinja, that's usually in the form of an array of objects that you iterate over.

Make sure your INSERT, UPDATE, and DELETE forms have valid HTML code. All of your input fields should have valid names, and the submit button should work. The form's action should point to the data processing path provided by your server-side code.

Then, using any HTTP request, write your server-side code to retrieve the data. This information should then be inserted into your DML query before it is executed on the database. Any database results would then be sent out in an HTTP response and displayed to the user.

All interactions with the server-side code can take place using AJAX or not. Common errors and omissions in server-side interaction and database querying can be debugged by printing (or displaying) a variable before it is processed and used. For example, you might want to print the request before extracting and using data from it because your form may send an HTTP GET request while your server-side code only handles HTTP POST requests, resulting in a mismatch.

If you have any questions, please ask them on Ed Discussions or during office hours!

Instructions

In this step, you will create the HTML front-end for your web app to provide all the CRUD functionalities described in the CS340 Project Guide, as well as write the queries that will be run on the Database to provide each functionality.

Deliverable

A ZIP archive containing the FOUT ITEMS detailed below:

1. A .PDF file containing information from previous steps.
2. A URL to an index.html file that contains a list of all your HTML pages. Include this URL at the top of your PDF file and in your discussion post.
3. The .SQL file with your Data Manipulation Queries. These are the queries used by your website to allow users interact with data. SELECT, INSERT, UPDATE and DELETE queries are required to provide the functionalities described in the CS340 Project Guide.
4. The .SQL file that contains your Data Definition Queries as well as the sample data INSERT statements from the previous step.

Make sure to name your zip file ​projectgroupX_stepX_DRAFT/FINAL.zip., e.g. ProjectGroup42_Step3_DRAFT.zip.

PDF File

1. Updated Project and Database Outlines, ERD, Schema, and Sample Data

   This section should include the revised outline, ERD, Schema, and sample data based on the feedback from the grader and peers, as well as any design decisions you made on your own. It is required that you use the various design tools you have learned up to this point (such as Normalization steps, ON DELETE CASCADE, etc.) to review and fix your ERD and Schema. The application of these concepts will be graded your Final Project submission.

2. Fixes based on Previous Step Feedback:

   This section of your PDF should detail everything you were told to fix in the feedback from the grader and peer reviewers in previous steps, as well as how you fixed it. If you chose not to fix things based on the feedback, this section would explain why you believe they should remain as they are. Please mention if you did not receive feedback from a grader or reviewer on a specific step.

   In addition, if you are making any adjustments due to your own revised design choices, they should be listed here along with the justification.

URL of an index.html page

You must submit a URL for an index page that links to all the other static pages in your website (e.g. http://flip2.engr.oregonstate.edu:9124 —or— https://web.engr.oregonstate.edu/~yourONID/cs340/index.html) with a short but informative title for each page such as "Browse existing ships", "Add new ship", "Manage certifications for each character".

These are the static HTML pages that the user will interact with when visiting your Project Website. Your HTML pages should be the front-end implementation for all of the CS340 Project Guide's functionalities. These can be plain old HTML, or you can build them using the templating engine of the web technology you choose (e.g. handlebars files), as long as each page is browsable and contains the CRUD forms you intend to implement.

So, basically, you need to create the client-side version of your site, which includes any forms, any client-side validation that you want to perform (though it is not required), and any images, etc. that you want to be included in the website. As a result, the project website is operational without any back-end or database interaction.

You can simply deploy this website inside the public_html directory on the flip server, or you can create a server running with a port number, as you did in Activity 2 - Connect webapp to database (Individual). Keep in mind that the public_html servers are running outdated versions of Node.JS and Python, which students are unable to update. So, anything other than HTML (e.g. a templating engine such as handlebars, jinga or react) requires a server running with a port number on one of the flip servers, just like what you did in Activity 2 - Connect webapp to database.

This URL must be included on the first page of your PDF as well as in your discussion post.

For a sample website built with the BSG database, I would submit the URL to an index.html page that lists all of the other pages on the site, along with a brief description of what functionality is available on each page. Here is an example of how those files might look.

The Data Manipulation Queries should be contained in a single .SQL file:

These are the queries used by your website to allow users to interact with data. SELECT, INSERT, UPDATE, and DELETE queries are required to provide the functionalities described in the CS340 Project Guide.

Anything that is a variable that you expect the user to fill or that is computed by your back-end code and passed to MySQL/MariaDB should be surrounded by special characters. This variable is commonly used by your back-end code to send data to the SQL server. You can choose from a variety of special characters. Simply add a SQL comment (as shown below) indicating which characters will be used to denote the sample part.

The following query, for example, can be found in bsg_sample_data_manipulation_queries.sql.

-- Query for add a new character functionality with colon : character being used to 
-- denote the variables that will have data from the backend programming language

INSERT INTO bsg_people (fname, lname, homeworld, age)
VALUES (:fnameInput, :lnameInput, :homeworld_id_from_dropdown_Input, :ageInput);

Here, :fnameInput denotes the variable that will contain the value of First Name when the NodeJS code processes it. Again, you are free to use any method of denoting these variables.

We only want to see SQL in this case. Include no code from JavaScript, PHP, or any other programming language used to process the data.

Except for the variable part described above, all queries should be syntactically correct and relevant to other parts of your Project and the version of Data Definition Queries that you submit here. This means that if the variable names are replaced with actual data, all of your queries should work.

How do I turn in this assignment?

1. Create a post in Ed Discussion called Group XX Project Step 3 Draft. Project Step 3 Drafts & Reviews is the category for your posting. Communicate with your team to find out who is submitting the draft. Include both members' names as well as your Group number.
   1. Post the deliverables described (the ZIP file and the URL of your website) in the Discussions section of the Project Step 3 Draft Version Ed Discussion post.
2. After that, copy the URL of your Ed post and paste it into Canvas. For the group, only one teammate must submit.
3. Each team member should then review two additional drafts — reviews are an important part of the process because you are helping others while learning. Make an effort to make thoughtful and substantial suggestions.

How should I get started?

I recommend creating a list of all CS340 Project functionalities that are relevant to your database. "Add entries to each table," for example, could mean "Add a new BSG person," "Add a new Certification," and so on.

Then, create a prototype of how you want your HTML page to look. An "Add new BSG person" page, for example, would include fields for first and last name, a dropdown for Homeworld, and an age field. Also, keep in mind that you do not have to implement all of your functionalities on separate pages. On the page that lists all bsg_people, I can have a delete button for each entry.

Instructions

This is your database design, created with the design tools and strategies you've learned so far. You will apply the normalization process to your design, create a schema, and write SQL to define the tables as well as insert sample data.

Details

A ZIP archive containing TWO THINGS as detailed below.

The zip file should be named something like​ projectgroupX_stepX_DRAFT/FINAL.zip. e.g. ProjectGroup42_Step2_DRAFT.zip

1. A PDF file containing all of the items from #1 and #2, as well as a schema diagram that should correspond to the (updated) database outline and ER diagram, as well as the example data from your database.
2. The .SQL file containing your Data Definition Queries (e.g. DDL.sql) and sample data INSERT statements.
   Your .ZIP file, as it should be posted to the Ed Discussion (please see "How do I turn in this assignment" below for instructions).

Both members' names and Group number should be included in the files as well as a comment on the post.

When working on this Step, it is expected that you apply all of the concepts you have learned thus far.

The PDF file should include

1. Fixes based on #1 and #2 feedback:

   This section of your PDF should detail everything you were told to fix in any feedback from your peers and the TA/instructors on #1 and #2, as well as how you fixed them. If you chose not to make changes based on the feedback, this section would explain why you believe the design should remain unchanged. Aside from that, any changes made as a result of your changed design decisions should be listed here.

   If you haven't received any feedback, state that you haven't yet received any feedback and skip this section.

2. Project and Database Outlines - Revised Version:

   This section should include an updated version of your #2 submission based on the feedback, as well as any design decisions you made on your own. For example, when applying the normalization process to your design, you may discover that your schema is not in 2NF or 3NF and that changes are required. These modifications must be reflected in the revised outline and ERD.

   If you haven't received any feedback, state that you haven't yet received any feedback and skip this section.

3. Entity-Relationship Diagram:

   This is an updated ER diagram that corresponds to your database outline. Anything that does not match the Database outline or uses incorrect notation will result in a point deduction in the #5 and #12 of this assignment.

   Note that you do not need to include all attributes in the ERD—just the primary key is sufficient. However, you must include all of them in the schema.

4. Schema:

   Apply the normalization process described in Exploration - Normalization Steps to your design. Begin by creating a sample report (e.g. in Excel) with some data and searching for redundancies and anomalies. Once your design meets 1NF and 2NF, look for any remaining transitive dependencies and decide whether to proceed to 3NF (not all transitive dependencies must be removed, only those that clearly cause data integrity issues). Once your schema has been successfully normalized, make sure to update the overview, outline and the ER diagram to match exactly. In the Final Version, you will be graded on the extent to which the schema matches the rest of your report, with an emphasis on whether the relationships, tables, and keys are correctly set up. You can either use an automated schema generator, such as the diagram visualizer in PhPMyAdmin or MySQL Workbench, or draw by hand and upload a legible scanned copy. Please adhere to the notation discussed in class. Diagrams should be easy to read (not too small or with too much wasted space), and care should be taken to avoid diagram line crossings.

5. Paste all of the example data from each table in your schema into your report. Indicate which table the data is being displayed from.

   Approximately 3-5 rows of data per table are sufficient to demonstrate how the table works; e.g., foreign keys demonstrate 1:M relationships in action, while intersection tables demonstrate M:M relationships in action. You are free to make up your own data, but it must be polite values that accurately represent the database in use. You can also obtain data from other sources, such as a sample report on a website, but please anonymize so that personal identifiers are not revealed.

Data Definition Queries should include

1. A DDL.sql file that should be cleanly importable on the database server hosting your CS340 database.

   If the .SQL file cannot be imported or does not create tables as described in your Schema, using PHPMyAdmin or the MySQL command line client, you will lose valuable feedback from your reviewers and lose points on subsequent assignments (e.g. #5 and #12).

   • SQL files that are well-structured, commented, and hand-written are preferred. MySQL dump exports with team-authored comments at the top of the file are also acceptable. This file is an example of this type: bsg_db.sql, which was generated with the mysqldump tool, as described in Activity 2 - Backing up and Restoring your Database.
   • The SQL should exactly match the report's schema.
   • All of the example data in your report must be included the .SQL file via INSERT statements.
   • Tables should have a primary key and foreign keys defined, and the appropriate fields should be referenced.
   • To handle any instances of the PK value changed as a foreign key in another table, it is recommended to use CASCADE methods as described in Exploration - MySQL Cascade.
   • To minimize import errors, disable commits and foreign key checks at the beginning of your file and then enable them at the end, as described in the MySQL documentation.
   • For example:

SET FOREIGN_KEY_CHECKS=0;
SET AUTOCOMMIT = 0;

... [your SQL goes here]
SET FOREIGN_KEY_CHECKS=1;
COMMIT;

• To minimize import errors, it is recommended that you use DROP TABLE IF EXISTS before any create table statements, or else use the CREATE OR REPLACE TABLE statement as described in Activity 1.

How do I turn in this assignment?

Follow the steps below to turn in your Project Step Draft and review the work of others:

1. Create a post in Ed Discussion called Group XX Project Step 2 Draft. Project Step 2 Drafts & Reviews is the category for your posting. Communicate with your team to find out who is submitting the draft. Include both members' names as well as your Group number.
2. After that, copy the URL of your Ed post and paste it into Canvas.
3. Each team member should then review two additional drafts — reviews are an important part of the process because you are helping others while learning. Make an effort to make thoughtful and substantial suggestions.

Executive Summary

• 1 page (maximum) summary of feedback and changes made to this Project from its inception to the present.
• Please keep in mind that the summary differs from the previous steps, which required you to copy and paste feedback and your response. We do not want to see any feedback or responses from previous steps. Just a synopsis.
• The executive summary contains information about the database design process and the implementation steps. It reflects on major changes to this project from your initial proposal to the present, as well as how feedback influenced those changes.
  Consider the executive summary to be the only thing your boss will read.
• The executive summary should be at the top of the PDF that you submit with the .ZIP file.

Project and Database Outlines

• The Project and Database Outlines have been updated. Reading these should provide a layperson with a complete understanding of your Project and its universe, allowing them to navigate the website.

ER Diagram

• When compared to the Schema, the updated version of your ERD using the notations we use in class should represent a logical model of your design and omit some physical level of detail. The diagram should be clear and easy to read, with no crossed lines.

Schema

• The most recent version of the schema, which should exactly match the database outline. It will be graded again based on how closely it matches the outline, with an emphasis on whether relationships and keys are correctly set up. Please remember to use the notations that we use in class. The diagram should be clear and easy to read, with no crossed lines.

Sample Data

• Paste 3-5 rows of example data from each table in your schema into your report. Indicate which table the data is being displayed from. Data should be polite values that reasonably represent how the table works, e.g., foreign keys demonstrate 1:M relationships in action, and intersection tables demonstrate M:M relationships in action.

UI Screen Shots with Informative Titles

• Screenshots of each of your website's user interface pages. Add a title directly above each Screen capture that describes the CRUD step. "READ/BROWSE/DISPLAY Customers page," for example, or "CREATE/INSERT/ADD NEW Invoice page." Captures should be clear and simple to read. Highlight where you've used delete from a M:N, NULLable relationships, and M:N updates. You will lose points if you do not highlight these and we overlook them when grading, even if you have implemented them.

Data Definition Queries

• The updated SQL file that you provide should be cleanly importable on the database Server that hosts your CS340 database. Add comments at the top of the DDL file to describe it and make it clear that it corresponds to the CS340 Portfolio Project deliverables. You will lose points if your .SQL file cannot be imported or does not create tables as described in your schema using PHPMyAdmin or the MySQL command line client. SQL files that are well-structured, commented, and hand-written are preferable. MySQL exports with team-authored comments are acceptable.

Data Manipulation Queries

• The updated SQL file that contains all of your DML queries in the format described in #6. These queries should match those in your server-side code. Include brief comments describing the functionality/entity/relationship to which the query relates.

Website Functionality

All of the functionalities described in the CS340 Project Guide must be implemented and evaluated using the rubric.

• You cannot expect the user to manually type any data when dealing with relationships. Allow the user to select items to relate to each other using drop-down menus or another UI element where the user selects from existing items to add them to a relationship. When selecting items, show the name that makes the most sense to the user. This is almost certainly not the primary key. In the context of the BSG database, for example, when adding a row to the relation between bsg_people and bsg_cert, we would expect bsg_cert.title values to be displayed in a dropdown/set of radio buttons rather than allowing entry of bsg_cert.id.
• The URL of the website should be included in the PDF as well as a comment on the submission.
• READ/BROWSE/DISPLAY entities
  CREATE/INSERT/ADD NEW entites
  DELETE functionality (one M:N) clearly indicate where you have implemented the delete of a M:N
EDIT/UPDATE functionality (one nullable FK, update one M:N relationship) Clearly indicate where you have implemented update of one M:N relationship and NULLable relationship.
  DYNAMIC DROPDOWN functionality for FKs

Style

Website

• The site's entity organization and CRUD operations should be logical and usable. We anticipate data to be displayed in a tabular format, with form elements reasonably grouped. Section headers for sections of your pages/forms would undoubtedly make your site more navigable. Any design elements should help the user understand and find any CRUD elements. The site navigation should not be confusing. White space, colors, fonts, graphic elements, and/or alignment can help organize material, but they must be consistent and not distract from the content. Avoid grammatical, capitalization, punctuation, and spelling errors. Good quality design includes using html date input for dates and highlighting required fields.

PDF

• Your documentation write-up should be well formatted and sectioned for easy navigation and readability.

Code

• The project code should be organized logically, with self-documenting folders and file names. Instead of large monolithic code blocks, server-side code should be divided into manageable blocks/files. Make use of meaningful variable names and provide brief comments to explain logic.

Citations

• Any non-original code should be accompanied by a citation that clearly credits the source. I.e., if you modified the CS340 starter app code, it should be clearly stated in the readme (e.g., "All code is based on the CS 340 starter code, with the exception of...") and also in the source code to describe the extent to which any non-original sources were used (e.g., "this module adapted from the starter code"). Emphasize your original work (for example, "update M:N entirely our own work"). HTML and JavaScript files should include comments and be structured in a way that promotes clarity and readability.

Points will be deducted if you do not include the source code. Code that does not include citations may be flagged for plagiarism and referred to the office of student conduct.

Instructions

You will implement MOST (roughly two-thirds or more) of the remaining CRUD steps for entities in your web app as described in the CS340 Project Guide in this step. It is entirely up to you how far you progress because you will have another week to complete the project. Best case scenario, your group completes all CRUD functionality required, allowing you to focus on styling and documentation the following week.

What should I submit for this assignment?

1. Post the deliverables described below (the ZIP file and the URL to your website) in the Discussions section of the Project Step 4 Draft Version Ed Discussion post. Only one member of the group should post it.

   This draft post will differ slightly from previous steps. Please include the following with your deliverables (the ZIP file and the URL of your website):

   1. What technology you are using (e.g. NodeJS/Flask Python).
   2. What is working.
   3. What is not working.
   4. Where and/or why are you blocked?

   The post can be very brief—just enough to let us know how you are doing and how we can help. If you have completed all of the requirements for this step, please respond by saying so and let us know if you would like feedback or suggestions.

2. Copy the URL of your Ed post and paste it into Canvas. For the Group, only one teammate must submit.

3. Each team member should then review two additional drafts — reviews are an important part of the process because you are helping others while learning. Make an effort to make thoughtful and substantial suggestions.

Deliverable (TWO):

1. An index.html URL that links to your website's pages that implement the CRUD functionalities. Put this URL on the first page of the PDF file as well.

2. A ZIP archive containing these files

   • A .PDF document.
   • A .SQL file that contains Data Definition Queries.
   • A .SQL file with Data Manipulation Queries.
   • The current version of your project's code.

   Make sure your zip file is named ​projectgroupX_stepX_DRAFT/FINAL.zip, e.g. ProjectGroup42_Step5_DRAFT.zip.

Details

1. URL to an index.html
   You must submit the URL of an index page that links to pages on your website that implement the CRUD step.

   We expect that MOST OF the pages will function properly at this point.

   For example, if you have 5 tables in your schema and intend to implement 5 SELECTs, 5 INSERTs, 1 UPDATE (+1 NULLable relationship), 1 DELETE (M:M), and 1 Dynamic drop-down/Search for a total of 14 functions, we anticipate that approximately 10 of those will be completed.

   This URL must be included on the first page of your PDF.

2. PDF File

   1. Updated Project Outline and Database Outline, ERD, Schema, and Sample Data
      This section should include the revised outline, ERD, Schema, and sample data based on feedback from the grader and peers, as well as any design decisions you made on your own. It is required that you use the various design tools you have learned up to this point (such as Normalization steps, ON DELETE CASCADE, and so on) to review and fix your ERD and Schema. The application of these concepts will be graded in your Final Project submission.

   2. Fixes based on Previous Step Feedback
      This section of your PDF should detail everything you were told to fix in the feedback from the grader and peer reviewers in previous steps, as well as how you fixed it. If you chose not to fix things based on the feedback, this section would explain why you believe they should remain as they are. Please mention if you did not receive feedback from a grader or reviewer on a specific step.

3. Data Definition Queries in a .SQL File
   In a separate SQL file, submit an updated version of the Data Definition Queries you submitted in the previous Step, with fixes based on any feedback (which you are required to list and describe in the PDF section above). As with the previous step, this file should be easily importable using PHPMyAdmin on the college's database server. Though not required, it is recommended that you review and fix your Data Definition Queries using the various concepts you learn in this module, such as Database Normalization. Your application of these concepts will be graded in your Final Project submission.

4. Data Manipulation Queries in a .SQL File
   In a separate SQL file, submit an updated version of the DML queries you submitted in the previous Step, with fixes based on any feedback (which you are required to list and describe in the PDF section above).

5. Current version of your project's code.
   We only need the HTML and server-side code needed for your project. Please do not include common library folders as this will result in a very large zip file. It is expected that your project is well-organized, with self-documenting folders and file names. Your programming code should contain meaningful variable names as well as brief comments that explain logic. Any non-original code should be accompanied by a citation that clearly credits the source.

Instructions

As a Group, you turn in the Final Version of the Step 1 Draft: Project Proposal, Outline, ERD #1. It is expected that you have made changes to your submission in response to the peer review(s) you received for the Draft version.

Along with everything described in the Project Step 1 Draft (Group, on Ed Discussions), you must also include the following sections at the top of your PDF file:

The peer reviewer's feedback

Include any Group Review reviews that your Draft submission received verbatim. Mention that you did not receive any reviews. You would incorporate feedback from both Project Group members.

Actions taken in response to feedback

List briefly the actions you chose to take in response to the above feedback. If you decided not to act on a specific suggestion, you must explain why in detail.

Improvements to the Draft version

If you are making changes to the files as a result of your own design decisions, they should be listed in this section.

How do I try to complete this Step's final version?

Use the feedback from your classmates to improve your Draft version. And, as you learn more about database design patterns this week, try to apply them and normalize your database to avoid common problems associated with poor design (e.g. data redundancy, and data anomalies, as discussed in Exploration - Database Design Patterns. Remember this simplified view of normalization: each table represents a single theme, has a PK and no attribute is determined by another non-key attribute. You can continue to use the Project Review group to solicit feedback and bounce ideas off your classmates. Finally, if you have any questions, please post them on Ed Discussion or ask them during TA or Instructor office hours.