Transcript Query - Wiley

Introduction to Business
Information Systems
by
James Norrie,
Mark Huber, Craig Piercy, and Patrick McKeown
Tech Guide D
The Details of SQL, Logical
Modelling, and XML
What We Will Cover:
 Using SQL to Query Relational Databases
 Using Logical Modelling to Create a Relational Database
 Querying Multitable Databases
 Using XML for Data Transfer
Copyright 2010 John Wiley & Sons Canada Ltd.
Using SQL to Query Relational
Databases



The primary function of a database is to let
knowledge-enabled professionals obtain
information in a usable form using queries
(questions)
To query a relational database, many people use
Structured Query Language (SQL), which is a
computer language for manipulating data in a
relational database
SQL queries also enable database users to add
new records, or change or delete records
Copyright 2010 John Wiley & Sons Canada Ltd.
Important Terms



Primary key: a field that holds a unique value
for each record in a table
Foreign key: the primary key from another
table that is in the current table; it relates the
two tables
Entity: another name for a table in a relational
database
Copyright 2010 John Wiley & Sons Canada Ltd.
Important Terms



Row: another name for a record in a
relational database table
Column: another name for a field in a
relational database table
Query: a statement written in SQL that
requests matching records, changes
matching records, deletes matching records
or adds new records
Copyright 2010 John Wiley & Sons Canada Ltd.
Querying a Single Table Database

To query a single table database, we use an
SQL statement of the form:
SELECT fields FROM tables WHERE fields match
query condition




The SELECT keyword designates which fields
to display as a result of the query
The FROM keyword designates which tables
to search
The WHERE keyword specifies the search
criteria to use in finding records
Note that we use uppercase for keywords to
make them stand out, but otherwise case is
not important when using SQL
Copyright 2010 John Wiley & Sons Canada Ltd.
Other Query Keywords

In addition to the SELECT keyword, there are
a number of other keywords:





CREATE a table
INSERT new records in a table
DELETE records from a table
UPDATE one or more records in a table
We can also search for records that are like a
specific condition, or compute sums,
averages, and so on for all records that
match some criteria
Copyright 2010 John Wiley & Sons Canada Ltd.
Using SQL to Display
Specific Information

To display all of the table, that is, all fields for
all of the records:
SELECT * FROM TableName

To display only a subset of the fields:
SELECT FieldName1, FieldName2, … FROM
TableName

To display these records in some order, other
than in increasing order of the primary key,
we can add the Order By clause:
SELECT FieldName1, FieldName2, … FROM
TableName ORDER BY FieldName
Copyright 2010 John Wiley & Sons Canada Ltd.
Displaying Selected Fields for
Matching Records

To display selected fields for records that
match some condition we use the WHERE
keyword followed by some query condition
involving one of six comparison operators
plus a fieldname and a value:






equals (=)
greater than (>)
less than (<)
greater than or equal to (>=)
less than or equal to (<=)
not equal to (<>)
Copyright 2010 John Wiley & Sons Canada Ltd.
Using the LIKE Operator
 To look for an “almost match” or to look for
information about a group of products, the
LIKE operator is appropriate
 The LIKE operator uses the wildcard
character (either an asterisk [*] in Microsoft
Access or the percent sign [%] in other
database management systems) as a
replacement for unknown or non-existing
characters
 The general form of this type of query is
SELECT FieldName1,Fieldname2,… FROM
TableName WHERE FieldName LIKE ‘*value*’
Copyright 2010 John Wiley & Sons Canada Ltd.
Inserting Records

To insert a record into a table, you would use
an SQL statement of the form:
INSERT INTO TableName Values (value1,
value2, …)

Note that the values must be entered in the
exact order as the fields in the record,
separated by commas. If there are null or
missing values, the corresponding comma
must be entered
Copyright 2010 John Wiley & Sons Canada Ltd.
Deleting or Changing Records

To delete an existing record from a database
table, you would use an SQL statement of the
form:
DELETE FROM TableName WHERE FieldName
= value

To change values in a row of a database, you
can use the UPDATE and SET keywords in
the form:
UPDATE TableName SET FieldName1 = value
WHERE FieldName2 = value
Copyright 2010 John Wiley & Sons Canada Ltd.
Using Aggregate Functions in SQL



SQL can be used to compute certain values in the
table using five different aggregate functions—
COUNT, AVG, SUM, MIN, and MAX
In each case, you must use a dummy field name
for the result of the computation
The form for the AVG, SUM, MAX, and MIN
functions is:
SELECT AVG(fieldname) AS DummyName FROM
TableName WHERE Query condition

The COUNT function uses a different form:
SELECT COUNT(*) AS DummyName FROM
TableName WHERE Query condition
Copyright 2010 John Wiley & Sons Canada Ltd.
Using Logical Modelling to Create
a Relational Database




Redundancy can occur, which can result in the
database table taking up unneeded storage
space as well as causing problems when trying
to insert, delete, or update records
These problems, typically referred to as
anomalies, can harm the integrity of the
database records
To create relational databases that avoid these
problems with redundancy, logical modelling is
used
The first step in logical modelling is to create an
entity-relationship diagram (ERD) that allow
us to focus on the “big picture,” that is, the
entities and the relationships
Copyright 2010 John Wiley & Sons Canada Ltd.
Entity-Relationship Diagramming
The symbols for an ERD (where 1:1 means a
one-to-one relationship and 1:M means a oneto-many relationship):
Copyright 2010 John Wiley & Sons Canada Ltd.
Example ERDs
 A one-to-many
relationship with
one vendor selling
many products
 A many-to-many
relationship with no
primary key–foreign
key relationship;
many customers
buy many products
Copyright 2010 John Wiley & Sons Canada Ltd.
Using a Relational
Entity for M:M Relationships
We convert an M:M
relationship into two 1:M
relationships by adding
the PURCHASE
relational entity between
the CUSTOMER and
the PRODUCT entities.
This enables us to have
the required primary
and foreign keys to
carry out queries
Copyright 2010 John Wiley & Sons Canada Ltd.
Relational Data Models


The next step after
creating an ERD is to
convert it into a
corresponding relational
data model (the
relational data model
for the 1:M ERD is
shown to the right)
Note that the relational
diamond is replaced by
a “crow’s foot”
representing the “many”
end of the relationship
Copyright 2010 John Wiley & Sons Canada Ltd.
Many-to-Many Relational Models
To model the many-to-many ERD, we add
the PURCHASE entity and then use it to link
the CUSTOMER and PRODUCT entities
Copyright 2010 John Wiley & Sons Canada Ltd.
Complete Relational Data Model
We can combine the two models to link all four
entities into one data model
Copyright 2010 John Wiley & Sons Canada Ltd.
Querying Multitable Databases


The most common operation in querying
multitable databases is the JOIN operation in
which we create one table from two
For example, to query the Product-Vendor data
model shown earlier, the JOIN query would be:
SELECT * FROM Product, Vendor WHERE
Product.VendorID = Vendor.VendorID

This would result in a table containing all fields
from both the PRODUCT and VENDOR tables
where there is a match between the primary key
in the VENDOR table and the foreign key in the
PRODUCT table
Copyright 2010 John Wiley & Sons Canada Ltd.
More Multitable Queries
 To list products, vendor name, and item cost in
alphabetical order of item name, the query is:
SELECT ItemName, VendorName, ItemCost FROM
Product, Vendor WHERE Product.VendorID =
Vendor.VendorID ORDER BY ItemName
 To output the item name, vendor name, item cost,
discount, and the net cost, the query is:
SELECT ItemName, VendorName, ItemCost,
Discount, ItemCost*(1-Discount) as NetCost FROM
Product, Vendor WHERE Product.VendorID =
Vendor.VendorID ORDER BY ItemName
Copyright 2010 John Wiley & Sons Canada Ltd.
Creating Views




In most database systems, queries can be
saved and reused
A saved query is often referred to as a view
A query can be saved as a view and then
used again by itself or as part of another
query
One reason to save a query as a view is for
security. By creating a view only showing
fields that we want others to see, we can
protect confidential information
Copyright 2010 John Wiley & Sons Canada Ltd.
Using XML




Most companies today require the ability to
share data and resources over the Web
For many years companies have been using
a system known as electronic data
interchange (EDI) that is expensive and
useful only to very large companies
With the rapid growth of the Internet,
organizations have turned to the use of XML
as a way of carrying out the same processes
An XML file can be processed purely as data
by a program, it can be stored with similar
data on another computer, or, like an HTML
file, it can be displayed
Copyright 2010 John Wiley & Sons Canada Ltd.
Comparing XML to HTML
Feature
XML
HTML
Type of text
Structured with
meaning defined
Formatted with
meaning inferred
Definition of
structure
User defined
Predetermined
Retrieval
Context sensitive
Limited
Searchability Searchable by text or
meaning
Searchable only
by text or format
Hypertext
linkage
Limited
Extensive
Copyright 2010 John Wiley & Sons Canada Ltd.
Setting Up an XML Document
 The first step to creating an XML file is to
decide which tags to use to describe the data
that is being transferred over the Internet
 As with HTML, the tags are enclosed in
greater-than and less-than symbols (< >).
 However, unlike HTML, each beginning tag
must have an ending tag. For example, if
using a <PARTID> tag to describe the part
ID, then there must be a matching <PARTID>
closing tag
Copyright 2010 John Wiley & Sons Canada Ltd.
Setting Up an XML Document
 Tags must come immediately before and
after the data item, to ensure that there is no
ambiguity or inconsistency about the
description
 It is a good idea to provide a formal definition
of all the data elements in the XML file. This
can be done in one of two ways—using the
document type definition method or the
XML schema method
 Both can be incorporated into the XML file or
created as separate files with DTD or XSD
extensions, respectively
Copyright 2010 John Wiley & Sons Canada Ltd.
XML Schema (XSD) File
Copyright 2010 John Wiley & Sons Canada Ltd.
Example XML File Corresponding
to the Previous Schema
Copyright 2010 John Wiley & Sons Canada Ltd.
Using Stylesheet Files
 To display an XML file in a more readable form
on a Web browser, we need to use an XML
stylesheet (XSL) file
 This file uses a combination of HTML and XML
tags and is itself an XML file
Copyright 2010 John Wiley & Sons Canada Ltd.
Combining the Stylesheet and
XML Files
Copyright 2010 John Wiley & Sons Canada Ltd.
Copyright
Copyright © 2010 John Wiley & Sons Canada, Ltd. All
rights reserved. Reproduction or translation of this work
beyond that permitted by Access Copyright (The Canadian
Copyright Licensing Agency) is unlawful. Requests for
further information should be addressed to the Permissions
Department, John Wiley & Sons Canada, Ltd.
The
purchaser may make back-up copies for his or her own use
only and not for distribution or resale. The author and the
publisher assume no responsibility for errors, omissions, or
damages caused by the use of these programs or from the
use of the information contained herein.
Copyright 2010 John Wiley & Sons Canada Ltd.