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), 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 ) Job table (project 1)

create table job ( jobCode char(4), jobdesc varchar(50) ) ProjectMain table (Project 2)

create table ProjectMain

(

projectId char(4),

projectName varchar(50),

firmFedID char(9),

fundedbudget decimal(16,2),

projectStartDate date,

projectStatus varchar(25),

projectTypeCode char(5),

projectedEndDate date,

projectManager char(8),

PRIMARY KEY (projectId)

);

ActivityMain table (Project 2)

Create table ActivityMain

(

projectId char(4),

activityId char(4),

activityName varchar(50),

costToDate decimal(16,2),

activityStatus varchar(25),

startDate date ,

endDate date,

PRIMARY KEY (projectId,activityId),

FOREIGN KEY (projectId ) REFERENCES ProjectMain (projectId )

);

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: 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. Parameters: @databasename varchar(250) Hint: Use sys.master_files from the Database Health Monitoring document. OutPut: DatabaseName LogSize TotalSize COP4703 8 16