Question

Step 1: Create table audits via triggers

The system must log any insertion, deletion, or updates to the following tables:

• Employee table (project 1)

create table Employee

(

empNumber char(8) not null,

firstName varchar(25) null,

lastName varchar(25) null,

ssn char(9) null,

address varchar(50) null,

state char(2) null,

zip char(5) null,

jobCode char(4) null,

dateOfBirth date null,

certification bit null,

salary money null,

constraint PK_EMP PRIMARY KEY(empNumber),

constraint EMP_STATECHECK CHECK(state in ('CA','FL'))

)

GO

• Job table (project 1)

create table Job

(

jobCode char(4) not null,

jobdesc varchar(50) null,

constraint PK_JobCode PRIMARY KEY(jobCode),

constraint JOB_JOBCODE CHECK(jobCode in ('CAST','ENGI','INSP','PMGR'))

)

GO

• ProjectMain table (Project 2)

create table ProjectMain

(

projectId char(4) NOT NULL PRIMARY KEY,

projectName varchar(50),

firmFedID char(9),

fundedbudget decimal(16, 2),

startDate date,

projectStatus varchar(25),

projectTypeCode char(5),

projectedEndDate date,

projectManager char(8)

)

GO

• ActivityMain table (Project 2)

create table ActivityMain

(

ActivityId char(4) NOT NULL PRIMARY KEY,

projectId char(4),

activityName varchar(50),

costToDate decimal(16, 2) ,

activityStatus varchar(25),

startDate date,

endDate date

)

GO

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: System Catalog Scripts

Using the System Catalog Views

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql

create the SQL Scripts for the following views:

1. vw_TableNoIndexes: a. Description: Return all user tables with no clustered/ nonclustered Indexes.

b. Columns: name, create_date

2. vw_ProjectIdTables: a. Description: Return all the user tables which contain the column “projectId”

b. Columns: name, create_date

3. vw_Last7Obj: a. Description: All the objects that have been modified in the last 7 days

b. Columns: name, modify_date

4. vw_ProjectProcs: a. Description: The SQL logic (definition) from the stored procedures which have “Project” in their name

b. Columns: name, definition, create_date

NOTE: Please make sure you use the EXACT columns listed above for these views.

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.

Columns: DatabaseName, NumberOfConnections, LoginName

Example OutPut: DatabaseName	NumberOfConnections	LoginName
COP4703	2	aroque

Sp_LogFileStatus

Return all the status of all the transaction log files for a given database name.

Parameter: @databasename varchar(250)

Hint: Use sys.master_files from the Database Health Monitoring document.

Columns: DatabaseName, LogSize, TotalSize

Example OutPut: DatabaseName	LogSize	TotalSize
COP4703	8	16

Answer 1

//AuditTableScript.sql

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) null,

zip char(5) null,

jobCode char(4) null,

dateOfBirth date null,

certification bit null,

salary money null,

operation varchar(50),

dateTimeStamp timestamp

)

GO

create table JobAudit

(

jobCode char(4) not null,

jobdesc varchar(50) null,

operation varchar(50),

dateTimeStamp timestamp

)

GO

create table ProjectMainAudit

(

projectId char(4) NOT NULL PRIMARY KEY,

projectName varchar(50),

firmFedID char(9),

fundedbudget decimal(16, 2),

startDate date,

projectStatus varchar(25),

projectTypeCode char(5),

projectedEndDate date,

projectManager char(8),

operation varchar(50),

dateTimeStamp timestamp

)

GO

create table ActivityMainAudit

(

ActivityId char(4) NOT NULL PRIMARY KEY,

projectId char(4),

activityName varchar(50),

costToDate decimal(16, 2) ,

activityStatus varchar(25),

startDate date,

endDate date,

operation varchar(50),

dateTimeStamp timestamp

)

GO

//Triggers.sql

CREATE TRIGGER trgEmployee ON dbo.Employee

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

INSERT INTO dbo.EmployeeAudit

(empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, operation)

SELECT i.empNumber, i.firstName, i.lastName, i.ssn, i.address, i.state, i.zip, i.jobCode, i.dateOfBirth, i.certification, i.salary, 'INSERTED'

FROM dbo.Employee e

INNER JOIN inserted i ON e.empNumber=i.empNumber

INSERT INTO dbo.EmployeeAudit

(empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, operation)

SELECT d.empNumber, d.firstName, d.lastName, d.ssn, d.address, d.state, d.zip, d.jobCode, d.dateOfBirth, d.certification, d.salary, 'DELETED'

FROM dbo.Employee e

INNER JOIN deleted d ON e.empNumber=d.empNumber

END

GO

CREATE TRIGGER trgJob ON dbo.Job

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

INSERT INTO dbo.JobAudit

(jobCode, jobdesc, operation)

SELECT i.jobCode, i.jobdesc, 'INSERTED'

FROM dbo.Job j

INNER JOIN inserted i ON j.jobCode=i.jobCode

INSERT INTO dbo.JobAudit

(jobCode, jobdesc, operation)

SELECT d.jobCode, d.jobdesc, 'DELETED'

FROM dbo.Job j

INNER JOIN deleted d ON j.jobCode=d.jobCode

END

GO

CREATE TRIGGER trgProjectMain ON dbo.ProjectMain

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

INSERT INTO dbo.ProjectMainAudit

(projectId, projectName, firmFedID, fundedbudget, startDate, projectStatus, projectTypeCode, projectedEndDate, projectManager, operation)

SELECT i.projectId, i.projectName, i.firmFedID, i.fundedbudget, i.startDate, i.projectStatus, i.projectTypeCode, i.projectedEndDate, i.projectManager, 'INSERTED'

FROM dbo.ProjectMain p

INNER JOIN inserted i ON p.projectId=i.projectId

INSERT INTO dbo.ProjectMainAudit

(projectId, projectName, firmFedID, fundedbudget, startDate, projectStatus, projectTypeCode, projectedEndDate, projectManager, operation)

SELECT d.projectId, d.projectName, d.firmFedID, d.fundedbudget, d.startDate, d.projectStatus, d.projectTypeCode, d.projectedEndDate, d.projectManager, 'DELETED'

FROM dbo.ProjectMain p

INNER JOIN deleted d ON p.projectId=d.projectId

END

GO

CREATE TRIGGER trgActivityMain ON dbo.ActivityMain

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

INSERT INTO dbo.ActivityMainAudit

(ActivityId, projectId, activityName, costToDate, activityStatus, startDate, endDate, operation)

SELECT i.ActivityId, i.projectId, i.activityName, i.costToDate, i.activityStatus, i.startDate, i.endDate, 'INSERTED'

FROM dbo.ActivityMain a

INNER JOIN inserted i ON a.ActivityId=i.ActivityId

INSERT INTO dbo.ActivityMainAudit

(ActivityId, projectId, activityName, costToDate, activityStatus, startDate, endDate, operation)

SELECT d.ActivityId, d.projectId, d.activityName, d.costToDate, d.activityStatus, d.startDate, d.endDate, 'DELETED'

FROM dbo.ActivityMain a

INNER JOIN deleted d ON a.ActivityId=d.ActivityId

END

GO