Docstoc

Labs

Document Sample
Labs Powered By Docstoc
					CP611 Labs
Lab01
Objective:

            To login to the network, invoke Access and use the help file.
            To access the unit home page and download the sample database.
            To do some preliminary reading on Entity-Relationship Modelling

What you will need:

            A network account.
            Kroenke (textbook – see unit description)

What to submit:

            Nothing for this lab.

Tasks:

     1.      Login to your network account. Consult your tutor if this is a problem.
     2.      Start Internet Explorer (Note, you cannot use Netscape). Go to the URL
             http://www.ballarat.edu.au/~fdeluca/cp611/ There you will see a list of files.
             Select the file called cp611.mdb to download a sample database. Right
             click the mouse and choose Save Target As. Save it to your J: drive.
     3.      At the same URL find the file called unitdesc.doc and read the contents,
             particularly the sections Learning Tasks and Assessment and Assessment
             Criteria. Please take note of due dates and rules on late submissions.
     4.      Start MS Access and open the database cp611.mdb you just downloaded.
     5.      Open the table called Category and review the data about product
             categories. Close the table.
     6.      Open the same table in design view and review the table's structure.
             Examine all the components of the design screen and the options
             available.
     7.      Invoke Help | Contents and Index on the Access menu. Select Contents.
             Find and open the Microsoft JET SQL Reference section. Find and open
             the Data Manipulation Language section. Review the syntax of the
             SELECT statement.
     8.      Read Kroenke Chapter 3 – The Entity-Relationship Model.
Lab02
Objective:

            Understand some key database and DBMS terms.
            To do some preliminary reading on SQL

What you will need:

            Kroenke
What to submit:
            Answers to tasks 1, 2, 3, and 4 below. To ensure work is returned to you
             promptly be sure to place your name, lab sheet number, and the day of
             week and time of your usual lab (e.g. Thursday 10:30), on your
             submission.

When to submit:

            Lab submissions will be accepted no later than the end of the following
             weeks lab. E.g. Lab 2 will be accepted until the end of your usual lab time
             in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs
             submitted e.g. by email or placed in their assignment box.

Tasks:

     1.      “A database is a self-describing collection of integrated records” (Kroenke
             2002). What does this mean?
     2.      Briefly explain the advantages a database system would have over an
             application system implemented using traditional file processing.
     3.      What is a DBMS?
     4.      What are the different types of queries that a DBMS like MS Access offers?
             Briefly describe how they work.
     5.      Read Kroenke Chapter 9 – Structured Query Language
Lab03
Objective:

            To model a real world problem and convert it into an Entity-Relationship
             diagram.

What you will need:

            No special requirements.
            Note, remember to use our diagramming conventions for the E-R
             diagrams.

What to submit:

            Answers to each of the tasks below. To ensure work is returned to you
             promptly be sure to place your name, lab sheet number, and the day of
             week and time of your usual lab (e.g. Thursday 10:30), on your
             submission.

When to submit:

            Lab submissions will be accepted no later than the end of the following
             weeks lab. E.g. Lab 2 will be accepted until the end of your usual lab time
             in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs
             submitted e.g. by email or placed in their assignment box.

Tasks:

     1.      Draw an entity-relationship model for the following business description.
             Each driver in our system must drive at least one car and may drive
             several cars.
     2.      Draw an entity-relationship model for the following business description.
             Students at a university can have only one major and are assigned to one
             advisor. All students must have a major, but they may choose not to have
             an advisor. A professor may have many students as advisees or may have
             none.
     3.      Draw an entity-relationship model based on the following business
             documentation. Include primary keys and attributes. The following
             information may also be of help
              Each employee belongs to one department only.
              An employee is assigned to at least one project and a project must have
                 at least one employee.
 Employee No: 01267                    Employee Name: Graham Clark
 Employee Address: 12 Smith Street Richmond VIC 3456
 Hire Date: 02/03/1980                 Salary: 30,000
 Project(s) assigned to:               Project no:        Project Start Date:
 Marketing project                          123                      12/01/2002
 Payroll project                            175                      13/06/2002
 Leave: 03/03/1999 - 17/03/1999
       15/12/1999 - 29/12/1999
 Department No: 05          Department Name:                Department Location:
                            Computing                       MEL


4.   Draw an entity-relationship model for the following business description.
     Include primary keys and attributes.
     A project manager has just described his/her data requirements for the
     system that you are preparing a data model for. The details are as follows:
     A Project ID identifies each project. Each project is also given a short title
     that may or may not be unique and may be given a longer description. A
     project manager must manage each project and may have many team
     members assigned to work on it. Some of the team members may work on
     more than one project. The project has a start date and scheduled
     completion date. The project also has a total estimated cost. Employee ID
     identifies people in the company, although the project manager needs to
     know the name, phone number and e-mail address assigned to each
     member of his team.
     Each project consists of several tasks, identified by a Task ID. Each task
     has a scheduled start date and a scheduled completion date. As the tasks
     are performed the actual start and end dates are also recorded. Each task
     has a planned duration. A task may also have one or more tasks that must
     precede it.
Lab04
Objective:

             To model a real world problem and convert it into normalized data
              structures.

What you will need:

              No special requirements.

What to submit:

              Answers to each of the tasks below. To ensure work is returned to you
               promptly be sure to place your name, lab sheet number, and the day of
               week and time of your usual lab (e.g. Thursday 10:30), on your
               submission.

When to submit:

              Lab submissions will be accepted no later than the end of the following
               weeks lab. E.g. Lab 2 will be accepted until the end of your usual lab time
               in Week 3.

Where to submit

              Your lab tutor will provide instructions on how they would like the labs
               submitted e.g. by email or placed in their assignment box.

Task:

        1.     Convert the conceptual model from task 4 from the previous lab (A
               project manager …) into normalised relational structures showing all
               attributes and identifying primary keys.
        2.     Convert the following data tables into normalized data structures showing
               all attributes and identifying primary keys.


             Order# Product# QtyOrdered ProductPrice ProductDescription Customer#
             1001     102         3               $75            Widget                   104
             1001     107         6               $110           Gadget                   104
             1002     102         2               $75            Widget                   101
             1003     107         4               $110           Gadget                   103
             1003     104         1               $155           Dealie                   103
             1004     107         2               $110           Gadget                   104
3.   Convert the following data tables into normalized data structures showing
     all attributes and identifying primary keys.


 EmployeeID Name           WageClass WageRate DeptName DeptHead BirthDate
 46823             Jan     B              $8.50         Packing      L. Davis    8/6/57
                   Jones
 37910             Al      A              $7.25         Shipping     S. Smith    1/4/51
                   Evans
 52907             Sam     C              $9.75         Shipping     S. Smith    23/2/44
                   Smith
 63015             Ann   A                $7.25         Packing      L. Davis    27/9/59
                   Adams
 29414             Tom     B              $8.50         Sales        J. Kerns    11/3/64
                   Bates
 40174             Fay     C              $9.75         Shipping     S. Smith    15/4/42
                   Moss


4.   Convert the following data tables into normalized data structures showing
     all attributes and identifying primary keys.


 SubjectNo Semester SubjectDescription RoomNo RoomCapacity Instructor
 CIS120        1           Intro to CIS           108           50               Morgan
 CIS120        2           Intro to CIS           205           60               Kroenke
 CIS220        1           Programming            108           50               Bosse
 ACC255        1           Intro to Accounting    204           80               Evans
 ACC255        2           Intro to Accounting    205           60               Smith
Lab05
Objective:
            To practice retrieving data from one table.
What you will need:
            The employee and category tables in the MS Access database file called
             cp611.mdb you download from http://www.ballarat.edu.au/~fdeluca/cp611/
            You may also find the following table and column names useful.




What to submit:
            A copy of the SQL used for each task below. Query results are not required, but
             be sure to test your SQL as marks will only be given for SQL that works. To
             ensure work is returned to you promptly be sure to place your name, lab sheet
             number, and the day of week and time of your usual lab (e.g. Thursday 10:30),
             on your submission
When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.


Tasks:
     1.      List all details about all employees.
2.    List all details about all categories.
3.    List all details (by naming each field) about all categories.
4.    List all details about all employees but place the postal code column at the
      extreme left.
5.    List all details about all employees ordered by last name in ascending order
6.    List all details about all employees ordered by city and, where the city is the
      same, by employee id in descending order.
7.    List the last names, country and home phone number of all employees.
8.    List the city for each row of the employee table.
9.    List all cities where an employee lives but eliminate duplicates.
10.   List all details about any employees with a last name of King.
11.   List all details about employees that live in the city of London
12.   List all details about employees that do not live in London.
13.   List all details about employees who were born before 1960.
14.   List all details about employees that were hired before 1994 and live in London.
15.   List all details about employees that live in the city, Seattle or live in the country,
      UK.
16.   List all details about employees that live in the city of London, or those that live in
      Tacoma and do not report to anyone. (Hint, you may need to use the keyword
      NULL).
17.   Using the OR operator, list all details about employees that live in Tacoma or
      Seattle or Redmond. Repeat using the IN operator.
18.   List all details about employees with ids between 3 and 7.
19.   List all details about employees whose last name has the letter e in the second
      position.
Lab06
Objective:
            To practice using aggregate functions and group by from one table.
What you will need:
            The product, order, and order detail tables in the MS Access database file called
             cp611.mdb you download from http://www.ballarat.edu.au/~fdeluca/cp611/
What to submit:
            A copy of the SQL used for each task below. Query results are not required, but
             be sure to test your SQL as marks will only be given for SQL that works. To
             ensure work is returned to you promptly be sure to place your name, lab sheet
             number, and the day of week and time of your usual lab (e.g. Thursday 10:30),
             on your submission
When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.
Tasks:
     1.      Show the average unit price of all products.
     2.      Show the total number of orders.
     3.      Show the total number of orders where the ship region has not been set. (Hint i.e.
             NULL).
     4.      Show the number of discontinued products.
     5.      Show the price of the cheapest product.
     6.      Show the price of the most expensive product.
     7.      Display the total number of products supplied by each supplier. Show the count
             heading as "No of products supplied".
     8.      Display the total units on hand for all products.
     9.      Display the total quantity ordered for each product across all orders.
     10.     For each category, display the total number products.
     11.     For each category, display the total number of products if that number is 10 or
             more.
     12.     For each category, display the total number of products and the total stock on
             hand, for that category, if that total stock on hand is not more than 200 units
Lab07
Objective:
            To practice using joins and sub queries.
What you will need:
            The supplier, category, product, order, and order detail tables in the MS Access
             database file called cp611.mdb you download from
             http://www.ballarat.edu.au/~fdeluca/cp611/
            Note, DO NOT use INNER JOIN command.
What to submit:
            A copy of the SQL used for each task below. Query results are not required, but
             be sure to test your SQL as marks will only be given for SQL that works. To
             ensure work is returned to you promptly be sure to place your name, lab sheet
             number, and the day of week and time of your usual lab (e.g. Thursday 10:30),
             on your submission
When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.

Tasks:

     1.      Display company name and product name for all products.
     2.      List the category name and product name for all products and order the list by
             product .
     3.      List the order id and the id and name of all the products that have been ordered
             by any customer. Place all products for the same order together.
     4.      List the customer company name and the order id and date for all the orders that
             have not yet been shipped.
     5.      Show the product name and the unit price for the most expensive product(s) in
             the product range.
     6.      List the product id and name for the first 10 products in the product range.
     7.      List the product id, name and unit price for the 10 most expensive products in the
             product range.
     8.      Display the product id, name and unit price for all products whose unit price is
             greater that the average unit price in the product range.
     9.      Show all the details (from the Orders table only) for all the orders that were
             ordered on the first day of orders.
     10.     Display the product id and name for any product that have not been ordered
             (customer orders).
     11.     Show all the cities (only once) that the suppliers, customers or employees live in.
     12.     Display the customer's company name, customer id, product name, and the
             quantity ordered for all orders that were shipped after they were required.
Lab08
Objective:

            To practice inserting, modifying and deleting data as well as using DDL
             statements

What you will need:
            The MS Access database file called cp611.mdb you download from
             http://www.ballarat.edu.au/~fdeluca/cp611/
What to submit:

            A copy of the SQL used for each task below. Query results are not required, but
             be sure to test your SQL as marks will only be given for SQL that works. To
             ensure work is returned to you promptly be sure to place your name, lab sheet
             number, and the day of week and time of your usual lab (e.g. Thursday 10:30),
             on your submission

When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.

Tasks:

     1.      Make a backup copy of Category table using SQL and call it CategoryBackup
     2.      Create a table called ShipperBackup which is the same as the Shipper table
             using the CREATE statement. (Hint you will need to define the first field as type
             INTEGER not AUTONUMBER).
     3.      Copy all the records in the Shipper table to the ShipperBackup table using SQL
     4.      Delete all the records from the ShipperBackup table.
     5.      Copy records in the Shipper table to the ShipperBackup table using SQL, for the
             company "Speedy Express", using SQL
     6.      Insert your details into the Customers table.
     7.      Change the price of all products to allow for the GST by increasing them by 10%,
             using SQL.
     8.      Insert the following values into the Supplier table for supplier number 1, Fax =
             "111 222" and Homepage = "ExoticLiquids@blahblah.com" , using SQL
     9.      Remove all products from the products table where no customer orders exist for
             those products, using SQL
     10.     In your copy of cp611.mdb. Create a new table called Annual Leave and define
             the following fields and options, using SQL.
             a. EmployeeID. (data type: integer)
             b. Starts (data type: date)
             c. Ends (data type: date)
     11.     Alter the table called Annual Leave and add Loading. (data type: currency) ,
             using SQL
     12.     Remove the field Loading from Annual Leave, using SQL
     13.     Remove the tables Annual Leave from the cp611 database, using SQL.
Lab09
Objective:
            Develop a small system using MS Access.
What you will need:
            The MS Access database file called cp611.mdb you download from
             http://www.ballarat.edu.au/~fdeluca/cp611/
            For those students who cannot find an alternative image, you can also obtain a
             graphic file called logo.gif from the above URL, for task 5b below.
What to submit:
            Screen prints of each of the forms, queries, reports and macros developed for
             tasks 1 to 6, below. To ensure work is returned to you promptly be sure to place
             your name, lab sheet number, and the day of week and time of your usual lab
             (e.g. Thursday 10:30), on your submission.
When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.
Tasks:
     1.      Create a form for the Employee table using the Autoform feature of MS Access.
     2.      Create a form for the Customer table using the Form Wizard.
     3.      Create a query for the Supplier table using Query Design. Only include the
             columns: company name, contact name, city, country, phone and fax. Sort the
             results by country.
     4.      Create a report for the Category table using the Report Wizard.
     5.      Create a form manually, call it "menu", and include the following functionality:
             a. Place an appropriate label on the screen telling the user what system they
                   are using.
             b. Insert a small graphic somewhere on the form.
             c. Include a button that opens the Employee form you created above
             d. Include a button that opens the Customer form you created above.
             e. Include a button that opens the Supplier query you created above.
             f. Include a button that opens the Category report you created above.
             g. Include a button which closes the system
     6.      Create a macro, call it "autoexec" and have it open a maximized version of the
             menu form.
Lab10
Objective:
            Revise Distributed Databases.
What you will need:
            The MS Access database file called cp611.mdb you download from
             http://www.ballarat.edu.au/~fdeluca/cp611/
            Kroenke Chapter 17 – Sharing Enterprise Data
What to submit:
            Brief answers to tasks 5 and 7 and the printed output from task 6. To ensure work
             is returned to you promptly be sure to place your name, lab sheet number, and
             the day of week and time of your usual lab (e.g. Thursday 10:30), on your
             submission
When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.
Tasks:
     1.      Create a replica of the cp611 database. Hint: use the menu items - Tools ...
             Replication … Create Replica wizard. Select Yes to close the database when
             asked. Select No to create a backup of the database when asked. Select OK to
             location of replica database when asked. Select OK to confirm that the replica
             has been created when asked.
     2.      Ensure you have the cp611: Design Master database open.
             a. Alter the ContactName of CustomerId="AROUT" to "Thomas Hardy Jnr".
             b. Alter the ContactTitle of CustomerId="BSBEV" to "Sales Agent".
     3.      Open the replica of cp611 you created above.
             a. Alter the ContactTitle of CustomerId="BSBEV" to "Sales Associate".
     4.      Synchronize the Design Master and the Replica database. Hint: Tools …
             Replication … Synchronize Now. Select OK to synchronize the databases when
             asked. Select Yes to close the database when asked. Select OK to acknowledge
             that the databases have been synchronized when asked.
     5.      Select Yes to resolve the conflicts when asked. Select View to see the conflicts.
             Describe the options you have to resolve the conflicts that exist. Select Resolve
             and then Close when asked. Close the replica database.
     6.      Create a Partial replica of the replica cp611 database for customers who live in
             ‘London’. Hint: use the menu items - Tools ... Replication … Partial Replica
             wizard. Select the Next buttons at the end of each step. Note; deselect all tables
             that your filter does not directly apply to. Print the replication report.
     7.      As well as an exercise in replication the above also illustrated a form of database
             partitioning / fragmentation.
             a. What type of partitioning / fragmentation was performed?
b. Define the terms partitioned and replicated as they pertain to distributed
   applications.
c.   Explain the differences in the four types of distributed databases. (i.e.
     Nonpartitioned-Nonreplicated, Partitoned-Nonreplicated etc.)
Lab11
Objective:
            Revise Concurrency and Recovery Issues and Object Oriented DBMS.
What you will need:
            Kroenke Chapter 11 – Managing Multi-user Databases
            Kroenke Chapter 18 - Object Oriented Database Processing.
What to submit:
            Brief answers to each of the tasks below. To ensure work is returned to you
             promptly be sure to place your name, lab sheet number, and the day of week
             and time of your usual lab (e.g. Thursday 10:30), on your submission
When to submit:
            Lab submissions will be accepted no later than the end of the following weeks
             lab. E.g. Lab 2 will be accepted until the end of your usual lab time in Week 3.

Where to submit

            Your lab tutor will provide instructions on how they would like the labs submitted
             e.g. by email or placed in their assignment box.
Tasks:
     1.      Differentiate between a shared lock and an exclusive lock
     2.      What is a deadlock? How can it be avoided? How can it be resolved once it
             occurs?
     3.      Describe what is meant by the following terms:
             a. Object Classes
             b. Objects
             c.   Methods
             d. Attributes
             e. Inheritance
             f.   Superclass
             g. Encapsulation

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:20
posted:8/5/2011
language:English
pages:15