Distributed Data Problem

A multinational engineering company has decided to distribute its project management information at the regional level. The current centralized schema is as follows:

Employees( EID, FName, LName, Address, DOB, PayRate, NumDependants, Email, OfficePhone, DeptNum)
Departments (DeptNum, DeptName, ManagerEID, BusinessArea, Region)
Projects (ProjectNum, ProjectName, ContractPrice, ProjectMgrEID, DeptNum)
WorksOn(EID, ProjectNum, HoursWorked)
BusinessAreas(BusinessArea, CostCenter)
Region (Region, RegionMgrEID)

Where:

• There is only one manager per department, per project and per region
• The region associated with the project's Department is responsible for the project
• Employee time spent on each project is stored in Works On.
• Employees may work part-time on multiple projects in a given week. Weeks are identified using standard Week Numbers for the year.
• Employees may assist with projects by working remotely on projects located in departments/regions other than their home office.
• The relevant business Areas for project-related activities are: Software Engineering, Mechanical Engineering and Electrical Engineering.
• Regions include North, South and West
• There is no Software Engineering business area in the North region, and all Electrical Engineering departments are in the West region.
• Information is required by each project, business area and region. Cost center summaries are needed at the enterprise level.
• There is a need for project managers to have access to work contact information (email, phone) for all employees working on their projects.
• The Human Resources department needs access to Personal information for all employees, and the Payroll department needs access to all work and pay related information. Human Resources is located in the North Region, while Payroll works out of offices in the South region.

1. Produce a distributed data design for this enterprise. Show data fragmentation/partitioning and replication for each regional database location. Indicate what attributes are in each fragment.

2. Provide a rationale for the fragmentation and replication design decisions you made.

3. List the steps in an example distributed transaction that takes place across regions. Types of transaction examples might be storing weekly hours worked for an employee on projects in multiple regions, or in a region different than his own department; Transferring an employee to another department/region, or changing hours worked on one project to another project. What kind of transaction you choose will depend on your distributed design - but the transaction must affect data on different regional servers. You do NOT need to write full SQL code for this problem. Just list in order the SELECT, LOCK, UPDATE/INSERT, BEGIN-TRANSACTION and COMMIT steps and indicate which fragment is affected, and at which location.