Search results for 'microsoft access 2010'

Build queries through SQL along with the query-building tool with sales databases built in Week Two for Riordan Manufacturing. Create a separate table for query information. Include the query name and query description. Enter data into the fields, naming and describing as many queries as you can, and then write two thirds of the queries using the query-building tool. Write the remaining queries in SQL. Create a new query without the tools and enter the SQL into its view. Test your queries to verify that they return the data you described. 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
For this lab you will begin by using the same set of tables that you used for Lab 1 so be sure that you are connected to Oracle as the DBM449_USER user. The objective of this lab will be to create a series of object-relational tables using the SQL*Plus editor that will allow data to be stored in a more "real-world" format. Data for your new tables can be found in the file Lab4_data.txt associated with this link. You will need to manipulate the data in various ways, but the file will give you access to the raw data to use.
To record your work for this lab use the LAB4_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 4.

L A B S T E P S
STEP 1: Create a table with a column data type
Modify the design of the COURSE table created in iLab 1 to incorporate the use of the column abstract data type.

1. Write and execute the SQL to create a single object type called COURSE_OBJ1 that contains both the attributes course code and course name. Remember that with abstract objects you must use the / after the CREATE statement to execute it.
2. Next, write and execute the SQL to create a table called NEW_COURSE1 that contains COURSE_OBJ1 along with the original attributes from the original COURSE table. Keep in mind what attributes the new object type COURSE_OBJ1 contains. Your table should have a total of 4 individual columns when finished.
3. Using the data from the LAB4_DATA file create and execute the insert statements to load the new table NEW_COURSE1. SUGGESTION: Using the Lab4_data file create a script file of your insert statements and then run the script file. Remember that you will need enclose some of the data in single quotes depending on if it is character, date, or numeric data.
4. Run DESCRIBE command to describe structure of table NEW_COURSE1.
5. SET DESCRIBE DEPTH 2 and run DESCRIBE NEW_COURSE1 again.
6. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query). Use the COLUMN column_name FORMAT A## session command to format columns within the table to keep the result set data from wrapping around. Be sure that you properly display data inside the object column. (HINT: When querying attributes of an abstract data type, you must use a correlation variable for the table.)

STEP 2: Create an object table with a row data type
Create a second COURSE table, this time as an object table using the row abstract data type.

1. Write and execute the SQL to create an object called COURSE_OBJ2 that contains the attributes course code, course name, course date, instructor, and location.
2. Write and execute the SQL to create a table called NEW_COURSE2 with a single column defined using the COURSE_OBJ2 object.
3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_COURSE2.
4. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query).

STEP 3: Create a Varying Array
Modify the design of the CLIENT table created in iLab 1 to incorporate the use of the Varying Array.

1. Write and execute the SQL to create a Varying Array to represent the phone contact information for the client (up to 3 phone numbers). Name the varying array as PHONE_LIST.
2. Write and execute the SQL to create a table called NEW_CLIENT that contains the attributes that the original CLIENT table contained plus the phone list array.
3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table NEW_CLIENT.
4. Execute a SELECT statement to query the data from the CLIENT_NO and CLIENT_NAME columns along with the data in the column containing the phone number Varray (You cannot use a SELECT * type query for this step).

Deliverables
Submit your completed Lab 4 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 your lab for this week you are going to work with three different areas and processes within the Oracle Database that can be used to control data security. Each of these three processes has its own distinctive application to providing levels of security. In each case the individual processes deal with either limiting a users access to the database, limiting access to processes within the database, or keeping track of what the user is doing while in the database.

For the lab you will be using the SCOTT user which is already created in your instance. In Step 4 you will also be asked to shutdown you instance, make some edits to the init.ora file for your instance and then restart the instance. If you are not comfortable with this process which was first introduced to you in DBM438 the refer to the iLab Manual found in week 1 for guidance.

To record your work for this lab use the LAB5_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 Dropbox for Week 5.

LAB STEPS

STEP 1: Define a New Profile

Oracle provides the ability to set expirations, limit the reuse, and define the complexity of passwords. In addition, accounts can be locked if the password is entered incorrectly too many times. In this section of the lab we are going to create a custom profile that will then be applied to the SCOTT user.

1. To begin, log into your instance as the SYS user.
2. Write SQL script that will create a new profile named DBM449_SCOTT_PROFILE that will do the following:
• Limit the number of failed login attempts to 3 in a row.
• Limit the overall connection time to 10 hours (we will give him a little leeway incase he has to work overtime).
• Allow a session to be idle no more than 1 hour.
• Change the password every 60 days.
• Allow the user 3 days to change the password after it expires.
• Not allow a previous password be reused before there have been three password changes.
3. Execute your pfile script and verify that the profile has been created by running a query against the DBA_PROFILES view in the data dictionary. Limit your output to ONLY the DBM449_SCOTT_PROFILE parameters.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 2: Testing the New Profile

Now that we have a new profile for the SCOTT user we need to verify that it works properly. For obvious reasons there are going to be parts of the profile that we cannot test within the confines of this lab due to time constraints, but we can test to verify that the SCOTT user is being controlled by the profile.

1. The first thing we need to do is assign the profile to the SCOTT user. While still logged into your instance as the SYS user write and execute the SQL command that will assign the new SBM449_SCOTT_PROFILE profile to the SCOTT user.
2. Now log into SCOTT (password is TIGER). Remember that you must supply the database instance name when logging in from the SQL> prompt just as you do when using the login window, i.e. CONN SCOTT/TIGER@DB####.WORLD.
3. There are several things that we can test related to the logging in and changing a password so here we go.
• You should now be successfully connect to the SCOTT user. Write the connect command again on this time use an incorrect password. NOTE: you should get a warning message stating that you are no longer connected to Oracle. That is fine, just keep trying to log in.
• Repeat the above process until you get the ORA-28000: the account is locked error which will indicate that the profile is working here.
• At this point we need to get the account unlocked so you will need to login to your instance as the SYS user and unlock the SCOTT account BUT DO NOT LOG BACK INTO THE SCOTT USER YET.
• Now we can test the password reuse parameter. To do this we must EXPIRE the current password. Write and execute the SQL command to expire the password for the SCOTT user.
• Now log back into the SCOTT user. You should receive a message stating that the password has expired (ORA-28001: the password has expired) and then prompting you to change the password.
• Try to reuse the TIGER password. You should receive the following - ORA-28007: the password cannot be reused.
4. Now log into the SCOTT user again and this time change the password to LION to complete this step of the lab.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 3: Using the PRODUCT_USER_PROFILE table

As the owner of a schema a user has certain inherited privileges that would allow the user to pass access to his/her own objects on to other users. Often times this can open up data to scrutiny by individuals who probably do not need to have access to it. These types of decisions should always be made by the DBA in charge of the database. One mechanism the DBA has to keeping users from using these inherited privileges is by excluding those commands using the PRODUCT_USER_PROFILE (PUP) table. In this section of the lab we are going to do this to the SCOTT user by setting up the scenario that will prohibit him from giving the user GEORGE (created in lab 2) access to the EMP table.

1. For this section and remainder of the lab you must have the PRODUCT_USER_PROFILE successfully loaded and accessible in your instance. The creation of this profile was one of the first things done back in Lab 1 when you ran the PUPBLD.SQL script. If you are getting an error message stating "Error accessing PRODUCT_USER_PROFILE" when you log in as the DBM449_USER or the SCOTT user then this profile is not successfully installed. Work with your instructor to figure out why your script from Lab 1 did not work correctly. Until this is resolved you will not be able to complete the remainder of the lab.
2. If you have the PRODUCT_USER_PROFILE successfully working then log in to your database instance as the SYS user.
3. Now we need to limit SCOTT from being able to use the GRANT command.
• Insert the proper values into the PRODUCT_USER_PROFILE table that will keep the SCOTT user from using the GRANT command. Remember that some of the values in your insert statement must be in upper case and some will need to be in mixed case. Once you have done this then query the table to verify the insert (REMEMBER: you cannot query the table as the SYS user, only as the SYSTEM user).
• Now we need to test our above settings and make sure they are working.
• Connect to the SCOTT user (remember that you changed the password to LION).
• Write and execute the statement that would GRANT the user GEORGE the ability to write a select statement and see the data in the EMP table owned by SCOTT. You should receive the following message - SP2-0544: Command "grant" disabled in Product User Profile.
4. This verifies that you have now disabled the ability of the SCOTT user to allow another user to access any of the data in his schema.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 4: Setting up the Database to use Auditing

Being able to audit what, when and where people are doing things in the database can be a very enlightening thing for a DBA. It can also be a very important tool in working with Data Security. Oracle provides the ability to do various types of auditing, but it takes some special setting up of the environment for this to work. In this step we are going to make the necessary adjustments to the current Oracle instance so that we can enable auditing and make some tests. If you need to review the processes to be used here then refer to the iLab Manual in week 1.

1. First you need to make sure that you are logged into your instance as the SYS user.
2. At this point issue a SHUTDOWN IMMEDIATE command to shut down you database instance.
3. Once the instance is shut down you need to go into your Citrix Windows Explorer application, find your database instance set of directory folders, drill down to the pfile directory folder and open your init.ora file found in that folder.
4. Under the section titled "Security and Auditing" you need to add the parameter AUDIT_TRAIL and set the parameter to DB_EXTENDED. This will allow the SQL_TEXT column of the DBA_AUDIT_OBJECT view to be populated. Save and close the file and then go back to your SQL*Plus session.
5. Now using the init.ora file, start your instance back up to an OPEN status. You can do this by issuing a STARTUP PFILE= statement and pointing to your init.ora file.
6. Once you have completed this process you are ready to begin setting up the database to audit some activity.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

STEP 5: Creating an Audit Trail

Oracle permits audit trails to be generated for session login attempts, access to objects, and activity performed on objects. Again using the SCOTT user we are going to set up several scenarios for auditing what SCOTT does while in a session. NOTE: if you need to work through this process several times you can delete the values in the AUD$ base table by issuing the TRUNCATE TABLE AUD$ command while logged in as the SYS user.

1. Make sure that you are connected as user SYS.
2. Display value of the parameter AUDIT_TRAIL. For the VALUE column you should have a value of DB_EXTENDED.
3. Now we can set up auditing to track what goes on in the database.
• Write SQL statements to audit successful and unsuccessful login attempts by SCOTT.
• Write SQL statement to audit any successful INSERT, UPDATE or DELETE performed on table DEPT in scott's schema.
4. Now we need to test the audits to verify that they work.
• Log into the SCOTT user (remember that the password is LION) and perform the following:
• write and execute an UPDATE statement that will change the value in the LOC column of the DEPT table to MIAMI where the DEPTNO value is 10.  Be sure to issue a COMMIT.
• Write and execute the INSERT statement that will in insert the following values into DEPT - (50, 'LEGAL', 'HOUSTON').  Be sure to issue a COMMIT.
• Write and execute the DELETE statement that will delete the row from the DEPT table that was just inserted in the step above.  Again, be sure to issue a COMMIT.
• Try to reconnect to the SCOTT user with an invalid password.
• Now connect back to the SYS user.

Now we need to see if our auditing worked.

1. While logged into your instance as the SYS user, query the DBA_AUDIT_OBJECT view of the data dictionary for the user name of the account (Not the OS), the object owner, the object name, the action name and the SQL command (text) from the DBA_AUDIT_OBJECT view in the Data Dictionary.
2. Did you notice that the entries for successful logon and unsuccessful logon attempts were not there. Now query the user name, action name and return code values in the DBA_AUDIT_SESSION view. You should find that information here.

Be sure to copy/paste your script and results sets output to the appropriate section in the Lab5_report document.

Deliverables

Submit your completed Lab 5 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

MIS582 iLab 2: Data Modeling Using Visio

iLAB OVERVIEW
Scenario and Summary

In this assignment, you will learn to create a physical database model in Visio from business requirements. To complete this assignment, you will need to be able to run Visio 2010, either through Citrix or installed on your workstation or laptop.

Deliverables
Name your Visio file using Lab2_, your first initial, and your last name (e.g., Lab2_JSmith.vsd). Create and save your database model in your Visio file.

iLAB STEPS

STEP 1:
Read the following business requirements closely to determine the entities and relationships needed to fulfill the requirements. The nouns in the paragraph will tell you the entities that will be needed. The verbs in the paragraph will help you determine the relationships between the entities.
Muscles Health Club Database Requirements:
The Muscles Health Club needs a database to keep track of its members, their personal trainers, and the fitness classes they are taking. Employees can act as personal trainers for members. However, only certified employees can act as personal trainers. A member can work with only one personal trainer at a time. Members can take multiple fitness classes. Fitness classes are taught by employees who can teach multiple classes. Fitness classes are taught in one of the classrooms at one of Muscles Health Club’s several locations. Each fitness classroom is designed for a different type of class (e.g., spinning, aerobics, water aerobics, weight training, etc.). It is necessary to track what fitness classes are being held in each of the different Muscles Health Club locations.

STEP 2:
• Run Visio 2010 either via Citrix or on your workstation.
• Click on the Software and Database Template group in the main window.
• Double-click on the Database Model Diagram Template to open a new file.
• Save the file with a name containing Lab2_, your first initial, and your last name as the file name (e.g., Lab2_JSmith.accdb). You will need to click the computer icon in the Save As window to see the different drives. Be sure to save the file to a local drive so it will be on your workstation.

STEP 3:
Add an entity for each entity you identified in the requirements.
• Drag the entity icon onto the drawing area in Visio.
• In the Database Properties window, add a physical name to identify it.

STEP 4:
For each entity, create a list of attributes you think would be useful to describe the entity.
• Select an entity in the drawing area of Visio.
• In the Database Properties window, select the Columns category.
• Use the table to add your attributes to the selected entities.
• Select one of the attributes to be the primary key (PK).

STEP 5:
Set the diagram to use crow’s feet notation.
• On the Database tab, in the Manage group, click Display Options.
• In the Database Document Option dialog, select the Relationship tab.
• Select the Crow’s Feet check box, and then click OK.

STEP 6:
Draw relationships between your entities.
• Drag the relationship icon onto a blank part of the drawing area.
• Connect the two ends to each of the two entities in the relationship. The parent entity must have a PK defined. The entity will be outlined in bold red lines when it connects to one end of the relationship.

STEP 7:
Set the cardinality of your relationships.
• Select a relationship line in the drawing area that is connecting two entities.
• In the Database Properties window, select the miscellaneous category.
• Select the cardinality for the selected relationship.

STEP 8:
When you are done, save the file on your local hard drive and upload it to the Course Project Drop box. Your file should have the following filename format: Lab2_FirstInitialLastName.vsd.
Submit your assignment to the Drop box located on the silver tab at the top of this page.

Learn More

MIS582 iLab 3: Database Construction Using Access

iLAB OVERVIEW
Scenario and Summary
In this assignment, you will learn to create an Access database from a given ERD. To complete this assignment, you will need to be able to run Access 2010, either through Citrix or installed on your workstation or laptop.

Deliverables
Name your Access database file using Lab3_, your first initial, and your last name (e.g., Lab3_JSmith.accdb). Create and save your Access database file. When you are done, submit your database to the Course Project Dropbox.

iLAB STEPS

STEP 1:
Review the ERD below to understand the entities, attributes, primary keys, and relationships that you will create in your Access database.

STEP 2:
• Run Access 2010, either via Citrix or from Visio 2010 installed on your workstation.
• Select the blank database icon in the main window.
• Save the file with a name containing Lab3_, your first initial, and your last name (e.g., Lab3_JSmith.accdb). In Citrix, you will need to click the computer icon in the Save As window to see the different drives. Be sure to save the file to a local drive so it will be on your workstation.
See the tutorials above for instructions on how to perform the following steps in Access 2010.

STEP 3:
Add tables to the Access database.
• Add a table for each entity listed in the provided ERD diagram.
• Add a column for each attribute listed in the provided ERD diagram.
• Select a primary key for each table as indicated in the provided ERD diagram.

STEP 4:
For every column in every table, update the data type as needed to enforce the domain constraints of the data.
• Dates should have a date data type.
• Surrogate keys should be autonumbered.
• Numeric data should have a numeric data type.
• Character data should have a character data type.

STEP 5:
Draw relationships between your entities.
• Selection Relationships under Database Tools. Move all your tables into the Relationship window by dragging them in or by using the Show Tables pop-up window.
• Second item
o Add the relationship between the tables in your database.
o Enable referential integrity on the relationship.
o Enable cascade updates on the relationship.

STEP 6:
Add at least two rows of data to each table in your database. Use any values you like for each of the columns. Remember that you must add data to parent tables before adding data to child tables, because referential integrity is enabled.

STEP 7:
Set the following column constraints in your database.
• Student first and last name cannot be a null value.
• Course credit hours must be between one and four.
• Course name must be unique and cannot be a null value.
• Instructor first and last name cannot be a null value.
• Grade must be one of these values: A, B, C, D, F, I, W, or E. W signifies withdrawn and E signifies enrolled.

STEP 8:
When you are done, save the file on your local hard drive and upload it to the Course Project Dropbox. Your file should have the following filename format: Lab3_FirstInitialLastName.accdb.
For instructions on how to copy files between the Citrix server and your local machine, watch the iLab tutorial, Copying Files from Citrix, located in the iLab menu tab under Course Home.
Note!
Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Learn More

MIS582 iLab 4 Forms and Reports

i L A B O V E R V I E W
Scenario and Summary
In this assignment, you will learn to create and save forms and reports in a provided Access database. To complete this assignment, you will need to be able to run Access 2010.

Deliverables
Name your Access database file using Lab4_, your first initial, and your last name (e.g., Lab4_JSmith.accdb). Create and save your Access database file. When you are done, submit your database to the Course Project Dropbox.

i L A B S T E P S
STEP 1
• Download the Lab4_FormsReports.accdb database from Doc Sharing.
• Rename the Lab4_FormsReports.accdb database with a name containing Lab4_, your first initial, and your last name as the file name (e.g., Lab4_JSmith.accdb).

STEP 2:
• Run Access 2010, either via Citrix or from Visio 2010 installed on your workstation.
• Open the database you renamed in Step 1.

STEP 3:
Reports
Create a report for each of the following requirements. Reports may be created using the wizard or an SQL query. Reports should follow the formatting guidelines given in the next step. Reports should be named as indicated below (e.g., Report1, Report2, etc.).
1. Report1: Show Instructor information (Instructor_ID, First_Name, Last_Name) grouped by Approved Course_No.
2. Report2: Show student information (Student_ID, First_Name, Last_Name, Status, Zip) grouped by Major.
3. Report3: Show student information (Student_ID, First_Name, Last_Name, Major) grouped by Zip code. Zip codes should be in increasing order.
4. Report4: Show student information (First_Name, Last_Name) grouped by Course Number and Course Name. Course Number should be in in increasing order.
5. Report5: Show student information (Student_ID, First_Name, Last_Name, Address, Zip) grouped by Status. Students should be in alphabetical order by Last_Name and;then First_Name.

STEP 4:
Report Format
• Reports should display two or more child rows for each parent row. For example, Report1 should display each Course_No with instructors who are approved to teach the course grouped under it. In this case, the parent row would be the Approved Course_No with Instructor listed as child rows underneath each parent row
• There should be at least two child rows of data for each parent row. Add data to the tables in order to have reports show at least two child rows per parent.

STEP 5:
Form1: Create a form based on an SQL query.
• Create and run an SQL query that displays Student Information (Student_ID, First_Name, Last_Name, Address, Zip).
• Save the query in the database named Form1Query.
• Create a form based on Form1Query. While Form1Query is selected, go to the Create menu and select Form to build the basic form.
• Select the form and switch between the views (Forms, Layout, and Design) to change the properties or the form’s appearance as desired.
• Save the form in the database named Form1Query.

STEP 6:
Form2: Create a form using the Form Wizard.
• Invoke the Form Wizard as shown in the tutorials above.
• Using the Form Wizard, create a form showing instructor information (Instructor_ID, First_Name, Last_Name, Office_No).
• Switch between the views (Forms, Layout, and Design) to change the properties or the form’s appearance as desired.
• Save the form in the database named Form2Wizard.

STEP 7:
Form3: Create a form using a master detail relationship. A master detail relationship is simply a 1:N relationship between two tables. Use subforms to format the form in a split data entry form as shown in Figure 1-9. You may use SQL queries or the wizard or both to create your form and subform.
• Create a master form showing student information (Student_ID, First_Name, Last_Name, Address, Zip, Major, Status).
• Create a detail Subform showing Student_ID, Class_ID, and Student_Grade.
• For each student displayed, the student’s classes and grades should be displayed in the detail form.
• Switch between the views (Forms, Layout, and Design) to change the properties or the form’s appearance.
• Save the form in the database named Form3MaterDetail.

STEP 8:
When you are done, save the file on your local hard drive and upload it to the Course Project Dropbox. Your file should have the following filename format: Lab4_FirstInitialLastName.accdb.

Learn More

Case Problem 2
Data File needed for this case problem: exercise.accdb (cont. from Tutorial 10)

Parkhurst Health & Fitness Center Martha Parkhurst asks you to continue your work on the Exercise database by creating a new form and enhancing its accuracy and appearance. To help Martha with her request, complete the following steps:

1. Open the Exercise database located in theAccess3\Case2 folder provided with your Data Files.

2. Use the Form tool to create a form named frmMemberinfo using the tblMember table as the source table. Create a procedure for the frmMemberInfo form to convert City field values to proper case—capitalize the first letter of each word, and convert all other letters to lowercase. Test the procedure.

3. Create a procedure to verify Phone field valuesin the frmMemberInfo form by doing the following:
a. For a State field value of VA, the first three digits of the Phone field value must equal 703 or 804. If the Phone field value isinvalid, display an appropriate message, cancel the event, undo the change, and move the focus to the Phone field.
b. No special action is required for other Phone field values.
c. Test the procedure, and then save your form changes.

4. Create a procedure for the frmMemberInfo form to do the following:
a. Display the word current to the right of the MemberID text box in bold, magenta text only when the MembershipStatus field value is Active. Otherwise, display the
word review in bold, blue text. (Hint: Remove all controlsfrom the control layout before resizing the MemberID text box and adding the label for the message. Use the Caption property in your VBA code, and make sure you enclose Caption property settings in quotation marks.)
b. Test the modified form, and then save your form changes.

5. Make a backup copy of the database, compact and repair the Exercise database, and then close the database.

Learn More

GetWell, Inc. is a leading pharmaceutical organization. The organization wants to store all its customer and order details in an MS Access database. You need to perform the following tasks:

Create a database called GetWell.
Create two tables, Customer and OrderDetails, with the following information:

Customer
FieldName Type Size
CustomerID Text 5
CustomerName Text 25
CustomerAge Text 3
CustomerAddress Text 30

CustomerID will be the primary key in the Customer table.

OrderDetails
FieldName Type Size
OrderNumber Text 5
CustomerID Text 5
OrderDate Date/Time
OrderValue Number
Discount Number

OrderNumber will be the primary key in the OrderDetails table.

Enter following records in the Customer table:

CustomerID CustomerName CustomerAge CustomerAddress
C0001 Debbie Howe 28 123, Florida
C0002 Ed Young 33 New York, 234
C0003 Tom Wilkins 47 A-234 New Jersey
C0004 XXXXX XXXXXe 76XXXXXSuite
C0005 Ken Burton 52XXXXXNorth
C0006 XXXXX XXXXX 65 53, N. Dakota
C0007 XXXXX XXXXX 29 655, Wisconsin
C0008 John Barrett 55 2234, Georgia

Enter the following records in the Order table:

OrderNumber CustomerID OrderDate OrderValue Discount
O1207 C0003 10/1/2004 10500 10
O2222 C0008 4/30/2005 26790 15
O2657 C0001 11/23/2004 50000 15
O3412 C0003 2/3/2004 222 0
O4532 C0004 3/27/2005 12320 10
O4578 C0006 6/8/2004 1110 5
O4778 C0001 7/8/2004 3378 5
O5732 C0007 4/15/2005 98700 15


Write SQL statements for the database you have created to do each of the following:

Display details of all the customers whose age is greater than 45 years.
Display the names and addresses of all the customers who have placed orders with a value more than $10,000.
Display the names of customers along with the values of the orders they placed after March 01, 2005.
Create an INSTEAD OF trigger to display the message "You cannot update Order Value", if a user tries to update the OrderValue field in the Order table.

Perform all the above activities using MS Access.

Learn More