INFO639 Final Project Solution

Description of Topic
The final project is designed to demonstrate your mastery of technologies introduced in class. The project involves designing a complete database management system to address a practical database need and implementing a relational database in MySQL based on that design. Your database system should be designed to perform general information management tasks such as systematic collection, update, and retrieval of information for a small organization (e.g. a record or book store, an archive, digital library, research lab, museum, non-profit, etc.) The final project will consist of four deliverables, described in detail below:
Final Project Proposal Week 6 10%
Final Project Design Week 10 20%
Final Project Implementation Week 15 25%
Final Project Demo Week 15 5%
1. Final Project Proposal [10pts]
Length & Requirements: 2-3 pages (double spaced, font size 10-12), submitted as one Word/PDF/RTF file
In your proposal, you should:
1. Name your project
2. Briefly describe the purpose of your database in a few sentences
3. Explain who the users of your database are and their information needs. You should consider what questions the users might need to answer using your database.
4. Explain the problems your database can solve.
5. Describe the input data that is available to the database and what kind of information should be stored.
Rubric for Proposal
A/Excellent [10-9pts] B/Above Average [8pts] C/Poor
[7-0pts]
Purpose and Topic The writer clearly describes the project's purpose and generally displays evidence of thoughtful analysis. The writer somewhat describes the project's purpose but analysis is vague. The writer provides little or no description of the topic they wish to address.
2. Final Project Design [20pts]
Length & Requirements: One Word/PDF/RTF document, including all required narrative and diagrams
The design portion of your final project will describe the steps taken during the conceptual and logical design of your database. Your final project design should include the following items, completed in the order listed:
1. A list of business rules
2. An initial conceptual data model including the important entities and the relationships among them, using Crow's Foot Notation. The diagram should be created using diagramming software.
5. A one to two paragraph summary of any changes made between the proposal, conceptual, and logical design stages and your rationale for doing so.
Rubric for Final Project Design
A/Excellent [20-18pts] B/Average [17-16pts] C or below/Poor [15-0pts]
Business rules Business rules are highly thorough, complete, and accurately support the users' needs. Business rules are generally thorough and complete, while supporting the users' needs. Business rules are missing, inaccurate and/or fail to align with the users' needs.
Conceptual data model Conceptual data model is appropriately high-level, identifies all important entities and relationships and adheres to
Crow's Foot Notation standards. Conceptual data model mostly identifies all important entities and relationships and generally adheres to Crow's Foot Notation standards. Conceptual data model fails to identify all important entities and relationships and somewhat adheres to Crow's Foot Notation standards.
Logical data model Logical data model is highly and accurately detailed, including all entities, attributes, relationships and primary/foreign keys. Design is appropriately normalized. Diagram adheres to Crow's Foot Notation standards. Logical data model is generally detailed, including all entities, attributes, relationships and primary/foreign keys. Design is appropriately normalized. Diagram adheres to Crow's Foot Notation standards. Logical data model is missing significant detail and is not appropriately normalized.
Diagram somewhat adheres to
Crow's Foot Notation standards.
Tasks and rationale Tasks are detailed and closely aligned with the user needs identified in the proposal. Any major or minor changes to the design or project scope are described with appropriate rationale. Tasks are generally detailed and aligned with the user needs identified in the proposal. Any major or minor changes to the design or project scope are described with mostly appropriate rationale. Tasks are missing and/or fail to align with the user needs identified in the proposal. Major or minor changes to the design and scope are made without appropriate rationale.
3. Final Project Implementation [25pts]
Length & Requirements: One zip file containing 1) a Word/PDF/RTF document containing your SQL queries and 2) a .sql file dump of your final database
The implementation portion of your final project will consist of the physical design and implementation of your database. For your final project submission you should include the following:
1. A Word/PDF/RTF document containing:
b. Sample queries supporting the users' tasks you identified in the previous stages of the project. You should also include queries to demonstrate the following (at a minimum), if they are not covered in your users' task queries:
i. Insert data into the database
ii. Delete data from your database
iii. Update existing data in your database iv. Join two or more tables
v. A view that satisfies one of your user needs
vi. A transaction
vii. A trigger
viii. A query with a subquery
c. One to two paragraph summary of any changes made between the design and implementation stages and your rationale for doing so
2. A .sql file dump of your entire database (including structure, data, and triggers) - Instructions for exporting, i.e.
dumping, your database are in the "Importing and Exporting Your Database" file in Week 8 on the LMS.
a. Your database should include sample data sufficient to demonstrate its use (at least 20 rows per table)
3. Create one zip file containing both files and submit on the LMS.

Rubric for Final Project Implementation
A/Excellent B/ Average C or below/Poor
Physical design Physical design is highly detailed and correct, including all tables,
columns, data types, relationships and primary/foreign keys. Physical design clearly builds on logical design. Diagram adheres to
Crow's Foot Notation standards. Physical design is detailed and generally correct, including all tables, columns, data types, relationships and primary/foreign keys. Physical design builds on logical design. Diagram adheres to Crow's Foot Notation standards. Physical design is lacking in necessary detail and/or does not clearly build on logical design. Diagram does not fully adhere to Crow's Foot Notation standards.
Implementation The queries provided accurately and correctly create a database matching the physical design, with no errors. The queries provided generally accurately and correctly create a database matching the physical design, with little error. The queries provided fail to create a database matching the physical design, with significant error.
Sample SQL Queries Queries provided meet and exceed the basic requirements in number and complexity. All queries run without error. Queries provided meet the basic requirements in number and/or complexity. Most queries run without error. Queries provided do not meet the basic requirements in number and/or complexity. Few or no queries run without error.
Data Sample data sufficient to demonstrate the database's use is included, going well beyond the minimum requirement. Sample data sufficient to demonstrate the database's use is included. Sample data is missing or lacking in volume.
Rationale Any major or minor changes to the design or project scope are described with appropriate rationale, using correct technical terminology. Any major or minor changes to the design or project scope are generally described with appropriate rationale, using mostly correct technical terminology. Major or minor changes to the design and scope are made without appropriate rationale, and/or using incorrect technical terminology.

4. Final Project Demo [5pts]
Length & Requirements: 10 minutes maximum in length, with a maximum of 10 slides or other visuals, e.g. screenshots.
In your presentation, you should briefly:
• Explain the purpose of your database, your users' needs, and the problem you are solving.
• Describe your design process and present supporting materials, e.g. ERD diagrams, etc.
• Showcase your database implementation, which could include showing data entered in tables, running various queries, etc. Highlight the features that support the user needs you identified.
• Discuss any difficulties encountered during your project and what you would like to do next, if you were given more time

Rubric for Final Project Demo
A/Excellent B/Average C/Poor

• MS Library & Information Science: Portfolio - http://bit.ly/prattmslisportfolio
• MS Information Experience Design: Portfolio - http://bit.ly/prattmsixdportfolio
• MS Data Analytics and Visualization: Portfolio - http://bit.ly/prattmsdavportfolio
• MS Museums and Digital Culture: Portfolio - http://bit.ly/prattmsmdcportfolio