OVERVIEW

This week's material takes the data modeling ideas from lecture 2/ tutorial 3 and examines how they are put to use in

relational databases. We start with a consideration of some of the key ideas behind the relational database model and the perceived advantages that it offers (from lecture 2). Following this introduction, the components of the relational

database are introduced (including tables, attributes, keys, and relationships), with these linked back to the classes and UML diagrams that we worked with in last week's material.

With the above concepts in hand we then explore how data is extracted from the various data tables and combined to

produce useful information for organisation decision making. This process of producing reports is carried out through the  use of Structured Query Language ('SQL'). We will spend some time having a look at how SQL works, emphasising the    commands, syntax, and the critical role for the correct establishment of the database tables, attributes, primary keys, and foreign keys.

Finally, we briefly consider the Enterprise System as an example of a large-scale relational model and examine how it benefits organisations and how its use has been enhanced through the development of cloud computing.

REFERENCES

.       Richardson et al (2021) – Chapter 4

区 OBJECTIVES

By the end of this tutorial you should be able to:

.       Explain the connection between the UML class diagram and an Access relational database

.       Be able to apply common SQL commands to generate queries

.       Describe the operation of different SQL commands

.       Set up primary keys in an Access database

.       Establish relationships between tables in an Access database

.       Create and run SQL queries in an Access database

KEY CONCEPTS

Chapter 3

Association / relationship, attributes, business rule, cardinalities / multiplicities, class, class diagram, data model, entities, foreign key, primary key, structure model

Chapter 4

Data dictionary, database, database management system, entity integrity rule, query, referential integrity rule, relational data model, Structured Query Language (SQL)

SQL COMMANDS

SELECT

FROM

WHERE

AS

.       ORDER BY

.       GROUP BY

.       IN

.      ASC

DSC

BETWEEN

COUNT

SUM

MAX

MIN

AVG

AND

o REVISION OF KEY CONCEPTS

1-1  Prepare a summary table that describes the operation of each of the following SQL commands.

.       SELECT

.       FROM

.       WHERE

.      AS

.       ORDER BY

.       GROUP BY

1-2  Explain the difference between the ORDER BY and GROUP BY SQL commands.

Which of the commands would be used if a business wanted to use the SALES table shown in Table 1 to generate a report that listed total sales for each customer?

SALES

Table 1: Sales table

1-3 When creating a database for a business your friend gets shown the pop-up window in Figure 1. They are unsure what referential integrity is and whether or not it should be enforced in their database.

Figure 1: Referential integrity in Access

Using the example of the customer and sales tables in Figure 1:

.        Explain what is meant by the term referential integrity.

.       Why is referential integrity important for the operation of relational databases?

.        How does referential integrity relate to the information qualities we discussed in the first week?

1-4 What is the relationship between the UML Class diagram and the construction of an Access database? Based on this, explain why structure models are important for accountants and auditors.

a STEVE’S STYLIN’ SUNGLASSES

In the chapter 4 material we looked at Steve’s Stylin’ Sunglasses. The business had a UML Class diagram as shown in Figure 2.

Figure 2: UML Class Diagram for Steve's Stylin' Sunglasses

A.    Identify the changes in class relationships and multiplicities that would occur if the business made the following adjustments to its business rules:

i.   The business starts to sell mass-produced brand-name sunglasses instead of custom fashion sunglasses

ii.  The business decides it would like to know their market better, so they create a web page that allows

people to create customer web profiles, with these providing personalised information about product ranges and upcoming sales. Staff can also view the customer profile when they are dealing with the customer in

the store and discussing different types of products the customer may like. This is seen as a way of attracting new customers and generating new sales.

B.    Assume that both of the above proposed changes occur.

i.           Redraw the Class Diagram for the new database.

ii.          How many tables would be required to implement the new database?

iii.         Why is the number of database tables going to be different from the original design?

QUERY GENERATION WITH SQL

Complete Problems 1-3 on page 1141

CASH

Table 2: CASH table

3-1 – Based on the Cash table above (see Table 2), what would be the output from the following SQL query? SELECT Account#, Balance

FROM Cash

WHERE Balance<50000;

3-2 – Based on the Cash table (see Table 2), what output would be generated by the following query?

SELECT Account#, Balance

FROM Cash

WHERE Bank = ‘Boston5’

ORDER BY Balance DESC;

3-3 – Write an SQL query that would create a list of checking accounts. The list should include the Account#, Type, Bank, and Balance.

3-4 Specify the SQL query that would be needed to create the output shown in Table 3:

Table 3: Query output for 3-4

MICROSOFT ACCESS

This task is based on Problems 8 and 9 in the Richardson et al (2021) text. It is designed to introduce you to Microsoft Access. By the end of this task you should be able to:

.       Open an Access file

.       View the contents of a data table

.       Specify primary keys in tables

.       Create relationships between tables

.       Create single and multiple table queries using the Query Design function

.       Construct calculations that appear in query outputs

.       Group data in query outputs

.       Apply summary functions (sum, average…) in queries

NOTE: If you are using a Mac or any other non-Windows based computer then you will need to use Microsft   Access through the Monash Virtual Environment (MoVE). Access is available for use in the PC-based labs on campus.

GETTING FAMILIAR WITH THE DATABASE

i.             Download the database file Access_Practice_Student from Moodle

ii.             Complete Table 4, which summarises the tables in the database

.     Column 1 – the name of the table

.     Column 2 – the type of data in the table (e.g. customer details)

.     Column 3 – the Primary Key for the table

.     Column 4 – the other tables in the database that the table is related to (you will need to think in terms of primary-foreign key connections)

Table 4: Access database summary

BUILDING RELATIONSHIPS

Based on your completed analysis in Table 4, establish the relationships in Access that will allow for the connections between the different tables.

Remember, to build relationships you need to go to the DATABASE TOOLS menu tab and select RELATIONSHIPS

Add the tables by clicking on Show Table

Build the connection between tables by dragging a primary key to the related foreign key

Ensure referential integrity is turned on for the relationships you create

QUERY BUILDING

As we progress through the queries for this week, it will be helpful to think about how each query can build on what has already been created. For example, the total dollar value of each invoice builds off the AMT we calculated for each

invoice line item. Thinking of the query you want to create and how it relates to already created queries can introduce more logic to query development and make the query creation process more efficient.

Figure 3 illustrates these relationships for the queries we are building in this week’s tutorial.

Figure 3: Relationships between queries

How to build queries:

Select ‘QUERY DESIGN’ from the ‘CREATE’ menu

Select the tables you want to include in the query

Add the fields you want to include

Add any totals or groupings

Run the query

i.           Calculating Line Item Total (AMT) in the joining table

We will use the Sales Items table to create a query that includes InvoiceID, InventoryID, Quantity, UnitPrice Add the Sales Item Table to the Query Design

Double click on the attributes in the SalesItems table that you want to include in the query

Click on the ‘Run’ button in the ‘Design’ menu bar

Your query result should look like the one below.

Save the query as Line_Items_StudentID

Return to Design View by clicking the View button

Adding Calculations to a Query

You will now create a new field in your Line_Items query and it will calculate the Quantity x UnitPrice for each instance in the SalesItems table.

To create the new field we need to know what it will be called and how it will be calculated.

We will call the new field Amt (it is the amount of each line item).

Amt is calculated by multiplying two of the existing fields together  (UnitPrice and Quantity).

This instruction can be shown in Access by going to the first empty column in the Query Design section and entering the following:

Field Name:[Field 1]*[Field 2]

Where

Field Name – the name of the field being created

[Field 1] – the name of the first existing field that is part of the calculation

[Field 2] – the name of the second existing field that is part of the calculation

Once you have entered the new field details click on the RUN button to execute the query.

If the query has been correctly constructed, you should end up with the following:

Formatting the AMT item to CURRENCY format

The values in the Amt column can be converted to currency amounts.

Click in the field box for the Amt calculation you just created.

From the menu bar click on ‘Property Sheet’

The Property Sheet contains different options for the selected field. This includes a list of ‘Format’ choices that include currency.

The Amt should now be formatted to two decimal places

Save the query as Line_Items_Amt_StudentID

ii.          Calculating the total dollar value of each sale (Invoice Amount)

Create a query that calculates the total dollar value of each sale.

ou need to include InvoiceID, Invoice date, CustomerID, EmployeeID (from Sales) and create a new field called

This query is taking all the line items you calculated in the previous step and grouping them by InvoiceID, providing a total of all items on the invoice.

HINT: To get the group By and Sum functions click on the TOTALS button in the toolbar.

This adds a ‘Total’ row to the design space, allowing you to specify which fields should be used for grouping and which    ones should be used for other actions. Since we want to get the total of all line item amounts for each invoice we need to make sure that the ‘Total:’ option for the Amt field is set to Sum.

HINT: When building a query you can include fields from existing Tables and from Queries that have already been

created. If you need to use a field from a pre-existing query select the ‘Queries’ tab in the ‘Show Table’ pop-up window and then select the query that has the field you need.

You should end up with a query result as shown below. Notice how each InvoiceID only appears once (because we GROUPED BY InvoiceID) and the Invoice Amount is the SUM of all line items included in the invoice.

Save the query as Invoice_Total_StudentID

iii.         Calculating the total dollar value of sales for each product (Total Product Sales)

Create a query that calculates the total sales for each inventory item. The query result should include InventoryID, Name, and Total Sales for each item (to be called ‘Total Product Sales’ .

The logic of the process for building this query is similar with the previous example of total sales amount for a transaction, however this time we will not be grouping by InvoiceID!

What should be used as the GROUP BY item?

Save the query as Product_Sales_StudentID

iv.         Calculating the total dollar value of all sales

Create a query that calculates Total Sales.

Save the query as Total_Sales_StudentID

v.          Allocating a sale to a month classification

Calculate the month (number and name) in which each sale occurred. Your query should include InvoiceID and InvoiceDate from the SALES table. Call the new fields SaleMonth and SaleMonthName.

Hint: The Expression Builder is another way of creating formulas and algorithms for new fields. To access the formula

builder, (1) select the field you want to create/apply a formula to (in this case we will create a new field) and then (2) click on ‘Builder’ in the Design menu.

The expression builder provides a space where you can create calculations that will occur for a specified field. You can select from a range of functions and also select fields from tables and queries to be included in the calculation.

SALE MONTH will be calculated using the MONTH function in expression builder – this takes a date that has been

recorded and returns the month number in the date. The SALE MONTH NAME will be calculated using the MonthName function in the formula builder.

Save the query as Invoice_Month_StudentID

vi.         Calculating the total dollar value of sales for each customer

Calculate the total sales for each customer.

Your query should include Customer ID, Company Name&nbs