DBMS

Data: The collection of related information it is called data

Database: The organize collection of information related data it is called database

DBMS: DBMS consist of collection of inter-related data & set of program to access that data.

DBMS Software available in the market

• Oracle [oracle Enterprise]
• Ms Access [Microsoft Corporation ]
• FoxPro [Fox base Corporation]
• SQL Server (Structure Query Language)
• Sybase
• DB2 [ IBM Corporation ]
• My SQL (Linux base)

Database Applications:

§  Banking: all transactions

§  Airlines: reservations, schedules

§  Universities: registration, grades

§  Sales: customers, products, purchases

§  Manufacturing: production, inventory, orders, supply chain

§  Human resources: employee records, salaries, tax deductions

Primary Goal of DBMS

To provide environment that is convenient & efficient to use in retrieving & storing database information.

Database Management Systems

1. A database management system (DBMS), or simply a database system (DBS), consists of

§  A collection of interrelated and persistent data (usually referred to as the database (DB)).

§  A set of application programs used to access, update and manage that data (which form the data management system (MS)).

2. The goal of a DBMS is to provide an environment that is both convenient and efficient to use in

§  Retrieving information from the database.

§  Storing information into the database.

3. Databases are usually designed to manage large bodies of information. This involves

§  Definition of structures for information storage (data modeling).

§  Provision of mechanisms for the manipulation of information (file and systems structure, query processing

§  Providing for the safety of information in the database (crash recovery and security).

§  Concurrency control if the system is shared by users.

Early Information System (File Processing System) OR Need For DBMS

§  Early information system or file processing system is a system of such time when there is no concept of the database management system is developed at that time the operating system provide the text file to the user to store the data.

§  User use this text file and programmer written a program in a different programming language like ‘C’, ‘Basic’ to access that text file separately and linked it.

§  Here programmer has to write each program individually. And this way the data access is possible but here there is some limitation so the concept of database management system is developed.

Limitation of File Processing System

§  Data Redundancy:-

Since different programmers create the files and application programs over a long period, the various files are likely to have different formats and the programs may be written in several programming languages. Moreover, the same information may be duplicated in several files, this duplication of data over several files is known as data redundancy.

E.g. The address and telephone number of a particular customer may appear in a file that consists of saving- account records and in a file that consists of checking account records. This redundancy leads to higher storage & excess cost also leads to inconsistency discussed in the next.

§  Data Inconsistency:-

The various copies of same data may no longer agree i.e. various copies of the same data may contain different information.

E.g. A changed customer address may be reflected in savings-account records but not elsewhere in the system.

§  Difficulty in accessing the data:-

In a conventional file processing system it is difficult to access the data in a specific manner and it is require creating an application program to carry out each new task.

E.g. suppose that one of the bank officers needs to find out the names of all customers who live within a particular postal-code area. We ask the officer of data-processing department to generate such a list.

§  We have 2 choices:
- List all customer names & manually do the information required
- Ask the data processing dept. to have system programmer to write necessity application program Because the designers of the original system did not anticipate this request, there is no application program on hand to meet it.

§   Data Isolation:-

Because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult.

§  Integrity problem:-

The data stored in the database must satisfy certain types of consistency constraints. E.g. The balance of a bank account may never fall below a prescribed amount (say, ICICI 2500/- ). Developers enforce these constraints in the system by adding appropriate code in the various application programs. However, when new constraints are added, it is difficult to change the programs to enforce them. The problem is compounded when constraints involve several data items from different files.

§  Atomicity problems:-

A computer system, like any other mechanical or electrical device, is subject to failure. In many applications, it is crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure.

E.g. Before computer format, we require to have a backup first. It is difficult to ensure atomicity in a conventional file processing system.

§  Concurrent-access anomalies :-

For the sake of overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. In such an environment, interaction of concurrent updates may result in inconsistent data.

E.g. Consider bank account A, containing $500. If two customers withdraw funds (say $50 and $100 respectively) from account A at about the same time, the result of the concurrent executions may leave the account in an incorrect (or inconsistent) state. Suppose that the programs executing on behalf of each withdrawal read the old balance, reduce that value by the amount being withdrawn, and write the result back. If the two programs run concurrently, they may both read the value $500, and write back $450 and $400, respectively. Depending on which one writes the value last, the account may contain$450 or $400, rather than the correct value of $350. To guard against this possibility, the system must maintain some form of
supervision. But supervision is difficult to provide because data may be accessed by many different application programs that have not been coordinated previously.

§  Security Problems :-

Not every user of the database system should be able to access all the data.

E.g. In a bank system, payroll personnel need to see only that part of the database that has information about the various bank employees. They do not need access to information about customer accounts. But, since application programs are added to the system in an ad hoc manner, enforcing such security constraints is difficult.

Data Abstraction

§  The major purpose of a database system is to provide users with an abstract view of the system.

§  The system hides certain details of how data is stored and maintained

§  Complexity should be hidden from database users.

§  To simplify user interaction with DBMS.

Types of abstractions as follow.

• Physical level
• Logical level
• Conceptual level / View level

Physical level is the lowest level in the DBMS & conceptual level is highest level in the DBMS.

1. Physical level: It describes how a record (e.g., customer) is stored.
Features:
a) Lowest level of abstraction.
b) It describes how data are actually stored.
c) It describes low-level complex data structures in detail.
d) At this level, efficient algorithms to access data are defined.

2. Logical level: It describes what data stored in database, and the relationships among the data.
Features:
a) It is next-higher level of abstraction. Here whole Database is divided into small simple structures.
b) Users at this level need not be aware of the physical-level complexity used to implement the simple structures.
c) Here the aim is ease of use.
d) Generally, database administrators (DBAs) work at logical level of abstraction.

3. View level: Application programs hide details of data types. Views can also hide information (e.g., salary) for security purposes.
Features:
a) It is the highest level of abstraction.
b) It describes only a part of the whole Database for particular group of users.
c) This view hides all complexity.
d) It exists only to simplify user interaction with system.
e) The system may provide many views for the whole system.

Instances and Schemes

§  Databases change over time as information is inserted & deleted.

§  The information in a database at a particular point in time is called an instance of the database.

§  The overall design of the database is called the database scheme.

§  A database schema correspondence to the programming language type definition. The value of variable is correspondence to the instance of data base schema.

§  There are several schemes, corresponding to levels of abstraction:

1.      Physical scheme

2.      Conceptual scheme

3.      Subschema (can be many)

§  Physical Schema 

·         It describes the database designed at physical level.

§  Logical Schema

·         It describes the database designed at logical level.

§  Sub Schema

·         A database may also have the view level called sub schema.

·         Logical schema is most important in terms its effect on application program since programmer by using the logical schema the physical schema is hidden behind the logical schema.

Data Independency

§  Ability to modify the schema definition in one level without affecting the schema definition in the next level is called data independence.

§  There are two level of data independency.

1)      Physical Level [Physical Data Independency]

·         The ability to modify physical schema without affecting logical schema is called as physical data independency.

·         Modification at the physical level is occasionally necessary to improve performance.

2)       Logical Level [Logical Data Independency]

·         The ability to modify logical schema without causing application program to be re-written is called as logical data independency.

·         Modification at the logical level is necessary when ever the logical structure of the data base alter.

·         Logical data independence is more difficult to achieve than physical data independency because application program are heavily depended upon logical structure of the data.

Data Models

Data models are a collection of conceptual tools for describing data, data relationships, and data semantics and data constraints. There are three different groups:

(A) Object-based Logical Models.

(B) Record-based Logical Models.

(C) Physical Data Models.

A) Object-based logical models:

§  Describe data at the conceptual and view levels.

§  Provide fairly flexible structuring capabilities.

§  Allow one to specify data constraints explicitly.

§  Over 30 such models, including

·         Entity-relationship model.

·         Object-oriented model.

·         Binary model.

·         Semantic data model.

The E-R Model

The entity-relationship model is based on a perception of the world as consisting of a collection of basic objects (entities) and relationships among these objects.

Entity: - An entity is a thing or an object. E.g. book, student, college etc…

Entity sets: - An entity set is a set of entities that have some common properties or attributes.

 E.g. student and teacher, common properties are name, address, phone, email, etc…

Relations (Relationship):- Relationship is an association (connection) between several entities.

 E.g. Book is issued by student where book and student are entities and issue is relation.

Relationship set: - Relationship set is a set of relationships of the same type.

Types of relationship set:-

·         Recursive relationship set:-

§  The same entity set participates in a relationship set more than once then it is called recursive relationship set.

§  E.g. an employee entity participated in relationship under with department entity as an employee as well manager also.

·         Binary relationship set:-

§  Relationship between 2 entities is called binary relationship.

§  E.g. the relationship sets borrower and loan branch provide an example of a binary relationship set – that is , one that involves two entity sets.

§  Most of the relationship sets in a database system are binary.

·         Ternary relationship set:-

§  Relationship between 3 entities is called binary relationship.

§  E.g. considers the entity sets employee, branch and job.

·         The number of entity sets that participate in a relationship set also the degree of the relationship set.

·         A binary set relationship set is of degree 2 and a ternary relationship set is of degree 3.

Attributes: - Attributes are properties hold by each member of an entity set.

E.g. Entity is student and attributes of students are enrollmentno, address, name etc …

Types of attributes:-

·         Simple attribute: It cannot be divided into subparts. E.g. , roll no

·         Composite attribute: It can be divided into subparts. E.g. address

·         Single valued attribute: It has single data value. E.g. enrollment no, birth date

·         Multi valued attribute: It has multiple data value. E.g. phone no ( may have multiple phones)

·         Stored attribute: its value is stored in database. E.g. birth date

·         Derived attribute: its value is derived or calculated from other attributes. E.g. age ( can be calculated using current date and birth date)

·         Null Attributes :A null value is used when entity does not have a value for an attribute and attributes that can accept null value is known as null attribute null represent that particular information may be either missing or not known. Ex telephone No attribute of customer entity.

·         Not Null Attributes : An attributes that can not accept null value is known as not null attribute. Ex Name attribute of customer.

·         Descriptive attributes: A relationship may also have attributes like an entity. These attributes are called descriptive attributes.

E.g. Student gets degree certificate on 14th March 2011. Student and Degree are entities, gets certificate is relation and certificate date is an attribute of relation.

Mapping cardinality (cardinality constraints):-

·         It represents the number of entities of another entity set which are connected to an entity using a relationship set.

·         It is most useful in describing binary relationship sets.

·         For a binary relationship set the mapping cardinality must be one of the following types:

1. One to one

2. One to many

3. Many to one

4. Many to many

• We express cardinality constraints by drawing either a directed line (→), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set.

Other information coming soon..