Part 1 (100 points):

Take any scenario where you might deploy a database- at home, work, or school.  The following are required for your project

Describe the scenario completely using 2-3 paragraphs.  The scenario should describe the entities, any relationship between the entities, and also the participation and cardinality (min, max) constraints.  You should have a minimum of 5 entities in your problem scenario. (PPT/MSWord document).  Make sure your scenario is reasonable  and unique (not similar to the ones described in class – 50 points will be taken off)

Part 2 (250 points) :  Use your work from Part 1 and continue on as below

1. Construct an ER diagram for the scenario from (1).  Translate the ER diagram to a relational database structure (tables)  (PPT/MSWord document)

2. Implement the relational database structure from (2) in SQLServer

3. Add data to your entities and relationships- 7-10 records per table.  Make sure that the data looks real, e.x,, do not set a persons first name as ‘As809fsl’  or Department as “098sdfoj’- instead have first name as ‘Mary’ or ‘Tom’, … and Department as ‘Sales’ or ‘Marketing’ etc.  Note that you are free to choose names (you do not have to use ‘Mary’ or ‘Tom’ or ‘Sales’ or ‘Marketing’ 

4. Formulate 10 SQL queries to ask your database system.  The queries should include

a. Single Table Queries

b. Join Queries

c. Sub-Queries

d. Aggregate Queries (using Group By and  Group By/Having) on Single Tables, and Multiple Tables

e. Set Valued Queries (Union, Difference, Intersection)

The outputs should be neatly printed, with readable column headers

5.  Read and understand Triggers in SQLServer.  Describe (add to your document) 3 triggers that you would set for your database.  Implement the triggers in SQLServer and clearly show the effect of the triggers through your screenshots

Upload your entire document into Canvas, including problem scenario, ER Diagram, Table Structure and Data, SQL Queries, SQL Triggers, and the output of the SQL Queries/SQL Triggers.