Question

- - Requirements Building upon your project 1 and project 2, 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)), firstName (varchar(25)), lastName varchar(25)) ssn (char(9)), -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)) - Operation (varchar(50)) - DateTimeStamp (datetime) . address (varchar(50)), . state (char(2)) zip (char(5)) jobCode (char(4) dateOfBirth (date) . certification(bit), . salary(money)) 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 projectld and projectName and listens for Inserts, Deletes, and Updates trgActivityMain: Will be placed on the ActivityMain table that contains the activityld and activityName and listens for Inserts, Deletes, and Updates - Again, each trigger will write to its respective audit table trgActivityMain: Will be placed on the ActivityMain table that contains the activityld 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 trg Employee 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 SQLServer) relational-database ob catalog-vi create the SQL Scripts for the following views: 1. vw TableNolndexes: User tables with no Indexes 2. vw ProjectldTables: All the tables which contain the column "projectld" 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 aroque COP4703 Sp_LogFileStatus 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) l/relational-databases ie catalog-views create the SQL Scripts for the following views: 1. vw TableNolndexes: User tables with no Indexes 2. vw ProjectldTables: All the tables which contain the column "projectld" 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 aroque COP4703 Sp_LogFileStatus Return all the status of all the transaction log files for a given database name and their size in kilobytes. Parameters: @databasename 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 varchar(250) LogSize 12168 DataSize COP4703 73728

Answer 1

SELECT * 
INTO EmployeeAudit 
FROM Employee 
WHERE 0 = 1;
ALTER TABLE EmployeeAudit 
  ADD Operation varchar(50),
  DateTimeStamp datetime;
GO

SELECT * 
INTO JobAudit 
FROM Job 
WHERE 0 = 1;
ALTER TABLE JobAudit
  ADD Operation varchar(50),
  DateTimeStamp datetime;
GO

SELECT * 
INTO ProjectMainAudit 
FROM ProjectMain 
WHERE 0 = 1;
ALTER TABLE ProjectMainAudit
  ADD Operation varchar(50),
  DateTimeStamp datetime;
GO

SELECT * 
INTO ActivityMainAudit 
FROM ActivityMain 
WHERE 0 = 1;
ALTER TABLE ActivityMainAudit
  ADD Operation varchar(50),
  DateTimeStamp datetime;
GO



CREATE TRIGGER trgEmployee
ON Employee 
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  if exists(SELECT * FROM inserted)
BEGIN
  INSERT INTO EmployeeAudit (
    empNumber, 
    firstName, 
    lastName, 
    ssn, 
    address, 
    state, 
    zip, 
    jobCode, 
    dateOfBirth, 
    certification, 
    salary, 
    Operation, 
    DateTimeStamp
    )
  SELECT empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, 'INSERTED', CURRENT_TIMESTAMP 
  FROM inserted
END

  if exists(SELECT * FROM deleted)
BEGIN
  INSERT INTO EmployeeAudit (
    empNumber, 
    firstName, 
    lastName, 
    ssn, 
    address, 
    state, 
    zip, 
    jobCode, 
    dateOfBirth, 
    certification, 
    salary, 
    Operation, 
    DateTimeStamp
    )
  SELECT empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, 'DELETED', CURRENT_TIMESTAMP 
  FROM deleted
END

  if(UPDATE(empNumber) OR UPDATE(firstName) OR UPDATE(lastName) OR UPDATE(ssn) OR UPDATE(address) OR UPDATE(state) OR UPDATE(zip) OR UPDATE(jobCode) OR UPDATE(dateOfBirth) OR UPDATE(certification) OR UPDATE(salary)) 
BEGIN
  INSERT INTO EmployeeAudit (
    empNumber, 
    firstName, 
    lastName, 
    ssn, 
    address, 
    state, 
    zip, 
    jobCode, 
    dateOfBirth, 
    certification, 
    salary, 
    Operation, 
    DateTimeStamp
    )
  SELECT empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, 'DELETED', CURRENT_TIMESTAMP 
  FROM deleted
  SELECT empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, 'INSERTED', CURRENT_TIMESTAMP 
  FROM inserted
END
END;
GO



CREATE TRIGGER trgJob
ON Job 
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  if exists(SELECT * FROM inserted)
BEGIN
  INSERT INTO JobAudit (
    jobCode, 
    jobDesc, 
    Operation, 
    DateTimeStamp
    )
  SELECT jobCode, jobDesc, 'INSERTED', CURRENT_TIMESTAMP 
  FROM inserted
END

  if exists(SELECT * FROM deleted)
BEGIN
  INSERT INTO JobAudit (
    jobCode, 
    jobDesc, 
    Operation, 
    DateTimeStamp
    )
  SELECT jobCode, jobDesc, 'DELETED', CURRENT_TIMESTAMP 
  FROM deleted

  if(UPDATE(jobCode) OR UPDATE(jobDesc))
BEGIN
  INSERT INTO JobAudit (
    jobCode, 
    jobDesc, 
    Operation, 
    DateTimeStamp
    )
  SELECT jobCode, jobDesc, 'DELETED', CURRENT_TIMESTAMP 
  FROM deleted
  SELECT jobCode, jobDesc, 'INSERTED', CURRENT_TIMESTAMP 
  FROM inserted
END
END;
GO



CREATE TRIGGER trgProjectMain
ON ProjectMain 
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  if exists(SELECT * FROM inserted)
BEGIN
  INSERT INTO ProjectMainAudit (
    projectId, 
    projectName, 
    firmFedID, 
    fundedbudget, 
    projectStartDate, 
    projectStatus, 
    projectTypeCode, 
    projectedEndDate, 
    projectManager, 
    Operation, 
    DateTimeStamp
    )
  SELECT projectId, projectName, firmFedID, fundedbudget, projectStartDate, projectStatus, projectTypeCode, projectedEndDate, projectManager, 'INSERTED', CURRENT_TIMESTAMP 
  FROM inserted
END

if exists(SELECT * FROM deleted)
BEGIN
  INSERT INTO ProjectMainAudit (
    projectId, 
    projectName, 
    firmFedID, 
    fundedbudget, 
    projectStartDate, 
    projectStatus, 
    projectTypeCode, 
    projectedEndDate, 
    projectManager, 
    Operation, 
    DateTimeStamp
    )
  SELECT projectId, projectName, firmFedID, fundedbudget, projectStartDate, projectStatus, projectTypeCode, projectedEndDate, projectManager, 'DELETED', CURRENT_TIMESTAMP 
  FROM deleted          
END
END;
GO



CREATE TRIGGER trgActivityMain
ON ActivityMain FOR INSERT, DELETE, UPDATE
AS
BEGIN
  if exists(SELECT * FROM inserted)
BEGIN
  INSERT INTO ActivityMainAudit (
    activityId, 
    activityName, 
    projectId, 
    costToDate, 
    activityStatus, 
    startDate, 
    endDate, 
    Operation, 
    DateTimeStamp
    )
  SELECT activityId, activityName, projectId, costToDate, activityStatus, startDate, endDate, 'INSERTED', CURRENT_TIMESTAMP 
  FROM inserted
END

  if exists(SELECT * FROM deleted)
BEGIN
  INSERT INTO ActivityMainAudit (
    activityId, 
    activityName, 
    projectId, 
    costToDate, 
    activityStatus, 
    startDate, 
    endDate, 
    Operation, 
    DateTimeStamp
    )
  SELECT activityId, activityName, projectId, costToDate, activityStatus, startDate, endDate, 'DELETED', CURRENT_TIMESTAMP 
  FROM deleted
END
END;
GO



CREATE VIEW vw_TableNoIndexes
AS
SELECT name, create_date
FROM sys.objects
WHERE (type = 'U') AND (object_id NOT IN( SELECT object_id FROM sys.indexes));
GO



CREATE VIEW vw_ProjectIdTables
AS
SELECT DISTINCT SO.name AS object_name     
FROM sys.objects SO INNER JOIN sys.columns SC ON SO.object_id=SC.object_id
WHERE SC.name LIKE '%projectid%'; 
GO



CREATE VIEW vw_Last7Obj
AS
SELECT name AS object_name, modify_date  
FROM sys.objects  
WHERE modify_date > GETDATE() - 7; 
GO



CREATE VIEW vw_ProjectProcs
AS
SELECT name AS proc_name, SM.definition, create_date    
FROM sys.objects SO INNER JOIN sys.sql_modules SM ON SM.object_id = SO.object_id  
WHERE SM.definition LIKE '%project%'; 
GO



CREATE PROCEDURE Sp_ActiveConnections
@databasename varchar(250)
AS
SELECT  db_name(dbid) DatabaseName, count(spid) NumberOfConnections, LoginName
FROM sys.sysprocesses
WHERE db_name(dbid)=@databasename
GROUP BY db_name(dbid), LoginName;
GO

EXEC Sp_ActiveConnections 'Rose_Z2969824'
GO

CREATE PROCEDURE Sp_LogFileStatus
@databasename varchar(250)
AS
SELECT  db_name(database_id) DatabaseName, sum(size*iif(type_desc='LOG', 1, 0)) LogSize, sum(size) TotalSize
FROM sys.master_files
WHERE (db_name(database_id)=@databasename)
GROUP BY db_name(database_id);
GO

EXEC Sp_LogFileStatus 'Rose_Z2969824'
GO