Reference no: EM132840593

COM636 Distributed Data - Wrexham Glyndwr University

Learning Outcome 1: Critically assess some of the more advanced developments in database technology, e.g.Distributed databases, Multidimensional Database, Multimedia Databases, Mobile Databases

Learning Outcome 2: Evaluate the current issues associated with theory to practical implementations in database research.

Learning Outcome 3: Explore advanced aspects of data warehousing, distributed data, data intensive computing, remote access and personalised data; encompassing the principles, research results and commercial application of the technologies.

You will be writing reports and designing Distributed Database Systems and Data Warehouse Systems.

Tasks

Task 1: Distributed Database Design

Happy Day Marketing is a large marketing company that has 50 consultancy offices across Europe. Most of these offices operate autonomously, with the exception of consultant salaries that are paid by the head offices in each individual country. The marketing consultancy roles involve the generation and printing of reports using specific system tools and applications. Each job that a consultant does will require the use of systems and recourses such as application and software licences, paper and Ink for printing reports etc., these resources are assigned to a job via purchase orders. Each job will have multiple purchase orders associated with it. Similarly, each purchase order may contain several purchase items.

At present Happy Day Marketing use a centralised database and all officesaccess the details from the centralised server.The schema is as follows which lists the attributes for each entity along with the primary keyandforeign keys:
COUNTRY: (countryNo, countryName)
ITEM: (ItemNo, iDescription, AmountInStock, Price)
OFFICE: (OfficeNo, oAddress1, oAddress2, oAddress3, oPostCode, oTelNo, oFaxNo, mgrSNo, countryNo)
STAFF: (StaffNo, fName, lName, sAddress1, sAddress2, sAddress3, sPostCode, sTelNo, DOB, sPosition, NIN, taxCode, salary, OfficeNo)
CLIENT: (CliNo, cName, cCity, cTelNo, cFaxNo, officeNo)
JOB: (JobNo, JobNo, JobData, JobDescription, JobType, supervisorSNo)
PURCHASEORDER: (PoNo, JobNo, poDate)
PO_ITEM: (PoNo, ItemNO, quantity)
Where:
COUNTRY - containsdetails of each country that Happy Day Marketing operates in (UK, France, Germany, Italy, Spain)
OFFICE -containsdetails of each office along with office managers staff number
STAFF -containsdetails of staffand the office they work
CLIENT - contains details of client and the office they are registered with
ITEM - contains the details of all materials, licences etc. that can be used in consultancy jobs along with quantity in stock in its warehouse and price
JOB - contains details of the current job along with the details of supervisor staff assigned to the job and job type (Normal or Urgent)
PURCHASEORDERS - contains purchase order details for each job
PO_ITEM - contains details of the items on the purchase order
To improve the sharing and communication of data, the company is thinking about implementing a Distributed DBMS.You have been asked by the Managing Director toinvestigate the data distribution requirements and provide expert advice on the potential use of Distributed Database System and Data Distribution scheme for Happy Day Marketing.

a. Write a report critically analysingthe potential advantages and disadvantages of implementing a Distributed DatabaseSystemand evaluatingvariousdata distributionoptions for Happy Day Marketingover a Distributed Databases System. Your report should recommend an appropriate data distribution solution for Happy Day Marketingwith relevant justification.

b. Present your data distribution scheme in-line with your recommended solution using a diagram representing the nodes (please note you don't have to represent 50 nodes) and data to be placed in the individual nodes or with the help of SQL statements.

Task 2: Data Warehouse

A shipping company keep the details about the customers, products, location and shipper details. They are planning to design a data warehouse to analyse the shipment details.Initial analysis has resulted in identifying the dimensions like customer, time, product, from location, to location, and shipper to measure quantity, weight, cost, and charge. Cost is the cost of shipping, and charge is the shipping fee charged to the customer. Someof the dimensions are listed below with some recommended attributes.

Customer - (name, street, city, state, country)
Time - (day, week, month, year)
From_Location - (street, city, state, country)
To_Location - (street, city, state, country)
Shipper - (name, company, street, city, state, country)
a. Discuss the concepts associated with dimensional modelling, it's association with Data Warehouse Design, and explain the Star Schema and Snow Flake Schema in a data warehouse design context.

b. Design a Star Schema for modelling the data warehouse forthe shipping company data structure. You must identify the FACT table with appropriate details and keys for the Dimensions.

c. Using the principles of normalisation convert the Star Schema to a Snowflake Schema.

Attachment:- Distributed Data.rar