A hospital has a database system to page physicians. The CIO of the hospital not

Question

A hospital has a database system to page physicians. The CIO of the hospital noticed that there has been a series of inexplicable, suspicious activities on the application and production databases. The CIO wants to know who accessed those databases, who modified data, and who changed the data structure. The CIO also want have an audit trail for all those activities but he isn't interested in a historical data change trail. As a database consultant, you are hired to design and implement a DBMS to meet the CIO's requirement. Phase 1 The application database schema is attached as bellows. Alert Schedule PK Alert n First Name Last Name Moble Number Pager Alert Times Alert Status Alter Count Response FK1 Physician ID A senior database expert designed an auditing model for this case and kindly shares it with you. App_Audit_User PK UserID App Audit tables PK IableID Table Name User name App Audit_Action FK1 Table ID FK2 User ID FK3 Action Type ID Aud Start Date Aud_Expiration_Date HO- Aud Ins DTTM Aud_Upd_DTTM Aud_Upd_User App Audit_Data PK | Audit Data App_Audit_Action_Type PK Action ypeID Aud Ins DTTM Aud_Upd_DTTM App Upd_User FK1 Audit Action ID Action_Type Desc Note: 1) You are NOT required to strictly follow this model. You may modify it or create your own auditing model. You are fine as long as your solution satisfies the user requirement. 2). App Audit tables - stores the tables information to be audited. 3). App_Audit User - stores the user information to be audited.

Explanation / Answer

If you have any doubts, please give me comment...

CREATE TABLE Physician(
Physician_ID INTEGER NOT NULL PRIMARY KEY,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Mobile_Number INTEGER,
Pager_Number INTEGER
);

CREATE TABLE Alert_Schedule(
Alert_ID INTEGER NOT NULL PRIMARY KEY,
Alert_Times INTEGER,
Alert_Status TINYINT,
Alter_Count INTEGER,
Response VARCHAR(50),
Physician_ID INTEGER,
FOREIGN KEY(Physician_ID) REFERENCES Physician(Physician_ID)
);

CREATE TABLE App_Audit_tables(
Table_ID INTEGER NOT NULL PRIMARY KEY,
Table_Name VARCHAR(100)
);

CREATE TABLE App_Audit_User(
User_ID INTEGER NOT NULL PRIMARY KEY,
User_name VARCHAR(50)
);

CREATE TABLE App_Audit_Action_Type(
Action_Type_ID INTEGER NOT NULL PRIMARY KEY,
Action_Type_Desc VARCHAR(255)
);

CREATE TABLE App_Audit_Action(
Audit_Action_ID INTEGER NOT NULL PRIMARY KEY,
Table_ID INTEGER,
User_ID INTEGER,
Action_Type_ID INTEGER,
Aud_Start_Date DATE,
Aud_Expiration_Date DATE,
Aud_Ins_DTTM DATETIME,
Aud_Upd_DTTM DATETIME,
Aud_Upd_User INTEGER
FOREIGN KEY(Table_ID) REFERENCES App_Audit_tables(Table_ID),
FOREIGN KEY(User_ID) REFERENCES App_Audit_User(User_ID),
FOREIGN KEY(Action_Type_ID) REFERENCES App_Audit_Action_Type(Action_Type_ID)
);

CREATE TABLE App_Audit_Data(
Audit_Data_ID INTEGER NOT NULL PRIMARY KEY,
Aud_Ins_DTTM DATETIME,
Aud_Upd_DTTM DATETIME,
App_Upd_User INTEGER,
Audit_Action_ID INTEGER,
FOREIGN KEY(Action_Type_ID) REFERENCES App_Audit_Action_Type(Action_Type_ID)  
);

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.