Fundamentals of Database Management Project Final Report

DB Project Final Requirements

50 Points

You are required to build a database with APEX based on the logical design result from Part 1; and create and run several SQL queries on the data stored in the database. In addition, you are also required to create an APEX application that showcase the database and data stored in it.

As documentation, you need to produce the following as print out and upload to Canvas as a single file.

1. (5 points) Title Page – Name of team/students and APEX User ID:

2. Logical design of the database: Updated ERD (NOT graded for final part, this was Part-I)

3. (10 points) SQL codes for creating all tables (with all PK and FK constraints specified)

4. (10 points) SQL codes for inserting at least 5 rows of data into each table (You must insert data for Consultant ID 100, Mark Meyers. You must also insert data for SKILL ID 1, 2, and 3)

5. (20 points, 2 points for each query) Create SQL queries for the following scenarios (even if you do not have data in your tables that will produce output, you must write the query correctly). Remember to save all the queries on APEX with names such as 4a, 4b, … 4j.

a. Retrieve the name of each city where a consultant lives. Suppress duplicate output and display the values in alphabetical order.

b. Retrieve the consultant id, skill id and certification status for every consultant who is proficient with skill ID 1, 2, or 3;

c. Retrieve the first and last names of those consultants whose last names begin with an "M";

d. Retrieve the last name of each consultant and the name of each project they work on (it's ok to have duplicate consultant names);

e. List the first and last name of every consultant who has worked with Mark Meyers (use a subquery);

f. Display each project id and the total number of hours that all consultants have spent on that project (only display data for projects that have accumulated over 100 hours);

g. Develop your own query that uses an outer join;

h. Develop your own query that uses a group by with the having clause;

i. Develop your own query that uses the LIKE keyword;

j. Develop your own query that uses the DISTINCT keyword.

6. (5 points) Overall neatness and clarity.

All SQL codes must be executed in APEX and the Team should be able to demonstrate correct execution if asked to do so.