Database Partitioning, Replication, and Transparency
Assessment Description
You are working as a data engineer at an enterprise that has multiple branches across the U.S. The enterprise needs to maintain information about the customer of each branch, as well as administrative information about its offices. Your job is to design a data distribution plan in which each branch can maintain information in its local database and its customers but also access customer data from other branches without having to store that information in its local database. In addition, the headquarters of the enterprise can maintain administrative information about all the branches in its enterprise. Assume that your enterprise has two branches, one in Phoenix and one in Denver.
For this activity, provide a table, screenshots, and a 825 word document addressing the following:
1. Create a new Employee table with a total of 5 employees. Include columns such as empID, empName, empGender, empPhone, empDOB, location.
2. Explain how you would apply horizontal partitioning to this table and write a SQL query to reconstruct the complete table after partitioning.
3. Explain how you would apply vertical partitioning to this table and write a SQL query to reconstruct the complete table after partitioning.
4. Explain how and why data replication should be addressed in this environment. What replication strategies are available and how do they work?
5. List and explain a minimum of three distributed database transparency features that should be applied to this environment.
Prepare this assignment according to the guidelines found in the APA Style Guide, located in the Student Success Center. An abstract is not required.
An example of paper is in the Attachment as a PDF and I also need the screenshots of the work
Database Partitioning, Replication, and Transparency
Student Name:
Institutional Affiliation:
Corse Name
Professor’s Name:
Date:
Database Partitioning, Replication, and Transparency
Database
This table stores the employee information such as ID, name, gender, phone number, date of birth, and location (branch) for each employee. With this setup, each branch can maintain its own local database of employees, and the headquarters can access and maintain information about all employees in the enterprise.
Partitioning
Horizontal partitioning is a technique in which a table is split into multiple smaller tables, each containing a portion of the data based on a specific column value (Mahmud et al., 2020). In this case, the column that can be used for partitioning is location. It can be used to partition the Employee table into two separate tables such as Phoenix and Denver employees. The complete table after partitioning can then be constructed using the UNION clause to combine the data from both tables.
In contrast, vertical partitioning is a technique in which a table is split into multiple smaller tables, each containing a subset of columns from the original table (Mahmud et al., 2020). This technique is used to optimize query performance and reduce data redundancy. The Employee table can be partitioned into two separate tables, Employee_Info and Employee_Contact tables. In this regard, the Employee_Info table can be used to store data relating to an employee such as their name, gender, and date of birth, among other details. The Employee_Contact table can then be used to store contact data such as employee phone number and it can be related to the Employee_Info table using a foreign key.
Replication
Data replication is the process of copying data from one database to another, which allows multiple instances of the same data to exist in different locations. It is useful in an environment with multiple branches, as it provides a way to share customer and administrative information between branches and the headquarter. There are several reasons why data replication should be addressed in the current scenario. For instance, according to Pramudita et al. (2020), replication can help to improve the availability of data. In this case, with multiple copies of the data stored in different locations, there is less risk of data loss or downtime in the event of a single database failure. Moreover, data replication can improve the performance of the database. Distributing data across multiple databases can help to reduce the load on any one database, improving query performance. Further, data replication provides a means of recovering data in the event of a disaster at one of the branches or headquarters.
There are several different strategies for data replication. For example, the ma...