Designing An Essay On Database System For Incident Management In Identity Theft.

This new system would need a database designed that would help with some appropriate data about all incidents. It is crucial in remembering the victims of all kinds of crimes are quite reticent in sharing all personal information. Hence, this is crucial that this database is designed in holding a minimum amount of personal information. This database would fit the purpose and would effectively as well as efficiently collect data about the incidents of identity compromise, notify all appropriate agencies as well as the organization of such incidents along with responding to all needs of all victims. The core entities of this data are to be agencies or organizations with the incident of identity compromise, along with the clients. There would be composite as well as business rules, along with business rules that would determine all entity relationships.

This database consists of 5 entities, and all of these entities include several attributes for storing crucial data within this database system. The organization entity consists of several attributes such as organization_id, organization_name, address, phone_number, email, contact_name, business_sector, description and specialty, where organization_id is the primary key of this entity. The client entity consists of several attributes such as client_id, client_name, address, phone and email, where client_id is the primary key of this entity. The incident entity consists of several attributes such as incident_id, client_id, organization_id, date_of_report, date_resolved, and type_of_information, where incident_id is the primary key and client_id, as well as organization_id, are the foreign keys of this entity. The incident_details entity consists of several attributes such as incident_id, date_of_incident, time_of_incident, description, incident_process_found, number_of_credentials, date_incident_found, and use_of_internet where incident_id is the primary key of this entity. The message entity consists of several attributes such as message_id, client_id, organization_id, date_stamp, and incident_message_alert where message_id is the primary key and client_id, as well as organization_id, are the foreign keys of this entity.

Client-> Client_ID (PK), Client_Name, Email, Address, Phone

Organization-> Organization_ID (PK), Organization_Name, Address, Phone_Number, Contact_Name, Email, Business_Sector, Description, Speciality

Incident-> Incident_ID (PK), Client_ID (FK), Organization_ID (FK), Date_of_Report, Date_Resolved, Type_of_Information

Incident_Details-> Incident_ID (PK, FK), Date_of_Incident, Date_Incident_Found, Description, Use_of_Internet, Time_of_Incident, Number_of_Credentials, Incident_Process_Found

Message-> Message_ID (PK), Client_ID (FK), Organization_ID (FK), Date_Stamp, Incident_Message_Alert

Client

Field Name	Data Type	Field Length	Constraint	Format	Acceptable values	Accepts null value
Client_ID	Integer	20	Primary Key	NNNN	N/A	No
Client_Name	Text	50	-	-	N/A	No
Address	Text	50	-	-	N/A	No
Phone	Integer	20	-	-	N/A	No
Email	Text	50	-	-	N/A	No

Organization

Field Name	Data Type	Field Length	Constraint	Format	Acceptable values	Accepts null value
Organization_ID	Integer	20	Primary Key	NNNN	N/A	No
Organization_Name	Text	50	-	-	N/A	No
Contact_Name	Text	50	-	-	N/A	No
Address	Text	50	-	-	N/A	No
Phone_Number	Integer	20	-	-	N/A	No
Email	Text	50	-	-	N/A	No
Business_Sector	Text	50	-	-	N/A	No
Description	Text	50	-	-	N/A	No
Speciality	Text	50	-	-	N/A	No

Incident

Field Name	Data Type	Field Length	Constraint	Format	Acceptable values	Accepts null value
Incident_ID	Integer	20	Primary Key	NNNN	N/A	No
Client_ID	Integer	20	Foreign Key	NNNN	N/A	No
Organization_ID	Integer	20	Foreign Key	NNNN	N/A	No
Date_of_Report	Date	-	-	YYYY-MM-DD	N/A	No
Date_Resolved	Date	-	-	YYYY-MM-DD	N/A	No
Type_of_Information	Text	50	-	-	N/A	No

Incident_Details

Field Name	Data Type	Field Length	Constraint	Format	Acceptable values	Accepts null value
Incident_ID	Integer	20	Primary Key	NNNN	N/A	No
Date_of_Incident	Date	-	-	YYYY-MM-DD	N/A	No
Time_of_Incident	Text	50	-	-	N/A	No
Date_Incident_Found	Date	-	-	YYYY-MM-DD	N/A	No
Description	Text	50	-	-	N/A	No
Incident_Process_Found	Text	50	-	-	N/A	No
Number_of_Credentials	Integer	20	-	-	N/A	No
Use_of_Internet	Text	50	-	-	N/A	No

Message

Field Name	Data Type	Field Length	Constraint	Format	Acceptable values	Accepts null value
Message_ID	Integer	20	Primary Key	NNNN	N/A	No
Client_ID	Integer	20	Foreign Key	NNNN	N/A	No
Organization_ID	Integer	20	Foreign Key	NNNN	N/A	No
Date_Stamp	Date	-	-	YYYY-MM-DD	N/A	No
Incident_Message_Alert	Text	50	-	-	N/A	No

The assumptions for the foundations for all functionality as well as future enhancements for this database system are:

• The agencies or organizations associated with the incident of identity compromise would consist of a unique id, a contact name, a name, address, email address, a phone number, along with the type of business sector. Also, there would be the opportunity for the description of the agency or organization along with their specialty.
• There would be a unique incident id for the incidents. All recorded details would consist of the time as well as the date of all incidents, the date when the incident was discovered by the victim, how the victims found out, how many pieces of information were compromised during those incidents, and if the internet was used during the incident. The description of those incidents would be recorded also. The incidents would include the compromise of several pieces of credentials or personal information. The kinds of credentials, as well as personal information which have been compromised in the incidents, would be recorded.
• The client would be provided with a unique client id. The information shared by the clients would include personal as well as sensitive information. Hence, whilst this is crucial that the contact information of the client is recorded, this is also crucial in ensuring that the minimum amount of personal information of the client is only recorded. Hence, the only personal information of the clients that would be stored would be the name of the client, address, along with their email address and phone number.
• The date any incident was reported along with the date that the incident was resolved would be recorded as well. It is for offering the progress feedback to the clients.
• Whilst one client would be involved in each incident, this would be recognized that every client should be unfortunate enough in experiencing several incidents. Every incident could involve several different kinds of personal information of the clients. Due to this, several kinds of personal information of the clients might or mightn’t be compromised several times in the course of several incidents happening.
• It must be also recognized that resolving the incident might include several agencies or organizations. However, every agency or organization might or mightn’t need in being involved to resolve several incidents.
• When any incident is reported along with all details of that incident would be added to this database, the alert system would be sent automatically to every agency or organization that issued the impacted credential. It would help in enabling speedy cancellation along with a reissue of all impacted credentials. It would be done with the use of the messaging system. This messaging system would be efficient as well as centred on the message entity. This message entity would consist of a unique message id, organization id and client id. This message entity would also consist of a field for incident message alert and a field for the date stamp. This format would provide the basis upon which all messages would be sent to all appropriate organizations, along with the flexibility for using it in several ways in future.

create table if not exists `Identity_Theft`.`Client` (`Client_Id` int(11) not null, `Client_Name` varchar(45) null default null, `Address` varchar(45) null default null, `Phone` int(11) null default null, `Email` varchar(45) null default null, primary key (`Client_Id`));

create table if not exists `Identity_Theft`.`Organization` (`Organization_Id` int(11) not null, `Organization_Name` varchar(45) null default null, `Contact_Name` varchar(45) null default null, `Phone__Number` int(11) null default null, `Email` varchar(45) null default null, `Business_Sector` varchar(45) null default null, `Address` varchar(45) null default null, `Description` varchar(45) null default null, `Specialty` Varchar(45) null default null, primary key (`Organization_Id`));

create table if not exists `Identity_Theft`.`Incident` (`Incident_Id` int(11) not null, `Date_Of_Report` datetime null default null, `Date_Resolved` datetime null default null, `Type_Of_Information` varchar(45) null default null, `Client_Id` int(11) not null, `Organization_Id` int(11) not null, primary key (`Incident_Id`), index `Fk_Incident_Client1_Idx` (`Client_Id` Asc) visible, index `Fk_Incident_Organization1_Idx` (`Organization_Id` Asc) visible, constraint `Fk_Incident_Client1` foreign key (`Client_Id`) references `Identity_Theft`.`Client` (`Client_Id`) on delete no action on update no action, constraint `Fk_Incident_Organization1` foreign key (`Organization_Id`) references `Identity_Theft`.`Organization` (`Organization_Id`) on delete no action on update no action);

Assumptions for Functionality

create table if not exists `Identity_Theft`. `Incident_Details` (`Incident_Details` int(11) null, `Date_Of_Incident` datetime null default null, `Time_Of_Incident` Varchar(45) null default null, `Description` Varchar(45) null default null, `Incident_Process_Found` varchar(45) null default null, `Number_Of_Credentials` int(11) null default null, `Date_Incident_Found` datetime null default null, `Use_Of_Internet` varchar(45) null default null, `Incident_Id` int(11) not null, index `Fk_Incident_Details_Incident1_Idx` (`Incident_Id` Asc) visible, primary key (`Incident_Id`), constraint `Fk_Incident_Details_Incident1` foreign key (`Incident_Id`) references `Identity_Theft`.`Incident` (`Incident_Id`) on delete no action on update no action);

create table if not exists `Identity_Theft`.`Message` (`Message_Id` int(11) not null, `Date_Stamp` datetime null default null, `Incident_Message_Alert` varchar(45) null default null, `Organization_Id` int(11) not null, `Client_Id` int(11) not null, primary key (`Message_Id`), index `Fk_Message_Organization_Idx` (`Organization_Id` Asc) visible, index `Fk_Message_Client1_Idx` (`Client_Id` Asc) visible, constraint `Fk_Message_Organization` foreign key (`Organization_Id`) references `Identity_Theft`.`Organization` (`Organization_Id`) on delete no action on update no action, constraint `Fk_Message_Client1` foreign key (`Client_Id`) eeferences `Identity_Theft`.`Client` (`Client_Id`) on delete no action on update no action);

create trigger msg before insert on message for each row insert into message values (new.message_id, new.date_stamp, new.incident_message_alert, new.organization_id, new.client_id);

insert into client values (101, 'Yathartha Rana', '21 Rawdon Street, Burns, 2003', 0456564456, '[email protected]');

insert into client values (102, 'Percy White', '1 Early Street, Grange, 4051', 0476123456, '[email protected]');

insert into client values (103, 'Joe Spent', '17 Hard Road, Manly, 2095', 0403987654, '[email protected]');

insert into client values (104, 'Rachel Davis', '3 Air Crescent, Coolum, 4573', 0409654321, '[email protected]');

insert into client values (105, 'Melanie Green', '53 Middle St., Preston, 3072', 0476963258, '[email protected]');

insert into client values (106, 'Rich Cooke', '352 Long Road, Cairns, 4870', 0453741852, '[email protected]');

insert into client values (107, 'Sara Hajari', '34 George Street, Thames, 2031', 0493579544, '[email protected]');

insert into client values (108, 'Tim David', '65 Washignton Street, Ornaldo, 5644', 0434754334, '[email protected]');

insert into client values (109, 'Sara Nath', '53 Cumberbatch Street, Wales, 2076', 0447834566, '[email protected]');

insert into client values (110, 'David Smith', '44 Wellignton Street, Dalton, 2444', 0428855743, '[email protected]');

insert into organization values (501, 'Nationwide Bank', 'Max Henry', 0712345678, '[email protected]', 'Banking', '33 Adams Street Canberra', 'Transactions', 'Credits');

insert into organization values (502, 'Dept. of Foreign Affairs and Trade', 'Ella Starr', 0398712365, '[email protected]', 'Passport Office', '67 Georgia Street Hobart', 'Trading', 'Business credits');

insert into organization values (503, 'QBank', 'Art Redmond', 0795123578, '[email protected]', 'Banking', '77 Elgin Road Sydney', 'Transactions', 'Credits');

insert into organization values (504, 'Australian Tax Office', 'Dan McKenzie', 1800364987, '[email protected]', 'Tax Dept.', '382 Rollins Street Victoria', 'Trading', 'Income files');

insert into organization values (505, 'Queensland Bank', 'Ben Stock', 789993344, '[email protected]', 'Banking', '66 Aeros Street Canberra', 'Transactions', 'Credits');

insert into organization values (506, 'Sydney RTO Office', 'Liza Affens', 378890054, '[email protected]', 'Tax Dept.', '533 Roman Street Sydney', 'Trading', 'Income files');

insert into organization values (507, 'Commonwealth Bank', 'Diana Thames', 876567908, '[email protected]', 'Banking', '345 Edith Street Melbourne', 'Transactions', 'Credits');

insert into organization values (508, 'Maxwell NGO', 'Steve Trevor', 567865467, '[email protected]', 'NGO', '44 Dealt Road Canberra', 'Well-beings', 'Personal profiles');

insert into incident values (1001, '2022-04-10', '2022-04-17', 'Address', 101, 501);

insert into incident values (1002, '2022-02-14', '2022-02-20', 'Passport information', 102, 502);

insert into incident values (1003, '2022-03-23', '2022-03-28', 'Medicare information', 107, 503);

insert into incident values (1004, '2022-02-24', '2022-03-01', 'Signature', 103, 504);

Conclusion

insert into incident values (1005, '2022-03-21', '2022-03-25', 'Password', 105, 505);

insert into incident values (1006, '2022-03-12', '2022-03-17', 'Student number', 103, 506);

insert into incident values (1007, '2022-04-06', '2022-04-10', 'Bank account information', 107, 507);

insert into incident values (1008, '2022-02-24', '2022-03-02', 'Biometric information', 104, 508);

insert into incident values (1009, '2022-03-17', '2022-04-03', 'Tax file number', 107, 502);

insert into incident values (1010, '2022-03-13', '2022-03-19', 'Shareholder identification number', 109, 503);

insert into incident values (1011, '2022-03-13', '2022-03-24', 'Gender', 104, 502);

insert into incident values (1012, '2022-02-13', '2022-02-21', 'Medicare information', 103, 505);

insert into incident values (1013, '2022-01-13', '2022-02-09', 'Biometric information', 105, 507);

insert into incident values (1014, '2022-02-13', '2022-02-25', 'Passport information', 110, 507);

insert into incident values (1015, '2022-03-13', '2022-03-24', 'Tax file number', 108, 503);

insert into incident_details values ('2022-04-06', '11:30 AM', 'Address', 'Tracking bills', 3, '2022-04-08', 'No', 1001);

insert into incident_details values ('2022-02-10', '10:34 AM', 'Passport information', 'Tracking bills', 2, '2022-02-08', 'No', 1002);

insert into incident_details values ('2022-03-18', '02:30 PM', 'Medicare information', 'Tracking bills', 3, '2022-03-10', 'No', 1003);

insert into incident_details values ('2022-02-18', '06:56 AM', 'Signature', 'Checking bank statement', 4, '2022-02-11', 'Yes', 1004);

insert into incident_details values ('2022-03-20', '10:30 PM', 'Password', 'Checking bank statement', 1, '2022-03-21', 'Yes', 1005);

insert into incident_details values ('2022-03-06', '07:34 AM', 'Student number', 'Tracking bills', 2, '2022-03-07', 'No', 1006);

insert into incident_details values ('2022-04-01', '11:30 PM', 'Bank account information', 'Checking bank statement', 3, '2022-04-02', 'Yes', 1007);

insert into incident_details values ('2022-02-18', '10:33 AM', 'Biometric information', 'Tracking bills', 4, '2022-02-19', 'No', 1008);

insert into incident_details values ('2022-03-09', '09:53 PM', 'Tax file number', 'Checking bank statement', 2, '2022-03-10', 'Yes', 1009);

insert into incident_details values ('2022-03-04', '08:34 AM', 'Shareholder information number', 'Checking bank statement', 3, '2022-03-05', 'Yes', 1010);

insert into incident_details values ('2022-03-02', '10:53 PM', 'Gender', 'Tracking bills', 2, '2022-02-03', 'No', 1011);

insert into incident_details values ('2022-02-03', '11:42 PM', 'Medicare information', 'Tracking bills', 1, '2022-02-04', 'Yes', 1012);

insert into incident_details values ('2022-01-04', '08:46 AM', 'Biometric information', 'Tracking bills', 3, '2022-03-05', 'No', 1013);

insert into incident_details values ('2022-02-06', '11:30 AM', 'Passport information', 'Tracking Bills', 3, '2022-04-08', 'No', 1014);

insert into incident_details values ('2022-03-02', '11:30 AM', 'Tax file number', 'Tracking Bills', 3, '2022-04-08', 'No', 1015);

insert into message values (2001, '2022-04-10', 'Address', 501, 101);

insert into message values (2002, '2022-02-14', 'Passport information', 502, 102);

insert into message values (2003, '2022-03-23', 'Medicare information', 503, 107);

insert into message values (2004, '2022-02-24', 'Signature', 504, 103);

insert into message values (2005, '2022-03-21', 'Password information', 505, 105);

insert into message values (2006, '2022-03-12', 'Student number', 506, 104);

insert into message values (2007, '2022-04-06', 'Bank account information', 507, 107);

insert into message values (2008, '2022-02-24', 'Biometric information', 508, 104);

insert into message values (2009, '2022-03-17', 'Tax file number', 502, 107);

insert into message values (2010, '2022-03-13', 'Shareholder identification number', 503, 109);

insert into message values (2011, '2022-03-13', 'Gender', 502, 104);

insert into message values (2012, '2022-02-13', 'Medicare information', 505, 103);

insert into message values (2013, '2022-01-13', 'Biometric information', 507, 105);

insert into message values (2014, '2022-02-13', 'Passport information', 507, 110);

insert into message values (2015, '2022-03-11', 'Tax file number', 503, 108); 

Display organization id, organization name, incident id, date, number of credentials compromised, client name, and address for all incidents reported between 15^th March 2022 and 10^th April 2022. The report would be grouped by the organization name and ordered in consecutive date order.

select incident.incident_id, incident_details.date_of_incident, incident_details.number_of_credentials, client.client_name, client.address, organization.organization_id, organization.organization_name from incident, incident_details, client, organization where incident.incident_id=incident_details.incident_id and incident.client_id=client.client_id and incident.organization_id=organization.organization_id and incident.date_of_report between '2022-03-15' and '2022-04-10'  group by organization.organization_name order by (incident_details.date_of_incident) asc;

Provide the statement that includes the tutor’s name as client name, client id, date of the incident, time of the incident, organization id, organization name, incident id, incident date, incident time, number of credentials, the overall number of incidents, and average number of credentials impacted per incident.

select client.client_name, client.client_id, incident.date_of_report, organization.organization_id, organization.organization_name, incident_details.incident_id, incident_details.date_of_incident, incident_details.time_of_incident, count(incident.incident_id) as 'Total number of incidents reported',avg(incident_details.number_of_credentials) as 'Average number of credentials'from incident, incident_details, client, organization where incident.incident_id=incident_details.incident_id and incident.client_id=client.client_id and incident.organization_id=organization.organization_id and client_name='Sara Hajari' group by incident.client_id;

Alsharif, A., Kapfhammer, G.M. and McMinn, P., 2018, April. DOMINO: Fast and effective test data generation for relational database schemas. In 2018 IEEE 11th International Conference on Software Testing, Verification and Validation (ICST) (pp. 12-22). IEEE.

Cappuzzo, R., Papotti, P. and Thirumuruganathan, S., 2020, June. Creating embeddings of heterogeneous relational datasets for data integration tasks. In Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data (pp. 1335-1349).

Chen, X. and Zhang, J., 2021, December. The Applications PHP, HTML and MYSQL in Development of Website–Query Function. In ICMLCA 2021; 2nd International Conference on Machine Learning and Computer Application (pp. 1-4). VDE.

Chren, S., Buhnova, B., Macak, M., Daubner, L. and Rossi, B., 2019, May. Mistakes in UML diagrams: analysis of student projects in a software engineering course. In 2019 IEEE/ACM 41st International Conference on Software Engineering: Software Engineering Education and Training (ICSE-SEET) (pp. 100-109). IEEE.

Christudas, B., 2019. MySQL. In Practical Microservices Architectural Patterns (pp. 877-884). Apress, Berkeley, CA.

Ghosh, S., Mukherjee, P., Chakraborty, B. and Bashar, R., 2018, December. Automated generation of er diagram from a given text in natural language. In 2018 International Conference on Machine Learning and Data Engineering (iCMLDE) (pp. 91-96). IEEE.

Jose, B. and Abraham, S., 2020. Performance analysis of NoSQL and relational databases with MongoDB and MySQL. Materials today: PROCEEDINGS, 24, pp.2036-2043.

Kashmira, P.G.T.H. and Sumathipala, S., 2018, December. Generating entity relationship diagram from requirement specification based on nlp. In 2018 3rd International Conference on Information Technology Research (ICITR) (pp. 1-4). IEEE.

Lubis, J.H. and Zamzami, E.M., 2020, June. Relational database reconstruction from SQL to Entity Relational Diagrams. In Journal of Physics: Conference Series (Vol. 1566, No. 1, p. 012072). IOP Publishing.

Maatuk, A., Ali, A. and Rossiter, N., 2019. A framework for relational database migration.

Matallah, H., Belalem, G. and Bouamrane, K., 2021. Comparative study between the MySQL relational database and the MongoDB NoSQL database. International Journal of Software Science and Computational Intelligence (IJSSCI), 13(3), pp.38-63.

Ni, J., Xie, S., Li, Z. and Jia, C., 2018, August. Optimization design method of cache extension in MySQL database. In 2018 IEEE International Conference on Mechatronics and Automation (ICMA) (pp. 2295-2299). IEEE.

Ongo, G. and Kusuma, G.P., 2018, September. Hybrid database system of MySQL and MongoDB in web application development. In 2018 International Conference on Information Management and Technology (ICIMTech) (pp. 256-260). IEEE.

Setiyadi, A. and Setiawan, E.B., 2018, August. Information System Monitoring Access Log Database on Database Server. In IOP Conference Series: Materials Science and Engineering (Vol. 407, No. 1, p. 012110). IOP Publishing.

Setyawati, E., Wijoyo, H. and Soeharmoko, N., 2020. Relational Database Management System (RDBMS).

van de Put, B., Vaandrager, F.W. and Achten, P., 2018. Scoring Entity-Relationship Diagrams Drawn by a Computer Algorithm.