Creating the Pressly Ad Agency Database
(graded individual assignment #5)
GOAL: Learning SQL
TASK: Create an ADS notebook containing the queries necessary to create and populate the DB described below. Post the notebook by the due date in your BOX folder. Filename for the notebook is YourLastname_YourFirstName_SQL3.
Insert the pledge at the top of the notebook.
CRITERIA: SQL must produce the correct answer.
This is a pledged individual assignment. You must work on your own.
The Pressly AD Agency DB
In class we normalized the table below, which contained data for this small Ad Agency. We split that big table into several normalized tables. In class we also saw examples of how to create tables and views.
1) Create the tables that compose the normalized database (pick data types that make sense). Include all PKs andFKs. For all tables in all queries use the extended name with your userid, e.g., fkg2s.CUSTOMER.
2) Write DROPs that drop the tables created in (1) and the View in (5) from the database if they are already there (this is for allow
the TA to grade). Place that code before the CREATEs.
3) Fill the 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 that joins the smaller tables and reproduces the denormalized big table as in the figure below, except that 'John' will now be 'Olaf'
