CT230 Lab 1

http://geminga.it.nuigalway.ie/~gettrick/courses/CT230/labs/lab1.html

• if any of this lab is unclear, raise your hand or go up to ask the TA
• your lab report (hardcopy/printout) should be written during the lab and given to the TA at the end of the lab for grading [make sure your ID & name are on the cover]
• if there is a problem with your computer, try rebooting. if this does not work, go to another computer, and send an e-mail to joe.oconnell@nuigalway.ie to report the problem

Microsoft ACCESS is the Database Management System we will use in CT230. If you have Microsoft Office, you will find ACCESS included in that. It creates (writes) and reads from .mdb (Microsoft DataBase) files. In this lab you will create a new Database called lab1.mdb. ACCESS also comes with sample databases which you will look at to get used to creating tables.

TIP: WHILE DOING YOUR LAB, YOU CAN RUN INTERNET EXPLORER (OR NETSCAPE) TO LOOK AT THE LAB HANDOUT, AND AT THE SAME TIME RUN ACCESS TO CARRY OUT THE TASKS IN THE HANDOUT IN A SEPARATE WINDOW. FOR EACH WINDOW, CLICK ON THE SMALL 'BOX' ICON SECOND FROM THE END IN THE TITLE BAR (BESIDE THE 'X' IN THE TOP RIGHT CORNER). IT IS GOOD TO GET INTO THE PRACTICE OF DOING THIS - IT ALLOWS YOU TO CUT AND PASTE MATERIAL.

• To start the ACCESS program
  • double click on the ACCESS icon if it appears on your desktop when you log in
  • at the bottom left of the screen choose programs from the start menu and select ACCESS
  TIP: BEFORE YOU START CREATING NEW FILES, ITS A GOOD IDEA TO CREATE NOW A NEW FOLDER (CALL IT CT230 OR WHATEVER) INTO WHICH YOU CAN PUT ALL FILES, DATABASES, REPORTS ETC. TO DO WITH THIS LAB. THIS WILL HELP YOU FIND MATERIAL LATER.

• To start with, look at one of the sample databases available. Choose Open Existing database and select the Northwind database. Your screen should look like the following: After opening an existing database, ACCESS will display several object tabs. Each tab represents a grouping of objects for the data stored in the database. The objects are of different types; tables, queries, forms, reports, etc. Each object type is a different way to look at the same set of data elements stored in the database. Start by looking at the Customers table in the Northwind database.

• Datasheet View
  Double click on the table called Customers. You should now see the contents (data) in the table: At the top of each column we have the attribute name. Values for each attribute appear in the columns. All values in one row (tuple) of the table are considered a record. Each of the fields in one record relate to the same customer and can be identified by the key field, 'Customer ID'.

  This is called the Datasheet View. It is the form for update and display of the actual data contents of a database file/table.

• Design View
  The Design View gives the actual structure of the table, i.e. it defines the relation schema. To open it, double click on the Design icon (2nd. from the left). The display should change from the data sheet view to the following design view. The design view will list each attribute in the table (called Field Name), with parameters describing the attribute name, data type and description. The attribute names correspond to the table's column headings.

  Data type defines the type of data allowed in that field. There are a number of different data types you can use. Access makes it easy to choose by listing them for you. To see this, click on the data type field for Customer ID. A down arrow will appear in the far right end of the data type slot. Click on that arrow and Access will display the different data types available.

  The different attributes/fields are listed row by row in the Design View. Each row can be highlighted separately by clicking on it. Once highlighted, the tab General below the table will show parameters that can be set for that attribute. For example, Field Size is a non-negative integer giving the maximum number of characters that are allowed in a field; Required, which can be set to Yes or No, is a parameter that states whether the attribute is required or not, i.e. is it allowed have a NULL value; Allow Zero Length which can be set to Yes or No, is a parameter that states whether the attribute can be e.g. the empty string.

  Note at the left of the Customer ID field a small key symbol. This indicates that the Customer ID has been designated as the primary key for this table.

• Finding Records
  How can we locate a particular customer in the customer table? We don't want to look through all 91 records to locate one individual. First, go back to the data sheet view of the customer table. To do this, locate the small icon in the upper left corner. It will resemble a small table. Click on that, and you should see the table of data we first saw.

  Say we want to find the record with Customer ID corresponding to PARIS. Click once on the attribute name (column title) corresponding to the attribute you wish to search (Customer ID in this case). The complete column should become highlighted.

  From the top toolbar, select Find from the Edit menu. This will give the following Find Box. Enter PARIS and click Find Next. This will highlight the record corresponding to PARIS. Click on Find Next once again. It should not find a second record corresponding to PARIS. The reason is you are searching on a key attribute, which has to be unique. Experiment by entering some other values for Customer ID search and satisfy yourself that they are either not found or occur only once.

• TO BE HANDED IN
  
  1. Find all the Company Names of Customers in Madrid
  2. Find all the Company Names of Customers in Nantes
  3. Find a Company Name whose Contact (Sur)Name is Ottlieb
  4. Find all Contact Names of Customers whose postal code is 10100
  5. Records can be sorted based on any attribute: The sorting above is alphabetical on Customer ID. To sort on another attribute, click on the column heading, and from the Records menu at the top choose Sort. Experiment with sorting on addresses, phone numbers, etc. When sorted alphabetically on the Contact Name attribute, what is the Customer ID of the last record?

• Creating a new database
  
  • Close ACCESS completely.
  • Start ACCESS again, this time selecting Blank Access Database.
  • Under File Name: enter "lab1.mdb". Make sure the entry after Save in: is where you want to save the file (you should here save in a special CT230 folder) Click on the create button.
  • Click on the New icon in the tool bar (3rd. from the left). From the options, choose Design View
  • To design the table, enter under Field Name the first attribute name you want for this table. Click on the corresponding box under Data Type and choose by clicking on the down arrow. Optionally, enter also a data description. Further attributes can be entered in the 2nd., 3rd., 4th. rows etc.
  • By right clicking immediately to the left of any row corresponding to an attribute, you will be given a menu from which you can choose that attribute as a primary key. If you enter some attributes and then choose save, you will automatically be prompted to choose which attribute corresponds to the primary key.
  • TO BE HANDED IN
    Create a new blank database with one table called Customers with schema Customers(ID, FIRSTNAME, LASTNAME, ADDRESS, CITY, STATE, ZIPCODE). The Data Type of ID should be AutoNumber, STATE should be restricted to 2 letters, and zipcode a 5-digit number. ID should be the primary key. Open the table in Datasheet View, and make up five entries. Along with these add the two entries

    John Lalor,              Sara Green,
    32 Hawthorn Drive,       109 Beech Park,
    Albany, NY 00508         Gary, IN 46517
    

    Order your table (which should now have seven entries) based on ZIPCODE, print it out and hand it in.