Data Management - Applications D191 Project Script

Good afternoon. The business problem that I’ve chosen to address for this assessment is as follows. The Marketing department has requested a report that shows how many rentals each of our customers has made so that they can target groups of customers with marketing incentives, customer loyalty rewards, and exclusive deals for our top customers. The data that marketing requires can be found in the customer and rental tables of the database. The specific details that marketing needs are the customer id, first name, last name, email address, if the account is active or not, and we will need the rental id to count how many rentals each customer has made. Our first task is to create the details table, which we will call customer_rentals. We will write the code to pull in the details we just outlined. We will also use a case statement to transform the active flag from 1 and 0 to a less ambiguous ‘Yes’ or ‘No’. We will also use the Select INTO method to automatically create the customer_rentals table so that we are efficient in our code. (((WRITE CODE))) Now that the code is written, we’ll validate that we are pulling the data by running a select statement against our newly created customer_rentals table. We’ll limit it to 20 lines so we can ensure data is there. (((RUN SELECT STATEMENT))) Now that we have the details gathered, we’ll create the summary table for the Marketing department. We will call this table rental_summary. We’re going to pull in customer id, first name, last name, email, and the active_acct column that we created with our case statement. We are also going to filter the data we pull so that we are only pulling in accounts where the active_acct is listed as ‘Yes’. This will give us only the active accounts. Again, we will use the Select INTO method to create the rental_summary table for efficiency. (((WRITE CODE))) Now that we have the table created, we will again validate with a simple select statement. (((RUN SELECT STATEMENT))) Now we see that we have the number of recent rentals by rental id, with all the corresponding customer data. The marketing department can now run queries against this table to pull groups of customers based on their rental history. For example, if the marketing department wants to send a special offer to our top 10 customers, they can pull a report showing who those top 10 customers are and easily get their information. (((RUN TOP TEN QUERY))) This data should be refreshed on a monthly basis to provide marketing with fresh data to create their marketing strategies. With our tables created, now we want to create some automation so that we are not having to manually run queries to keep the data fresh. We’ll start with a function and trigger on our detail table, customer_rentals that will update the summary table, rental_summary, whenever a change is made. We’ll start by creating the function code to refresh the rental_summary table. (((WRITE FUNCTION))) Now we’ll write the trigger against the customer_rentals table so that any time there is an update, insert, or delete to the customer_rentals table, the rental_summary table is updated as well. (((WRITE TRIGGER))) Now we want to create a stored procedure for refreshing both the customer_rentals table and the rental_summary table automatically. (((WRITE PROCEDURE))) With the procedure written, the DBA can use a third-party application such as pgAdmin to run this procedure monthly. The DBA could also write a script to automate the running of this procedure monthly. This procedure can also be run manually any time the marketing department requests fresh data as their business requires.