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

Transcribed Image Text:

A multinational engineering company has decided to distribute its project management information at the regional level in Oman. The current centralized relational schema is as follows: Employee (NIN, fName, IName, address, DOB, gender, salary, taxCode, deptNo) Department (deptNo, deptName, managerNIN, businessAreaNo, regionNo) Project (projNo, projName, contractPrice, project ManagerNIN, 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 project ManagerNIN, 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. Region contains names of the regions and the key is regionNo. Departments are grouped regionally as follows: Region 1: Muscat Governorate 2: Governorate of Dhofar 3: Dakhiliyah Governorate Information is required by business area, which covers three areas: 1- Production, 2-Sales, and 3- Marketing. There is no Production department in Dhofar, and all Sales departments are in Dakhiliyah Governorate only. 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). You are required to produce a distributed database design for this system based on the requirements given above, including: (a) A suitable fragmentation schema for the system. (b) The reconstruction of global relations from fragments. A multinational engineering company has decided to distribute its project management information at the regional level in Oman. The current centralized relational schema is as follows: Employee (NIN, fName, IName, address, DOB, gender, salary, taxCode, deptNo) Department (deptNo, deptName, managerNIN, businessAreaNo, regionNo) Project (projNo, projName, contractPrice, project ManagerNIN, 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 project ManagerNIN, 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. Region contains names of the regions and the key is regionNo. Departments are grouped regionally as follows: Region 1: Muscat Governorate 2: Governorate of Dhofar 3: Dakhiliyah Governorate Information is required by business area, which covers three areas: 1- Production, 2-Sales, and 3- Marketing. There is no Production department in Dhofar, and all Sales departments are in Dakhiliyah Governorate only. 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). You are required to produce a distributed database design for this system based on the requirements given above, including: (a) A suitable fragmentation schema for the system. (b) The reconstruction of global relations from fragments.

Answer rating: 100% (QA)

To design a distributed database for the given system we need to consider the requirements for regional distribution and access by personal and workre... View the full answer