Transcript Controlled

Database Features
Lecture 2
Desirable features in an
information system
•
•
•
•
•
•
Integrity
Referential integrity
Data independence
Controlled redundancy
Security
Privacy
Integrity
• Valid and consistent stored data
– If there is a rule that a schoolchild should be
between 4 and 18 years old, is the rule:
•
•
•
•
4<age<18 or
4<=age<18 or
4<=age<=18 or
4<age<=18?
– By keeping the rule with the data definition,
this ensures integrity.
Referential integrity
• Data has referential integrity if it is valid in
its own right and valid in relation to
another occurrence.
• E.g. a student is not a student unless
he/she is studying a course
• The course listed against the student must
exist.
– Master : detail relationship
– Implemented through Foreign Keys.
Data independence
• This is where the data is independent of the
program that uses it:
– E.g.
• Select stock_code from stock;
– This will work, regardless of the definition of the table
stock, unless
• You have no access to the table stock
• There is no column ‘stock_code’ in the table stock
– This allows us to change
• the front layers of software without changing the tables
• The tables without changing the front layers (some
recompilation may be required)
Controlled redundancy
• Controlled redundancy
– Redundant data causes problems
• E.g. staff details – addresses and phone numbers,
• If recorded separately for payroll and human
resources, then when one is changed, the other is
not.
• Bank accounts and credit card accounts.
– NO redundancy causes problems
• One server goes down, there is no other server
• In order to retrieve information, a traversal of three
tables is required – inefficient.
Security
• From theft and fraud
• Loss of confidentiality
• DBMS controls:
– Granting of different privileges to different users
• You will have SELECT access to the tables in the BUILDER
schema.
• You have owner access to your own Schema and to your team
schema.
• You may grant and revoke access to your tables to others in your
class.
–
–
–
–
Backup and Recovery
Journalling
Commit and Rollback
Encryption
Privacy
• Can be maintained through:
– Use of views
• E.g. although the staff table shows full details of
– Salary
– Direct payments to family members
– Loan repayments, etc.
• The person in charge of the staff roster only sees:
• Name, rank, qualifications, free slots (probably a
different table)
Database definition
• a computerised record-keeping system
• used by a range of users who have different
requirements
– minimal enquiries
– in-depth updating
– restructuring
• A well-implemented database will have data integrity,
data independence, controlled redundancy, security and
privacy, for all users.
Uses of a Database
• Generally used for on-line transaction
processing (OLTP)
• Data Warehouses are a hybrid of
databases which are used for On-line
analytical processing (OLAP)
File systems
• Predecessors of Database Systems
• File types:
– Sequential or serial
• Suitable for use on a serial medium – e.g. a tape.
• Access is determined by the ‘position’ of the reader on the
serial file or tape. No sharing.
– Indexed sequential
• Closest in form to relational databases
• Can be used to implement relational databases, but all of the
DBMS features must be programmed in.
– Relative
• Based on the offset of the record from the start of the file.
• Field values are not involved in keys.
File systems
• Sequential or serial
• Indexed sequential
• Relative
Database definition
• a computerised record-keeping system
• used by a range of users who have different
requirements
– minimal enquiries
– in-depth updating
– restructuring
• A well-implemented database will have data integrity,
data independence, controlled redundancy, security and
privacy, for all users.
Uses of a Database
• Generally used for on-line transaction
processing (OLTP)
• Data Warehouses are a hybrid of
databases which are used for On-line
analytical processing (OLAP)
Structure of a database
External
Schema
Conceptual
Schema
Internal
Schema
Physical
Schema
External level
• Level visible to user
• Multiple views of the system
– e.g. View an order - see limited product and
customer information
• Only the database Administrator may
access the whole database at this level
EXTERNAL SCHEMA
• Each external view is defined by means of an
external schema
• Provides definitions of each external view.
• Written in a Data Definition Language
• individual to the user
• accessed through a 3GL, a query language or a
special purpose forms or menu-based language
Conceptual level
• CONCEPTUAL - represents the entire
information content of the database
• Consists of multiple types of conceptual record.
This level preserves the data independence of
the database.
• CONCEPTUAL SCHEMA - defines each of the
various types of conceptual record, in a
conceptual Data Definition Language.
Internal level
• INTERNAL - a low-level representation of the
entire database; it consists of multiple
occurrences of multiple types of internal record.
It is the stored record, inasmuch as it contains all
but the device-specific information on the
storage of the database.
• PHYSICAL - the physical device and block
addresses for each of the records.
Mappings
• Each level maps onto adjoining levels
• conceptual / internal mapping specifies how
conceptual records and fields are represented at
the internal level
• Changes can be made in the internal level
without affecting the conceptual level
• external / conceptual mapping defines the
correspondence between an external view and
the conceptual view
DBMS - Database Management
System
• software handling access to the database
• allows both the database administrator
and all users the access to the database
to which they are entitled
How requests are processed
• User issues request (e.g. through SQL)
• DBMS intercepts and analyses request
• DBMS inspects user's external schema, external
to conceptual mapping, conceptual schema,
conceptual to internal mapping and the storage
structure definition.
• DBMS executes operations on stored database.
DATABASE ADMINISTRATOR
(DBA)
• Decide on the storage structure and
access strategy
• Liaise with the users
• Define security and integrity checks
• Define a backup and recovery strategy
• Monitor and respond to performance
Utilities used by the DBA
•
•
•
•
•
•
Load routines
Dump/Restore routines
Reorganisation routines
Statistics routines
Analysis routines
Data dictionary (containing METADATA,
which gives data descriptions and
mappings)
Relational database
• Data is independent from programs and from
other data
• Data is represented in TABLES rather than files.
(one entity corresponds to 1 table)
• Column headings are described as DOMAINS.
(i.e. attributes)
• Items of information as TUPLES or ROWS
rather than records (i.e. occurrences of the
entity)