Project Proposal and Design Assignment

Activity Content:

Instructions

1. Project Goal
   • Design and implement a relational database for a real-world scenario. This will allow you to apply the concepts learned in class (data modeling, SQL, normalization, etc.) in a practical setting.

2. Project Phases
   • Phase 1: Requirements Gathering and Conceptual Design
     • Choose a Scenario: Scenarios should be relatable and allow for a database with at least 5-7 tables in the final implementation.
       • Examples: Library system, online store, school management system, small clinic patient

records, sports team management, etc.

• Describe your scenario using 1 paragraph of text.

• Gather Requirements: Identify the information needs and how the database will be used. This

might involve:

• Creating user stories (e.g., “As a librarian, I want to be able to search for books by title or author so I can quickly find a book for a patron.”)
• Interviewing potential “users” (even if they are hypothetical)

• Conceptual Design:
  • Develop an Entity-Relationship Diagram (ERD) to model the data.
  • Clearly define entities, attributes, relationships, and cardinalities.
  • Document any assumptions made about the data.
  • Produce a high-level ERD using Mermaid. https://mermaid.live/
  • Copy and paste your Mermaid code with your assignment submission.

• Phase 2: Logical Design and Normalization
  • Logical Design:
    • Translate the ERD into a relational schema.
    • Define tables, columns, data types, primary keys, and foreign keys.

• Normalization:
  • Apply normalization rules (up to 3NF) to minimize data redundancy and improve data

integrity.

• Justify normalization choices and explain the potential issues avoided.
• Produce a logical ERD at the table level including tables, attributes, types, PK/FK designations using Mermaid. Copy and paste your Mermaid code with your assignment submission.

The above 3 highlighted items are as the submission for this assignment (20% of overall project).

• Phase 3: Physical Design and Implementation
  • Create the Database:
    • Use SQL (DDL) or phpMyAdmin to create the database schema (tables and constraints).
    • Populate the database with sample data (at least 5 records per table).

• Implement Queries:
  • Develop SQL queries (DML) to perform various operations:
    • Retrieving data (SELECT with different clauses like WHERE, ORDER BY, JOIN, subqueries, aggregates).
    • Inserting, updating, and deleting data (INSERT, UPDATE, DELETE).

• Phase 4: Front-end Applications
  • Either using PHP or AppSheet, create one more front-end applications that make use of your database. Be sure the apps cover the following:
    • A feature that allows the user to insert new data into the database.
    • A feature that allows the user to supply some input to query and/or filter the data contained in the database in some way

• Phase 5: Documentation
  • Project Report: Write a brief report documenting your design choices and challenges faced

4. Assessment
   • ERD and Relational Schema: Accuracy, completeness, adherence to design principles.
   • Normalization: Correct application of normalization rules, justification of choices.
   • SQL Implementation: Correctness of SQL code, efficiency of queries, handling of constraints.
   • Project Report: Clarity, organization, completeness of documentation.

The final deadline for the entire project is the last day of classes,

Tue. (80% of overall project).