QuestionQuestion

Requirements

The park will be a Star Wars themed park. You must design additional parts of the database and create the following SQL Script.

Step 1: Create table audits via triggers

The system must log any insertion, deletion, or updates to the following tables:
• Employee table (project 1)
• Job table (project 1)
• ProjectMain table (Project 2)
• ActivityMain table (Project 2)

For each one of the table above, you will write the SQL Script to create their respective AUDIT table which is a table that contains the same columns as the original table, plus the additional columns Operation and DateTimeStamp.

For example, for the EMPLOYEE table with the given columns, you will create EMPLOYEEAUDIT:
Employee -> EmployeeAudit
• empNumber (char(8)), - empNumber (char(8)),
• firstName (varchar(25)), - firstName (varchar(25)),
• lastName varchar(25)), - lastName varchar(25)),
• ssn (char(9)), - ssn (char(9)),
• address (varchar(50)), - address (varchar(50)),
• state (char(2)), - state (char(2)),
• zip (char(5)), - zip (char(5)),
• jobCode (char(4)) , - jobCode (char(4)) ,
• dateOfBirth (date), - dateOfBirth (date),
• certification(bit), - certification(bit),
• salary(money) ) - salary(money) )
• - Operation (varchar(50))
• - DateTimeStamp (datetime)

Therefore, your assignment script will create the following 4 Audit tables:
- EmployeeAudit
- JobAudit
- ProjectMainAudit
- ActivityMainAudit

NOTE: You MUST use the above names for the audit tables. You will create the following 4 triggers:
- trgEmployee: Will be placed on the Employee table and listens for Inserts, Deletes, and Updates
- trgJob: Will be placed on the Job table and listens for Inserts, Deletes, and Updates
- trgProjectMain: Will be placed on the ProjectMain table that contains the projectId and projectName and listens for Inserts, Deletes, and Updates

- trgActivityMain: Will be placed on the ActivityMain table that contains the activityId and activityName and listens for Inserts, Deletes, and Updates.

Again, each trigger will write to its respective audit table:

trgProjectMain will write to ProjectMainAudit trgActivityMain will write to ActivityMainAudit trgEmployee will write to EmployeeAudit trgJob will write to JobAudit
Again, the columns which will be written to the audit tables will be all the original columns plus “Operation” and “DateTimeStamp”

The trigger will support the Insert, Delete and Update operation as follows:

• If a record is inserted in the original table, then the audit table will contain the original values plus “INSERTED”
into the operation column with the datetime stamp.
• For the Delete operation, it will contain “DELETED” in the operation column plus the datetimestamp.
• For the Update operation, it will contain 2 records in the audit table, one for the old values (Operation will have DELETED) and one for the new values (Operation will have INSERTED).

Step 2: DBA Exercise: Create System Catalog Scripts

For Step2 and Step 3, make sure you review the Database Health Monitoring Doument

Using the System Catalog Views (known as Object Catalog Views in SQL Server)

create the SQL Scripts for the following views:
1. vw_TableNoIndexes: User tables with no Indexes
2. vw_ProjectIdTables: All the tables which contain the column “projectId”
3. vw_Last7Obj: All the objects that have been modified in the last 7 days
4. vw_ProjectProcs: The SQL logic from the stored procedures which have “Project” in their name

Step 3: DBA Troubleshooting

Create the following Stored Procedures to assist in performing a database administrator tasks.

Sp_ActiveConnections
Return all the active connections for a given database name. Parameters: @databasename varchar(250)
Hint: Use sys.sysprocesses. You should do some grouping to a count of the connections. Output:
DatabaseName NumberOfConnections LoginName
COP4703 1 aroque

Sp_LogFileStatus
Return all the status of all the transaction log files for a given database name and their size in kilobytes. Parameters: @databasename varchar(250)
LogSize: Size of the log file DataSize: Size of the data file
Hint: Use sys.master_files from the Database Health Monitoring document. Output:
DatabaseName LogSize DataSize
COP4703 12168 73728

Step 4: Verify Objects Verify Object names
Once you have completed your work and created the required objects in your database, you will need to run the Project3Verifier to assure that:
- Your objects have been properly created
- Object naming is correct.

Run the Project3Verifier.sql script on the database where you created your objects. If any errors appear, you need to review them before proceeding.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

use Agui_M5696129
go

insert into master.dbo.assignments
(pantherId, firstname, lastname, databasename, assignment)
values
('5696129' , 'Maurice' , 'Aguilar' , 'Agui_M5696129' , 1)

go

-- create audit table


CREATE TABLE EmployeeAudit(
empNumber char(8) NOT NULL,
firstName varchar(25) NULL,
lastName varchar(25) NULL,
ssn char(9) NULL,
address varchar(50) NULL,
state char(2) NOT NULL,
zip char(5) NULL,
jobCode char(4) NOT NULL,
dateOfBirth date NOT NULL,
certification bit NOT NULL,
salary money NOT NULL,
Operation varchar(50),
DateTimeStamp datetime

go

CREATE TABLE JobAudit(
jobCode char(4) NOT NULL,
jobdesc varchar(50) NULL,
Operation varchar(50),
DateTimeStamp datetime
)

go

CREATE TABLE ActivityMainAudit(
activityId char(4) NOT NULL,
activityName varchar(50) NULL,
costToDate decimal(16, 2) NULL,
activityStatus varchar(25) NULL,
startDate date NULL,
endDate date NULL,
projectId char(4) NULL,
Operation varchar(50),
DateTimeStamp datetime
)
GO

CREATE TABLE ProjectMainAudit(
projectId char(4) NOT NULL,
projectName varchar(50) NULL,
projectStartDate date NULL,
projectstatus varchar(25) NULL,
projectTypeCode char(5) NULL,
projectedEndDate date NULL,
projectManager char(8) NULL,
fundedbudget decimal(16, 2) NULL,
Operation varchar(50),
DateTimeStamp datetime
)
GO


-- triggers

create or alter trigger trgEmployee on Employee after insert, update, delete as
begin
DECLARE @Activity VARCHAR (50)
DECLARE @ActivityTime datetime

declare @empNumber char(8)
          ,@firstName varchar(25)
          ,@lastName varchar(25)
          ,@ssn char(9)
          ,@address varchar(50)
          ,@state char(2)
          ,@zip char(5)
          ,@jobCode char(4)
          ,@dateOfBirth date
          ,@certification bit
          ,@salary money

set @ActivityTime = SYSDATETIME()

if exists (select * from deleted)
begin
set @Activity = 'DELETED'
select @empNumber = empNumber from deleted i
select @firstName = firstName from deleted i
select @lastName = lastName from deleted i
select @ssn = ssn from deleted i
select @address = address from deleted i
select @state = state from deleted i
select @zip = zip from deleted i
select @jobCode = jobCode from deleted i
select @dateOfBirth = dateOfBirth from deleted i
select @certification = certification from deleted i
select @salary = salary from deleted i

insert into EmployeeAudit
([empNumber]
          ,[firstName]
          ,[lastName]
          ,[ssn]
          ,[address]
          ,[state]
          ,[zip]
          ,[jobCode]
          ,[dateOfBirth]
          ,[certification]
          ,[salary]
          ,[Operation]
          ,[DateTimeStamp])
values (
@empNumber
          ,@firstName
          ,@lastName
          ,@ssn
          ,@address
          ,@state
          ,@zip
          ,@jobCode
          ,@dateOfBirth
          ,@certification
          ,@salary
   , @Activity
   , @ActivityTime
   )
end

if exists (select * from inserted)
begin
set @Activity = 'INSERTED'
select @empNumber = empNumber from inserted i
select @firstName = firstName from inserted i
select @lastName = lastName from inserted i
select @ssn = ssn from inserted i
select @address = address from inserted i
select @state = state from inserted i
select @zip = zip from inserted i
select @jobCode = jobCode from inserted i
select @dateOfBirth = dateOfBirth from inserted i
select @certification = certification from inserted i
select @salary = salary from inserted i

insert into EmployeeAudit
([empNumber]
          ,[firstName]
          ,[lastName]
          ,[ssn]
          ,[address]
          ,[state]
          ,[zip]
          ,[jobCode]
          ,[dateOfBirth]
          ,[certification]
          ,[salary]
          ,[Operation]
          ,[DateTimeStamp])
values (
@empNumber
          ,@firstName
          ,@lastName
          ,@ssn
          ,@address
          ,@state
          ,@zip
          ,@jobCode
          ,@dateOfBirth
          ,@certification
          ,@salary
   , @Activity
   , @ActivityTime
   )
end
end

go...

By purchasing this solution you'll be able to access the following files:
Solution.zip.

$100.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Web Development (HTML, XML, PHP, JavaScript, Adobe, Flash, etc.) Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats