Transcript Database Management Systems

D
A
T
A
B
A
S
E
Distributed Databases





SELECT Sales
FROM Britain.Sales
UNION
SELECT Sales
FROM France.Sales
UNION
SELECT Sales
FROM Italy.Sales
Definition
Advantages / Uses
Problems / Complications
Client-Server / SQL Server
Microsoft Access
Germany
Britain
France
Italy
1
D
A
T
A
B
A
S
E
Distributed Database Definition
 Multiple independent databases
 Each DBMS is a complete
DBMS (engine, queries,
locking, transactinos, etc.)
 Usually on different machines.
 Usually in different locations.
 Connected by a network.
 Might be different environments
 Hardware
 Operating System
 DBMS Software
Database
Apollo
Database
Zeus
England
France
Database
Athena
United States
2
D
A
T
A
B
A
S
E
Distributed Database Rules

 C.J. Date

 Rule 0: Transparency: the
user should not know or care
that the database is distributed. 





Local autonomy.
No reliance on a central site.
Continuous operation.
Location independence.
Fragmentation independence
(physical storage).
 Replication independence.
Distributed query processing.
Distributed transaction
management.
Hardware independence.
 Operating system independence.
 Network independence.
 DBMS independence.
3
D
A
T
A
B
A
S
E
Distributed Features
 Each database can continue to run even if portion fails.
 Data and hardware can be moved without affecting
operations or users.
 Expanding operations.
 Performance issues.
 System expansion and upgrades.
 Add new section without affecting others.
 Upgrade hardware, network and DBMS.
4
D
A
T
A
B
A
S
E
Advantages and Applications
 Business operations are
often distributed
local
transactions
 Work and data are
segmented by department.
 Work and data are
segmented by geographical
location.
 Improved performance
 Most updates and queries
are performed locally.
 Maintain local control and
responsibility over data.
future
expansion
 Can still combine data
across the system.
 Scalability and expansion
 Add on, not replacement.
5
D
A
T
A
B
A
S
E
Creating a Distributed Database
 Design administration plan.
 Choose hardware and DBMS vendor,
and network.
 Set up network and DBMS
connections.
 Choose locations for data.
 Choose replication strategy.
 Create backup plan and strategy.
 Create local views and synonyms.
 Perform stress test: loads and failures.
6
D
A
T
A
B
A
S
E
Distributed Query Processing
 Networks are slow




Drives: 10 - 20 MB per sec.
LANs: 1 - 10 MB per sec.
WANs: 0.01 - 5 MB per sec.
Faster is possible but expensive!
 Goal is to minimize transmissions.
WAN
0.1 - 5 MB
 Each system must be capable of
evaluating queries--preferably SQL.
 Results depend heavily on how the
system joins tables.
10 - 20 MB
Disk drive
1 - 10 MB
LAN
7
D
A
T
A
B
A
S
E
Distributed Query Processing
 Example




NY
NY: Customers: 1 M rows
Customers(C#, …)
LA: Production: 10 M rows
1,000,000
C# list from
Chicago: Sales: 20 M rows
desired P#
Query: List customers who
Chicago
Matching
bought blue products on 1-Mar-01
Sales(S#, C#, Sdate) Customer
 Bad idea #1
data
20,000,000
SaleItem(S#, P#,…)
 Transfer all rows to Chicago
50,000,000
 Then JOIN and select.
 Better idea #2 (probably)
P# sold on
 Transfer blue products from LA 1-Mar-01
to Chicago
Blue P#
sold on
 Better idea #3
LA
1-Mar-01
 Get sale items on 1-Mar-01
Products(P#, Color…)
 Get blue products from LA
10,000,000
 Send C# to NY
8
D
A
T
A
B
A
S
E
Data Replication
 Goals
 Minimize transmissions
 Improve performance
 Support heavy multiuser
access.
 Problems
 Updating copies
Britain
Britain: Customers
& Sales
Market research &
data corrections.
France: Customers
& Sales
Spain: Customers
& Sales
Periodic
updates
 Bulk transmissions
 Site unavailable
 Concurrency
 Easier for two people to
change the same data at
the same time.
 Decision support systems.
 Data warehouse.
Spain
Britain: Customers
& Sales
France: Customers
& Sales
Spain: Customers
& Sales
Update data.
9
D
A
T
A
B
A
S
E
Concurrency and Locks
 Each DBMS must maintain
lock facility.
 To update, each DBMS must
utilize and recognize other
lock mechanisms and return
codes.
 Each DBMS must have a
deadlock resolution protocol
that recognizes the
distributed databases.
 Random wait.
 Optimistic updates.
 Two-phase commit.
DBMS #1
Accounts
Jones
8898
Transaction A
Locked
Waiting
DBMS #2
Accounts
Jones
3561
Transaction B
Waiting
Locked
10
D
A
T
A
B
A
S
E
Transactions & Two-Phase Commit
 Two (or more) separate lock
managers.
 DBMS initiating update
serves as the coordinator.
 Two phases
Database 1
Initiate Transaction
1. Prepare to commit.
All agree?
 Coordinator sends message
2. Commit
and data to all machines to
“get ready.”
 Local machines save data in
logs, verify update status
and return message.
 If all locals report OK, then
Database 2
Lock tables.
coordinator writes log and
Database 3
Save log.
instructs others to proceed.
Update all tables.
If any fail, it sends Rollback
message.
11
D
A
T
A
B
A
S
E
Distributed Design Questions
Question
What level of data consistency is needed?
How expensive is storage?
What are the shared access requirements?
How often are the tables updated?
Required speed of updates (transactions)?
How important are predictable transaction times?
DBMS support for concurrency and locking?
Can shared access be avoided?
Concurrent
High
Medium – High
Global
Often
Fast
High
Good – Excellent
No
Replication
Low – Medium
Low
Local
Seldom
Slow
Low
Poor
Yes
12
D
A
T
A
B
A
S
E
Distributed Databases In Oracle
 Database Links
 Full database names.
 CONNECT command.
 Linking through synonyms.
 CREATE SYNONYM …
 Central control over permissions.
Schema.Table@Location
[email protected]
Server
database
Synonym:
Employee
Procedure:
DELETE FROM
Employee
WHERE ...
 Linking through Views/queries.
 CREATE VIEW AS …
 Can assign local permissions.
 Linking through stored procedures.
 DELETE …
 Strong control over actions.
View
user
permissions
User can only
run procedure.
No other access.
13
D
A
T
A
B
A
S
E
Client-Server
Server
Server
Shared
Database
Front-end
User Interface
Clients
Clients
14
D
A
T
A
B
A
S
E
LAN File Server
File Server
 Not a distributed database.
 Data file stored on server.
 Server is passive, appears
as giant disk drive to PC.
 PC processes all data.
 Retrieves all needed data
across the network.
 Performance improvements.
 Indexes are crucial.
 Store some data on each
PC (replication).
 Store applications on PC
(graphics & forms).
 Convert to SQL-Server
DBMS data file
Application
Shared
Data
All data from all tables are
read by PC, which performs
JOIN and WHERE test. If
available, reads index first.
SELECT Name, SaleDate
FROM Customer INNER JOIN Sales
ON Customer.C# = Sales.C#
WHERE SaleDate BETWEEN #1-Mar-97#
AND #9-Mar-97#;
15
D
A
T
A
B
A
S
E
LAN File Server: Slow
File Server
MyFile.mdb
CustID Name …
115
Jenkins …
Forms 125
Juarez ...
Order ...
DBMS
software
transferred.
Application
and query
transferred.
SELECT *
FROM Customer
WHERE City = “Sandy”
One row at a time
transferred, until
all rows are examined.
16
D
A
T
A
B
A
S
E
Client-Server Databases
File Server
 One machine machine is
dominant (server) and
handles data for many
clients.
 Client machines handle
front-end tasks and small
data tables that are not
shared.
DBMS
SQL Server
Send SQL
statement.
Shared
Data
Return
matching
data.
application
17
D
A
T
A
B
A
S
E
Microsoft Access
 Access is a client-side database.
 In file server environment.
 As a client to a database server.
 Attach or Link to other databases.
 Link to an Access database is file server.
 Link through ODBC is database server.
 ODBC: Open DataBase Connectivity
 Pass-Through Queries
18
D
A
T
A
B
A
S
E
Open Database Connectivity: ODBC
Server Computer
 Microsoft connectivity standard.
 Most DBMS companies
provide drivers.
 SQL Server, Oracle, Ingres,
etc.
Database
Server
ODBC driver
 Driver is installed on PC and
Server.
 ODBC handles:




Login to database.
Send query.
Interpret result codes.
Exchange data.
ODBC driver
Link Tables
Access
Client Computer
19
D
A
T
A
B
A
S
E
ODBC Basics
 Set the connection string.
 Datatype: odbc
 DSN: data source name holds linkage data--built with Windows Control
Panel or RegisterDatabase.
 uid: login Username
 pwd: login Password
 database: full database name on remote system.
20
D
A
T
A
B
A
S
E
ADO and Direct Connections
Server Computer
The Database vendor
provides its own data
transport (e.g,. Oracle or
SQL Server) installed on
the server and the client.
Database
Server
DBMS transport
ADO provides a driver that
connects your application to
the transport services.
ODBC can serve as the
data transport if nothing
else is available
DBMS transport
ADO
Visual Basic
application
Client Computer
21
D
A
T
A
B
A
S
E
Three-Tier Client-Server
Databases.
 Server Databases
Transactions.
 Client front-end
Legacy applications.
 Middle
 Locate databases
 Business rules
 Program code
Database links.
Business rules.
Program code.
Application.
Front-end.
User Interface.
Database
Servers
Middleware
Client
24
D
A
T
A
B
A
S
E
The Internet as Client-Server
information
Internet
Router
Router
Server
Client
Browser
request
http://server.location/page
Web Server
HTML pages
Forms
Graphics
25
D
A
T
A
B
A
S
E
HTML Limited Clients
<HTML>
<HEAD>
<TITLE>My main page</TITLE></HEAD>
<BODY BACKGROUND=“graphics/back0.jpg”>
<P>My text goes in paragraphs.</P>
<P>Additional tags set <B>boldface</B> and <I>Italic</I>.
<P>Tables are more complicated and use a set of tags for rows and
columns.</P>
<TABLE BORDER=1>
<TR><TD>First cell</TD><TD>Second cell</TD></TR>
<TR><TD>Next row</TD><TD>Second column</TD></TR>
</TABLE>
<P>There are form tags to create input forms for collecting data.
But you need CGI program code to convert and use the input data.</P>
</BODY>
</HTML>
26
D
A
T
A
B
A
S
E
HTML Output
My text goes in paragraphs.
Additional tags set boldface and Italic.
Tables are more complicated and use a set of tags
for rows and columns.
First cell
Second cell
Next row
Second column
There are form tags to create input forms for
collecting data. But you need CGI program code
to convert and use the input data.
27
D
A
T
A
B
A
S
E
Web Server Database Fundamentals
0 Request Server/Form.html
3
Client/Browser
Database
1
2 Data 3
HTML Form
Action=
“Query.asp”
Result Page
2
SQL Server
Result
1
Query
Web Server (IIS)
1
HTML
form
Form.html
2
Query
Template
+ Code
Query.asp
28
D
A
T
A
B
A
S
E
Database Example: Client Side
0 Request Server/Form.html
Server
1 Initial form
3 Results
2
29
D
A
T
A
B
A
S
E
Form Code
1
<HTML><HEAD><TITLE>Catalog Search</TITLE></HEAD>
<BODY TEXT="#000000" BGCOLOR="#ffffff">
<FORM ACTION="PetStoreSearch.asp" METHOD="Put">
<P>
<SELECT NAME="Category">
<OPTION SELECTED VALUE="Cat">Cat
<OPTION VALUE="Dog">Dog
<OPTION VALUE="Fish">Fish
</SELECT> Category</P>
<P>
<INPUT TYPE="TEXT" NAME="Color" VALUE="Black"> Color</P>
<P>
<INPUT TYPE="SUBMIT" VALUE="Search" NAME="Submit"></P>
</FORM></BODY> </HTML>
30
D
A
T
A
B
A
S
E
ASP Code Structure
Connect to the Database
2
Create the SQL
Connect to the Database/Recordset
Loop through the Recordset
Get a Field
Display it
Move to the next row
End Loop
Note: Access will create
the basic structure.
Export a query as asp.
Then edit the file.
Set objConn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
sql = " SELECT …"
rst.Open sql …
rst.MoveFirst
do while Not rs.eof
Response.Write Server.HTMLEncode(rst(”LastName"))
rst.MoveNext
Loop
31
D
A
T
A
B
A
S
E
ASP Query: Build SQL
<HTML><HEAD><TITLE>Animal Search Results</TITLE></HEAD>
<BODY>
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "PetStore","",""
sql = "SELECT AnimalID, Name, Category, Breed, DateBorn, Color, ListPrice "
sql = sql & "FROM Animal WHERE (Category = '"
sql = sql & Request.Form("Category") & "’) AND (Color LIKE '*"
sql = sql & Request.Form("Color") & "*’)"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open sql, objConn, 3, 3
%>
32
D
A
T
A
B
A
S
E
ASP Query: Create Table
<TABLE BORDER=1><CAPTION><B>ASPSearch1</B></CAPTION>
<THEAD><TR>
<TH>AnimalID</TH>
<TH>Name</TH>
<TH>Category</TH>
<TH>Breed</TH>
<TH>DateBorn</TH>
<TH>Color</TH>
<TH>ListPrice</TH>
</TR></THEAD><TBODY>
<%
On Error Resume Next
rst.MoveFirst
do while Not rst.eof
%>
<TR VALIGN=TOP>
<TD><%=Server.HTMLEncode(rst("AnimalID"))%>
<BR></TD>
<TD><%=Server.HTMLEncode(rst("Name"))%>
<BR></TD>
<TD><%=Server.HTMLEncode(rst("Category"))%>
<BR></TD>
<TD><%=Server.HTMLEncode(rst("Breed"))%>
<BR></TD>
<TD><%=Server.HTMLEncode(rst("DateBorn"))%>
<BR></TD>
<TD><%=Server.HTMLEncode(rst("Color"))%>
<BR></TD>
<TD><%=Server.HTMLEncode(rst("ListPrice"))%>
<BR></TD>
</TR>
<%
rst.MoveNext
loop
%>
</TBODY></TABLE></BODY></HTML>
33
D
A
T
A
B
A
S
E
Client-Server Data Transfer
Order Form
Order ID
1015
Customer
Jones, Martha
Order Date
12-Aug
What if there are 10,000 customers?
How much time to load the combo box?
How do you refresh/reload the combo box?
Alternatives?
34
D
A
T
A
B
A
S
E
XML: Transferring Data
Order: OrderID, OrderDate
Item: ItemID, Quantity, Cost
Item: ItemID, Quantity, Cost
Item: ItemID, Quantity, Cost
DTD: Document Type
Definition is hierarchical.
+ Repeats 1 or more
? Optional
* Repeats 0 or more
#PCDATA: parsed
character data
<!ELEMENT OrderList (Order+)>
<!ELEMENT Order (OrderID,OrderDate,Comment?,ShippingCost,Items+)>
<!ELEMENT OrderID (#PCDATA)>
<!ELEMENT OrderDate (#PCDATA)>
<!ELEMENT Comment (#PCDATA)>
<!ELEMENT ShippingCost (#PCDATA)>
<!ELEMENT Items (Item+)>
<!ELEMENT Item (ItemID,Description,Quantity,Price)>
<!ELEMENT ItemID (#PCDATA)>
<!ELEMENT Description (#PCDATA)>
<!ELEMENT Quantity (#PCDATA)>
<!ELEMENT Cost (#PCDATA)>
35
D
A
T
A
B
A
S
E
XML Data Example
<?xml version="1.0"?>
<!DOCTYPE OrderList SYSTEM "orderlist.dtd">
<OrderList>
<Order>
<OrderID>1</OrderID>
<OrderDate>3/6/2001</OrderDate>
<ShippingCost>$33.54</ShippingCost>
<Comment>Need immediately.</Comment>
<Items>
<ItemID>30</ItemID>
<Description>Flea Collar-DogMedium</Description>
<Quantity>208</Quantity>
<Cost>$4.42</Cost>
<ItemID>27</ItemID>
<Description>Aquarium Filter &amp;
Pump</Description>
<Quantity>8</Quantity>
<Cost>$24.65</Cost>
</Items>
</Order>
</OrderList>
XML: extensible markup
language
36
D
A
T
A
B
A
S
E
XML Example in Explorer
37