Produce a distributed data design for enterprise

Assignment Help Database Management System
Reference no: EM13752625

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)


• 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: EM13752625

Exchange commission edgar database

Select a publicly traded corporation for which you would like to work or are currently working. Research the corporation on its own Website, the public filings on the Securi

What stage of water watch would you put ellis in

This is REAL data. Ellis, KS is currently listed in a Stage 3 Water Emergency. Some argue that this is not reflective of the actual situation. What stage of water watch wo

Validate the logical model using normalization

Your Learning Team has been hired as database consultants for a start-up company that will stream movies over the Internet (similar to Hulu, Netflix, and others). Validate t

Analyze the database environment

Analyze the database environment, including its objectives (why will it be built?), its boundaries (what is in and out of scope?), its constraints (what rules must it follow

How to design and create tables

Databases are powerful tools for storing, managing, manipulating, and accessing data. Most information systems in use today make use of some form of database. In this less

Derive an efficient computation method

Discovery-driven cube exploration is a desirable way to mark interesting points among a large number of cells in a data cube. Derive an efficient computation method to identif

Construct an e-r diagram for the database

Consider a database used to record the marks that students get on different examinations of different course offerings. Construct an E-R diagram for the database that models e

Create a supplier database and related reports

Create a supplier database and related reports and queries to capture contact information for potential PC component suppliers that might be used to purchase the equipment y


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd