DBMS

Database:
It is a store of integrated data capable of being directly addressed for multiple uses.
It is an organized collection of data.

Database Management System (DBMS):
DBMS is a collection of inter-related data and a set of programs to access those data.
It is a software that determines how data must be structured to produce the user's view.

DBMS is an interface between the application program and the OS to access or manipulate the database.
Its objective is to provide a convenient and effective method of defining, storing, and retrieving the information contained in the database.

Implications Of The Database Approach

1-      Potential for enforcing standards
the database approach permits the DBA to define and enforce standards among database users in a large organization.

a.       crucial for the success of database applications in large organizations

b.      standards refer to data item names, display formats, screens, report structures, meta-data (description of data) etc.

2- Reduced application development time
designing and implementing a new database from scratch may take more time than writing a single specialized file application

–        incremental time to add each new application is reduced.

3- Flexibility to change data structures
it may be necessary to change the structure of a database as requirements change

database structure may evolve as new requirements are defined.
4- Availability of Up-To-Date Information
A DBMS makes the database available to all users

very important for on-line transaction systems such as airline, hotel, car reservations.
5- Economic Of Scale
The DBMS approach permits consolidation of data and applications, thus reducing the amount of wasteful overlap between activities of data-processing personnel in different projects or departments.

By consolidating data and applications across departments wasteful overlap of resources and personnel can be avoided.

Uses of Database

Databases for Businesses

• The business world depends on databases 24 hours a day/seven days a week. Inventory, order processing, payroll, accounting, shipping and transportation routing are often tracked within a main database that keeps the company functioning.

Databases for Educational Institutions

• From elementary schools to colleges, educational institutions have used databases to keep track of students, grades, transfers, transcripts and other student data. There are even specialized database packages geared toward schools and colleges.

Databases for Non-Profit Organizations

• Like businesses and educational institutions, non-profit organizations must have a system to keep track of information. Many charities and other non-profit groups use a database for keeping track of donations, volunteers, hours served in the community, clients helped and other information related to the organization.

Databases for Household and Family Management

• The database also has a home in household and family management for many individuals and families. Many individuals/families use a database to keep track of family birthdays, bills and expenses within a home; addresses of friends and relatives; movie/DVD collections; and other lists.

Databases In Use Everyday

• Each time you make a purchase and the sales clerk asks for a your address or ZIP code, your information is kept and stored in a customer database. These collections of data are used to send mailings of special offers, discounts and other deals.

DATABASE ADMINISTRATOR (DBA)

•          A database administrator (short form DBA) is a person responsible for the installation, configuration, upgrade, administration, monitoring and maintenance of databases in an organization.

Responsibilities of DBA

•          Installing and upgrading the database server and application tools

•          Allocating system storage and planning future storage requirements for the database system

•          Creating primary database storage structures (tablespaces) after application developers have designed an application

•          Creating primary objects (tables, views, indexes) once application developers have designed an application

•          Modifying the database structure, as necessary, from information given by application developers.

•          Enrolling users and maintaining system security

•          Ensuring compliance with database vendor license agreement

•          Controlling and monitoring user access to the database

•          Monitoring and optimizing the performance of the database

•          Planning for backup and recovery of database information

•          Maintaining archived data

•          Backing up and restoring databases

•          Contacting database vendor for technical support

•          Generating Various reports by querying from database as per need.

ARCHITECTURE OF DBMS

Three level architecture:

• External level or view level
• Conceptual level or logical level
• Internal level or storage level or physical level

Objective: Separate each user’s view of the database from the way it is physically represented.

Three Level Architecture

External Level:

•        It is the user’s view of the database.

•        It consists of a number of different external views of the DB.

•        It describes part of the DB for particular group of users.

•        It provides a powerful and flexible security mechanism by hiding parts of the DB from certain users. The user is not aware of the existence of any attributes that are missing from the view.

•        It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.

·          The external view is computed when accessed.  It is not stored.

·         A single database can have any number of views at the external level.

·         It simplifies the interaction of the user with the system.

Conceptual Level:

•        It is also referred to as the logical level.

•        It represents the logical structure of the entire database as seen by DBA.

•        It represents what data is stored in the database.

•        It represents the relationships among the data.

•        It is the complete view of the data requirements of the organization, independent of any storage consideration.

•        It represents:

-          entities, attributes, relations

-          constraints on data

-          semantic information on data

-          security, integrity information

•        It supports each external view: any data available to a user must be contained in, or derivable from the conceptual level.

•        It hides details of the physical level.

Internal Level:

•        It is the physical representation of the DB on the computer.

•        It represents how the data is stored in the database.

•        It is the physical implementation of the DB to achieve optimal run–time performance and storage space utilization.

•        It covers:

-           Storage space allocation for data and indexes

-           Record description for storage

-           Record placement

-           Data compression, encryption

 

Objectives of the three level architecture

The objective of the three level architecture is:

• To separate each user's view of the database from the way the database is physically represented.
• Each user should be able to access the same data but have a different customized view of data.
• Users should not have to deal directly with physical database storage details.
• To enable the DBA to change the database storage structures without affecting the user's view.
• The internal structure of the database should be unaffected by changes to the physical aspects of storage such as change over to a new storage device.
• DBA should be able to change the conceptual structure of the database without affecting all the users.

DATABASE SCHEMA AND INSTANCE

The collection of information stored in the database at a particular moment is called an instance of the database.

The overall design of the database is called the database schema.

A database instance is the actual data stored in a database at a particular moment in time. This includes the collection of all the data in the database.

A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk. The instance manages its associated data and serves the users of the database.

Every running database is associated with at least one database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.

Database schema is the structure of a database system, described in a formal language supported by the database management system (DBMS).

Schema gives the names of the entities and attributes. It specifies the relationship among them.

Example of Database Schema:


Database Instance corresponding to the above schema:

Types of schema:

• External schema

• Conceptual schema

• Internal schema

 

=================================================================

KEYS

A key is an attribute (also known as column or field) or a combination of attribute that is used to identify records.

A key is a set of one or more columns whose combined values are unique among all occurrences in a given table.

Types of keys:

• ·         Candidate
• ·         Primary
• ·         Super
• ·         Composite
• ·         Foreign

Candidate keys (or Alternate key) are those attributes of a relation which have the properties of uniqueness and irreducibility.

A candidate is a subset of a super key.

Every table must have at least one candidate key but at the same time can have several.

Uniqueness: No legal values of R ever contains two distinct rows with the same value for K.

Irreducibility: No proper subset of K has the uniqueness property.

Super key has the uniqueness property but not necessarily the irreducibility property.

A candidate key is a special case of super key.

Primary key is an attribute or a set of attributes that uniquely identifies a row or record in a table.

A primary key is a candidate key that is most appropriate to be the main reference key for the table.

The primary key must contain unique values, must never be null and uniquely identify each record in the table.

The primary attributes are required to be non-null.

Properties of Primary Key:

Stable

Minimal

Definitive

Accessible

Composite Keys:

A composite key consists of more than one field to uniquely identify a record.

Example:  firstName + lastName

Foreign Keys are the attributes of a table which refers to the primary key of some other table.

Foreign keys are used to link together two or more different tables.

The table containing the reference is called the target table.