DOWNLOAD THE SQL DEVELOPER BY ORACLE AND FOLLOW THE INSTRUCTION I HAVE ATTACHED.DEADLINE: 04/27/2021 AT 05:00 PM CDT
1. RUN THE START DDL SCRIPT FROM THE ATTACHED2. WRITE OUT THE CODES FOR EACH QUESTION LISTED IN THE WORD DOCUMENT ATTACHED.3. UPLOAD THE COMPLETED FILE ACCORDING TO THE INSTRUCTION IN THE WORD DOCUMENT AS .SQL FILE AND .TXT FILE
ETL (MIS 325) – The last one! Apr 2021.v1 Instructions: · Do your own work: This is an individual assignment and you must do your own work and create your own SQL statements. If you are caught cheating on this, you will receive a zero on this assignment and be reported to the Dean of Students. Also this homework prepares you for the exam coming up so doing the work now will help you learn and do well on when it counts more. · What to turn in · Clearly separate your code for each question. Save your code into one SQL file with the naming format: LastName_FirstName_UTEid. Please make sure the lastname and firstname you use matches what is in Canvas. · Submit your .sql file on Canvas before the deadline. Late submissions receive 50% off. No submissions will be accepted 24 hours after the deadline. · The SQL problems below will be based on the DDL script that is posted on the Canvas instructions. Download that script and run it before you start. NOTE: This script is different that script used on other homeworks. Challenge: 1. Review the structure of the current patron and book_club_signup tables. Identify the fields they have in common. 2. Create DDL script that produces a new table called patron_dw to serve as a data warehouse for the two groups of users (current patrons and book club signup) tables. In this patron_dw table, include only fields that are in common which are the following: the respective user_id, first name, last name, email, phone, and zip. Plan to include a column called data_source in the table that stores one of the following codes (PATR, CLUB) to identify the table that the record came from. The data_source and id will serve as a joint primary key to the patron_dw table. IMPORTANT: Name the id column of this table user_id. 3. Write 2 create view statements that will format the data from the two tables in a unified, consistent format that matches the user structure in target patron_dw table. The phone number column should be named “phone”. The view must be named after the table and include a “_view” suffix. a. Phone numbers should all be formatted as ###-###-#### format in the data warehouse table. 4. Write 2 insert statement that will select the columns from the view and then insert them into the patron_dw table but only if the records don’t exist already in the data warehouse table. The insert statements should be designed to run via a procedure that runs to merge new records into the warehouse which means that can run over and over (i.e. not just once) without errors. Note: Eventually this proc can be run on a nightly schedule to update the dw table each day. 5. Write 2 update statement that will update the records on the patron_dw table with the latest name, email, phone, or zip from the source views. The update statements will be run in a the nightly scheduled procedure and just update all fields with the current value in the source table, regardless if it has changed since the update statement last ran. NOTE: These statements should be designed to run over and over (i.e. not just once) without error. · Path 1: If you have trouble using an UPDATE, consider utilizing a MERGE statement. NOTE: a merge statement can take the place of UPDATES or it can be used in place of both INSERTS and UPDATES. · Path 2: You can also consider doing a total refresh which would entail doing no updates but instead truncates the data warehouse and reinserts all the records again. · Path 3: BULK COLLECT can be useful here but may require some problem solving and googling. 6. As a last step create a procedure called user_etl_proc that executes the 2 insert statements and 2 update statements. Here you will place the 2 insert statements and 2 update statements that you wrote. You will not want to leave those statements outside of the procedure. Make sure you DO NOT include an EXCEPTION statement. We want to see your errors. If we see an EXCEPTION statement in your procedure, that is automatically 5 points off. Note, typically you would want to have three insert statements, one for a view from each table (old HW assignment had three tables to join), but we decided to give you a bit of a break on this one and give you two 7. Turn in: · A script that includes the following your the DDL to create the patron_dw table, the DDL to create your view, and the statement to create your procedure. You do not need to include drop statements or procedure call statements for your work since we’ll be using the code listed below to drop and run your code. How to test and confirm your work: · Make sure your script creates the views, table, and procedure exactly according to the naming standards listed in the instructions above. This will allow us to run a standard drop script (listed below) after grading each student and will make the grading process go faster and be more efficient. Failure to following naming standards will result in points off. · Test that the following drop script runs without unexpected errors to drop your database objects. If you’re getting errors, correct these before submitting. If misnamed tables or errors prevent this script from running during the grading process, points will be deducted. drop table patron_dw; drop view patron_view; drop view book_club_signup_view; drop procedure user_etl_proc; · To test your ETL works correctly, use the following steps. 1. Drop and recreate the source tables using the start DDL script. This ensures source data is refreshed. 2. Run your DDL to create views and dw table and compiled procedure. Ensure no unexpected errors occur. 3. Run the procedure to ensure it runs without errors (call it). 4. Rerun the procedure to ensure it runs without error (call it again). The code in the proc should allow reruns 5. Confirm that data from source tables is in the patron_dw table. 6. Insert a record in each of the source tables and also update a record on each source table. After you do these inserts and updates, call your procedure again. Confirm there are no unexpected errors. 7. After the procedure runs successfully, refresh the patron_dw table by rerunning your procedure and ensure your newly inserted records and record updates are showing up as expected. 8. If everything works then you can run the drop statements above and save/submit your script. · NOTE: The steps above are what we’ll do to test your code works. High Level Steps NOTE: These is just the high-level steps. A detailed breakdown of the steps is below. Move on to page 2 and follow the steps close. Setup SQL Developer 1. Set up an oracle account and confirm account 2. Download sql_developer and install it Setup McCombs Oracle Server Connection 3. Create McCombs Account and connect to server. Create tables and seed data 4. Need to run the script in the client to create the tables. (Mccombs_db_setup folder) a. Run Create_ap_ex_om…. script. (Run as a script. Hit F5) b. Run VERIFY_ap_ex_om…. script. (This reports run numbers in newly created tables) i. One you complete the verify step, go complete the pre-class survey which will ask you to provide the counts you got to check you did things right. SQL Developer Setup Steps Step 1: First go to www.oracle.com and create an account. You will find a tab at the top of screen where it reads “Sign in” and the pull down menu will allow you to choose Create Account. Step 2: Use any web browser, search for “oracle sql developer download”. Click on the first link to get to the following landing page. Click on “Accept License Agreement”. Download the the right version that matches your operating system. If you’re unsure if your PC is 32-bit or 64-bit, go to your Control Panel and double click on “System” to see what version of system you have. If you have a 64-bit system chose the 64-bit with JDK version to download. Note: Download whatever the most current version is. Once you click the download link, you’ll likely be prompted to log into your Oracle account. This is required to start the download of the sql developer zip file. Heads up, the download can take a while so start the process when you’re have a bit of time. You’ll get a zip file named something like “sqldeveloper-17.2.0.188.1159-x64”. Once downloaded, Install SQL Developer by unzipping (aka extracting) it. (Recommend unzipping into the C:\Program Files directory on a Windows system.), you’ll get a folder named “sqldeveloper” which contains the following files and subfolders. NOTE: The extract will take several minutes. Now you can start SQL Developer by double clicking the sqldeveloper file as shown above. The startup page looks like the figure below. (You may want to create a shortcut for the sqldeveloper file and add it to the Start menu or desktop for easy access.) Setup McCombs Oracle Server Connection Step 1. Setup password for your McCombs Oracle database server account. The official, McCombs way to initialize all Oracle Database server passwords is to use this web page: https://secure.mccombs.utexas.edu/missqlpwdchange/default.aspx Select “Server for Clint Tuttle’s MIS325 Class (msb-mis325.austin.utexas.edu)” from the drop-down menu. Then, set your password by following the password requirements on the website. Step 2. Establish a connection to your McCombs database server. Start the Oracle client program (SQL Developer). Establish a connection to the McCombs database server by click New Connection (the plus sign). You will be prompted to enter the definition of the new database connection. Enter the following information: Connection Name: This can be whatever (e.g. My 325 Server Connection) * User Name:
Password: Connection Type: Basic Role: Default Hostname: msb-mis325.austin.utexas.edu Port: 1521 SID: ORCL *This is whatever you want to save the connection name for later use. At this point, you should see the newly connected server on the left side of the screen. Expand it to view the contents of your database space on the server. A SQL worksheet also automatically opens on the right side of the screen, which can be used to write SQL commands. Create Tables and Seed Data Download the three files from the