Search results for 'cmis 141 project 4'

Individual Assignment BI Presentation Resources: Huffman Trucking in the Virtual Organizations; the entity relationship diagram, tables, and sample database from the Week Two and Week Three individual assignments Prepare a Microsoft PowerPoint Presentation for senior management at Huffman Trucking explaining how integrating the fleet truck maintenance information into the corporate data warehouse will support organizational goals. Explain how business intelligence may improve efficiencies and fleet performance. Provide a detailed example of a data-mining technique or application and how it will provide information useful to management. · Describe a data-mining technique from Table 9-4 of Modern Database Management or a data-mining application from Table 9-5 of Modern Database Management. · Identify a specific use of this data-mining tool with the information from the sample database from the Week Four individual assignment. · Identify the attributes that this data-mining tool will access. · Explain how the information gathered using this data-mining tool will support Huffman Trucking’s organizational goals. Support your arguments with at least four peer-reviewed or industry publications. Present your recommendation in a 6- to 8-slide Microsoft® PowerPoint® presentation. Keep your slides uncluttered with at most 5 bullets and 7 words per bullet. Your must include speaker notes in your presentation. Learn More

Individual Data Collection Standards Memo and Form/Report/Queries Write a 700- to 1,050-word memo to Taylor Ambulance explaining data collection standards used in the health care industry(7 pts) Create two forms within the Microsoft® Access® database used in Week Three to capture data(4 pts). Please use the most relevant/important tables in the design. One of the forms should include an incorporated sub form. Create two reports within the Microsoft® Access® database used in Week Three that shows information that management at Taylor Ambulance might want to see as they manage their business. One of the reports should include an incorporated sub report. (4 pts). Submit your memo and Microsoft® Access® database file to your facilitator Learn More

Individual Assignment Database Paper Choose one of the following assignment options to complete: Option 1: Data Governance, Quality, Integration, and Security in your organization Review data management and information security practices where you work or at an organization with which you are familiar. Recommend three specific policy changes to improve data governance, quality, integration, and security. Support each recommendation with evidence from at least two scholarly or trade publications. Option 2: Mountain View Community Hospital Case Study Review the Case sections of Ch. 10 and Ch. 11 in Modern Database Management. Analyze the data governance, quality, integration, and security at Mountain View Community Hospital. Address the strengths and opportunities for improvement in data management and security. Support your arguments with evidence from scholarly or trade publications. For either option chosen, write a 1,050- to 1,400-word paper consistent with APA guidelines. Learn More

Individual Database Environment Paper Write a 750- to 1,050-word paper in which you complete the following: · Choose a database environment from the following: o An appropriate database environment within your workplace (must be approved by your instructor) o An Art Museum that needs to track the artwork, artists, and locations where the art is displayed or stored within the museum o Smith Consulting (Virtual Organization) – needs a database to track their consulting staff, each staff member’s skill sets, and what projects they are working on · Analyze the database environment. · Describe the problems and constraints. · Describe the objectives of the database environment. · Describe the scope and boundaries. · List the data specifications (must include a minimum of three entities with attributes). Include 3 to 5 references. Format your paper consistent with APA guidelines. · Note. The database environment chosen will be used in the Weeks Three and Four Individual Assignments. Learn More

Individual ERD Creation Project The following assignment is based on the database environment chosen and discussed in the Week Two Individual Assignment. Use a Microsoft® Visio® diagram to create a detailed ERD using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty’s feedback. · Use a Microsoft® Access® database to create the preliminary database tables, columns with data types, primary keys, and relationships. Learn More

Individual Normalization of the ERD The following assignment is based on the database environment chosen and created in the Week Three Individual Assignment. Your database project must meet the following assessment requirements: Design and develop a database using professional principles and standards. · Provide a logical and physical design of the database. · Use relational database software application to develop database. · Provide an entity relationship diagram. · Normalize the database. · Generate and provide test data. Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF). Use the Microsoft® Access® database created in Week Three to create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables. Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access®database. Submit the ERD and final database to the appropriate Assignment link. Learn More

GENERAL OVERVIEW
Scenario/Summary
My colleague, Ann Henry, operates a regional training center for a commercial software organization. She created a database to track client progress so she can analyze effectiveness of the certification program. CLIENT, COURSE, and COURSE_ACTIVITY are three of the tables in her database. The CLIENT table contains client name, company, client number, pre-test score, certification program and email address. The COURSE_ACTIVITY table contains client number, course code, grade, and instructor notes. The COURSE table contains the course code, course name, instructor, course date, and location. Although she and her instructors enter much of the data themselves, some of the data are extracted from the corporate database and loaded into her tables.

Loading the initial data was easy. For grade entry at the end of each course, a former employee created a data entry form for the instructors. Updating most client information and generating statistics on client progress is not easy because Ann does not know much SQL. For now, she exports the three tables into three spreadsheets. To look up a grade in the COURSE_ACTIVITY spreadsheet, she first has to look up client number in the CLIENT spreadsheet. While this is doable, it is certainly not practical. For statistics, she sorts the data in the COURSE_ACTIVITY spreadsheet using multiple methods to get the numbers she needs.

Every month, Ann's database tables need to be refreshed to reflect changes in the corporate database. Ann describes this unpleasant task. She manually compares the contents of newly extracted data from corporate to the data in her spreadsheets, copies in the new values, and then replaces the database contents with the new values.

Ann needs our help. Let’s analyze her situation and determine what advanced SQL she could use to make her tasks easier.
     
L A B O V E R V I E W

Scenario/Summary

The purpose of this lab is to explore join operators to determine which, if any, are appropriate for solving Ann's business problems, as described in this week's lecture.

Since Ann prefers to work from Excel spreadsheets, she wants her CLIENT and COURSE_ACTIVITY tables exported into one spreadsheet rather than two, as she is currently using. We need to determine which, if any, of the join operators will provide the data she wants for the single spreadsheet. (Note: we will not perform the export, just determine how to retrieve the necessary data.) Using the spreadsheet, she will be able to determine:

1. Which course(s) a specific client has taken
2. What grade(s) a specific client has earned in a specific course
3. Which clients did not take any courses
4. Which courses were not taken by any client

Here are results from DESCRIBE commands that show structure (columns and their data types) of tables CLIENT and COURSE_ACTIVITY. You may refer to it while constructing your queries.

SQL*Plus: Release 10.2.0.1.0 — Production on Thu Jun 14 22:38:52 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 — 64bit Production
With the Partitioning, OLAP and Data Mining Options

SQL> desc course_activity

SQL>

For this lab you will be creating several documents. First, write your queries in Notepad to create a script file that will contain all of the queries asked for in lab steps 4 through 13. You can (and should) test each query as you write it to make sure that it works and is returning the correct data. Once you have all of your queries written then create a SPOOL session and run your entire script file. Be sure that you execute a SET ECHO ON session command before running the file so that both the query and the output will be captured in the SPOOL file. IMPORTANT: If you are using Windows Vista you will need to create a directory on your C: drive to SPOOL your file into. Vista will not allow you to write a file directly to the C: drive. This will give you two files for the lab. The third file will the be the Lab1 Report document found in Doc Sharing. You will need to put your responses to the questions asked in the various lab steps.

Now let's begin.

L A B S T E P S

STEP 1: Start Oracle SQL*Plus via Citrix

Log into the Citrix iLab environment. Open your Oracle folder, select SQL Plus and log in to your database instance. Use "sys" as User Name, and "oracle" as the Password. Enter the Host String as "DB9999.world as sysdba" where 9999 is the database number you have been assigned.

STEP 2: Initialize tables

Download the pupbld.sql and Lab1_init.sql files associated with the links to your C: drive or to the F: drive in your Citrix environment. You will need to open each of the files and edit the connection string to reflect your instance name. The pupbld.sql file has two connections strings; one at the top of the script and another at the bottom. Be sure to change both of these to reflect your instance name.

Once you have done this then run the pupbld.sql script first (DO NOT copy and paste it) in your SQL*Plus session. The script will create the product_user_profile synonym in the SYSTEM account which will be used each time you log in as a normal user.

Next run the lab1_init.sql script in your session. The script will create a new user (DBM449_USER) that will be used in various labs in this course. You can find the password for this new user by looking at the CREATE USER statement in the script file. Disregard the DROP TABLE error messages. They occur because the script is designed to work regardless of whether you have already created the tables or not. This way, you may run it if you ever decide to resent the contents of your tables to the original values. When you run the script for the first time, the error messages appear as you attempt to drop tables that do not exist.

Once the script has finished you will be logged into the new user and ready to start your lab.

STEP 3: Verify your tables

You want to verify that everything completed successfully. To do this execute a SELECT * FROM TAB statement to make sure all 5 tables were created and then you can execute a SELECT COUNT(*) FROM statement using each of the table names. You should find the following numbers of records for each table.

• CLIENT table - 5 rows
• COURSE table - 5 rows
• COURSE_ACTIVITY table - 6 rows
• CORP_EXTRACT1 table - 3 rows
• CORP_EXTRACT2 table - 0 rows

NOTE: In the following steps when writing your queries be sure to list the tables in the FROM clause in the same order they are listed in the instructions. Reversing the order of the tables in the FROM clause will produce an incorrect results set

STEP 4: Using the FULL OUTER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a FULL OUTER JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the FULL OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 5: Using the RIGHT OUTER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a RIGHT OUTER JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the RIGHT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 6: Using the LEFT OUTER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a LEFT OUTER JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the LEFT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 7: Using the NATURAL JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a NATURAL JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
• Will the NATURAL JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 8: Using the INNER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a INNER JOIN.
Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the INNER JOIN be helpful to Ann? Place your response in the lab report document for this step.

Write a conclusion based on the five steps above, which join - if any - should Ann use to populate the spreadsheet that can answer her questions.

STEP 9: Using the UNION operator 

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 10: Using the UNION ALL operator

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION ALL operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 11: Using the INTERSECT operator

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the INTERSECT operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 12: Using the MINUS operator

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the MINUS operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 13: Using subqueries

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using a subquery with NOT IN operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

Deliverables
    
What is Due

Submit your spooled lab file with the queries and results sets along with the completed Lab 1 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

Learn More

In this lab we will focus on several common performance tuning issues that one might encounter while working with a database.  You will need to refer to both your text book and the lecture material for this week for examples and direction to complete this lab.
To record your work for this lab use the LAB2_Report.doc found in Doc Sharing. As in your first lab you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Drop Box for Week 2.

L A B   S T E P S   
STEP 1: Examine Query Optimization using OEM

Oracle Enterprise Manager (OEM) provides a graphical tool for query optimization.  The tables that you will be using in this lab are the same ones that were created in the first lab in the DBM449_USER schema.

1. Start OEM via Citrix iLab. If you need help or instructions on how to do this you can refer to the How_to_use_OEM_in_Citrix iLab.pdf file associated with this link.
2. Select Database Tools icon from the vertical tool bar and Select SQL Scratchpad icon from the expanded tool bar. If you need help or instructions on how to do this your can refer to theExecuting_and_Analyzing_Queries_in_OEM.pdf file associated with this link.
3. Write a SQL statement to query all data from table COURSE (you will need to connect as the DBM449_USER). Click on Execute. Take a screen shot that shows the results and paste that into the lab document.
4. Click on Explain Plan. Take screen shot of the results and past that into the lab document.
5. Write a comment how this query is executed.
6. Write a SQL statement to query the course_name, client_name and grade from the COURSE, COURSE_ACTIVITY and CLIENT tables and order the results by course name, and within the same course by client name.
7. Click on Explain Plan. Take screen shot of the results and past that into the lab document.
8. Exit out of OEM at this point.
9. Write a comment on how this query is executed.

STEP 2: Examine Query Optimization using SQL*Plus

In this portion of the lab we are going to use SQL*Plus to replicate what we did in Step one using OEM.  At the end of this part of the lab you will be asked to compare the results between the processes.

1. Before you can analyze an SQL statement in SQL*Plus you first need to create a Plan Table that will hold the results of your analysis.  To do this you will need to download the UTLXPLAN.SQL file associated with this link and run this script in an SQL*Plus session while logged in as the DBM449_USER user.  Once the script has completed then execute a DESC command on the PLAN_TABLE.
2. Again you are going to write a SQL statement to query all data from table COURSE.  Remember to make the modifications to the query so that it will utilize the plan table that you just created.
3. Now write the query that will create a results table similar to the one below by using the DBMS_XPLAN procedure.

PLAN_TABLE_OUTPUT
Plan hash value:  1263998123
     
  Id      Operation      Name      Rows      Bytes      Cost (%CPU)      Time
  0      SELECT STATEMENT          5      345      3    (0)      00:00:01
  1      TABLE ACCESSFULL      COURSE      5      345      3    (0)      00:00:01
    
Note
PLAN_TABLE_OUTPUT
- dynamic spamling used for this statement

4. Now execute the second query you used in Step 1 and then show the results in the plan table for that query.  HINT: Before you run your second query you will need to delete the contents of the plan table so that you will get a clean analysis.
5. Write a short paragraph comparing the output from OEM to the output from the EXPLAIN PLAN process you just ran.  Be sure to copy/paste all of the queries and results set from this step into the lab report section for this step.

STEP 3: Dealing With Chained Rows

In this portion of the lab we are going to create a new table and then manipulate some data to generate a series of chained rows within the table.  After you have generated this problem then we are going to go through the process of correcting the problem and tuning the table so that the chained rows are gone.  The process is a little tricky and is going to require you to think through your approach to some of the SQL.  Remember that every table has a hidden column named ROWID that is created implicitly by the system when the table is created.  This column can be queried just like any other column.  You will need this information in step 6 of this part of the lab. 

1. For this part of the lab you will need to create a new user named GEORGE.  You can determine your own password but you want to make sure that the default tablespace is USERS and the temporary tablespace is TEMP.  Grant both the connect and resource rolls to the new user and then log in to create a session for the new user GEORGE.
2. Once logged in to the new user then write the SQL to create a new table using the given column information and storage parameters listed below.  NOTE:  the parameters have been chosen intentionally so please do not change them.

Table name: NEWTAB
Columns: Prod_id       NUMBER
     Prod_desc VARCHAR2(30)
     List_price NUMBER(10,2)
     Date_last_upd DATE

Tablespace:    USERS
     PCTFREE    10
     PCTUSED    90
     Initial and Next extents:    1K
     MinExtents    1
     MaxExtents    121
     PCTINCREASE    0

3. Next, you will need to download both the UTLCHAIN.SQL and LAB2_FILL_NEWTAB.SQL scripts from the links shown.  First run the UTLCHAIN script in your SQL*Plus session and then run the LAB2_FILL_TAB script.  Be sure that you run them in the order just described.
4. Now execute the ANALYZE command on the table NEWTAB to gather any chained rows.  HINT: refer back to the lecture material for this week and your text book. 
5. Write and execute the query that will list the owner_name, table_name and head_rowid columns from the CHAINED_ROWS table.  You will have approximately 200+ rows in your result set so please do not copy/paste all of them into the lab report.  You only need the first 10 or 15 rows as a representation of what was returned.
6. Now you need to go through the steps of getting rid of all the chained rows using these steps.  
• You can create your temporary table to hold the chained rows of the NEWTAB table as a select statement based on the existing table.  HINT: CREATE TABLE NEWTAB_TEMP AS SELECT * FROM NEWTAB....  You want to be sure that you only pull data from the existing table that matches the data in the CHANED_ROW table.  To assure this you will need a WHERE clause to pull only this records with a HEAD_ROWID value in the CHAINED_ROWS table that matches a ROWID value for the NEWTAB table.  
• Now you need to delete the chained rows from the NEWTAB table.  To accomplish this you will need a subquery that pulls the HEADROW_ID value from the CHAINED_ROWS table to match against the ROWID value in the NEWTAB table.  The number of rows deleted should be the same as the number that you retrieved in the query for part 5 of this section.
• Now write an insert statement that will insert all of the rows of data in the temporary table that you created above into the NEWTAB table.  Be sure that you explicitly define the rows that you are pulling data from in the NEWTAB_TEMP table.
• Next, write and execute the statement that will TRUNCATE the chained_rows table.
• Now run the same ANALYZE statement you did in step 4 and then the query you did in part 5 above.  This time you should get a return message stating no rows selected.

Be sure that you copy/paste all of the above SQL code and returned results sets and messages into the appropriate place in the Lab Report for this week.

Deliverables     

What is Due
Submit your completed Lab 2 Report to the Dropbox as stated below.  Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.
 

Learn More

L A B O V E R V I E W    

Scenario/Summary
To the end user working with databases distributed through out a company's network is not different than working with multiple tables within a single database. The fact that the different databases exist in other locations should be totally transparent to the user. For this lab we are going to take on the roll of a database administrator in a company that has three regional offices in the country. You work in the central regional office, but there is also a West Coast Region located in Seattle and an East Coast Region located in Miami. Your roll is to gather report information from the other two regions.

For this lab you are going to work with three different databases. You already have your own database instance. You will also be working with the a database named SEATTLE representing the West Coast Region and a database named MIAMI representing the East Coast Region. Login information for these two additional database instances is as follows:

SEATTLE: Userid - seattle_user
Password - seattle
Host String - seattle

MIAMI: Userid - miami_user
Password - miami
Host String - miami

To record your work for this lab use the LAB3_Report.doc found in Doc Sharing. As in your previous labs you will need to copy/paste your SQL statements and results from SQL*Plus into this document. This will be the main document you submit to the Drop Box for Week 3.

L A B S T E P S    
STEP 1: Setting up Your Environment

1. Be sure you are connected to the DBM449_USER schema that was created in lab 1. 
2. To begin this lab you will need to download the LAB3_DEPTS.SQL script file associated with the link and run the script in your DBM449_USER schema of your database instance. This script contains a single table and that you will be using to help pull data from each of the other two database instances.  Notice that the DEPTNO column in this table is the PRIMARY KEY column and can be used to reference or link to the DEPTNO column in the other two database employee tables.
3. Now you need to create a couple of private database links that will allow you to connect to your other two regional databases. To accomplish this use the connection information listed above in the Lab Overview section. Name your links using your database instance name together with the region name as the name for the link. Separate the two with an underscore (example - DB1000_SEATTLE).
4. After creating both of your database links, query the USER_DB_LINKS view in the data dictionary to retrieve information about your database links.  The output from your query should look similar to what you see below.  You will need to set your linesize to 132 and format the DB_LINK and HOST columns to be only 25 bytes wide to get the same format that you see.

DB_LINK                   USERNAME                       HOST                      CREATED
------------------------- ------------------------------ ------------------------- ---------
DB1000_MIAMI              MIAMI_USER                     miami                     09-DEC-08

STEP 2: Testing your Database Links
Each of your remote databases has an employee data table. The tables are named SEATTLE_EMP and MIAMI_EMP respective to the database they are in. Using the appropriate database link, query each of the two tables to retrieve the employee number, name, job function, and salary. (HINT: you can issue a DESC command on each of the distributed tables to find out the actual column names just like you would for a table in your own instance.

STEP 3: Connecting Data in the Seattle Database
Write a query that will retrieve all employees from the Seattle region who are salespeople working in the marketing department. Show the employee number, name, job function, salary, and department name (HINT: The department name is in the DEPT table) in the result set.

STEP 4: Connecting Data in the Miami Database
Write a query that will retrieve all employees from the Miami region who work in the accounting department. Show the employee number, name, job function, salary, and department name (HINT: The department name is in the DEPT table) in the results set.

STEP 5: Connecting Data in all Three Databases
Now we need to increase our report. Write a query that will retrieve employees from both the Seattle and Miami regions who work in sales. Show the employee number, employee name, job function, salary and location name in the result set (HINT: The location name is in the DEPT table).

STEP 6: Improving Data Retrieval from all Three Databases
Writing queries like the ones above can be fairly cumbersome. It would be much better to be able to pull this type of data as though it was coming from a single table, and in fact this can be done by creating a view.

1. Using the query written above as a guide, write and execute the SQL statement that will create a view that will show all employees in both the Seattle and Miami regions (you can use your own naming convention for the view name). Show all the employee number, name, job, salary, commission, department number and location name for each employee (HINT: The location name is in the DEPT table).
2. Now write a query that will retrieve all the data from the view just created.

Deliverables
Submit your completed Lab 3 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

Learn More