DBMS (R23 Syllabus)

UNIT - I
Syllabus: Introduction: Database system, Characteristics (Database Vs File System), Database Users,
Advantages of Database systems, Database applications. Brief introduction of different Data Models; Concepts
of Schema, Instance and data independence; Three tier schema architecture for data independence; Database
system structure, environment, Centralized and Client Server architecture for the database.
Entity Relationship Model: Introduction, Representation of entities, attributes, entity set, relationship,
relationship set, constraints, sub classes, super class, inheritance, specialization, generalization using ER
Diagrams.
Introduction
1.1 Database System
Data: Data is the collection of raw facts. It can be in the form of numbers, text, images, or other types of data.
Information: Processed data is the information.
Database System: The database is the collection of data that is stored in the system. It can be thought of as a
container that holds the data.
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 stored, despite system crashes or
attempts at unauthorized access. If data are to be shared among several users, the system must avoid possible
anomalous results.
Database Management System (DBMS): The DBMS is the software that manages the database. It provides a
interface between the user and the database, and allows users to create, modify, and query the database.
Or
A database-management system (DBMS) is a collection of interrelated data and a set of programs to access
those data.
1.2 Characteristics (Database Vs File System):
A shared collection of logically related data along with the description of the data that suits to the needs of
large enterprises.

This unit describes the basic differences between the traditional way of processing, also called as file processing,
and the database method of processing the data. Every operating system provides users to open, save, and close
a file. The users can store appropriate information in these files. Take a look at the Figure 1.1 which shows the
traditional file processing system that stores the program and data description in a file. The related information
of a particular application is stored in various files named as File1, File2, etc., and these files are manipulated
using Program1, Program2, etc. This is the method that was used in early days.
It means that without a DBMS, the data will simply be dumped into one or more files. For any updation, the
files need to be opened and manually search for the line or record, update and then save the file. Now you can
understand the difficulties involved in marinating this type of information storage.

With the advent of database systems, the file processing approach is no longer used. Now you can observe with
the Figure 1.2 that the database is in the disk which in turn is controlled by the DBMS. In this approach the
Application Program-1 along with its data semantics, Application Program-2 along with its data semantics, etc.,
interact with the database where the actual data and constraints are stored through the DBMS. The DBMS
provides the necessary control and manipulation software modules for these application programs to access the
data stored in the database.
Drawbacks of File Processing System
1.Catalog: In DBMS, the database structure is stored in a catalog and it also contains the storage details with
constraints.
The DBMS software must equally work with any number of database applications provided the catalog contains
the structure and other details of that application. In file processing the data definition is part of the application
program.
Example: Record declaration in Pascal. Class or structure declaration in C++.
2. Program-data independence: In file processing, if changes are done in the structure of the file, then we may
require changing the program design that accesses it. In DBMS the access programs are written independent of
any specific files. This is called as program-data independence.
The DBMS stores the data in such a way that the user need not be aware of these details. This concept is called
as data abstraction and it may also be called as conceptual representation
3. Views: A database may have many users and each one may be interested on a particular view of the
application. A view is conceptually a table, but the records of this table are not stored in the database.
Example: Consider the Student database in which we can think of two views:
View 1: Students Grade in various courses. To obtain this information the tables Course and Grade_Report are
to be joined and created as a view.
View 2: If we want to know the Prerequisite Courses that a student needs to study, three tables are to be joined.
These tables are nothing but Student, Section and Prerequisite.
4. Sharing and Transaction processing: A DBMS must provide control for various users trying to access the
database.

Example: Railway Reservation System with multiple counters.

Whenever several users try to access the same application at the same time, we call this situation as concurrent
transaction processing. Generally, the concurrent access is achieved with a simple Local Area Network (LAN).
It is also possible to book railway tickets online i.e. through Internet.

1.3 Database users

Database Users and User Interfaces There are four different types of database-system users, differentiated by
the way they expect to interact with the system. Different types of user interfaces have been designed for the
different types of users.
Naive Users are unsophisticated users who interact with the system by invoking one of the application programs
that have been written previously. For example, a clerk in the university who needs to add a new instructor to
department A invokes a program called new hire. This program asks the clerk for the name of the new instructor,
her new ID, the name of the department (that is, A), and the salary. The typical user interface for naive users is
a forms interface, where the user can fill in appropriate fields of the form. Naive users may also simply read
reports generated from the database. As another example, consider a student, who during class registration
period, wishes to register for a class by using a Web interface. Such a user connects to a Web application
program that runs at a Web server. The application first verifies the identity of the user, and allows her to access
a form where she enters the desired information. The form information is sent back to the Web application at
the server, which then determines if there is room in the class (by retrieving information from the database) and
if so adds the student information to the class roster in the database.
Application programmers are computer professionals who write application programs. Application
programmers can choose from many tools to develop user interfaces. Rapid application development (RAD)
tools are tools that enable an application programmer to construct forms and reports with minimal programming
effort.
Sophisticated users interact with the system without writing programs. Instead, they form their requests either
using a database query language or by Apago PDF Enhancer using tools such as data analysis software. Analysts
who submit queries to explore data in the database fall in this category
Specialized users are sophisticated users who write specialized database applications that do not fit into the
traditional data-processing framework. Among these applications are computer-aided design systems,
knowledge base and expert systems, systems that store data with complex data types (for example, graphics
data and audio data), and environment- modelling systems.
Database Administrators: One of the main reasons for using DBMSs is to have central control of both the
data and the programs that access those data. A person who has such central control over the system is called a
database administrator (DBA).
The functions of a DBA include:
• Schema definition. The DBA creates the original database schema by executing a set of data definition
statements in the DDL.
• Storage structure and access-method definition.
• Schema and physical-organization modification. The DBA carries out changes to the schema and physical
organization to reflect the changing needs of the organization, or to alter the physical organization to improve
performance
1.4 Advantages of Database Systems
Data redundancy and inconsistency. Since different programmers create the files and application programs
over a long period, the various files are likely to have different structures and the programs may be written in
several programming languages. Moreover, the same information may be duplicated in several places (files).
For example, if a student has a double major (say, music and mathematics) the address and telephone number
of that student may appear in a file that consists of student records of students in the Music department and in a
file that consists of student records of students in the Mathematics department. This redundancy leads to higher
storage and access cost. In addition, it may lead to data inconsistency; that is, the various copies of the same
data may no longer agree. For example, a changed student address may be reflected in the Music department
records but not elsewhere in the system.
Difficulty in accessing data. Suppose that one of the university clerks needs to find out the names of all students
who live within a particular postal-code Apago PDF Enhancer area. The clerk asks the data-processing
department to generate such a list. Because the designers of the original system did not anticipate this request,
there is no application program on hand to meet it. There is, however, an application program to generate the
list of all students. The university clerk has now two choices: either obtain the list of all students and extract the
needed information manually or ask a programmer to write the necessary application program. Both alternatives
are obviously unsatisfactory. Suppose that such a program is written, and that, several days later, the same clerk
needs to trim that list to include only those students who have taken at least 60 credit hours. As expected, a
program to generate such a list does not exist. Again, the clerk has the preceding two options, neither of which
is satisfactory.
Data isolation. Because data are scattered in various files, and files may be in different formats, writing new
application programs to retrieve the appropriate data is difficult.
• Integrity problems. The data values stored in the database must satisfy certain types of consistency
constraints. Suppose the university maintains an account for each department, and records the balance amount
in each account. Suppose also that the university requires that the account balance of a department may never
fall below zero. Developers enforce these constraints in the system by adding appropriate code in the various
application programs. However, when new constraints are added, it is difficult to change the programs to enforce
them. The problem is compounded when constraints involve several data items from different files.
Atomicity problems. A computer system, like any other device, is subject to failure. In many applications, it is
crucial that, if a failure occurs, the data be restored to the consistent state that existed prior to the failure.
Consider a program to transfer $500 from the account balance of department A to the account balance of
department B. If a system failure occurs during the execution of the program, it is possible that the $500 was
removed from the balance of department A but was not credited to the balance of department B, resulting in an
inconsistent database state. Clearly, it is essential to database consistency that either both the credit and debit
occur, or that neither occur. That is, the funds transfer must be atomic—it must happen in its entirety or not at
all. It is difficult to ensure atomicity in a conventional file-processing system.
Concurrent-access anomalies. For the sake of overall performance of the system and faster response, many
systems allow multiple users to update the data simultaneously. Indeed, today, the largest Internet retailers may
have millions of accesses per day to their data by shoppers. In such an environment, interaction of concurrent
updates is possible and may result in inconsistent data. Consider department A, with an account balance of
$10,000. If two department clerks debit the account balance (by say $500 and $100, re spectively) of department
A at almost exactly the same time, the result of the concurrent executions may leave the budget in an incorrect
(or inconsistent) state. Suppose that the programs executing on behalf of each withdrawal read the old
balance, reduce that value by the amount being withdrawn, and write the result back. If the two programs run
concurrently, they may both read the value $10,000, and write back $9500 and $9900, respectively. Depending
on which one writes the value last, the account balance of department A may contain either $9500 or $9900,
rather than the correct value of $9400. To guard against this possibility, the system must maintain some form
of supervision. But supervision is difficult to provide because data may be accessed by many different
application programs that have not been coordinated previously.
1.5 Database Applications
Databases are widely used. Here are some representative applications:
Enterprise Information
Sales: For customer, product, and purchase information.
Accounting: For payments, receipts, account balances, assets and other accounting information.
Human resources: For information about employees, salaries, payroll taxes, and benefits, and for generation
of pay checks.
Manufacturing: For management of the supply chain and for tracking production of items in factories,
inventories of items in warehouses and stores, and orders for items.
Online retailers: For sales data noted above plus online order tracking, generation of recommendation lists,
and maintenance of online product evaluations.
Banking and Finance
◦ Banking: For customer information, accounts, loans, and banking transac tions. ◦
Credit card transactions: For purchases on credit cards and generation of monthly statements.
◦ Finance: For storing information about holdings, sales, and purchases of financial instruments such as stocks
and bonds; also for storing real-time market data to enable online trading by customers and automated trading
by the firm.
Universities: For student information, course registrations, and grades (in addition to standard enterprise
information such as human resources and accounting).
 Airlines: For reservations and schedule information. Airlines were among the first to use databases in a
geographically distributed manner.
Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on
prepaid calling cards, and storing information Apago PDF Enhancer about the communication networks.

The importance of database systems can be judged in another way—today, database system vendors like
Oracle are among the largest software companies in the world, and database systems form an important part of
the product line of Microsoft and IBM.
1.6 Brief introduction of different 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. A data model provides a way to describe the
design of a database at the physical, logical, and view levels.
There are a number of different data models that we shall cover in the text. The data models can be classified
into four different categories:
• 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. Tables are also known
as relations. The relational model is an example of a record-based model. Record-based models are so named
because the database is structured in fixed-format records of several types. Each table contains records of a
particular type. Each record type defines a fixed number of fields, or attributes. The columns of the table
correspond to the attributes of the record type. The relational data model is the most widely used data model,
and a vast majority of current database systems are based on the relational model.
The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships
among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other
objects.
Object-Based Data Model. Object-oriented programming (especiallyinJava, C++, or C#) has become the
dominant software-development methodology. This led to the development of an object-oriented data model
that can be seen as extending the E-R model with notions of encapsulation, methods (functions), and object
identity. The object-relational data model combines features of the object-oriented data model and relational
data model.
• Semistructured Data Model. The semi structured data model permits the specification of data where
individual data items of the same type may have different sets of attributes. This is in contrast to the data models
mentioned earlier, where every data item of a particular type must have the same set of attributes. The Extensible
Markup Language (XML) is widely used to represent semi structured data.
1.7 Concept of Schema
When we talk about a database, we must differentiate between the database schema, which is the logical design
of the database, and the database instance, which is a snapshot of the data in the database at a given instant in
time. The concept of a relation corresponds to the programming-language notion of a variable, while the concept
of a relation schema corresponds to the programming-language notion of type definition. In general, a relation
schema consists of a list of attributes and their corresponding domains.

The schema for that relation is

department (dept name, building, budget)
1.8 Database Instance
It is important that we distinguish these two terms individually. Database schema is the skeleton of database. It
is designed when the database doesn't exist at all. Once the database is operational, it is very difficult to make
any changes to it. A database schema does not contain any data or information.
A database instance is a state of operational database with data at any given time. It contains a snapshot of the
database. Database instances tend to change with time. A DBMS ensures that its every instance (state) is in a
valid state, by diligently following all the validations, constraints, and conditions that the database designers
have imposed.
1.9 Data Independence
A very important advantage of using a DBMS is that it offers data independence. That is, application programs
are insulated from changes in the way the data is structured and stored. Data independence is achieved through
use of the three levels of data abstraction; in particular, the conceptual schema and the external schema provide
distinct benefits in this area.
Relations in the external schema (view relations) are in principle generated on demand from the relations
corresponding to the conceptual schema.4 If the underlying data is reorganized, that is, the conceptual schema
is changed, the definition of a view relation can be modified so that the same relation is computed as before.
For example, suppose that the Faculty relation in our university database is replaced by the following two
relations:
Faculty_public(fid: string, fname: string, office: integer)
Faculty_private (J£d: string, sal: real)
Intuitively, some confidential information about faculty has been placed in a separate relation and information
about offices has been added. The Course info view relation can be redefined in terms of Faculty_public and
Faculty_private, which together contain all the information in Faculty, so that a user who queries Courseinfo
will get the same answers as before.
1.10 Three tier schema architecture for data independence;
A database schema is the skeleton structure that represents the logical view of the entire database. It defines
how the data is organized and how the relations among them are associated. It formulates all the constraints that
are to be applied on the data.
A database schema defines its entities and the relationship among them. It contains a descriptive detail of the
database, which can be depicted by means of schema diagrams. It’s the database designers who design the
schema to help programmers understand the database and make it useful.

A database schema can be divided broadly into two categories –

 Physical Database Schema − this schema pertains to the actual storage of data and its form of storage like
files, indices, etc. It defines how the data will be stored in a secondary storage.
 Logical Database Schema − this schema defines all the logical constraints that need to be applied on the data
stored. It defines tables, views, and integrity constraints.
1.11 Database System Structure and Environment
Storage manager: The storage manager is the component of a database system that provides the interface
between the low-level data stored in the database and the application programs and queries submitted to the
system. The storage manager is responsible for the interaction with the file manager. The raw data are stored
on the disk using the file system provided by the operating system. The storage manager translates the various
DML statements into low-level file-system commands.
Thus, the storage manager is responsible for storing, retrieving, and updating data in the database.
The storage manager components include:
Authorization and integrity manager, which tests for the satisfaction of integrity constraints and checks the
authority of users to access data.
• Transaction manager, which ensures that the database remains in a consis tent (correct) state despite
system failures, and that concurrent transaction executions proceed without conflicting.
• File manager,which manages the allocation of space on disk storage and the data structures used to
represent information stored on disk.
• Buffer manager,which is responsible for fetching data from disk storage into main memory, and deciding
what data to cache in main memory. The buffer manager is acritical part of the database system, since it
enables the database to handle data sizes that are much larger than the size of main memory.
The storage manager implements several data structures as part of the physical system implementation:
• Datafiles, which store the database itself.
• Data dictionary, which stores metadata about the structure of the database, in particular the schema of the
database.
• Indices, which can provide fast access to data items. Like the index in this textbook, a database index provides
pointers to those data items that hold a particular value. For example, we could use an index to find the instructor
record with a particular ID, or all instructor records with a particular name. Hashing is an alternative to indexing
that is faster in some but not all cases
The Query Processor
The query processor components include:
DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary.
DML compiler, which translates DML statements in a query language into an evaluation plan consisting of
low-level instructions that the query evaluation engine understands.
A query can usually be translated into any of a number of alternative evaluation plans that all give the same
result. The DML compiler also performs query optimization; that is, it picks the lowest cost evaluation plan
from among the alternatives.
• Query evaluation engine, which executes low-level instructions generated by the DML compiler.

1.12 Centralized and Client Server architecture for the database.

In a two-tier architecture, the application resides at the client machine, where it invokes database system
functionality at the server machine through query language statements. Application program interface standards like
ODBC and JDBC are used for interaction between the client and the server.

In contrast, in a three-tier architecture, the client machine acts as merely a front end and does not contain any
direct database calls. Instead, the client end communicates with an application server, usually through a forms
interface. The application server in turn communicates with a database system to access data. The business logic
of the application, which says what actions to carry out under what conditions, is embedded in the application
server, instead of being distributed across multiple clients. Three-tier applications are more appropriate for large
applications, and for applications that run on the World Wide Web.
Entity Relationship Model
1.13 Introduction
The Entity-Relationship (ER) Model is a conceptual framework used to describe the structure of a
database in terms of entities, their attributes, and the relationships between them. It was introduced by
Peter Chen in 1976 and is commonly used in the design phase of database systems to visually represent
 the data requirements and relationships before the actual database is created.
Advantages of the ER Model:
 Easy to Understand: The ER model provides an intuitive and easy-to-understand view of data
relationships.
 Comprehensive: It captures all essential aspects of the data model, including entities,
attributes, and relationships.
 Flexible: The model can be adapted to describe complex relationships, including hierarchies and
weak entities.
Limitations:
 Static Representation: The ER model captures data at a specific point in time and doesn't
represent changes or dynamics over time.
 Not Suitable for Some Database Operations: Some database operations, like performance
optimization or indexing, are not covered in the ER model.
1.14 Representation of Entities
Entity: An entity is an object and it is represented by rectangle symbol.
Student College

For example: In the following ER diagram we have two entities Student and College and these two entities
have many to one relationship as many student’s study in a single college.

Student College
study
Types of Entities: An entity is classified into two types. 1. Weak Entity and 2. Strong Entity
1. Weak Entity: An entity that cannot be uniquely identified by its own attributes and relies on
the relationship with other entity is called weak entity. The weak entity is represented by a
double rectangle.

For example – a bank account cannot be uniquely identified without knowing the bank to
which the account belongs, so bank account is a weak entity.

Bank Account Bank

Is opened in

Weak entity Strong entity

1. Strong Entity: A strong entity is an entity that can exist independently and is uniquely
identifiable by its own attributes. It has a primary key that uniquely identifies each entity
instance.
Example: A "Student" entity in a college database, with StudentID as its primary key, is
a strong entity because it doesn’t rely on other entities for identification.

Student College
study

1.15 Attributes:
 Attributes represent the properties or characteristics of an entity.
 For example, for a "Student" entity, attributes might be Name, Student ID, Date of Birth, etc.
 Each attribute is represented by an oval in an ER diagram, connected to its corresponding entity.
Types of attributes:
 Simple Attribute (or) Key Attribute : Cannot be divided further (e.g., Age, ID).
For example, student roll number can uniquely identify
a student from a set of students. Key attribute is
represented by oval same as other attributes however
the text of key attribute is underlined.

 Composite Attribute: Can be divided into

smaller sub-parts (e.g., Full Name can be
divided into First Name and Last Name).
For example, In student entity, the student
address is a composite attribute as an address is
composed of other attributes such as pin code,
state, country.
 Derived Attribute: Value can be derived from other attributes. The
derived attribute denoted by dotted lines. (e.g., Age can be derived from
Date of Birth).
For example – Person age is a derived attribute. The age of person is
derived from Date of Birth and Current Date.

 Multi-valued Attribute: An attribute that can hold multiple values is known as multivalued
attribute. It is represented with double ovals in an ER Diagram. For example – A person can have
more than one phone numbers so the phone number attribute is multivalued. (e.g., Phone
Numbers for a person).

1.16 Entity Sets: An entity set is a group of entities that share the same properties and are used to
organize data in a database.
For example, a customer entity might have attributes like name, address, and phone number. A customer
entity set would be a group of customers with those same attributes.
 Customer
name
Customer_id Customer phone
no

Customer

1.15. Relationship and Relationship set:

Relationship:
 Relationships describe how entities are related to one another.
 For example, a "Customer" places an "Order," or a "Student" enrolls in a "Course."
 Relationships are represented by diamonds in an ER diagram, with lines connecting them to the
related entities.

Relationship set:
In a Relational database, relationship sets are built
up by utilizing keys, such as primary and foreign
keys, to interface related records over distinctive
tables.

Weak Relationship: A weak, or non-

identifying, relationship exists
if the primary key of the related entity does
not contain a primary key component of
the parent entity.

In the above ER Diagram, ‘Payment’ is the weak entity, ‘Loan Payment’ is the non-identifying
relationship and ‘Payment Number’ is the partial key. Primary Key of the Loan along with the partial key
would be used to identify the records.

Strong Relationship: A strong relationship in an

Entity Relationship (ER) diagram is when the
primary key of a related entity contains the
primary key of the parent entity
Types of Relationships:
There are two types of relationships in dbms. They are
1. Degree of Relationship.
2. Cardinality Relationship.
1. Degree of Relationship:
In Database Management Systems (DBMS), the degree of a relationship refers to the number of entity
types (or entity sets) involved in a relationship. The degree of relationship in DBMS can be categorized
as a Unary, Binary, Ternary.
Unary Relationship (Degree 1):
 A unary relationship involves only one entity type that is related to itself.
 This is also known as a recursive relationship, where an entity participates in the relationship with
another entity of the same type.
 Example: In an organization, the
Employee entity might have a unary
relationship called manages, where one
employee manages another employee.
o Entity: Employee
o Relationship: Manages (between
an employee and another Employee Manages
employee)

Binary Relationship: A relationship among 2 entity sets. Example: A professor teaches a course
and a courseis taught by a professor.

Ternary Relationship: A relationship among 3 entity sets. Example: A professor teaches a course in so
and sosemester.

2. Cardinality: Cardinality defines the number of instances of one entity that can or must be associated
with instances of another entity in a relationship.
The main types of cardinality are:
 One-to-One (1:1): Each instance of one entity is related to exactly one instance of another entity.
For example, a student has only
one identification card and an identification
card is given to one person.
 One-to-Many (1:M): An instance of one entity can be related to multiple instances of
another entity. For example, a customer
can place many orders, but an order
cannot be placed by many customers.

 Many-to-One (M:1): Many instances of one entity can be related to one instance of another entity.
 For example, many students can involve only on one project.
For example, students have to opt for a single
course, but a course can have many students.
 Many-to-Many (M:M): Instances of one entity can be related to many instances of another entity,
and vice versa. For example, students can enroll in multiple courses, and courses can have multiple
students.
 For example, you can assign an employee to
many projects and a project can have many
employees.

1.16. Constraints: In Database Management Systems (DBMS), constraints are rules enforced on
the data in tables to ensure accuracy, integrity, and consistency. They define certain conditions that data
must meet when being inserted, updated, or deleted from a database. Constraints play a critical role in
maintaining data integrity across various operations in a database. Types of Constraints: There are various
types of constraints in DBMS. They are
1. Domain Integrity Constraints
2. Entity Integrity Constraints
3. Referential Integrity Constraints
4. Key Constraints
1. Domain Integrity Constraints: Domain constraints in DBMS are the set of rules which defines
what kind of data can be stored in an attribute. Domain Constraints help us to enter the data into
the table according to the particular data type. The domain constraints are
a) NOT NULL 2) CHECK.
a) NOT NULL: It is domain constraint that a column cannot have a NULL value.
Syntax: CREATE TABLE <table name> (Column1 data type NOT NULL,
Column2 data type NOT NULL,
...
Column n data type NOT NULL); Example: CREATE
TABLE customers ( ID number(6) NOT NULL,
NAME varchar2(20), NOT NULL,
AGE number(3) NOT NULL, ADDRESS
char(25),
SALARY number(10,2),
PRIMARY KEY (ID));
b) CHECK: Check is a domain constraint, it allows the data into the table only after checking the
condition.
Syntax: CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);
Example: CREATE TABLE student(Rollno number(6) NOT NULL, Name
varchar2(20) NOT NULL, Age number(3) CHECK (Age>=18));
2. Entity Integrity Constraint: Entity integrity ensures that every table has a primary key, and that the
primary key values are unique and non-null. This ensures that every entity (record) in a table can be
uniquely identified.
Example: A table of students must have a not-null, unique StudentID for each record.
create table student(StudentID number(5) NOT NULL, name varchar2(20), marks number(3),
grade char(1), primary key(StudentID));
3. Referential Integrity Constraint: Referential integrity (Foreign Key Constraints) is maintained
using foreign key constraints, which enforce a link between two tables. A foreign key in one table
refers to a primary key in another table. This constraint ensures that records in a table cannot
reference non-existent records in another table, preventing invalid data entries.
Example: Table-1 Department and Table-2 Employee
 CREATE TABLE Department (DeptID NUMBER(6) PRIMARY KEY, DeptName VARCHAR2(50) );

CREATE TABLE Employee (EmployeeID NUMBER(6) PRIMARY KEY, Name VARCHAR2(50), DeptID
NUMBER(2), FOREIGN KEY (DeptID) REFERENCES

Department(DeptID) );
4. Key Constraints: Key constraints in Database Management Systems (DBMS) ensure that data
within a table is uniquely identifiable and that the integrity of the data is maintained. Key constraints
are rules that are applied to keys, which are attributes (columns) used to identify rows (records) in a
table.
There are several types of key constraints in DBMS, and each has a specific role in maintaining data
integrity and preventing redundancy. The most common key constraints are Primary Key, Unique
Key, Foreign Key, and Composite Key.
a. Primary Key: A primary key is used in table that uniquely identifies each row/record. This
is also one type of Integrity Constraint. Primary keys must have distinct values. Null values
are not allowed in a primary key column. A table can
only have one primary key, which can be made up of one or more fields. It creates a
composite key when several fields are used as a primary key.
Syntax: Create table table_name (Column_name1 datatype NOT NULL, Column_name2 datatype,
..... Column_name n datatype, PRIMARY KEY (column_name1) ) .

Example: create table student(id number(6) not null, name varchar2(20), marks number(3),
grade varchar2(5), primary key(id));
b. Foreign key: A foreign key is used to define a column or group of columns in a relational
database table that provides a link between data in two tables. It acts as a cross-reference
between tables because it references the primary key of another table, thereby establishing a
link between them. It means, the foreign key constraint ensures that values in the foreign key
column(s) match values in the referenced primary key column(s).
Syntax:CREATE TABLE Table_Name ( col_name type(size) FOREIGN KEY(col_name)
REFERENCES table_name);

Example: Providing relation between Customer table and Order table.

CREATE TABLE customers (customerID number(6) PRIMARY KEY, FirstName
VARCHAR2(30), LastName VARCHAR2(30), Email VARCHAR2(50) UIQUE);
CREATE TABLE orders (orderID NUMBER(6) PRIMARY KEY, customerID
NUMBER(6), orderdate DATE, TotalAmount NUMBER(10, 2), FOREIGN KEY
(customerID REFERENCES customers(customerID));

customers table
CustomerID FirstName LastName Email

orders table

OrderID CustomerID orderdate TotalAmount

c. Unique Constraint:
 A unique constraint ensures that all values in a column or a set of columns are unique.
Unlike the primary key, a unique constraint allows NULL values.
Syntax
CREATE TABLE Table_name (
 Column_Name1 DataType NOT NULL UNIQUE,
Column_Name2 DataType NOT NULL, Column_Name3
DataType,
.......
Column_Name N DataType);
Example:
CREATE TABLE Student_DB (S_ID number NOT NULL UNIQUE, L_Name
varchar2(255) NOT NULL, F_Name varchar2(255), Age number);

1.17. Sub Class and Super Class:

Sub Class: In a database management system (DBMS), a subclass
is a type of entity that is a subgroup of another entity type.
Subclasses share common attributes or relationships that are
distinct from other subgroups.
For example, if subclass P inherits from superclass Q, and subclass
Q inherits from superclass S, then an instance of P is an instance
of Q and also (by transitivity) an instance of S.

Super Class: Super classes are components of data

inheritance where certain properties, attributes, or
characteristics of an entity are inherited or passed
down from a parent object to its child objects or
entities.

Car, Truck and Motorcycle are all subclasses of the

superclass Vehicle.

1.18. Inheritance: It refers to the ability of a subclass (or specialized entity) to inherit attributes and
relationships from a superclass (or general entity). This allows for more efficient and logical modeling of
hierarchical relationships within a database.
Superclass and Subclass:
 A superclass is a general entity that holds attributes and relationships common to multiple
entities.
 A subclass is a specialized entity that inherits all the attributes and relationships of the
superclass but may have additional attributes or relationships specific to itself.
Attributes Inheritance:
 A subclass inherits all the attributes of the superclass, meaning the subclass automatically
includes the attributes of the superclass without redefining them.
Relationships Inheritance:
  A subclass also inherits the relationships that the superclass participates in. For example, if the
superclass has a relationship with another entity, the subclass will also have the same relationship.
Types of Inheritance in DBMS: 1. Generalization and 2. Specialization.
1. Generalization: Generalization is a bottom-up approach in which two lower level entities
combine to form a higher-level entity. In generalization, the higher-level entity can also
combine with other lower level entity to make further higher-level entity.
For example, if both Car and Truck share common attributes like VehicleID, Make, and Model,
these attributes can be abstracted into a Vehicle superclass, from which both Car and Truck will
inherit.

Specialization: Specialization is the opposite of generalization. It is the process of creating a more specific
entity (subclass) from a more general one (superclass). The specialized subclass inherits the common attributes
and relationships of the superclass but can also have its own unique attributes. For example, the entity Person
can be specialized into Student and Teacher. In the diagram, the higher-level entity called Product, which
represents all products. Then, there are specialized entities for specific product categories, such as Electronics,
Clothing, and Books, with each having their own specialized attributes.