Unit 1

Introduction to Database Management

System
Introduction
• Managing Database.
• Data : Raw Facts
Unorganized
Unprocessed Information
Eg. Marks/ Results of complete class

• Information:
• Processing of Data
• Organized fashion
• Eg. Average Marks or Passed percentage
• Database is collection of Data or information that is organized in
such way that it can be easily accessible or managed and updated.

• Objectives:
Mass Storage-Large amount of data
Remove Duplicates
Multi-User- Access can be done by multiple users.
Protection: Security for data access.

• DBMS: It is collection of Interelated data and set of programs to

access those data
Purpose of Database System
• Data Redundancy and inconsistency: Duplication of data , all
copies may not be updated.
• Difficulty in accessing data: new programme needed for each task
• Data isolation: Data in different format.
• Integrity problem: Accuracy cannot be maintained.
• Atomicity of updates: Failures of data may result inconsistent.
• Concurrent access by multiple users: Multiple users can access
data.
• Security Problem: Restricted access
• Support for multiple views of data: Different view of data for
many users
Database System Applications
• Airlines
• Business
• E-commerce
• Universities
• Banking
• Sales:
• Fianance
View of Data
• Data Abstraction :(Different Levels of Abstraction)
• Primary goal of database: Store and Retrieve
• Convenient and efficient.
• Complex Data Structure
• Hiding the Complexity
• Several Levels of Abstraction:
• Three Levels:
Physical Level
Logical Level
View Level
Physical Level
• Lowest Level of abstraction
• Deals with how data are stored.
• Complex low level data structures.
• Database level.
• Storage level.
Logical Level
• Deals with what and relationship.
• Entire database with simple data structures.
• Physical Data Independence.
• DBA.
View Level
• Highest Level
• Users and Access.
• Interaction with the system.
• Applications programs.
• Several views and Security.
• E.g. ATM and Bank
Database Languages
• A database is a collection of organized information or data that is stored on a computer.
• Database Language:
• Database language refers to the specific types of commands or instructions used to
communicate with a database.
• It helps users or applications to create, manage, update, and retrieve data from a
database.
• Types of Database Languages in DBMS
• DDL
• DML
• DCL
• TCL
• DDL(Data Definition Language)
• The DDL stands for Data Definition Language, Which is used to define the database's internal structure and
Pattern of the Database.
• The DDL is used for creating tables, indexes, constraints, and schema in the Database.
• By using DDL statements we can able to create the architecture of the required database.
• Create
• Drop
• Alter
• Truncate
• Rename
• Comment
• DML:(Data Manipulation Language)
• The Data Manipulation Language is used to Manipulate the data in the database by using different
commands.
• Select
• Insert
• Update
• Delete
• Merge
• DCL (Data Control Language)
• The DCL stands for Data Control Language means these commands are used to retrieve the saved data from
the database.
• Grant
• Revoke
• TCL ( Transaction Control Language )
• The TCL full form is Transaction Control Language commands are used to run the changes made by the
DML commands one more thing is TCL can be grouped into a logical transaction.
• Commit
• Rollback
Database System Structure
• Structure of Database Management System is also referred to as Overall System Structure or Database
Architecture but it is different from the tier architecture of Database.
• Components of a Database System
• Query Processor, Storage Manager, and Disk Storage. These are explained as following below.
• 1. Query Processor:
• It interprets the requests (queries) received from end user via an application program into instructions. It also
executes the user request which is received from the DML compiler.
• Query Processor contains the following components –
• DML Compiler: It processes the DML statements into low level instruction (machine language), so that they can
be executed.
• DDL Interpreter: It processes the DDL statements into a set of table containing meta data (data about data).
• Embedded DML Pre-compiler: It processes DML statements embedded in an application program into
procedural calls.
• Query Optimizer: It executes the instruction generated by DML Compiler.
• 2. Storage Manager:
• Storage Manager is a program that provides an interface between the data stored in the database and the queries
received.
• It is also known as Database Control System.
• It maintains the consistency and integrity of the database by applying the constraints and executing the DCL
statements.
• It is responsible for updating, storing, deleting, and retrieving data in the database.
• It contains the following components –
• Authorization Manager: It ensures role-based access control, i.e,. checks whether the particular person is
privileged to perform the requested operation or not.
• Integrity Manager: It checks the integrity constraints when the database is modified.
• Transaction Manager: It controls concurrent access by performing the operations in a scheduled way that it
receives the transaction. Thus, it ensures that the database remains in the consistent state before and after the
execution of a transaction.
• File Manager: It manages the file space and the data structure used to represent information in the database.
• Buffer Manager: It is responsible for cache memory and the transfer of data between the secondary storage
and main memory.

• 3. Disk Storage:
• It contains the following components:
• Data Files: It stores the data.
• Data Dictionary: It contains the information about the structure of any database object. It is the repository of
information that governs the metadata.
• Indices: It provides faster retrieval of data item.
Data Models
• A Data Model in Database Management System (DBMS) is the concept of tools that are developed to
summarize the description of the database.
• Data Models provide us with a transparent picture of data which helps us in creating an actual database.
• It shows us from the design of the data to its proper implementation of data.

• Types of Data Models:

• Conceptual Data Model:
• The conceptual data model describes the database at a very high level and is useful to understand the needs or
requirements of the database.
• It is used in the requirement-gathering process i.e. before the Database Designers start making a particular database.
• One such popular model is the entity/relationship model (ER model).
• The E/R model specializes in entities, relationships, and even attributes that are used by database designers.
• Discussion can be made with non-technical person or users and stakeholders and their requirements can be
understood.
• Characteristics of a conceptual data model:
• Offers Organization-wide coverage of the business concepts.
• This type of Data Models are designed and developed for a business audience.
• The conceptual model is developed independently of hardware specifications like data storage capacity, location or
software specifications like DBMS vendor and technology. The focus is to represent data as a user will see it in the
“real world.”
• 2. Representational Data Model:
• This type of data model is used to represent only the logical part of the database and does not represent the physical
structure of the database.
• The representational data model allows us to focus primarily, on the design part of the database.
• A popular representational model is a Relational model.
• The relational Model consists of Relational Algebra and Relational Calculus.
• In the Relational Model, we basically use tables to represent our data and the relationships between them.
• It is a theoretical concept whose practical implementation is done in Physical Data Model.
• Characteristics of Representational Data Model:
• Represents the logical structure of the database.
• Relational models like Relational Algebra and Relational Calculus are commonly used.
• Uses tables to represent data and relationships.
• Provides a foundation for building the physical data model.
• 3. Physical Data Model:
• The physical Data Model is used to practically implement Relational Data Model.
• All data in a database is stored physically on a secondary storage device such as discs and tapes.
• This is stored in the form of files, records, and certain other data structures.
• It has all the information on the format in which the files are present and the structure of the databases, the presence
of external data structures, and their relation to each other.
• In order to come up with a good physical model, we have to work on the relational model in a better way.
• Structured Query Language (SQL) is used to practically implement Relational Algebra.
• This Data Model describes HOW the system will be implemented using a specific DBMS system.
• This model is typically created by DBA and developers.
• The purpose is actual implementation of the database.
• Characteristics of a physical data model:
• The physical data model describes data need for a single project or application though it maybe
integrated with other physical data models based on project scope.
• Data Model contains relationships between tables that which addresses cardinality and nullability
of the relationships.
• Developed for a specific version of a DBMS, location, data storage or technology to be used in the
project.
• Columns should have exact datatypes, lengths assigned and default values.
• Primary and Foreign keys, views, indexes, access profiles, and authorizations, etc. are defined
Database Design and ER Model:
• Database Design can be defined as a set of procedures or collection of tasks involving various steps taken to
implement a database.
• A good database design is important.
• It helps you get the right information when you need it.
• Following are some critical points to keep in mind to achieve a good database design:
• Data consistency and integrity must be maintained.
• Low Redundancy
• Faster searching through indices
• Security measures should be taken by enforcing various integrity constraints.
• Data should be stored in fragmented bits of information in the most atomic
format possible.
• Database Design Lifecycle:
• An Example of Logical Design
• The Entity Relationship Model is a model for identifying entities (like student, car or company) to be
represented in the database and representation of how those entities are related.
• The ER data model specifies enterprise schema that represents the overall logical structure of a database
graphically.
• Why Use ER Diagrams In DBMS?
• ER diagrams represent the E-R model in a database, making
them easy to convert into relations (tables).
• ER diagrams provide the purpose of real-world modeling of
objects which makes them intently useful.
• ER diagrams require no technical knowledge of the underlying
DBMS used.
• It gives a standard solution for visualizing the data logically.
Symbols Used in ER Model

• ER Model is used to model the logical view of the system from a data perspective which consists of
these symbols:
• Rectangles: Rectangles represent Entities in the ER Model.
• Ellipses: Ellipses represent Attributes in the ER Model.
• Diamond: Diamonds represent Relationships among Entities.
• Lines: Lines represent attributes to entities and entity sets with other relationship types.
• Double Ellipse: Double Ellipses represent Multi-Valued Attributes.
• Double Rectangle: Double Rectangle represents a Weak Entity.
Entity
• An Entity may be an object with a physical existence – a particular person, car, house, or
employee – or it may be an object with a conceptual existence – a company, a job, or a university
course.
• Entity Set:
• An Entity is an object of Entity Type and a set of all entities is called an entity set. For Example,
E1 is an entity having Entity Type Student and the set of all students is called Entity Set. In ER
diagram, Entity Type is represented as:
• Types of Entity:
1. Strong Entity:
• A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not depend on other
Entity in the Schema. It has a primary key, that helps in identifying it uniquely, and it is
represented by a rectangle. These are called Strong Entity Types.
• 2. Weak Entity:
• A weak entity type is represented by a Double Rectangle. The participation of weak entity types is
always total. The relationship between the weak entity type and its identifying strong entity type is
called identifying relationship and it is represented by a double diamond.
Attributes
• Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age, Address,
and Mobile_No are the attributes that define entity type Student. In ER diagram, the attribute is represented
by an oval.
Types of Attributes
• 1. Key Attribute
• The attribute which uniquely identifies each entity in the entity set is called the key attribute. For example,
Roll_No will be unique for each student. In ER diagram, the key attribute is represented by an oval with
underlying lines.
• 2. Composite Attribute
• An attribute composed of many other attributes is called a composite attribute.
• For example, the Address attribute of the student Entity type consists of Street, City, State, and
Country.
• In ER diagram, the composite attribute is represented by an oval comprising of ovals.
• 3. Multivalued Attribute:
• An attribute consisting of more than one value for a given entity. For example, Phone_No (can be
more than one for a given student). In ER diagram, a multivalued attribute is represented by a
double oval.
Relationships
Keys
• Keys are one of the basic requirements of a relational database model. It is widely used to identify
the tuples(rows) uniquely in the table. We also use keys to set up relations amongst various
columns and tables of a relational database.
• Need of Keys in DBMS:
• We require keys in a DBMS to ensure that data is organized, accurate, and easily accessible.
• Keys help to uniquely identify records in a table, which prevents duplication and ensures data
integrity.
• Keys also establish relationships between different tables, allowing for efficient querying and
management of data.
• Without keys, it would be difficult to retrieve or update specific records, and the database could
become inconsistent or unreliable.
• Different Types of Database Keys:
• Super Key
• Candidate Key
• Primary Key
• Alternate Key
• Foreign Key
• Super Key:
• The set of one or more attributes (columns) that can uniquely identify a tuple (record) is known as
Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc.
• A super key is a group of single or multiple keys that uniquely identifies rows
in a table. It supports NULL values in rows.
• Candidate Key:
• The minimal set of attributes that can uniquely identify a tuple is known as a candidate key. For
Example, STUD_NO in STUDENT relation.
• A candidate key is a minimal super key, meaning it can uniquely identify a record but
contains no extra attributes.
• It is a super key with no repeated data is called a candidate key.
• The minimal set of attributes that can uniquely identify a record.
• A candidate key must contain unique values, ensuring that no two rows have the same value
in the candidate key’s columns.
• Every table must have at least a single candidate key.
• A table can have multiple candidate keys but only one primary key.
Primary Key

Primary Key
• There can be more than one candidate key in relation out of which one can be chosen as the
primary key. For Example, STUD_NO, as well as STUD_PHONE, are candidate keys for
relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many
candidate keys).
• A primary key is a unique key, meaning it can uniquely identify each record (tuple) in a table.
• It must have unique values and cannot contain any duplicate values.
• A primary key cannot be NULL, as it needs to provide a valid, unique identifier for every record.
• A primary key does not have to consist of a single column. In some cases, a composite primary
key (made of multiple columns) can be used to uniquely identify records in a table.
• Databases typically store rows ordered in memory according to primary key for fast access of
records using primary key.
• Alternate Key:
• An alternate key is any candidate key in a table that is not chosen as the primary key. In other
words, all the keys that are not selected as the primary key are considered alternate keys.
• An alternate key is also referred to as a secondary key because it can uniquely identify records in
a table, just like the primary key.
• An alternate key can consist of one or more columns (fields) that can uniquely identify a record,
but it is not the primary key
• Eg:- SNAME, and ADDRESS is Alternate keys
• Foreign Key:
• A foreign key is an attribute in one table that refers to the primary key in another table. The
table that contains the foreign key is called the referencing table, and the table that is
referenced is called the referenced table.
• A foreign key in one table points to the primary key in another table, establishing a
relationship between them.
• It helps connect two or more tables, enabling you to create relationships between them. This
is essential for maintaining data integrity and preventing data redundancy.
• They act as a cross-reference between the tables.
• For example, DNO is a primary key in the DEPT table and a non-key in EMP
Extended-ER Diagram(EER)
• EER is a high-level data model that incorporates the extensions to the original ER model.
• Enhanced ERD are high level models that represent the requirements and complexities of
complex database
• In addition to ER model concepts EE-R includes −.
• Subclasses and Super classes.
• Specialization and Generalization.
• Category or union type.
• Aggregation.
• Subclasses and Super class
• Super that can be divided into further subtype.
• Sub classes class is an entity are the group of entities with some unique attributes. Sub class
inherits the properties and attributes from super class.
• Specialization and Generalization:
• Generalization is a process of generalizing an entity which contains generalized attributes or
properties of generalized entities.
• It is a Bottom up process i.e. consider we have 3 sub entities Car, Truck and Motorcycle. Now
these three entities can be generalized into one super class named as Vehicle.

• Specialization is a process of identifying subsets of an entity that share some different

characteristic.
• It is a top down approach in which one entity is broken down into low level entity.

• In above example Vehicle entity can be a Car, Truck or Motorcycle.

• Category or Union
• Relationship of one super or sub class with more than one super class.
• Aggregation:
• Represents relationship between a whole object and its component.
• Consider a ternary relationship Works_On between Employee, Branch and Manager.
• The best way to model this situation is to use aggregation, So, the relationship-set, Works_On
is a higher level entity-set.
• Such an entity-set is treated in the same manner as any other entity-set.
• We can create a binary relationship, Manager, between Works_On and Manager to represent
who manages what tasks.
Converting ER Diagrams into tables
• 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.
• There are some points for converting the ER diagram to the table:
• Entity type becomes a table.
• In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual
tables.
• 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.
• 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.
• 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.
• 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.
• 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.
• 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: