DATA BASE MANAGEMENT SYSTEM KCS 501

(1)

Er. Navnish Goel

Assistant Professor Department of Information Technology

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 1

DATA BASE MANAGEMENT SYSTEM – KCS 501

Programme: CSE / IT Degree: B. Tech. Year: III

Course: Database Management Systems Design Semester: V Credits: 3

Course Code: KCS501 Course Type: Core

Course Area/Domain: System Software Concepts Contact Hours: 3 Hours / Week

Corresponding Lab Course Code (If Any): KCS551 Lab Course Name: Database Management Systems Lab Syllabus

KCS-501: Database Management Systems 4-0-0

Unit Topic Proposed

Lecture I Introduction: Overview, Database System vs File System, Database System Concept

and Architecture, Data Model Schema and Instances, Data Independence and Database Language and Interfaces, Data Definitions Language, DML, Overall Database Structure. Data Modelling Using the Entity Relationship Model: ER Model Concepts, Notation for ER Diagram, Mapping Constraints, Keys, Concepts of Super Key, Candidate Key, Primary Key, Generalization, Aggregation, Reduction of an ER Diagrams to Tables, Extended ER Model, Relationship of Higher Degree.

08

II Relational data Model and Language: Relational Data Model Concepts, Integrity Constraints, Entity Integrity, Referential Integrity, Keys Constraints, Domain Constraints, Relational Algebra, Relational Calculus, Tuple and Domain Calculus.

Introduction on SQL: Characteristics of SQL, Advantage of SQL. Sql Data Type and Literals. Types of SQL Commands. SQL Operators and Their Procedure. Tables, Views and Indexes. Queries and Sub Queries. Aggregate Functions. Insert, Update and Delete Operations, Joins, Unions, Intersection, Minus, Cursors, Triggers, Procedures in SQL/PL SQL

08

III Data Base Design & Normalization: Functional dependencies, normal forms, first, second, 8 third normal forms, BCNF, inclusion dependence, loss less join decompositions, normalization using FD, MVD, and JDs, alternative approaches to database design

08

IV Transaction Processing Concept: Transaction System, Testing of Serialability, Serialability of Schedules, Conflict & View Serializable Schedule, Recoverability, Recovery from Transaction Failures, Log Based Recovery, Checkpoints, Deadlock Handling. Distributed Database: Distributed Data Storage, Concurrency Control and Directory System.

08

V Concurrency Control Techniques: Concurrency Control, Locking Techniques for Concurrency Control, Time Stamping Protocols for Concurrency Control, Validation Based Protocol, Multiple Granularity, Multi Version Schemes, Recovery with Concurrent Transaction, Case Study of Oracle.

08

Total Hours 40

Text / Reference Books:

1. Korth, Silbertz, Sudarshan,” Database Concepts”, McGraw Hill 2. Date C J, “An Introduction to Database Systems”, Addision Wesley 3. Elmasri, Navathe, “Fundamentals of Database Systems”, Addision Wesley 4. RAMAKRISHNAN"Database Management Systems",McGraw Hill

5. Bipin C. Desai, “An Introduction to Database Systems”, Gagotia Publications

(2)

Er. Navnish Goel

Course Objective:

1. To impart the basic understanding of the theory and applications of database management systems.

2. To give basic level understanding of internals of database systems.

3. To expose to some of the recent trends in databases.

By the end of the course, students will be able to:

Course Outcome:

KCS501.1 understand the concepts of DBMS and would have acquired skills to analyse the real- world problem domains in the context of DBMS and demonstrate the same through ER diagram.

KCS501.2 apply and demonstrate with understanding of relational query languages such as SQL, Relational Algebra & Relational Calculus. The students will be able to construct an Entity-Relationship (E-R) model from specifications and to perform the transformation of the conceptual model into corresponding logical data structures.

KCS501.3 design a relational database following the design principles. To relate the concepts of inference rules, data constraints and normalization. Students would also have acquired skills to identify application of the same.

KCS501.4 understand basic issues of transaction processing, concurrency control and serializability.

KCS501.5 define, explain and illustrate fundamental principles of data organization, query optimization. To classify various concurrency control techniques and recovery procedures.

WEB SOURCE REFERENCES:

1. https://nptel.ac.in/courses/106/105/106105175/

2. www.tutorialspoint.com/plsql

3. https://swayam.gov.in/nd1_noc20_cs09/preview

(3)

Er. Navnish Goel

UNIT-I (CHAPTER 1)

IMPORTANT QUESTION AND ANSWERS 1. What is Data?

Data is a collection of raw, unorganised facts and details like text, observations, figures, symbols and description of any things etc. In other words, data does not carry any specific purpose and has no significance itself. Moreover, data is measured in terms of bits and bytes - which are basic units of information in the context of computer storage and processing. Name of a student, age, class and name of subjects can be counted as data for recording purposes.

Collection of related data is known as Database. The collection of data, usually referred to as the database, contains information relevant to an enterprise. For example: Set of student info.

2. What is Information?

Information is the processed, organised and structured data. It provides context for data and enables decision making. For example, if we have data about marks obtained by all students, we can then find out toppers of the class and average marks etc.

3. Explain Data Vs Information in tabular form?

Basis for

Comparison Data Information

Meaning Data means raw facts gathered about someone or something, which is bare and random.

Facts, concerning a particular event or subject, which are refined by processing is called information.

What is it? It is just text and numbers. It is refined data or meaningful data Based on Records and Observations Analysis

Form Unorganized Organized

Useful May or may not be. Always

Dependency Does not depend on information.

Without data, information cannot be processed.

4. What do you mean by database management system?

(4)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 4 A database-management system (DBMS) is a collection of interrelated data and a set of programs to access these data. A database management system stores data, in such a way which is easier to retrieve, manipulate and helps to produce information. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. Database systems are designed to manage large bodies of information. Management of data involves both defining structures for storage of information and providing mechanisms for the manipulation of information. In addition, the database system must ensure the safety of the information storage, system crashes or unauthorized access.

Interface Between User and Files

Disk Figure 1: Flow of DBMS

5. Define DBMS and File management system (FMS)?

Database management system (DBMS) is a collection of interrelated data and a set of programs to access those data. Some well-known DBMS software are Microsoft Access, Microsoft SQL Server, Oracle, SAP, dBASE, FoxPro, IBM dB2, SQLite etc.

Figure 2: FMS Vs DBMS

A file management system is an abstraction to store, retrieve, management and update a set of files. In simple terms, a File Management System (FMS) is a kind of Database Management System that allows access to single files or tables at a time. FMS’s accommodate flat files that

USER

DBMS SOFTWARE

DB FILES

(5)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 5 have no relation to other files. The FMS was the predecessor for the Database Management System (DBMS), which allows access to multiple files or tables at a time.

6. Explain the difference between file systems, DBMS in detail.

File Management System Database Management System

File System is a general, easy-to-use system to store general files which require less security and constraints.

Database management system is used when security constraints are high.

Data Redundancy is more in file management system.

Data Redundancy is less in database management system.

Data Inconsistency is more in file system. Data Inconsistency is less in database management system.

Centralization is hard to get when it comes to File Management System.

Centralization is achieved in Database Management System.

User locates the physical address of the files to access data in File Management System.

In Database Management System, user is unaware of physical address where data is stored.

Security is low in File Management System. Security is high in Database Management System.

7. What are the disadvantages of file management system over DBMS?

The disadvantages of file management systems over DBMS are:

a) Data redundancy and inconsistency.

b) Difficulty in accessing data.

c) Data isolation.

d) Integrity problems.

e) Atomicity problems.

f) Concurrent access anomalies.

8. What are the advantages of DBMS over file management system?

The advantages of DBMS over file management system are:

a) Control redundancy

b) Restrict unauthorized access

(6)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 6 c) Provide multiple user interfaces

d) Enforce integrity constraints.

e) Provide backup and recover.

9. Explain Database Users with their types?

DBMS users are those persons who work with a database for different purposes. The users of DBMS are categized into two groups.

A) END USER

➢ End users are those who actually reap the benefit of having a DBMS.

➢ End users are the people whose jobs require access to the database for a querying, updating and generating reports.

➢ Other subtypes of end uses are Casual End User, Naïve, Sophisticated, Stand Alone End User.

END USER ADMINISTRATOR DESIGNERS (OPTIONAL)

Figure 3: DATABASE USERS

B) DBA (Data Base Administrator)

Data Base Administrator maintain the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used.

10. Is there any difference between DBMS and file management system in terms of ACID properties?

DBMS ensures data integrity by managing transactions through ACID property.

A = Atomicity.

C = Consistency.

I = Isolation D = Durability.

While such integrity is absent in file management system.

DBMS

(7)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 7 UNIT-I (CHAPTER 2)

IMPORTANT QUESTION AND ANSWERS

11. Explain view of data at different levels of abstraction or data abstraction?

Abstraction is one of the main features of database systems. Hiding irrelevant details from user and providing abstract view of data to users, helps in easy and efficient user-database interaction. In other words, Abstraction is the process to hide the irrelevant things from the users and represent the relevant things to the user. Database systems contain of complex data structures. Thus, to retrieval the data and reduce the complexity of the users, we use the data abstraction method. There are mainly three levels of data abstraction:

a) Internal Level: Actual physical storage structure and access paths.

b) Conceptual or Logical Level: Structure and constraints for the entire database c) External or View level: Describes various user views.

Figure 4: View of Data / Data Abstraction

External View – This is the highest level of abstraction as seen by a user. It describes only the part of entire database, which is relevant to a particular user.

Conceptual View – This is the next higher level of abstraction which is the sum total of Database Management System user's views. It describes what data are actually stored in the database. It contains information about entire database in terms of a small number of relatively simple structure.

Internal View – This is the lowest level of abstraction. It describes how the data are physically stored and describes the data structures and access methods to be used by the database.

(8)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 8 12. Explain Instance and Schema?

The description of a database is called the database schemas, which is specified during database design and is not expected to change frequently for example student schema

Name Roll_no Class Marks

Course Course_no Department

Figure 5: Schemas and Instance

Every time we insert or delete a record or change the values of a data items in a record, we change one stage of database into another stage.

13. Explain Data Models?

Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. To illustrate the concept of a data model, first we will try to understand few other terms related to data base.

Schema - description of data at some level (e.g., tables, attributes, constraints, domains) Model - tools and languages for describing:

• Languages – Conceptual / logical and external schema described by the data definition language (DDL).

• Integrity constraints, domains described by DDL

• Operations on data described by the data manipulation language (DML) Types of data models:

a) Entity-Relationship model - Proposed by P.P. Chen in 1970s. E-R modelling is a conceptual level model.

• Entities are real-world objects about which we collect data, represented by rectangles.

• Attributes describe the entities, it is represented by ellipses.

• Relationships are associations among entities, it is represented by diamonds.

• Entity set - set of entities of the same type.

• Relationship set - set of relationships of same type.

• Relationships sets may have descriptive attributes.

b) Relational Model

The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. It uses 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.

(9)

Er. Navnish Goel

(10)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 10 The main highlights of this model are −

• Data is stored in tables called relations.

• Relations can be normalized.

• In normalized relations, values saved are atomic values.

• Each row in a relation contains a unique value.

• Each column in a relation contains values from a same domain.

14. Explain various symbols used to draw an E-R diagram and illustrate example using given information about a bank, customers and their account. Customer has a name, address which consists of house number, area and city, and one or more phone numbers. Account has number, type and balance. We need to record customers who own an account. Account can be held individually or jointly. An account cannot exist without a customer. Arrive at an E-R diagram. Clearly indicate attributes, keys, the cardinality ratios and participation constraints.?

Figure 5: Notations of E-R diagram

(11)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 11 Figure 6: Banking management information about a bank, customers, their account details.

15. What do you mean by Data Independence? Explain the types of Data Independence (D/I)?

(12)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 12 16. Explain Database Languages in details?

(13)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 13 There are two types of DML

[1] Procedural DMLs. A query in procedural DML requires the user to specify not only ―what data is required to be extracted from the database‖ but also to specify ―how to extract those data‖.

[2] Non-Procedural DMLs. A Query in Non-Procedural DML requires the user to specify only

―what data is needed‖, without specifying how to get those data. Non-procedural DMLs are easier to learn and to use than the procedural DMLs. However, since non-Procedural DMLs do not specify ―how to get the data‖.

This limitation of Non-Procedural DMLs is overcome by performing query optimization at the System Level.

17. What do you mean by Referential Integrity?

Referential integrity requires that a foreign key must have a matching primary key or it must be null. This constraint is specified between two tables (parent and child); it maintains the correspondence between rows in these tables. It means the reference from a row in one table to another table must be valid. Examples of referential integrity constraint in the Customer/Order database of the Company:

Customer (CustID, CustName) Order (OrderID, CustID, OrderDate)

To ensure that there are no orphan records, we need to enforce referential integrity. An orphan record is one whose foreign key FK value is not found in the corresponding entity – the entity where the PK is located. Recall that a typical join is between a PK and FK.

The referential integrity constraint states that the customer ID (CustID) in the Order table must match a valid CustID in the Customer table. Most relational databases have declarative referential integrity. In other words, when the tables are created the referential integrity constraints are set up.

Here is another example from a Course/Class database:

Course (CrsCode, DeptCode, Description) Class (CrsCode, Section, ClassTime)

The referential integrity constraint states that CrsCode in the Class table must match a valid CrsCode in the Course table. In this situation, it’s not enough that the CrsCode and Section in the Class table make up the PK, we must also enforce referential integrity.

(14)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 14 18. Construct E-R Diagram for College management system?

(15)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 15 19. Construct E-R diagram for Hospital Management System?

(16)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 16 20. Reduced given E-R diagram into table?

The database can be represented using the notations, and these notations can be reduced to a collection of tables. In the database, every entity set or relationship set can be represented in tabular form. The ER diagram is given below:

There are some points for converting the ER diagram to the table:

o Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

o All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

o A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key attribute of the entity.

o The multivalued attribute is represented by a separate table.

In the student table, a hobby is a multivalued attribute. So it is not possible to represent multiple values in a single column of STUDENT table. Hence we create a table STUD_HOBBY with column name STUDENT_ID and HOBBY. Using both the column, we create a composite key.

o Composite attribute represented by components.

In the given ER diagram, student address is a composite attribute. It contains CITY, PIN, DOOR#, STREET, and STATE. In the STUDENT table, these attributes can merge as an individual column.

o Derived attributes are not considered in the table.

In the STUDENT table, Age is the derived attribute. It can be calculated at any point of time by calculating the difference between current date and Date of Birth.

(17)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 17 Using these rules, you can convert the ER diagram to tables and columns and assign the mapping between the tables. Table structure for the given ER diagram is as below

Figure: Table structure

(18)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 18 UNIT-II (CHAPTER 1)

IMPORTANT QUESTION AND ANSWERS

21. Define Integrity Constraints?

Integrity Constraints

• Integrity constraints are a set of rules. It is used to maintain the quality of information.

• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.

• Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

A. DOMAIN CONSTRAINTS

• Domain constraints can be defined as the definition of a valid set of values for an attribute.

• The data type of domain includes string, character, integer, time, date, currency, etc.

The value of the attribute must be available in the corresponding domain.

CREATE TABLE customer_details (

customer_id character varying(255) NOT NULL, customer_name character varying(255) NOT NULL, quantity integer NOT NULL,

date_purchased date );

CREATE TABLE Student (

s_id int NOT NULL, name varchar(60), age int

);

If you wish to alter the table after it has been created, then we can use the ALTER command for it: ALTER TABLE Student MODIFY s_id int NOT NULL;

CREATE TABLE employee (

id number(5) PRIMARY KEY, name char(20),

dept char(10), age number(2),

gender char(1) CHECK (gender in ('M','F')), salary number(10),

location char(10) );

(19)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 19 CHECK CONSTRAINT AT TABLE LEVEL:

id number(5) PRIMARY KEY, name char(20),

dept char(10), age number(2), gender char(1), salary number(10), location char(10),

CONSTRAINT gender_ck CHECK (gender in ('M','F')) );

USING CHECK CONSTRAINT AT TABLE LEVEL CREATE table Student

(

s_id int NOT NULL CHECK(s_id > 0), Name varchar (60) NOT NULL,

Age int );

ALTER table Student ADD CHECK(s_id > 0)

(20)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 20 B. ENTITY CONSTRAINTS

• The entity integrity constraint states that primary key value can't be null.

• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.

• A table can contain a null value other than the primary key field.

CREATE TABLE Students (

Student_ID int NOT NULL,

Student_Name varchar(255) NOT NULL, Class_Name varchar(255) UNIQUE, Age int,

PRIMARY KEY (Student_ID) );

ALTER table Student ADD PRIMARY KEY (s_id);

PRIMARY KEY AT COLUMN LEVEL:

id number(5), name char(20), dept char(10), age number(2), salary number(10), location char(10),

CONSTRAINT emp_id_pk PRIMARY KEY (id) );

PRIMARY KEY AT TABLE LEVEL:

id number(5), NOT NULL, name char(20),

dept char(10), age number(2), salary number(10), location char(10),

ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id) );

(21)

Er. Navnish Goel

S. D. COLLEGE OF ENGINEERING TECHNOLOGY Page 21 C. REFERENTIAL INTEGRITY CONSTRAINTS

• A referential integrity constraint is specified between two tables.

• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

CREATE TABLE Department (

Department_ID int NOT NULL,

Department_Name varchar(255) NOT NULL, PRIMARY KEY(Department_ID)

);

CREATE TABLE Employees (

Employee_ID int NOT NULL,

Employee_Name varchar(255) NOT NULL, Department int NOT NULL,

Age int,

FOREIGN KEY (Department) REFERENCES Department(Department_ID) );