MP6: Pressly Ad Agency

The first consultant was fired. Will you?

Goal

Learning about improving (normalizing and de-normalizing) DB design for efficiency and ease of use.
Learning how to create tables and views, and insert and update records.

Context

You really like your new job at Pressly Ad. It’s a nice change of industry after your work at the consulting firm. You’ve heard rumors about the company’s failed first database and expect the project to be given to you.  Sure enough, during your first week at the agency, the CIO approaches you at one of the high tables shared by the analysts.
“Welcome to Pressly! I’m so glad you joined us. Did you hear about the database project? The last consultants we hired were a DISASTER! I told HR to hire from UVA, but no, they wanted to save money and hired someone from some other college. BIG MISTAKE! Their database was just a dump of the Excel spreadsheet we use to track our campaigns. Nothing worked the way it was supposed to. What a waste of time and money!”
“I see. Copying a spreadsheet into a database without any data modeling is usually not good practice.”
“I’d use stronger words than ‘not good practice.’ Anyway, I want you to fix it.”
“I can do that. I know about good database design.”
“Great. BUT I also want you to preserve the old spreadsheet view. Many employees need this data and are confused by databases. They don’t know SQL, they can’t join tables... that’s complicated stuff! The managers want to see our data in the old format, as a single table.”
“I can create a view that looks exactly like their old spreadsheet.”
“You can do that? That would be perfect!”
"I am on it Boss!"

Task

The agency CIO wants a well-designed database to store the campaign data, AND she also wants a 'big table' view that contains all the info that the 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 normalization and SQL views!

Create a VS Code 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 myFiles folder. Filename for the notebook is "MP6 YourLastname.ipynb".

Criteria

Your SQL must 1) drop any table and view created by previous runs of your code (this is for grading), 2) create the correct tables and view, inclusive of PKs and FKs, 3) populate them with the correct data, and create and show a view that looks like the picture below - including the order of rows and formats.
For all tables and views always use the extended names. Extended names include your userid, e.g., fkg2s.AD_CAMPAIGN.

Building the Pressly DB

In class, you learned how to normalize tables by splitting them into several smaller tables. The W3 site has explanations and examples on SQL commands such as create table, create view, insert into, update, and delete records. Your favorite AI can help, too.

To build the Pressly DB, follow these steps:

1) Normalize the table below (3NF). Note that the table below is not identical to the one shown in class. Do all the tests and fixes for NF 1, 2 and 3.  For guidance, see the deck for the class that covered Normalization, ask GenAI for help, or contact the professor. 

2) Use the COMM3220 database (same server, userId and password). Write in the notebook SQL statements that create the normalized tables.
Alternate in the notebook text describing what the following SQL statement will do, and then a runnable SQL.
Pick data types that fit the contents of the columns, not their visualizations.  For example, budget pct must be stored as a decimal number, and later visualized as a percentage, with a % sign.
Include all PKs and FKs. 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 below.

4) Add a query that changes the name of m202 to 'Bethel'.

5) Create a view called BIG_TABLE that joins the smaller tables and reproduces faithfully the denormalized big table as in the figure below, except that 'Elizabeth' will now be 'Bethel'.  Visualize the exact same column names (use alias if necessary), same column order, same row order, same data and formats. Ok if not centered in column.

6) Write a Select * for the view.

7) Write DROPs that delete the tables created in step 2 and the view from step 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 matters: you cannot drop a table that has an active FK.

This exercise was inspired by the Jukic et al. DB book.