Database Management ER Diagram and SQL workbench.

  

Part 2: - Project Implementation – due Nov 20st @ 11:00 pm:

Report 2 Contents:

1) Project goals.

2) Database description: A general description of the database, what data is stored in the database.

How the database will benefit the users.

3) Data model & design:

o Completed ER/EER diagram; 8 tables 

 MUST use https://www.draw.io to draw the ER diagram, and then take a

snapshot.

 Include a snapshot of the diagram in the report.

o Business rules.

o Data dictionary: For each one of the tables, list columns, data types, column restrictions,

column descriptions.

4) Implementation:

o Use MySQL Server and MySQL Workbench to create the database/tables.

 Include a snapshot of the SQL code in your report

o Populate the each table with a minimum of 20 rows of sample data. Make sure the

entered data is descriptive (not just random numbers and letters.

 Include a snapshot of the SQL code in your report.

o Use MySQL Server and MySQL Workbench to write/run the SQL queries below. For

each query, provide the following in Report 2:

 Snapshot of the code and the output in the report.

 In about 2 lines, explain what the query returns.

Queries:

 1 trivial query. Simple select with ordering.

 2 medium difficulty queries. Queries that use composite condition for selection,

computations, aggregate function and grouping.

 1 query that uses subquery.

 2 queries that uses join (1 inner join, 1 left or right outer join).

 1 view (query must use join – hint: you can use one of the queries from the

previous question).

 1 query that uses union.

 1 custom stored function.

 1 custom stored procedure.