Business Analysis
ITEC 630 - Fall 2009
Prof. J. Alberto Espinosa

Last updated: 10/30/09

Database Self-Practice Lab Exercise

Database Design, Implementation and Queries  

General Instructions:

The Assignment

You will be using MS Visio and MS Access for this exercise. Although MS Access is not as powerful as other databases like Microsoft SQL Server, Oracle, Visual FoxPro and DB2, it is widely used in companies as a front-end to other data sources. MS Access also contains most of the relational database features discussed in class.  This assignment contains three main sections: (1) data modeling (first week); (2) relational database design and implementation (first week) and (3) database queries (following week).

 

Database Implementation

 

SECTION 1: DATA MODELING (Entity-Relationship Diagrams)

Scenario: Hypothetically, a small company has hired you as a consultant to help them design and develop a database to track client orders. You have outsourced the requirements analysis work to another consultant, Dr. Espinosa, who has interviewed all relevant managers and users of this company you come up with a written description of the entities (i.e., tables) and relationships you need for this system.  He has identified the requirements for this database application, which are stated below.

Note: While not needed, some times it helps to have a visual display of the tables with data. You can find the data tables here.

Requirements:   (note: entities or tables are expressed as nouns and are bold-faced; relationships between entities are expressed as verbs and are underlined): The company has a list of clients who place their orders by phone. Clients can place many orders, but each order must be associated with only one client.  Each order must contain one or more line items and each line item can only be associated with one order.  Also, a product may appear in many line items provided that they come from different orders.  But a line item can only be associated with only one product from the company's product list. Each product belongs to one product type and each product type can have many products .  Because the company is customer-oriented (but doesn't have a CRM system), they also want to keep track of all client communication every time a company representative contacts a client.  A client can be contacted many times and each client contact entry must be associated with only one client .

Please create a data model using MS Visio. You can find a quick guideline on how to use MS Visio for data modeling here. Please be sure to read these Visio guidelines before you continue.

Consistent with the lecture notes, you should follow this sequence to build your data model:

  1. Open MS Visio and select the Database category and then the Database Model Diagram option
  2. I highly recommend selecting View from the main menu, then unchecking Grid and Connection Points
  3. Create all the entities by dragging one entity symbol to your design for each entity:
    Clients, Products, ProductTypes, Orders, LineItems and ClientComm
    Then double-click on each entity and give them their respective names in the Definition tab
  4. Add all primary keys to these entitites using the Columns tab. Enter the primary key field(s) for each entity and check the PK box. Notice that the PK appears bold-faced in your diagram and with a separating line (below which all other attributes will added later). The primary keys are all the underlined attributes listed in the Data Definition part described in the next section.
  5. Now create all relationships by dragging the relationship symbol to the diagram and connecting them to the 2 respective entities you are relating. If your relationship has an arrow, it doesn't matter at this point where the arrow goes.
  6. If your relationship line shows an arrow, please change the line shape to "Crow's Feet" notation, which is more standard. To do this, select "Database" from the main menu, then "Options", then "Document", and then click on the Relationships tab and check the Crow's Feet box. This makes it easier to distinguish cardinalities and modalities. You can click on the relationship line to name the relationship and to change cardinatlities and modalities

    Note: there is a nuisance issue in Access in that the "one" side of a relationship always shows up as an optional relationship -- i.e., with a 0 -- and there is no option to change this to mandatory -- i.e., 1; but there is a workaround: you can change this by making the relationship "Identifying" and then change it back to "Non-Identifying". I will explain this in class.
  7. When you create relationships, the foreign keys should be noted automatically by Visio (why do you think this is? "a good exam question")
  8. Now add the remaining attributes, also listed in the Data Definition part of the next section below.
  9. I suggest that you print your relationship or keep it handy on the screen as you work on the next section of the exercise to better understand the conversion from a data model to a relational database design.

SECTION 2: RELATIONAL DATABASE DESIGN

You will work on this part of the lab exercise with MS Access. This section of the assignment contains 8 parts.

1. Data definition--define your database by creating and modifying tables as needed
2. Entity integrity--define all the necessary primary keys (PKs)
3. Data modeling--define all relationships
4. Update and delete rules--maintain referential integrity when updating and deleting data
5. Implement picklists--a convenient feature for data entry
6. Business rules--implement some important business data constraints
7. Design two forms and a report
8. Populate the database (i.e., enter some data)

To complete this exercise you need to run MS Access in your computer and perform all the steps specified below.

Part 1: Data Definition

Your inexpensive consultant, Dr. Espinosa, has created most of the tables you need for this database.  But you have just completed a thorough requirements analysis, and you realized that the Dr. missed one table and made some mistakes in other 3 tables. You get what you pay for. So, you need to add 1 new table and modify 3 of the existing tables.  Please create the new table and modify the 3 tables as indicated below.

Create New Table: As of now, the company only offers computer products, but it plans to expand the product offering to multiple product types later on (e.g., software, music, books, etc.).  You need to create a new table to record all the product types that the company decides to carry.  Please create the following new table:

ProdTypes (ProdType [text(8)], TypeName [text(36)])

Technical note: ProdType [text(8)] means that this field is named ProdType and it is defined in the database as a text field with a width of 8 characters. ProdType is underlined because it is the Primary Key for the table. Generally, all primary keys are underlined in this notation.

To build this table: (1) click on the "Tables" view button and then click on the "New" icon, and pick "Design View"; (2) once on the Design View, type in ProdType (no spaces) as your first field, click on Data Type and select "Text", then type in 8 for the Field Size (General tab); (3) Do the same for TypeName, but use a field size of 36; (4) To finish, click on the X on the top right corner to close the Design View window--the system will prompt you to give a name to the table you just created, so please call it exactly "ProdTypes" (no spaces) and click OK.  A warning message will pop on the screen saying that "There is no primary key defined" and will ask you if you want to create the primary key. Please answer NO. It is not a good idea to have database tables without primary keys, but you will define all primary keys later on, in Part 2 below. You should now be back on the main database screen in the Tables view, and you should now see the Clients table listed in your database.

Change Existing Tables: After analyzing the detailed requirements, you decide to change the length of some fields in 3 tables.  You also figure out that you need to add a field called "ProdType" in the Products table so that you can identify and record the product type for each of the products offered.  Please compare the data definition in the 3 tables below and make the necessary changes in the database (select one table and click "Design", then make the necessary changes and close and save the changes):

Clients (ClientID [text(8)], ClientName [text(36)], Address [text(40)], City [text(18)], State [text(2)], ZipCode [text(5)],  Telephone [text(10)]. One record per client. That is, change the field size for ClientName (from 32 to 36), Address (from 36 to 40), and City (from 16 to 18).

Products (ProdID [text (8)], ProdName [text(24)], ProdType [Text(8)], ProdDescription [memo], Price [number (single, Format=Fixed, 2 decimals)]). This table contains a list of products the company sells, one record per product. That is, change the field size for the field ProducName from 20 to 24. Also note that you need to create a new field on this table: ProdType [Text(8)]

Orders (OrderNo [text(8)], ClientID [text(8)], OrderDate [date/time (Format=Short Date)], OrderDescription [text(50)], OrderStatus [text(16)], DeliveryDueDate [time/date (Format=Short Date)]). List of orders placed by every client. It contains one record for every order placed. The LineItems table provides the details for each order. That is, change the size of fields OrderDescription (from 60 to 50) and OrderStatus (from 50 to 16).

Note: since you are reducing the size of these fields, when you save your changes, MS Access may warn you that some data may be lost with these changes. All this means is that if you have data in the OrderDescription beyond the first 50 characters, these extra characters will be chopped off. The same thing for the OrderStatus field. Just answer "Yes" and continue on.

The remaining 2 tables in this database don't need to be changed:

LineItems (OrderNo [text(8)], LineItem [number(integer, 0 decimal places)], ProdID [text(8)], Qty [number(integer, 0 decimals)]). This table contains a record for every product ordered in every order.  Note: PK is composite (dual).

ClientComm (ClientID [text(8)], Date [date/time (Format=Short Date)], Comment [memo]). This table contains one record for each contact made to each client.  Note: PK is composite (dual).

Part 2:  Implement Entity Integrity in all tables using Primary Keys

To implement entity integrity in a given table, highlight that table and then click the “Design” button, which should open the Design View screen.  Once in the table design screen, highlight the field(s) that will be your primary key(s), then right-click and select “Primary Key”.  A little key icon will appear next to the PK field(s) confirming your PK selection.  If your table contains a composite PK (i.e., PK is composed of more than one field--e.g., LineItems table), highlight both fields together when you select them as PK (Access won't let you select one field at a time, you have to select both fields together).  Selecting a field as the PK for a table  will do the following: (1) sort the records in that table by the PK field; (2) the table will not allow null (blank) values in the PK fields; and (3) the table will not allow records with duplicate PK’s.  Just try to enter a null or duplicate PK in the table itself and see what happens.

Please implement PK's as indicated in the table definitions above in each of the 6 tables.

Question: (don’t answer, just ensure that you know the answer): is this is sufficient to achieve entity integrity?

Part 3: Prepare the Relational Design for this database (Note: this desing is similar in principe to the data models or entity-relationship diagrams -- i.e., ERD's -- we did in class).

Use the MS Access “Relationships” feature to build the data model for this application.   If you are a non-technical business manager, knowing how to build a data model will help you communicate your business application requirements to the IT staff in your company in a way that they understand, which will ensure that your database application will be build as your application needs it. 

The relationships and Foreign Keys (FK’s) stated in the requirements analysis is conviniently listed below in the table below:

Table

Related Table

FK

Clients

ClientComm

ClientID

Clients

Orders

ClientID

Orders

LineItems

OrderNo

Products

LineItems

ProdID

ProdTypes

Products

ProdType

This part that follows may sound a bit confusing, so please pay close attention:

The table above shows the "Table", "Related Table" (this is MS Access' terminology) and the Foreign Key (FK). The table listed under "Related Table" is the table that contains the FK. This FK in the "Related Table" needs to be validated against the data in the respective table listed in the "Table" column. In other words, the FK field listed is a FK in the "Related Table", which means that the field is also is a PK in the "Table".

This sounds like a tongue twister, but it is quite simple. For example, the ClientID you enter in the ClientComm table must exist in the Clients table (i.e., you can only record comments for valid clients), the ClientID you enter in the Orders table must exist in the Clients table (i.e., only registered clients can place orders), and so on. This is what referential integrity is all about. In essence, the FK is the common field that links the "Related Table" to the "Table". 

To create your relational design that contins these relationships in MS Access, click on the "Tools" menu option on the main menu and select "Relationships" (or click on the Relationships icon -- looks like 3 little squares connected by lines).  You will see a blank screen.  Now, right-click anywhere on the screen and then select “Show Table”.  Highlight ALL the tables and then select "Add" and then close the table list.  You should now see all tables in the relationship design window.  You can move the tables around by dragging them with the mouse to any location you want.  I suggest that you arrange them from left to right in this order: ClientComm, Clients, Orders, LineItems, Products and ProdTypes (this is not required, but it will help you visualize your design). 

Once all your tables are displayed in the Relationships screen, select a FK field in a “Related Table” and drag it to its corresponding PK field in primary “Table”.  For example, for the first relationship in the table, select the ClientID field in the ClientComm table and drag it to the ClientID field in the Clients table. When you do this, the “Edit Relationships” dialog box pops up. When this happens, it is very important that you do 2 things: (1) that you have the correct tables on the “Table” and “Related Table” sides (BE SURE that they are NOT the other way around); and (2) check the box that says “Enforce Referential Integrity”.  Leave the "cascade" boxes unchecked for now and click OK (you will change this later).  You should see a relationship line now between the 2 tables.  You will also be able to see if the relationship is 1-to-1 or 1-to-many (e.g., one client can place many orders). 

Question (you don't need to answer this in the for this exercisek, but you may want to try to answer this question): how does MS Access know the cardinality of the relationship? That is, how does it know when to put the 1 and when to put the many?

Repeat these steps for all relationships listed in the table, which should yield these cardinalities:

Clients-ClientComm: A client can be contacted one or more times. Each contact in the ClientComm file must relate to a client.

Clients-Orders: A client can place many orders. Each order must belong to one client.

Orders-LineItems : An order can contain one or more line items. Each line item must relate to a single order.

Products-LineItems: A product can appear in one or more line items (from different orders).  Each line item must refer to a single product.

ProdTypes-Products: Each product must belong to one product type.  Each product type may be associated with many products. 

Sanity Check: To ensure that everything works well and to see how these rules work, click on the "Tables" button and then double-click on the "Clients" table. Once the table opens, go to the bottom row (which should be empty) and enter one new client (enter any client data you wish). Then, do the same thing in the Orders table and enter a new order for this client. Then enter a new product in the Products table.  Finally, enter one line item in the LineItems table. Then go back to the Clients table and see what happens when you try to change the ClientID for this test client.  The system shouldn't let you do it. Now try to delete that client from the Clients table.  The system shouldn’t let you either.  Then try to update the OrderId in the test order you entered.  The system shouldn't let you either. This is so because the Update Cascade and Delete Cascade boxes are un-checked so far. This means that at this time that both, the delete and update rules are respectively "Don't allow". You are about to change some of these rules. Note: you can delete these records you added as long as there are no linked records. That is, if you delete the line item you entered first, then you will be able to delete the new order and product you entered, and then you will be able to delete the new client you entered. That is, you can undo your entries in reverse order.

Note: At this point, your relationship diagram in MS Access should look similar to your data model in MS Visio. If not, one of the two diagrams is wrong. Another Note: while you can do the next steps in MS Visio and other data modeling software, what follows is not really part of the data modeling or entity-relationship diagram, but part of your relational design.

Part 4: Implement Update and Delete Rules

Update Rules: By default, once your tables are linked in a database design, MS Access doesn't let you update values in a primary key field if other tables refer to this primary key (via a foreign key). However, sometimes is convenient to allow changes to values in a primary key field (e.g., change a ClientID), but in order to maintain the referential integrity in the database, this change needs to be "cascaded" to all the tables (e.g., ClientComm and Orders) that relate to this table. Fortunately, this is really easy to do in MS Access. For example, double-click on the relationship line between the Clients and Orders tables. Then check the box that says "Cascade Update Related Fields" (leave the Cascade Delete box unchecked) and click OK.

Now do the same thing for all relationships in your design. That is, change all the "Update" rules from "Don't allow" to "Cascade". Now change a ClientID in the Clients table and observe how that client ID gets automatically changed in all related table. Then go back and restore the original ClientID.

Delete Rules: By default, once your tables are linked in a database design, MS Access doesn't let you delete rows in a table whose primary key is linked to another table. However, sometimes is convenient to allow this deletion, but in order to maintain the referential integrity in the database, these deletions need to be cascaded to all the tables that relate to this table. Fortunately, this is really easy to do in MS Access. However, this is a very dangerous thing to do if you are not careful!! You may end up deleting a lot of data. Often, it is better to not allow deletions, so we are only going to implement one delete rule to illustrate how it works. For example, if you delete an order, it would not make any sense to keep in the database the line items related to that order. While deleting orders is not a good idea, some systems some times allow you to delete erroneous orders or orders that have been cancelled, if they have not been processed yet. So, click in the Relationship line between the Orders and LineItems tables and check the box that says "Cascade Delete Related Records" and click OK. Please don't test this now. You can test it later when you enter practice records in your database below.

Part 5: Implement Lookup Pick Lists

Lookup pick lists are a very nice feature in MS Access that makes it really easy to enter valid data in FK fields by scrolling down a list of valid options to pick.  You need to implement lookup picklists for each of the foreign keys (FK) in the database, listed in the table above in Part 3. You only need to implement pick lists for the FK in the tables listed in the "Related Table" column, NOT the ones in the "Table" column. For example, when you enter a comment record for a client in the ClientComm table, you must enter a valid ClientID. This means that you either have to remember that client's ID, or you must have a way to display a scrollable list of valid client ID's. Fortunately, this is really easy to do in MS Access. For example, in the Tables view, select the ClientComm table and click on the “Design” icon.  Click on the ClientID field (i.e., ClientID is the FK in the ClientComm table which relates to the PK in the Clients table) and then click on the “Lookup” tab.  In the “Display Control” entry select “List Box”; in the “Row/Source Type” select “Table/Query”; in the “Row Source” scroll down the list and select “Clients” (the table you want to lookup); and in the “Column Bound” entry select 1 (this is the column number of the Clients table you want to display in the lookup scrollable list).  One of the nice things about this scrollable list is that it automatically updates itself when you enter new clients. Now, implement a lookup pick list for all the FK's in the database, listed in FK column in the table above in Part 3. Please note that lookup pick lists are ONLY implemented in the Related Table, where the FK resides.

Optional: pick lists are dynamic, they change as you enter data. However, you can also prepare a pick list with fixed options. For example, select the Orders table and then click on the “Design” button.  Then select the OrderStatus field and click on the lookup tab.  In the “Display Control” entry select “List Box”; in the “Row/Source Type” entry select “Value List”.  MS Access now expects you to type the fixed values that you will allow in this field.  For example, type in “Top Priority”;”Medium Priority”;”Low Priority” in the “Row Source” entry.  When you go back to enter data in this field, you will see the scrollable list with these options.

Part 6: Implementing Business Rules (i.e., data constraints)

Some times you want to impose constraints in the type of data that will be accepted in your database.  For example, if you run a wholesale operation, you may have a business rule that limits client orders to a minimum of 10 units.  You can impose business rules like this in your database by "validating" acceptable data.  You can do that by selecting the table in which you want to impose the business rule and then click on the “Design” button.  Then select the field for which you want to establish the rule.  Then click on the “General” tab and enter the respective “Validation Rule” (specified below).  Please implement the following business validation rules:

LineItems table: For business reasons, management decided that the minimum order quantity would be 10 units.  Therefore, the validation rule for the Qty field is: >= 10 (alternatively, > 9, will work too). 

Technical Note: when you implement this constraint and save your design changes, MS Access will give you a warning that says "Data integrity rules have been changed; existing data may not be valid for the new rules". All this means is that you may already have quantities below 10 in your existing data, which would violate this business rule. So MS Access wants to know if this is OK with you. Just select "Yes" and continue. If you had quantities below 10, it would leave that data alone and impose the business rule on new data only.

Products table: For marketing reasons, management decided that the maximum price they want to charge on any product is $2,000.  Therefore, the validation rule is for the Price field is: <= 2000. 

Part 7: Create Forms and Reports

Now, you will create a form and a report for the Orders table. As we discussed in class, forms are powerful database tools to provide different views into the data in a database. When you implement database in practice, you should never let data entry clerks or read-only viewers (e.g., managers) work directly in the data tables. With forms, you can design the layout of the screen, select which fields to display, and even block data entry to all or some fields for read-only viewers (e.g., managers). In case of related tables (e.g., Orders and LineItems), you can create forms with subforms so that you can view both tables in the same screen.

Reports work similar to forms, but they are optimal for printing detail or summary reports from your database tables. This is probably what you would use to prepare management reports that look professional and which contain useful aggregate information.

Creating forms and reports can be time consuming and in some cases difficult. Fortunately, MS Access has very nice and user-friendly form and report wizards that make creating forms and reports quite simple. Forms and reports created with the wizard have limitations, so if you need complex forms and reports you would need to use the form and report design views. However, chances are that the form and report wizards can address the majority of your needs. Furthermore, even if your forms and reports are complex, you can get started with the wizards and then finish them up with the design view. In this exercise, you will be using the simpler form and report wizards.

(a) Creating a Simple Form

You will first create a simple form to enter data into a single table (Products). In the next section you will create a form with a subform to enter data into two related tables.

In order to create a form for the Products table, first select the Forms button and then click the New button from the Database window. Once in the New Form dialog box, choose the Form Wizard option. Select the Products table in the Choose the Table or Query scrollable list at the bottom of the New Form window. Then, select OK.

After you press OK, the field selection window appears. The field selection dialog box has three work areas. The first area lets you choose multiple tables or queries, so you can create many types of forms. Since you already chose the Products table in the previous window, the default table here should be Table:Products. The list box "Available Fields" displays the possible fields, and below Selected Fields displays the selected fields. You can select the fields you want in your form by highlighting a field and then pressing ">". However, since you will be using all the fields in the table, you can select all of them by simply clicking ">>". Once the "Selected Fields" window displays all the fields you selected, press "Next ".

Now, you can choose from six different types of layouts. You can experiment with these later on, but for this assignment select the Columnar layout, then press "Next". You have many choices for the style of the form. When you select a style, the display on the left changes to illustrate the special effect used to create the look. Select any style you wish for this form and "Next". Then leave the title of your form as "Products", select the "Open the form" button and press "Finish". You should be done now. The form will open for you to use.

(b) Creating a more Complex Form that includes a Subform

You will now create a more complex form to enter data into a two tables (Orders and LineItems). These two tables MUST be related, so if you have not completed Part 3 of this exercise, this Form-Subform excersise won't work. Please ensure that you have your relationships well defined. The Orders and LineItems tables should be related with a 1 to many relationship (i.e., an order has many line items and every line item relates to a single order).

You will create the form and subform simultaneously. First select the Forms button and then click the New button from the Database window. Once in the New Form dialog box, choose the Form Wizard option. Select the Orders table in the Choose the Table or Query scrollable list at the bottom of the New Form window. Then, select OK. The table listed in the box should be "Table:Orders". Select all the fields from the Orders table by pressing ">>". Once the "Selected Fields" window displays all the Orders fields you selected, click on the Tables/Queries scrollable list and select the LineItems table. Once you do the box should display "Table: LineItems". Again, select all the LineItems fields by pressing ">>". Once the "Selected Fields" window displays all the Orders and LineItems fields press "Next".

The wizard now wants to know in which order you wish to view your records. Highlight "by Orders". Also, be sure that the "Forms with subform(s)" button is selected, then press "Next". Then you need to select the layout for the datasheet. Both, the Tabular and Datasheet layouts work really well for subforms. We will use the Datasheet layout for this exercise. So, select the "Datasheet" button and press "Next". Then select a style for this new form and press "Next". Leave the Form name as "Orders" and the Subform name as "LineItems Subform", select the "Open the form" button and press "Finish". You should be done now. The form will open for you to use.

(c) Creating a Report

You will create a quick report to print our all Orders, sorted by delivery due date, which will enable you to keep track of which orders need to be delivered sooner. Select the "Reports" button and press "New". In the New Report dialog box, select "Report Wizard". Select the "Orders" table in the Choose the Table or Query scrollable listat the bottom of the New Form window and click "OK".

This time we will select fields one by one because we want the delivery due date to appear in the first column. Select fields one by one by highlighting them individually and then pressing ">", in this order: DeliveryDueDate, OrderNo, ClientID, OrderDate, OrderDescription and OrderStatus. Then press "Next". The next dialog box enables you to choose which field(s) you want to use for a grouping. Groups are used to combine data with common values. However, in this assignment, we don't use groupings. You will notice that "ClientID" was automatically selected by the MS Access as a grouping field, but we don't want this. So, click "<" to remove it as a grouping field (it will move to the left box). Don't do anything else in this dialog box and just press "Next".

The next dialog box enables you to specify the sort order of the records of the database table. In this assignment, we would like to see which order needs to be immediately delivered. So, we would like to sort the customer orders data by delivery due date in ascending order. Select "DeliveryDueDate" in the scrollable list . And make sure “Ascending” order is selected for this first sorting key. Since more than one order may need to be delivered on the same day, we would like the orders for the same delivery day to be sorted by order number. So, elect "OrderNo" in the scrollable list and ensure that "Ascending” order is selected for the second sorting key. Then press "Next" .

Now you can choose the layout for the report. Select "Tabular" for the layout and select "Portrait" for the orientation. Check the box “Adjust the field width so all fields fit on a page” and click "Next".

Now select an appropriate report style and press "Next". This time, change the report title from "Orders" to "Order Delivery Dates". Then select "Preview the report" and press "Finish". Then, a report preview window will appear. Check if everything looks fine. Close the window and you are done.

NOTE: Unfortunately, MS Access Wizard is not so smart and it may chop off some of your column labels. You can fix this by selecting the report and then pressing "Design", which will open your report in Design View. This is not necessary for this exercise, but feel free to try it out if you wish.

ANOTHER NOTE ABOUT A BUG IN THE REPORT WIZARD:

When you use the Report Wizard and you include a table field that contains a pick list, MS Access creates a funny looking report. For example, in the Order Delivery Dates report you are creating for the exercise, the report displays a "list box" with all client ID's in each record, with the correct ID highlighted in black. This is not what you want, so if this happens, please correct it. What you really want is to display only the client ID for the client who place that order.

This anomaly can be easily corrected as follows:

Part 8: Populate the Database

Note: I suggest that you read the cool feature below before you complete this part. It will save you a LOT of time when entering your data. Also, I suggest that you enter some data directly into the tables and some data through the forms you created in Part 7. This will give you some experience with two ways of entering data.

You will now enter data in the database tables. One interesting aspect of this excercise is that you just spend a lot of time in parts 1-7 defining what kind of data you can enter in the database. This includes several constraints that you have imposed (field types, field sizes, PK and entity integrity, FK and referential integrity, business rules, etc.). Now you will experience how the tables behave when you enter data and MS Access enforces these constraints.

In order to enter data in one table, simply click on that table and go to the bottom row, which should be empty, and just type in the data.

Please enter 5 new clients in the Clients table (enter any clients you wish).  Then enter 3 product types in the ProdTypes table.  One of these product types must be "hw" with a product name of "Hardware Products" and the other 2 can be anything you wish.  Then go to the Products table and record "hw" as the ProdType for all existing products.  Then enter 5 more products in the Product table (of any valid product type you wish). Ensure that each of these products has a product type recorded (these product types, of course, will have to be valid as listed in the ProdType table. Then enter 2 new orders for each of the 5 new clients (a total of 10 orders).  Then enter line items for each of these orders. You need to have a few orders with 1 line item and a few with 2, 3 and 4 line items.

A cool feature in MS Acccess: after you create your relational design with all related tables linked to each other (Part 3), you will see little plus signs "+" appearing in some tables. The + sign means that this table has related tables in the design. The nice thing is that you can enter data in the related tables without opening them, by simply clicking on the + sign. When you click on the + sign, all the related records in the Related Table will be displayed. Now you can modify, delete and add records as needed.

 

Database SQL and QBE Queries

SECTION 3: DATABASE QUERIES

You will work on this section using MS Access. You will continute to work on the same DBLab.mdb file you used for Section 2 above.

Lab Practice for Before or After the Lab (if you need more practice):

If you want additional practice on SQL queries, before or after the lab, please download the following database to your G drive: SalarySurvey.mdb

The database tables can be printed from: SalarySurveyTables&Queries.pdf

I encourage you to first write your queries by hand and understand them before you type them in MS Access.  This query form gives you a template to prepare your queries by hand: QueryForm.doc

General Instructions for the Lab Section on Queries:

This section of the assignment is intended to be a hands-on tutorial on how to prepare queries to retrieve information from a database.  You will only use one Structured Query Language (SQL) command.  This is the SELECT command.  The SELECT command is the most useful SQL command to learn because it allows you to extract any information you could possibly need from a database.

Requirements:

 

Please prepare and run the following queries (notes: (1) be sure to save your queries; (2) be sure to verify that your queries are correct--you  have been given printed tables with very little data to facilitate this verification)

Note: see the Background information and Technical Notes below to help you prepare, save and run SQL queries.

Simple Queries (8 pts., 2 pts each) -- These queries must be prepared using "proper" SQL syntax (i.e., don't use the Design View):

1.     Let's retrieve client information for Alberto Espinosa.  List the client ID, client name, state and zip code for the client with an ID of "alberto" (note: you need to enclose "alberto" in quotations for queries like this that involve text strings).

2.   Let's get a list of highly priced products.  Display the product ID, product name and price for all products priced over $200.

3.   Let's find out which notes and comments have been entered for Alberto Espinosa.  Display the client ID and all the comments entered for a client with an ID of "alberto".

4.   Let's list all the orders that contain computers and the quantities ordered. Display the order ID, product ID and quantity of all line items containing a product ID of "comp".

Queries with Aggregates (8 pts., 2 pts each) -- These queries must be prepared using "proper" SQL syntax (i.e., don't use the Design View):

5.   Let's find out how many computers have been ordered.  Display the total quantity of computers ordered (i.e., ProdID = "comp").  Tip: you need to add up the quantities of computers ordered, so you will need to use the Sum function.

6.   Let's find out how many top priority orders have been placed.  Count and display the total number of "Top Priority" orders.  Tip: you simply need to count all orders that meet this criteria, so you need to use the Count(*) function  (the * is a wildcard in computing and it simply means "count all"). Also, be sure to enclose the "Top Priority" text string within quotations and to leave a space between Top and Priority.

7.   Let's find out what is the average price of all products offered.  Tip: you need to use the Avg function for this.

8.   Now, let's be more specific and find out the average price of all expensive products.  That is, find out what is the average price of all products that cost more than $200.

Queries with Aggregates and Grouping (6 pts., 2 pts. each) -- These queries must be prepared using "proper" SQL syntax (i.e., don't use the Design View):

9.   Let's find out how many units have been ordered for each product (like query 5, but for all products). Display a list of product ID's along with the total quantity of units ordered.

10. Let's find out how many top priority orders have been placed by each client (like query 6, but for all clients). Display a list of client ID's along with the total number of "Top Priority" orders they have ordered.

11. Let's count how many clients we have in every state. Display a list of states along with the number of clients that live in that state.

Complex Queries that Require Joining 2 Tables (9 pts., 3 pts. each) -- These queries must be prepared using "proper" SQL syntax (i.e., don't use the Design View; your query CANNOT contain the INNER JOIN clause):

12.    Let's find out how much money your computer orders are making. List all order numbers, line items, product names, quantities ordered, unit price (Price), and total price (Qty*Price) for products with a product ID of  “comp” (enclose comp in quotations here too).  Note: you can use full expressions like Qty*Price in an SQL query, not just single columns.  When you do this, MS Access will assign an ugly name for the corresponding column (e.g., expr1). However, you can give this column a more meaningful name using the "AS" clause (e.g.,  Qty*Price AS TotalPrice).   Tip: you need to join two tables for this query (Products and LineItems) because part of the information is available the Products table only (e.g., Price) and other information is available in the LineItems table only (e.g., Qty).

13. L et's run a similar query to item 3 above, but this time let's also list the Client Name (i.e., if you need to use this query for a management report you need to include client names, not just client IDs).  So, display the client ID, client name and comments entered for the client with an ID = "alberto".  Tip: the difference between this query and item 3 above is that you need to join the ClientComm table with the Clients table (so that you can get the client name from the Clients table).

14. Finally, since we keep track of orders and the line items for these orders in separate tables, lets build a query that gives us a complete view of all orders and their respective line items.  List all orders, client ID, line item numbers, product ID and quantities ordered in all line items in all orders.  Tip: you need to join the Orders and the LineItems tables.

Complex Queries that Require Joining 2 Tables (9 pts., 3 pts. each) -- These queries must be prepared using QBE (i.e., use the Design View; the SQL equivalent of your query MUST contain the INNER JOIN clause):

15.  Prepare Query #12 using QBE (Design View) and joining the tables visually.

16. Prepare Query #13 using QBE (Design View) and joining the tables visually.

17. Prepare Query #14 using QBE (Design View) and joining the tables visually.

The challenge. Here are a couple of examples of queries that require joining more than 2 tables:

18. Let's find out how much money your clients are spending. List all client ID's, client names, order numbers, order description, line items, product ID's, quantities ordered, unit price (Price), and total amount (Qty*Price). Note: you can use full expressions like Qty*Price in an SQL query.  You can give this column a more meaningful name using the "AS" keyword (e.g.,  Qty*Price AS TotalPurchases--no spaces). Sort your query results by client ID and by order number.

19. Let's find out how much money your computer orders are making. List all product ID's, product names, order dates, order numbers, line items, quantities ordered, unit price (Price), and total amount (Qty*Price). Note: you can use full expressions like Qty*Price in an SQL query.  You can give this column a more meaningful name using the "AS" keyword (e.g.,  Qty*Price AS TotalSold--no spaces). Sore your query results by Product ID and by order date.

20. The superchallenge--aggregate with grouping and 3-table join: Let's find out how much money your various product types are making. List all product type names and the total quantities (Sum of Qty) sold for each of these types. Hint: even though you are only listing two columns, the product type names and quantity fields can only be connected through the Products table (see your relational design). Therefore, you need to join three tables, even though you will not be displaying anything from the products table.

Technical Notes to Help You Your Queries

Background on queries:

All DBMSs have one or more query features to help you retrieve data from your databases.  Some of these query features are proprietary (only available in the particular DBMSs) and some of them are standard (available in most DBMSs).  The most popular standard query facilities included in DBMSs are Query by Example (QBE) and Structured Query Language (SQL).  A typical QBE interface presents the user with an empty table in which one can list the database fields (i.e., columns) wanted in the query results.  In addition, one can enter in each column a criteria of which records (i.e., rows) to include (or filter out) in the query in the form of an example (e.g., enter "PA" in the "state" column to only list records for the state of Pennsylvania).  You have probably used something similar to QBE many times via web forms while doing electronic searches in the library, in you IT Job Search assignment, or when shopping for a computer at the Dell site.

SQL does the same thing as QBE, except that the query is formulated (i.e., typed) using a standard query language.  Most QBE queries can be translated into an SQL query and viceversa.  In fact, all MS Access QBE queries are automatically converted to SQL queries internally before executing.  While writing SQL queries can be more cumbersome than QBE, it is a very useful thing to know because SQL queries are the same regardles of DBMS (e.g. MS Access, Oracle, etc.).  Once you learn SQL you can use it with any DBMS.  For example, if you have invested many hours building queries for an MS Access database, and your company decides to change its database platform to Oracle, you can simply port and run the same queries in Oracle (because they are "standard"). Furthermore, many programming and web scripting languages allow you to embed SQL queries in programs or web pages so that you can retrieve data from a database to display in dynamically generated web pages (we will talk abou this in class later on).

Queries in MS Access:

Once you start MS Access and retrieve the database file for this assignment, click on the “Queries” button.  Queries in MS Access are saved with a name for future use.  This is very convenient because often times you need to run the same query over and over again.  To create a new query click on the “New” button and then select “Design View”.  This will open MS Access' version of QBE (Access calls it "Design View").  You will see how easy it is to move from/to Design View (i.e., QBE) to/from the SQL view.  In fact, an easy way to start building your SQL queries is to first use the Design View and then switch to SQL view. Before you start with the assignment you may want to test a few QBE queries just to see how they work.

Entering, Saving and Running SQL Queries

To type a query, first click on the Queries button in the Objects pane (left pane). Once you are in the Queries view, you should see a blank page with two entries that say "Create query in design view" and "Crete query by using wizard".  This screen will show your queries as you create them.  Right now there should be no queries. Unfortunately, and strangely, MS Access doesn't have a direct way to bring you into the SQL query view, so we have to use a convoluted way through the Design View (but you will get used to it quickly).

Now open the Design View, either by clicking on the Design button (with the little square ruler) or by double clicking on "Create query in design view". When you see a dialog box called "Show Table", click Close. Now you will see the Design View (or QBE). You will notice a button on the left top corner labeled "SQL". Click on that button and the SQL editing box will open.  If you want to go to Design view click on the little squared ruler. You can go back and forth.  Now click on the little scrollable icon next to SQL or next the the Design icon.  You will notice that you can switch to a number of views.  We are only interested in the SQL, Design and Datasheet views at this point.

To run (execute) a query, you can just double click on it from the Queries view. If you are editing a query and you want to test it, simply click on the exclamation mark ! icon or select Query from the main menu and then Run. Alternatively, click on the little scrollable icon on the top left corner next to SQL and select Datasheet view.

Once you are happy with your query, click on the X mark on the top right corner of the query editing box.  MS Access will prompt you for a query name.  Enter the query name and click OK to save it.

(S) SQL Simple Query Syntax (when you can retrieve all columns from a single table)

Note: the keywords or conditions in brackets [ ] are optional

SELECT [*] [DISTICT] [TOP n] [BOTTOM n] field1, field2, field formula [AS name], etc.
     FROM tablename
     [WHERE condition1]
          [AND condition 2, etc.]
      [ORDER BY field1, field2 [DESC][ASC]]

The (mandatory) SELECT line has a list of fields or columns to display in the results query (e.g., Qty, Price) but it can also contain formulas combining columns (e.g. Qty * Price).

The (mandatory) FROM line has the name of the table that contains the SELECT fields you want to retrieve in your query

The (optional) WHERE line contains the condition that allows  you to filter which records get displayed (e.g., WHERE Salary>100000).  If you have more than condition you can use the AND keyword to add more conditions (e.g., WHERE Salary>100000 AND State="NY").

The (optional) ORDER BY line contains the fields you want to use for sorting. By default, the query will sort in ascending order, but you can include the DESC keyword if you want it sorted in descending order (e.g. SELECT EmployeeName, Salary FROM Employees ORDER BY Salary DESC). You can use more than one field to sort (e.g. SELECT CompanyID, EmployeeName FROM Employees ORDER BY CompanyID, EmployeeName -- will list employees sorted by their company ID's and by their name, within each company ID)

(A) Syntax for SQL Queries with Aggregate Functions

An aggregate function sums, averages, counts, etc. a particular field over the query results.  So, if the command SELECT Salary FROM Employees lists all the salaries for all employees, then the command SELECT Sum(Salary) FROM Employees will give you a grand total of all those salaries. 

SELECT AggregateFunction1(field1), AggregateFunction2(field2)
     FROM tablename
     [WHERE condition1]
          [AND condition 2, etc.]

The aggregate functions you can use are: Sum( ), Avg( ), Count( ), Min( ), Max( ), StDev( ) and Var( )

VERY IMPORTANT: a query with aggregate functions produces a result with ONLY one row that contains the sum, average, etc. Consequently, when you use aggregate functions, you CANNOT include any other fields in the SELECT line, only aggregate functions.  For example, the command SELECT EmployeeName, Sum(Salary) FROM Employees will give you an error in MS Access because Sum(Salary) is trying to give you a single line with the sum of all salaries, while EmployeeName is trying to list every employee.  It doesn't make sense and it just won't work.

In sum, 2 RULES OF THUMB when you use aggregate functions: (1) your query results will have ONE row only; (2) you should never list individual fields in the SELECT line, only aggregate functions.  And exception to this second rule is when you use the keyword GROUP BY, but we will not cover it in this course.

(AG) Syntax for SQL Queries with Aggregate Functions with Grouping

When you want to aggregate numeric fields (e.g., Qty), but grouped by another (e.g., ProdID), you need to use the GROUP BY clause. IMPORTANT: when you do this, the only fields you can include in the SELECT line are the fields you are aggregating (e.g., Avg(Qty), Max(Price)) and the fields you are using to group (e.g., ProdID). If you include any other field in the SELECT line, your query will be incorrect and MS Access will give you an error as a result. Syntax:

SELECT field1, AggregateFunction(field2)
     FROM tablename
     [WHERE condition1]
          [AND condition 2, etc.]
     GROUP BY field1

(C) SQL Complex Query Syntax (when the columns you need to retrieve are in more than one table)

SELECT [*] [DISTICT] [TOP n] [BOTTOM n] field1, field2, field formula [AS name], etc.
     FROM tablename1, tablename2, etc.
     WHERE tablename1.commonfield = tablename2.commonfield  --- this is the JOIN condition
          [AND otherconditions, etc.]
      [ORDER BY field1, field2 [DESC][ASC]]

This works just like the simple queries, except that:

Experimenting with the Design View (or Query by Example -- QBE)

You can experiment with the Design View (QBE) interface as you wish, but I suggest that you start with a simple query involving only one table (simple query ).  For example, try selecting the LineItems table.  Then double click each of the four fields to bring them into the QBE table.  Then go to the “Criteria” row for the Qty column and type >=15.  Then select Query, Run from the menu or click on the ! symbol to run your query.  What do you think you will get as a result? You guessed right!! You will get all line items with a quantity greater than or equal to 15.  Now click on the design icon on the top left corner of the screen to go back to the QBE screen.  You will notice a small scrollable icon next to the Design button.  Click on that icon and select SQL view.  MS Access will now display and SQL command editing screen with the equivalent SQL commands for the QBE query you just constructed.  This is a convenient way to build queries.  You can go back and forth between SQL and QBE as you wish. 

I suggest that you experiment with QBE for a while, but strongly recommend that you prepare the queries for this assignment by typing your SQL commands manually.  This will help you practice for the quiz.  Note: some versions of Access add a whole bunch of parentheses, brackets and dots to your SQL query.  These are not part of the standard SQL, but they are harmless.  You can leave them or delete them as you wish.

A Note on Complex Queries using QBE

For complex queries involving two tables or more, MS Access Design View uses a JOIN clause.  We did not cover the JOIN clause in class but it works similar to the join condition used in the WHERE clause of the SELECT statement.  Both commands are correct and both yield identical results .  If you like this syntax better than the one we illustrated in class it is OK to use.  You can join and query more than one table in Design View by adding to the query screen all the tables you need and then connecting them through their respective joining fields.  For example, try adding the Orders table to your prior practice query (right-click and select Show Table and then select the Orders table).  MS Access some times automatically joins the two tables via the common field OrderID.  If it doesn't, you can join the tables by clicking on the OrderID field in one table and dragging it to the OrderID field in the other table. Now you can include fields from the Orders table in your query.  Pick a couple of fields like ClientID and OrderDate and run the query.  Then switch to the SQL View and see what kind of syntax MS Access created for you.  It should say something like:

SELECT LineItems.OrderNo, LineItems.LineItem, LineItems.ProdID, 
               LineItems.Qty, Orders.ClientID, Orders.OrderDate
    FROM Orders INNER JOIN LineItems 
             ON Orders.OrderNo = LineItems.OrderNo 
    WHERE LineItems.Qty >= 15;

The following command we discussed in class is equivalent and will give you identical results (it is a little simpler too):

SELECT LineItems.OrderNo, LineItem, ProdID, Qty, ClientID, OrderDate
     FROM Orders, LineItems
     WHERE Orders.OrderNo = LineItems.OrderNo
            AND Qty>=15;

Note the similarities and differences: (1) MS Access requires a semi-colon (;) at the end of your query, so don't forget to include it;  (2) QBE sometimes adds a lot of parentheses ( ) and brackets [ ], which are often unnecessary and can be deleted but don't hurt if keep them; (3) QBE pre-fixes each field with a table name and a period (e.g., Orders.OrderNo), but you only need to do this when more than one table (e.g., Orders, LineItems) contain the same field name (e.g., OrderNo).  If the field is only contained in one table (e.g., LineItem), you don't need to include the table prefix, but it doesn't hurt if you do; (4) QBE joins the tables with the INNER JOIN clause and it specifies the join condition with the ON clause, whereas the standard SQL command we learned in class joins the tables with the FROM clause and specifies the join condition with the WHERE clause.  Again, both queries yield identical results.