Schema
A schema is also known as database schema. It is a logical design of the database and a database instance is a snapshot of the data n the database at a given instant of time. A relational schema consists of a list of attributes and their corresponding domains.
Types of Schemas
It can be classified into three parts, according to the levels of abstraction Physical/internal Schema Describes the database design at the physics level.
Logical/Conceptual Schema/Community User View Describes the database design at the logical level.
Sub-schemas/View/External Schema Describes different views of the database, views may be queried, combined in queries with base relation used to define other views in general, not updated freely.
Schema Architecture
Data Independence
Possibility to change the schema at one level without having to change it at the next higher level (nor having to change programs that access it at the higher level).
There are two parts of data independence
Logical Data Independence Refers to the immunity of the external schema to changes in the conceptual schema (i.e., community schema) e.g., add new record or field.
Physical Data independence Refers to the immunity of the conceptual to the conceptual schema to changes in the internal schema, e.g., addition of an index should not affect existing one..
Database Functions and Application Functions
Application Program Functions
To be programmed in application programs,
Database Functions or DBMS Functions
Supplied by the DBMS and invoked in application programs.
Database Design Phases
Transaction
One execution of a user program (executing the same programs several :Ties corresponds to several transactions). Basic unit of change as seen by the DBMS.
OLTP (On-Line Transaction Processing) applications (e.g., banking and aline systems) with multiple simultaneous users.
Functionality of DBMS
- Concurrency control 2. Backup and recovery
- Redundancy management 4. Access control
- Performance optimisation
- Metadata management
- Active features (rules, triggers)
Concurrency Control
It is responsible for ensuring correctness of competing accesses to same data. one single unit.One or more Structure Query Language (SQL) statements altogether treated as one single unit.
Correctness of data requires four desirable properties (ACID. properties) e.g., concurrency control means there should not be two simultaneous withdrawals from the same bank account or there should not be multiple reservation of the same airplane seat.
sBackup and Recovery
- Facilities for recovering from hardware and software failures.
- If the computer system fails during a complex update program database must be resumed, where it was interrupted so that its full effect is recorded in the database.
- In a multiuser environment, it is more complex and important.
Redundancy Management
Redundancy means storing several copies of the same data. Redundant entries are frequent in traditional file processing; a goal of the database approach was to control redundancy as much as possible.
Problems with redundancy includes waste of storage space, duplication of effort to perform a single conceptual update, danger of introducing inconsistency, if multiple updates are not coordinated.
Access Control
Responsible for enforcing security and authorisation (e.g., who can create new bank accounts) and data (e.g., which bank accounts can I see).
It is all about who accesses what data, to do what, when, from where etc.
Some examples of access privileges are as follows
- To create a database
- To authorise (grant) additional users to access the database, access some relations, create new relations and update the database.
- To revoke privileges.
Key Points
- In a multiuser database, access control is mandatory g., for confidentiality.
- Vitalriodus ways to access data (e.g., read only, read and update).
- The data dictionary holds information about users and their access privileges
(e•g., name and password).
Performance Optimisation
Performing physical reorganisations to enhance performance e.g., adding index, dropping index, sorting file.
Performance optimisation is made possible by physical data independence and high level data models with user program which can be optimized through DBMS software.
Metadata Management
Metadata means data about data. is maintained in a special database, which we can system catalog or data dictionary. It involves storing of information about other information. With different types of media being used, refrerence to location of the data can allow management of diverse repositories.
Active Features
Data objects, database statistics, physical structures and access paths, user access privileges etc, are active features of DBMS.
Types of Database Model
The database model can be of three types as given below.