Search results for 'accounting'

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.

CS362 Structured Query Language for Data Management Week 2 IP

After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information.

1. Insert the following classes' records:
Code     Name     Description
ACCT306     Accounting 1     This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.
CS362     Structured Query Language for Data Management     This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.
ENG115     English Composition     In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.
FIN322     Investments     This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.

2. Insert the following advisors' records:
Name     Email
Fred Stone     fred@college.edu
Bob Gordon     bob@college.edu
Jack Simpson     jack@college.edu

3. Insert the following students' records:
Name     Birthdate     Gender     StartDate     GPA     IsActive     AdvisorID
Craig Franklin     1970-03-15     Male     2010-05-30     3.10     Yes     3
Harriet Smith     1982-04-15     Female     2010-05-30     3.22     Yes     1
George David     1984-11-05     Male     2010-10-01     0.00     Yes     3
Ben Jefferson     1976-09-25     Male     2009-02-21     1.80     No, the student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year.     3

4. Delete the course named Investments from the system.

5. Change Harriet Smith’s birthdate to April 25, 1982 and her GPA to 3.25.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Provide an introduction explaining the important of these commands in relation to your overall Key Assignment.

COM 330 Chapter 6 Assignment

1. Using the STUDENT table structure shown in Table P6.4, do the following.

Table P6.4 Sample STUDENT Records
Attribute Name    Sample Value    Sample Value    Sample Value    Sample Value    Sample Value
STU_NUM    211343    200128    199876    199876    223456
STU_LNAME    Stephanos    Smith    Jones    Ortiz    McKulski
STU_MAJOR    Accounting    Accounting    Marketing    Marketing    Statistics
DEPT_CODE    ACCT    ACCT    MKTG    MKTG    MATH
DEPT_NAME    Accounting    Accounting    Marketing    Marketing    Mathematics
DEPT_PHONE    4356    4356    4378    4378    3420
COLLEGE_NAME    Business Admin    Business Admin    Business Admin    Business Admin    Arts & Sciences
ADVISOR_LNAME    Grastrand    Grastrand    Gentry    Tillery    Chen
ADVISOR_OFFICE    T201    T201    T228    T356    J331
ADVISOR_BLDG    Torre Building    Torre Building    Torre Building    Torre Building    Jones Building
ADVISOR_PHONE    2115    2115    2123    2159    3209
STU_GPA    3.87    2.78    2.31    3.45    3.58
STU_HOURS    75    45    117    113    87
STU_CLASS    Junior    Sophomore    Senior    Senior    Junior

c. Write the relational schema and draw the dependency diagram to meet the 3NF requirements to the greatest extent possible. If you believe that practical considerations dictate using a 2NF structure, explain why your decision to retain 2NF is appropriate. If necessary, add or modify attributes to create appropriate determinants and to adhere to the naming conventions.

d. Draw the Crow’s Foot ERD.

Load the data from table P6.4 into an Access database. Add 5 additional students (make up your data) following the table structure.
Create a query in Access that list the students who have a Statistics major.
List the students who have a GPA of 3.45 or higher in a report.
Report the students who have the same advisor.

Submit c and d as an attachment, attach your Access database.