Assignment 4 -- OUTDATED

Question 1 (20 Points)

Based on the ontology constructed in the prior assignment, construct an entity-relationship model using the "Crow's Feet" notation in LucidChart that can support the following "use cases":

1. As a director, I want to know all of the projects that are currently ongoing at the organization.
2. As a director, I want to know the projects that are ongoing or have been completed for a particular client.
3. As a project manager, I want to know all the projects to which I have been assigned.
4. As a team member, I want to know the projects to which I'm assigned.
5. As a team member, I want to know the number of hours I am assigned to some project.
6. As a client, I want to see the project team that's working on one of my projects.
7. As a director, I want to know which projects are not presently on time or on budget.

Question 2 (10 Points)

Convert the entity-relationship model from question 1 to a relational database schema. Define the tables, attributes, and keys.

Question 3 (30 Points)

Implement the relational database schema in a database of your choice (Access, MySQL, SQLite) database. Define the relationships properly so that referential integrity is enforced. Be sure to define input masks and validation rules so that bad data does not enter the database (ensure data quality).

Question 4 (20 Points)

Input sample data for the database so that the queries and the structure can be sufficiently tested. Make sure that all primary key to foreign key relationships are properly implemented.

Question 5 (20 Points)

Create SQL queries that provide the information sought in the use cases of question 1.

Scoring

Total Number of Earnable Points: 100
Approximate Time to Complete: 3-4 hours
Due Date: see Calendar or Blackboard

Submit a report that contains screens shots of your E-R diagram, schema, Access relationships, sample data, query designs, and results of queries. In addition, upload your database file to Blackboard.