Transcript Database

A E-R Model for
Online Flower Store
Product
Code
Occasion
code
Description
Unit price
1
Name
Occasion
Picture
Products
description
address
N
Receivers
1
M
M
Orders
Customer ID
1
Order Date
M
M
address
Order Number
Order lines
M
Buyers
1
Credit card
Customer ID
Product
Code
quantity
Order
Number
Name
A Simplified E-R Model
Product
Code
Description
Occasion code
Unit price
Occasion
Picture
Products
1
Receiver Name
N
M
Receiver address
Order Number
Credit card #
M
M
Order lines
1
Order Date
Orders
address
M
Product
Code
quantity
Order
Number
1
Customer
Name
Customer
Password
Phone Number
Simplified Tables

PRODUCTS




USAGE



Product_Code (Primary Key)
Product_Name
Unit_Price
Product_Code (Concatenated Key)
Occasion (Concatenated Key)
CUSTOMER




Customer_Name(Primary Key)
Password
Address
Phone_Number
Tables

ORDERS









Order_Number (Primary Key)
Order_Date
Customer Name (Foreign Key)
Receiver Name
Receiver Address
Credit_Card_Type
Credit_Card_Num
Credit_Card_Expiry
ORDERLINE



Order_Number (Concatenated Key)
Product_Code (Concatenated Key)
Quantity
Functional Dependence

PRODUCT (3NF)
Product_Code  Product Name,Unit Price


USAGE (BCNF)
CUSTOMER (3NF)
CustomerID  Name, Address, password, phone number

ORDERS (2NF)
Order Number  CustomerID, Order Date, Receiver Name,…
CreditCardNum  CreditCardExpiry (transitive dependence)
Receiver Name  Receiver Address (transitive dependence)

ORDERLINE (3NF)

ProductNumber + OrderNumber  Quantity
Transaction Processing
Transaction




A transaction is a sequence of steps
that constitute some well-defined
business activities
Transaction boundaries - the logical
beginning and end of transactions
Commit changes for successful
transactions
Reject changes for aborted transactions
Embedded SQL





SQL can be embedded in host languages :
PL/I, COBOL, etc.
SQL statements are prefixed by EXEC SQL
Use EXEC SQL DECLARE to define tables,
views, and cursors
In SQL, use prefix “:” to reference host
variables
Return feedback information through SQLCA
SQLCA


SQLCA
SQL Communication Area
SQLCODE 0 success, +100 no data found,
<0 error
EXEC SQL INCLUDE SQLCA
.....
IF SQLCODE < 0 THEN
....
Reference: C.J. Date, A Guide to DB2 , ch.1011.
Example of Embedded SQL

DCL GIVENS#
CHAR(5) ;
DCL RANK
FIXED BIN(15) ;
DCL CITY
CHAR(15) ;
EXEC SQL DECLARE S TABLE
(S# CHAR(5) NOT NULL,
SNAME CHAR(20),
STATUS SMALLINT,
CITY CHAR(15) ;
EXEC SQL INCLUDE SQLCA ;
GET LIST (GIVENS# ) ;
EXEC SQL SELECT STATUS, CITY
INTO :RANK, :CITY
FROM S
WHERE S# = :GIVENS# ;
IF SQLCODE = 0 THEN
PUT SKIP LIST (RANK, CITY);
Example of Embedded SQL



Update:
EXEC SQL UPDATE S
SET STATUS = STATUS + :RAISE,
WHERE CITY = ‘LONDON’ ;
Delete:
EXEC SQL DELETE
FROM S
WHERE STATUS = NULL ;
Insert:
EXEC SQL INSERT
INTO S (S#, SNAM E, STATUS, CITY)
VALUES(:NEWS#, :NEWSNAME,
:NEWSTATUS,'LONDON’) ;
Using Cursors for Sequential
Access

Declare a cursor and use it as a sequential file of the
query results
EXEC SQL DECLARE X CURSOR FOR
SELECT S#, SNAME, STATUS
FROM S
WHERE CITY = :PCITY
FOR UPDATE OF STATUS ;
GET LIST (PCITY, GIVENINC);
EXEC SQL OPEN X ;
DO WHILE ( SQLCODE = 0 )
EXEC SQL FETCH X INTO :PS#, :PSNAME, :PSTATUS;
PUT SKIP LIST(PSNAME, PS#, PSTATUS);
EXEC SQL UPDATE S
SET STATUS = STATUS + :GIVENINC
WHERE CURRENT OF X
END;
EXEC SQL CLOSE X ;
Integrity Control in SQL

Change supplier # of supplier SX from SX to SY in
both tables S and SP.
TRANEX: PROC OPTIONS (MAIN) :
EXEC SQL WHENEVER SQLERROR GO TO UNDO;
GET LIST(SX, SY);
EXEC SQL UPDATE S
SET S# = :SY
WHERE S# = :SX;
EXEC SQL UPDATE SP
SET S# = :SY
WHERE S# = :SX;
EXEC SQL COMMIT;
GO TO FINISH;
UNDO:
EXEC SQL ROLLBACK;
FINISH: RETURN;
END TRANEX;
Desired Transaction Properties




Atomicity -- All operations of a transaction must
be completed. If not, the transaction is aborted.
Durability -- When a transaction is completed,
the database reaches a consistent state which
cannot be lost.
Serializability -- The concurrent transaction are
treated as though they were executed in serial
order.
Isolation -- The data used during the execution
of a transaction cannot be used by a second
transaction until the first one is completed.
Concurrent Transactions

When transactions are interleaved, errors will occur in
updating unless the DBMS has features to prevent
interference between transactions
1
CUST
A
BAL
100
GET CUSTA
A
100
GET CUSTA
A
100
BAL : BAL - 50
A
BAL : BAL + 25
50
A
STORE CUSTA
3
2
125
STORE CUSTA
A
50
A
125
4
Problems with Concurrent
Transactions



Lost updates - T2 is executed before
T1 is committed
Uncommitted data -- T1 is rolled back
after T2 accessed the uncommitted
data
Inconsistent retrievals - - T1 calculates
some summary data while T2 is
updating the data
Concurrency Control - the
Scheduler



Establish the order in which the operations in
concurrent transactions are executed without
causing inconsistency
Locking
Guarantee exclusive use of a data item to a
current transaction
Timestamping
Assign a global unique timestamp to each
transaction. Execute operations in timestamp
order.
Resource Locking

Lock granularity


Lock Types



Database level, Table level, Page level, Row level,
Field level
Binary locks -- locked ,Unlocked
Shared/Exclusive Locks -- Unlocked, Shared
(Read) lock, Exclusive (Write) lock
Two-Phase Locking


A growing phase
A shrinking phase
Deadlock



Deadly embrace
Deadlock prevention:lock all required records
at beginning of transaction
Deadlock resolution:back out of one of
transactions and start again
User B
User A
Lock X
X
Y
Wait for Y
Lock Y
Wait for X
Optimistic vs. Pessimistic Locking

Optimistic locking




Assume the majority of database operations do
not conflict.
Process each transaction in three phases: read,
validation, and write.
Less restriction, may repeat process
Pessimistic locking



Assume the conflicts most likely will happen.
Lock, process, and unlock.
More restriction, no repeat process
Database Recovery


Database may be damaged or lost because of
some system failure. The DBMS must
provide mechanisms for restoring a database
quickly and accurately after loss or damage.
System failures:
machine failures
disk head crashes
program bugs
incorrect operation
Database Recovery
DBMS
Database
(current)
Transaction
log
Database
(backup)
Database
change
log
before image
after image
Basic Recovery Facilities




Backup facilities
Provide periodic backup copies of the entire database
Journalizing facilities
Maintain an audit trail of transactions and database
changes
Checkpoint facility
Periodically suspends all processing and synchronizes its
files and journals
Recovery manager
Allows the DBMS to restore the database to a correct
condition and restart processing transactions
Recovery via Restore/Rerun





Restore the latest backup copy
Reprocess the day’s transactions (up to the
point of failure) against the backup copy of
the database
Simple procedure
Time to reprocess transactions may be
prohibitive
Sequencing of reprocessed transactions may
be different from the original
Recovery via
Roll Back/Roll Forward
New
Databas
e
with
changes
Roll Back
(Removing Database Changes)
Undo
Old
Database
without
changes
Before
images
Old
(saved)
Roll Forward
(Repeating Database Changes)
Database
without
changes
Redo
After
images
New
Database
with
changes
Recovery Strategies




Aborted transactions (i.e. communication interruption)
Backward recovery
Incorrect data
Backward recovery or add compensation transactions
System failure (database is not damaged)
Restart from the most recent checkpoint before the
system failure, roll forward.
Database destruction (database damaged)
Restore backup copy.
Forward recovery to the check point before the loss
occurred.
Reprocess transactions after the check point.
Security Control

Security refers to the protection of data
against unauthorized disclosure,
alteration, or destruction



Physical security
Password security
Authentication schemas: biometric devices,
smart card
Security Control





Views or subschemas
User-defined procedures
Authorization rules
Audit trails
Data encryption
Authorization Rules


Grant select on Staff to User01
Revoke update on Staff from User02
Authorization rules
Subject
Object
Action
Constraint
Sales
department
Customer
record
Insert
Credit limit
<= 5000
Order
transactions
Customer
record
Read
None
Database Administration


A critical success factor in managing the
data resource in an organization
An indication of top management’s
commitment to data resource
management
DA and DBA


Data Administrator (DA)
Responsible for controlling the overall corporate data
resource, both computerized and non-computerized.
Strong managerial orientation with company-wide
scope.
Database Administrator (DBA)
Responsible for the control of the centralized and
shared database.
Tends to be more technically oriented and has a
narrower, DBMS-specific scope.
DBA Placement


Should not be organizationally below any group on
which it imposes restrictions.
Should not be more than one level above the
organizations with which it interfaces.
V ic e P r e s id e n t
D a ta P r o c e s s in g
DBA
M anager
S y s te m s
M anager
O p e r a tio n s
M anager
P r o g r a m m in g
Desired DBA Skills
Technical
Managerial






Broad business
understanding
Coordination skills
Analytical skills
Conflict resolution skills
Communication skills
Negotiation skills






Broad data processing
background
Systems Development Life
Cycle
Structured methodologies
Database Life Cycle
Database design and
modeling skills
Data dictionary management
DBA’s Managerial Role





Support the end-user community, resolve conflicts
Enforce policies, procedures, and standards for data creation,
usage, distribution, and deletion within the database.
Control data security, privacy, and integrity
Data are protected, reconstructable, auditable, tamperproof.
Users are identifiable, authorized, monitored.
Plan, test, and Implement data backup and recovery
Database security officer (DSO), Disaster management
Ensure data be distributed to the right persons at the right time
in the right format.
DBA’s Technical Role






DBMS and utilities selection, evaluation ,and installation
Design and implementation of databases and applications
Testing and evaluation of databases and applications
Operation of the DBMS, utilities, and applications
System support, performance monitoring and tuning, backup
and recovery, security auditing and monitoring
Training and supporting users to use DBMS
Maintenance of the DBMS, utilities, and applications
Storage reorganization, software upgrade, data migration and
conversion
Database Administration Tools


Data Dictionary
Stores the definition of data characteristics and
relationships.
May be integrated or stand-alone, active or passive.
A tool for information resource management.
The basis for monitoring database use and the
assignment of access rights to the database users.
Support data analysis-and-design activities
CASE Tools
Database Administration Tools

The Data Dictionary

Different types of data dictionaries:
 An
integrated data dictionary is included with
the DBMS, while a stand-alone data dictionary
comes from a third-party vendor.
 An active data dictionary is automatically
updated by the DBMS, while a passive data
dictionary requires a batch process to create
and update the dictionary.
Database Administration Tools

CASE Tools


CASE -- Computer-Aided Software Engineering
It provides an automated framework for the
Systems Development Life Cycle.



Front-end CASE tools provide support for the planning,
analysis, and design phases.
Back-end CASE tools provide support for the coding and
implementation phases.
It is based on the use of structured methodologies
and powerful graphical interfaces.
Database Administration Tools

CASE Tools

Benefits of CASE tools
A
reduction in development time and costs
 The automation of the SDLC
 Standardization of the systems development
methodologies
 Easier maintenance of application systems
developed with CASE tools
 Improve communications among the DBA,
applications designers, and the end users.
Database Administration Tools

CASE Tools
A CASE tool keeps track of all objects
created by the systems designer in the
data dictionary.
 Some CASE tools provide interfaces that
interact with the DBMS.
 The CASE tool integrates all systems
development information in a common
repository.

Database Administration Tools

Commercial CASE Tools

Excelerator from Intersolv, Inc. provides five
components:





Graphics designed to produce structured diagrams as
data flow diagrams and E-R diagrams.
Screen painters and report generators to produce the
information system’s input/output formats.
An integrated repository for storing and crossreferencing the system design data.
An analysis segment to provide a fully automated check
on system consistency, syntax, and completeness.
A program document generator.
Database Administration Tools

Commercial CASE Tools

ERwin by LogicWorks
 It
produces fully documented E-R diagrams
that can be displayed at different abstraction
levels.
 It is able to produce detailed relational designs.

Major relational DBMS vendors, such as
ORACLE, provide fully integrated CASE
tools for their own DBMS software as well
as for RDBMSs supplied by other vendors.
Future Trend



The development of distributed databases may force
an organization to decentralize the dataadministration function further.
The introduction of an object-oriented DBMS is very
likely to add more coding in the DBA’s data
modeling and design activities, thus expanding and
diversifying the DBA’s job.
The rapid spread of microcomputers and local area
networks tends to diffuse operational control over
data, thus making centralized data administration
more difficult.