Module 1

Database system architecture

1.1 Data Abstraction, Data Independence

Data Abstraction

1. A major objective of database system is to provide users with an abstract view of the data.
2. The system hides details of how the data is stored and maintained.
3. Database system has to provide efficient mechanism for data retrieval.
4. Efficiency leads to the design of complex data structure for representation of data in database.
5. This complexity must be hidden from users.
6. This can be done by providing several levels of abstractions.
7. In the Three Schema Architecture, three levels of abstraction have been created. These are:-

a)       Logical View/External View

b)      Conceptual View

c)       Physical view

1.2 Data Definition Language (DDL)

DDL is used to define the conceptual schema. It includes:-

1. Entity
2. Attributes and types
3. Relation among entity sets.

The definition also includes constraints on values that can be assigned to a given attribute.

For example:

Execution will create an empty table emp.Definitions are stored along with the database.

Eg. Create table, view, index

Alter table.

Drop table, view, index

It records the data definition statements into set of tables. These tables contains metadata i.e. data about other tables.

1.3 Data Manipulation Language (DML)

1. DML allows user to manipulate data stored in database.
2. Eg.

Data Retrieval

Data Insertion

Data Deletion

Data Modification.

Iii. DML provides commands for data manipulation.

Eg.   select name from emp

Where

Salary > 15000;

Iv. DML commands can be either in interactive mode or these commands can be embedded in programming language like C, VB, Java etc.

Types of DML:-

1. Procedural:-

It requires a user to specify what data is needed and how to retrieve it from database. The user instructs the system to perform a sequence of options on the database to compute the desired result.

B.    Non-Procedural:-

It requires a user to specify what data is needed without specifying how to get it.

1. Embedded DML Precompiler:-

It converts DML statements embedded in as application program to normal procedure call in the host language.

2.       DML Compiler:-

Translates DML statements into evaluation plan(low level instructions) that Query Evaluation Engine understands. A query can be translated to any number of alternative evaluation plans that all give the same results . DML compiler also performs query optimization i.e. picks lowest cost evaluation plan.

1.4 Data models: Entity-relationship model

The logical structure of a database can be expressed by an E-R diagram. The mainComponents of ER Diagram are:-

a)       Rectangles                           Entity Sets

b)      Ellipses                                Attributes

c)       Diamond                              Relation among entity sets

d)      Lines                                    Connects attributes to entity sets and entity sets to relationships.                              

Example:-

Fig. 1.1 ER diagram

The  above E-R diagram has two entities: customer and account .

Customer entity has three attributes:

Cust_id

Cust_name

Cust_city

Account entity has two attributes:-

Acc_no

Balance

Relation depositor gives relationship between customers account.

Eg. Account  A-101 is held by customer John.

Database design in E-R model is converted to design in Relational model.

Relational Model:-

Relational model uses a collection of tables to represent both data and the relationships among those data.

Sample Relational Database:-

(a)  Customer Table

(b) Account table

(c)  Depositor table

1.5 Network model

Network model uses two different data structures:-

a)       A record type is used to represent as entity set.

b)      A set  type is used to represent a directed relationship between two record type. It specifies 1:M relationship between owner record type and member record type.

1.6 Relational and object oriented data models

These models are used to handle objects:

1)       To provide support for complex objects.

2)       To provide user extensibility for data types operators and access methods.

3)       There should be mechanism to support:

a)       Abstract data type.

b)      Data of type ‘procedure’.

c)       Rules.

Advantages of ORDM:-

1. Reuse of sharing standard functions can be implemented centrally, rather than coding it in each application.
2. It preserves the existing relational applications.

1.7 Integrity constraints

Entity Integrity constraints states that,

“In a base relation, primary key cannot have null value”.

A primary key is used to identify records uniquely. Therefore, primary key should not be null. For example, as emp_no is the primary key of the employee relation, we should not be able to insert a record into the employee relation with a null for the emp_no attribute.

References:

1. “Principles of Database and Knowledge – Base Systems”, Vol 1 by J. D. Ullman, Computer Science Press.

2. “Fundamentals of Database Systems”, 5th Edition by R. Elmasri and S. Navathe, Pearson Education

3. “Foundations of Databases”, Reprint by Serge Abiteboul, Richard Hull, Victor Vianu, Addison-Wesley