LIS639 Lab 3: Database Implementation (15pts) Solution

This lab will give you experience using data definition language (DDL) to define and change the structures in a database, as well as with data manipulation language (DML) to add, modify and query the data stored.
Submission Instructions
For this assignment you will be submitting one SQL file named lab3.sql that contains the SQL commands necessary to create the database in Problem 1 and run the queries in Problem 2. Your .sql file should include commented lines to note which question the query is a response to, e.g.:
-- Question 1

CREATE…etc.
-- Question 2.1

SELECT...etc.

Submit your lab3.sql file to the LMS using the "Lab #3: Database Implementation" assignment link.


Problem #1 [7.5pts]: Database Creation
Given the following entity-relationship diagram, create a MySQL database using SQL. Your SQL queries should faithfully implement all the tables, attributes, and relationships (including PK and FKs and the necessary constraints) expressed in the ERD. Make sure to take note of whether relationships are optional or mandatory and support these details in your implemented database. Also, consider what referential actions should take place when primary key values are modified and/or deleted. List all the queries necessary to create your database in your lab3.sql answer file.


Problem #2 [7.5pts]: Queries
Using the database that you created in the previous step, write SQL queries to complete the following questions. List your queries in your lab3.sql file with a comment indicating the question number.
2.2 – Write a query that changes the primary key value of one record within a parent table in a relationship. What is the impact on any related foreign keys and why? Place your explanation within a multi-line comment, e.g. /* your comment here */
2.3 – Write a query to delete one row from a parent table in a relationship. What is the impact on any related foreign keys and why? Place your explanation within a multi-line comment, e.g. /* your comment here */
2.4 – Write a query to add an hourly rate column to the contract table with a default value of 50.00
2.6 – Write a query to provide the following output, with "Estimated Contract Cost" calculated by multiplying each contract's hourly rate by its number of estimated hours. Note – your exact output will vary based on whatever sample data you entered.
+-----------------+-------------------------+ | Contract Number | Estimated Contract Cost | +-----------------+-------------------------+ | 1 | $2400.00 |
| 2 | $2000.00 | | 3 | $2600.00 | | 4 | $1000.00 | +-----------------+-------------------------+

2.7 – Write a query to determine and output the contract id and the number of days each contract took, for the contracts that have been completed, i.e. those that have an end_date. Note – your exact output will vary based on whatever sample data you entered.
+-------------+----------------+ | Contract ID | Number of days | +-------------+----------------+ | 1 | 6 | | 3 | 4 | | 4 | 11 |
+-------------+----------------+
2.8 – Re-write your query from the previous question 2.7 to include contracts with no end_date; instead of outputting NULL for the number of days, output the text "Still in progress", Note – your exact output will vary based on whatever sample data you entered. e.g.:
+-------------+-------------------+ | Contract ID | Number of days | +-------------+-------------------+ | 1 | 6 |
| 2 | Still in progress | | 3 | 4 |
| 4 | 11 |
+-------------+-------------------+

2.9 – Write a query to count the number of contracts that were completed within the last 30 days, .
Note – your exact output will vary based on whatever sample data you entered. e.g.
+---------------------------------------+ | # Contracts Completed In Last 30 Days | +---------------------------------------+
| 3 |
+---------------------------------------+
2.10 – Write a query to output each owner's number of contracts and total estimated cost of their contracts, i.e. the estimated costs of all their contracts added up. Note – your exact output will vary based on whatever sample data you entered. For example:
+------------------+-------------+--------------------------------+ | Billing Owner ID | # Contracts | Estimated Total Contracts Cost | +------------------+-------------+--------------------------------+
| 1 | 2 | $4400.00 |
| 2 | 1 | $750.00 |
| 3 | 1 | $1500.00 | +------------------+-------------+--------------------------------+