Category: Database Design

Procedural Query language

.. query specification involves giving a step by step process of obtaining the query result
e.g, relational algebra
.. usage calls for detailed knowledge of the operators involved
.. difficult for the use of non – experts

Declarative Query language

.. query specification involves giving the logical conditions the results is required to satisfy
.. easy for the use of non – experts

By Prof. P.Sreenivasa Kumar, IIT Chennai

view / download: DB-TupleRelationalcalculus (size 106 KB)

 

The SQL Standard
SQL – Structured Query Language
A ‘standard’ that specifies how

• a relational schema is created
• data is inserted / updated in the relations
• data is queried
• transactions are started and stopped
• programs access data in the relations
• and a host of other things are done

 

Every relational database management system (RDBMS) is required to support /
implement the SQL standard.

By Prof. P.Sreenivasa Kumar, IIT Chennai

view / download: DB-SQL (size 157 KB)

Relational Model
Introduction

Proposed by E.F. Codd in early seventies.
.. Most of the modern DBMS are relational.
.. Simple and elegant model.
.. Led to the development of a theory of data
.. Dependencies and database design.
.. Relational algebra operations –
Crucial role in query optimization & execution.
.. Led to the development of database standard SQL

By Prof. P.Sreenivasa Kumar, IIT Chennai

view / download: DB-RelationalModel (size 219 KB)

Database
A collection of related pieces of data –
.. Representing/capturing the information about a real-world enterprise or part of an enterprise.
.. Collected and maintained to serve specific data management needs of the enterprise.
.. Activities of the enterprise are supported by the database and continually update the database.
Example
University Database-
Data about students, faculty, courses, research- laboratories, course registration / enrollment etc.
Reflects the state of affairs of the academic aspects of the university.
Purpose: To keep an accurate track of the academic activities of the university.
Database Management System (DBMS)
Software system enabling-
.. Creation of large disk-resident databases.
.. Posing data retrieval queries in a standard manner.
.. Retrieval of query results efficiently.
.. Concurrent use of the system by a large number of users in a consistent manner.
.. Guaranteed availability of data irrespective of system failures.

By Prof. P.Sreenivasa Kumar, IIT Chennai

view / download: DB-Introduction (size 72 KB)

File Organization and Indexing
The data of a RDB is ultimately stored in disk files
Disk space management:
Should Operating System services be used ?
Should RDBMS manage the disk space by itself ?
2nd option is preferred as RDBMS requires complete control over when a block in main memory buffer is written to the disk
This is important for recovering data when system crash occurs.

By Prof. P.Sreenivasa Kumar, IIT, Chennai

view / download: DB-FileOrganization (size 201 KB)

Entity-Relationship (E/R) Model

Database Design: coming up with a ‘good’ scheme is very important.
How do we characterize the “goodness” of a scheme?
If two or more alternative schemes are available how do we compare them?
What are the problems with “bad” scheme designs?

Normal Forms:
Each normal form specifies certain conditions
If the conditions are satisfied by the schema certain kind of problems are avoided

-By Prof. P.Sreenivasa Kumar, IIT, Chennai

view / download: DB-DatabaseDesign (size 203 KB)