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 customers 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 for 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 single Microsoft Word document that fully responds to all the following prompts in 750-1,000 words:
1. Create a new Employee table with a minimum of 5 employees. Include the following columns: empID, empName, empGender, empPhone, empDOB, and empWorkLocation. Take a screenshot.
2. Explain and apply horizontal partitioning to this table. Take a screenshot validating that the partitioning has been completed.
3. Write a SQL query to reconstruct the complete table after partitioning. Take a screenshot of the query and its results.
4. Explain and apply vertical partitioning to this table. Take a screenshot validating that the partitioning has been completed.
5. Write a SQL query to reconstruct the complete table after partitioning. Take a screenshot of the query and its results.
6. Explain how and why data replication should be addressed in this environment. What replication strategies are available and how do they work? How do data replication/partitioning impact reliability, expandability, communications overhead cost, manageability, and data consistency?
7. Explain how and why data partitioning and replication impact the creation of IT and technology infrastructures, creation of plans and schedules, measuring performance, and ensuring quality.
Submit the following in a single Microsoft Word document through the digital classroom. Include a one-line description above each screenshot.
• Screenshot of Employee table
• Paragraph explaining horizontal partitioning
• Screenshot of SQL query and result to apply horizontal partitioning
• Screenshot of SQL query and result to reconstruct the complete table
• Paragraph explaining vertical partitioning
• Screenshot of SQL query and result to apply vertical partitioning
• Screenshot of SQL query and result to reconstruct the complete table
• Paragraph explaining data replication, strategies, and impacts
• Paragraph explaining how and why data partitioning and replication impact the creation of IT and technology infrastructures, creation of plans and schedules, measuring performance, and ensuring quality
