data & databases basic data fundamentals. data vs information l data: facts computer systems...
TRANSCRIPT
Data & Databases
Basic Data Fundamentals
Data vs Information
Data: facts
Computer systems store data. Information: facts organized for a
specific application
Information is the output of a computer system
Data
Atomic Data
Data in units that cannot be subdivided• First Name, Price, etc.
Binary Large Objects (BLOB)
Images and figures• Pictures, Cartoons
Objects
Data and associated processes• Spreadsheet tables, graphics components
Atomic Data
Most current systems store atomic data. Atomic data is typically made up of characters (letters and numbers) stored in coded groups of 8 bits called bytes.
ASCII American Standard Code for Information Interchange
EBCDIC Extended Binary Coded Decimal Interchange Code
Atomic Data
Because atomic variables aremade up of a relatively small number of codes, computers can interpret and manipulate them
Binary Large Objects
These are typically bit mapped pictures or images (such as FAXs) that are stored. Parts of these pictures have meaning (unlike atomic data). Digital pictures, video clips, stored images and signatures are BLOBs.
PIXEL blobs are usually stored as pixel codes that represent a point on a video screen
Bit Maps
1028
High Resolution
1028 x 640 = 657,920 pixelsBlack & White = 1 bit per pixel256 tone Color = 8 bits per pixel800
Image Processing
Images cannot be processed easily (other than displayed and scaled) by normal technology andusually require atttached atomicindexes. Text images can be con-verted although the software is notfully reliable.
Objects
Objects combine data and the processes that manipulate it. An example of an object is a spreadsheet table imported with the manipulation rules.
Encapsulation Formal Interface for Messages Inheritance
Object Processing
Object databases are new technologieswith immature design methodologies. They promise extensive code reuse andeasy system development if the tech-nology proves itself.
Data, Metadata and Business Rules
Metadata is data about data, the structure descriptions of the stored files. Metadata includes location, format, field name, data description, etc.
Business rules include editing and processing rules as well as automatic procedures that must be processed for accurate data.
Data Access Technology
Flat File structures.Data in one location; metadata and business rules in programs
Database Management SystemsData and metadata together; business rules in programs
Object Oriented systemsData, metadata and business rules together
Database Approach
Program 1
Program 2
Program 3
Meta-Data
Meta-Data
Meta-Data
Data
Program 1
Program 2
Program 3
Meta- Data
Data
FILE SYSTEM
DATABASE
Distribution Strategies for Databases
Centralized Data and Processing: Dumb terminal with "screen scraping".
Intelligent Terminal: Data and processing centralized; data preparation and display on remote devices.
Distributed Logic: Data storage distributed; processed at the optimal location. A version of parallel processing.
Client Server: Data (usually departmental) maintained on a server. Subsetting occurs on the server, processing on client machines.
Distributed Database: Data distributed among different locations; processing access data wherever it is located. Data may be replicated or partitioned.
Data Trends
Centralized and Client/Server systems Multi Layered architectures Objects and encapsulation Data driven organizations
Functions of a DBMSC.J. Date
Indexing Views Security Integrity Concurrency
Backup/Recovery Design Documentation Update/Query
Views
Custom representations of a database that correspond to the
needs of a class of users. Stored SELECT statements.
Views
Views Permit Maintaining a constant user interface Restricting access to specified
attributes Specifying user rights
Views3 Schema Architecture
PHYSICAL
CONCEPTUAL
LOGICALUser Views (Views or Queries)
Database administrators model for the data (E-R diagrams)
Actual data placement and structure (SQL statements)
Security
Components that limit access or actions to limit potential damage
to data.
Security Approaches
Views limit access and actions Authorization Rules identify users and
restrict actions User Defined Procedures in addition
to database security functions Encryption encode stored data Authentication positively identify users
Authorization Rules
Some DBMS products authorize actions based on specific records and functional descriptions. However, most DBMS’s limit actions on tables to one of:• Read: view but not change• Insert: read and add records• Update: read, insert and change records• Alter/Delete: read, insert, update and
delete records, change table structure
Authorization Rules
Subject Object Action Constraint
Sales Dept Cust Insert Credit < $5000
Program Ar4 Order Modify None
Terminal 12 Cust Modify Balance Due
Order Trans Cust Read None
Integrity
Components that preserve the relationship among different
related records in the database
Integrity
The relationship among records in the database
Referential Integrity Non Key Integrity Derived Conditions
Concurrency
Preventing two users from interfering with each other when they use the same information
Concurrency
LockoutRestricting access to users who could be
misled by partial transactions Versioning
Making trial updates on versions of the database and denying one if there is a data conflict.
Locks
MasterStudent Grade
00 Fred01 Anthony02 Steve03 Ivan
Program 1 locks record<exclusive>.
No other program canread the record.No program can havean active lock.
Program 2 locks record<shared>
Other programs can read, but not changerecord.No program can have an exclusive lock.
Versioning
Version 1
Time 1 Version 2
Time 2Version 3
Time 3
Commits version 3 only after changes to versions 1 and 2 have been rolled back.
Backup and Recovery
Processes to confirm and repeat transactions so that database
can be restored to a valid state after a problem.
Backup and Recovery
Backup Copies• Master• Transaction Log
Journalization• Forward Log• Backward Log
Checkpoints
DBMS Logs
MasterStudent Grade
00 Fred01 Anthony02 Steve03 Ivan
TransactionInsert Li with grade AChange Fred’s grade to A
Recover from Backup
Slow May give different answers from original
BackupTransac-
tion+ = RecoveredDatabase
DBMS Logs
MasterStudent Grade
00 Fred A01 Anthony02 Steve03 Li A
TransactionIns Li with grade AChg Fred’s grade to A
Backward LogStudent Grade
03 n/p00 Fred
Forward LogStudent Grade
03 Li A00 Fred A
DBMS Logs
MasterStudent Grade
00 Fred A01 Anthony02 Steve03 Li A
TransactionIns Li with grade AChg Fred’s grade to A10:00 Checkpoint
Backward LogStudent Grade
03 n/p00 Fred
Chkpt
Forward LogStudent Grade
03 Li A00 Fred A
Chkpt
DBMS Logs
MasterStudent Grade
00 Fred A01 Anthony02 Steve B03 Li A
TransactionIns Li with grade AChg Fred’s grade to A10:00 CheckpointChg Steve grade to B
Backward LogStudent Grade
03 n/p00 Fred
Chkpt02 Steve
Forward LogStudent Grade
03 Li A00 Fred A
Chkpt02 Steve B
Recover to Checkpoint Using Logs
ContaminatedDatabase
BackwardLog- Correct at
Checkpoint=
RecentTransactions+ Recovered
Database=
Transaction Processing
A set of computer operations required to process a single unit of work.
A transaction must conclude with the database in a valid state whether the transaction terminates correctly or abnormally
Transaction Boundaries
Set Boundary• Obtain Locks• Execute Code Modules• Evaluate Correctness
Commit or Rollback• Release Locks
Transaction BoundariesPremiere Products Example
SALESREP CUSTOMER
ORDER-PRODUCT
ORDER
PRODUCT
Place an order for a new customer with a 1500 credit limit
Transaction BoundariesPremiere Products Example
SALESREP CUSTOMER
ORDER-PRODUCT
ORDER
PRODUCT
•Insert CUSTOMER Record•Update CUSTOMER with SALESREP Foreign Key•Insert ORDER Record•Insert ORDER-PRODUCT with Foreign Keys•Update ProductOnHand in PRODUCT•Check Credit Limit
D A T A