Every time a CREATE (Table, Index, Database, Stored Procedure, Trigger) is used ensure it has a corresponding DROP IF EXISTS that precedes it. This allows the script to be run over and over getting in an inconsistent state (e.g. remanent objects)

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

See attachment

*****************************************

General tips for getting the highest score

 

Ensure the entire script can run from beginning to end without errors. If you cannot fully implement a PART or some statements, add what works correctly, and add comments to describe the issue. If you can't get a statement to process correctly, comment it out and add comments to explain the issue.

 

Again, the entire script needs to run without errors.

 

Every time a CREATE (Table, Index, Database, Stored Procedure, Trigger) is used ensure it has a corresponding DROP IF EXISTS that precedes it. This allows the script to be run over and over getting in an inconsistent state (e.g. remanent objects)

 

See the CREATE database section below as an example for a CREATE DATABASE statement, you'll need to do this for EVERY object  (Table, Index, Database, Stored Procedure, Trigger) you create.

 

 

For many batch statements, you'll need to use a GO as a separator, which resets the transaction block,otherwise you'll get an error.

 

As an example, make sure your DROP statements that precede a CREATE of the same object have a GO in between

 

DROP statement <OBJECTA>

--followed by a

GO

--followed by a

CREATE statement <OBJECTA>

 

Without the GO, you'll get a Error like .....CREATE ....' must be the first statement in a query batch

 

Also, while not absolute, the same type of error CAN happen after a CREATE statement followed directly by a corresponding SELECT statement of the same object. (e.g. Create VIEW followed by a GO, then by a SELECT from the VIEW).

 

To be safe in this project, follow this pattern when you have the two statements back to back.

CREATE statement <OBJECTA>

--followed by a

GO

--followed by a

SELECT statement <OBJECTA>

********************************************/

 

 

/***********************************************

Part A - Design and Create Database

 

First CREATE the DB, then Create the Tables

************************************************/

-- Write statements below

 

 

 

 

 

PRINT 'Part A Completed'

 

/***********************************************

Part B -Create key data management stored procedures

************************************************/

-- Write statements below

 

-------------------------

-- sp_addEmployee

-------------------------

 

 

-------------------------

-- fn_findEmployeeID

-------------------------

 

-------------------------

-- sp_addOffice

-------------------------

 

 

-------------------------

-- fn_findOfficeID

-------------------------

 

 

-------------------------

-- sp_addEmployeeOffice

-------------------------

 

 

-------------------------

-- sp_addSoftware

-------------------------

 

 

 

 

-------------------------

-- sp_assignSoftware

-------------------------

 

PRINT 'Part B Completed'

 

 

/***********************************************

Part C - Populate database by using the following stored procedure calls. 

************************************************/

-- Add Employees

EXEC sp_addEmployee 'Mickey','Mouse','01/02/1933','90012'

EXEC sp_addEmployee 'Minnie','Mouse','02/03/1933','90012'

EXEC sp_addEmployee 'Donald', 'Duck','03/04/1944','30011'

EXEC sp_addEmployee 'Porky', 'Pig','05/06/1956','90056'

EXEC sp_addEmployee 'Mister', 'Incredible','05/06/1956','90056'

EXEC sp_addEmployee 'Snow', 'White','07/08/1962','32700'

 

-- Add Offices

EXEC sp_addOffice 'Ariel',100,'92801','Mickey Mouse'

EXEC sp_addOffice 'Looney Tunes',500,'91522','Minnie Mouse'

EXEC sp_addOffice 'Olaf',300,'32830','Mickey Mouse'

EXEC sp_addOffice 'Simba',300,'32830','Mister Incredible'

 

-- Assign Employess to Offices

EXEC sp_addEmployeeOffice 'Mickey Mouse','Ariel'

EXEC sp_addEmployeeOffice 'Mickey Mouse','Olaf'

EXEC sp_addEmployeeOffice 'Mickey Mouse','Looney Tunes'

EXEC sp_addEmployeeOffice 'Donald Duck','Olaf'

EXEC sp_addEmployeeOffice 'Porky Pig','Looney Tunes'

EXEC sp_addEmployeeOffice 'Minnie Mouse','Ariel'

EXEC sp_addEmployeeOffice 'Minnie Mouse','Olaf'

EXEC sp_addEmployeeOffice 'Mister Incredible','Simba'

EXEC sp_addEmployeeOffice 'Snow White','Ariel'

EXEC sp_addEmployeeOffice 'Snow White','Olaf'

 

-- Add Software to the system

EXEC sp_addSoftware 'MW08ABCD','Microsoft','Windows','08','01/01/2017'

EXEC sp_addSoftware 'AA13EFGH','Adobe','Acrobat','14','01/06/2016'

EXEC sp_addSoftware 'MO16IJKLM','Microsoft','Windows','10','01/01/2017'

EXEC sp_addSoftware 'MW10NOPQ','Microsoft','Windows','10','01/01/2017'

EXEC sp_addSoftware 'MW11RSTU','Microsoft','Windows','11','01/01/2017'

EXEC sp_addSoftware 'MW13VWXY','Microsoft','Windows','13','01/01/2017'

EXEC sp_addSoftware 'MW14ZABC','Microsoft','Windows','14','01/01/2017'

EXEC sp_addSoftware 'AA12EFGH','Adobe','Acrobat','12'

EXEC sp_addSoftware 'AA11IJKL','Adobe','Acrobat','11'

 

-- Assign Software to Employees

EXEC sp_assignSoftware 'MW08ABCD','Mickey Mouse','Mickey Mouse'

EXEC sp_assignSoftware 'AA13EFGH','Mickey Mouse','Mickey Mouse'

EXEC sp_assignSoftware 'MO16IJKLM','Mickey Mouse','Mickey Mouse'

EXEC sp_assignSoftware 'MW10NOPQ','Donald Duck','Minnie Mouse'

EXEC sp_assignSoftware 'MW11RSTU','Minnie Mouse','Mickey Mouse'

EXEC sp_assignSoftware 'MW13VWXY','Mister Incredible','Minnie Mouse'

EXEC sp_assignSoftware 'MW14ZABC','Porky Pig','Mickey Mouse'

EXEC sp_assignSoftware 'MW14ZABC','Porky Pig','Minnie Mouse'

EXEC sp_assignSoftware 'MW14ZABC'

 

 

PRINT 'Part C Completed'

/***********************************************

Part D - Create Software Archive Tracking

************************************************/

 

-------------------------

-- Create SoftwareArchive Table

-------------------------

 

 

-------------------------

-- Trigger?

-------------------------

 

 

 

-- Test Data to assume the Archive is correctly recording changes

EXEC sp_assignSoftware 'AA11IJKL','Porky Pig','Mickey Mouse'

EXEC sp_assignSoftware 'AA12EFGH','Porky Pig','Minnie Mouse'

-- Unassign softwware A11IJKL

EXEC sp_assignSoftware 'AA11IJKL'

 

 

 

-------------------------

-- fn_findEmployeeName

-------------------------

 

-------------------------

-- vSoftwareArchive

-------------------------

 

 

PRINT 'Part D Completed'

/***********************************************

Part E - New SoftwareArchive view

************************************************/

 

 

-------------------------

-- vSoftwareArchive

-------------------------

 

 

PRINT 'Part E Completed'

/***********************************************

Part F - Integrate City, State, StateAbbrev

************************************************/

 

 

-- #1 Using a BULK INSERT us_postal_codes

 

-- #2 Expand Offices table

 

-- #3 Update Offices with new location info

 

-- #4 Modify sp_addOffice

 

 

 

-- Test modified version of sp_addOffice

EXEC sp_addOffice 'Jafar',800,'60062','Mister Incredible'

 

PRINT 'Part F Completed'

 

/***********************************************

Part G - Terminate Employee (Create sp_terminateEmployee)

************************************************/

 

-- Accommodate termDate field

 

-- sp_terminateEmployee

 

 

-- Execute as a test

EXEC sp_terminateEmployee 'Porky', 'Pig'

 

PRINT 'Part G Completed'

 

Attachments:

Expert's Answer

536 Times Downloaded

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of