Database System Concepts By A.Silberschatz,Henry Korth,S.Sudarshan

Download the pdf here . 

THREE LEVEL ARCHITECTURE

For the system to be usable, it must retrieve data efficiently. The need for efficiency has led designers to use complex data structures to represent data in the database. Since many database-systems users are not computer trained, developers hide the complexity from users through several levels of abstraction, to simplify users’ interactions with the system:

 • External view (Individual user view)

 • Conceptual View (Global or community user view)

 • Internal level (physical or storage view).




OBJECTIVES OF THREE LEVEL ARCHITECTURE:

      The database views were suggested because of following reasons or objectives of levels of a database:

1. Make the changes easy in database when some changes needed by environment.
2. The external view or user views do not depend upon any change made ii other view. For example changes in hardware, operating system or internal view should not change the external view.
3. The users of database should not worry about the physical implementation and internal working of database system.
4. The data should reside at same place and all the users can access it as per their requirements.
5. DBA can change the internal structure without effecting the user’s view.
6. The database should be simple and changes can be easily made.
7. It is independent of all hardware and software.


External/View level

      The highest level of abstraction where only those parts of the entire database are included which are of concern to a user. Despite the use of simpler structures at the logical level, some complexity remains, because of the large size of the database. Many users of the database system will not be concerned with all this information. Instead, such users need to access only a part of the database. So that their interaction with the system is simplified, the view level of abstraction is defined. The system may provide many views for the same database.

      Databases change over time as information is inserted and deleted. 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. Schemas are changed infrequently, if at all.

      Database systems have several schemas, partitioned according to the levels of abstraction that we discussed. At the lowest level is the physical schema; at the intermediate level is the logical schema and at the highest level is a subschema.

      The features of this view are

      • The external or user view is at the highest level of database architecture.

      • Here only one portion of database will be given to user.

      • One portion may have many views.

      • Many users and program can use the interested part of data base.

      • By creating separate view of database, we can maintain security.

      • Only limited access (read only, write only etc) can be provided in this view.

      For example: The head of account department is interested only in accounts but in library information, the library department is only interested in books, staff and students etc. But all such data like student, books, accounts, staff etc is present at one place and every department can use it as per need.

Conceptual/Logical level

     Database administrators, who must decide what information is to be kept in the database, use this level of abstraction. One conceptual view represents the entire database. There is only one conceptual view per database.

      The description of data at this level is in a format independent of its physical representation. It also includes features that specify the checks to retain data consistence and integrity.

The features are:

      • The conceptual or logical view describes the structure of many users.

      • Only DBA can be defined it.

      • It is the global view seen by many users.

      • It is represented at middle level out of three level architecture.

      • It is defined by defining the name, types, length of each data item. The create table  

        commands of Oracle creates this view.

     • It is independent of all hardware and software.

Internal/Physical level

      The lowest level of abstraction describes how the data are stored in the database, and what relationships exist among those data. The entire database is thus described in terms of a small number of relatively simple structures, although implementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity.

      The features are :

     • It describes the actual or physical storage of data.

     • It stores the data on hardware so that can be stored in optimal time and accessed

       in optimal time.

     • It is the third level in three level architecture.

     • It stores the concepts like:

           • B-tree and Hashing techniques for storage of data.

           • Primary keys, secondary keys, pointers, sequences for data search.

           • Data compression techniques.

           • It is represented as

                       FILE EMP [

                                    INDEX ON EMPNO

                                                FIELD = {

                                                             (EMPNO: BYTE (4),

                                                             ENAME BYTE(25))]

          Mapping between views

      • The conceptual/internal mapping:

         o defines conceptual and internal view correspondence

      • specifies mapping from conceptual records to their stored counterparts

         o An external/conceptual mapping:

      • defines a particular external and conceptual view correspondence

      • A change to the storage structure definition means that the conceptual/internal          

        mapping must be changed accordingly, so that the conceptual schema may remain

        invariant, achieving physical data independence.

       • A change to the conceptual definition means that the conceptual/external mapping      

         must be changed accordingly, so that the external schema may remain invariant,

         achieving logical data independence.

KEYS

Keys are, as their name suggests, a key part of a relational database and a vital part of the structure of a table. They ensure each record within a table can be uniquely identified by one or a combination of fields within the table. They help enforce integrity and help identify the relationship between tables. There are three main types of keys, candidate keys, primary keys and foreign keys. There is also an alternative key or secondary key that can be used, as the name suggests, as a secondary or alternative key to the primary key.


Super Key:
A Super key is any combination of fields within a table that uniquely identifies each record within that table.


Candidate Key:
A candidate is a subset of a super key. A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table. The least combination of fields distinguishes a candidate key from a super key. Every table must have at least one candidate key but at the same time can have several.



As an example we might have a student_id that uniquely identifies the students in a student table. This would be a candidate key. But in the same table we might have the student’s first name and last name that also, when combined, uniquely identify the student in a student table. These would both be candidate keys.

In order to be eligible for a candidate key it must pass certain criteria.

• It must contain unique values
• It must not contain null values
• It contains the minimum number of fields to ensure uniqueness
• It must uniquely identify each record in the table

Once your candidate keys have been identified you can now select one to be your primary key.


Primary Key:
A primary key is a candidate key that is most appropriate to be the main reference key for the table. As its name suggests, it is the primary key of reference for the table and is used throughout the database to help establish relationships with other tables. As with any candidate key the primary key must contain unique values, must never be null and uniquely identify each record in the table.



As an example, a student id might be a primary key in a student table, a department code in a table of all departments in an organisation.  In the table above we have selected the candidate key student_id to be our most appropriate primary key

Primary keys are mandatory for every table each record must have a value for its primary key. When choosing a primary key from the pool of candidate keys always choose a single simple key over a composite key.


Foreign Key:
A foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.



An example might be a student table that contains the course_id the student is attending. Another table lists the courses on offer with course_id being the primary key. The 2 tables are linked through course_id and as such course_id would be a foreign key in the student table.

DIFFERENCE BETWEEN PHYSICAL AND LOGICAL DATA INDEPENDENCE

One of the biggest advantages of database is data independence. It means we can change the conceptual schema at one level without affecting the data at other level. It means we can change the structure of a database without affecting the data required by users and program. This feature was not available in file oriented approach. There are two types of data independence and they are:

      1. Physical data independence

      2. Logical data independence

      Data Independence The ability to modify schema definition in on level without affecting schema definition in the next higher level is called data independence. There are two levels of data independence:

      1. Physical data independence is the ability to modify the physical schema without causing application programs to be rewritten. Modifications at the physical level are occasionally necessary to improve performance. It means we change the physical storage/level without affecting the conceptual or external view of the data. The new changes are absorbed by mapping techniques.

      2. Logical data independence in the ability to modify the logical schema without causing application program to be rewritten. Modifications at the logical level are necessary whenever the logical structure of the database is altered (for example, when money-market accounts are added to banking system).

      Logical Data independence means if we add some new columns or remove some columns from table then the user view and programs should not changes. It is called the logical independence. For example: consider two users A & B. Both are selecting the empno and ename. If user B add a new column salary in his view/table then it will not effect the external view user; user A, but internal view of database has been changed for both users A & B. Now user A can also print the salary.

      User A’s External View

           

      (View before adding a new column)

   

      User B’s external view

         

      (View after adding a new column salary)

      It means if we change in view then program which use this view need not to be changed.

      Logical data independence is more difficult to achieve than is physical data independence, since application programs are heavily dependent on the logical structure of the data that they access.

      Logical data independence means we change the physical storage/level without effecting the conceptual or external view of the data. Mapping techniques absorbs the new changes.

NETWORK MODEL INTRODUCTION

Each database system uses a approach to store and maintain the data. For this purpose different data models were developed like Hierarchical model, Network Model and Relational Model.
NETWORK MODEL:
The popularity of the network data model coincided with the popularity of the hierarchical data model. Some data were more naturally modeled with more than one parent per child. So, the network model permitted the modeling of many-to-many relationships in data.The basic data modeling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multiparent concept is supported. An owner record type can also be a member or owner in another set. The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them . Thus, the complete network of relationships is represented by several pairwise sets; in each set some (one) record type is owner (at the tail of the network arrow) and one or more record types are members (at the head of the relationship arrow). Usually, a set defines a 1:M relationship, although 1:1 is permitted. The CODASYL network model is based on mathematical set theory.



Like the hierarchical model, this model uses pointers toward stored data. However, it does not necessarily use a downward tree structure.

ER DIAGRAMS TO SQL QUERIES

ONE TO MANY:

For any A there are many Bs. For any B there is no more than one A. (Equivalently, there may be one A)

           

create table B
(
 b_id type,
 b_name type,
 <b_other>
 constraint primary key(b_id)
);
create table A
(
 a_id type,
 a_name type,
 b_id type,
 <a_other>,
 constraint primary key(a_id),
 constraint foreign key(b_id) references B
);
 
ONE TO MANY WITH TOTAL PARTICIPATION:
  For any A there are possibly many Bs. For any B, there only one A.
 
create table B
  (b_id type primary key,
   b_name type,
   <b_other>);
create table A (
   a_id type primary key,
   a_name type,
   b_id type not null,
   <a_other>,
   foreign key (b_id) references B);
 
 
MANY TO MANY:
          For any A there are possibly many Bs. For any B there are possibly many As.

           

        create table A

  (a_id type,

   a_name type,

   <a_other>,

   primary key (a_id));

create table B

  (b_id type,

   b_name type,

   <b_other>,

   primary key (b_id));

create table R

  (a_id type,

    b_id type,

   <r_other>,

   primary key (a_id, b_id),

   foreign key (a_id) references A,

   foreign key (b_id) references B);

ONE TO ONE(0-1—0-1):

For any A there may be one B. For any B there may be one A

create table A
  (a_id type,
   a_name type,
   <a_other>,
   primary key (a_id));
 
create table B
  (b_id type,
   b_name type,
   <b_other>,
   primary key (b_id));
 
create table R (
   a_id type,
   b_id type not null,
   <r_other>,
   primary key (a_id),
   foreign key (a_id) references A,
   foreign key (b_id) references B,
   unique (b_id));
 
 
MANY TO ONE:
 
create table A
  (a_id type,
   a_name type,
   <a_other>,
   primary key (a_id));
 
create table B
  (b_id type,
   b_name type,
    a_id type, type
   <b_other>,
   primary key (b_id),
   foreign key (a_id) references A);
 
 
 
AGGREGATE:

This looks like the subtype relationship but it is interpreted differently. Attributes are not “inherited” by the subparts like they are by the subtypes. Suppose we had the following information to store:

car

id, name

body

id, name

engine

id, name

create table car (

  id type primary key,

  name type,

  body_id type,

  engine_id type,

  foreign key (body_id) references body,

  foreign key (engine_id) references engine);

create table body

  (id type primary key,

   name type);

create table engine

  (id type primary key,

   name type);

WEAK ENTITIES:

These entities exist only when another entity exists.
employee(id, name)
salhist(Id, revision_date, salary)
 
create table employee (
   id type primary key,
   name type);
create table salhist (
   id type,
   revision_date date,
   salary number,
   primary key (id, revision_date),
   foreign key (id) references employee
    on delete cascade);
 
 
ONE TO ONE (1-1):

 

For any A there must be one B. For any B there must be one A.

create table A

  (a_id type,

   a_name type,

   <a_other>,

   primary key (a_id));

create table B

  (b_id type,

   b_name type,

   fk_b_a type not null,

   primary key (b_id),

   unique (fk_b_a),

   foreign key (fk_b_a) references A);