View the step-by-step solution to:

A multinational engineering company has decided to distribute its project management information at the regional level in mainland Britain. The...


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

Employee (NIN, fName, lName, address, DOB, sex, salary, taxCode, deptNo)
Department (deptNo, deptName, managerNIN, businessAreaNo, regionNo)
Project (projNo, projName, contractPrice, projectManagerNIN, deptNo)
WorksOn (NIN, projNo, hoursWorked)
Business (businessAreaNo, businessAreaName)
Region (regionNo, regionName)

where Employee contains employee details and the national insurance number NIN is the key.
Department contains department details and deptNo is the key. managerNIN identifies the employee who is the manager of the department. There is only one manager for each department.
Project contains details of the projects in the company and the key is projNo. The project manager is identified by the projectManagerNIN, and the department responsible for the project by deptNo.
WorksOn contains details of the hours worked by employees on each project and (NIN, projNo) forms the key.
Business contains names of the business areas and the key is businessAreaNo.
and Region contains names of the regions and the key is regionNo.

Departments are grouped regionally as follows:
Region 1: Scotland; Region 2: Wales; Region 3: England

Information is required by business area, which covers: Software Engineering, Mechanical Engineering and Electrical Engineering. There is no Software Engineering in Wales and all Electrical Engineering departments are in England. Projects are staffed by local department offices.

As well as distributing the data regionally, there is an additional requirement to access the employee data either by personal information (by Personnel) or by work related information (by Payroll).

a) Draw an Entity-Relationship (ER) diagram to represent this system using UML notation.
Using the ER diagram prepared in (a) above, produce a distributed database design for this system, and include:
b) A suitable fragmentation schema for the system
c) In the case of primary horizontal fragmentation, a minimal set of predicates
d) The reconstruction of global relations from fragments

State any assumptions necessary to support your design.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question