Transcript cos346day23

COS 236
Day 23
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-1
Agenda
• Assignment 9 Graded
– 2 B’s, 5 C’s
• Assignment 10 Due Today
• Assignment 11 Posted
– Due May 3
• Assignment 12 Posted
– Due May 9
• Last Capstone Progress Reports Due Today
• Today we will discuss
– XML and ADO.NET
– Support-Files-Chap-13-XML
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-2
End of days? (subject to
change)
• May 3
• April 26
– DP Chapter 13
– Assignment 10 due
• April 30
– DP Chap 13
– DP Chap 15
– Assignment 11 Due
– Review
• May 9
–
–
–
–
Assignment 12 due
Quiz 3
10 AM
Capstone
presentations
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-3
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Thirteen:
XML and ADO.NET
Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-4
Introduction
• Database processing and document processing
need each other.
– Database processing needs document processing
for transmitting database views.
– Document processing needs database processing
for storing and manipulating data.
• As Internet usage increases, organizations want
to make their Web pages more functional by
displaying and updating data from organizational
databases.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-5
XML
• XML, or Extensible Markup Language, was developed
in early 1990s:
– XML is a subset of SGML, or Standard Generalized Markup
Language.
• Today XML is a hybrid of document processing and
database processing.
– It provides a standardized yet customizable way to describe the
content of documents.
– XML documents can automatically be generated from database
data, and vice versa.
• SOAP (which formerly stood for Simple Object Access
Protocol but is now just a name instead of an acronym)
is an XML-based standard protocol for sending
messages of any type, using any protocol over the
Internet.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-6
XML (Continued)
• XML is used for describing, representing, and
materializing database views.
• XML is better than HTML because:
– It provides a clear separation between document structure,
content, and materialization.
– It is standardized but allows for extension by developers
– XML tags accurately represent the semantics of their data.
• Document Type Declarations (DTDs) and
XML Schemas can be used to describe the content of
XML documents.
• Both Oracle and SQL Server can produce XML
documents from database data.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-7
XML DTD
• An XML document consists of two sections:
– Document Type Declaration (DTD).
• The DTD begins with DOCTYPE <document_type_name>
– Document data.
• An XML document can be:
– Type-valid if the document conforms to its DTD.
– Well-formed and not type-valid, if
• It violates the structure of its DTD, or
• It has no DTD.
• A DTD may be stored externally so many documents can
be validated against the same DTD.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-8
XMLSpy
• The diagrams in the text and in the
following slides were prepared with
Altova’s XMLSpy.
• There is a 30 trial version of XMLSpy 2007
available at:
http://www.altova.com/download.html
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-9
XML Document with Internal DTD
Support-Files-Chap-13-XML\Figure 13-1.xml
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-10
XML Document with External DTD
Support-Files-Chap-13-XML\Figure 13-2.xml
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-11
External DTD for CustomerList
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-12
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-13
XML Document with Two Customers
Support-Files-Chap-13-XML\Figure 13-3.xml
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-14
XSLT
• XSLT, or the Extensible Style Language may be used
to materialize (transform) XML documents using XSL
document.
– From XML documents into HTML or into XML in another format.
• XSLT is a declarative transformation language:
– Declarative: create rules, not procedures, to materialize the
document.
– Transformational: transforms the input document into another
document.
• XSLT uses stylesheets to indicate how to transform the
elements of the XML documents into another format.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-15
Example XSL Stylesheet
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-16
Example HTML in Browser
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-17
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Thirteen Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-18
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Thirteen:
XML and ADO.NET
Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-19
XML Schema
• XML Schema is a standard for describing the content of
an XML document, i.e., defining custom vocabularies:
– Documents that conform to an XML Schema are called schemavalid.
– An XML document can be well-formed and be neither type-valid
nor schema-valid.
• Unlike DTDs, XML Schema documents are themselves
XML documents that can be validated against the
schema maintained by W3C.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-20
XML Schema Document
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-21
Schema-Valid XML Document
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-22
Elements and Attributes
• Schemas consist of elements and attributes:
– Elements are used to carry data and attributes are
used to carry metadata.
• Two types of elements:
– Simple elements have a single data value.
– ComplexType elements can have one or more
simple or ComplexType elements.
• ComplexType elements can have attributes.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-23
Flat Schemas:
XML Schema
• Flat schemas have all elements at the same
level:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-24
Flat Schemas:
Schema-Valid Document
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-25
Flat Schemas:
SGraphical Representation of Schema
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-26
Structured Schemas:
XML Schema
• Structured
schemas
have
defined
subgroups:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-27
Structured Schemas:
Schema-Valid Document
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-28
Structured Schemas:
Graphical Representation of Schema
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-29
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Thirteen Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-30
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Thirteen:
XML and ADO.NET
Part Three
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-31
Global Elements
• To eliminate the definition duplication,
elements can be declared globally, i.e.,
reside at the top level of the schema, and
then reused.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-32
Global Elements :
XML Schema with Global PhoneType
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-33
Global Elements :
PhoneType Global Element
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-34
Global Elements :
Graphical Representation of Schema
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-35
Creating XML Documents from
Database Data
• Both Oracle and SQL Server can generate XML
documents from database data.
• For Oracle — Use
http://www.oracle.com/technology/tech/xml/xdkh
ome.html
• For SQL Server:
– SELECT . . . FOR XML
• SELECT … FOR XML RAW — Places the values of columns as
attributes in the XML document.
• SELECT … FOR XML AUTO, ELEMENTS — Places the values of
columns as elements in the XML document.
• SELECT … FOR XML EXPLICIT — Allows the designation of which
values of columns become attributes and elements.
• XML Spy can generate schemas from such output.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-36
XML Schema with Multivalue Paths
• Some views cannot be created in a single
SQL statement because the construct
requires two or more multivalued paths.
• XML does not have this limitation.
• An XML document can have as many
multivalued paths as necessary.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-37
Multivalue
Paths:
XML
Schema
with Two
Multivalued
Paths
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-38
Multivalue Paths:
Graphical View of the Schema
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-39
Industries with XML Industry Standards
http://www.xml.org/
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-40
Industries with XML Industry Standards
(Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-41
XML Standards
• XML: Extensible Markup Language.
• XSL: XSLT Stylesheet. The document that provides the
{match, action} pairs and other data for XSLT to use
when transforming an XML document.
• XSLT: A program or process that applies XSLT
Stylesheets to an XML document to produce a
transformed XML document.
• XML schema: An XML-compliant language for
constraining the structure of an XML document.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-42
Additional XML Standards
• Xpath
– A sublanguage within XSLT used to identify parts of an XML
document to be transformed
– Can also be used for calculations and string manipulation
• Xpointer
– A standard for linking one document to another
• SAX: Simple API (application program interface) for
XML.
– An event-based parser that notifies a program when the
elements of an XML document have been encountered during
document parsing
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-43
Additional XML Standards
(Continued)
• DOM: Document Object Model.
– An API that represents an XML document as a tree
– Each node of the tree represents a piece of the XML document.
– A program can directly access and manipulate a node of the
DOM representation.
• XQuery
– A standard for expressing database queries as XML documents
– The structure of the query uses XPath facilities, and the result of
the query is represented in an XML format.
• XML Namespaces: A standard for allocating terminology
to defined collections
– X:Name is interpreted as the element Name as defined in
namespace X.
– Useful for disambiguating terms
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-44
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Thirteen Part Three
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-45
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Thirteen:
XML and ADO.NET
Part Four
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-46
ADO.NET
• ADO.NET is a new, improved, and greatly
expanded version of ADO that was developed
for the Microsoft .NET initiative.
• It incorporates all of the functionality of ADO and
OLE DB facilitates the transformation of XML
documents to and from database constructs.
• It uses datasets, which are in-memory, fullyfunctioned, independent databases.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-47
Role of ADO.NET
• ADO.NET serves as an intermediary between
all types of .NET applications and the DBMS
and database:
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-48
Data Provider
• A .NET data provider is a library of classes that
provides ADO.NET services.
• Microsoft’s provides three data providers:
– OLE DB data provider can be used to process any
OLE DB-compliant data source.
– SQLClient data provider is purpose-built for use with
SQL Server.
– OracleClient data provider is purpose-built for use
with Oracle.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-49
Data Provider Components
• A connection object is similar to the OBDC’s
connection object.
• A command object is created on an established
connection.
• A data reader provides read-only, forward-only, fast
access to database data.
• An application can get and put data to and from the
database using the command object.
• A dataset is an in-memory database that is
disconnected from any regular database.
– It distinguishes ADO.NET from the previous data access
technology.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-50
Data Provider Components
(Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-51
The ADO.NET Dataset
• A dataset is an in-memory database that is
disconnected from any regular database.
• Datasets can have:
– Multiple tables, views, and relationships
• Tables may have surrogate key (auto increment columns),
primary keys, and be declared as unique.
– Referential integrity rules and actions
– The equivalent of triggers
• Datasets may be constructed from several
different databases and managed by different
DBMS products.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-52
Dataset Advantages
• Dataset contents and its XML schema can be easily
formatted as an XML document.
• Also, XML schema documents can be read to create the
structure of the dataset, and XML documents can be
read to fill the dataset.
• Datasets are needed to provide a standardized, nonproprietary means to process database views.
– This is important for the processing of views with multiple multivalue paths.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-53
Dataset Disadvantages
• Because dataset data are disconnected from regular
database, only optimistic locking can be used when
updating the regular database with the dataset.
• In the case of conflict, either the dataset must be
reprocessed or the data change must be forced onto the
database, causing the lost update problem.
• Thus, datasets cannot be used for applications in which
optimistic locking is problematical.
– Instead, the ADO.NET command object should be used.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-54
ADO.NET:
Creating the Dataset, Connection, and Data Adapter
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-55
ADO.NET:
Using the Data
Adapter to Fill the
Dataset Tables
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-56
ADO.NET:
Building Relationships
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-57
ADO.NET:
Creating
Referential
Integrity
Constraints
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-58
ADO.NET:
Adding a Computed Column to a Data Table
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-59
ADO.NET:
Using Data Grids
– Filling the Grids
with Dataset
Tables
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-60
ADO.NET:
Using Data Grids — Grid Display in Brower
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-61
David M. Kroenke’s
Database Processing:
Fundamentals, Design, and
Implementation
Chapter Thirteen:
XML and ADO.NET
Part Five
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-62
ADO.NET:
Code to Generate an XML Document from the Dataset
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-63
ADO.NET:
Portion of XML Document Generated from the Dataset
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-64
ADO.NET:
Code to Generate an XML Schema from the Dataset
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-65
ADO.NET:
Portion of XML Schema Generated from the Dataset
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-66
ADO.NET:
Updating a Dataset and Database — Update Code
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-67
ADO.NET:
Updating a Dataset and Database — Trigger-Like Event
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-68
ADO.NET:
Updating a Dataset and Database
Display RowVersion Code
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-69
ADO.NET:
Updating a Dataset and Database
Creating the Update Command
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-70
ADO.NET:
Updating a Dataset and Database
Log Showing Dataset Updates
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-71
ADO.NET:
Updating a Dataset and Database
Dataset Tables after Update
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-72
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Thirteen Part Five
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
13-73