Transcript Tech Guide D

Introduction to Business
Information Systems
by
Mark Huber, Craig Piercy, Patrick McKeown,
and James Norrie
Tech Guide D:
The Details of SQL, Data Modelling,
and XML
Using SQL to Query Relational
Databases
 The primary function of a database is to
enable people to obtain information from it in
a usable form using __________ (questions)
 To query a relational database, many use
__________ Query __________ (SQL),
which is a computer language for
manipulating data in a relational database.
 SQL queries also enable database users to
add ____________ records, or
____________ or ____________ records in a
database.
Copyright 2008 John Wiley & Sons Canada Ltd.
2
Important Terms
 __________ key: a field that holds a unique
value for each record in a table.
 __________ key: the primary key from
another table that is in the current table.
 ______________: another name for a table in
a relational database.
 ______________: another name for a record
in a relational database table.
 ______________: another name for a field in
a relational database table.
Copyright 2008 John Wiley & Sons Canada Ltd.
3
Querying a Single Table Database
 To query a single table database, we use an
SQL statement of the form:
__________ fields FROM tables
__________ fields match query condition



Where the __________________ keyword
designates which fields to display as a result
of the query
the ________________ keyword designates
which tables to search
the ____________________ keyword
specifies the search criteria, or query
condition, to use in finding records.
Copyright 2008 John Wiley & Sons Canada Ltd.
4
Other Query Keywords
 In addition to the _______________ keyword,
there are a number of other keywords that we
can use to:
 __________ a table
 __________ new records in a table
 __________ records from a table
 __________ one or more records in a table
Copyright 2008 John Wiley & Sons Canada Ltd.
5
Using SQL to Display Specific
Information
 The SQL to display all of the table, that is, all
fields for all of the records is:
____________________________
 To display only a _____________ of the
fields, the standard form of this query is
(where queries can run on to the next line):
SELECT FieldName1, FieldName2, …
FROM TableName
Copyright 2008 John Wiley & Sons Canada Ltd.
6
Displaying Selected Fields for
Matching Records
 In many cases, we may only want to display selected
fields for records that match some condition.
 To do this, we need to use the _________________
keyword followed by some query condition involving
one of six comparison operators:
 ___________________
 ___________________
 ___________________
 ___________________
 ___________________
 ___________________
 plus a fieldname and a value.
Copyright 2008 John Wiley & Sons Canada Ltd.
7
Using the LIKE Operator
 Using the ________________ sign in a
SELECT query looks for an exact match.
 To look for an “_____________________” the
LIKE operator is appropriate.
 The LIKE operator uses the _____________
character as a replacement for unknown or
non-existing characters in attempting to find
matches to a group of characters.
 The wildcard character is usually either the
___________ or the ___________________
 The general form of this type of query is:
SELECT FieldName1,Fieldname2,… FROM
TableName WHERE FieldName LIKE ‘*value*’
8
Inserting, Deleting, or Changing
Records
 To insert a record into a table, you would use an
SQL statement of the form: __________ INTO
TableName Values (value1, value2, …)
 To delete an existing record from a database
table, you would use an SQL statement of the
form: __________ 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: ____________ TableName SET
FieldName1 = value WHERE FieldName2 =
value
Copyright 2008 John Wiley & Sons Canada Ltd.
9
Using Aggregate Functions in SQL
 SQL can be used to compute certain values in
the table using five different aggregate functions:
 __________________
 __________________
 __________________
 __________________
 __________________
 In each case, you must use a _______________
field name for the result of the computation.
Copyright 2008 John Wiley & Sons Canada Ltd.
10
Using Logical Modelling to Create a
Relational Database
 Most relational databases include many
tables, not just one, to avoid
____________________.
 Problems caused by redundancy, typically
referred to as _______________, can harm
the integrity of the database records.
 To create relational databases that avoid
these problems,
_____________________________ is used.
 The first step in logical modeling is to create
an __________ Diagram (ERD)
Copyright 2008 John Wiley & Sons Canada Ltd.
11
Example ERDs
 A ________________
relationship with one
vendor selling many
products.
 A ________________
relationship with no
primary key–foreignkey relationship. Many
customers buy many
products.
Copyright 2008 John Wiley & Sons Canada Ltd.
12
Relational Data Models
 The next step after creating an ERD is to
convert it into a corresponding
__________________________________
(see Figure D.10 on page 506 of your text).
 Note that the relational diamond is replaced
by a “_______________________”
representing the “many” end of the
relationship.
Copyright 2008 John Wiley & Sons Canada Ltd.
13
Querying Multitable Databases
 The most common operation in querying
multitable databases is the ______________
operation in which we create one table from
two.
Copyright 2008 John Wiley & Sons Canada Ltd.
14
Creating Views
 In most database systems, ______________
can be saved and reused.
 A saved query is often referred to as a
__________.
 A query can be saved as a view for reasons
of __________________. By creating a view
only showing fields that we want others to
see, we can protect confidential information in
the data, such as customer information.
Copyright 2008 John Wiley & Sons Canada Ltd.
15
Using XML
 For many years companies have been using
a system known as
______________________________ (EDI)
to share data and resources over the Web
 With the rapid growth of the Internet,
organizations have turned to the use of
_____________ as a way of carrying out the
same processes
Copyright 2008 John Wiley & Sons Canada Ltd.
16
Setting Up an XML Document
 The first step to creating an XML file is to
decide which ____________ to use to
describe the data
 Each _______________ tag must have an
________________ tag
 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
____________________________ method
or the ________________________ method.
Copyright 2008 John Wiley & Sons Canada Ltd.
17
Copyright
Copyright © 2008 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 2008 John Wiley & Sons Canada Ltd.
18