The Jurisdiction dataset looks like the following in Microsoft Excel:
Carry out the instructions contained in the following document:
Generate a screenshot similar to that presented in the final step of the exercise document and incorporate it into your Microsoft Word assignment document. This will serve as confirmation of work completion.
Part 2: COVID-19 Database Creation Script
The following documents contain the physical design diagram and data dictionary for the COVID-19 database:
A partially completed COVID-19 database creation script is contained in the following file:
Download the database creation script file to an accessible location on your computer. Open the script file in the Microsoft SQL Server Management Studio application by selecting FILE OPEN FILE in the top menu:
The Open File dialogue will appear. Navigate to the script file, select it, and then click on the Open button.
The script file contents will appear in a new windowpane on the right side of the Microsoft SSMS application. Using the provided physical database design diagram, complete the CREATE TABLE definitions for the following tables: Jurisdiction, CovidCases, and CovidDeaths. Replace the ?????????? tags with the required SQL code per the database design specification. Click on the Save button to update the database creation script file.
Please note that the CovidCases and CovidDeaths tables contain composite primary keys.
Take screenshots of the completed CREATE TABLE definitions for the Jurisdiction, CovidCases, and CovidDeaths tables and incorporate them into your Microsoft Word assignment document. This will serve as confirmation of work completion.
Part 3: COVID-19 Database Implementation
Execute the completed COVID-19 database creation script by opening the file in Microsoft SSMS and then clicking on the Execute button:
After the COVID-19 database is established without error, download the following COVID-19 database population script file to an accessible location on your computer:
Open the database population script in Microsoft SSMS in the same way it was done for the database creation script in the Part 2 assignment work. You do not need to modify the database population script after it is opened in Microsoft SSMS. You just need to click on the Execute button to run the script, which will populate the established COVID-19 database tables with data.
Please note that the database population script takes several minutes to run. The script execution time depends on the performance of the computer running the Microsoft SQL Server and SSMS applications.
Click on the Refresh button (
) in the top menu to refresh the list of databases in the Object Explorer pane.
Any errors that occur during the execution of the database creation and population scripts are likely due to mistakes in the Part 2 assignment work. Re-visit Part 2 and make the appropriate corrections to the CREATE TABLE definitions for the Jurisdiction, CovidCases, and CovidDeaths tables. After the corrections are incorporated and the database creation script file is saved, re-run both the database creation and population scripts.
Incorporate screenshots of the generated database into your Microsoft Word assignment document. The screenshots need to show the structure of each established COVID-19 database table. This will serve as confirmation of work completion. An example screenshot showing database structures (i.e., tables, attributes, attribute types, and keys) from the Object Explorer pane of SQL Server Management Studio is provided below:
Part 4: Manual Data Manipulation
The COVID-19 database population script executed in Part 3 of the assignment does not contain any data for the VaccineResearcher table. Generate and execute INSERT statements to add the following vaccine researchers:
Researcher 1:
FirstName = Katalin
LastName = Kariko
Title = Lead Researcher
VaccineID = 2
Researcher 2:
FirstName = Hamilton
LastName = Bennett
Title = Senior Director
VaccineID = 1
Please note that you do not need to provide values for the ResearcherID attribute in the VaccineResearcher INSERT statement. The ResearcherID attribute has the IDENTITY specification assigned to it, which means that the ResearcherID value is automatically assigned via sequential numbering.
Take screenshots showing the completed INSERT statements along with confirmation of the data entry into the VaccineResearcher table. Incorporate the screenshots into your Microsoft Word assignment document. The image below provides a screenshot example.
Katalin Kariko has been promoted to Vice President. Perform an update of Katalin Karikos record in the VaccineResearcher table to reflect this. Take a screenshot showing the completed UPDATE statement along with confirmation of the data modification in the VaccineResearcher table. Incorporate the screenshot into your Microsoft Word assignment document. The image below provides a screenshot example.
Hamilton Bennett has retired and is no longer performing vaccine research work. Delete Hamilton Bennetts record from the VaccineResearcher table. Take a screenshot showing the completed DELETE statement along with confirmation of the data deletion in the VaccineResearcher table. Incorporate the screenshot into your Microsoft Word assignment document. The image below provides a screenshot example.
Requirements: word document with screen shots and 300-400 words