Diagram a distributed database environment in Microsoft® Visio® using the "DreamHome Case Study" in Appendix A of Database Systems: A Practical Approach to Design, Implementation and Management (6th

Student Name

Phoenix University

Advanced Database Architecture

This article is aimed at defining the maintenance and security components necessary for the data models created for the “DreamHome Case Study” and the “Object Oriented Data Model and SQL Query Definition” of the DreamHome Case Study. The article also goes further in explaining how data replication will be used in the system and also effective and efficient recovery strategy. Before we look at security, we first need to define it.

According to Begg C and Connolly T (2015) security in relation to databases “refers to the protection of the database against unauthorized access, either intentional or accidental”. There are various security breaches that can happen to a database such as a hacker accessing the data over the network, an authorized user inserting the wrong data, virus infecting the database leading to Denial of Service (DoS) to other users etc. All these security breaches may lead to the following situations: theft and fraud of the data, loss of confidentiality of the database, loss of privacy of the data, loss of integrity of the database, and loss of availability of the database. Therefore, it is important for us to ensure that the DreamHome database is secure by ensuring various security controls such as authorization and authentication, access controls, backup and recovery, integrity and encryption. Let us look at each security component and map it to the DreamHome case study.

According to Begg C and Connolly T (2015) authorization is “the granting of a right or privilege that enables a subject to have legitimate access to a system or a system’s object”. Here the “subject” is viewed as the user and the “system’s object” is viewed as the database table, view, procedure, trigger etc.

According to Begg C and Connolly T (2015) authentication is “a mechanism that determines whether a user is who he or she claims to be.”

According to Pfleeger C et al (2015), user authentication is whereby every user is positively identified for the permission to access certain data in the database.

All users using the DreamHome database should have an account and password that will authenticate them to use the database. For example, the owner of a property will have different account and password compared to staff of the organization. The same will also apply for staff, managers and clients who are willing to buy properties.

According to Begg C and Connolly T (2015) the typical way of providing access controls for a database such as DreamHome is by granting and revoking of privileges. Beg C and Connolly T (2015) point out that a “privilege allows a user to create or access (that is read, write, or modify) some database object (such as a relation, view, or index) or to run certain DBMS utilities.

We need to ensure the security of the data by granting and revoking access to users of the DreamHome database. For example, a normal staff cannot be granted the right to read or write other staff salary apart from the manager. Property owner should not be able to see staff details such as “date of birth” etc. apart from the name of the staff who will be leasing his property to clients.

According to Begg C and Connolly T (2015) backup is “the process of periodically copying of the database and log file (and possibly programs) to offline storage media.” Dream Home database ought to be backed up so that it can be restored in the event of any failure such as database malfunction.

According to Begg C and Connolly T (2015) integrity constraints maintains a secure database system by preventing data from becoming invalid, and therefore, giving misleading or incorrect results.

According to Israni R (2016) “database integrity refers to the validity and consistency of stored data”. Israni R (2016) goes further in explaining that “integrity constraints ensure that changes (update, deletion, insertion) made to the database by authorized users do not result in a loss of data consistency”. DreamHome database ensures integrity by providing primary keys that are not null and also foreign keys. For example, we can see from week 1 diagram of DreamHome that the managers’ table has got a primary key of manager_no etc. Another constraint we can see is that a staff member can only manage 100 properties at a time. The DreamHome diagram also depicts another constraint where relationship exists. For example, a manager manages a branch etc.

According to Begg C and Connolly T (2015) encryption is “the encoding of the data by special algorithm that renders the data unreadable by any program without the decryption key”. In order to ensure security of the data, DreamHome database will ensure encryption of the data while is being transmitted over the network.

According to Pandey H (2018) data replication “is the process of storing data in more than one site or node”. Data replication will be used in our system whereby all branches will have the same data and they will all be interconnected. In case of a DBMS failure from one branch, they can still access the same data from the other branches.

According to Begg C and Connolly T (2015) database recovery is “the process of restoring the database to a correct state in the event of a failure”.

We will use the recovery technique using deferred update whereby updates will not be written to the database until after a transaction is complete. This is important because in case of any failure, is either the transaction commits or it rolls back.

Begg, C & Connolly, T (2015). Database Systems. A practical approach to design, implementation, and management. Retrieved from http://people.stfx.ca/x2011/x2011asx/5th%20Year/Database/Database%20Management%20Textbook.pdf

Israni, R. (2016). Explain types of integrity constraints with example. Retrieved from http://www.ques10.com/p/17134/explain-types-of-integrity-constraints-with-exampl/

Pandey, H. (2018). DBMS Data replication. Retrieved from https://www.geeksforgeeks.org/dbms-data-replication/

Pfleeger, C., Pfleeger, S., & Margulies, J. (2015). Security in computing. Retrieved from https://ahsanghazi.files.wordpress.com/2017/03/263973122-security-in-computing-5-e-charles-p-pfleeger-pdf1.pdf