III B.Sc/III B.Com - DBMS - Lab

 PROGRAM N0:01 DRAW ER DIAGRAMS FOR TRAIN SERVICES IN A RAILWAY STATION





PROGRAM NO.2: DRAW ER DIAGRAM FOR HOSPITAL ADMINISTRATION




PROGRAM NO.3: CREATION OF COLLEGE DATABASE AND ESTABLISH RELATIONSHIPS BETWEEN TABLES




CREATION OF TABLES
--------------------------------

SQL> Create table Department (Deptname char(15) primary key, Location char(10));

SQL> Create table Courses (CourseId  number(3) primary key,CourseName char(10), Duration number(3),Deptname char (15) References Department(Deptname));

SQL> Create table Student (StudentId  number(3) primary key,First_Name char(10), last_Name char(10), phone number(10), courseId number(3) references Courses(CourseId));

SQL> Create table Instructor (InstructorID number(3) primary key,First_Name char(10), last_Name char(10), phone number(10), courseId number(3) references Courses(CourseId), Deptname char (15) References Department(Deptname));

Note :

Insert any 5 records in each table.

Select * from Department;
Select * from Courses;
Select * from Student;
Select * from instructor;

SQL>
 
 

PROGRAM NO. 04: WRITE A VIEW TO EXTRACT DETAILS FROM TWO OR MORE TABLES

AIM:  To execute and verify the SQL commands for Views.

CREATION OF TABLES
--------------------------------
SQL> CREATE TABLE ST(SID INT,SNAME VARCHAR(10));

TABLE CREATED

SQL> CREATE TABLE MARKS (SID INT, SUB1 INT ,SUB2 INT, SUB3 INT);

TABLE CREATED

TABLE DESCRIPTION
-------------------------------
SQL> DESC ST;

SQL> DESC MARKS;

INSERTION OF DATA  INTO TABLES
-----------------------------------------------

SQL> INSERT INTO ST VALUES(1,’SHAIK’);

1 ROW INSERTED

SQL> INSERT INTO ST VALUES(2,’AYAAN’);

1 ROW INSERTED


SQL> INSERT INTO MARKS VALUES(1,50,62,89);

1 ROW INSERTED

SQL> INSERT INTO MARKS VALUES(2,90,99,89);

1 ROW INSERTED

SQL> INSERT INTO MARKS VALUES(3,10,59,69);

1 ROW INSERTED


SYNTAX FOR CREATION OF VIEW
--------------------------------------------------
SQL> CREATE <VIEW> <VIEW NAME> AS SELECT
<COLUMN_NAME_1>, <COLUMN_NAME_2> FROM <TABLE NAME>;


CREATION OF VIEW
------------------------------

SQL> CREATE VIEW ABC AS SELECT ST.SID,ST.SNAME,MARKS.SUB1,MARKS.SUB2,MARKS.SUB3
FROM ST , MARKS WHERE ST.SID=MARKS.SID;

VIEW CREATED




DESCRIPTION OF VIEW
--------------------------------
SQL> DESC EMPVIEW;
NAME             NULL?             TYPE
----------------------------------------- -------- ----------------------------
SID                    NUMBER(38)
SNAME                     CHAR(10)
SUB1                     NUMBER(38)
SUB2                     NUMBER(38)
SUB3                     NUMBER(38)


DISPLAY VIEW:
----------------------
SQL> SELECT * FROM ABC;

SYNTAX FOR DROP A VIEW:
---------------------------------
SQL> DROP VIEW <VIEW_NAME>


DROP A VIEW
-----------------------

SQL>DROP VIEW ABC;

VIEW DROPED


PROGRAM No. 05 : WRITE A PROGRAM TO DEMONSTRATE OF   AGGREGATE FUNCTIONS


CREATION OF TABLE
--------------------------------

SQL> CREATE TABLE PRODUCT (PNO INT ,PNAME VARCHAR(30),PRICE FLOAT,QUANTITY INT);
TABLE CREATED.
INSERTION OF DATA  INTO TABLE
-----------------------------------------------
SQL> INSERT INTO PRODUCT VALUES(1,'DAIRY MILK',60,2);

1 ROW CREATED.

SQL> INSERT INTO PRODUCT VALUES(2,'GOOD DAY',25,4);

1 ROW CREATED.

SQL> INSERT INTO PRODUCT VALUES(3,'BOOST',10,6);

1 ROW CREATED.

SQL> INSERT INTO PRODUCT VALUES(4,'MAGGI',5,10);

1 ROW CREATED.
SQL> INSERT INTO PRODUCT VALUES(5,'BOOK',20,20);

1 ROW CREATED.

DISPLAY OF DATA  FROM TABLE:
-----------------------------------------------
SQL> SELECT * FROM PRODUCT;

PNO PNAME                               PRICE      QUANTITY
---------- ------------------------------ ---------- ----------
1 DAIRY MILK    60         2
2 GOOD DAY    25    4
3 BOOST                  10    6
4 MAGGI    5    10
5 BOOK                20    20

PERFORMING COUNT FUNCTION
------------------------------------------------
SQL> SELECT COUNT(PRICE) FROM PRODUCT;
COUNT(PRICE)
------------
     5

PERFORMING SUM FUNCTION
------------------------------------------------
SQL> SELECT SUM(PRICE) FROM PRODUCT;
SUM(PRICE)
----------
120

PERFORMING AVG FUNCTION
------------------------------------------------
SQL> SELECT AVG(QUANTITY) FROM PRODUCT;
AVG(QUANTITY)
-------------
8.4

PERFORMING MAX FUNCTION
------------------------------------------------
SQL> SELECT MAX(PRICE) FROM PRODUCT;
MAX(PRICE)
----------
 60

PERFORMING MIN FUNCTION
------------------------------------------------
SQL> SELECT MIN(PRICE) FROM PRODUCT;
MIN(PRICE)
----------
 5


PERFORMING GROUP BY FUNCTION
------------------------------------------------


CREATE TABLE EMPLOY (SID INT,NAME VARCHAR(20),DEPT VARCHAR(10),SAL FLOAT);


TABLE CREATED


SELECT * FROM EMPLOY


SID   NAME    DEPT SAL
1      AYISHA  ECE   6000
2      SINDHU  IT     50000
3      SAI        IT      80000
4       LALLI      ECE    8000
SQL>SELECT DEPT,SUM(SAL) FROM EMPLOY GROUP BY DEPT;
DEPT   SUM(SAL)
IT         130000
ECE       14000

PERFORMING HAVING FUNCTION
------------------------------------------------

SQL>SELECT DEPT,SUM(SAL) FROM EMPLOY GROUP BY DEPT HAVING SUM(SAL)>25000;


DEPT   SUM(SAL)
 IT         130000


PROGRAM NO.06:  WRITE A STORED PROCEDURE TO PROCESS STUDENTS RESULTS

CREATION OF TABLE
--------------------------------

SQL> CREATE TABLE STU1(SNO INT,NAME CHAR(10),SUB1 INT,SUB2 INT, SUB3 INT);

TABLE CREATED.

INSERTION OF DATA  INTO TABLE
-----------------------------------------------

SQL> INSERT INTO STU1 VALUES(100,'WASIM',70,75,89);

1 ROW CREATED.

SQL> INSERT INTO STU1 VALUES(200,'SIVA',75,69,88);

1 ROW CREATED.

CREATION OF STORED PROCEDURE:
------------------------------------------------------

CREATE OR REPLACE PROCEDURE STUDENT_RESULT
 IS
 STU STU1%ROWTYPE;
    TOTAL NUMBER(4);
    RESULT VARCHAR(4);
    CURSOR C IS SELECT * FROM STU1;
BEGIN
  FOR STU IN C
    LOOP
         DBMS_OUTPUT.PUT_LINE('STUDENT MARKLIST');
         DBMS_OUTPUT.PUT_LINE('----------------------------');
         DBMS_OUTPUT.PUT_LINE('SNO:'||STU.SNO||' SNAME:'||STU.NAME);
         TOTAL:=STU.SUB1+STU.SUB2+STU.SUB3;
         IF STU.SUB1>35 AND STU.SUB2>35 AND STU.SUB3>35
         THEN
             RESULT:='PASS';
         ELSE
             RESULT:='FAIL';
         END IF;
          DBMS_OUTPUT.PUT_LINE('M1:'||STU.SUB1||' M2:'||STU.SUB2||' M3:'||STU.SUB3);
          DBMS_OUTPUT.PUT_LINE('TOTAL:'||TOTAL||' RESULT:'||RESULT);
       END LOOP;
END;
/

PROCEDURE CREATED.

EXECUTION PROCEDURE:
-------------------------------
SQL>SET SERVEROUTPUT ON;

SQL> EXECUTE STUDENT_RESULT;

STUDENT MARKLIST
----------------------------
SNO:100 SNAME:WASIM
M1:70 M2:75 M3:89
TOTAL:234 RESULT:PASS

STUDENT MARKLIST
----------------------------
SNO:200 SNAME:SIVA
M1:75 M2:69 M3:88
TOTAL:232 RESULT:PASS

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

PROGRAM NO.7 : WRITE A PROGRAM TO DEMONSTRATE A FUNCTION

PROCEDURE:
DECLARE
NUM NUMBER;
FACTORIAL NUMBER;
FUNCTION FACT(X NUMBER) RETURN NUMBER IS
F NUMBER;
BEGIN
IF X=0 THEN
F := 1;
ELSE
F := X * FACT(X-1);
END IF;
RETURN F;
END;
BEGIN
NUM:= &AMP;NUM;
FACTORIAL := FACT(NUM);
DBMS_OUTPUT.PUT_LINE(‘ FACTORIAL ‘|| NUM ||’ IS ‘ || FACTORIAL);
END;
/

EXECUTION:
SQL>EXECUTE  FUN.SQL;

ENTER VALUE FOR NUM: 3

FACTORIAL 3 IS 6
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

PROGRAM NO.8 :WRITE A PROGRAM TO DEMONSTRATE DATABASE TRIGGERS.

PROCEDURE:
SQL>CREATE TABLE EMPLOYEE_SALARY(EMP_NO NUMBER(2) PRIMARY KEY, BASIC NUMBER(8), HRA NUMBER(3), DA NUMBER(3), TOTAL_DEDUCTION NUMBER(3), NET_SALARY NUMBER(10), GROSS_SALARY NUMBER(10));
SQL> SELECT * FROM EMPLOYEE_SALARY;
EMP_NO BASIC HRA DA TOTAL_DEDUCTION NET_SALARY GROSS_SALARY
---------- ---------- ---------- ---------- --------------- ----------
2 15000 4000 1000 5000 15000 20000
1 31000 8000 1000 5000 35000 40000
3 14000 4000 1000 5000 15000 19000
4 14000 4000 1000 5000 15000 19000
5 13000 4000 1000 5000 15000 18000

SQL> ED PROG8.SQL;
CREATE OR REPLACE TRIGGER T   AFTER UPDATE ON EMPLOYEE_SALARY
FOR EACH ROW
BEGIN
INSERT INTO BACKUP VALUES (:OLD.EMP_NO,:OLD.GROSS_SALARY,:NEW.GROSS_SALARY);
END;
/
EXECUTION:
SQL> @ PROG8.SQL;
TRIGGER CREATED.

SQL> UPDATE EMPLOYEE_SALARY SET GROSS_SALARY=44000 WHERE EMP_NO=1;

SQL> SELECT * FROM BACKUP;
EMPNO     OLD_GROSS_SALARY         NEW_GROSS_SALARY
1         40000                         44000

SQL> UPDATE EMPLOYEE_SALARY SET GROSS_SALARY=20000 WHERE EMP_NO=2;




SQL> SELECT * FROM BACKUP;
EMPNO     OLD_GROSS_SALARY         NEW_GROSS_SALARY
1             40000                 44000
2             17600                     20000

SQL> UPDATE EMPLOYEE_SALARY SET GROSS_SALARY=48000 WHERE EMP_NO=1;

SQL> SELECT * FROM BACKUP;
EMPNO     OLD_GROSS_SALARY         NEW_GROSS_SALARY
1             40000                 44000
2             17600                 20000
1             44000                 48000


PROGRAM NO.9 :WRITE A PROGRAM TO DEMONSTRATE DATABASE CURSORS.

PROCEDURE:
SQL> CREATE TABLE STUDENT(RNO NUMBER(10),S1 NUMBER(10),S2 NUMBER(10),S3 NUMBER(10),S4 NUMBER(10),TOTAL NUMBER(20),PERCENTAGE NUMBER(6));
TABLE CREATED.

SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10011,56,78,79,56);
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10012,45,67,34,58);
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10013,76,86,94,58);
SQL> INSERT INTO STUDENT(RNO,S1,S2,S3,S4)VALUES(10014,57,48,39,92);

SQL> SELECT * FROM STUDENT;
RNO         S1     S2     S3     S4     TOTAL     PERCENTAGE
10011         56     78     79     56
10012         45     67     34     58
10013         76     86     94     58
10014         57     48     39     92
SQL>ED PROG9.SQL;
 DECLARE
 T STUDENT.TOTAL%TYPE;
 P STUDENT.PERCENTAGE%TYPE;
 CURSOR STU IS SELECT * FROM STUDENT;
 RW STU%ROWTYPE;
 BEGIN
 OPEN STU;
 LOOP
 FETCH STU INTO RW;
 EXIT WHEN STU%NOTFOUND;
 T:=RW.S1+RW.S2+RW.S3+RW.S4;
 P:=T*0.25;
 UPDATE STUDENT SET TOTAL=T, PERCENTAGE=P WHERE RNO=RW.RNO;
 END LOOP;
 CLOSE STU;
 END;
/
SQL> /
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.




SQL> SELECT * FROM STUDENT;
RNO         S1     S2     S3     S4     TOTAL     PERCENTAGE
10011         56     78     79     56    269            67
10012         45     67     34     58    204            51
10013         76     86     94     58    314            79
10014         57     48     39     92    236            59



PROGRAM NO.10:  WRITE A PROGRAM TO DEMONSTRATE JOINS

SQL>CREATE TABLE EMP1(ENAME CHAR(10),ENO NUMBER(3),MANGERNO NUMBER(3));
TABLE CREATED.

SQL> INSERT INTO EMPS1 VALUES('&ENAME',&ENO,&EMANGERNO);
ENTER VALUE FOR ENAME: NAGAENTER VALUE FOR ENO: 101
ENTER VALUE FOR EMANGERNO: 121
OLD 1: INSERT INTO EMPS1 VALUES('&ENAME',&ENO,&EMANGERNO)
NEW 1: INSERT INTO EMPS1 VALUES('NAGA',101,121)
1 ROW CREATED.

SQL> SELECT * FROM EMPS1;
ENAME         ENO         MANGERNO
---------- ---------- ----------
NAGA         101         121
NAVIN         111         121
RAM         121         131
RAJ         141         131

SELF JOINS:
----------------------
SQL> SELECT EMPS1.ENAME,EMPS1.ENO,EMPS1.MANGERNO FROM EMPS1 WHEREEMPS1.ENO=EMPS1.MANGERNO;

NO ROWS SELECTED

SQL> SELECT * FROM CUSTOMERS1;
ENO         SALARY         LOCATION
-------------------- ----------
101         10000         CHENNAI
111         10500         MADURAI
131         8000         PONDY
141         5000         AVADI
151         9800         LOCO

EQUI JOINS:

SQL> SELECTEMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.ENO,CUSTOMERS1.SALARY FROMEMPS1,CUSTOMERS1 WHERE EMPS1.ENO=CUSTOMERS1.ENO;

ENAME         ENO        ENO         SALARY
---------- ---------- ---------- ----------
NAGA         101         101         10000
NAVIN         111        111         10500
RAJ         141         141         5000


NON EQUI JOINS:

SQL> SELECT EMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.SALARY,CUSTOMERS1.ENO,CUSTOMERS.LOCATIONFROM EMPS1,CUSTOMERS1.ENO<>CUSTOMERS1.ENO;

ENAME ENO     SALARY ENO     LOCATION
---------- ---------- ---------- ---------- --------------------
NAVIN     111     10000     101     CHENNAI
RAM     121     10000     101     CHENNAI
RAJ     141     10000     101     CHENNAI
NAGA     101     10500     111     PONDY
RAM     121     10500     111     PONDY
RAJ     141     10500     111     PONDY
NAGA     101     8000     131     AVADI
NAVIN     111     8000     131     AVAD
IRAM     121     8000     131     AVADI
RAJ     141     8000     131     AVADI
NAGA     101     5000     141     LOCO
NAVIN     111     5000     141     LOCO
RAM     121     5000     141     LOCO
NAGA     101     9800     151     CHOOLAI
NAVIN     111     9800     151     CHOOLAI
RAM     121     9800     151     CHOOLAI
RAJ     141     9800     151     CHOOLAI
17 ROWS SELECTED.

LEFT OUTER JOINS:

SQL>SELECT EMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.SALARY,CUSTOMERS1.ENO FROMEMPS1,CUSTOMERS1 WHERE EMPS1.ENO(+)=CUSTOMERS1.ENO;

ENAME     ENO     SALARY     ENO
---------- ---------- ---------- --------
NAGA     101     10000     101
NAVIN     111     10500     111
8000     131
RAJ     141     5000     141
                     9800     151

RIGHT OUTER JOINS:

SQL> SELECT EMPS1.ENAME,EMPS1.ENO,CUSTOMERS1.ENO,CUSTOMERS1.SALARY FROMEMPS1,CUSTOMERS1 WHERE EMPS1.ENO=CUSTOMERS1.ENO(+);

ENAME     ENO     ENO     SALARY
---------- ---------- ---------- ----------
NAGA     101     101     10000
NAVIN     111     111     10500
RAM     121
RAJ     141     141     5000

III B.Sc (Computer Science)

Ans: Data and Information

Data are raw facts that constitute building block of information. Data are the heart of the DBMS. Data will not convey useful information. Useful information is obtained from processed data.

Data are a representation of facts, concepts, or instructions in a formalized manner suitable for communication, interpretation, or processing by humans or automatic means.

The data in DBMS can be broadly classified into two types, one is the collection of information needed by the organization and the other is “metadata” which is the information about the database.

Data are the most stable part of an organization’s information system. A company needs to save information about employees, departments, and salaries. These pieces of information are called data. Generally, we perform operations on data or data items to supply some information about an entity. For example library keeps a list of members, books, due dates, and fines.

Q) Explain briefly about Database.

A) Database

A database is a well-organized collection of data that are related in a meaningfulway, which can be accessed in different logical orders. Database systems are systems in which the interpretation and storage of information are of primary importance. The database should contain all the data needed by the organization as a result, a huge volume of data, the need for long-term storage of the data, and access of the data by a large number of users generally characterize database systems. The simplified view of database system is shown in Fig. 1.1.

 

From this figure, it is clear that several users can access the data in an organization still the integrity of the data should be maintained. A databaseis integrated when same information is not recorded in two places.

Q) Write a note on Database Management System

A) Database Management System:

A database management system (DBMS) consists of collection of interrelated data and a set of programs to access that data. It is software that is helpful in maintaining and utilizing a database.

A DBMS consists of:

– A collection of interrelated and persistent data. This part of DBMS is referred to as database (DB).

– A set of application programs used to access, update, and manage data.

This part constitutes data management system (MS).

– A DBMS is general-purpose software i.e., not application specific. The same DBMS (e.g., Oracle, Sybase, etc.) can be used in railway reservation system, library management, university, etc.

– A DBMS takes care of storing and accessing data, leaving only application specific tasks to application programs.

DBMS is a complex system that allows a user to do many things to data as shown in Fig. 1.2. From this figure, it is evident that DBMS allows user to input data, share the data, edit the data, manipulate the data, and display the data in the database.

Q) Explain Structure of DBMS

A) Structure of DBMS:

An overview of the structure of database management system is shown in Fig. 1.3. 

A DBMS is a software package, which translates data from its logical representation to its physical representation and back.

The DBMS uses an application specific database description to define this translation. The database description is generated by a database designer from his or her conceptual view of the database, which is called the Conceptual Schema. The translation from the conceptual schema to the database description is performed using a data definition language (DDL) or a graphical or textual design interface.

Q) What are the Objectives of DBMS? Explain it.

A) The main objectives of database management system are data availability, data integrity, data security, and data independence.

1.      Data Availability

Data availability refers to the fact that the data are made available to wide variety of users in a meaningful format at reasonable cost so that the users can easily access the data.

2.      Data Integrity

Data integrity refers to the correctness of the data in the database. In otherwords, the data available in the database is a reliable data.

3.      Data Security

Data security refers to the fact that only authorized users can access the data. Data security can be enforced by passwords. If two separate users are accessing a particular data at the same time, the DBMS must not allow them to make conflicting changes.

4.      Data Independence

DBMS allows the user to store, update, and retrieve data in an efficient manner. DBMS provides an “abstract view” of how the data is stored in the database.

In order to store the information efficiently, complex data structures are used to represent the data. The system hides certain details of how the data are stored and maintained.

Q) Explain the Evolution of Database Management Systems

File-based system was the predecessor to the database management system.

The chronological order of the development of DBMS is as follows:

–    Flat files – 1960s–1980s

–    Hierarchical – 1970s–1990s

–    Network – 1970s–1990s

–    Relational – 1980s–present

–    Object-oriented – 1990s–present

–    Object-relational – 1990s–present

–    Data warehousing – 1980s–present

–    Web-enabled – 1990s–present

Early 1960s. Charles Bachman at GE created the first general purpose DBMS Integrated Data Store. It created the basis for the network model which was standardized by CODASYL (Conference on Data System Language).

Late 1960s. IBM developed the Information Management System (IMS). IMS used an alternate model, called the Hierarchical Data Model.

1970. Edgar Codd, from IBM created the Relational Data Model. In 1981 Codd received the Turing Award for his contributions to database theory. Codd Passed away in April 2003.

1976. Peter Chen presented Entity-Relationship model, which is widely used in database design.

1980. SQL developed by IBM, became the standard query language for databases.

SQL was standardized by ISO.

1980s and 1990s. IBM, Oracle, Informix and others developed powerful DBMS

Q) Briefly explain the Classification of Database Management System

A) The database management system can be broadly classified into (1) Passive Database Management System and (2) Active Database Management System:

1. Passive Database Management System. Passive Database Management Systems are program-driven. In passive database management system the users query the current state of database and retrieve the information currently available in the database. Traditional DBMS are passive in the sense that they are explicitly and synchronously invoked by user or application program initiated operations. Applications send requests for operations to be performed by the DBMS and wait for the DBMS to confirm and return any possible answers. The operations can be definitions and updates of the schema, as well as queries and updates of the data.
2. Active Database Management System. Active Database Management Systems are data-driven or event-driven systems. In active database management system, the users specify to the DBMS the information they need.

If the information of interest is currently available, the DBMS actively monitors the arrival of the desired information and provides it to the relevant users. The scope of a query in a passive DBMS is limited to the past and present data, whereas the scope of a query in an active DBMS additionally includes future data. An active DBMS reverses the control flow between applications and the DBMS instead of only applications calling the DBMS, the DBMS may also call applications in an active DBMS.

Active databases contain a set of active rules that consider events that represent database state changes, look for TRUE or FALSE conditions as the result of a database predicate or query, and take an action via a data manipulation program embedded in the system. Alert is extension architecture at the IBM Almaden Research, for experimentation with active databases.

Q) Write a note on File-Based System.

Prior to DBMS, file system provided by OS was used to store information.

In a file-based system, we have collection of application programs that perform services for the end users. Each program defines and manages its own data.

Consider University database, the University database contains details about student, faculty, lists of courses offered, and duration of course, etc. In File-based processing for each database there is separate application program which is shown in Fig. 1.4.

One group of users may be interested in knowing the courses offered by the university. One group of users may be interested in knowing the faculty information. The information is stored in separate files and separate applications programs are written.

Q) What are the Drawbacks of File-Based System? Explain it.

The limitations of file-based approach are duplication of data, data dependence, incompatible file formats, separation, and isolation of data.

1.      Duplication of Data:

Duplication of data means same data being stored more than once. This can also be termed as data redundancy. Data redundancy is a problem in file based approach due to the decentralized approach. The main drawbacks of duplication of data are:

– Duplication of data leads to wastage of storage space. If the storage space is wasted it will have a direct impact on cost. The cost will increase.

– Duplication of data can lead to loss of data integrity; the data are no longer consistent. Assume that the employee detail is stored both in the department and in the main office. Now the employee changes his contact address. The changed address is stored in the department alone and not in the main office. If some important information has to be sent to his contact address from the main office then that information will be lost.

This is due to the lack of decentralized approach.

2.      Data Dependence

Data dependence means the application program depends on the data. If some modifications have to be made in the data, then the application program has to be rewritten. If the application program is independent of the storage structure of the data, then it is termed as data independence. Data independence is generally preferred as it is more flexible. But in file-based system there is program-data dependence.

3.      Incompatible File Formats

As file-based system lacks program data independence, the structure of the file depends on the application programming language. For example, the structure of the file generated by FORTRAN program may be different from the structure of a file generated by “C” program. The incompatibility of such files makes them difficult to process jointly.

4.      Separation and Isolation of Data

In file-based approach, data are isolated in separate files. Hence it is difficult to access data. The application programmer must synchronize the processing of two files to ensure that the correct data are extracted. This difficulty is more if data has to be retrieved from more than two files.

The draw backs of conventional file-based approach are summarized as:

1. We have to store the information in a secondary memory such as a disk. If the volume of information is large; it will occupy more memory space.

2. We have to depend on the addressing facilities of the system. If the database is very large, then it is difficult to address the whole set of records.

3. For each query, for example the address of the student and the list of electives that the student has chosen, we have to write separate programs.

4. While writing several programs, lot of variables will be declared and it will occupy some space.

5. It is difficult to ensure the integrity and consistency of the data when more than one program accesses some file and changes the data.

6. In case of a system crash, it becomes hard to bring back the data to a consistent state.

7. “Data redundancy” occurs when identical data are distributed over various files.

8. Data distributed in various files may be in different formats hence it is difficult to share data among different application (Data Isolation).

Q) Explain DBMS Approach and its advantages.

DBMS is software that provides a set of primitives for defining, accessing, and manipulating data. In DBMS approach, the same data are being shared by different application programs; as a result data redundancy is minimized. The DBMS approach of data access is shown in Fig. 1.5.

 

Advantages of DBMS

There are many advantages of database management system. Some of the advantages are listed later:

1. Centralized data management.

2. Data Independence.

3. System Integration.

 

1.      Centralized Data Management

In DBMS all files are integrated into one system thus reducing redundancies and making data management more efficient.

2.      Data Independence

Data independence means that programs are isolated from changes in the way the data are structured and stored. In a database system, the database management system provides the interface between the application programs and the data. Physical data independence means the applications need not worry about how the data are physically structured and stored. Applications should work with a logical data model and declarative query language.

If major changes were to be made to the data, the application programs may need to be rewritten. When changes are made to the data representation, the data maintained by the DBMS is changed but the DBMS continues to provide data to application programs in the previously used way.

Data independence is the immunity of application programs to changes in storage structures and access techniques. For example if we add a new attribute, change index structure then in traditional file processing system, the applications are affected. But in a DBMS environment these changes are reflected in the catalog, as a result the applications are not affected. Data independence can be physical data independence or logical data independence.

Physical data independence is the ability to modify physical schema without causing the conceptual schema or application programs to be rewritten.

Logical data independence is the ability to modify the conceptual schema without having to change the external schemas or application programs.

3.      Data Inconsistency

Data inconsistency means different copies of the same data will have different values. For example, consider a person working in a branch of an organization.

The details of the person will be stored both in the branch office as well as in the main office. If that particular person changes his address, then the “change of address” has to be maintained in the main as well as the branch office.

For example the “change of address” is maintained in the branch office but not in the main office, then the data about that person is inconsistent.

DBMS is designed to have data consistency. Some of the qualities achieved in DBMS are:

1. Data redundancy −→ Reduced in DBMS.

2. Data independence −→ Activated in DBMS.

3. Data inconsistency −→ Avoided in DBMS.

4. Centralizing the data −→ Achieved in DBMS.

5. Data integrity −→ Necessary for efficient Transaction.

6. Support for multiple views −→ Necessary for security reasons.

– Data redundancy means duplication of data. Data redundancy will occupy more space hence it is not desirable.

– Data independence means independence between application program and the data. The advantage is that when the data representation changes, it is not necessary to change the application program.

– Data inconsistency means different copies of the same data will have different values.

– Centralizing the data means data can be easily shared between the users but the main concern is data security.

– The main threat to data integrity comes from several different users attempting to update the same data at the same time. For example, “The number of booking made is larger than the capacity of the aircraft/train.”

– Support for multiple views means DBMS allows different users to see different “views” of the database, according to the perspective each one requires. This concept is used to enhance the security of the database.

Q) Explain ANSI/SPARK Data Model (American National Standard Institute/ Standards Planning and Requirements Committee)

The distinction between the logical and physical representation of data were recognized in 1978 when ANSI/SPARK committee proposed a generalized framework for database systems. This framework provided a three-level architecture, three levels of abstraction at which the database could be viewed.

1.      Need for Abstraction

The main objective of DBMS is to store and retrieve information efficiently; all the users should be able to access same data. The designers use complex data structure to represent the data, so that data can be efficiently stored and retrieved, but it is not necessary for the users to know physical database storage details. The developers hide the complexity from users through several levels of abstraction.

2.      Data Independence

Data independence means the internal structure of database should be unaffected by changes to physical aspects of storage. Because of data independence, the Database administrator can change the database storage structures without affecting the users view.

The different levels of data abstraction are:

1. Physical level or internal level

2. Logical level or conceptual level

3. View level or external level

1.      Physical Level

It is concerned with the physical storage of the information. It provides the internal view of the actual physical storage of data. The physical level describes complex low-level data structures in detail.

2.      Logical Level

Logical level describes what data are stored in the database and what relationships exist among those data. Logical level describes the entire database in terms of a small number of simple structures. The implementation of simple structure of the logical level may involve complex physical level structures; the user of the logical level does not need to be aware of this complexity. Database administrator use the logical level of abstraction.

3.      View Level

View level is the highest level of abstraction. It is the view that the individual user of the database has. There can be many view level abstractions of the same data. The different levels of data abstraction are shown in Fig. 1.6.

 

Q) Explain Database Instances and Schema.

Database Instances:

Database 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.

Database Schema:

The overall design of the database is called the database schema. A schema is a collection of named objects. Schemas provide a logical classification of objects in the database. A schema can contain tables, views, triggers, functions, packages, and other objects.

A schema is also an object in the database. It is explicitly created using the CREATE SCHEMA statement with the current user recorded as the schema owner. It can also be implicitly created when another object is created, provided the user has IMPLICIT SCHEMA authority.

Q) Explain briefly about Data Models.

A) Data model is collection of conceptual tools for describing data, relationship between data, and consistency constraints. Data models help in describing the structure of data at the logical level. Data model describe the structure of the database. A data model is the set of conceptual constructs available for defining a schema. The data model is a language for describing the data and database, it may consist of abstract concepts, which must be translated by the designer into the constructs of the data definition interface, or it may consist of constructs, which are directly supported by the data definition interface. The constructs of the data model may be defined at many levels of abstraction.

The entire structure of a database can be described using a data model. It is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

Data models can be classified into following types.

1.      Object Based Logical Models.

2.      Record Based Logical Models.

3.      Physical Models.

1.Object Based Logical Models: These models can be used in describing the data at the logical and view levels. These models are classified into following types.

a.      The entity-relationship model.

b.      The object-oriented model.

c.       The semantic data model.

d.      The functional data model.

THE ENTITY-RELATIONSHIP MODEL:

·         The entity-relationship (E-R) data model is a collection of basic objects, called entities, and of relationships among these objects.

·         An entity is a “thing” or “object” in the real world that is distinguishable from other objects. For example, each person is an entity, and bank accounts can be considered as entities.

·         A relationship is an association among several entities. For example, a depositor relationship associates a customer with each account that she has.

The overall logical structure (schema) of a database can be expressed graphically by an E-R diagram, which is built up from the following components:

• Rectangles, which represent entity sets

• Ellipses, which represent attributes

• Diamonds, which represent relationships among entity sets

• Lines, which link attributes to entity sets and entity sets to relationships

2.Record Based Logical Models: These models can also be used in describing the data at the logical and view levels. These models can be classified into,

a. Relational model.

b. Network model.

c. Hierarchal model.

a. Relational Model:

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

·         Each table has multiple columns, and each column has a unique name. Figure presents a sample relational database comprising three tables:

·         It shows how tables are linked, what type of links are between tables, what keys are used, what information is referenced between tables. It's an essential part of developing a normalised database structure to prevent repeat and redundant data storage

One shows details of bank customers, the second shows accounts, and the third shows which accounts belong to which customers.

b. Network Model: It is a modified version of hierarchical database. In network database structure each node may have several parents.

» It is very difficult to develop this type of database structures.

» It is useful for one-to-one and one-to-many record relationships.

» The relationships should be pre-determined. The records in the database are organized as a collection of arbitrary graph.                             

c. Hierarchical Model: - In this database structure, records are logically organised into a hierarchy of relationships and involve an inverted tree like structure. The tree consists of hierarchy of nodes and the uppermost tree is called parent. Every element can have any number of lower level elements, called children, but every node will have only one parent..

 

3. Physical Models: These models can be used in describing the data at the lowest level, i.e. physical level. These models can be classified into 

a. Unifying model 

b. Frame memory model

Q) Explain the Components and Interfaces of Database Management System.

A database management system involves five major components: data, hardware, software, procedure, and users. These components and the interface between the components are shown in Fig. 1.7.

1.      Hardware

The hardware can range from a single personal computer, to a single mainframe, to a network of computers. The particular hardware depends on the requirements of the organization and the DBMS used. Some DBMSs run only on particular operating systems, while others run on a wide variety of operating systems. A DBMS requires a minimum amount of main memory and disk space to run, but this minimum configuration may not necessarily give acceptable performance.

2.      Software

The software includes the DBMS software, application programs together with the operating systems including the network software if the DBMS is being used over a network. The application programs are written in third-generation programming languages like “C,” COBOL, FORTRAN, Ada, Pascal, etc. or using fourth-generation language such as SQL, embedded in a third-generation language. The target DBMS may have its own fourth-generation tools which allow development of applications through the provision of nonprocedural query languages, report generators, graphics generators, and application generators.

The use of fourth-generation tools can improve productivity significantly and produce programs that are easier to maintain.

3.      Data

A database is a repository for data which, in general, is both integrated and shared. Integration means that the database may be thought of as a unification of several otherwise distinct files, with any redundancy among those files partially or wholly eliminated. The sharing of a database refers to the sharing of data by different users, in the sense that each of those users may have access to the same piece of data and may use it for different purposes.

Any given user will normally be concerned with only a subset of the whole database. The main features of the data in the database are listed later:

1. The data in the database is well organized (structured)

2. The data in the database is related

3. The data are accessible in different orders without great difficulty

4.      Procedure

Procedures are the rules that govern the design and the use of database. The procedure may contain information on how to log on to the DBMS, start and stop the DBMS, procedure on how to identify the failed component, how to recover the database, change the structure of the table, and improve the performance.

5.      People Interacting with Database

Here people refers to the people who manages the database, database administrator, people who design the application program, database designer and the people who interacts with the database, database users.

A DBMS is typically run as a back-end server in a local or global network, offering services to clients directly or to Application Servers

Q) Who is DBA. What are the responsibilities of DBA?

DBA-Database Administrator:

Database Administrator is a person having central control over data and programs accessing that data. The database administrator is a manager whose responsibilities are focused on management of technical aspects of the database system. The objectives of database administrator are given as follows:

1. To control the database environment

2. To standardize the use of database and associated software

3. To support the development and maintenance of database application projects

4. To ensure all documentation related to standards and implementation is up-to-date

The summarized objectives of database administrator are shown in Fig. 1.8.

Responsibilities of Database Administrator (DBA)

The responsibility of the database administrator is to maintain the integrity, security, and availability of data. A database must be protected from database and from hardware or software failures that corrupt data. Protection from accidents that cause data inaccuracy is a part of maintaining data integrity.

Protecting the database from unauthorized or malicious use is termed as database security. The responsibilities of the database administrator are summarized as follows:

1. Authorizing access to the database.

2. Coordinating and monitoring its use.

3. Acquiring hardware and software resources as needed.

4. Backup and recovery. DBA has to ensure regular backup of database, incase of damage, suitable recovery procedure are used to bring the database up with little downtime as possible

Q) Explain briefly about different types of Database Users

Database users are the people who need information from the database to carry out their business responsibility. The database users can be broadly classified into two categories like application programmers and end users.

1.      Sophisticated End Users

Sophisticated end users interact with the system without writing programs. They form requests by writing queries in a database query language. These are submitted to query processor. Analysts who submit queries to explore data in the database fall in this category.

2.      Specialized End Users

Specialized end users write specialized database application that does not fit into data-processing frame work. Application involves knowledge base and expert system, environment modeling system, etc.

3.      Naive End Users

Naıve end user interact with the system by using permanent application program Example: Query made by the student, namely number of books borrowed in library database.

4.      System Analysts

System analysts determine the requirements of end user, and develop specification for canned transaction that meets this requirement

Q) Explain Database Architecture

Database architecture essentially describes the location of all the pieces of information that make up the database application. The database architecture can be broadly classified into two-, three-, and multitier architecture.

1.      Two-Tier Architecture

The two-tier architecture is a client–server architecture in which the client contains the presentation code and the SQL statements for data access. The database server processes the SQL statements and sends query results back to the client. The two-tier architecture is shown in Fig. 1.9. Two-tier client/server provides a basic separation of tasks. The client, or first tier, is primarily responsible for the presentation of data to the user and the “server,” or second tier, is primarily responsible for supplying data services to the client

Presentation Services: “Presentation services” refers to the portion of the application which presents data to the user. In addition, it also provides for the mechanisms in which the user will interact with the data. More simply put, presentation logic defines and interacts with the user interface. The presentation of the data should generally not contain any validation rules.

Business Services/objects

“Business services” are a category of application services. Business services encapsulate an organizations business processes and requirements. These rules are derived from the steps necessary to carry out day-today business in an organization. These rules can be validation rules, used to be sure that the incoming information is of a valid type and format, or they can be process

rules, which ensure that the proper business process is followed in order to complete an operation.

Application Services

“Application services” provide other functions necessary for the application.

Data Services

“Data services” provide access to data independent of their location. The data can come from legacy mainframe, SQL RDBMS, or proprietary data access systems. Once again, the data services provide a standard interface for accessing data

Three-tier Architecture

A “Multitier,” often referred to as “three-tier” or “N-tier,” architecture provides greater application scalability, lower maintenance, and increased reuse of components. Three-tier architecture offers a technology neutral method of building client/server applications with vendors who employ standard interfaces which provide services for each logical “tier.” The three-tier architecture is shown in Fig. 1.10.

From this figure, it is clear that in order to improve the performance a second-tier is included between the client and the server. Through standard tiered interfaces, services are made available to the application.

A single application can employ many different services which may reside on dissimilar platforms or are developed and maintained with different tools. This approach allows a developer to leverage investments in existing systems while creating new application which can utilize existing resources. Although the three-tier architecture addresses performance degradations of the two-tier architecture, it does not address division-of-processing concerns. The PC clients and the database server still contain the same division of code although the tasks of the database server are reduced. Multiple-tier architectures provide more flexibility on division of processing.

Q) Explain the Situations where DBMS is not Necessary

It is also necessary to specify situations where it is not necessary to use a DBMS. If traditional file processing system is working well, and if it takes more money and time to design a database, it is better not to go for the DBMS. Moreover if only one person maintains the data and that person is not skilled in designing a database as well as not comfortable in using the DBMS then it is not advisable to go for DBMS.

DBMS is undesirable under following situations:

– DBMS is undesirable if the application is simple, well-defined, and not expected to change.

– Runtime overheads are not feasible because of real-time requirements.

– Multiple accesses to data are not required.

Compared with file systems, databases have some disadvantages:

1. High cost of DBMS this includes:

– Higher hardware costs

– Higher programming costs

– High conversion costs

2. Slower processing of some applications

3. Increased vulnerability

4. More difficult recovery

Q) List out the DBMS Vendors and their Products

Some of the popular DBMS vendors and their corresponding products are given Table 1.1.

Review Questions

1.1. What are the drawbacks of file processing system?

The drawbacks of file processing system are:

– Duplication of data, which leads to wastage of storage space and data inconsistency.

– Separation and isolation of data, because of which data cannot be used together.

– No program data independence.

1.2. What is meant by Metadata?

Metadata are data about data but not the actual data

1.3. Define the term data dictionary?

Data dictionary is a file that contains Metadata.

1.4. What are the responsibilities of database administrator?

1.5. Mention three situations where it is not desirable to use DBMS?

The situations where it is not desirable to use DBMS are:

– The database and applications are not expected to change.

– Data are not accessed by multiple users.

1.6. What is meant by data independence?

Data independence renders application programs (e.g., SQL scripts) immune to changes in the logical and physical organization of data in the system.

Logical organization refers to changes in the Schema. Example adding a column or tuples does not stop queries from working.

Physical organization refers to changes in indices, file organizations, etc.

1.7. What is meant by Physical and Logical data independence?

In logical data independence, the conceptual schema can be changed without changing the external schema. In physical data independence, the internal schema can be changed without changing the conceptual schema.

1.8. What are some disadvantages of using a DBMS over flat file system?

– DBMS initially costs more than flat file system

– DBMS requires skilled staff

1.9. What are the steps to design a good database?

– First find out the requirements of the user

– Design a view for each important application

– Integrate the views giving the conceptual schema, which is the union of all views

– Map to the data model provided by the DBMS (usually relational)

– Design external views

– Choose physical structures (indexes, etc.)

1.10. What is Database? Give an example.

A Database is a collection of related data. Here, the term “data” means that known facts that can be record. Examples of database are library information system, bus, railway, and airline reservation system, etc.

1.11. Define – DBMS.

DBMS is a collection of programs that enables users to create and maintain a database.

1.12. Mention various types of databases?

The different types of databases are:

– Multimedia database

– Spatial database (Geographical Information System Database)

– Real-time or Active Database

– Data Warehouse or On-line Analytical Processing Database

1.13. Mention the advantages of using DBMS?

The advantages of using DBMS are:

– Controlling Redundancy

– Enforcing Integrity Constraints so as to maintain the consistency of the database

– Providing Backup and recovery facilities

– Restricting unauthorized access

– Providing multiple user interfaces

– Providing persistent storage of program objects and datastructures

1.14. What is “Snapshot” or “Database State”?

The data in the database at a particular moment is known as “Database

State” or “Snapshot” of the Database

1.15. Define Data Model.

It is a collection of concepts that can be used to describe the structure of a database.

The datamodel provides necessary means to achieve the abstraction i.e., hiding the details of data storage.

1.16. Mention the various categories of Data Model.

The various categories of datamodel are:

– High Level or Conceptual Data Model (Example: ER model)

– Low Level or Physical Data Model

– Representational or Implementational Data Model

– Relational Data Model

– Network and Hierarchal Data Model

– Record-based Data Model

– Object-based Data Model

Define the concept of “database schema.” Describe the types of schemas that exist in a database complying with the three levels ANSI/SPARC architecture.

Database schema is nothing but description of the database. The types of schemas that exist in a database complying with three levels of ANSI/SPARC architecture are:

– External schema

– Conceptual schema

– Internal schema