CST2355-Lab5

Algonquin College of Applied Arts and Technology CST2355 – Database Systems Lab Assignment 5 Student Name: __________________ Student ID: __________________ Student email: __________________ Hand-in: 1. The lab assignment will be graded out of a maximum 4 points. 2. This template should be used to submit your lab assignment. 3. Make sure you have enough screenshots to completely document that you have completed all the steps. Activities (Steps): In this lab, you will be using the Microsoft SQL Server Management Studio, building functions, stored procedures, and triggers. 1. First you will use SQL Server Management Studio to create a new database that is owned by your own personal account by performing the following tasks: 1.1. Create a new database named “lab5 ”, owned by yourlastnameLogin, by following these steps: 1.1.1. Connect using your Windows login account (so that you have all the required privileges). 1.1.2. Create a backup of the “lab4” database. 1.1.2.1. Select lab4 > Tasks > Back Up … 1.1.2.2. Make sure you select the “Copy Only” option. 1.1.3. Restore the backup to a new database called “lab5”. 1.1.3.1. Select Databases > Restore Database … 1.1.3.2. Provide the source database as “lab4”. Then a list of backups will appear. Select the destination as “lab5”. You should have something similar to the following:  2021 Algonquin College of Applied Arts and Technology Page 1 / 10

Algonquin College of Applied Arts and Technology Paste your screenshot here:  2021 Algonquin College of Applied Arts and Technology Page 2 / 10

Algonquin College of Applied Arts and Technology 3. .You will now create a user-defined function and use it. 3.1. . Create a new query using Project > New Query 3.1.1.1. Try executing the following query: SELECT * FROM lab5 . lab4EmployeeData . Employees ; Provide a screenshot:  2021 Algonquin College of Applied Arts and Technology Page 4 / 10

Algonquin College of Applied Arts and Technology 3.2. Use the function definition example provided in lab5ExampleFunction.sql (it is almost ready to use…) and run it in SSMS. 3.3. Paste a screenshot showing it working to provide the employees full name.  2021 Algonquin College of Applied Arts and Technology Page 5 / 10

Algonquin College of Applied Arts and Technology -- returns firstname, lastname, hobby for matching employees -- with the added restriction that the city field must be NULL USE lab5 ; GO CREATE PROCEDURE lab4EmployeeData . getAlienData @LastName nvarchar ( 50 ), @FirstName nvarchar ( 50 ) AS SET NOCOUNT ON ; SELECT lab4EmployeeData . ufnGetFullName ( e . ID ) AS 'Full Name' , Hobby FROM lab4EmployeeData . Employees e LEFT JOIN lab4EmployeeData . EmployeeHobby ON e . ID = lab4EmployeeData . EmployeeHobby . EmployeeID LEFT JOIN lab4EmployeeData . Hobbies ON lab4EmployeeData . EmployeeHobby . HobbyID = lab4EmployeeData . Hobbies . HobbyID WHERE e . [First Name] = @FirstName AND e . [Last Name] = @LastName AND City IS NULL; GO 4.1.2. Try using the stored procedure. EXEC lab4EmployeeData . getAlienData 'King' , 'Douglas' ; 4.1.3. Provide a screenshot of your stored procedure working on your data: 4.2. Now you will build a “real” stored procedure that creates a new table from data that has been imported into the database. This is typically one of the last steps of the workflow in an Extract-Transform-Load scenario. 4.2.1. Build a “cube” (note: this is not the same as a cube in the SQL Server Analysis tools…) of data for your lab4EmployeeData schema as a stored procedure. Whenever it needs to be re-built, the stored procedure can be executed. 4.2.1.1. Modify the example provided in the “lab5ExampleCalls.sql” file to create one or more queries that will create the CubeData table and then build the “cube” for your data called “CallsCube”. Your output table should use joined-in data to show the “Calls” totals for employees by name in each named department: 4.2.1.1.1. Include the following fields in your cube, in addition to the SUM(Calls) field : Country, Region, DepartmentName, FullName (use your function!), Year, Month, and Day  2021 Algonquin College of Applied Arts and Technology Page 7 / 10

Algonquin College of Applied Arts and Technology 4.2.1.1.2. Use hierarchical ROLLUP grouping for the Year, Month, Day. 4.2.1.1.3. Use CUBE grouping for Country, Region, DepartmentName, and FullName 4.2.1.2. Provide screenshot(s) that show the SQL: how you built the “cube”. 4.2.1.3. Provide a screenshot showing the resulting cube data (“SELECT * FROM CallsCube;”) 5. Now we will look at an actual data warehouse example. Download the AdventureWorks datawarehouse from Microsoft by using the following steps: 5.1. First, determine the version of SQL server you are connected to in SSMS. If you look at the connection information (here is mine): 5.2. It will show if it is a version 14 or 15 server. If it is a version 14 (like mine) then you can use the AdventureWorksDW2017 and AdventureWorks2017 databases. If it is a version 15, then you can use the AdventureWorksDW2019 and AdventureWorks2019 databases. Provide a screenshot of your version here: 5.3. Navigate to the Microsoft site that has backups stored for various versions of the AdventureWorks example database: https://docs.microsoft.com/en- us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms 5.4. Download the AdventureWorksDW2017.bak or AdventureWorksDW2019.bak backup file. You are going to restore this to your SQL Server 2019 server. 5.5. Download the AdventureWorks2017.bak or AdventureWorks2019.bak backup file. You are going to restore this to your SQL Server 2019 server. 5.6. You need to restore and set the new owner for the AdventureWorksDW2017 database 5.6.1. Restore: the first part of the following video shows the installation steps in detail:  2021 Algonquin College of Applied Arts and Technology Page 8 / 10

Algonquin College of Applied Arts and Technology OPTIONAL BONUS: (to guarantee 100%) 8. Create a similar query to that in Step 6, except use the tables in the OLTP version; that is use either the schema “AdventureWorks2017” or “AdventureWorks2019”. 8.1. You will have to discover where the data is stored. You can start with the following: use AdventureWorks2017 ; SELECT COLUMN_NAME AS 'Column_Name' , TABLE_NAME AS 'Table_Name' FROM INFORMATION_SCHEMA . COLUMNS WHERE COLUMN_NAME LIKE '%BirthDate%' ORDER BY Table_Name , Column_Name ; SELECT COLUMN_NAME AS 'Column_Name' , TABLE_NAME AS 'Table_Name' FROM INFORMATION_SCHEMA . COLUMNS WHERE COLUMN_NAME LIKE '%City%' ORDER BY Table_Name , Column_Name ; 8.2. It will take some digging…. 8.3. Provide a screenshot below to show your query and output:  2021 Algonquin College of Applied Arts and Technology Page 10 / 10