Comm 3220: DM4DM

Goal

Learning about normalized and de-normalized (i.e., 3NF) database design.

Learning how to create tables and views, and insert and update records.

Learning how to create views.

Task

You really like the new job at Pressly Ad.  A nice change of industry after the work at the Bank.  You heard some rumors about the failure at creating the first DB for the company and expect that the project will be given to you.
The CIO approaches you at one of the high tables:
"Welcome to Pressly! I am so glad that you joined us. Did you hear about the DB project? The last consultant we hired was a DISASTER!  I told HR to hire from McIntire, but no! They wanted to save money and hired somebody from some other school…. BIG MISTAKE!  The DB she built was just a dump of the Excel spreadsheet we use to track campaigns. Nothing worked the way it was supposed to.  What a waste of money!"
"I see… just copying an Excel spreadsheet into a DB is not a good practice."
"I would use stronger words than ‘not a good practice’.... Well… I want you to fix it."
"I can do that. I know about good DB design."
"BUT I also want to preserve the old spreadsheet view. Too many employees are confused by how DBs work. They do not know SQL, they cannot join tables… that’s complicated stuff. The managers want to see our data in the old format, as a single table of information."
"No problem, boss. I am on it...."

The Agency CIO wants a well-designed database to store their campaign data, but she also wants a 'big table' view that contains all the info that her employees are used to see in their Excel spreadsheets.  In other words, she wants her cake and eat it too. Good thing you know about views!

Create an ADS notebook that contains the queries necessary to create and populate the tables and the view described below. Post the notebook by the due date in your BOX folder. Filename for the notebook is "MP6 YourLastname YourFirstName.ipynb"
Use the COMM3220 database. Same server and same password as for the previous DB.

Criteria

Your SQL must produce the correct tables and views, populate them with the correct data, and drop them as needed.
For all tables and views use the extended names that includes your userid, e.g., fkg2s.AD_CAMPAIGN.

The Pressly AD Agency DB

In class, we normalized the table below, which contains campaign data for Pressly.  We split that bigger table into several normalized tables.  These are examples of how to create tables and views. The W3 site has additional examples and info on create table, create view, insert into, update, and delete records.

Follow these steps:

1) Normalize the table below (3NF). See the deck for the class that covered Normalization.

2) Use the COMM3220 database.  Write in the notebook SQL statements that create the normalized tables. Pick data types that make sense. Include all PKs and FKs as needed.   For all tables and views use the extended names that includes your userid, e.g., fkg2s.AD_CAMPAIGN.

3) Write SQL statements to fill the tables with the same data as in the figure.
4) Add a query that changes the name of  'John' to 'Olaf'.
5) Create a view called BIG_TABLE that joins the smaller tables and reproduces the denormalized big table as in the figure below, except that 'John' will now be 'Olaf'. Same column names (use alias if necessary), same column order, same rows order, same formats.

6) Write a Select * for the view

7) Write DROPs that delete the tables created in (2) and the view from (5) if they exist. Place this code at the top of your notebook, before the CREATEs (this is for the TA to grade). Make sure the DROPs work.  Order of drops matter: you cannot drop a table that has an active FK.

Note: in class I used a slightly different table and used Budget in millions rather than percentages. I did it to simplify the in-class exercise. For this assignment please visualize percentages as in the table below.

This exercise was inspired by the Jukic et al. book

Back home