Course Overview
• • PART I: Database Concepts
– – – – – – Ch. 1: Database Systems Ch. 2: Data Models Ch. 3: Ch. 4: Ch. 5: Ch. 6: The Relational Database Model Entity Relationship (ER) Modeling Normalization of Database Tables Advanced Data Modeling
PART II: Design Concepts
•
PART III: Advanced Design and Implementation
– – – Ch. 7: Introduction to Structured Query Language (SQL) Ch. 8: Advanced SQL Ch. 9: Database Design Ch. 10: Transaction Management and Concurrency Control Ch. 11: Database Performance Tuning and Query Optimization Ch. 12: Distributed Database Management Systems Ch. 13: Business Intelligence and Data Warehouses
•
PART IV: Advanced Database Concepts
– – – –
•
PART V: Databases and the Internet
– – Ch. 14: Database Connectivity and Web Technologies Ch. 15: Database Administration and Security
Team 1 & 2 Presentations
Name Name Email Team # Email Team #
Anyanwu, Innocent U. 1
1 1 2 2 2 2 3 3 3 3 4 4 4 4 Macauley, Ronald Godfrey Batra, Shelly Piller, William Robert Corio, Anthony Louis Muthappan, Annamalai Peter, Mercy Florence Punchhi, Aseem Hwang, Jeff Noronha, Natasha Siddiqui, Majdi Chiu, Lisa J.
iua2@njit.edu
jh73@njit.edu nn46@njit.edu ms369@njit.edu ljc5@njit.edu
5
5 5 5 6
Bharadwaj, Bindu
Crooke, Rosita E. Shah, Hemal Harshvadan Gu, Jian H. (a.k.a. Allen J. Gu) Hussain, Abraar Kelvadi, Sandeep Singh, Deepender Ambashanker, Ashok Mahadeo, Himraj Shrestha, Prerana Vaziri Khorrami, Ramtin Connelly, Christopher William Krikun, Anastasiya Martin, Carlos Schuckel, Erik Arthur
bb52@njit.edu
rec8@njit.edu hhs4@njit.edu jhg4@njit.edu ah58@njit.edu sk244@njit.edu ds82@njit.edu aa227@njit.edu hm37@njit.edu ps97@njit.edu rv59@njit.edu cwc3@njit.edu ak66@njit.edu cm48@njit.edu eas22@njit.edu
Kumar, Sailani
Usmani, Ather Watrous-deVersterre, Lori L. Hall, Robert L
sk346@njit.edu
au3@njit.edu llw2@njit.edu rlh2@njit.edu rgm7@njit.edu sb329@njit.edu wrp2@njit.edu alc22@njit.edu am299@njit.edu mfp2@njit.edu ap224@njit.edu
6
6 6 7 7 7 7 8 8 8 8
Database Systems: Design, Implementation, and Management
Eighth Edition
Chapter 14 Database Connectivity and Web Technologies
Objectives
• In this lecture, you will learn:
– Different database connectivity technologies – How Web-to-database middleware is used to integrate databases with the Internet – Web browser plug-ins and extensions – Services provided by Web application servers – What Extensible Markup Language (XML) is and why it is important for Web database development
Database Systems, 8th Edition
4
Database Connectivity
• Mechanisms by which application programs connect and communicate with data repositories.
– Also known as database middleware
• Data repository:
– Also known as a data source – Represents the data management application (i.e. Oracle RDBMS, SQL Server, etc)
• Used to store data generated by an application program
• ODBC, OLE-DB, ADO.NET: the backbone of MS Universal Data Access (UDA) architecture (a collection of technologies used to access any type of data source and manage the data through a common interface)
Database Systems, 8th Edition
5
Native SQL Connectivity
• Connection interface provided by database vendors
– Unique to each vendor
• Example: Oracle RDBMS
– Must install and configure Oracle’s SQL*Net interface in client computer
• Interfaces optimized for particular vendor’s DBMS
– Maintaining multiple native interfaces for different databases can become a burden for the programmer
Database Systems, 8th Edition
6
Configuration & Installation of SQL*Net
Database Systems, 8th Edition
7
iSQL*Plus Interface
Aqua Data Studio (ADS)
Microsoft & DB Connectivity
ODBC, DAO, and RDO
• Open Database Connectivity (ODBC)
– Microsoft’s implementation – Widely supported database connectivity interface – Any Windows application can access relational data sources – Uses SQL via standard application programming interface (API – “set of routines, protocols, and tools for building software applications.” www.webopedia.com)
Database Systems, 8th Edition
11
ODBC, DAO, and RDO (continued)
• Data Access Objects (DAO)
– Object-oriented API
• Accesses MS Access, MS FoxPro, and dBase databases from Visual Basic programs
– Provided an optimized interface that exposed functionality of Jet data engine to programmers – DAO interface can also be used to access other relational style data sources
Database Systems, 8th Edition
12
ODBC, DAO, and RDO (continued)
• Remote Data Objects (RDO)
– Higher-level object-oriented application interface used to access remote database servers – Uses lower-level DAO and ODBC for direct access to databases – Optimized to deal with server-based databases, such as MS SQL Server, Oracle, and DB2
Database Systems, 8th Edition
13
Database Systems, 8th Edition
14
ODBC, DAO, and RDO (continued)
• Basic ODBC architecture has three main components:
– High-level ODBC API through which application programs access ODBC functionality – Driver manager that is in charge of managing all database connections – ODBC driver that communicates directly to DBMS
Database Systems, 8th Edition
15
Database Systems, 8th Edition
16
Database Systems, 8th Edition
17
OLE-DB
• Object Linking and Embedding for Database • Database middleware that adds object-oriented functionality for access to relational and nonrelational data • Series of COM objects (used to enable interprocess communication and dynamic object creation in a large range of programming languages) provides low-level database connectivity for applications • Functionality divided into two types of objects:
– Consumers – Providers
Database Systems, 8th Edition
18
OLE-DB (Continued)
• Consumers are objects (applications or processes) that request and use data • Providers are objects that manage the connection with a data source and provide data to consumers.
OLE-DB (continued)
• OLE-DB did not provide support for scripting languages and therefore MS developed ActiveX Data Objects (ADO) • ADO provides high-level application-oriented interface to interact with OLE-DB, DAO, and RDO • ADO provides unified interface to access data from any programming language that uses the underlying OLE-DB objects
Database Systems, 8th Edition
20
Database Systems, 8th Edition
21
Database Systems, 8th Edition
22
Database Systems, 8th Edition
23
ADO.NET
• Data access component of Microsoft’s .NET application development framework • Used to develop distributed, heterogeneous, interoperable applications aimed at manipulating any type of data over any network under any operating system and programming language. • Two new features for development of distributed applications:
– DataSet is disconnected memory-resident representation of database – DataSet is internally stored in XML format
• Data in DataSet made persistent as XML documents
Database Systems, 8th Edition
24
Database Systems, 8th Edition
25
ADO.NET (continued)
• Specific objects manipulate data in data source – Connection – defines the data source used, the name of the server, the database and so on – Command – represents a database command to be executed within a specified database connection – DataReader – specialized object that creates a read-only session with the database to retrieve data sequentially
Database Systems, 8th Edition
26
ADO.NET (continued)
– DataAdapter – in charge of managing a DataSet object – DataSet – in-memory representation of the data in the database – DataTable – represents the data in tabular format
Java Database Connectivity (JDBC)
• Java is an object-oriented programming language
– Runs on top of Web browser software
• Advantages of JDBC:
– Company can leverage existing technology and personnel training – Allows direct access to database server or access via database middleware – Provides a way to connect to databases through an ODBC driver
Database Systems, 8th Edition
28
Database Systems, 8th Edition
29
Internet Databases
• Web database connectivity allows new innovative services that:
– Permit rapid response by bringing new services and products to market quickly – Increase customer satisfaction through creation of Web-based support services – Yield fast and effective information dissemination through universal access
Database Systems, 8th Edition
30
Database Systems, 8th Edition
31
Web-to-Database Middleware: Server-Side Extensions
• Web server is the main hub through which Internet services are accessed • Dynamic Web pages are at the heart of current generation Web sites • Server-side extension: a program that interacts directly with the Web server
– Also known as Web-to-database middleware
Database Systems, 8th Edition
32
Database Systems, 8th Edition
33
Web Server Interfaces
• Two well-defined Web server interfaces:
– Common Gateway Interface (CGI) – Application Programming Interface (API)
• Disadvantage of CGI scripts:
– Loading external script decreases system performance – Language and method used to create script also decrease performance
• API is more efficient than CGI
– API is treated as part of Web server program
Database Systems, 8th Edition
34
Database Systems, 8th Edition
35
The Web Browser
• Software that lets users navigate the Web • Located in client computer • Interprets HTML code received from Web server • Presents different page components in standard way • Web is a stateless system: Web server does not know the status of any clients
Database Systems, 8th Edition
36
Client-Side Extensions
• Add functionality to Web browser • Three general types:
– Plug-ins – Java and JavaScript – ActiveX and VBScript
Database Systems, 8th Edition
37
Client-Side Extensions (continued)
• Plug-in: an external application automatically invoked by the browser when needed • Java and JavaScript: embedded in Web page
– Downloaded with the Web page and activated by an event
• ActiveX and VBScript: embedded in Web page
– Downloaded with page and activated by event – Oriented to Windows applications
Database Systems, 8th Edition
38
Web Application Servers
• Middleware application that expands the functionality of Web servers
– Links them to a wide range of services
• Some uses of Web application servers:
– Connect to and query database from Web page – Create dynamic Web search pages – Enforce referential integrity
• Some features of Web application servers:
– Security and user authentication – Access to multiple services
Database Systems, 8th Edition
39
Extensible Markup Language (XML)
• Companies use Internet to create new systems that integrate their data
– Increase efficiency and reduce costs
• Electronic commerce enables organizations to market to millions of users • Most e-commerce transactions take place between businesses • HTML Web pages display in the browser
– Tags describe how something looks on the page
Database Systems, 8th Edition
40
Extensible Markup Language (XML) (continued)
• Extensible Markup Language (XML):
– Metalanguage to represent and manipulate data elements – Facilitates exchange of structured documents over the Web – Allows definition of new tags
• • • • Case sensitive Must be well-formed and properly nested Comments indicated with <- and -> XML and xml prefixes reserved for XML tags only
41
Database Systems, 8th Edition
XML Document Declaration Root Element Child Elements
Database Systems, 8th Edition
42
Document Type Definitions (DTD) and XML Schemas
• Document Type Definition (DTD)
– File with .dtd extension that describes elements – Provides composition of database’s logical model – Defines the syntax rules or valid tags for each type of XML document
• Companies engaging in e-commerce transaction must develop and share DTDs • DTD referenced from inside XML document
Database Systems, 8th Edition
43
Contents of the productlist.dtd document
1. 2. 3. 4. 5. 6. 7. 8.
First line declares the ProductList root element ProductList root element has one child, the Product element The plus “+” symbol indicates that Products occurs one or more times within ProductList An asterisk “*” would mean that the child element is optional The second line describes the Product element The question mark “?” after the P_INDATE and P_MIN indicates that they are optional elements The 3rd – 8th lines show what the Product element has 6 child elements The #PCDATA keyword represents the actual text data
Database Systems, 8th Edition
44
Productlistv2.xml
To be able to use a DTD file to define elements with an XML document the DTD must be referenced within that XML document
Database Systems, 8th Edition
45
B2B of OrderData
Database Systems, 8th Edition
46
Document Type Definitions (DTD) and XML Schemas (continued)
• XML schema
– Advanced data definition language – Describes the structure (elements, data types, relationship types, ranges, and default values) of XML data documents
• Advantage of XML schema:
– More closely maps to database terminology and features
• XML schema definition (XSD) file uses syntax similar to XML document
Database Systems, 8th Edition
47
Data types
Database Systems, 8th Edition
48
XML Presentation
• XML separates data structure from presentation and processing • Extensible Style Language (XSL) displays XML data
– Defines the rules by which XML data are formatted and displayed – Two parts:
• Extensible Style Language Transformations (XSLT) • XSL style sheets
Database Systems, 8th Edition
49
Describes the general mechanism that is used to extract and process data from one XML document and enable its transformation within another document (ex. Extract and XML document and convert it into a web page that is formatted for a mobile device.
Database Systems, 8th Edition
50
Color-coded, collapsible treelike structure
Database Systems, 8th Edition
51
Binds XML document to an HTML table
Database Systems, 8th Edition
52
XML Applications
• • • • • • • B2B exchanges Legacy systems integration Web page development Database support Database meta-dictionaries XML databases XML services
Database Systems, 8th Edition
53
Summary
• Database connectivity:
– Ways in which programs connect and communicate with data repositories
• Database connectivity software known as database middleware • Database repository also known as data source
– Represents data management application used to store data generated by the program
• Microsoft interfaces are dominant players
– ODBC, OLE-DB, ADO.NET
Database Systems, 8th Edition
54
Summary (continued)
• Microsoft’s Universal Data Access (UDA) architecture
– Collection of technologies to access any type of data source using common interface
• Native database connectivity: interface provided by database vendor
– ODBC is Microsoft's implementation of SQL Access – Allows any Windows application to access relational data sources using SQL
Database Systems, 8th Edition
55
Summary (continued)
• OLE-DB adds object-oriented functionality for access to data • ActiveX Data Objects provide interface with OLE-DB, DAO, and RDO • ADO.NET is data access component of Microsoft .NET framework • Java Database Connectivity (JDBC) interfaces with Java applications with data sources
Database Systems, 8th Edition
56
Summary (continued)
• Database access through the Web uses middleware • On client side of Web browser, use plug-ins, Java and JavaScript, ActiveX, and VBScript • On server side, middleware expands functionality of Web servers
– Links them to wide range of services
• XML provides semantics to share structured documents across the Web
– Produces description and representation of data
Database Systems, 8th Edition
57