#DBM 449 Laboratory Procedures Ilab 4 Answers
Explore tagged Tumblr posts
Visit Tumblr Blog
Explore Tumblr blogs with no restrictions, modern design and the best experience.
Last Seen Tumblr Blogs
Fun Fact
Tumblr is used by 21% of adults online aged 18-29 years.
Text
DBM 449 Laboratory Procedures Ilab 4 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-4-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
Laboratory Procedures DeVry University College of Engineering and Information Sciences
I. OBJECTIVES
Understand and become familiar with current capabilities and limitations of the OpenGIS implementation in MySQL.
Learn to create, update, and use spatial indices.
Explore practical approaches to calculating distances between points on the Earth’s surface.
Understand fundamentals of geotagging.
Create stored procedures to determine real-world distances, and to process spatial queries returning result sets of data points within a bounding rectangle.
Explore visualization of GIS data.
II. PARTS LIST
EDUPE-APP Omnymbus MySQL Environment (https://devry.edupe.net:8300/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
The argument could be made that Business Intelligence (BI) and Data Analytics revolutionized Online Analytical Processing (OLAP) by making it simple for users to traverse, examine, and visualize different aggregations of data over the dimension of time. Geographic Information Systems, once an arcane, rare, expensive, and highly specialized type of information system, have brought about a similar revolution using the spatial dimension. As these systems have become affordable and entered the mainstream—indeed, they are now ubiquitous—they have also become mainstream; or perhaps it would be more accurate to say that mainstream DBMS systems have come to commonly adopt and integrate the specialized data structures and algorithms required to implement spatially enabled, data-driven systems at will.
In this laboratory exercise, you will create a GIS-enabled database by implementing a spatially indexed table, populating it with spatially encoded data, and creating stored procedures to provide augmented functionality to determine distances between points, and to process queries returning results containing points within spatially defined boundaries. Finally, you will learn to express and explore spatial data in its most natural and intuitive form: visually displayed as maps and plots.
This lab may be completed using MySQL running on either your own computer, or the DeVry iLab. In either case, it is presumed that you will begin the initial lab step AFTER addressing any necessary routine housekeeping chores, such as creating an appropriate schema (e.g., DBM449LAB2), and creating any necessary user accounts, permissions, and so on.
Note: At the time this lab was written, the full OPENGIS standard was not implemented in the current production release of MySQL, but new features were being added with each point release of MySQL. It is entirely possible—in fact, quite likely—that improved OPENGIS compliance, including functions for distance calculation for spherical projections (e.g., points on the Earth’s surface) and circular proximities (e.g., “within radius of”) will become built-in to the MySQL database. However, as the study of the underlying mathematical and topographical principles needed to implement non-planar distance calculations, and for determining envelope or bounding box point results are a very worthwhile study, you should implement your own stored procedures for these functions, rather than substituting any built-in capabilities that become available. You may, however, repeat steps using such features as available, in order to compare and study the similarities and differences between your calculation methods and those later implemented as part of the OPENGIS API.
Important Further Note: At the time of this writing, spatial indices are supported ONLY in the MyISAM storage engine, and not in the InnoDB or other storage engines. BE SURE TO CREATE YOUR DATABASE TABLE FOR THIS LAB USING THE MyISAM STORAGE ENGINE!
Designing a Spatially-enabled Table, and Creating a Spatial Index
Create the table indicated in the following ERD.
Figure 1
Be sure you have first addressed the assigned research for this week’s unit involving Spatial Indices, and then use the following DDL to create a spatial index on the table just created:
CREATE SPATIAL INDEX `location` ON `Points` (`location` ASC);
Paste the complete SQL Data Definition Language (DDL) you used to create this table and index into your lab report.
Choose a point of interest (e.g., your house, your local DeVry campus, etc.), and at least three additional points within 20 miles, and three additional points more than 40 miles from the first point. For example, I chose my house, and three favorite restaurants in town, and three favorite restaurants in a distant town where I used to live. Using Google Maps or other service capable of converting street addresses to geographical (longitude and latitude) coordinates with good precision, note the geolocation of each point. Record this data in your lab report.
From your research, you should anticipate that you cannot simply insert these values directly. Model your insert statements for the data to be inserted into your Points table on the following example.
INSERT INTO Points (name, location) VALUES ( ‘point1’ , GeomFromText( ‘ POINT(31.5 42.2) ‘ ) )
CHECKPOINT QUESTION: Explain what the GeomFromText() function does, and why it is necessary to use this? Paste your response into your lab report.
Run your insert statement(s) to add the data to the Points table. Paste a screen shot showing your SQL statement AND result into your lab report.
Displaying Spatial Data in Human-readable Form
Attempt to retrieve all of the table’s contents using a SELECT * statement. You should find that this does not produce readable results. Your results may resemble the following.
Figure 2
CHECKPOINT QUESTION: Why does this query not produce the results you might typically expect from a SELECT * statement? How can the AsText() function be incorporated into a query returning every field in the table in a readable format? Paste your response into the lab report.
Execute the query you composed in the previous step, and paste a screenshot of the results into your lab report. The results should be similar to the following.
Figure 3
Calculating Distances on Earth’s Surface (Spherical, or Nonplanar Distance Calculation)
CHECKPOINT QUESTION: Your assigned research and graded threaded discussion questions this week should quickly lead you to discover that although the Pythagorean Theorem is marvelously useful for calculating the distance between points on a Cartesian planar surface, on a curved surface (such as the surface of the Earth), the further apart two points reside from one another, the greater is the error that results from misapplication of this formula to a curved (in this case, roughly spheroidal) surface. Better (less imprecise) results can be obtained by making use of the Great Circle Formula, haversine formulas, and cosine transforms. You will need to select and appropriate formula, and compose a stored procedure which can be used to calculate the geographic distances between points in your table. You will also need to use a coefficient or conversion factor so that the units of the results are expressed appropriately (e.g., kilometers, meters, miles, yards, feet, etc.), and with reasonable precision. Record your determination of the formula you will use, the reason you believe this is a good approach, and discuss both the degree of precision/error to be expected, and the units you elected to use for your measurement, and why. Record your answer in the lab report.
Compose and install your stored procedure or function for calculating geographic distance, into the database. Take a screen shot showing your SQL statement, and the result showing that the procedure was successfully created. Paste this into the lab report.
Spatial Queries: Retrieving Data Points Within a Bounding Polygon
CHECKPOINT QUESTION: Your assigned research and graded discussion questions this week will inform your understanding of the use of a bounding box or bounding polygon used to return all spatially indexed points stored in the database which reside within the area defined by the boundary. Parameters for a bounding rectangle can minimally be specified using the vertex points of either diagonal. For example, the upper-left corner, and the lower-right corner. In such case, all points with a horizontal value equal to or between the x-axis elements of the bounding points, that also have vertical value equal to or between the y-axis elements of the bounding points, reside within the qualifying region. You will want to easily be able to center this bounding rectangle on a point which you choose. How will you accomplish this? Design and document the stored procedure or function you will use to implement a bounding rectangle function, and paste your analysis and design into the lab report.
Install the stored procedure or function you designed in the previous step into the database. Create a screen shot showing the SQL used to create the procedure, and the result of its successful creation.
Write SQL to use your bounding box function, centered on your original point of interest, and all of the surrounding points of interests within 20 miles (horizontal and vertical distance) from that point. The results should show that points outside the region are not returned by this spatial query result. Paste a screen shot showing your query and the result, into your lab report.
CHECKPOINT QUESTION: It is possible for a point residing within 20 miles of your original point to correctly be omitted by the bounding box query. Explain why this is the case, and what improvements/refinements might be undertaken in order to improve upon this.
Visualization: Mapping and Displaying Spatial Data Graphically
CHECKPOINT QUESTION: Having created a stored procedure that can easily calculate the distance between any two points in your table, it will occur to you that you could easily create queries that would find “the point B, nearest a given point, A, meeting some additional criteria”. However, consider carefully that you would do this by using a calculated field (Distance). For how many points in your database would the query need to calculate Distance? What are the implications of this to performance and efficiency, if your table is quite large (millions of rows)? What approach could you take that would result in greater efficiency, perhaps allowing Distance to be calculated for a relatively small subset? (Hint: Think about your bounding box function. It returns a small set of points within a given proximity of a specified point, and does so pretty efficiently if the proper indexes are available, because it filters for latitude and longitude values within a bounded numerical range. What if you calculated Distance for only this subset, and further filtered for the minimum Distance?) Record your answers to these questions in your lab report.
With your answers to the previous questions in mind, formulate an EFFICIENT query that returns only the latitudes and longitudes for two points: the original point, and its nearest neighbor, in a single row (Hints: 1. A JOIN statement might be useful here; 2. It may be convenient to use the X() function and Y() function on your point data type columns, for example: “SELECT X(Points.location) as longitude1, Y(Points.location) as latitude1 FROM Points;”).
Test your query, and when you are satisfied that it is working correctly, paste a screen shot showing your query and its results into your lab report.
Modify your query using concatenation and string manipulation functions as needed so that the output of the results resembles:
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Notice that the highlighted elements in this output should be string literals. Only the X() and Y() values from the first and second points are values obtained from the database.
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Take a screenshot of your query, showing both the SQL and the result, and paste it into your lab report.
Test the URL you have generated in the previous query, by pasting it into the address bar of your internet browser. A route map should be generated with characteristics similar to the figure below (your map will, of course, reflect the unique points/locations you selected for your database).
Figure 4
In your lab report, provide a description explaining the route image, for example, “Closest Pizza Parlor to my home.”
CHECKPOINT QUESTION: What are the benefits of displaying spatial data visually? What are some examples of this sort of spatial visualization of GIS data OTHER than driving directions for consumers? Record your response in your lab report.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 4
Laboratory Title: Spatial Indices
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?)
0 notes
Text
DBM 449 Laboratory Procedures Ilab 4 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-4-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
Laboratory Procedures DeVry University College of Engineering and Information Sciences
I. OBJECTIVES
Understand and become familiar with current capabilities and limitations of the OpenGIS implementation in MySQL.
Learn to create, update, and use spatial indices.
Explore practical approaches to calculating distances between points on the Earth’s surface.
Understand fundamentals of geotagging.
Create stored procedures to determine real-world distances, and to process spatial queries returning result sets of data points within a bounding rectangle.
Explore visualization of GIS data.
II. PARTS LIST
EDUPE-APP Omnymbus MySQL Environment (https://devry.edupe.net:8300/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
The argument could be made that Business Intelligence (BI) and Data Analytics revolutionized Online Analytical Processing (OLAP) by making it simple for users to traverse, examine, and visualize different aggregations of data over the dimension of time. Geographic Information Systems, once an arcane, rare, expensive, and highly specialized type of information system, have brought about a similar revolution using the spatial dimension. As these systems have become affordable and entered the mainstream—indeed, they are now ubiquitous—they have also become mainstream; or perhaps it would be more accurate to say that mainstream DBMS systems have come to commonly adopt and integrate the specialized data structures and algorithms required to implement spatially enabled, data-driven systems at will.
In this laboratory exercise, you will create a GIS-enabled database by implementing a spatially indexed table, populating it with spatially encoded data, and creating stored procedures to provide augmented functionality to determine distances between points, and to process queries returning results containing points within spatially defined boundaries. Finally, you will learn to express and explore spatial data in its most natural and intuitive form: visually displayed as maps and plots.
This lab may be completed using MySQL running on either your own computer, or the DeVry iLab. In either case, it is presumed that you will begin the initial lab step AFTER addressing any necessary routine housekeeping chores, such as creating an appropriate schema (e.g., DBM449LAB2), and creating any necessary user accounts, permissions, and so on.
Note: At the time this lab was written, the full OPENGIS standard was not implemented in the current production release of MySQL, but new features were being added with each point release of MySQL. It is entirely possible—in fact, quite likely—that improved OPENGIS compliance, including functions for distance calculation for spherical projections (e.g., points on the Earth’s surface) and circular proximities (e.g., “within radius of”) will become built-in to the MySQL database. However, as the study of the underlying mathematical and topographical principles needed to implement non-planar distance calculations, and for determining envelope or bounding box point results are a very worthwhile study, you should implement your own stored procedures for these functions, rather than substituting any built-in capabilities that become available. You may, however, repeat steps using such features as available, in order to compare and study the similarities and differences between your calculation methods and those later implemented as part of the OPENGIS API.
Important Further Note: At the time of this writing, spatial indices are supported ONLY in the MyISAM storage engine, and not in the InnoDB or other storage engines. BE SURE TO CREATE YOUR DATABASE TABLE FOR THIS LAB USING THE MyISAM STORAGE ENGINE!
Designing a Spatially-enabled Table, and Creating a Spatial Index
Create the table indicated in the following ERD.
Figure 1
Be sure you have first addressed the assigned research for this week’s unit involving Spatial Indices, and then use the following DDL to create a spatial index on the table just created:
CREATE SPATIAL INDEX `location` ON `Points` (`location` ASC);
Paste the complete SQL Data Definition Language (DDL) you used to create this table and index into your lab report.
Choose a point of interest (e.g., your house, your local DeVry campus, etc.), and at least three additional points within 20 miles, and three additional points more than 40 miles from the first point. For example, I chose my house, and three favorite restaurants in town, and three favorite restaurants in a distant town where I used to live. Using Google Maps or other service capable of converting street addresses to geographical (longitude and latitude) coordinates with good precision, note the geolocation of each point. Record this data in your lab report.
From your research, you should anticipate that you cannot simply insert these values directly. Model your insert statements for the data to be inserted into your Points table on the following example.
INSERT INTO Points (name, location) VALUES ( ‘point1’ , GeomFromText( ‘ POINT(31.5 42.2) ‘ ) )
CHECKPOINT QUESTION: Explain what the GeomFromText() function does, and why it is necessary to use this? Paste your response into your lab report.
Run your insert statement(s) to add the data to the Points table. Paste a screen shot showing your SQL statement AND result into your lab report.
Displaying Spatial Data in Human-readable Form
Attempt to retrieve all of the table’s contents using a SELECT * statement. You should find that this does not produce readable results. Your results may resemble the following.
Figure 2
CHECKPOINT QUESTION: Why does this query not produce the results you might typically expect from a SELECT * statement? How can the AsText() function be incorporated into a query returning every field in the table in a readable format? Paste your response into the lab report.
Execute the query you composed in the previous step, and paste a screenshot of the results into your lab report. The results should be similar to the following.
Figure 3
Calculating Distances on Earth’s Surface (Spherical, or Nonplanar Distance Calculation)
CHECKPOINT QUESTION: Your assigned research and graded threaded discussion questions this week should quickly lead you to discover that although the Pythagorean Theorem is marvelously useful for calculating the distance between points on a Cartesian planar surface, on a curved surface (such as the surface of the Earth), the further apart two points reside from one another, the greater is the error that results from misapplication of this formula to a curved (in this case, roughly spheroidal) surface. Better (less imprecise) results can be obtained by making use of the Great Circle Formula, haversine formulas, and cosine transforms. You will need to select and appropriate formula, and compose a stored procedure which can be used to calculate the geographic distances between points in your table. You will also need to use a coefficient or conversion factor so that the units of the results are expressed appropriately (e.g., kilometers, meters, miles, yards, feet, etc.), and with reasonable precision. Record your determination of the formula you will use, the reason you believe this is a good approach, and discuss both the degree of precision/error to be expected, and the units you elected to use for your measurement, and why. Record your answer in the lab report.
Compose and install your stored procedure or function for calculating geographic distance, into the database. Take a screen shot showing your SQL statement, and the result showing that the procedure was successfully created. Paste this into the lab report.
Spatial Queries: Retrieving Data Points Within a Bounding Polygon
CHECKPOINT QUESTION: Your assigned research and graded discussion questions this week will inform your understanding of the use of a bounding box or bounding polygon used to return all spatially indexed points stored in the database which reside within the area defined by the boundary. Parameters for a bounding rectangle can minimally be specified using the vertex points of either diagonal. For example, the upper-left corner, and the lower-right corner. In such case, all points with a horizontal value equal to or between the x-axis elements of the bounding points, that also have vertical value equal to or between the y-axis elements of the bounding points, reside within the qualifying region. You will want to easily be able to center this bounding rectangle on a point which you choose. How will you accomplish this? Design and document the stored procedure or function you will use to implement a bounding rectangle function, and paste your analysis and design into the lab report.
Install the stored procedure or function you designed in the previous step into the database. Create a screen shot showing the SQL used to create the procedure, and the result of its successful creation.
Write SQL to use your bounding box function, centered on your original point of interest, and all of the surrounding points of interests within 20 miles (horizontal and vertical distance) from that point. The results should show that points outside the region are not returned by this spatial query result. Paste a screen shot showing your query and the result, into your lab report.
CHECKPOINT QUESTION: It is possible for a point residing within 20 miles of your original point to correctly be omitted by the bounding box query. Explain why this is the case, and what improvements/refinements might be undertaken in order to improve upon this.
Visualization: Mapping and Displaying Spatial Data Graphically
CHECKPOINT QUESTION: Having created a stored procedure that can easily calculate the distance between any two points in your table, it will occur to you that you could easily create queries that would find “the point B, nearest a given point, A, meeting some additional criteria”. However, consider carefully that you would do this by using a calculated field (Distance). For how many points in your database would the query need to calculate Distance? What are the implications of this to performance and efficiency, if your table is quite large (millions of rows)? What approach could you take that would result in greater efficiency, perhaps allowing Distance to be calculated for a relatively small subset? (Hint: Think about your bounding box function. It returns a small set of points within a given proximity of a specified point, and does so pretty efficiently if the proper indexes are available, because it filters for latitude and longitude values within a bounded numerical range. What if you calculated Distance for only this subset, and further filtered for the minimum Distance?) Record your answers to these questions in your lab report.
With your answers to the previous questions in mind, formulate an EFFICIENT query that returns only the latitudes and longitudes for two points: the original point, and its nearest neighbor, in a single row (Hints: 1. A JOIN statement might be useful here; 2. It may be convenient to use the X() function and Y() function on your point data type columns, for example: “SELECT X(Points.location) as longitude1, Y(Points.location) as latitude1 FROM Points;”).
Test your query, and when you are satisfied that it is working correctly, paste a screen shot showing your query and its results into your lab report.
Modify your query using concatenation and string manipulation functions as needed so that the output of the results resembles:
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Notice that the highlighted elements in this output should be string literals. Only the X() and Y() values from the first and second points are values obtained from the database.
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Take a screenshot of your query, showing both the SQL and the result, and paste it into your lab report.
Test the URL you have generated in the previous query, by pasting it into the address bar of your internet browser. A route map should be generated with characteristics similar to the figure below (your map will, of course, reflect the unique points/locations you selected for your database).
Figure 4
In your lab report, provide a description explaining the route image, for example, “Closest Pizza Parlor to my home.”
CHECKPOINT QUESTION: What are the benefits of displaying spatial data visually? What are some examples of this sort of spatial visualization of GIS data OTHER than driving directions for consumers? Record your response in your lab report.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 4
Laboratory Title: Spatial Indices
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?)
0 notes
Text
DBM 449 Laboratory Procedures Ilab 4 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-4-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
Laboratory Procedures DeVry University College of Engineering and Information Sciences
I. OBJECTIVES
Understand and become familiar with current capabilities and limitations of the OpenGIS implementation in MySQL.
Learn to create, update, and use spatial indices.
Explore practical approaches to calculating distances between points on the Earth’s surface.
Understand fundamentals of geotagging.
Create stored procedures to determine real-world distances, and to process spatial queries returning result sets of data points within a bounding rectangle.
Explore visualization of GIS data.
II. PARTS LIST
EDUPE-APP Omnymbus MySQL Environment (https://devry.edupe.net:8300/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
The argument could be made that Business Intelligence (BI) and Data Analytics revolutionized Online Analytical Processing (OLAP) by making it simple for users to traverse, examine, and visualize different aggregations of data over the dimension of time. Geographic Information Systems, once an arcane, rare, expensive, and highly specialized type of information system, have brought about a similar revolution using the spatial dimension. As these systems have become affordable and entered the mainstream—indeed, they are now ubiquitous—they have also become mainstream; or perhaps it would be more accurate to say that mainstream DBMS systems have come to commonly adopt and integrate the specialized data structures and algorithms required to implement spatially enabled, data-driven systems at will.
In this laboratory exercise, you will create a GIS-enabled database by implementing a spatially indexed table, populating it with spatially encoded data, and creating stored procedures to provide augmented functionality to determine distances between points, and to process queries returning results containing points within spatially defined boundaries. Finally, you will learn to express and explore spatial data in its most natural and intuitive form: visually displayed as maps and plots.
This lab may be completed using MySQL running on either your own computer, or the DeVry iLab. In either case, it is presumed that you will begin the initial lab step AFTER addressing any necessary routine housekeeping chores, such as creating an appropriate schema (e.g., DBM449LAB2), and creating any necessary user accounts, permissions, and so on.
Note: At the time this lab was written, the full OPENGIS standard was not implemented in the current production release of MySQL, but new features were being added with each point release of MySQL. It is entirely possible—in fact, quite likely—that improved OPENGIS compliance, including functions for distance calculation for spherical projections (e.g., points on the Earth’s surface) and circular proximities (e.g., “within radius of”) will become built-in to the MySQL database. However, as the study of the underlying mathematical and topographical principles needed to implement non-planar distance calculations, and for determining envelope or bounding box point results are a very worthwhile study, you should implement your own stored procedures for these functions, rather than substituting any built-in capabilities that become available. You may, however, repeat steps using such features as available, in order to compare and study the similarities and differences between your calculation methods and those later implemented as part of the OPENGIS API.
Important Further Note: At the time of this writing, spatial indices are supported ONLY in the MyISAM storage engine, and not in the InnoDB or other storage engines. BE SURE TO CREATE YOUR DATABASE TABLE FOR THIS LAB USING THE MyISAM STORAGE ENGINE!
Designing a Spatially-enabled Table, and Creating a Spatial Index
Create the table indicated in the following ERD.
Figure 1
Be sure you have first addressed the assigned research for this week’s unit involving Spatial Indices, and then use the following DDL to create a spatial index on the table just created:
CREATE SPATIAL INDEX `location` ON `Points` (`location` ASC);
Paste the complete SQL Data Definition Language (DDL) you used to create this table and index into your lab report.
Choose a point of interest (e.g., your house, your local DeVry campus, etc.), and at least three additional points within 20 miles, and three additional points more than 40 miles from the first point. For example, I chose my house, and three favorite restaurants in town, and three favorite restaurants in a distant town where I used to live. Using Google Maps or other service capable of converting street addresses to geographical (longitude and latitude) coordinates with good precision, note the geolocation of each point. Record this data in your lab report.
From your research, you should anticipate that you cannot simply insert these values directly. Model your insert statements for the data to be inserted into your Points table on the following example.
INSERT INTO Points (name, location) VALUES ( ‘point1’ , GeomFromText( ‘ POINT(31.5 42.2) ‘ ) )
CHECKPOINT QUESTION: Explain what the GeomFromText() function does, and why it is necessary to use this? Paste your response into your lab report.
Run your insert statement(s) to add the data to the Points table. Paste a screen shot showing your SQL statement AND result into your lab report.
Displaying Spatial Data in Human-readable Form
Attempt to retrieve all of the table’s contents using a SELECT * statement. You should find that this does not produce readable results. Your results may resemble the following.
Figure 2
CHECKPOINT QUESTION: Why does this query not produce the results you might typically expect from a SELECT * statement? How can the AsText() function be incorporated into a query returning every field in the table in a readable format? Paste your response into the lab report.
Execute the query you composed in the previous step, and paste a screenshot of the results into your lab report. The results should be similar to the following.
Figure 3
Calculating Distances on Earth’s Surface (Spherical, or Nonplanar Distance Calculation)
CHECKPOINT QUESTION: Your assigned research and graded threaded discussion questions this week should quickly lead you to discover that although the Pythagorean Theorem is marvelously useful for calculating the distance between points on a Cartesian planar surface, on a curved surface (such as the surface of the Earth), the further apart two points reside from one another, the greater is the error that results from misapplication of this formula to a curved (in this case, roughly spheroidal) surface. Better (less imprecise) results can be obtained by making use of the Great Circle Formula, haversine formulas, and cosine transforms. You will need to select and appropriate formula, and compose a stored procedure which can be used to calculate the geographic distances between points in your table. You will also need to use a coefficient or conversion factor so that the units of the results are expressed appropriately (e.g., kilometers, meters, miles, yards, feet, etc.), and with reasonable precision. Record your determination of the formula you will use, the reason you believe this is a good approach, and discuss both the degree of precision/error to be expected, and the units you elected to use for your measurement, and why. Record your answer in the lab report.
Compose and install your stored procedure or function for calculating geographic distance, into the database. Take a screen shot showing your SQL statement, and the result showing that the procedure was successfully created. Paste this into the lab report.
Spatial Queries: Retrieving Data Points Within a Bounding Polygon
CHECKPOINT QUESTION: Your assigned research and graded discussion questions this week will inform your understanding of the use of a bounding box or bounding polygon used to return all spatially indexed points stored in the database which reside within the area defined by the boundary. Parameters for a bounding rectangle can minimally be specified using the vertex points of either diagonal. For example, the upper-left corner, and the lower-right corner. In such case, all points with a horizontal value equal to or between the x-axis elements of the bounding points, that also have vertical value equal to or between the y-axis elements of the bounding points, reside within the qualifying region. You will want to easily be able to center this bounding rectangle on a point which you choose. How will you accomplish this? Design and document the stored procedure or function you will use to implement a bounding rectangle function, and paste your analysis and design into the lab report.
Install the stored procedure or function you designed in the previous step into the database. Create a screen shot showing the SQL used to create the procedure, and the result of its successful creation.
Write SQL to use your bounding box function, centered on your original point of interest, and all of the surrounding points of interests within 20 miles (horizontal and vertical distance) from that point. The results should show that points outside the region are not returned by this spatial query result. Paste a screen shot showing your query and the result, into your lab report.
CHECKPOINT QUESTION: It is possible for a point residing within 20 miles of your original point to correctly be omitted by the bounding box query. Explain why this is the case, and what improvements/refinements might be undertaken in order to improve upon this.
Visualization: Mapping and Displaying Spatial Data Graphically
CHECKPOINT QUESTION: Having created a stored procedure that can easily calculate the distance between any two points in your table, it will occur to you that you could easily create queries that would find “the point B, nearest a given point, A, meeting some additional criteria”. However, consider carefully that you would do this by using a calculated field (Distance). For how many points in your database would the query need to calculate Distance? What are the implications of this to performance and efficiency, if your table is quite large (millions of rows)? What approach could you take that would result in greater efficiency, perhaps allowing Distance to be calculated for a relatively small subset? (Hint: Think about your bounding box function. It returns a small set of points within a given proximity of a specified point, and does so pretty efficiently if the proper indexes are available, because it filters for latitude and longitude values within a bounded numerical range. What if you calculated Distance for only this subset, and further filtered for the minimum Distance?) Record your answers to these questions in your lab report.
With your answers to the previous questions in mind, formulate an EFFICIENT query that returns only the latitudes and longitudes for two points: the original point, and its nearest neighbor, in a single row (Hints: 1. A JOIN statement might be useful here; 2. It may be convenient to use the X() function and Y() function on your point data type columns, for example: “SELECT X(Points.location) as longitude1, Y(Points.location) as latitude1 FROM Points;”).
Test your query, and when you are satisfied that it is working correctly, paste a screen shot showing your query and its results into your lab report.
Modify your query using concatenation and string manipulation functions as needed so that the output of the results resembles:
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Notice that the highlighted elements in this output should be string literals. Only the X() and Y() values from the first and second points are values obtained from the database.
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Take a screenshot of your query, showing both the SQL and the result, and paste it into your lab report.
Test the URL you have generated in the previous query, by pasting it into the address bar of your internet browser. A route map should be generated with characteristics similar to the figure below (your map will, of course, reflect the unique points/locations you selected for your database).
Figure 4
In your lab report, provide a description explaining the route image, for example, “Closest Pizza Parlor to my home.”
CHECKPOINT QUESTION: What are the benefits of displaying spatial data visually? What are some examples of this sort of spatial visualization of GIS data OTHER than driving directions for consumers? Record your response in your lab report.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 4
Laboratory Title: Spatial Indices
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?)
t
0 notes
Text
DBM 449 Laboratory Procedures Ilab 4 Answers Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-4-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
Laboratory Procedures DeVry University College of Engineering and Information Sciences
I. OBJECTIVES
Understand and become familiar with current capabilities and limitations of the OpenGIS implementation in MySQL.
Learn to create, update, and use spatial indices.
Explore practical approaches to calculating distances between points on the Earth’s surface.
Understand fundamentals of geotagging.
Create stored procedures to determine real-world distances, and to process spatial queries returning result sets of data points within a bounding rectangle.
Explore visualization of GIS data.
II. PARTS LIST
EDUPE-APP Omnymbus MySQL Environment (https://devry.edupe.net:8300/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
The argument could be made that Business Intelligence (BI) and Data Analytics revolutionized Online Analytical Processing (OLAP) by making it simple for users to traverse, examine, and visualize different aggregations of data over the dimension of time. Geographic Information Systems, once an arcane, rare, expensive, and highly specialized type of information system, have brought about a similar revolution using the spatial dimension. As these systems have become affordable and entered the mainstream—indeed, they are now ubiquitous—they have also become mainstream; or perhaps it would be more accurate to say that mainstream DBMS systems have come to commonly adopt and integrate the specialized data structures and algorithms required to implement spatially enabled, data-driven systems at will.
In this laboratory exercise, you will create a GIS-enabled database by implementing a spatially indexed table, populating it with spatially encoded data, and creating stored procedures to provide augmented functionality to determine distances between points, and to process queries returning results containing points within spatially defined boundaries. Finally, you will learn to express and explore spatial data in its most natural and intuitive form: visually displayed as maps and plots.
This lab may be completed using MySQL running on either your own computer, or the DeVry iLab. In either case, it is presumed that you will begin the initial lab step AFTER addressing any necessary routine housekeeping chores, such as creating an appropriate schema (e.g., DBM449LAB2), and creating any necessary user accounts, permissions, and so on.
Note: At the time this lab was written, the full OPENGIS standard was not implemented in the current production release of MySQL, but new features were being added with each point release of MySQL. It is entirely possible—in fact, quite likely—that improved OPENGIS compliance, including functions for distance calculation for spherical projections (e.g., points on the Earth’s surface) and circular proximities (e.g., “within radius of”) will become built-in to the MySQL database. However, as the study of the underlying mathematical and topographical principles needed to implement non-planar distance calculations, and for determining envelope or bounding box point results are a very worthwhile study, you should implement your own stored procedures for these functions, rather than substituting any built-in capabilities that become available. You may, however, repeat steps using such features as available, in order to compare and study the similarities and differences between your calculation methods and those later implemented as part of the OPENGIS API.
Important Further Note: At the time of this writing, spatial indices are supported ONLY in the MyISAM storage engine, and not in the InnoDB or other storage engines. BE SURE TO CREATE YOUR DATABASE TABLE FOR THIS LAB USING THE MyISAM STORAGE ENGINE!
Designing a Spatially-enabled Table, and Creating a Spatial Index
Create the table indicated in the following ERD.
Figure 1
Be sure you have first addressed the assigned research for this week’s unit involving Spatial Indices, and then use the following DDL to create a spatial index on the table just created:
CREATE SPATIAL INDEX `location` ON `Points` (`location` ASC);
Paste the complete SQL Data Definition Language (DDL) you used to create this table and index into your lab report.
Choose a point of interest (e.g., your house, your local DeVry campus, etc.), and at least three additional points within 20 miles, and three additional points more than 40 miles from the first point. For example, I chose my house, and three favorite restaurants in town, and three favorite restaurants in a distant town where I used to live. Using Google Maps or other service capable of converting street addresses to geographical (longitude and latitude) coordinates with good precision, note the geolocation of each point. Record this data in your lab report.
From your research, you should anticipate that you cannot simply insert these values directly. Model your insert statements for the data to be inserted into your Points table on the following example.
INSERT INTO Points (name, location) VALUES ( ‘point1’ , GeomFromText( ‘ POINT(31.5 42.2) ‘ ) )
CHECKPOINT QUESTION: Explain what the GeomFromText() function does, and why it is necessary to use this? Paste your response into your lab report.
Run your insert statement(s) to add the data to the Points table. Paste a screen shot showing your SQL statement AND result into your lab report.
Displaying Spatial Data in Human-readable Form
Attempt to retrieve all of the table’s contents using a SELECT * statement. You should find that this does not produce readable results. Your results may resemble the following.
Figure 2
CHECKPOINT QUESTION: Why does this query not produce the results you might typically expect from a SELECT * statement? How can the AsText() function be incorporated into a query returning every field in the table in a readable format? Paste your response into the lab report.
Execute the query you composed in the previous step, and paste a screenshot of the results into your lab report. The results should be similar to the following.
Figure 3
Calculating Distances on Earth’s Surface (Spherical, or Nonplanar Distance Calculation)
CHECKPOINT QUESTION: Your assigned research and graded threaded discussion questions this week should quickly lead you to discover that although the Pythagorean Theorem is marvelously useful for calculating the distance between points on a Cartesian planar surface, on a curved surface (such as the surface of the Earth), the further apart two points reside from one another, the greater is the error that results from misapplication of this formula to a curved (in this case, roughly spheroidal) surface. Better (less imprecise) results can be obtained by making use of the Great Circle Formula, haversine formulas, and cosine transforms. You will need to select and appropriate formula, and compose a stored procedure which can be used to calculate the geographic distances between points in your table. You will also need to use a coefficient or conversion factor so that the units of the results are expressed appropriately (e.g., kilometers, meters, miles, yards, feet, etc.), and with reasonable precision. Record your determination of the formula you will use, the reason you believe this is a good approach, and discuss both the degree of precision/error to be expected, and the units you elected to use for your measurement, and why. Record your answer in the lab report.
Compose and install your stored procedure or function for calculating geographic distance, into the database. Take a screen shot showing your SQL statement, and the result showing that the procedure was successfully created. Paste this into the lab report.
Spatial Queries: Retrieving Data Points Within a Bounding Polygon
CHECKPOINT QUESTION: Your assigned research and graded discussion questions this week will inform your understanding of the use of a bounding box or bounding polygon used to return all spatially indexed points stored in the database which reside within the area defined by the boundary. Parameters for a bounding rectangle can minimally be specified using the vertex points of either diagonal. For example, the upper-left corner, and the lower-right corner. In such case, all points with a horizontal value equal to or between the x-axis elements of the bounding points, that also have vertical value equal to or between the y-axis elements of the bounding points, reside within the qualifying region. You will want to easily be able to center this bounding rectangle on a point which you choose. How will you accomplish this? Design and document the stored procedure or function you will use to implement a bounding rectangle function, and paste your analysis and design into the lab report.
Install the stored procedure or function you designed in the previous step into the database. Create a screen shot showing the SQL used to create the procedure, and the result of its successful creation.
Write SQL to use your bounding box function, centered on your original point of interest, and all of the surrounding points of interests within 20 miles (horizontal and vertical distance) from that point. The results should show that points outside the region are not returned by this spatial query result. Paste a screen shot showing your query and the result, into your lab report.
CHECKPOINT QUESTION: It is possible for a point residing within 20 miles of your original point to correctly be omitted by the bounding box query. Explain why this is the case, and what improvements/refinements might be undertaken in order to improve upon this.
Visualization: Mapping and Displaying Spatial Data Graphically
CHECKPOINT QUESTION: Having created a stored procedure that can easily calculate the distance between any two points in your table, it will occur to you that you could easily create queries that would find “the point B, nearest a given point, A, meeting some additional criteria”. However, consider carefully that you would do this by using a calculated field (Distance). For how many points in your database would the query need to calculate Distance? What are the implications of this to performance and efficiency, if your table is quite large (millions of rows)? What approach could you take that would result in greater efficiency, perhaps allowing Distance to be calculated for a relatively small subset? (Hint: Think about your bounding box function. It returns a small set of points within a given proximity of a specified point, and does so pretty efficiently if the proper indexes are available, because it filters for latitude and longitude values within a bounded numerical range. What if you calculated Distance for only this subset, and further filtered for the minimum Distance?) Record your answers to these questions in your lab report.
With your answers to the previous questions in mind, formulate an EFFICIENT query that returns only the latitudes and longitudes for two points: the original point, and its nearest neighbor, in a single row (Hints: 1. A JOIN statement might be useful here; 2. It may be convenient to use the X() function and Y() function on your point data type columns, for example: “SELECT X(Points.location) as longitude1, Y(Points.location) as latitude1 FROM Points;”).
Test your query, and when you are satisfied that it is working correctly, paste a screen shot showing your query and its results into your lab report.
Modify your query using concatenation and string manipulation functions as needed so that the output of the results resembles:
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Notice that the highlighted elements in this output should be string literals. Only the X() and Y() values from the first and second points are values obtained from the database.
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Take a screenshot of your query, showing both the SQL and the result, and paste it into your lab report.
Test the URL you have generated in the previous query, by pasting it into the address bar of your internet browser. A route map should be generated with characteristics similar to the figure below (your map will, of course, reflect the unique points/locations you selected for your database).
Figure 4
In your lab report, provide a description explaining the route image, for example, “Closest Pizza Parlor to my home.”
CHECKPOINT QUESTION: What are the benefits of displaying spatial data visually? What are some examples of this sort of spatial visualization of GIS data OTHER than driving directions for consumers? Record your response in your lab report.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 4
Laboratory Title: Spatial Indices
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?)
0 notes
Text
DBM 449 Laboratory Procedures Ilab 4 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-4-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
Laboratory Procedures DeVry University College of Engineering and Information Sciences
I. OBJECTIVES
Understand and become familiar with current capabilities and limitations of the OpenGIS implementation in MySQL.
Learn to create, update, and use spatial indices.
Explore practical approaches to calculating distances between points on the Earth’s surface.
Understand fundamentals of geotagging.
Create stored procedures to determine real-world distances, and to process spatial queries returning result sets of data points within a bounding rectangle.
Explore visualization of GIS data.
II. PARTS LIST
EDUPE-APP Omnymbus MySQL Environment (https://devry.edupe.net:8300/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
The argument could be made that Business Intelligence (BI) and Data Analytics revolutionized Online Analytical Processing (OLAP) by making it simple for users to traverse, examine, and visualize different aggregations of data over the dimension of time. Geographic Information Systems, once an arcane, rare, expensive, and highly specialized type of information system, have brought about a similar revolution using the spatial dimension. As these systems have become affordable and entered the mainstream—indeed, they are now ubiquitous—they have also become mainstream; or perhaps it would be more accurate to say that mainstream DBMS systems have come to commonly adopt and integrate the specialized data structures and algorithms required to implement spatially enabled, data-driven systems at will.
In this laboratory exercise, you will create a GIS-enabled database by implementing a spatially indexed table, populating it with spatially encoded data, and creating stored procedures to provide augmented functionality to determine distances between points, and to process queries returning results containing points within spatially defined boundaries. Finally, you will learn to express and explore spatial data in its most natural and intuitive form: visually displayed as maps and plots.
This lab may be completed using MySQL running on either your own computer, or the DeVry iLab. In either case, it is presumed that you will begin the initial lab step AFTER addressing any necessary routine housekeeping chores, such as creating an appropriate schema (e.g., DBM449LAB2), and creating any necessary user accounts, permissions, and so on.
Note: At the time this lab was written, the full OPENGIS standard was not implemented in the current production release of MySQL, but new features were being added with each point release of MySQL. It is entirely possible—in fact, quite likely—that improved OPENGIS compliance, including functions for distance calculation for spherical projections (e.g., points on the Earth’s surface) and circular proximities (e.g., “within radius of”) will become built-in to the MySQL database. However, as the study of the underlying mathematical and topographical principles needed to implement non-planar distance calculations, and for determining envelope or bounding box point results are a very worthwhile study, you should implement your own stored procedures for these functions, rather than substituting any built-in capabilities that become available. You may, however, repeat steps using such features as available, in order to compare and study the similarities and differences between your calculation methods and those later implemented as part of the OPENGIS API.
Important Further Note: At the time of this writing, spatial indices are supported ONLY in the MyISAM storage engine, and not in the InnoDB or other storage engines. BE SURE TO CREATE YOUR DATABASE TABLE FOR THIS LAB USING THE MyISAM STORAGE ENGINE!
Designing a Spatially-enabled Table, and Creating a Spatial Index
Create the table indicated in the following ERD.
Figure 1
Be sure you have first addressed the assigned research for this week’s unit involving Spatial Indices, and then use the following DDL to create a spatial index on the table just created:
CREATE SPATIAL INDEX `location` ON `Points` (`location` ASC);
Paste the complete SQL Data Definition Language (DDL) you used to create this table and index into your lab report.
Choose a point of interest (e.g., your house, your local DeVry campus, etc.), and at least three additional points within 20 miles, and three additional points more than 40 miles from the first point. For example, I chose my house, and three favorite restaurants in town, and three favorite restaurants in a distant town where I used to live. Using Google Maps or other service capable of converting street addresses to geographical (longitude and latitude) coordinates with good precision, note the geolocation of each point. Record this data in your lab report.
From your research, you should anticipate that you cannot simply insert these values directly. Model your insert statements for the data to be inserted into your Points table on the following example.
INSERT INTO Points (name, location) VALUES ( ‘point1’ , GeomFromText( ‘ POINT(31.5 42.2) ‘ ) )
CHECKPOINT QUESTION: Explain what the GeomFromText() function does, and why it is necessary to use this? Paste your response into your lab report.
Run your insert statement(s) to add the data to the Points table. Paste a screen shot showing your SQL statement AND result into your lab report.
Displaying Spatial Data in Human-readable Form
Attempt to retrieve all of the table’s contents using a SELECT * statement. You should find that this does not produce readable results. Your results may resemble the following.
Figure 2
CHECKPOINT QUESTION: Why does this query not produce the results you might typically expect from a SELECT * statement? How can the AsText() function be incorporated into a query returning every field in the table in a readable format? Paste your response into the lab report.
Execute the query you composed in the previous step, and paste a screenshot of the results into your lab report. The results should be similar to the following.
Figure 3
Calculating Distances on Earth’s Surface (Spherical, or Nonplanar Distance Calculation)
CHECKPOINT QUESTION: Your assigned research and graded threaded discussion questions this week should quickly lead you to discover that although the Pythagorean Theorem is marvelously useful for calculating the distance between points on a Cartesian planar surface, on a curved surface (such as the surface of the Earth), the further apart two points reside from one another, the greater is the error that results from misapplication of this formula to a curved (in this case, roughly spheroidal) surface. Better (less imprecise) results can be obtained by making use of the Great Circle Formula, haversine formulas, and cosine transforms. You will need to select and appropriate formula, and compose a stored procedure which can be used to calculate the geographic distances between points in your table. You will also need to use a coefficient or conversion factor so that the units of the results are expressed appropriately (e.g., kilometers, meters, miles, yards, feet, etc.), and with reasonable precision. Record your determination of the formula you will use, the reason you believe this is a good approach, and discuss both the degree of precision/error to be expected, and the units you elected to use for your measurement, and why. Record your answer in the lab report.
Compose and install your stored procedure or function for calculating geographic distance, into the database. Take a screen shot showing your SQL statement, and the result showing that the procedure was successfully created. Paste this into the lab report.
Spatial Queries: Retrieving Data Points Within a Bounding Polygon
CHECKPOINT QUESTION: Your assigned research and graded discussion questions this week will inform your understanding of the use of a bounding box or bounding polygon used to return all spatially indexed points stored in the database which reside within the area defined by the boundary. Parameters for a bounding rectangle can minimally be specified using the vertex points of either diagonal. For example, the upper-left corner, and the lower-right corner. In such case, all points with a horizontal value equal to or between the x-axis elements of the bounding points, that also have vertical value equal to or between the y-axis elements of the bounding points, reside within the qualifying region. You will want to easily be able to center this bounding rectangle on a point which you choose. How will you accomplish this? Design and document the stored procedure or function you will use to implement a bounding rectangle function, and paste your analysis and design into the lab report.
Install the stored procedure or function you designed in the previous step into the database. Create a screen shot showing the SQL used to create the procedure, and the result of its successful creation.
Write SQL to use your bounding box function, centered on your original point of interest, and all of the surrounding points of interests within 20 miles (horizontal and vertical distance) from that point. The results should show that points outside the region are not returned by this spatial query result. Paste a screen shot showing your query and the result, into your lab report.
CHECKPOINT QUESTION: It is possible for a point residing within 20 miles of your original point to correctly be omitted by the bounding box query. Explain why this is the case, and what improvements/refinements might be undertaken in order to improve upon this.
Visualization: Mapping and Displaying Spatial Data Graphically
CHECKPOINT QUESTION: Having created a stored procedure that can easily calculate the distance between any two points in your table, it will occur to you that you could easily create queries that would find “the point B, nearest a given point, A, meeting some additional criteria”. However, consider carefully that you would do this by using a calculated field (Distance). For how many points in your database would the query need to calculate Distance? What are the implications of this to performance and efficiency, if your table is quite large (millions of rows)? What approach could you take that would result in greater efficiency, perhaps allowing Distance to be calculated for a relatively small subset? (Hint: Think about your bounding box function. It returns a small set of points within a given proximity of a specified point, and does so pretty efficiently if the proper indexes are available, because it filters for latitude and longitude values within a bounded numerical range. What if you calculated Distance for only this subset, and further filtered for the minimum Distance?) Record your answers to these questions in your lab report.
With your answers to the previous questions in mind, formulate an EFFICIENT query that returns only the latitudes and longitudes for two points: the original point, and its nearest neighbor, in a single row (Hints: 1. A JOIN statement might be useful here; 2. It may be convenient to use the X() function and Y() function on your point data type columns, for example: “SELECT X(Points.location) as longitude1, Y(Points.location) as latitude1 FROM Points;”).
Test your query, and when you are satisfied that it is working correctly, paste a screen shot showing your query and its results into your lab report.
Modify your query using concatenation and string manipulation functions as needed so that the output of the results resembles:
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Notice that the highlighted elements in this output should be string literals. Only the X() and Y() values from the first and second points are values obtained from the database.
https://www.google.com/maps/dir/34.297106,+-119.164864/34.279759,+-119.191578
Take a screenshot of your query, showing both the SQL and the result, and paste it into your lab report.
Test the URL you have generated in the previous query, by pasting it into the address bar of your internet browser. A route map should be generated with characteristics similar to the figure below (your map will, of course, reflect the unique points/locations you selected for your database).
Figure 4
In your lab report, provide a description explaining the route image, for example, “Closest Pizza Parlor to my home.”
CHECKPOINT QUESTION: What are the benefits of displaying spatial data visually? What are some examples of this sort of spatial visualization of GIS data OTHER than driving directions for consumers? Record your response in your lab report.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 4
Laboratory Title: Spatial Indices
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?)
0 notes
Text
DBM 449 Laboratory Procedures iLab 6 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-6-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
I. OBJECTIVES
Understand and become familiar with the SQL Analytical Extensions.
Learn to create, use, and maintain materialized views, and their functional equivalents.
Effectively apply Advanced Aggregate SQL Operations, such as GROUP BY ROLLUP to solve business intelligence questions and analytical processing problems.
II. PARTS LIST
EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
Scenario and Summary
For the lab this week, we are going to look at how the ROLLUP and CUBE extensions available in SQL can be used to create query result sets that have more than one dimension to them. Both of these extensions are used in conjunction with the GROUP BY clause and allow for a much broader look at the data.
To record your work for this lab use the lab report found at the end of this document. As in your previous labs, you will need to copy/paste your SQL statements and results into this document. Upon completion and prior to the due date, submit this document to the appropriate Dropbox.
iLAB STEPS
STEP 1: Setting Up
For this lab you will be using a different user and set of tables than you have used so far for other labs. To set up your instance you will need to do the following.
The first thing you will do for this lab is to run the following SQL Script. Begin by creating the DBM449Lab6 Schema, and creating any user accounts and privileges you wish to use (at least one).
Run the following script to create and populate a set of tables that will be used for this lab. Instructions for this are outlined in Step 1.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;
USE `DBM449Lab6` ;
— —————————————————–
— Table `DBM449Lab6`.`DISTRICT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`DISTRICT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`DISTRICT` (
`DIST_ID` INT(11) NOT NULL,
`DIST_NAME` VARCHAR(10) NULL DEFAULT NULL,
PRIMARY KEY (`DIST_ID`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`CUSTOMER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`CUSTOMER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`CUSTOMER` (
`CUST_CODE` DECIMAL(10,0) NOT NULL,
`CUST_LNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_FNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_INITIAL` CHAR(1) NULL DEFAULT NULL,
`CUST_STATE` CHAR(2) NULL DEFAULT NULL,
`DIST_ID` INT(11) NOT NULL,
PRIMARY KEY (`CUST_CODE`),
CONSTRAINT `fk_CUSTOMER_DISTRICT1`
FOREIGN KEY (`DIST_ID`)
REFERENCES `DBM449Lab6`.`DISTRICT` (`DIST_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_CUSTOMER_DISTRICT1_idx` ON `DBM449Lab6`.`CUSTOMER` (`DIST_ID` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SUPPLIER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SUPPLIER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SUPPLIER` (
`SUP_CODE` INT(11) NOT NULL,
`SUP_NAME` VARCHAR(35) NULL DEFAULT NULL,
`SUP_AREACODE` CHAR(3) NULL DEFAULT NULL,
`SUP_STATE` CHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (`SUP_CODE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
— —————————————————–
— Table `DBM449Lab6`.`PRODUCT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`PRODUCT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`PRODUCT` (
`PROD_CODE` VARCHAR(10) NOT NULL,
`PROD_DESCRIPT` VARCHAR(35) NULL DEFAULT NULL,
`PROD_CATEGORY` VARCHAR(5) NULL DEFAULT NULL,
`SUP_CODE` INT(11) NOT NULL,
PRIMARY KEY (`PROD_CODE`),
CONSTRAINT `fk_PRODUCT_SUPPLIER`
FOREIGN KEY (`SUP_CODE`)
REFERENCES `DBM449Lab6`.`SUPPLIER` (`SUP_CODE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_PRODUCT_SUPPLIER_idx` ON `DBM449Lab6`.`PRODUCT` (`SUP_CODE` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SALES`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SALES` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SALES` (
`TIME_ID` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`CUST_CODE` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`PROD_CODE` VARCHAR(10) NOT NULL DEFAULT ”,
`SALE_UNITS` DECIMAL(10,0) NULL DEFAULT NULL,
`SALE_PRICE` DECIMAL(10,2) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`, `CUST_CODE`, `PROD_CODE`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`TIME`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`TIME` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`TIME` (
`TIME_ID` INT(11) NOT NULL,
`TIME_YEAR` INT(11) NULL DEFAULT NULL,
`TIME_MONTH` INT(11) NULL DEFAULT NULL,
`TIME_DAY` INT(11) NULL DEFAULT NULL,
`TIME_QTR` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
INSERT INTO SUPPLIER VALUES(31225,’Bryson, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31226,’SuperLoo, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(31231,’DE Supply’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31344,’Gomez Bros.’ ,’615′,’KY’);
INSERT INTO SUPPLIER VALUES(32567,’Dome Supply’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(33119,’Randsets Ltd.’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(44004,’Brackman Bros.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(44288,’ORDVA, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55443,’BK, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(55501,’Damal Supplies’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55595,’Rubicon Systems’ ,’904′,’FL’);
INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’CAT1′,55595);
INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘2232/QTY’,’BD jigsaw, 12-in. blade’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘2232/QWE’,’BD jigsaw, 8-in. blade’ ,’CAT3′,44288);
INSERT INTO PRODUCT VALUES(‘2238/QPD’,’BD cordless drill, 1/2-in.’ ,’CAT3′,55595);
INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’CAT3′,31225);
INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’CAT4’,31225);
INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’CAT2’,31231);
INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’CAT3′,55595);
INSERT INTO DISTRICT VALUES(1,’NE’);
INSERT INTO DISTRICT VALUES(2,’NW’);
INSERT INTO DISTRICT VALUES(3,’SE’);
INSERT INTO DISTRICT VALUES(4,’SW’);
INSERT INTO CUSTOMER VALUES(110010,’Ramas’ ,’Alfred’,’A’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110011,’Dunne’ ,’Leona’ ,’K’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110012,’Smith’ ,’Kathy’ ,’W’ ,’NY’,1);
INSERT INTO CUSTOMER VALUES(110013,’Olowski’ ,’Paul’ ,’F’ ,’NJ’,1);
INSERT INTO CUSTOMER VALUES(110014,’Orlando’ ,’Myron’ ,NULL,’CO’,2);
INSERT INTO CUSTOMER VALUES(110015,’O”Brian’,’Amy’ ,’B’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110016,’Brown’ ,’James’ ,’G’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110017,’Williams’,’George’,NULL,’CA’,4);
INSERT INTO CUSTOMER VALUES(110018,’Farriss’ ,’Anne’ ,’G’ ,’CA’,4);
INSERT INTO CUSTOMER VALUES(110019,’Smith’ ,’Olette’,’K’ ,’CO’,2);
INSERT INTO TIME VALUES(201,2009,09,29,3);
INSERT INTO TIME VALUES(202,2009,09,30,3);
INSERT INTO TIME VALUES(203,2009,09,31,3);
INSERT INTO TIME VALUES(206,2009,10,03,4);
INSERT INTO TIME VALUES(207,2009,10,04,4);
INSERT INTO SALES VALUES(201,110014,’13-Q2/P2′,1,14.99);
INSERT INTO SALES VALUES(201,110014,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(201,110015,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(201,110015,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(202,110016,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(202,110016,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(202,110017,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(202,110017,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(202,110018,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(203,110012,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(203,110014,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(203,110015,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110015,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(203,110016,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(203,110016,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(203,110016,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(203,110017,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(203,110017,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110017,’13-Q2/P2′,1,15.99);
INSERT INTO SALES VALUES(203,110018,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110018,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(203,110018,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(203,110019,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(206,110010,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(206,110010,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(206,110010,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(206,110010,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(206,110011,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(206,110011,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(206,110012,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(206,110012,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(207,110013,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(207,110013,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(207,110013,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(207,110014,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(207,110015,’23109-HB’,1,11.95);
Once the script has finished running, then issue a SHOW TABLES; sql statement. Make sure that you see the following tables listed.
STEP 2: Using the ROLLUP Extension
In this section of the lab you are going to create a sales report that will show a supplier code, product code and the total sales for each product based on unit price times a quantity. More importantly the column that shows the total sales will also show a grand total for the supplier as well as a grand total over all (this will be the last row of data shown). To do this you will use the ROLLUP extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.
SUPPLIER CODE
PRODUCT
TOTAL SALES
31225
23109-HB
119.50
31225
PVC23DRT
199.58
31225
SM-18277
53.70
31225
372.78
31344
13-Q2/P2
254.84
31344
54778-2T
71.88
31344
326.72
33119
1546-QQ2
623.90
33119
623.90
44288
2232/QTY
219.84
44288
89-WRE-Q
517.90
44288
737.74
55595
2238/QPD
77.90
55595
WR3/TT3
671.70
55595
749.60
2810.74
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 3: Using the CUBE Extension
In this section of the lab you are going to examine the creation of a sales report that will show a month code, product code and the total sales for each product based on unit price times a quantity. At the time of this writing, MySQL does not implement the CUBE clause, so we will study an example constructed using the ORACLE DBMS. In this report, the column that shows the total sales will also show a subtotal for each month (in this case representing a quarter). Following the monthly totals for each product and the subtotal by month then the report will list a total for each product sold during the period with a grand total for all sales during the period (this will be the last row of data shown). To do this, the CUBE extension is used as part of the GROUP BY clause in the query. Aliases are used for the column names so that the output columns in the result set look like the following.
MONTH PRODUCT TOTAL SALES
———- ———- ———–
9 13-Q2/P2 142.91
9 1546-QQ2 623.9
9 2232/QTY 109.92
9 2238/QPD 77.9
9 23109-HB 71.7
9 54778-2T 47.92
9 89-WRE-Q 258.95
9 PVC23DRT 99.79
9 SM-18277 26.85
9 WR3/TT3 335.85
9 1795.69
MONTH PRODUCT TOTAL SALES
———- ———- ———–
10 13-Q2/P2 111.93
10 2232/QTY 109.92
10 23109-HB 47.8
10 54778-2T 23.96
10 89-WRE-Q 258.95
10 PVC23DRT 99.79
10 SM-18277 26.85
10 WR3/TT3 335.85
10 1015.05
13-Q2/P2 254.84
1546-QQ2 623.9
MONTH PRODUCT TOTAL SALES
———- ———- ———–
2232/QTY 219.84
2238/QPD 77.9
23109-HB 119.5
54778-2T 71.88
89-WRE-Q 517.9
PVC23DRT 199.58
SM-18277 53.7
WR3/TT3 671.7
2810.74
31 rows selected.
Here is the ORACLE PL/SQL query which generated these results. Please study the example carefully.
SQL> SELECT T.TIME_MONTH AS “MONTH”, P.PROD_COD
2 SUM(S.SALE_UNITS*S.SALE_PRICE) AS “TOTAL S
3 FROM SALES S, PRODUCT P, TIME T
4 WHERE S.TIME_ID = T.TIME_ID
5 AND S.PROD_CODE = P.PROD_CODE
6 GROUP BY CUBE (T.TIME_MONTH, P.PROD_CODE)
7 ORDER BY T.TIME_MONTH, P.PROD_CODE;
NOTE: This query will produce the same results using NATURAL JOIN.
SELECT TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”, SUM(SALE_UNITS*SALE_PRICE) AS “TOTAL SALES”
FROM TIME NATURAL JOIN SALES NATURAL JOIN PRODUCT
GROUP BY CUBE (TIME_MONTH, PROD_CODE)
ORDER BY TIME_MONTH, PROD_CODE;
Notice that this report uses the SALES, PRODUCT and TIME tables. It is also possible to write the query using NATURAL JOIN but some developers may feel more comfortable using a traditional JOIN method that will work just as well. Notice that the grand total amount of 2810.74 is the same total as in step 2.
CHECKPOINT QUESTION: Research Data Warehouse Solutions that may be integrated into MySQL enterprise architecture (e.g., PENTAHO). Explain how these solutions may be employed to overcome the current limitations in MySQL support for OLAP functionality. Pay special attention to the reporting and analytic capabilities of these solutions. Based on your analysis and research, do you believe that analytic functions such as GROUP BY CUBE are best implemented in the database (MySQL), the Data Warehouse and Reporting Engine (e.g., PENTAHO), or both. Fully explain your analysis and conclusions.
STEP 4: Materialized Views and a Refresh Procedure
Materialized views (MVs), sometimes referred to as snapshots are a very important aspect of dealing with data when doing data mining or working with a data warehouse. Unlike regular views, a materialized view does not always automatically react to changes made in the base tables of the view. In database systems that directly implement MVs, a Materialized View Log must be created on each base table that will be used in the view. As discussed and explored briefly at the conclusion of Lab 5, MySQL does not directly implement materialized views, but these may be effectively emulated using tables constructed with the same attributes as the view, provided that steps are taken to ensure the automatic update or refresh of the table. For instructional purposes, we will accomplish this by the simple expedient of creating a stored procedure which will drop, recreate, and rebuild the table serving as the view. In our exploration of the concept of the materialized view, we are going to create we are going to use the TIME and the SALES tables.
Because we will emulate an MV in our solution, we will craft a stored procedure to update the MV.
Create and install a stored procedure, named REFRESH_MV_SALESBYMONTH, using the following SQL SELECT statement to guide you. This SELECT statement demonstrates the data to be selected to populate the table. Your stored procedure must: (1) drop the table if it exists; and (2) SELECT INTO the table MV_SALESBYMONTH to create and populate it. Note that, with this logic, no separate logic is needed to first create the view—simply run the stored procedure. The details of the implementation are left to you.
SELECT TIME_YEAR AS “YEAR”, TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”,
SUM(SALE_UNITS) AS “UNITS SOLD”, SUM(SALE_UNITS*SALE_PRICE) AS “SALES TOTAL”
FROM TIME T, SALES S
WHERE S.TIME_ID = T.TIME_ID
GROUP BY TIME_YEAR, TIME_MONTH, PROD_CODE;
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 5: Using the Materialized View
First, CALL REFRESH_MV_SALESBYMONTH. This will make sure that the view is created, and currently up-to-date.
Run the SQL statement: SELECT * FROM MV_SALESBYMONTH. The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.
YEAR MONTH PRODUCT CO UNITS SOLD SALES TOTAL
Now we are going to add some data and update the view. Remember, we must manually run the stored procedure to update the view, as it will not (yet!) automatically itself.
To begin with, insert the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
CALL the stored procedure to refresh the view.
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
Delete the row you just added to the SALES table, and call the stored procedure to refresh the view, proving that things are up-to-date.
Create a Trigger on the SALES table so that any insert automatically fires off the stored procedure to update the view.
Test your trigger, by again inserting the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
CHECKPOINT QUESTION: For instructional purposes and simplicity, we have indulged in the expedient of dropping and recreating the entire MV_SALESBYMONTH table, which now occurs each time a new record is inserted on the SALES table. Would this be unacceptable in practice? Explain why or why not. What other events, and what other tables would require wire-up of triggers in order to ensure that the view is kept up-to-date at all times?
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 3
Laboratory Title: SWL Analytical Extensions & Materialized Views
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?)
0 notes
Text
DBM 449 Laboratory Procedures iLab 6 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-6-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
I. OBJECTIVES
Understand and become familiar with the SQL Analytical Extensions.
Learn to create, use, and maintain materialized views, and their functional equivalents.
Effectively apply Advanced Aggregate SQL Operations, such as GROUP BY ROLLUP to solve business intelligence questions and analytical processing problems.
II. PARTS LIST
EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
Scenario and Summary
For the lab this week, we are going to look at how the ROLLUP and CUBE extensions available in SQL can be used to create query result sets that have more than one dimension to them. Both of these extensions are used in conjunction with the GROUP BY clause and allow for a much broader look at the data.
To record your work for this lab use the lab report found at the end of this document. As in your previous labs, you will need to copy/paste your SQL statements and results into this document. Upon completion and prior to the due date, submit this document to the appropriate Dropbox.
iLAB STEPS
STEP 1: Setting Up
For this lab you will be using a different user and set of tables than you have used so far for other labs. To set up your instance you will need to do the following.
The first thing you will do for this lab is to run the following SQL Script. Begin by creating the DBM449Lab6 Schema, and creating any user accounts and privileges you wish to use (at least one).
Run the following script to create and populate a set of tables that will be used for this lab. Instructions for this are outlined in Step 1.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;
USE `DBM449Lab6` ;
— —————————————————–
— Table `DBM449Lab6`.`DISTRICT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`DISTRICT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`DISTRICT` (
`DIST_ID` INT(11) NOT NULL,
`DIST_NAME` VARCHAR(10) NULL DEFAULT NULL,
PRIMARY KEY (`DIST_ID`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`CUSTOMER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`CUSTOMER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`CUSTOMER` (
`CUST_CODE` DECIMAL(10,0) NOT NULL,
`CUST_LNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_FNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_INITIAL` CHAR(1) NULL DEFAULT NULL,
`CUST_STATE` CHAR(2) NULL DEFAULT NULL,
`DIST_ID` INT(11) NOT NULL,
PRIMARY KEY (`CUST_CODE`),
CONSTRAINT `fk_CUSTOMER_DISTRICT1`
FOREIGN KEY (`DIST_ID`)
REFERENCES `DBM449Lab6`.`DISTRICT` (`DIST_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_CUSTOMER_DISTRICT1_idx` ON `DBM449Lab6`.`CUSTOMER` (`DIST_ID` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SUPPLIER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SUPPLIER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SUPPLIER` (
`SUP_CODE` INT(11) NOT NULL,
`SUP_NAME` VARCHAR(35) NULL DEFAULT NULL,
`SUP_AREACODE` CHAR(3) NULL DEFAULT NULL,
`SUP_STATE` CHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (`SUP_CODE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
— —————————————————–
— Table `DBM449Lab6`.`PRODUCT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`PRODUCT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`PRODUCT` (
`PROD_CODE` VARCHAR(10) NOT NULL,
`PROD_DESCRIPT` VARCHAR(35) NULL DEFAULT NULL,
`PROD_CATEGORY` VARCHAR(5) NULL DEFAULT NULL,
`SUP_CODE` INT(11) NOT NULL,
PRIMARY KEY (`PROD_CODE`),
CONSTRAINT `fk_PRODUCT_SUPPLIER`
FOREIGN KEY (`SUP_CODE`)
REFERENCES `DBM449Lab6`.`SUPPLIER` (`SUP_CODE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_PRODUCT_SUPPLIER_idx` ON `DBM449Lab6`.`PRODUCT` (`SUP_CODE` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SALES`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SALES` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SALES` (
`TIME_ID` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`CUST_CODE` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`PROD_CODE` VARCHAR(10) NOT NULL DEFAULT ”,
`SALE_UNITS` DECIMAL(10,0) NULL DEFAULT NULL,
`SALE_PRICE` DECIMAL(10,2) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`, `CUST_CODE`, `PROD_CODE`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`TIME`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`TIME` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`TIME` (
`TIME_ID` INT(11) NOT NULL,
`TIME_YEAR` INT(11) NULL DEFAULT NULL,
`TIME_MONTH` INT(11) NULL DEFAULT NULL,
`TIME_DAY` INT(11) NULL DEFAULT NULL,
`TIME_QTR` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
INSERT INTO SUPPLIER VALUES(31225,’Bryson, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31226,’SuperLoo, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(31231,’DE Supply’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31344,’Gomez Bros.’ ,’615′,’KY’);
INSERT INTO SUPPLIER VALUES(32567,’Dome Supply’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(33119,’Randsets Ltd.’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(44004,’Brackman Bros.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(44288,’ORDVA, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55443,’BK, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(55501,’Damal Supplies’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55595,’Rubicon Systems’ ,’904′,’FL’);
INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’CAT1′,55595);
INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘2232/QTY’,’BD jigsaw, 12-in. blade’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘2232/QWE’,’BD jigsaw, 8-in. blade’ ,’CAT3′,44288);
INSERT INTO PRODUCT VALUES(‘2238/QPD’,’BD cordless drill, 1/2-in.’ ,’CAT3′,55595);
INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’CAT3′,31225);
INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’CAT4’,31225);
INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’CAT2’,31231);
INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’CAT3′,55595);
INSERT INTO DISTRICT VALUES(1,’NE’);
INSERT INTO DISTRICT VALUES(2,’NW’);
INSERT INTO DISTRICT VALUES(3,’SE’);
INSERT INTO DISTRICT VALUES(4,’SW’);
INSERT INTO CUSTOMER VALUES(110010,’Ramas’ ,’Alfred’,’A’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110011,’Dunne’ ,’Leona’ ,’K’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110012,’Smith’ ,’Kathy’ ,’W’ ,’NY’,1);
INSERT INTO CUSTOMER VALUES(110013,’Olowski’ ,’Paul’ ,’F’ ,’NJ’,1);
INSERT INTO CUSTOMER VALUES(110014,’Orlando’ ,’Myron’ ,NULL,’CO’,2);
INSERT INTO CUSTOMER VALUES(110015,’O”Brian’,’Amy’ ,’B’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110016,’Brown’ ,’James’ ,’G’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110017,’Williams’,’George’,NULL,’CA’,4);
INSERT INTO CUSTOMER VALUES(110018,’Farriss’ ,’Anne’ ,’G’ ,’CA’,4);
INSERT INTO CUSTOMER VALUES(110019,’Smith’ ,’Olette’,’K’ ,’CO’,2);
INSERT INTO TIME VALUES(201,2009,09,29,3);
INSERT INTO TIME VALUES(202,2009,09,30,3);
INSERT INTO TIME VALUES(203,2009,09,31,3);
INSERT INTO TIME VALUES(206,2009,10,03,4);
INSERT INTO TIME VALUES(207,2009,10,04,4);
INSERT INTO SALES VALUES(201,110014,’13-Q2/P2′,1,14.99);
INSERT INTO SALES VALUES(201,110014,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(201,110015,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(201,110015,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(202,110016,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(202,110016,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(202,110017,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(202,110017,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(202,110018,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(203,110012,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(203,110014,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(203,110015,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110015,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(203,110016,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(203,110016,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(203,110016,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(203,110017,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(203,110017,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110017,’13-Q2/P2′,1,15.99);
INSERT INTO SALES VALUES(203,110018,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110018,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(203,110018,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(203,110019,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(206,110010,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(206,110010,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(206,110010,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(206,110010,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(206,110011,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(206,110011,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(206,110012,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(206,110012,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(207,110013,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(207,110013,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(207,110013,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(207,110014,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(207,110015,’23109-HB’,1,11.95);
Once the script has finished running, then issue a SHOW TABLES; sql statement. Make sure that you see the following tables listed.
STEP 2: Using the ROLLUP Extension
In this section of the lab you are going to create a sales report that will show a supplier code, product code and the total sales for each product based on unit price times a quantity. More importantly the column that shows the total sales will also show a grand total for the supplier as well as a grand total over all (this will be the last row of data shown). To do this you will use the ROLLUP extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.
SUPPLIER CODE
PRODUCT
TOTAL SALES
31225
23109-HB
119.50
31225
PVC23DRT
199.58
31225
SM-18277
53.70
31225
372.78
31344
13-Q2/P2
254.84
31344
54778-2T
71.88
31344
326.72
33119
1546-QQ2
623.90
33119
623.90
44288
2232/QTY
219.84
44288
89-WRE-Q
517.90
44288
737.74
55595
2238/QPD
77.90
55595
WR3/TT3
671.70
55595
749.60
2810.74
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 3: Using the CUBE Extension
In this section of the lab you are going to examine the creation of a sales report that will show a month code, product code and the total sales for each product based on unit price times a quantity. At the time of this writing, MySQL does not implement the CUBE clause, so we will study an example constructed using the ORACLE DBMS. In this report, the column that shows the total sales will also show a subtotal for each month (in this case representing a quarter). Following the monthly totals for each product and the subtotal by month then the report will list a total for each product sold during the period with a grand total for all sales during the period (this will be the last row of data shown). To do this, the CUBE extension is used as part of the GROUP BY clause in the query. Aliases are used for the column names so that the output columns in the result set look like the following.
MONTH PRODUCT TOTAL SALES
———- ———- ———–
9 13-Q2/P2 142.91
9 1546-QQ2 623.9
9 2232/QTY 109.92
9 2238/QPD 77.9
9 23109-HB 71.7
9 54778-2T 47.92
9 89-WRE-Q 258.95
9 PVC23DRT 99.79
9 SM-18277 26.85
9 WR3/TT3 335.85
9 1795.69
MONTH PRODUCT TOTAL SALES
———- ———- ———–
10 13-Q2/P2 111.93
10 2232/QTY 109.92
10 23109-HB 47.8
10 54778-2T 23.96
10 89-WRE-Q 258.95
10 PVC23DRT 99.79
10 SM-18277 26.85
10 WR3/TT3 335.85
10 1015.05
13-Q2/P2 254.84
1546-QQ2 623.9
MONTH PRODUCT TOTAL SALES
———- ———- ———–
2232/QTY 219.84
2238/QPD 77.9
23109-HB 119.5
54778-2T 71.88
89-WRE-Q 517.9
PVC23DRT 199.58
SM-18277 53.7
WR3/TT3 671.7
2810.74
31 rows selected.
Here is the ORACLE PL/SQL query which generated these results. Please study the example carefully.
SQL> SELECT T.TIME_MONTH AS “MONTH”, P.PROD_COD
2 SUM(S.SALE_UNITS*S.SALE_PRICE) AS “TOTAL S
3 FROM SALES S, PRODUCT P, TIME T
4 WHERE S.TIME_ID = T.TIME_ID
5 AND S.PROD_CODE = P.PROD_CODE
6 GROUP BY CUBE (T.TIME_MONTH, P.PROD_CODE)
7 ORDER BY T.TIME_MONTH, P.PROD_CODE;
NOTE: This query will produce the same results using NATURAL JOIN.
SELECT TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”, SUM(SALE_UNITS*SALE_PRICE) AS “TOTAL SALES”
FROM TIME NATURAL JOIN SALES NATURAL JOIN PRODUCT
GROUP BY CUBE (TIME_MONTH, PROD_CODE)
ORDER BY TIME_MONTH, PROD_CODE;
Notice that this report uses the SALES, PRODUCT and TIME tables. It is also possible to write the query using NATURAL JOIN but some developers may feel more comfortable using a traditional JOIN method that will work just as well. Notice that the grand total amount of 2810.74 is the same total as in step 2.
CHECKPOINT QUESTION: Research Data Warehouse Solutions that may be integrated into MySQL enterprise architecture (e.g., PENTAHO). Explain how these solutions may be employed to overcome the current limitations in MySQL support for OLAP functionality. Pay special attention to the reporting and analytic capabilities of these solutions. Based on your analysis and research, do you believe that analytic functions such as GROUP BY CUBE are best implemented in the database (MySQL), the Data Warehouse and Reporting Engine (e.g., PENTAHO), or both. Fully explain your analysis and conclusions.
STEP 4: Materialized Views and a Refresh Procedure
Materialized views (MVs), sometimes referred to as snapshots are a very important aspect of dealing with data when doing data mining or working with a data warehouse. Unlike regular views, a materialized view does not always automatically react to changes made in the base tables of the view. In database systems that directly implement MVs, a Materialized View Log must be created on each base table that will be used in the view. As discussed and explored briefly at the conclusion of Lab 5, MySQL does not directly implement materialized views, but these may be effectively emulated using tables constructed with the same attributes as the view, provided that steps are taken to ensure the automatic update or refresh of the table. For instructional purposes, we will accomplish this by the simple expedient of creating a stored procedure which will drop, recreate, and rebuild the table serving as the view. In our exploration of the concept of the materialized view, we are going to create we are going to use the TIME and the SALES tables.
Because we will emulate an MV in our solution, we will craft a stored procedure to update the MV.
Create and install a stored procedure, named REFRESH_MV_SALESBYMONTH, using the following SQL SELECT statement to guide you. This SELECT statement demonstrates the data to be selected to populate the table. Your stored procedure must: (1) drop the table if it exists; and (2) SELECT INTO the table MV_SALESBYMONTH to create and populate it. Note that, with this logic, no separate logic is needed to first create the view—simply run the stored procedure. The details of the implementation are left to you.
SELECT TIME_YEAR AS “YEAR”, TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”,
SUM(SALE_UNITS) AS “UNITS SOLD”, SUM(SALE_UNITS*SALE_PRICE) AS “SALES TOTAL”
FROM TIME T, SALES S
WHERE S.TIME_ID = T.TIME_ID
GROUP BY TIME_YEAR, TIME_MONTH, PROD_CODE;
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 5: Using the Materialized View
First, CALL REFRESH_MV_SALESBYMONTH. This will make sure that the view is created, and currently up-to-date.
Run the SQL statement: SELECT * FROM MV_SALESBYMONTH. The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.
YEAR MONTH PRODUCT CO UNITS SOLD SALES TOTAL
Now we are going to add some data and update the view. Remember, we must manually run the stored procedure to update the view, as it will not (yet!) automatically itself.
To begin with, insert the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
CALL the stored procedure to refresh the view.
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
Delete the row you just added to the SALES table, and call the stored procedure to refresh the view, proving that things are up-to-date.
Create a Trigger on the SALES table so that any insert automatically fires off the stored procedure to update the view.
Test your trigger, by again inserting the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
CHECKPOINT QUESTION: For instructional purposes and simplicity, we have indulged in the expedient of dropping and recreating the entire MV_SALESBYMONTH table, which now occurs each time a new record is inserted on the SALES table. Would this be unacceptable in practice? Explain why or why not. What other events, and what other tables would require wire-up of triggers in order to ensure that the view is kept up-to-date at all times?
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 3
Laboratory Title: SWL Analytical Extensions & Materialized Views
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?)
0 notes
Text
DBM 449 Laboratory Procedures iLab 6 Answers
Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-6-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
I. OBJECTIVES
Understand and become familiar with the SQL Analytical Extensions.
Learn to create, use, and maintain materialized views, and their functional equivalents.
Effectively apply Advanced Aggregate SQL Operations, such as GROUP BY ROLLUP to solve business intelligence questions and analytical processing problems.
II. PARTS LIST
EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
Scenario and Summary
For the lab this week, we are going to look at how the ROLLUP and CUBE extensions available in SQL can be used to create query result sets that have more than one dimension to them. Both of these extensions are used in conjunction with the GROUP BY clause and allow for a much broader look at the data.
To record your work for this lab use the lab report found at the end of this document. As in your previous labs, you will need to copy/paste your SQL statements and results into this document. Upon completion and prior to the due date, submit this document to the appropriate Dropbox.
iLAB STEPS
STEP 1: Setting Up
For this lab you will be using a different user and set of tables than you have used so far for other labs. To set up your instance you will need to do the following.
The first thing you will do for this lab is to run the following SQL Script. Begin by creating the DBM449Lab6 Schema, and creating any user accounts and privileges you wish to use (at least one).
Run the following script to create and populate a set of tables that will be used for this lab. Instructions for this are outlined in Step 1.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;
USE `DBM449Lab6` ;
— —————————————————–
— Table `DBM449Lab6`.`DISTRICT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`DISTRICT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`DISTRICT` (
`DIST_ID` INT(11) NOT NULL,
`DIST_NAME` VARCHAR(10) NULL DEFAULT NULL,
PRIMARY KEY (`DIST_ID`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`CUSTOMER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`CUSTOMER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`CUSTOMER` (
`CUST_CODE` DECIMAL(10,0) NOT NULL,
`CUST_LNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_FNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_INITIAL` CHAR(1) NULL DEFAULT NULL,
`CUST_STATE` CHAR(2) NULL DEFAULT NULL,
`DIST_ID` INT(11) NOT NULL,
PRIMARY KEY (`CUST_CODE`),
CONSTRAINT `fk_CUSTOMER_DISTRICT1`
FOREIGN KEY (`DIST_ID`)
REFERENCES `DBM449Lab6`.`DISTRICT` (`DIST_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_CUSTOMER_DISTRICT1_idx` ON `DBM449Lab6`.`CUSTOMER` (`DIST_ID` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SUPPLIER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SUPPLIER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SUPPLIER` (
`SUP_CODE` INT(11) NOT NULL,
`SUP_NAME` VARCHAR(35) NULL DEFAULT NULL,
`SUP_AREACODE` CHAR(3) NULL DEFAULT NULL,
`SUP_STATE` CHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (`SUP_CODE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
— —————————————————–
— Table `DBM449Lab6`.`PRODUCT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`PRODUCT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`PRODUCT` (
`PROD_CODE` VARCHAR(10) NOT NULL,
`PROD_DESCRIPT` VARCHAR(35) NULL DEFAULT NULL,
`PROD_CATEGORY` VARCHAR(5) NULL DEFAULT NULL,
`SUP_CODE` INT(11) NOT NULL,
PRIMARY KEY (`PROD_CODE`),
CONSTRAINT `fk_PRODUCT_SUPPLIER`
FOREIGN KEY (`SUP_CODE`)
REFERENCES `DBM449Lab6`.`SUPPLIER` (`SUP_CODE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_PRODUCT_SUPPLIER_idx` ON `DBM449Lab6`.`PRODUCT` (`SUP_CODE` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SALES`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SALES` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SALES` (
`TIME_ID` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`CUST_CODE` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`PROD_CODE` VARCHAR(10) NOT NULL DEFAULT ”,
`SALE_UNITS` DECIMAL(10,0) NULL DEFAULT NULL,
`SALE_PRICE` DECIMAL(10,2) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`, `CUST_CODE`, `PROD_CODE`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`TIME`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`TIME` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`TIME` (
`TIME_ID` INT(11) NOT NULL,
`TIME_YEAR` INT(11) NULL DEFAULT NULL,
`TIME_MONTH` INT(11) NULL DEFAULT NULL,
`TIME_DAY` INT(11) NULL DEFAULT NULL,
`TIME_QTR` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
INSERT INTO SUPPLIER VALUES(31225,’Bryson, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31226,’SuperLoo, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(31231,’DE Supply’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31344,’Gomez Bros.’ ,’615′,’KY’);
INSERT INTO SUPPLIER VALUES(32567,’Dome Supply’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(33119,’Randsets Ltd.’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(44004,’Brackman Bros.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(44288,’ORDVA, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55443,’BK, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(55501,’Damal Supplies’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55595,’Rubicon Systems’ ,’904′,’FL’);
INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’CAT1′,55595);
INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘2232/QTY’,’BD jigsaw, 12-in. blade’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘2232/QWE’,’BD jigsaw, 8-in. blade’ ,’CAT3′,44288);
INSERT INTO PRODUCT VALUES(‘2238/QPD’,’BD cordless drill, 1/2-in.’ ,’CAT3′,55595);
INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’CAT3′,31225);
INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’CAT4’,31225);
INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’CAT2’,31231);
INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’CAT3′,55595);
INSERT INTO DISTRICT VALUES(1,’NE’);
INSERT INTO DISTRICT VALUES(2,’NW’);
INSERT INTO DISTRICT VALUES(3,’SE’);
INSERT INTO DISTRICT VALUES(4,’SW’);
INSERT INTO CUSTOMER VALUES(110010,’Ramas’ ,’Alfred’,’A’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110011,’Dunne’ ,’Leona’ ,’K’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110012,’Smith’ ,’Kathy’ ,’W’ ,’NY’,1);
INSERT INTO CUSTOMER VALUES(110013,’Olowski’ ,’Paul’ ,’F’ ,’NJ’,1);
INSERT INTO CUSTOMER VALUES(110014,’Orlando’ ,’Myron’ ,NULL,’CO’,2);
INSERT INTO CUSTOMER VALUES(110015,’O”Brian’,’Amy’ ,’B’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110016,’Brown’ ,’James’ ,’G’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110017,’Williams’,’George’,NULL,’CA’,4);
INSERT INTO CUSTOMER VALUES(110018,’Farriss’ ,’Anne’ ,’G’ ,’CA’,4);
INSERT INTO CUSTOMER VALUES(110019,’Smith’ ,’Olette’,’K’ ,’CO’,2);
INSERT INTO TIME VALUES(201,2009,09,29,3);
INSERT INTO TIME VALUES(202,2009,09,30,3);
INSERT INTO TIME VALUES(203,2009,09,31,3);
INSERT INTO TIME VALUES(206,2009,10,03,4);
INSERT INTO TIME VALUES(207,2009,10,04,4);
INSERT INTO SALES VALUES(201,110014,’13-Q2/P2′,1,14.99);
INSERT INTO SALES VALUES(201,110014,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(201,110015,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(201,110015,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(202,110016,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(202,110016,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(202,110017,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(202,110017,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(202,110018,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(203,110012,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(203,110014,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(203,110015,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110015,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(203,110016,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(203,110016,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(203,110016,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(203,110017,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(203,110017,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110017,’13-Q2/P2′,1,15.99);
INSERT INTO SALES VALUES(203,110018,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110018,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(203,110018,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(203,110019,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(206,110010,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(206,110010,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(206,110010,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(206,110010,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(206,110011,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(206,110011,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(206,110012,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(206,110012,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(207,110013,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(207,110013,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(207,110013,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(207,110014,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(207,110015,’23109-HB’,1,11.95);
Once the script has finished running, then issue a SHOW TABLES; sql statement. Make sure that you see the following tables listed.
STEP 2: Using the ROLLUP Extension
In this section of the lab you are going to create a sales report that will show a supplier code, product code and the total sales for each product based on unit price times a quantity. More importantly the column that shows the total sales will also show a grand total for the supplier as well as a grand total over all (this will be the last row of data shown). To do this you will use the ROLLUP extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.
SUPPLIER CODE
PRODUCT
TOTAL SALES
31225
23109-HB
119.50
31225
PVC23DRT
199.58
31225
SM-18277
53.70
31225
372.78
31344
13-Q2/P2
254.84
31344
54778-2T
71.88
31344
326.72
33119
1546-QQ2
623.90
33119
623.90
44288
2232/QTY
219.84
44288
89-WRE-Q
517.90
44288
737.74
55595
2238/QPD
77.90
55595
WR3/TT3
671.70
55595
749.60
2810.74
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 3: Using the CUBE Extension
In this section of the lab you are going to examine the creation of a sales report that will show a month code, product code and the total sales for each product based on unit price times a quantity. At the time of this writing, MySQL does not implement the CUBE clause, so we will study an example constructed using the ORACLE DBMS. In this report, the column that shows the total sales will also show a subtotal for each month (in this case representing a quarter). Following the monthly totals for each product and the subtotal by month then the report will list a total for each product sold during the period with a grand total for all sales during the period (this will be the last row of data shown). To do this, the CUBE extension is used as part of the GROUP BY clause in the query. Aliases are used for the column names so that the output columns in the result set look like the following.
MONTH PRODUCT TOTAL SALES
———- ———- ———–
9 13-Q2/P2 142.91
9 1546-QQ2 623.9
9 2232/QTY 109.92
9 2238/QPD 77.9
9 23109-HB 71.7
9 54778-2T 47.92
9 89-WRE-Q 258.95
9 PVC23DRT 99.79
9 SM-18277 26.85
9 WR3/TT3 335.85
9 1795.69
MONTH PRODUCT TOTAL SALES
———- ———- ———–
10 13-Q2/P2 111.93
10 2232/QTY 109.92
10 23109-HB 47.8
10 54778-2T 23.96
10 89-WRE-Q 258.95
10 PVC23DRT 99.79
10 SM-18277 26.85
10 WR3/TT3 335.85
10 1015.05
13-Q2/P2 254.84
1546-QQ2 623.9
MONTH PRODUCT TOTAL SALES
———- ———- ———–
2232/QTY 219.84
2238/QPD 77.9
23109-HB 119.5
54778-2T 71.88
89-WRE-Q 517.9
PVC23DRT 199.58
SM-18277 53.7
WR3/TT3 671.7
2810.74
31 rows selected.
Here is the ORACLE PL/SQL query which generated these results. Please study the example carefully.
SQL> SELECT T.TIME_MONTH AS “MONTH”, P.PROD_COD
2 SUM(S.SALE_UNITS*S.SALE_PRICE) AS “TOTAL S
3 FROM SALES S, PRODUCT P, TIME T
4 WHERE S.TIME_ID = T.TIME_ID
5 AND S.PROD_CODE = P.PROD_CODE
6 GROUP BY CUBE (T.TIME_MONTH, P.PROD_CODE)
7 ORDER BY T.TIME_MONTH, P.PROD_CODE;
NOTE: This query will produce the same results using NATURAL JOIN.
SELECT TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”, SUM(SALE_UNITS*SALE_PRICE) AS “TOTAL SALES”
FROM TIME NATURAL JOIN SALES NATURAL JOIN PRODUCT
GROUP BY CUBE (TIME_MONTH, PROD_CODE)
ORDER BY TIME_MONTH, PROD_CODE;
Notice that this report uses the SALES, PRODUCT and TIME tables. It is also possible to write the query using NATURAL JOIN but some developers may feel more comfortable using a traditional JOIN method that will work just as well. Notice that the grand total amount of 2810.74 is the same total as in step 2.
CHECKPOINT QUESTION: Research Data Warehouse Solutions that may be integrated into MySQL enterprise architecture (e.g., PENTAHO). Explain how these solutions may be employed to overcome the current limitations in MySQL support for OLAP functionality. Pay special attention to the reporting and analytic capabilities of these solutions. Based on your analysis and research, do you believe that analytic functions such as GROUP BY CUBE are best implemented in the database (MySQL), the Data Warehouse and Reporting Engine (e.g., PENTAHO), or both. Fully explain your analysis and conclusions.
STEP 4: Materialized Views and a Refresh Procedure
Materialized views (MVs), sometimes referred to as snapshots are a very important aspect of dealing with data when doing data mining or working with a data warehouse. Unlike regular views, a materialized view does not always automatically react to changes made in the base tables of the view. In database systems that directly implement MVs, a Materialized View Log must be created on each base table that will be used in the view. As discussed and explored briefly at the conclusion of Lab 5, MySQL does not directly implement materialized views, but these may be effectively emulated using tables constructed with the same attributes as the view, provided that steps are taken to ensure the automatic update or refresh of the table. For instructional purposes, we will accomplish this by the simple expedient of creating a stored procedure which will drop, recreate, and rebuild the table serving as the view. In our exploration of the concept of the materialized view, we are going to create we are going to use the TIME and the SALES tables.
Because we will emulate an MV in our solution, we will craft a stored procedure to update the MV.
Create and install a stored procedure, named REFRESH_MV_SALESBYMONTH, using the following SQL SELECT statement to guide you. This SELECT statement demonstrates the data to be selected to populate the table. Your stored procedure must: (1) drop the table if it exists; and (2) SELECT INTO the table MV_SALESBYMONTH to create and populate it. Note that, with this logic, no separate logic is needed to first create the view—simply run the stored procedure. The details of the implementation are left to you.
SELECT TIME_YEAR AS “YEAR”, TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”,
SUM(SALE_UNITS) AS “UNITS SOLD”, SUM(SALE_UNITS*SALE_PRICE) AS “SALES TOTAL”
FROM TIME T, SALES S
WHERE S.TIME_ID = T.TIME_ID
GROUP BY TIME_YEAR, TIME_MONTH, PROD_CODE;
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 5: Using the Materialized View
First, CALL REFRESH_MV_SALESBYMONTH. This will make sure that the view is created, and currently up-to-date.
Run the SQL statement: SELECT * FROM MV_SALESBYMONTH. The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.
YEAR MONTH PRODUCT CO UNITS SOLD SALES TOTAL
Now we are going to add some data and update the view. Remember, we must manually run the stored procedure to update the view, as it will not (yet!) automatically itself.
To begin with, insert the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
CALL the stored procedure to refresh the view.
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
Delete the row you just added to the SALES table, and call the stored procedure to refresh the view, proving that things are up-to-date.
Create a Trigger on the SALES table so that any insert automatically fires off the stored procedure to update the view.
Test your trigger, by again inserting the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
CHECKPOINT QUESTION: For instructional purposes and simplicity, we have indulged in the expedient of dropping and recreating the entire MV_SALESBYMONTH table, which now occurs each time a new record is inserted on the SALES table. Would this be unacceptable in practice? Explain why or why not. What other events, and what other tables would require wire-up of triggers in order to ensure that the view is kept up-to-date at all times?
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 3
Laboratory Title: SWL Analytical Extensions & Materialized Views
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?)
0 notes
Text
DBM 449 Laboratory Procedures iLab 6 Answers Follow Below Link to Download Tutorial
https://homeworklance.com/downloads/dbm-449-laboratory-procedures-ilab-6-answers/
For More Information Visit Our Website ( https://homeworklance.com/ )
Email us At: [email protected] or [email protected]
I. OBJECTIVES
Understand and become familiar with the SQL Analytical Extensions.
Learn to create, use, and maintain materialized views, and their functional equivalents.
Effectively apply Advanced Aggregate SQL Operations, such as GROUP BY ROLLUP to solve business intelligence questions and analytical processing problems.
II. PARTS LIST
EDUPE-VT Omnymbus Virtual Machine Environment (https://devry.edupe.net:9090/) and/or:
MySQL (dev.mysql.com/downloads)
III. PROCEDURE
Scenario and Summary
For the lab this week, we are going to look at how the ROLLUP and CUBE extensions available in SQL can be used to create query result sets that have more than one dimension to them. Both of these extensions are used in conjunction with the GROUP BY clause and allow for a much broader look at the data.
To record your work for this lab use the lab report found at the end of this document. As in your previous labs, you will need to copy/paste your SQL statements and results into this document. Upon completion and prior to the due date, submit this document to the appropriate Dropbox.
iLAB STEPS
STEP 1: Setting Up
For this lab you will be using a different user and set of tables than you have used so far for other labs. To set up your instance you will need to do the following.
The first thing you will do for this lab is to run the following SQL Script. Begin by creating the DBM449Lab6 Schema, and creating any user accounts and privileges you wish to use (at least one).
Run the following script to create and populate a set of tables that will be used for this lab. Instructions for this are outlined in Step 1.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;
USE `DBM449Lab6` ;
— —————————————————–
— Table `DBM449Lab6`.`DISTRICT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`DISTRICT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`DISTRICT` (
`DIST_ID` INT(11) NOT NULL,
`DIST_NAME` VARCHAR(10) NULL DEFAULT NULL,
PRIMARY KEY (`DIST_ID`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`CUSTOMER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`CUSTOMER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`CUSTOMER` (
`CUST_CODE` DECIMAL(10,0) NOT NULL,
`CUST_LNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_FNAME` VARCHAR(15) NULL DEFAULT NULL,
`CUST_INITIAL` CHAR(1) NULL DEFAULT NULL,
`CUST_STATE` CHAR(2) NULL DEFAULT NULL,
`DIST_ID` INT(11) NOT NULL,
PRIMARY KEY (`CUST_CODE`),
CONSTRAINT `fk_CUSTOMER_DISTRICT1`
FOREIGN KEY (`DIST_ID`)
REFERENCES `DBM449Lab6`.`DISTRICT` (`DIST_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_CUSTOMER_DISTRICT1_idx` ON `DBM449Lab6`.`CUSTOMER` (`DIST_ID` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SUPPLIER`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SUPPLIER` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SUPPLIER` (
`SUP_CODE` INT(11) NOT NULL,
`SUP_NAME` VARCHAR(35) NULL DEFAULT NULL,
`SUP_AREACODE` CHAR(3) NULL DEFAULT NULL,
`SUP_STATE` CHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (`SUP_CODE`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
— —————————————————–
— Table `DBM449Lab6`.`PRODUCT`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`PRODUCT` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`PRODUCT` (
`PROD_CODE` VARCHAR(10) NOT NULL,
`PROD_DESCRIPT` VARCHAR(35) NULL DEFAULT NULL,
`PROD_CATEGORY` VARCHAR(5) NULL DEFAULT NULL,
`SUP_CODE` INT(11) NOT NULL,
PRIMARY KEY (`PROD_CODE`),
CONSTRAINT `fk_PRODUCT_SUPPLIER`
FOREIGN KEY (`SUP_CODE`)
REFERENCES `DBM449Lab6`.`SUPPLIER` (`SUP_CODE`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE INDEX `fk_PRODUCT_SUPPLIER_idx` ON `DBM449Lab6`.`PRODUCT` (`SUP_CODE` ASC);
— —————————————————–
— Table `DBM449Lab6`.`SALES`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`SALES` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`SALES` (
`TIME_ID` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`CUST_CODE` DECIMAL(10,0) NOT NULL DEFAULT ‘0’,
`PROD_CODE` VARCHAR(10) NOT NULL DEFAULT ”,
`SALE_UNITS` DECIMAL(10,0) NULL DEFAULT NULL,
`SALE_PRICE` DECIMAL(10,2) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`, `CUST_CODE`, `PROD_CODE`))
ENGINE = InnoDB;
— —————————————————–
— Table `DBM449Lab6`.`TIME`
— —————————————————–
DROP TABLE IF EXISTS `DBM449Lab6`.`TIME` ;
CREATE TABLE IF NOT EXISTS `DBM449Lab6`.`TIME` (
`TIME_ID` INT(11) NOT NULL,
`TIME_YEAR` INT(11) NULL DEFAULT NULL,
`TIME_MONTH` INT(11) NULL DEFAULT NULL,
`TIME_DAY` INT(11) NULL DEFAULT NULL,
`TIME_QTR` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`TIME_ID`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
INSERT INTO SUPPLIER VALUES(31225,’Bryson, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31226,’SuperLoo, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(31231,’DE Supply’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(31344,’Gomez Bros.’ ,’615′,’KY’);
INSERT INTO SUPPLIER VALUES(32567,’Dome Supply’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(33119,’Randsets Ltd.’ ,’901′,’GA’);
INSERT INTO SUPPLIER VALUES(44004,’Brackman Bros.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(44288,’ORDVA, Inc.’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55443,’BK, Inc.’ ,’904′,’FL’);
INSERT INTO SUPPLIER VALUES(55501,’Damal Supplies’ ,’615′,’TN’);
INSERT INTO SUPPLIER VALUES(55595,’Rubicon Systems’ ,’904′,’FL’);
INSERT INTO PRODUCT VALUES(’11QER/31′,’Power painter, 15 psi., 3-nozzle’ ,’CAT1′,55595);
INSERT INTO PRODUCT VALUES(’13-Q2/P2′,’7.25-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’14-Q1/L3′,’9.00-in. pwr. saw blade’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(‘1546-QQ2′,’Hrd. cloth, 1/4-in., 2×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘1558-QW1′,’Hrd. cloth, 1/2-in., 3×50′ ,’CAT2’,33119);
INSERT INTO PRODUCT VALUES(‘2232/QTY’,’BD jigsaw, 12-in. blade’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘2232/QWE’,’BD jigsaw, 8-in. blade’ ,’CAT3′,44288);
INSERT INTO PRODUCT VALUES(‘2238/QPD’,’BD cordless drill, 1/2-in.’ ,’CAT3′,55595);
INSERT INTO PRODUCT VALUES(‘23109-HB’,’Claw hammer’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘23114-AA’,’Sledge hammer, 12 lb.’ ,’CAT4′,31225);
INSERT INTO PRODUCT VALUES(‘54778-2T’,’Rat-tail file, 1/8-in. fine’ ,’CAT1′,31344);
INSERT INTO PRODUCT VALUES(’89-WRE-Q’,’Hicut chain saw, 16 in.’ ,’CAT2′,44288);
INSERT INTO PRODUCT VALUES(‘PVC23DRT’,’PVC pipe, 3.5-in., 8-ft’ ,’CAT3′,31225);
INSERT INTO PRODUCT VALUES(‘SM-18277′,’1.25-in. metal screw, 25′ ,’CAT4’,31225);
INSERT INTO PRODUCT VALUES(‘SW-23116′,’2.5-in. wd. screw, 50′ ,’CAT2’,31231);
INSERT INTO PRODUCT VALUES(‘WR3/TT3′ ,’Steel matting, 4”x8”x1/6″, .5″ mesh’,’CAT3′,55595);
INSERT INTO DISTRICT VALUES(1,’NE’);
INSERT INTO DISTRICT VALUES(2,’NW’);
INSERT INTO DISTRICT VALUES(3,’SE’);
INSERT INTO DISTRICT VALUES(4,’SW’);
INSERT INTO CUSTOMER VALUES(110010,’Ramas’ ,’Alfred’,’A’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110011,’Dunne’ ,’Leona’ ,’K’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110012,’Smith’ ,’Kathy’ ,’W’ ,’NY’,1);
INSERT INTO CUSTOMER VALUES(110013,’Olowski’ ,’Paul’ ,’F’ ,’NJ’,1);
INSERT INTO CUSTOMER VALUES(110014,’Orlando’ ,’Myron’ ,NULL,’CO’,2);
INSERT INTO CUSTOMER VALUES(110015,’O”Brian’,’Amy’ ,’B’ ,’TN’,3);
INSERT INTO CUSTOMER VALUES(110016,’Brown’ ,’James’ ,’G’ ,’GA’,3);
INSERT INTO CUSTOMER VALUES(110017,’Williams’,’George’,NULL,’CA’,4);
INSERT INTO CUSTOMER VALUES(110018,’Farriss’ ,’Anne’ ,’G’ ,’CA’,4);
INSERT INTO CUSTOMER VALUES(110019,’Smith’ ,’Olette’,’K’ ,’CO’,2);
INSERT INTO TIME VALUES(201,2009,09,29,3);
INSERT INTO TIME VALUES(202,2009,09,30,3);
INSERT INTO TIME VALUES(203,2009,09,31,3);
INSERT INTO TIME VALUES(206,2009,10,03,4);
INSERT INTO TIME VALUES(207,2009,10,04,4);
INSERT INTO SALES VALUES(201,110014,’13-Q2/P2′,1,14.99);
INSERT INTO SALES VALUES(201,110014,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(201,110015,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(201,110015,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(202,110016,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(202,110016,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(202,110017,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(202,110017,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(202,110018,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(203,110012,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(203,110014,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(203,110015,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110015,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(203,110016,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(203,110016,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(203,110016,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(203,110017,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(203,110017,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110017,’13-Q2/P2′,1,15.99);
INSERT INTO SALES VALUES(203,110018,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(203,110018,’54778-2T’,2,5.99);
INSERT INTO SALES VALUES(203,110018,’2238/QPD’,1,38.95);
INSERT INTO SALES VALUES(203,110019,’1546-QQ2′,1,311.95);
INSERT INTO SALES VALUES(206,110010,’13-Q2/P2′,5,15.99);
INSERT INTO SALES VALUES(206,110010,’54778-2T’,3,5.99);
INSERT INTO SALES VALUES(206,110010,’23109-HB’,2,11.95);
INSERT INTO SALES VALUES(206,110010,’PVC23DRT’,12,5.87);
INSERT INTO SALES VALUES(206,110011,’SM-18277′,3,8.95);
INSERT INTO SALES VALUES(206,110011,’2232/QTY’,1,109.92);
INSERT INTO SALES VALUES(206,110012,’23109-HB’,1,11.95);
INSERT INTO SALES VALUES(206,110012,’89-WRE-Q’,1,258.95);
INSERT INTO SALES VALUES(207,110013,’13-Q2/P2′,2,15.99);
INSERT INTO SALES VALUES(207,110013,’54778-2T’,1,5.99);
INSERT INTO SALES VALUES(207,110013,’PVC23DRT’,5,5.87);
INSERT INTO SALES VALUES(207,110014,’WR3/TT3′,3,111.95);
INSERT INTO SALES VALUES(207,110015,’23109-HB’,1,11.95);
Once the script has finished running, then issue a SHOW TABLES; sql statement. Make sure that you see the following tables listed.
STEP 2: Using the ROLLUP Extension
In this section of the lab you are going to create a sales report that will show a supplier code, product code and the total sales for each product based on unit price times a quantity. More importantly the column that shows the total sales will also show a grand total for the supplier as well as a grand total over all (this will be the last row of data shown). To do this you will use the ROLLUP extension as part of the GROUP BY clause in the query. Use aliases for the column names so that the output columns in the result set look like the following.
SUPPLIER CODE
PRODUCT
TOTAL SALES
31225
23109-HB
119.50
31225
PVC23DRT
199.58
31225
SM-18277
53.70
31225
372.78
31344
13-Q2/P2
254.84
31344
54778-2T
71.88
31344
326.72
33119
1546-QQ2
623.90
33119
623.90
44288
2232/QTY
219.84
44288
89-WRE-Q
517.90
44288
737.74
55595
2238/QPD
77.90
55595
WR3/TT3
671.70
55595
749.60
2810.74
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 3: Using the CUBE Extension
In this section of the lab you are going to examine the creation of a sales report that will show a month code, product code and the total sales for each product based on unit price times a quantity. At the time of this writing, MySQL does not implement the CUBE clause, so we will study an example constructed using the ORACLE DBMS. In this report, the column that shows the total sales will also show a subtotal for each month (in this case representing a quarter). Following the monthly totals for each product and the subtotal by month then the report will list a total for each product sold during the period with a grand total for all sales during the period (this will be the last row of data shown). To do this, the CUBE extension is used as part of the GROUP BY clause in the query. Aliases are used for the column names so that the output columns in the result set look like the following.
MONTH PRODUCT TOTAL SALES
———- ———- ———–
9 13-Q2/P2 142.91
9 1546-QQ2 623.9
9 2232/QTY 109.92
9 2238/QPD 77.9
9 23109-HB 71.7
9 54778-2T 47.92
9 89-WRE-Q 258.95
9 PVC23DRT 99.79
9 SM-18277 26.85
9 WR3/TT3 335.85
9 1795.69
MONTH PRODUCT TOTAL SALES
———- ———- ———–
10 13-Q2/P2 111.93
10 2232/QTY 109.92
10 23109-HB 47.8
10 54778-2T 23.96
10 89-WRE-Q 258.95
10 PVC23DRT 99.79
10 SM-18277 26.85
10 WR3/TT3 335.85
10 1015.05
13-Q2/P2 254.84
1546-QQ2 623.9
MONTH PRODUCT TOTAL SALES
———- ———- ———–
2232/QTY 219.84
2238/QPD 77.9
23109-HB 119.5
54778-2T 71.88
89-WRE-Q 517.9
PVC23DRT 199.58
SM-18277 53.7
WR3/TT3 671.7
2810.74
31 rows selected.
Here is the ORACLE PL/SQL query which generated these results. Please study the example carefully.
SQL> SELECT T.TIME_MONTH AS “MONTH”, P.PROD_COD
2 SUM(S.SALE_UNITS*S.SALE_PRICE) AS “TOTAL S
3 FROM SALES S, PRODUCT P, TIME T
4 WHERE S.TIME_ID = T.TIME_ID
5 AND S.PROD_CODE = P.PROD_CODE
6 GROUP BY CUBE (T.TIME_MONTH, P.PROD_CODE)
7 ORDER BY T.TIME_MONTH, P.PROD_CODE;
NOTE: This query will produce the same results using NATURAL JOIN.
SELECT TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”, SUM(SALE_UNITS*SALE_PRICE) AS “TOTAL SALES”
FROM TIME NATURAL JOIN SALES NATURAL JOIN PRODUCT
GROUP BY CUBE (TIME_MONTH, PROD_CODE)
ORDER BY TIME_MONTH, PROD_CODE;
Notice that this report uses the SALES, PRODUCT and TIME tables. It is also possible to write the query using NATURAL JOIN but some developers may feel more comfortable using a traditional JOIN method that will work just as well. Notice that the grand total amount of 2810.74 is the same total as in step 2.
CHECKPOINT QUESTION: Research Data Warehouse Solutions that may be integrated into MySQL enterprise architecture (e.g., PENTAHO). Explain how these solutions may be employed to overcome the current limitations in MySQL support for OLAP functionality. Pay special attention to the reporting and analytic capabilities of these solutions. Based on your analysis and research, do you believe that analytic functions such as GROUP BY CUBE are best implemented in the database (MySQL), the Data Warehouse and Reporting Engine (e.g., PENTAHO), or both. Fully explain your analysis and conclusions.
STEP 4: Materialized Views and a Refresh Procedure
Materialized views (MVs), sometimes referred to as snapshots are a very important aspect of dealing with data when doing data mining or working with a data warehouse. Unlike regular views, a materialized view does not always automatically react to changes made in the base tables of the view. In database systems that directly implement MVs, a Materialized View Log must be created on each base table that will be used in the view. As discussed and explored briefly at the conclusion of Lab 5, MySQL does not directly implement materialized views, but these may be effectively emulated using tables constructed with the same attributes as the view, provided that steps are taken to ensure the automatic update or refresh of the table. For instructional purposes, we will accomplish this by the simple expedient of creating a stored procedure which will drop, recreate, and rebuild the table serving as the view. In our exploration of the concept of the materialized view, we are going to create we are going to use the TIME and the SALES tables.
Because we will emulate an MV in our solution, we will craft a stored procedure to update the MV.
Create and install a stored procedure, named REFRESH_MV_SALESBYMONTH, using the following SQL SELECT statement to guide you. This SELECT statement demonstrates the data to be selected to populate the table. Your stored procedure must: (1) drop the table if it exists; and (2) SELECT INTO the table MV_SALESBYMONTH to create and populate it. Note that, with this logic, no separate logic is needed to first create the view—simply run the stored procedure. The details of the implementation are left to you.
SELECT TIME_YEAR AS “YEAR”, TIME_MONTH AS “MONTH”, PROD_CODE AS “PRODUCT”,
SUM(SALE_UNITS) AS “UNITS SOLD”, SUM(SALE_UNITS*SALE_PRICE) AS “SALES TOTAL”
FROM TIME T, SALES S
WHERE S.TIME_ID = T.TIME_ID
GROUP BY TIME_YEAR, TIME_MONTH, PROD_CODE;
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
STEP 5: Using the Materialized View
First, CALL REFRESH_MV_SALESBYMONTH. This will make sure that the view is created, and currently up-to-date.
Run the SQL statement: SELECT * FROM MV_SALESBYMONTH. The output columns from your view should look similar to the following (use aliases to format the column headings) and you should have 18 rows in the result set.
YEAR MONTH PRODUCT CO UNITS SOLD SALES TOTAL
Now we are going to add some data and update the view. Remember, we must manually run the stored procedure to update the view, as it will not (yet!) automatically itself.
To begin with, insert the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
CALL the stored procedure to refresh the view.
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
Delete the row you just added to the SALES table, and call the stored procedure to refresh the view, proving that things are up-to-date.
Create a Trigger on the SALES table so that any insert automatically fires off the stored procedure to update the view.
Test your trigger, by again inserting the following data into the SALES table—(207, 110016, ‘SM-18277’,1,8.95).
Query the view once again.
You should now see that the row for units sold in month 10 for SM-18277 has increased from 3 to 4 and total sales amount has gone from 26.85 to 35.80.
CHECKPOINT QUESTION: For instructional purposes and simplicity, we have indulged in the expedient of dropping and recreating the entire MV_SALESBYMONTH table, which now occurs each time a new record is inserted on the SALES table. Would this be unacceptable in practice? Explain why or why not. What other events, and what other tables would require wire-up of triggers in order to ensure that the view is kept up-to-date at all times?
Be sure to copy your SQL code and the result set produced and paste it into the appropriate place in the LAB6_REPORT.
Laboratory Report DeVry University College of Engineering and Information Sciences
Course Number: DBM449
Laboratory Number: 3
Laboratory Title: SWL Analytical Extensions & Materialized Views
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?)
0 notes