Produce a distributed data design for enterprise

Assignment Help Database Management System

Reference no: EM13752627

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.

Reference no: EM13752627

Questions Cloud

How should organizations respond to security threats : How should organizations respond to security threats and Discuss some human safeguards for employees that can ensure the security of information systems

Differences between types of costs : Discussion questions-Differences Between Types of Costs

Find out the project equivalent annual cost : A five-year project has an initial fixed asset investment of $355,000, an initial NWC investment of $39,000, and an annual OCF of -$38,000. The fixed asset is fully depreciated over the life of the project and has no salvage value. If the required..

Who was father miguel hidalgo y costilla : Who was Father Miguel Hidalgo y Costilla? What was the "Grito de Dolores"? Where did Simón Bolívar come from? Who was José de San Martín?

Produce a distributed data design for enterprise : 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

What is the basis of international trade : What is the basis of international trade and what are the benefits and the costs? Under what conditions would you advocate for trade restrictions?

Means of compensating production : The ________ pay plan has long been popular as a means of compensating production workers by paying a fixed sum for each unit of production completed.

How can the falling us dollar impact your travel expenses : How can the falling U.S. dollar impact your travel expenses and Why would a cheap dollar relative to other nations' currencies be good or bad for U.S. trade?

User Account

All Pages