Data Distribution Strategy
This assignment uses a GCU-provided virtual machine.
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.
View the “Distributed Databases” video within the “Video Playlist: Relational Databases for Business Applications,” located in the topic Resources.
For this activity, provide a single Microsoft Word document that fully responds to all the following prompts in 750-1,000 words:
- 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.
- Explain and apply horizontal partitioning to this table. Take a screenshot validating that the partitioning has been completed.
- Write a SQL query to reconstruct the complete table after partitioning. Take a screenshot of the query and its results.
- Explain and apply vertical partitioning to this table. Take a screenshot validating that the partitioning has been completed.
- Write a SQL query to reconstruct the complete table after partitioning. Take a screenshot of the query and its results.
- 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?
- 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
While APA style is not required for the body of this assignment, solid academic writing is expected, and documentation of sources should be presented using APA formatting guidelines, which can be found in the APA Style Guide, located in the Student Success Center.
This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
You are required to submit this assignment to LopesWrite. A link to the LopesWrite technical support articles is located in Class Resources if you need assistance.