LASA 2: Building The Human Resources Database Solution

For this assignment, you will build the database that you designed in M3 Assignment 2 LASA 1 and populate it with employee information. Then, you will execute Structured Query Language (SQL) statements against the database in order to insert, update, modify, and display the information contained in the database.

Scenario:

The president of Altair Software has reviewed and approved the database design that you completed in M3 Assignment 2 LASA 1. You will now build, configure, and populate the database. Additionally, since Altair Software is a fast-growing company, there have been several changes in salary and personnel since you designed the database. You will need to write SQL statements to insert, update, and remove records in the database, as well as a statement to display the records and run them against the database to see the results. The changes are as follows:

1.   Both vice presidents easily surpassed their goals and objectives for the prior year and are being given salary raises. The new salary for the vice president position is $80,000.

2.   To attract talented programmers directly out of college, Altair Software has decided to raise the base salary for both Programmer I and Programmer II by $5,000 to $40,000 and $50,000, respectively.

3.   Programmer Arlene Smith was recruited by a competitor but in order to keep her with Altair Software, the company promoted her to the newly created position of Programmer III, with a salary of $60,000.

4.   Altair Software hired two new employees:

1.    

Employee ID
402
Employee Last Name
Smith
Employee First Name
Frances
Street Address
4545 Oxbow Lane
City
Anytown
State
AZ
ZIP Code
85331
Department
DEV
Manager
John Smith
Position
Programmer I
Salary
$40,000
1.    

1.    

Employee ID
403
Employee Last Name
Smith
Employee First Name
Greg
Street Address
18951 Silverlake Avenue
City
Sometown
State
AZ
ZIP Code
85332
Department
SALES
Manager
Jane Smith
Position
Sales Representative
Salary
$25,000
1.   Sales Representative Eric Smith left Altair Software to return to school to earn a bachelor’s degree.

2.   Programmer I, Bob Smith, moved out of state.

Tasks:

Note: As you complete the steps below, be sure to save your work often.

1.   Create the Database for the Company

1.   Using Microsoft Access 2010, create the tables that you designed in M3 Assignment 2 LASA 1. Be sure to use your third normal formtables. Enter each data field name in each table and save each table as you work.

2.   Open each table in Design View (right-click the table in the navigation pane and click Design View) and set the appropriate field to be the Primary Key.

3.   While in Design View, ensure that each field is configured for the right type of data (text or number, for instance) and set the field size appropriately.

4.   On the Database Tools tab, in the Relationships group, click Relationships. Using your entity-relationship diagram as a guide, create the appropriate relationships between the tables by matching the appropriate data fields from each table.

5.   Open each table (double-click the table in the navigation pane) and manually enter the appropriate company data. When you are done, each table should contain the same data that was entered in your spreadsheet from M3 Assignment 2 LASA 1.

2.   Display the Information in the Database

1.    

1.   Create an SQL query to display all the information in the database by following the steps below:

1.   On the Create tab, in the Queries group, click Query Design.

2.   Select all the tables in your database by holding the Ctrl key and clicking on each of the tables.

3.   Click the Add button and then the Close button. The tables will be added to Design View window and you will see the relationships between the tables displayed.

4.   Refer to M3 Assignment 2 LASA 1 and add each of the data fields in the order in which they are listed in M3 Assignment 2 LASA 1 to the query by double-clicking each field name in the appropriate table. You will see the query being built in the Design View window.

5.   On the Query Tools Design tab, in the Results group, click the Run button. This will execute the query against the database.

6.   Review the report. When you are satisfied, close the results of the query and click Yes when prompted to save the changes. Name the query “Display Employee Information”.

7.   The query will now appear in the navigation pane. Right-click the query in the navigation pane, select Export, and then click PDF or XPS.

Save the PDF as M5_A1_Lastname_Firstname_EmpInfoBefore.PDF.

Note: For components C, D, and E, you will use SQL View to write simple SQL statements to modify the database. To enter SQL View, complete the following steps:

1.    

1.   On the Create tab, in the Queries group, click Query Design.

2.   When prompted to add tables, click the Close button.

3.   On the Query Tools Design tab, in the Results group, click on the down arrow below the word View and select SQL View. This is where you will compose your SQL statements.

4.   To run your SQL statements against the database, click the Runbutton in the Results group.

2.   Insert Records into the Database

1.    

1.   Create an SQL query to create a new position entitled “Programmer III” with a salary of $60,000. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Insert New Position”.

2.   Create an SQL query to create a new employee record for Frances Smith. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Insert New Programmer”.

3.   Create an SQL query to create a new employee record for Greg Smith. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Insert New Sales Representative”.

2.   Update Records in the Database

1.    

1.   Create an SQL query to update Arlene Smith to Programmer III. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Update Employee”.

2.   Create an SQL query to update the salary for the Vice President position to $80,000. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Update Vice President Salary”.

3.   Create an SQL query to update the salary for the Programmer I position to $40,000. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Update Programmer I Salary”.

4.   Create an SQL query to update the salary for the Programmer II position to $50,000. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Update Programmer II Salary”.

2.   Delete Records from the Database

1.    

1.   Create an SQL query to delete Eric Smith from the database. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Delete Sales Representative”.

2.   Create an SQL query to delete Bob Smith from the database. Run the query against the database. You will be prompted to confirm that you want to modify data in your database. Click Yes. Close the query and save it as “Delete Programmer I”.

3.   Create a 1- to 2-page Microsoft Word document that includes answers to the following questions:

1.   When an employee leaves Altair Software, the company deletes the employee record from the human resources database. Is this a good practice? Why or why not? How would you suggest the company handle employee separations?

Save the document as M5_A1_Lastname_Firstname_GoodPractices.doc.

2.   Display the Information in the Database

1.    

1.   Repeat Step B.6.g and name the PDF as M5_A1_Lastname_Firstname_EmpInfoAfter.PDF.

2.   Compile SQL Queries and Database Reports for Submission

1.    

1.   For each of the ten SQL queries you have created, perform the following steps:

1.   Open the query in Design View (right-click the query in the navigation pane and click Design View).

2.   On the Query Tools Design tab, in the Results group, click on the down arrow below the word View and select SQL View.

3.   Copy the entire SQL statement and paste it into a Microsoft Word document.

2.   Create a 2- to 3-page document in Microsoft Word and paste all of the SQL statements you created in Step E.3. Label each statement with the appropriate name, e.g., “Update Employee”.

Save your document as M5_A1_Lastname_Firstname_SQLStatements.doc