Description of this paper

Loading

Laboratory Procedures DeVry University College of Engineering and-(Answered)

Description

Step-by-step Instant Solution


Question

Hi desainidhi99,?


I need help with this lab for DBM449. Can you please help me with this lab?? Can you do it??


Please review the attached lab and advise.


Thank you,


Justice12345


Laboratory Procedures

 

DeVry University

 

College of Engineering and Information Sciences

 

I.

 


 

OBJECTIVES

 

1. Understand and become familiar with the benefits and limitations of distributed database

 

architecture.

 

2. Learn to create, use, and maintain distributed databases using the MySQL FEDERATED

 

storage engine.

 

3. Join tables between different host database servers.

 

4. Explore key concepts of database replication and materialized views as performanceenhancing techniques in a distributed database architecture.

 

II.

 


 

PARTS LIST

 

1. EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/)

 

and/or:

 

2. MySQL (dev.mysql.com/downloads)

 


 

III.

 


 

PROCEDURE

 


 

To the end user working with databases distributed throughout a company's network is not

 

different than working with multiple tables within a single database. The fact that the different

 

databases exist in other locations should be totally transparent to the user. For this lab, we are

 

going to take on the roll of a database administrator in a company that has three regional offices

 

in the country. You work in the central regional office, but there is also a West Coast Region

 

located in Seattle and an East Coast Region located in Miami. Your role is to gather report

 

information from the other two regions.

 

For this lab, you are going to work with three different databases. You will be working with a

 

database named SEATTLE representing the West Coast Region and a database named MIAMI

 

representing the East Coast Region. For the central regional office database, you may re-use a

 

VM running MySQL from a previous lab if you have one, use a VM provided for this purpose by

 

your instructor (if your instructor provides it), or create a new VM, install MySQL, and enable

 

the FEDERATED storage engine. Your instructor may provide a shared instance of the

 

SEATTLE and MIAMI MySQL servers for your class to share, may provide you with your own

 

VM instances of these servers, or may require you to create and configure these VMs for

 

yourself. If your instructor has provided shared SEATTLE and MIAMI servers, contact your

 

instructor for login account and password information for these servers, if the instructor has not

 

already provided it. It is also possible to complete this lab using multiple computers on a home

 

network, multiple VMs running under a local VM environment (e.g., VMWARE Player, or Sun?s

 

Virtual Box), or a combination of the two.

 

To record your work for this lab use the LAB3_Report.doc found at the bottom of this document.

 


 

Preliminary Steps

 

1. Some of the preliminary steps may be optional, depending on what components your

 

instructor provides for you, and which the instructor requires you to create and configure

 

yourself.

 

2. (Optional) Create the Central Regional Office DBMS. This is the database you will

 

consider to be local. From this database, you will connect to the remote databases for

 

MIAMI and SEATTLE. If you have a VM running MySQL available from a previous lab,

 

you may use that system. If not, configure a new VM. Download and install MySQL.

 

3. (Optional) Create the MIAMI DBMS. This database will contain data specific to the East

 

Coast operations. If your instructor has provided a MIAMI server to be shared by you and

 

your classmates, you may skip this step. Otherwise, create/configure a new VM as

 

appropriate to your VM lab environment (this may include setting a root password, and

 

establishing a hostname. Using a host naming convention, such as MIAMI_LNAME,

 

where LNAME is replaced with your last name is a wise practice). Install MySQL and

 

perform basic configuration.

 

4. (Optional) If you have created your own MIAMI server, log onto that server, and open

 

the MySQL admin console. Create a new schema (e.g., DBM449LAB3). Create a user

 

account to be used to connect to this server remotely (from the Central Regional Office),

 

and provision that user account with at least the privileges needed to read and query

 

tables on the schema (Hint: for our purposes in this lab, it is okay to grant the user full

 

privileges). Configure MySQL to permit remote connections on the default port.

 

Configure the firewall (if running) to permit connections from the Central Regional

 

Office server (Hint: for our purposes, it is okay to simply turn the firewall off. While we

 

would never do this in the real world, it may be simpler and save time to simply disable

 

the firewall).

 

5. (Optional) If you have created your own MIAMI server, log onto that server, and open

 

the MySQL admin console. Analyze the data below, and create an appropriate data

 

model, including Primary Key (Hint: when creating other tables for this lab, make sure

 

analogous fields and fields likely to be used as Foreign Keys or for other JOINS have

 

compatible (identical, ideally) data types). Name this table MIAMI_EMP:

 

MIAMI_EMPNO

 

8369

 

8499

 

8521

 

8566

 

8654

 

8698

 

8782

 

8788

 

8839

 

8844

 

8876

 

8900

 

8902

 

8934

 


 

ENAME

 

SMITH

 

ALLEN

 

WARD

 

JONES

 

MARTIN

 

FREANK

 

THOMPSON

 

GLENN

 

SMITH

 

TURNER

 

ADAMS

 

JAMES

 

FORD

 

MILLER

 


 

JOB

 

CLERK

 

SALESMAN

 

SALESMAN

 

DEPARTMENT MANAGER

 

SALESMAN

 

DEPARTMENT MANAGER

 

DEPARTMENT MANAGER

 

ANALYST

 

REGION 1 MANAGER

 

SALESMAN

 

CLERK

 

CLERK

 

ANALYST

 

CLERK

 


 

Table 1

 


 

SAL

 

800

 

1600

 

1250

 

2975

 

1250

 

2850

 

2450

 

3000

 

5000

 

1500

 

1100

 

950

 

3000

 

1300

 


 

6. (Optional) Create the SEATTLE DBMS. This database will contain data specific to the

 

West Coast operations. If your instructor has provided a SEATTLE server to be shared by

 

you and your classmates, you may skip this step. Otherwise, create/configure a new VM

 

as appropriate to your VM lab environment (this may include setting a root password, and

 

establishing a hostname. Using a host naming convention, such as MIAMI_LNAME,

 

where LNAME is replaced with your last name is a wise practice). Install MySQL and

 

perform basic configuration.

 

7. (Optional) If you have created your own SEATTLE server, log onto that server, and open

 

the MySQL admin console. Create a new schema (e.g., DBM449LAB3). Create a user

 

account to be used to connect to this server remotely (from the Central Regional Office),

 

and provision that user account with at least the privileges needed to read and query

 

tables on the schema (Hint: for our purposes in this lab, it is okay to grant the user full

 

privileges). Configure MySQL to permit remote connections on the default port.

 

Configure the firewall (if running) to permit connections from the Central Regional

 

Office server (Hint: for our purposes, it is okay to simply turn the firewall off. While we

 

would never do this in the real world, it may be simpler and save time to simply disable

 

the firewall).

 

8. (Optional) If you have created your own SEATTLE server, log onto that server, and open

 

the MySQL admin console. Analyze the data below, and create an appropriate data

 

model, including Primary Key (Hint: when creating other tables for this lab, make sure

 

analogous fields and fields likely to be used as Foreign Keys or for other JOINS have

 

compatible (identical, ideally) data types). Name this table SEATTLE_EMP:

 

SEATTLE_EMPNO

 

7369

 

7499

 

7521

 

7566

 

7654

 

7698

 

7782

 

7788

 

7839

 

7844

 

7876

 

7900

 

7902

 

7934

 


 

ENAME

 

SMITH

 

ALLEN

 

WARD

 

JONES

 

MARTIN

 

BLAKE

 

CLARK

 

SCOTT

 

KING

 

TURNER

 

ADAMS

 

JAMES

 

FORD

 

MILLER

 


 

JOB

 

CLERK

 

SALESMAN

 

SALESMAN

 

DEPARTMENT MANAGER

 

SALESMAN

 

DEPARTMENT MANAGER

 

DEPARTMENT MANAGER

 

ANALYST

 

REGION 1 MANAGER

 

SALESMAN

 

CLERK

 

CLERK

 

ANALYST

 

CLERK

 


 

Table 2

 


 

SAL

 

800

 

1600

 

1250

 

2975

 

1250

 

2850

 

2450

 

3000

 

5000

 

1500

 

1100

 

950

 

3000

 

1300

 


 

9. Log into the Central Regional Office DBMS, and open and log into MySQL admin.

 

Create a new schema (e.g., DBM449LAB3). Analyze the data below, and create an

 

appropriate data model, including Primary Key (Hint: when creating other tables for this

 

lab, make sure analogous fields and fields likely to be used as Foreign Keys or for other

 

JOINS have compatible (identical, ideally) data types). Name this table DEPT.

 

DEPTNO

 

10

 

20

 

30

 

40

 

50

 

60

 

70

 

80

 

90

 

00

 


 

DNAME

 

HR

 

MARKETING

 

MID MANAGEMENT

 

ACCOUNTING

 

MANAGEMENT

 

HR

 

MARKETING

 

MID MANAGEMENT

 

ACCOUNTING

 

MANAGEMENT

 

Table 3

 


 

LOC

 

MIAMI

 

MIAMI

 

MIAMI

 

MIAMI

 

MIAMI

 

SEATTLE

 

SEATTLE

 

SEATTLE

 

SEATTLE

 

SEATTLE

 


 

10. Research requirements for configuring the FEDERATED storage engine for use in

 

MySQL.

 

11. On the Central Regional Office DBMS, implement/enable/configure the FEDERATED

 

storage engine.

 

Testing, Troubleshooting, and Debugging

 

12. Check to make certain that all the VMs or computers (Central Regional Office, Miami,

 

Seattle) are up and running (e.g., not in a suspended or shutdown state).

 

13. Log onto the Central Regional Office system.

 

14. Ping the MIAMI server, using either its IP address, or hostname. Although the first one or

 

two ping packets may be lost (no response/host unreachable, remaining pings should be

 

successful). Take a screen shot of the successful result (troubleshoot and remediate until

 

you are able to ping successfully), and paste it into your lab report.

 

15. Ping the SEATTLE server, using either its IP address, or hostname. Although the first one

 

or two ping packets may be lost (no response/host unreachable, remaining pings should

 

be successful). Take a screen shot of the successful result (troubleshoot and remediate

 

until you are able to ping successfully), and paste it into your lab report.

 

16. CHECKPOINT QUESTION: Why is testing to ensure that you are able to ping MIAMI

 

and SEATTLE the first step to take in making sure that the remainder of the lab

 

procedure will be possible? Paste your response into the lab report.

 

17. Note that the MySQL Admin Console may be used to log into EITHER the local MySQL

 

database, OR to a database running on a remote server. The format for logging in is:

 

mysql -u USERNAME -pPASSWORD -h HOSTNAMEORIP

 


 

For example, from the Central Regional Office system, one might log into a server named

 

MIAMI_SMITH with a username of USER1 and a password of ABRACADBRA using

 

the following command.

 

mysql -u USER1 -pABRACADABRA -h MIAMI_SMITH

 

Similarly, the same login might be accomplished using the IP address of the

 

MIAMI_SMITH host. If that IP address were: 192.168.1.253, then the command would

 

resemble:

 

mysql -u USER1 -pABRACADABRA -h 192.168.1.253

 

18. From the Central Regional Office system, run the MySQL Admin Console and log into

 

the MIAMI server. If the login fails, troubleshoot and correct the issue. You should only

 

attempt this AFTER you have proven that it is possible to ping to this server. If ping

 

works, but the remote login using MySQL Admin fails, some possibilities to consider in

 

your troubleshooting include: Is the MySQL database engine running on the remote

 

system? Are firewall rules on the remote system preventing the connection? Are firewall

 

rules on the local system preventing the connection? Does the ID and password exist on

 

the remote system?s MySQL database, AND has it been granted permission for remote

 

connection? While we would never run without appropriate firewall protections in place

 

in the real world, it may be expedient to disable the firewalls on both local and remote

 

systems for purposes of this lab exercise. Upon establishing a successful remote MySQL

 

Admin Console connection to the remote server, take a screen shot showing the

 

command and its successful result, and paste it into your lab report.

 

19. Issue a Select * query on the MIAMI_EMP table, using the remote MySQL Admin

 

Console connection established in the previous step. Take a screen shot showing the

 

Select statement and its result, and paste it into your lab report.

 

20. From the Central Regional Office system, run the MySQL Admin Console and log into

 

the SEATTLE server. If the login fails, troubleshoot and correct the issue. You should

 

only attempt this AFTER you have proven that it is possible to ping to this server. If ping

 

works, but the remote login using MySQL Admin fails, some possibilities to consider in

 

your troubleshooting include: Is the MySQL database engine running on the remote

 

system? Are firewall rules on the remote system preventing the connection? Are firewall

 

rules on the local system preventing the connection? Does the ID and password exist on

 

the remote system?s MySQL database, AND has it been granted permission for remote

 

connection? While we would never run without appropriate firewall protections in place

 

in the real world, it may be expedient to disable the firewalls on both local and remote

 

systems for purposes of this lab exercise. Upon establishing a successful remote MySQL

 

Admin Console connection to the remote server, take a screenshot showing the command

 

and its successful result, and paste it into your lab report.

 

21. Issue a Select * query on the SEATTLE_EMP table, using the remote MySQL Admin

 

Console connection established in the previous step. Take a screen shot showing the

 

Select statement and its result, and paste it into your lab report.

 

22. CHECKPOINT QUESTION: Why is ping testing between the systems not enough?

 

Why is testing connectivity to the remote MySQL DBMS necessary? What are some

 

problems/misconfigurations that could allow ping to work, but prevent use of/connection

 

to the remote MySQL database instance? Record your response in your lab report.

 


 

23. Referring to your research and reading on creating FEDERATED tables, create

 

FEDERATED tables for MIAMI_EMP and SEATTLE_EMP on the Central Regional

 

Office DBMS. Capture one screen shot for the SQL statement used to create each table,

 

AND its successful result. Paste these two screen shots into your lab report. Note: At this

 

point, you have already established that basic network connectivity and remote database

 

connectivity are working. However, this step can still fail due to configuration errors.

 

The most likely reason for a failure at this point is that the FEDERATED storage engine

 

is not properly configured on the Central Regional Office computer, that the IDs being

 

used to connect the FEDERATED tables to their counterparts on the MIAMI and

 

SEATTLE servers do not exist or lack appropriate privileges, or that the SQL or

 

commands are not properly composed/formatted. You must troubleshoot and resolve any

 

errors that arise. Remember to make use of the Q & A discussions in order to problem

 

solve with your classmates and your instructor.

 

24. CHECKPOINT QUESTION: You have now created FEDERATED tables, which act

 

like links (they contain no actual data) to the tables residing on the remote servers.

 

Describe what occurs when a user logged into the Central Regional Office instance of

 

MySQL writes a query which references data using the local (FEDERATED) tables.

 

Does the user need to be aware of, or do anything special in order to make use of this

 

remotely-stored data? Record your answers in your lab report.

 

Using FEDERATED Tables

 

25. Log into the Central Regional Office computer, if you are not already logged on. If you

 

have any MySQL Admin Consoles open, close them. Open a new MySQL Admin

 

Console, and log into the local (Central) MySQL database instance.

 

26. Write a query to retrieve the employee number, name, job function, and salary from the

 

FEDERATED (local) SEATTLE_EMP table. Create a screenshot showing both the SQL

 

and its result, and paste a copy into your lab report.

 

27. Write a query to retrieve the employee number, name, job function, and salary from the

 

FEDERATED (local) MIAMI_EMP table. Create a screenshot showing both the SQL and

 

its result, and paste a copy into your lab report.

 

28. Now we need to increase our report. Write a single query that will retrieve employees

 

from both the Seattle and Miami regions who work in sales. Show the employee number,

 

employee name, job function, salary, and location name in the result set (HINT: The

 

location name is in the DEPT table).

 

29. CHECKPOINT QUESTION: Describe the part each local and remote server plays in

 

processing the query and especially how joins are resolved when data resides across

 

remote systems. Record your response in the lab report.

 

Improving Performance

 

30. As you can see from your experience in the previous steps, the ability to distribute data

 

and yet access that data as if it resided locally provides great flexibility and transparency.

 

However, for certain purposes, such as consolidated processing and reporting, it can also

 

introduce performance bottlenecks and complexities. As you have learned in previous

 

studies, one means of hiding complexities is to provide a consolidated presentation of

 


 

data using a view. However, simply creating a view residing on Central that aggregated

 

data from SEATTLE_EMP and MIAMI_EMP would not markedly improve performance,

 

as it would still essentially be limited by the bandwidth and network performance

 

connecting the Central servers to the remote servers. This situation would only be

 

exacerbated as the size of these tables grow, when queries generated table scans

 

(requiring the entire table contents to be transmitted), and with queries performing

 

additional joins between remote tables. Many databases offer the ability to create a

 

materialized view. In a materialized view, data and indices required to construct the view

 

are cached on the local database server (usually cached in-memory, for high

 

performance), and automatically synchronized with any changes in their underlying

 

tables. As you might expect, locking up the memory resources to keep the view wired in

 

memory, and the CPU work required to keep the view updated can be considerable.

 

Consequently, the decision to create a materialized view is one to be carefully considered.

 

However, materialized views can deliver unprecedented performance benefits.

 

31. MySQL, as of the time of this writing, does not explicitly provide materialized views.

 

However, essentially the same benefit can be achieved through the expediency of simply

 

creating a new table possessing the same attributes as would the view. The onus of this

 

approach on the DBA or database developer is this: the designer must provide for the

 

table?s synchronization with any changes in the underlying data. This can be achieved

 

and tuned using a system of stored procedures and triggers, or through MySQL?s

 

excellent REPLICATION services. The correct solution will depend on the requirements

 

for the currency of the data. For example, all changes in the underlying data can be

 

propagated as they occur, keeping the view table immediately up-to-date. However, if this

 

is not required, updates could be batched every 5 minutes, hourly, daily, or on any

 

arbitrary schedule as determined by the designer. For example, such a view used only to

 

provide a consolidated report each night need only be refreshed once each day (before the

 

nightly report processing).

 

32. Design a table to act as a Materialized View, which consolidates the SEATTLE_EMP and

 

MIAMI_EMP tables into a single, local table (CONSOL_EMP) with data resident on the

 

Central server.

 

33. Run the query designed in the previous step, and paste a screenshot showing the query

 

and its result into your lab report.

 

34. Perform a SELECT * query on CONSOL_EMP, and paste the result into your lab report.

 

35. CHECKPOINT QUESTIONS: Research MySQL Replication services, and describe how

 

you might use these to keep CONSOL_EMP up-to-date automatically. Give an example

 

of an UPDATE, INSERT, or DELETE Trigger that you could create upon the

 

SEATTLE_EMP table to ensure that CONSOL_EMP would automatically be updated

 

when such a transaction occurred on the SEATTLE_EMP table. What are some concerns

 

that need to be considered in such a design (e.g., if the network connection between

 

Seattle and the Central office is down, would such a transaction be rolled back (stopping

 

business in Seattle!), or permitted (in which case, how would the CONSOL_EMP table

 

be brought back in sync later?).

 


 

Laboratory Report

 

DeVry University

 

College of Engineering and Information Sciences

 


 

Course Number: DBM449

 

Laboratory Number: 3

 

Laboratory Title: Working With Distributed Databases

 

Note: There is no limit on how much information you will enter under the three topics below. It is

 

important to be clear and complete with your comments. Like a scientist you are documenting

 

your progress in this week?s lab experiment.

 

Objectives: (In your own words what was this lab designed to accomplish? What was its

 

purpose?)

 


 

Results: (Discuss the steps you used to complete your lab. Were you successful? What

 

did you learn? What were the results? Explain what you did to accomplish each step.

 

You can include screen shots, code listings, and so on. to clearly explain what you did.

 

Be sure to record all results specifically directed by the lab procedure. Number all

 

results to reflect the procedure number to which they correspond.)

 


 

Conclusions: (After completing this lab, in your own words, what conclusions can you

 

draw from this experience?)

 


 

 

Paper#9256222 | Written in 27-Jul-2016

Price : $16
SiteLock