Lab 12 : The Blue Lab

Introduction to Databases : MS Access




Warning:

Complete this lab with the Access package that comes with Office '97. This is available in any of the clusters on campus. Using Access 2000 will ensure that the lab that you turn in will not be compatible with the version of Access that we grade on. If you do choose to use Access 2000, make sure you save it in Access 97 format before turning it in. Also, make sure it opens in Access 97 with no problems. If it does not open in Access 97 and work correctly in Access 97, your grade, without any shadow of a doubt will not reflect any of the effort that you put into the lab. Note: Whining will not be entertained!

Click here to see the color version of this lab.


Download:

Download the lab12.mdb Access file from here.



Objectives:

  • Understanding MS Access
  • Using multiple tables in a single query.
  • Using criteria to narrow query results.
  • Controlling query output to display specified fields.
  • Creating a stylized report based on a query result.



    • Introduction:

    This lab is a tribute to Joni Mitchell, one of the greatest song writers and vocalists EVER! If you don't know who Joni Mitchell is, I suggest that you use this lab as an opportunity for enlightenment and buy Blue (her best album, in my not so humble opinion). It is your prerogative to ignore this tip, but remember you are voluntarily relegating yourself to a lower cultural plane forever.



    Databases Defined:

    In a nutshell, a database is a repository for data, provides convenient ways to access and query the data. A database differs from a spreadsheet in that it is not meant to involve intensive computation, instead it focuses on the efficient storage and retrieval of data.

    In a database data is stored in one or more tables. A table consists of many components called records. Each record consists of one or more fields. These fields are the *actual* data stores. A database is a great example of abstractions as layers are added to improve the organization of the data.

    Generally speaking, there are a couple of ways that databases can be structured.

  • All the data in stored in one table. This is the simple solution, and gives all the advantages associated with simplicity; ease of creation and short creation time. The draw back though is that you have one honking big database that has little or no organization and is inefficient to search. When using a single table, all the records must have the same properties. Thus, if there was a database that for some inane reason stored information about cars and planes , any information stored about a car, would also have to be stored for each plane, and visa versa. Logically this is incorrect, but cannot be easily remedied as the database is not designed to create separate records for a car and a plane. This simple example illustrates the inefficiencies can thus arise in a single table database.

  • A relational database, may have multiple tables of data that are linked together by one shared field (the shared field means the same data field that is common to both tables). This modular approach can make the database much easier to handle, as you only have to work with the portions of the table you need at any given time. The multiple table design also makes the database more modular. This means new information can be added to the database with relative ease. The database is thus much easier to maintain and update. The multiple table design makes relationships between different elements easy to illustrate. Referring back to the previous contrived example, in a relational database the planes and automobiles could be separated into different tables. Each of the tables would have unique properties appropriate to the vehicle.

    Each record in your database is much like an element in a hash table. Each hash table has elements, and each element must have a unique key associated with it. This key guarantees that Access can distinguish records from one another. Text fields, such as names, are bad primary keys, because two objects could have the same value in the text field (two people can have the same name). It is better to use some sort of ID number (like a social security number). When Access adds a primary key field, it will be a number field that automatically increments itself. The field will label the first record in the table 1, and count upward from there. This automatically generated key is what is used as the identity for a field in each of the tables.



    The tools of the trade:

    The major tasks that can be performed with a database are data entry, data retrieval, and data display. Microsoft Access's tools to accomplish these tasks are:

  • Forms: To enter the data you can create a data entry form. A form is an interface template for a table. When you make a form for a table, the form allows you to enter data into the table. The form also allows you to edit previously entered data.

  • Queries: To search the data you perform a query. To perform a query you specify the data you want to search, and then specify what you are looking for in the data. This involves using a special query language for the database.

    Queries are used to find specific data in the database. With a query, the database is asked a question about the data that it contains, the database then returns an answer to the query. This is one of the most important and often the most complex features of a database.

  • Reports: To display the data you can create a report. A report can show all of the data, certain fields of a the menu.table, or a summary of the data.



    • Procedure:

    Step 1:

    Open up Microsoft Access and then choose the open option. Open up the file that you have just downloaded. Once you have accomplished this step give yourself a huge pat on the back. You should see something like this on your screen.





    Let us study the picture in some more detail. There are various tabs in the window. The tabs that we will be concerned with are the Table tab, Query tab and the Report tab.

    Step 2:

    The Table Tab:

    First click on the table tab. Tables as mentioned before are the main data storage elements in the database. The three tables that are present in this database are:

  • Covered Songs (A list of Joni Mitchell songs that have been covered by other artists)
  • Reprise Albums (A list of albums that Joni Mitchell recorded when she was contracted to the Reprise record label)
  • Song List (A list of all the songs on the Reprise Albums)

    • You can select one of these tables followed by a click on the open option on the left of the window. Peruse these tables for information on the great one.

    Step 3:

    Queries:

    There a few fundamentals that you need to grok before performing queries in Access. The baby steps required to create a new query are listed below:

  • Click on the Query tab
  • Click New
  • Choose the design view option
  • Select all three tables that show up (This option lets you select tables used in the search)
  • Add the tables to the query

    • After you have created the new query and added the tables, you will see a window with two main sections. The upper section contains miniatures of the tables that you selected for the query. The lower section is where you insert fields and criteria for your queries.





    The database you are using in this lab has three tables. The Reprise Albums table contains three fields the ProductID field (the unique key), album name and Year of release. The Song List table contains the ID (the unique key), an Album ID (the ProductID from the aforementioned table) and the songs from the respective album. The Covered Songs table contains the unique ID, the name of the song that was covered, the year that the song was covered and the name of the artist who covered the song.

    The three tables allows the Joni-Base to contain disparate kinds of data. By joining/linking our tables we can get them to work together. All you have to do to join the tables is click on the field name in one table and drag it over to the like field name in the other table. When you let go of the mouse button, a line will appear, linking the two tables. To delete a link, right-click on it and choose delete from the pop-up menu.

    Join the tables EXACTLY as shown in the picture above so that the ProductID field from the Reprise Albums table is linked to the Album ID field in the Song List table and the Song Name field in the Song List table is linked to the Song Covered field in the Covered Sings table.

    You only need to be concerned with a few of the cells in the lower grid. The first one is labeled Field. In Field you specify the field you want to use in your query. The Sort cell is where you select the order in which you want the data from the query to be displayed. Placing an 'X' in the Show cell will cause that field to be displayed when you execute the query. The cell you will use the most is the Criteria cell. This is where you will specify the data attributes by which you are searching.

    Saving a query in Microsoft Access is simple. Unlike some other database programs, Access uses one file to store all of the tables, form, reports, queries, etc. After you have run a query and have the results you wanted to obtain, all you have to do is go to the File menu and choose Save. This gives you the opportunity to name and save your query.

    Let us walk through the queries that you need to conduct to complete this Mitchellicous lab.

    Before you conduct each of these queries remember that you need to create a new query. This is done by selecting the query tab and then hitting new.

    Query 1:

    Query the database for the names of all the songs that were originally sung by Joni and have been covered by other artist. Display the albums, the name of the songs and the names of the artists.

  • Hit the query tab
  • Hit new
  • Add the tables you need for the queries (all 3 tables)
  • Join the tables as described above

    • Since you have three tables in the upper portion we will select three fields.

  • The field from the Reprise Albums table will be the Album Name
  • The field from the Song list table will be Song Name
  • The field from the Covered Song table be Artist Covering Song

    • After you have selected the fields go to the Query menu and select the run option. If you have followed the steps to the letter you will get a list of all the covered Joni Mitchell songs.

    Select save from the File menu and save this query as Joni Cov Songs.

    Query 2:

    Query the database for the names of all the songs that were originally sung by Joni and have been covered by Tori Amos. Display just the name of the song.

    Go through the steps to add and join tables again

    Since you have three tables in the upper portion we will select three fields.

  • The field from the Reprise Albums table will be the Album Name
  • The field from the Song list table will be Song Name
  • The field from the Covered Song table be Artist Covering Song

    • We need to make one minor modification as we are searching for one specific artist.

    In the Criteria field type *tori amos*. This will narrow the search to just Tori

    After you have selected the fields go to the Query menu and select the run option. If you have followed the steps to the letter you will get one song that Tori Amos has covered that was sung by Joni Mitchell.

    Remember that I want just the name of the song, hence you will need to select/de select the show boxes as you see fit.

    Select save from the File menu and save this query as A Case of Joni

    Query 3:

    Query the database for the names of all the songs that have been covered from the album Blue. Display the names of the songs and the artists that covered them. Please don't display the name of the album. If you understood how the previous two parts work this should be a piece of cake for you.

    Select save from the File menu and save this query as Kind of Blue.
    You are now 3/4ths of the way through the lab, easy huh!

    Step 4:

    Reports:

    Creating a report is a practical way to represent your sorted data as document suitable for printing. You can also print forms or tables, but reports give you more control over how you display and organize your data. We will now create a report that will display

    Report 1:

    To create a new report:

    Choose the Report tab and click on the New button.
    Click on the Report Wizard in the upper list menu.

    Choose a field from the Table/Queries list. For this report select Kind of Blue. Make sure to add all the available feeds into the report.
    Follow the directions in the wizard and feel free to exercise your artistic abilities.
    Click on the Finish button to view the report

    To save a report, go to the File menu and click on save. Save the report as Blue-Report.

    Step 5:

    Forms:

    Forms are one of Microsoft Access' most versatile tools. You can view all the fields for one record in Form view, or you can view all the records at one time in the Data sheet view. Forms are also a very efficient way of entering data into your tables. Overall a form is an excellent layout for entering, viewing, or changing the data in your database. The information for a form can come from a table, a query, or both.

    Since you want to get done with this lab quickly and since I don't want you mess with my Joni database I will not bore you with details about forms.

    Step 6:

    Turnin:

  • Turn in lab12.mdb via WebWorks
  • Go buy Blue (optional, but highly recommended)
  • Listen to Blue and then proceed to spend oodles of money on the rest of Joni Mitchell's albums