Transcript The functions a database should provide

Database Function
What should every database
do?
Copyright © 1998-2008 Curt Hill
Functions of a Database
• According to EF Codd a database
should provide the following
services:
•
•
•
•
•
•
•
•
•
Data storage and retrieval
User-accessible catalog
Transaction support
Concurrency support
Recovery services
Authorization services
Support for data communication
Integrity services
Data independence services
Copyright © 1998-2008 Curt Hill
Data storage and retrieval
• While hiding the file structure, users
may:
– Add
– Delete
– Update in place
• Duh!
Copyright © 1998-2008 Curt Hill
Queries
• A mechanism where a question is
asked of the database and it
provides an answer:
– Not in natural language
– Usually in Structured Query Language
• “Sequel”
– Usually state what is wanted rather
than how to get it
• The Data Manipulation Language
usually includes both query and
update facilities
Copyright © 1998-2008 Curt Hill
User-accessible catalog
• The database itself knows the shape
of the data
–
–
–
–
Available records and fields
Attributes of fields
Meaning of the field
Relationships with other fields or
records
– Applications that access
• The administrator is the most
frequent user of this info
Copyright © 1998-2008 Curt Hill
Transaction Support
• The unit of update is a transaction
– All or nothing
– No partial updates are allowed
• The DBMS must guarantee the
integrity of a transaction, even in the
face of:
– Program errors
– Incorrect input
– System crashes
Copyright © 1998-2008 Curt Hill
Entering an order
• Gain access to the system or subsystem
• Find the record of the item requested
• If there are enough to satisfy the request, then
reserve them and decrement the number in
stock
• This reservation is termed committed – no longer
available
• If there are not enough mark this item as back
ordered
• If the request brings the item below the reorder
point generate a transaction that sets in motion
an order
• Initiate a shipping order
• Generate an invoice
Copyright © 1998-2008 Curt Hill
Transactions: ACID
• Atomic
– Transaction perceived to be indivisible
• Consistent
– Transforms database from one consistent
state to another
• Isolated
– Understandable without regards to any
other agents
• Durable
– Once committed permanence is
guaranteed even with system crashes
Copyright © 1998-2008 Curt Hill
Concurrency support
• A mechanism is needed to prevent
two concurrent applications from
both getting the last airline seat
• Usually a locking protocol
– A transaction may lock a table or
fragment of the table
– Two types
• Shared for queries
• Exclusive for updates
• Concurrency example
Copyright © 1998-2008 Curt Hill
Concurrency example
• Two agents ask how many widgets
there are
– Both are told 20
• Both order 15
– One gets 15
– The other is told they are backordered
• As soon as the 15 are committed
everyone else is told there are 5
Copyright © 1998-2008 Curt Hill
Recovery services
• Many dangers:
– Power failure
– Hardware failure
– Software failure
• How do we get the database back to
a consistent state?
– Backups
– Journaling
Copyright © 1998-2008 Curt Hill
Recoveries
• Forward
– Use if database is destroyed
– Restore from backup
– Reapply transactions from the
log/journal
• Backward
– Use if database is inconsistent
– Process log backward
– Unroll transactions that may have been
incomplete
Copyright © 1998-2008 Curt Hill
Recovery with checkpoints
• At some point delay new
transactions until all current
transactions finish
• Save the database
• This is the point from which a
backup may be made
Copyright © 1998-2008 Curt Hill
Authorization services
• Verify that those who access the
data are authorized
• Encryption
• Views or sub-schemas
• Authorizations
• User defined procedures
• Privacy
Copyright © 1998-2008 Curt Hill
Encryption
• Transparent to user
• Data is encrypted before write and
decrypted before read
• Securely prevents access from any
other program than DBMS
• Does the file system perceive the
database as one or many files?
Copyright © 1998-2008 Curt Hill
Views
• Restrict view to the authority of the
viewer
• If user is not authorized to change or
see a value, remove from the view
• If they may view but not change,
mark as read only
Copyright © 1998-2008 Curt Hill
Authorization rules
• Subject
• Often identified by a login or password
• Object
• Set of database entities that are subject to
access
• Action
• View, update, delete, etc
• Constraint
• Limitations on what can be done
• Manager may only change pay of own
department workers
Copyright © 1998-2008 Curt Hill
User Defined Procedures
• When a particular action is
performed a subroutine is executed
• Subroutine is user-written
• Vendor supplies exits to these
subroutines in the DBMS
• Checks validity or logs information
customized for the organization
Copyright © 1998-2008 Curt Hill
Privacy
• A query should not violate the
privacy of individuals
• A query may be attempting to find a
statistical value
– Find the mean salary of VCSU math
professors
• It may be tailored to violate privacy
– Find the mean salary of VCSU math
professors with a CS master’s degree
Copyright © 1998-2008 Curt Hill
Support for data
communication
• Access should not be limited to a
single machine
• From where
–
–
–
–
–
Telephone
LAN
Intranet
WAN
Internet
Copyright © 1998-2008 Curt Hill
Integrity services
• Guaranteeing that an item satisfies
integrity constraints
• Data type
– Integer must be numeric
• Legal values
– A month of 13 is numeric but not legal
– Legal values do not always have to be
contiguous
• Format
• Key constraints
Copyright © 1998-2008 Curt Hill
Format Rule Example
• A product serial number must have:
–
–
–
–
–
First two characters are digits
Next three digits must be alphabetic
Next must be a hyphen
Next must be six digits
Last character is an R or M
• Do not confuse with display
formatting
– Tele is stored as 8457103 and shown as
845-7103
Copyright © 1998-2008 Curt Hill
Key Constraints (1 of 3)
• Candidate key
– A minimal collection of one or more
fields that uniquely identify a record
– Removing any makes the key not
unique
– This may be one field or every field in
the record
• Multiple candidate keys may be
possible
Copyright © 1998-2008 Curt Hill
Key Constraints (2 of 3)
• Primary key
– Any candidate key designated by the
DBMS
– Inserting a record with an identical
primary key must be rejected by the
DBMS
– Entity integrity
• Primary key may not be duplicate or null
Copyright © 1998-2008 Curt Hill
Key Constraints (3 of 3)
• Foreign key
– A field in a record that matches a
primary key in another table
– Referential integrity
• A record with a foreign key must have a
corresponding record in the other table,
where the key is primary
Copyright © 1998-2008 Curt Hill
Key Example
• Three tables:
– Students, primary key is NAID
– Courses, primary key is department,
course, section
– Grades, primary key is NAID,
department, course, section
• NAID is foreign key as is the other three
• Must not allow a record where the
foreign keys reference non-existing
records
Copyright © 1998-2008 Curt Hill
Data Independence
• Users should be unaffected by
changes in the database structure
• Physical data independence
– Changing the physical structure does
not affect the user
– Such as inserting or removing an index
• Logical data independence
– Adding/removing a field does not affect
any program that does not use the field
– Changing the format of a field should
also be painless
Copyright © 1998-2008 Curt Hill
Are there others?
• Every database offers other
services
• However these services do not
distinguish a database from other
applications
• These may include
– Accepting SQL or other query language
– Report generation
Copyright © 1998-2008 Curt Hill