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);

Practice Assignment

Schema :
Nurse(NID,Name,Bdate,WID)
Function(FID,Fname,Description)
Ward(WID,Wname,Location)
Services(WID,FID)
Certified(NID,FID)
Queries :
1.print names of nurses not assigned to any ward.
2.print the name of the ward for which no nurse is assigned.
3.for each ward print ward name and no.of services it offers.
4.print the ward with maximum no.of nurses assigned.
5.print the names of nurses whose functions are ensured by the ward to which they are assigned.
6.list the wards that offer all services offered by ward w1.
7.print the name of the most certified nurse.
8.print pairs of nurses assigned to same ward.
9.print the name of wards that ensure each function offered by the hospital.
10.print nurse-id of nurses certified for every function the hospital offers.
11.for each ward print ward-id and nurse-id of most certified nurse.

Oracle Database Download

To practice SQL queries at home, you could download Oracle 10g Express Edition from here and then follow this tutorial. (Note that the sample database supplied with Oracle 10g is the same database we use in college).

References For Database Management Systems

Slides for the Silberchatz-Korth-Sudarshan book can be found here.

For Database Design Concepts book click here.

To download Database System Concepts book(6th edition) by Silberschatz click here.

Study material can be found here.

Birthday Reminder Application

Ever missed a birthday? I did. That's why I created Birthday Reminder to remind myself of when birthdays are coming.
Birthday Reminder is a simple program that remind users of important birthdays.The application helps us to add our friend's birthday, view all the birthdays stored,search for the dearest one's birthday and also to edit them.


 By- 

• Soma Sneha - 1005-10-733051
• V.Rasagna  - 1005-10-733042
• Puranam Srinivas  - 1005-10-733054