Database Concepts

2020 midterm prof.docx

IT 240 Midterm Proficiency Exam Spring 2020 Page 2 of 2

NAME:_______________

Submit:

1. Your Word document for Parts 1 & 2 with documentation.

2. Your working DB with saved SQL query views.

Part 1

Access: make certain to submit the .accdb and NOT the locked DB (in other words, save and close the DB before submitting).

For the selected list of data (playlists_export.csv) included, you must:

1) (15 points) normalize the list into a multiple related tables design

2) (points for submission) build an MS-Access

Note: You must use SQL statements to create your tables.

You may use the Insert statement, or import Excel/CSV files, to populate your tables.

3) (10 points) validate data is built correctly, i.e., use both equi-join and inner-join test queries

Name these queries ‘equi-join’ and inner-join’ respectively.

4) (15 points) solve these specific analysis questions using SQL queries:

· top 3 artists (by playlist tracks; by played tracks);

· bottom 3 playlist tracks (by track duration or length);

· Best Album (by playlist tracks; by played tracks) 

5) Document your work in a single Word document with screenshots and descriptions

6) (15) Document your success by validating the DB works as intended, i.e. run (3) additional queries. Provide an explanation of the query (what you were attempting to accomplish), the code, and the results. Make certain you SAVE the query in the DB.

7) Do not encrypt or lock the database with username/password.

8) (25 points) Submit working database (including the saved sql queries) AND the Word document

Part 2 Queen Anne DB (use the DB provided)

T. (5 points) Given your assumptions about cascading deletions in your answer to part B of the Queen Anne case study, write the fewest number of DELETE statements possible to remove all the data in your database, but leave the table structures intact.

Do not run these statements if you are using an actual database!

If you decide to run the code, make a copy of the DB and use it to test your SQL statements.

U. (15 points) Chapter 2 discussed multivalued dependencies and the associated multivalue, multicolumn problem and how to resolve it (pages 96-98). Does the VENDOR table have the multivalue, multicolumn problem?

If so, use the discussion on pages 114-118 as the basis for solving it for the QACS database.

Create a new table named PHONE_NUMBER, link it to the VENDOR table, populate the PHONE_NUMBER table, and finally alter the VENDOR table to remove any unneeded columns. Hint: Read the additional discussion of the SQL ALTER TABLE statement in online Extension B, “Advanced SQL”.

Use the ALTER statement.

Do not run these SQL statements if you are using an actual database!

If you decide to run the code, make a copy of the DB and use it.

Write your SQL code here: