give detailed & most asked dbms intervier questions from following pdf : 
LEC-1: Introduction to DBMS 
1.
 2.
 3.
 What is Data?
 a.
 b.
 Data is a collection of raw, unorganized facts and details like text, observations, figures, symbols,
 and descriptions of things etc.
 In other words, data does not carry any specific purpose and has no significance by 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.
 Data can be recorded and doesn’t have any meaning unless processed.
 Types of Data
 a.
 Quantitative
 i.
 ii.
 Numerical form
 Weight, volume, cost of an item.
 b. Qualitative
 i.
 ii.
 Descriptive, but not numerical.
 Name, gender, hair color of a person.
 What is Information?
 a.
 b.
 c.
 d.
 e.
 Info. Is processed, organized, and structured data.
 It provides context of the data and enables decision making.
 Processed data that make sense to us.
 Information is extracted from the data, by analyzing and interpreting pieces of data.
 E.g., you have data of all the people living in your locality, its Data, when you analyze and interpret
 the data and come to some conclusion that:
 i.
 There are 100 senior citizens.
 ii.
 iii.
 The sex ratio is 1.1.
 Newborn babies are 100.
 These are information.
 4. Data vs Information
 a.
 b.
 c.
 d.
 e.
 f.
 g.
 5.
 Data is a collection of facts, while information puts those facts into context.
 While data is raw and unorganized, information is organized.
 Data points are individual and sometimes unrelated. Information maps out that data to provide a
 big-picture view of how it all fits together.
 Data, on its own, is meaningless. When it’s analyzed and interpreted, it becomes meaningful
 information.
 Data does not depend on information; however, information depends on data.
 Data typically comes in the form of graphs, numbers, figures, or statistics. Information is typically
 presented through words, language, thoughts, and ideas.
 Data isn’t sufficient for decision-making, but you can make decisions based on information.
 What is Database?
 a.
 Database is an electronic place/system where data is stored in a way that it can be easily accessed,
 managed, and updated.
 b.
 6.
 To make real use Data, we need Database management systems. (DBMS)
 What is DBMS?
 a.
 A database-management system (DBMS) is a collection of interrelated data and a set of
 programs to access those data. The collection of data, usually referred to as the database,
 contains information relevant to an enterprise. The primary goal of a DBMS is to provide a way to
 store and retrieve database information that is both convenient and efficient.
 b.
 A DBMS is the database itself, along with all the software and functionality. It is used to perform
 different operations, like addition, access, updating, and deletion of the data.
 CodeHelp
7. 
8. DBMS vs File Systems
 a.
 b.
 File-processing systems has major disadvantages.
 i.
 Data Redundancy and inconsistency
 ii.
 iii.
 iv.
 Difficulty in accessing data
 Data isolation
 Integrity problems
 v. Atomicity problems
 vi.
 Concurrent-access anomalies
 vii.
 Security problems
 Above 7 are also the Advantages of DBMS (answer to “Why to use DBMS?”)
 CodeHelp
LEC-2: DBMS Architecture 
1.
 2.
 View of Data (Three Schema Architecture)
 a.
 b.
 c.
 The major purpose of DBMS is to provide users with an abstract view of the data. That is, the
 system hides certain details of how the data is stored and maintained.
 To simplify user interaction with the system, abstraction is applied through several levels of
 abstraction.
 The main objective of three level architecture is to enable multiple users to access the same data
 with a personalized view while storing the underlying data only once
 d. Physical level / Internal level
 i.
 The lowest level of abstraction describes how the data are stored.
 ii.
 iii.
 iv.
 Low-level data structures used.
 It has Physical schema which describes physical storage structure of DB.
 Talks about: Storage allocation (N-ary tree etc), Data compression & encryption etc.
 v. Goal: We must define algorithms that allow efficient access to data.
 e.
 f.
 g. 
Logical level / Conceptual level:
 i.
 ii.
 iii.
 iv.
 The conceptual schema describes the design of a database at the conceptual level,
 describes what data are stored in DB, and what relationships exist among those data.
 User at logical level does not need to be aware about physical-level structures.
 DBA, who must decide what information to keep in the DB use the logical level of
 abstraction.
 Goal: ease to use.
 View level / External level:
 i.
 ii.
 iii.
 iv.
 Highest level of abstraction aims to simplify users’ interaction with the system by
 providing different view to different end-user.
 Each view schema describes the database part that a particular user group is interested
 and hides the remaining database from that user group.
 At the external level, a database contains several schemas that sometimes called as
 subschema. The subschema is used to describe the different view of the database.
 At views also provide a security mechanism to prevent users from accessing certain parts
 of DB.
 Instances and Schemas
 a.
 CodeHelp
 The collection of information stored in the DB at a particular moment is called an instance of DB.
b.
 c.
 d.
 e.
 f.
 g.
 3.
 The overall design of the DB is called the DB schema.
 Schema is structural description of data. Schema doesn’t change frequently. Data may change
 frequently.
 DB schema corresponds to the variable declarations (along with type) in a program.
 We have 3 types of Schemas: Physical, Logical, several view schemas called subschemas.
 Logical schema is most important in terms of its effect on application programs, as programmers
 construct apps by using logical schema.
 Physical data independence, physical schema change should not affect logical
 schema/application programs.
 Data Models:
 a.
 b.
 c.
 4.
 Provides a way to describe the design of a DB at logical level.
 Underlying the structure of the DB is the Data Model; a collection of conceptual tools for describing
 data, data relationships, data semantics & consistency constraints.
 E.g., ER model, Relational Model, object-oriented model, object-relational data model etc.
 Database Languages:
 a.
 b.
 c.
 d.
 e.
 5.
 Data definition language (DDL) to specify the database schema.
 Data manipulation language (DML) to express database queries and updates.
 Practically, both language features are present in a single DB language, e.g., SQL language.
 DDL
 DML
 i.
 i.
 ii.
 We specify consistency constraints, which must be checked, every time DB is updated.
 Data manipulation involves
 1.
 Retrieval of information stored in DB.
 2.
 3.
 4.
 Insertion of new information into DB.
 Deletion of information from the DB.
 Updating existing information stored in DB.
 Query language, a part of DML to specify statement requesting the retrieval of
 information.
 How is Database accessed from Application programs?
 a.
 b.
 c.
 Apps (written in host languages, C/C++, Java) interacts with DB.
 E.g., Banking system’s module generating payrolls access DB by executing DML statements from
 the host language.
 API is provided to send DML/DDL statements to DB and retrieve the results.
 i.
 Open Database Connectivity (ODBC), Microsoft “C”.
 ii.
 Java Database Connectivity (JDBC), Java.
 6. Database Administrator (DBA)
 a.
 A person who has central control of both the data and the programs that access those data.
 b.
 7.
 Functions of DBA
 i.
 Schema Definition
 ii.
 iii.
 iv.
 Storage structure and access methods.
 Schema and physical organization modifications.
 Authorization control.
 v. Routine maintenance
 1.
 Periodic backups.
 2.
 3.
 Security patches.
 Any upgrades.
 DBMS Application Architectures: Client machines, on which remote DB users work, and server machines
 on which DB system runs.
 a.
 T1 Architecture
 i.
 The client, server & DB all present on the same machine.
 CodeHelp
b.
 T2 Architecture
 i.
 App is partitioned into 2-components.
 ii.
 iii.
 c.
 Client machine, which invokes DB system functionality at server end through query
 language statements.
 API standards like ODBC & JDBC are used to interact between client and server.
 T3 Architecture
 i.
 ii.
 iii.
 iv.
 App is partitioned into 3 logical components.
 Client machine is just a frontend and doesn’t contain any direct DB calls.
 Client machine communicates with App server, and App server communicated with DB
 system to access data.
 Business logic, what action to take at that condition is in App server itself.
 v. T3 architecture are best for WWW Applications.
 vi.
 Advantages:
 1.
 Scalability due to distributed application servers.
 2.
 3.
 Data integrity, App server acts as a middle layer between client and DB, which
 minimize the chances of data corruption.
 Security, client can’t directly access DB, hence it is more secure.
 CodeHelp
LEC-3: Entity-Relationship Model 
1.
 Data Model: Collection of conceptual tools for describing data, data relationships, data semantics, and consistency
 constraints.
 2. ER Model
 1.
 2.
 3.
 It is a high level data model based on a perception of a real world that consists of a collection of basic objects, called
 entities and of relationships among these objects.
 Graphical representation of ER Model is ER diagram, which acts as a blueprint of DB.
 It has physical existence.
 Entity: An Entity is a “thing” or “object” in the real world that is distinguishable from all other objects.
 1.
 2.
 3.
 4.
 5.
 Each student in a college is an entity.
 Entity can be uniquely identified. (By a primary attribute, aka Primary Key)
 Strong Entity: Can be uniquely identified.
 Weak Entity: Can’t be uniquely identified., depends on some other strong entity.
 1.
 It doesn’t have sufficient attributes, to select a uniquely identifiable attribute.
 2.
 3.
 Loan -> Strong Entity, Payment -> Weak, as instalments are sequential number counter can be generated
 separate for each loan.
 Weak entity depends on strong entity for existence.
 4. Entity set
 1.
 2.
 3.
 5.
 It is a set of entities of the same type that share the same properties, or attributes.
 E.g., Student is an entity set.
 E.g., Customer of a bank
 Attributes
 1.
 An entity is represented by a set of attributes.
 2.
 3.
 4.
 5.
 Each entity has a value for each of its attributes.
 For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute.
 E.g., Student Entity has following attributes
 A. Student_ID
 B.
 Name
 C. Standard
 D. Course
 E. Batch
 F.
 Contact number
 G. Address
 Types of Attributes
 1.
 Simple
 1.
 2.
 Attributes which can’t be divided further.
 E.g., Customer’s account number in a bank, Student’s Roll number etc.
 2. Composite
 1.
 2.
 3.
 4.
 3.
 Can be divided into subparts (that is, other attributes).
 E.g., Name of a person, can be divided into first-name, middle-name, last-name.
 If user wants to refer to an entire attribute or to only a component of the attribute.
 Address can also be divided, street, city, state, PIN code.
 Single-valued
 1.
 2.
 Only one value attribute.
 e.g., Student ID, loan-number for a loan.
 4. Multi-valued
 1.
 2.
 3.
 5.
 Attribute having more than one value.
 e.g., phone-number, nominee-name on some insurance, dependent-name etc.
 Limit constraint may be applied, upper or lower limits.
 Derived
 1.
 CodeHelp
 Value of this type of attribute can be derived from the value of other related attributes.
e.g., Age, loan-age, membership-period etc.
 2.
 6. NULL Value
 1.
 2.
 3.
 An attribute takes a null value when an entity does not have a value for it.
 It may indicate “not applicable”, value doesn’t exist. e.g., person having no middle-name
 It may indicate “unknown”.
 1.
 Unknown can indicate missing entry, e.g., name value of a customer is NULL, means it is missing as name
 must have some value.
 2.
 Not known, salary attribute value of an employee is null, means it is not known yet.
 6. Relationships
 1.
 2.
 3.
 4.
 5.
 7.
 Association among two or more entities.
 e.g., Person has vehicle, Parent has Child, Customer borrow loan etc.
 Strong Relationship, between two independent entities.
 Weak Relationship, between weak entity and its owner/strong entity.
 1.
 e.g., Loan <instalment-payments> Payment.
 Degree of Relationship
 1.
 2.
 3.
 4.
 5.
 Number of entities participating in a relationship.
 Unary, Only one entity participates. e.g., Employee manages employee.
 Binary, two entities participates. e.g., Student takes Course.
 Ternary relationship, three entities participates. E.g, Employee works-on branch, employee works-on job.
 Binary are common.
 Relationships Constraints
 1.
 Mapping Cardinality / Cardinality Ratio
 1.
 2.
 3.
 4.
 5.
 2.
 Number of entities to which another entity can be associated via a relationship.
 One to one, Entity in A associates with at most one entity in B, where A & B are entity sets. And an entity
 of B is associated with at most one entity of A.
 1.
 E.g., Citizen has Aadhar Card.
 One to many, Entity in A associated with N entity in B. While entity in B is associated with at most one
 entity in A.
 1.
 e.g., Citizen has Vehicle.
 Many to one, Entity in A associated with at most one entity in B. While entity in B can be associated with
 N entity in A.
 1.
 e.g., Course taken by Professor.
 Many to many, Entity in A associated with N entity in B. While entity in B also associated with N entity in
 A.
 1.
 2.
 Customer buys product.
 Student attend course.
 Participation Constraints
 1.
 2.
 3.
 4.
 5.
 6.
 8. ER Notations
 Aka, Minimum cardinality constraint.
 Types, Partial & Total Participation.
 Partial Participation, not all entities are involved in the relationship instance.
 Total Participation, each entity must be involved in at least one relationship instance.
 e.g., Customer borrow loan, loan has total participation as it can’t exist without customer entity. And 
customer has partial participation.
 Weak entity has total participation constraint, but strong may not have total.
 CodeHelp
CodeHelp
LEC-4: Extended ER Features 
1.
 Basic ER Features studied in the LEC-3, can be used to model most DB features but when complexity increases, it is
 better to use some Extended ER features to model the DB Schema.
 2. Specialisation
 1.
 In ER model, we may require to subgroup an entity set into other entity sets that are distinct in some way with other
 entity sets.
 2. Specialisation is splitting up the entity set into further sub entity sets on the basis of their functionalities,
 specialities and features.
 3.
 It is a Top-Down approach.
 4. e.g., Person entity set can be divided into customer, student, employee. Person is superclass and other specialised
 entity sets are subclasses.
 1.
 We have “is-a” relationship between superclass and subclass.
 2. Depicted by triangle component.
 5.
 Why Specialisation?
 1.
 Certain attributes may only be applicable to a few entities of
 the parent entity set.
 2. DB designer can show the distinctive features of the sub entities.
 3.
 To group such entities we apply Specialisation, to overall refine the DB blueprint.
 3.
 Generalisation
 1.
 It is just a reverse of Specialisation.
 2. DB Designer, may encounter certain properties of two entities are overlapping. Designer may consider to make a
 new generalised entity set. That generalised entity set will be a super class.
 3.
 “is-a” relationship is present between subclass and super class.
 4. e.g., Car, Jeep and Bus all have some common attributes, to avoid data repetition for the common attributes. DB
 designer may consider to Generalise to a new entity set “Vehicle”.
 5.
 It is a Bottom-up approach.
 6. Why Generalisation?
 1.
 Makes DB more refined and simpler.
 2. Common attributes are not repeated.
 4. Attribute Inheritance
 1.
 Both Specialisation and Generalisation, has attribute inheritance.
 2.
 3.
 5.
 T
 he attributes of higher level entity sets are inherited by lower level entity sets.
 E.g., Customer & Employee inherit the attributes of Person.
 Participation Inheritance
 1.
 If a parent entity set participates in a relationship then its child entity sets will also participate in that relationship.
 6. Aggregation
 1.
 2.
 3.
 How to show relationships among relationships? - Aggregation is the technique.
 Abstraction is applied to treat relationships as higher-level entities. We can call it Abstract entity.
 Avoid redundancy by aggregating relationship as an entity set itself.
 CodeHelp
LEC-7: Relational Model 
1.
 2.
 3.
 4.
 5.
 6.
 7.
 8.
 9.
 Relational Model (RM) organises the data in the form of relations (tables).
 A relational DB consists of collection of tables, each of which is assigned a unique name.
 A row in a table represents a relationship among a set of values, and table is collection of such relationships.
 Tuple: A single row of the table representing a single data point / a unique record.
 Columns: represents the attributes of the relation. Each attribute, there is a permitted value, called domain of the
 attribute.
 Relation Schema: defines the design and structure of the relation, contains the name of the relation and all the
 columns/attributes.
 Common RM based DBMS systems, aka RDBMS: Oracle, IBM, MySQL, MS Access.
 Degree of table: number of attributes/columns in a given table/relation.
 Cardinality: Total no. of tuples in a given relation.
 10. Relational Key: Set of attributes which can uniquely identify an each tuple.
 11.
 Important properties of a Table in Relational Model
 1.
 T
 he name of relation is distinct among all other relation.
 2.
 3.
 4.
 5.
 6.
 T
 he values have to be atomic. Can’t be broken down further.
 T
 he name of each attribute/column must be unique.
 Each tuple must be unique in a table.
 T
 he sequence of row and column has no significance.
 Tables must follow integrity constraints - it helps to maintain data consistency across the tables.
 12. Relational Model Keys
 1.
 2.
 3.
 4.
 5.
 6.
 7.
 8.
 Super Key (SK): Any P&C of attributes present in a table which can uniquely identify each tuple.
 Candidate Key (CK): minimum subset of super keys, which can uniquely identify each tuple. It contains no
 redundant attribute.
 1.
 CK value shouldn’t be NULL.
 Primary Key (PK):
 1.
 Selected out of CK set, has the least no. of attributes.
 Alternate Key (AK)
 1.
 All CK except PK.
 Foreign Key (FK)
 1.
 It creates relation between two tables.
 2.
 3.
 4.
 A relation, say r1, may include among its attributes the PK of an other relation, say r2. This attribute is called FK
 from r1 referencing r2.
 T
 he relation r1 is aka Referencing (Child) relation of the FK dependency, and r2 is called Referenced (Parent)
 relation of the FK.
 FK helps to cross reference between two different relations.
 Composite Key: PK formed using at least 2 attributes.
 Compound Key: PK which is formed using 2 FK.
 Surrogate Key:
 1.
 Synthetic PK.
 2.
 3.
 Generated automatically by DB, usually an integer value.
 May be used as PK.
 13. Integrity Constraints
 1.
 2.
 3.
 CRUD Operations must be done with some integrity policy so that DB is always consistent.
 Introduced so that we do not accidentally corrupt the DB.
 Domain Constraints
 1.
 2.
 3.
 Restricts the value in the attribute of relation, specifies the Domain.
 Restrict the Data types of every attribute.
 E.g., We want to specify that the enrolment should happen for candidate birth year < 2002.
 4. Entity Constraints
 1.
 CodeHelp
 Every relation should have PK. PK != NULL.
5.
 Referential Constraints 
1.
 2.
 3.
 4.
 6.
 Specified between two relations & helps maintain consistency among tuples of two relations. 
It requires that the value appearing in specified attributes of any tuple in referencing relation also appear in the 
specified attributes of at least one tuple in the referenced relation.   
If FK in referencing table refers to PK of referenced table then every value of the FK in referencing table must be 
NULL or available in referenced table. 
FK must have the matching PK for its each value in the parent table or it must be NULL. 
Key Constraints: The six types of key constraints present in the Database management system are:- 
1.
 NOT NULL:  This constraint will restrict the user from not having a NULL value. It ensures that every element in 
the database has a value. 
2.
 3.
 4.
 5.
 6.
 UNIQUE: It helps us to ensure that all the values consisting in a column are different from each other. 
DEFAULT: it is used to set the default value to the column. The default value is added to the columns if no value 
is specified for them.  
CHECK: It is one of the integrity constraints in DBMS. It keeps the check that integrity of data is maintained 
before and after the completion of the CRUD. 
PRIMARY KEY: This is an attribute or set of attributes that can uniquely identify each entity in the entity set. The 
primary key must contain unique as well as not null values. 
FOREIGN KEY: Whenever there is some relationship between two entities, there must be some common 
attribute between them. This common attribute must be the primary key of an entity set and will become the 
foreign key of another entity set. This key will prevent every action which can result in loss of connection 
between tables.
 CodeHelp
LEC-8: Transform - ER Model to Relational Model 
1.
 2.
 3.
 Both ER-Model and Relational Model are abstract logical representation of real world enterprises. Because the two
 models implies the similar design principles, we can convert ER design into Relational design.
 Converting a DB representation from an ER diagram to a table format is the way we arrive at Relational DB-design from
 an ER diagram.
 ER diagram notations to relations:
 1.
 Strong Entity
 1.
 2.
 3.
 Becomes an individual table with entity name, attributes becomes columns of the relation.
 Entity’s Primary Key (PK) is used as Relation’s PK.
 FK are added to establish relationships with other relations.
 2. Weak Entity
 1.
 2.
 3.
 3.
 A table is formed with all the attributes of the entity.
 PK of its corresponding Strong Entity will be added as FK.
 PK of the relation will be a composite PK, {FK + Partial discriminator Key}.
 Single Values Attributes
 1.
 Represented as columns directly in the tables/relations.
 4. Composite Attributes
 1.
 Handled by creating a separate attribute itself in the original relation for each composite attribute.
 2.
 5.
 e.g., Address: {street-name, house-no}, is a composite attribute in customer relation, we add address-street
name & address-house-name as new columns in the attribute and ignore “address” as an attribute.
 Multivalued Attributes
 1.
 New tables (named as original attribute name) are created for each multivalued attribute.
 2.
 3.
 4.
 5.
 6.
 PK of the entity is used as column FK in the new table.
 Multivalued attribute’s similar name is added as a column to define multiple values.
 PK of the new table would be {FK + multivalued name}.
 e.g., For Strong entity Employee, dependent-name is a multivalued attribute.
 1.
 New table named dependent-name will be formed with columns emp-id, and dname.
 2.
 3.
 PK: {emp-id, name}
 FK: {emp-id}
 Derived Attributes: Not considered in the tables.
 7.
 Generalisation
 1.
 2.
 3.
 Method-1: Create a table for the higher level entity set. For each lower-level entity set, create a table that
 includes a column for each of the attributes of that entity set plus a column for each attribute of the primary key
 of the higher-level entity set.
 For e.g., Banking System generalisation of Account - saving & current.
 1.
 Table 1: account (account-number, balance)
 2.
 3.
 Table 2: savings-account (account-number, interest-rate, daily-withdrawal-limit)
 Table 3: current-account (account-number, overdraft-amount, per-transaction-charges)
 Method-2: An alternative representation is possible, if the generalisation is disjoint and complete—that is, if no
 entity is a member of two lower-level entity sets directly below a higher-level entity set, and if every entity in
 the higher level entity set is also a member of one of the lower-level entity sets. Here, do not create a table for
 the higher-level entity set. Instead, for each lower-level entity set, create a table that includes a column for each
 of the attributes of that entity set plus a column for each attribute of the higher-level entity sets.
 Tables would be:
 1.
 2.
 Table 1: savings-account (account-number, balance, interest-rate, daily-withdrawal-limit)
 Table 2: current-account (account-number, balance, overdraft-amount, per-transaction-charges)
 Drawbacks of Method-2: If the second method were used for an overlapping generalisation, some values such
 as balance would be stored twice unnecessarily. Similarly, if the generalisation were not complete—that is, if
 some accounts were neither savings nor current accounts—then such accounts could not be represented with
 the second method.
 CodeHelp
 8. Aggregation
1.
 Table of the relationship set is made. 
2.
 3.
 Attributes includes primary keys of entity set and aggregation set’s entities. 
Also, add descriptive attribute if any on the relationship.
 CodeHelp
LEC-9: SQL in 1-Video 
1. SQL: Structured Query Language, used to access and manipulate data.
 2. SQL used CRUD operations to communicate with DB.
 1. CREATE - execute INSERT statements to insert new tuple into the relation.
 2. READ - Read data already in the relations.
 3. UPDATE - Modify already inserted data in the relation.
 4. DELETE - Delete specific data point/tuple/row or multiple rows.
 3. SQL is not DB, is a query language.
 4. What is RDBMS? (Relational Database Management System)
 1. Software that enable us to implement designed relational model.
 2. e.g., MySQL, MS SQL, Oracle, IBM etc.
 3. Table/Relation is the simplest form of data storage object in R-DB.
 4. MySQL is open-source RDBMS, and it uses SQL for all CRUD operations
 5. MySQL used client-server model, where client is CLI or frontend that used services provided by MySQL server.
 6. Difference between SQL and MySQL
 1. SQL is Structured Query language used to perform CRUD operations in R-DB, while MySQL is a RDBMS used to
 store, manage and administrate DB (provided by itself) using SQL.
 SQL DATA TYPES (Ref: https://www.w3schools.com/sql/sql_datatypes.asp) 
1. In SQL DB, data is stored in the form of tables.
 2. Data can be of different types, like INT, CHAR etc.
 DATATYPE Description
 CHAR string(0-255), string with size = (0, 255], e.g., 
CHAR(251)
 VARCHAR string(0-255)
 TINYTEXT String(0-255)
 TEXT string(0-65535)
 BLOB string(0-65535)
 MEDIUMTEXT string(0-16777215)
 MEDIUMBLOB string(0-16777215)
 LONGTEXT string(0-4294967295)
 LONGBLOB string(0-4294967295)
 TINYINT integer(-128 to 127)
 SMALLINT integer(-32768 to 32767)
 MEDIUMINT integer(-8388608 to 8388607)
 INT integer(-2147483648 to 2147483647)
 BIGINT integer (-9223372036854775808 to 
9223372036854775807)
 FLOAT Decimal with precision to 23 digits
 DOUBLE Decimal with 24 to 53 digits
 DATATYPE
 CodeHelp
DATATYPE
 Description
 DECIMAL
 Double stored as string
 DATE
 YYYY-MM-DD
 DATETIME
 YYYY-MM-DD HH:MM:SS
 TIMESTAMP
 TIME
 ENUM
 SET
 BOOLEAN
 BIT
 YYYYMMDDHHMMSS
 HH:MM:SS
 One of the preset values
 One or many of the preset values
 0/1
 e.g., BIT(n), n upto 64, store values in bits.
 3.
 4.
 5.
 Size: TINY < SMALL < MEDIUM < INT < BIGINT. 
Variable length Data types e.g., VARCHAR, are better to use as they occupy space equal to the actual data size. 
Values can also be unsigned e.g., INT UNSIGNED. 
6. Types of SQL commands: 
1.
 2.
 3.
 4.
 5.
 DDL (data definition language): defining relation schema. 
1.
 CREATE: create table, DB, view. 
2.
 3.
 4.
 5.
 ALTER TABLE: modification in table structure. e.g, change column datatype or add/remove columns. 
DROP: delete table, DB, view. 
TRUNCATE: remove all the tuples from the table. 
RENAME: rename DB name, table name, column name etc. 
SELECT 
DRL/DQL (data retrieval language / data query language): retrieve data from the tables. 
1.
 DML (data modification language): use to perform modifications in the DB 
1.
 INSERT: insert data into a relation 
2.
 3.
 UPDATE: update relation data. 
DELETE: delete row(s) from the relation. 
DCL (Data Control language): grant or revoke authorities from user. 
1.
 GRANT: access privileges to the DB 
2.
 REVOKE: revoke user access privileges. 
TCL (Transaction control language): to manage transactions done in the DB 
1.
 START TRANSACTION: begin a transaction 
2.
 3.
 4.
 COMMIT: apply all the changes and end transaction 
ROLLBACK: discard changes and end transaction 
SAVEPOINT: checkout within the group of transactions in which to rollback. 
MANAGING DB (DDL) 
1.
 Creation of DB 
1.
 CREATE DATABASE IF NOT EXISTS db-name; 
2.
 3.
 4.
 5.
 USE db-name; //need to execute to choose on which DB CREATE TABLE etc commands will be executed. 
//make switching between DBs possible. 
DROP DATABASE IF EXISTS db-name; //dropping database. 
SHOW DATABASES; //list all the DBs in the server. 
SHOW TABLES; //list tables in the selected DB. 
CodeHelp
DATA RETRIEVAL LANGUAGE (DRL) 
1. Syntax: SELECT <set of column names> FROM <table_name>; 
2. Order of execution from RIGHT to LEFT. 
3. Q. Can we use SELECT keyword without using FROM clause? 
1. Yes, using DUAL Tables. 
2. Dual tables are dummy tables created by MySQL, help users to do certain obvious actions without referring to user 
defined tables. 
3. e.g., SELECT 55 + 11; 
SELECT now(); 
SELECT ucase(); etc. 
4. WHERE 
1. Reduce rows based on given conditions. 
2. E.g., SELECT * FROM customer WHERE age > 18; 
5. BETWEEN 
1. SELECT * FROM customer WHERE age between 0 AND 100; 
2. In the above e.g., 0 and 100 are inclusive. 
6. IN 
1. Reduces OR conditions; 
2. e.g., SELECT *  FROM officers WHERE officer_name IN ('Lakshay', ‘Maharana Pratap', ‘Deepika’); 
7. AND/OR/NOT 
1. AND: WHERE cond1 AND cond2 
2. OR: WHERE cond1 OR cond2 
3. NOT: WHERE col_name NOT IN (1,2,3,4); 
8. IS NULL 
1. e.g., SELECT * FROM customer WHERE prime_status is NULL; 
9. Pattern Searching / Wildcard (‘%’, ‘_’) 
1. ‘%’, any number of character from 0 to n. Similar to ‘*’ asterisk in regex. 
2. ‘_’, only one character. 
3. SELECT * FROM customer WHERE name LIKE ‘%p_’; 
10. ORDER BY 
1. Sorting the data retrieved using WHERE clause. 
2. ORDER BY <column-name> DESC; 
3. DESC = Descending and ASC = Ascending 
4. e.g., SELECT * FROM customer ORDER BY name DESC; 
11. GROUP BY 
1. GROUP BY Clause is used to collect data from multiple records and group the result by one or more column. It is 
generally used in a SELECT statement. 
2. Groups into category based on column given. 
3. SELECT c1, c2, c3 FROM sample_table WHERE cond GROUP BY c1, c2, c3. 
4. All the column names mentioned after SELECT statement shall be repeated in GROUP BY, in order to successfully 
execute the query. 
5. Used with aggregation functions to perform various actions. 
1. COUNT() 
2. SUM() 
3. AVG() 
4. MIN() 
5. MAX() 
12. DISTINCT 
1. Find distinct values in the table. 
2. SELECT DISTINCT(col_name) FROM table_name; 
3. GROUP BY can also be used for the same 
1. “Select col_name from table GROUP BY col_name;” same output as above DISTINCT query. 
CodeHelp
2.
 SQL is smart enough to realise that if you are using GROUP BY and not using any aggregation function, then
 you mean “DISTINCT”.
 13. GROUP BY HAVING
 1.
 2.
 3.
 4.
 Out of the categories made by GROUP BY, we would like to know only particular thing (cond).
 Similar to WHERE.
 Select COUNT(cust_id), country from customer GROUP BY country HAVING COUNT(cust_id) > 50;
 WHERE vs HAVING
 1.
 Both have same function of filtering the row base on certain conditions.
 2.
 3.
 4.
 5.
 6.
 WHERE clause is used to filter the rows from the table based on specified condition
 HAVING clause is used to filter the rows from the groups based on the specified condition.
 HAVING is used after GROUP BY while WHERE is used before GROUP BY clause.
 If you are using HAVING, GROUP BY is necessary.
 WHERE can be used with SELECT, UPDATE & DELETE keywords while GROUP BY used with SELECT.
 CONSTRAINTS (DDL) 
1.
 Primary Key
 1.
 PK is not null, unique and only one per table.
 2.
 Foreign Key
 1.
 2.
 3.
 FK refers to PK of other table.
 Each relation can having any number of FK.
 CREATE TABLE ORDER (
 id INT PRIMARY KEY,
 delivery_date DATE,
 order_placed_date DATE,
 cust_id INT,
 FOREIGN KEY (cust_id) REFERENCES customer(id)
 );
 3. UNIQUE
 1.
 Unique, can be null, table can have multiple unique attributes.
 2.
 CREATE TABLE customer (
 …
 email VARCHAR(1024) UNIQUE,
 …
 );
 4. CHECK
 1.
 CREATE TABLE customer (
 …
 CONSTRAINT age_check CHECK (age > 12),
 …
 );
 2.
 CodeHelp
 “age_check”, can also avoid this, MySQL generates name of constraint automatically.
DEFAULT
 5.
 6.
 7.
 1.
 2.
 Set default value of the column.
 CREATE TABLE account (
 …
 saving-rate DOUBLE NOT NULL DEFAULT 4.25,
 …
 );
 An attribute can be PK and FK both in a table.
 ALTER OPERATIONS
 1.
 Changes schema
 2. ADD
 1.
 Add new column.
 2.
 3.
 3.
 ALTER TABLE table_name ADD new_col_name datatype ADD new_col_name_2 datatype;
 e.g., ALTER TABLE customer ADD age INT NOT NULL;
 MODIFY
 1.
 Change datatype of an attribute.
 2.
 3.
 ALTER TABLE table-name MODIFY col-name col-datatype;
 E.g., VARCHAR TO CHAR
 ALTER TABLE customer MODIFY name CHAR(1024);
 4. CHANGE COLUMN
 1.
 Rename column name.
 2.
 3.
 5.
 ALTER TABLE table-name CHANGE COLUMN old-col-name new-col-name new-col-datatype;
 e.g., ALTER TABLE customer CHANGE COLUMN name customer-name VARCHAR(1024);
 DROP COLUMN
 1.
 Drop a column completely.
 2.
 3.
 ALTER TABLE table-name DROP COLUMN col-name;
 e.g., ALTER TABLE customer DROP COLUMN middle-name;
 6. RENAME
 1.
 Rename table name itself.
 2.
 3.
 INSERT
 ALTER TABLE table-name RENAME TO new-table-name;
 e.g., ALTER TABLE customer RENAME TO customer-details;
 DATA MANIPULATION LANGUAGE (DML) 
1.
 1.
 INSERT INTO table-name(col1, col2, col3) VALUES (v1, v2, v3), (val1, val2, val3);
 2. UPDATE
 1.
 2.
 3.
 3.
 UPDATE table-name SET col1 = 1, col2 = ‘abc’ WHERE id = 1;
 Update multiple rows e.g.,
 1.
 UPDATE student SET standard = standard + 1;
 ON UPDATE CASCADE
 1.
 DELETE
 1.
 Can be added to the table while creating constraints. Suppose there is a situation where we have two tables
 such that primary key of one table is the foreign key for another table. if we update the primary key of the first
 table then using the ON UPDATE CASCADE foreign key of the second table automatically get updated.
 DELETE FROM table-name WHERE id = 1;
 2.
 3.
 DELETE FROM table-name; //all rows will be deleted.
 DELETE CASCADE - (to overcome DELETE constraint of Referential constraints)
 1.
 What would happen to child entry if parent table’s entry is deleted?
 2.
 CREATE TABLE ORDER (
 order_id int PRIMARY KEY,
 delivery_date DATE,
 cust_id INT,
 CodeHelp
FOREIGN KEY(cust_id) REFERENCES customer(id) ON DELETE CASCADE 
); 
3. ON DELETE NULL - (can FK have null values?)
 1. CREATE TABLE ORDER (
 order_id int PRIMARY KEY,
 delivery_date DATE,
 cust_id INT,
 FOREIGN KEY(cust_id) REFERENCES customer(id) ON DELETE SET NULL
 );
 4. REPLACE
 1. Primarily used for already present tuple in a table.
 2. As UPDATE, using REPLACE with the help of WHERE clause in PK, then that row will be replaced.
 3. As INSERT, if there is no duplicate data new tuple will be inserted.
 4. REPLACE INTO student (id, class) VALUES(4, 3);
 5. REPLACE INTO table SET col1 = val1, col2 = val2;
 JOINING TABLES 
1. All RDBMS are relational in nature, we refer to other tables to get meaningful outcomes.
 2. FK are used to do reference to other table.
 3. INNER JOIN
 1. Returns a resultant table that has matching values from both the tables or all the tables.
 2. SELECT column-list FROM table1 INNER JOIN table2 ON condition1
 INNER JOIN table3 ON condition2
 …;
 3. Alias in MySQL (AS)
 1. Aliases in MySQL is used to give a temporary name to a table or a column in a table for the purpose of
 a particular query. It works as a nickname for expressing the tables or column names. It makes the query short
 and neat.
 2. SELECT col_name AS alias_name FROM table_name;
 3. SELECT col_name1, col_name2,... FROM table_name AS alias_name;
 4. OUTER JOIN
 1. LEFT JOIN
 1. This returns a resulting table that all the data from left table and the matched data from the right table.
 2. SELECT columns FROM table LEFT JOIN table2 ON Join_Condition;
 2. RIGHT JOIN
 1. This returns a resulting table that all the data from right table and the matched data from the left table.
 2. SELECT columns FROM table RIGHT JOIN table2 ON join_cond;
 3. FULL JOIN
 1. This returns a resulting table that contains all data when there is a match on left or right table data.
 2. Emulated in MySQL using LEFT and RIGHT JOIN.
 3. LEFT JOIN UNION RIGHT JOIN.
 4. SELECT columns FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.id = t2.id
 UNION
 SELECT columns FROM table1 as t1 RIGHT JOIN table2 as t2 ON t1.id = t2.id;
 5. UNION ALL, can also be used this will duplicate values as well while UNION gives unique values.
 5. CROSS JOIN
 1. This returns all the cartesian products of the data present in both tables. Hence, all possible variations
 are reflected in the output.
 2. Used rarely in practical purpose.
 3. Table-1 has 10 rows and table-2 has 5, then resultant would have 50 rows.
 4. SELECT column-lists FROM table1 CROSS JOIN table2;
 6. SELF JOIN
 CodeHelp
1.
 2.
 3.
 4.
 7.
 It is used to get the output from a particular table when the same table is joined to itself. 
Used very less. 
Emulated using INNER JOIN. 
SELECT columns FROM table as t1 INNER JOIN table as t2 ON t1.id = t2.id; 
Join without using join keywords. 
1.
 2.
 SELECT * FROM table1,  table2 WHERE condition; 
e.g., SELECT artist_name, album_name, year_recordedFROM artist, albumWHERE artist.id = album.artist_id; 
SET OPERATIONS 
1.
 2.
 Used to combine multiple select statements. 
Always gives distinct rows. 
JOIN
 Combines multiple tables based on matching 
condition.
 SET Operations
 Combination is resulting set from two or more 
SELECT statements.
 Column wise combination.
 Row wise combination.
 Data types of two tables can be different.
 Can generate both distinct or duplicate rows.
 Datatypes of corresponding columns from each 
table should be the same.
 Generate distinct rows.
 The number of column(s) selected may or may not 
be the same from each table.
 The number of column(s) selected must be the 
same from each table.
 Combines results horizontally.
 3.
 UNION 
1.
 2.
 3.
 Combines two or more SELECT statements. 
SELECT * FROM table1 
UNION 
SELECT * FROM table2; 
Combines results vertically.
 Number of column, order of column must be same for table1 and table2. 
4. INTERSECT 
1.
 2.
 3.
 4.
 5.
 Returns common values of the tables. 
Emulated. 
SELECT DISTINCT column-list FROM table-1 INNER JOIN table-2 USING(join_cond); 
SELECT DISTINCT * FROM table1 INNER JOIN table2 ON USING(id); 
MINUS 
1.
 2.
 3.
 4.
 T
 his operator returns the distinct row from the first table that does not occur in the second table. 
Emulated. 
SELECT column_list FROM table1 LEFT JOIN table2 ON condition WHERE table2.column_name IS NULL; 
e.g., SELECT id FROM table-1 LEFT JOIN table-2 USING(id) WHERE table-2.id IS NULL; 
SUB QUERIES 
1.
 2.
 3.
 4.
 Outer query depends on inner query. 
Alternative to joins. 
Nested queries. 
SELECT column_list (s) FROM  table_name  WHERE  column_name OPERATOR 
(SELECT column_list (s)  FROM table_name [WHERE]); 
5.
 6.
 e.g., SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table1); 
Sub queries exist mainly in 3 clauses 
1.
 Inside a WHERE clause. 
CodeHelp
2.
 3.
 7.
 Inside a FROM clause. 
Inside a SELECT clause. 
Subquery using FROM clause 
1.
 SELECT MAX(rating) FROM (SELECT * FROM movie WHERE country = ‘India’) as temp; 
8. Subquery using SELECT 
1.
 9.
 SELECT (SELECT column_list(s) FROM T_name WHERE condition), columnList(s) FROM T2_name WHERE 
condition; 
Derived Subquery 
1.
 SELECT columnLists(s) FROM (SELECT columnLists(s) FROM table_name WHERE [condition]) as new_table_name; 
10. Co-related sub-queries 
1.
 With a normal nested subquery, the inner SELECT query 
runs first and executes once, returning values to be used by 
the main query. A correlated subquery, however, executes 
once for each candidate row considered by the outer query. 
In other words, the inner query is driven by the outer query. 
JOIN VS SUB-QUERIES 
JOINS
 Faster
 SUBQUERIES
 Slower
 Joins maximise calculation burden on DBMS
 Complex, difficult to understand and implement
 Keeps responsibility of calculation on user.
 Comparatively easy to understand and implement.
 Choosing optimal join for optimal use case is 
difficult
 MySQL VIEWS 
1.
 Easy.
 A view is a database object that has no values. Its contents are based on the base table. It contains rows and columns 
similar to the real table. 
2.
 3.
 4.
 5.
 6.
 7.
 In MySQL, the View is a virtual table created by a query by joining one or more tables. It is operated similarly to the base 
table but does not contain any data of its own. 
T
 he View and table have one main difference that the views are definitions built on top of other tables (or views). If any 
changes occur in the underlying table, the same changes reflected in the View also. 
CREATE VIEW view_name AS SELECT columns FROM tables [WHERE conditions]; 
ALTER VIEW view_name AS SELECT columns FROM table WHERE conditions; 
DROP VIEW IF EXISTS view_name; 
CREATE VIEW Trainer AS SELECT c.course_name, c.trainer, t.email FROM courses c, contact t WHERE c.id = t.id; (View 
using Join clause). 
NOTE: We can also import/export table schema from files (.csv or json).
 CodeHelp
LEC-11: Normalisation 
1.
 Normalisation is a step towards DB optimisation.
 2. Functional Dependency (FD)
 1.
 It's a relationship between the primary key attribute (usually) of the relation to that of the other attribute of the
 relation.
 2. X -> Y, the left side of FD is known as a Determinant, the right side of the production is known as a Dependent.
 3.
 Types of FD
 1.
 Trivial FD
 1.
 A → B has trivial functional dependency if B is a subset of A. A->A, B->B are also Trivial FD.
 2. Non-trivial FD
 1.
 A → B has a non-trivial functional dependency if B is not a subset of A. [A intersection B is NULL].
 4. Rules of FD (Armstrong’s axioms)
 1.
 Reflexive
 1.
 If ‘A’ is a set of attributes and ‘B’ is a subset of ‘A’. Then, A→ B holds.
 2. If A ⊇ B then A → B.
 2. Augmentation
 1.
 If B can be determined from A, then adding an attribute to this functional dependency won’t change
 anything.
 2. If A→ B holds, then AX→ BX holds too. ‘X’ being a set of attributes.
 3.
 3.
 Transitivity
 1.
 If A determines B and B determines C, we can say that A determines C.
 2. if A→ B and B→ C then A→ C.
 Why Normalisation?
 1.
 To avoid redundancy in the DB, not to store redundant data.
 4. What happen if we have redundant data?
 1.
 Insertion, deletion and updation anomalies arises.
 5.
 Anomalies
 1.
 Anomalies means abnormalities, there are three types of anomalies introduced by data redundancy.
 2. Insertion anomaly
 1.
 When certain data (attribute) can not be inserted into the DB without the presence of other data.
 3.
 Deletion anomaly
 1.
 T
 he delete anomaly refers to the situation where the deletion of data results in the unintended loss of some
 other important data.
 4. Updation anomaly (or modification anomaly)
 1.
 T
 he update anomaly is when an update of a single data value requires multiple rows of data to be updated.
 2. Due to updation to many places, may be Data inconsistency arises, if one forgets to update the data at all the
 intended places.
 5.
 Due to these anomalies, DB size increases and DB performance become very slow.
 6. To rectify these anomalies and the effect of these of DB, we use Database optimisation technique called
 NORMALISATION.
 6. What is Normalisation?
 1.
 Normalisation is used to minimise the redundancy from a relations. It is also used to eliminate undesirable
 characteristics like Insertion, Update, and Deletion Anomalies.
 2. Normalisation divides the composite attributes into individual attributes OR larger table into smaller and links them
 using relationships.
 3.
 7.
 T
 he normal form is used to reduce redundancy from the database table.
 Types of Normal forms
 1.
 1NF
 1.
 Every relation cell must have atomic value.
 2. Relation must not have multi-valued attributes.
 CodeHelp
Relation must be in 1NF. 
2. 2NF 
1.
 2. There should not be any partial dependency. 
1.
 All non-prime attributes must be fully dependent on PK. 
2. Non prime attribute can not depend on the part of the PK. 
3.
 3NF 
1.
 Relation must be in 2NF. 
2. No transitivity dependency exists. 
1.
 Non-prime attribute should not find a non-prime attribute. 
4. BCNF (Boyce-Codd normal form) 
1.
 Relation must be in 3NF. 
2. FD: A -> B, A must be a super key. 
1.
 We must not derive prime attribute from any prime or non-prime attribute. 
8. Advantages of Normalisation 
1.
 Normalisation helps to minimise data redundancy. 
2. Greater overall database organisation. 
3.
 Data consistency is maintained in DB.
 CodeHelp
LEC-12: Transaction 
1.
 Transaction
 1.
 3.
 A unit of work done against the DB in a logical sequence.
 2. Sequence is very important in transaction.
 It is a logical unit of work that contains one or more SQL statements. The result of all these statements in a
 transaction either gets completed successfully (all the changes made to the database are permanent) or if at any
 point any failure happens it gets rollbacked (all the changes being done are undone.)
 2. ACID Properties
 1.
 To ensure integrity of the data, we require that the DB system maintain the following properties of the transaction.
 2. Atomicity
 1.
 Either all operations of transaction are reflected properly in the DB, or none are.
 3.
 Consistency
 1.
 Integrity constraints must be maintained before and after transaction.
 2. DB must be consistent after transaction happens.
 4. Isolation
 1.
 Even though multiple transactions may execute concurrently, the system guarantees that, for every pair of
 transactions Ti and Tj, it appears to Ti that either Tj finished execution before Ti started, or Tj started execution
 after Ti finished. Thus, each transaction is unaware of other transactions executing concurrently in the system.
 2. Multiple transactions can happen in the system in isolation, without interfering each other.
 5.
 3.
 Durability
 1.
 After transaction completes successfully, the changes it has made to the database persist, even if there are
 system failures.
 Transaction states
 1.
 Active state
 1.
 T
 he very first state of the life cycle of the transaction, all the read and write operations are being
 performed. If they execute without any error the T comes to Partially committed state. Although if any
 error occurs then it leads to a Failed state.
 2. Partially committed state
 1.
 After transaction is executed the changes are saved in the buffer in the main memory. If the changes made
 are permanent on the DB then the state will transfer to the committed state and if there is any failure, the T
 will go to Failed state.
 3.
 CodeHelp
 Committed state
1.
 When updates are made permanent on the DB. Then the T is said to be in the committed state. Rollback 
can’t be done from the committed states. New consistent state is achieved at this stage. 
4. Failed state 
1.
 5.
 When T is being executed and some failure occurs. Due to this it is impossible to continue the execution of 
the T. 
Aborted state 
1.
 When T reaches the failed state, all the changes made in the buffer are reversed. After that the T rollback 
completely. T reaches abort state after rollback. DB’s state prior to the T is achieved. 
6. Terminated state 
1.
 A transaction is said to have terminated if has either committed or aborted.
 CodeHelp
LEC-13: How to implement Atomicity and Durability in Transactions 
1.
 Recovery Mechanism Component of DBMS supports atomicity and durability. 
2. Shadow-copy scheme 
1.
 2.
 3.
 4.
 5.
 6.
 7.
 Based on making copies of DB (aka, shadow copies).
 Assumption only one Transaction (T) is active at a time.
 A pointer called db-pointer is maintained on the disk; which at any instant points to current copy of DB.
 T, that wants to update DB first creates a complete copy of DB.
 All further updates are done on new DB copy leaving the original copy (shadow copy) untouched.
 If at any point the T has to be aborted the system deletes the new copy. And the old copy is not affected.
 If T success, it is committed as,
 1.
 OS makes sure all the pages of the new copy of DB written on the disk. 
2.
 3.
 4.
 5.
 DB system updates the db-pointer to point to the new copy of DB.
 New copy is now the current copy of DB.
 T
 he old copy is deleted.
 T
 he T is said to have been COMMITTED at the point where the updated db-pointer is written to disk. 
8. Atomicity 
1.
 2.
 3.
 9.
 If T fails at any time before db-pointer is updated, the old content of DB are not affected.
 T abort can be done by just deleting the new copy of DB.
 Hence, either all updates are reflected or none.
 Durability 
1.
 2.
 3.
 4.
 Suppose, system fails are any time before the updated db-pointer is written to disk. 
When the system restarts, it will read db-pointer & will thus, see the original content of DB and none of the effects of T will 
be visible. 
T is assumed to be successful only when db-pointer is updated.
 If system fails after db-pointer has been updated. Before that all the pages of the new copy were written to disk. Hence, 
when system restarts, it will read new DB copy. 
10. The implementation is dependent on write to the db-pointer being atomic. Luckily, disk system provide atomic updates to entire 
block or at least a disk sector. So, we make sure db-pointer lies entirely in a single sector. By storing db-pointer at the beginning 
of a block. 
11.
 3.
 Inefficient, as entire DB is copied for every Transaction.
 Log-based recovery methods 
1.
 T
 he log is a sequence of records. Log of each transaction is maintained in some stable storage so that if any failure occurs, then 
it can be recovered from there. 
2.
 3.
 4.
 5.
 If any operation is performed on the database, then it will be recorded in the log.
 But the process of storing the logs should be done before the actual transaction is applied in the database.
 Stable storage is a classification of computer data storage technology that guarantees atomicity for any given write operation 
and allows software to be written that is robust against some hardware and power failures. 
Deferred DB Modifications 
1.
 Ensuring atomicity by recording all the DB modifications in the log but deferring the execution of all the write operations 
until the final action of the T has been executed. 
2.
 3.
 4.
 5.
 Log information is used to execute deferred writes when T is completed.
 If system crashed before the T completes, or if T is aborted, the information in the logs are ignored.
 If T completes, the records associated to it in the log file are used in executing the deferred writes.
 If failure occur while this updating is taking place, we preform redo. 
6. Immediate DB Modifications 
1.
 DB modifications to be output to the DB while the T is still in active state.
 2.
 3.
 4.
 5.
 DB modifications written by active T are called uncommitted modifications.
 In the event of crash or T failure, system uses old value field of the log records to restore modified values.
 Update takes place only after log records in a stable storage.
 Failure handling
 1.
 System failure before T completes, or if T aborted, then old value field is used to undo the T.
 2.
 If T completes and system crashes, then new value field is used to redo T having commit logs in the logs.
 CodeHelp
LEC-14: Indexing in DBMS 
1.
 2.
 3.
 4.
 5.
 6.
 7.
 8.
 Indexing is used to optimise the performance of a database by minimising the number of disk accesses required when a query is 
processed. 
T
 he index is a type of data structure. It is used to locate and access the data in a database table quickly. 
Speeds up operation with read operations like SELECT queries, WHERE clause etc. 
Search Key: Contains copy of primary key or candidate key 
of the table or something else. 
Data Reference: Pointer holding the address of disk block 
where the value of the corresponding key is stored. 
Indexing is optional, but increases access speed. It is not the 
primary mean to access the tuple, it is the secondary mean. 
Index file is always sorted. 
Indexing Methods 
1.
 Primary Index (Clustering Index) 
1.
 A file may have several indices, on different search keys. If the data file containing the records is sequentially ordered, a 
Primary index is an index whose search key also defines the sequential order of the file. 
2.
 3.
 NOTE: The term primary index is sometimes used to mean an index on a primary key. However, such usage is 
nonstandard and should be avoided. 
All files are ordered sequentially on some search key. It could be Primary Key or non-primary key. 
4. Dense And Sparse Indices 
1.
 Dense Index 
1.
 2.
 3.
 T
 he dense index contains an index record for every search key value in the data file. 
T
 he index record contains the search-key value and a pointer to the first data record with that search-key value. 
T
 he rest of the records with the same search-key value would be stored sequentially after the first record. 
It needs more space to store index record itself. The index records have the search key and a pointer to the actual 
record on the disk. 
2. Sparse Index 
1.
 2.
 5.
 An index record appears for only some of the search-key values. 
Sparse Index helps you to resolve the issues of dense Indexing in DBMS. In this method of indexing technique, a 
range of index columns stores the same data block address, and when data needs to be retrieved, the block 
address will be fetched. 
Primary Indexing can be based on Data file is sorted w.r.t Primary Key attribute or non-key attributes. 
6. Based on Key attribute 
1.
 2.
 3.
 7.
 Data file is sorted w.r.t primary key attribute. 
PK will be used as search-key in Index. 
Sparse Index will be formed i.e., no. of entries in the index file = no. of blocks in datafile. 
Based on Non-Key attribute 
1.
 Data file is sorted w.r.t non-key attribute. 
2.
 3.
 4.
 No. Of entries in the index  = unique non-key attribute value in the data file. 
T
 his is dense index as, all the unique values have an entry in the 
index file. 
E.g., Let’s assume that a company recruited many employees in 
various departments. In this case, clustering indexing in DBMS 
should be created for all employees who belong to the same 
dept. 
8. Multi-level Index 
1.
 2.
 Index with two or more levels. 
If the single level index become enough large that the binary 
search it self would take much time, we can break down 
indexing into multiple levels. 
2. Secondary Index (Non-Clustering Index) 
1.
 2.
 3.
 4.
 5.
 Datafile is unsorted. Hence, Primary Indexing is not possible. 
Can be done on key or non-key attribute. 
Called secondary indexing because normally one indexing is already 
applied. 
No. Of entries in the index file = no. of records in the data file. 
CodeHelp
 It's an example of Dense index. 
9.
 Advantages of Indexing 
1.
 2.
 Faster access and retrieval of data.
 IO is less.
 10. Limitations of Indexing 
1.
 2.
 Additional space to store index table
 Indexing Decrease performance in INSERT, DELETE, and UPDATE query.
 CodeHelp
LEC-15: NoSQL 
1.
 NoSQL databases (aka "not only SQL") are non-tabular databases and store data differently than relational tables. NoSQL databases 
come in a variety of types based on their data model. The main types are document, key-value, wide-column, and graph. They 
provide flexible schemas and scale easily with large amounts of data and high user loads.
 1.
 2.
 3.
 4.
 5.
 T
 hey are schema free.
 Data structures used are not tabular, they are more flexible, has the ability to adjust dynamically.
 Can handle huge amount of data (big data). 
Most of the NoSQL are open sources and has the capability of horizontal scaling.
 It just stores data in some format other than relational. 
2. History behind NoSQL 
1.
 2.
 3.
 4.
 5.
 3.
 NoSQL databases emerged in the late 2000s as the cost of storage dramatically decreased. Gone were the days of needing to 
create a complex, difficult-to-manage data model in order to avoid data duplication. Developers (rather than storage) were 
becoming the primary cost of software development, so NoSQL databases optimised for developer productivity. 
Data becoming unstructured more, hence structuring (defining schema in advance) them had becoming costly.
 NoSQL databases allow developers to store huge amounts of unstructured data, giving them a lot of flexibility.
 Recognising the need to rapidly adapt to changing requirements in a software system. Developers needed the ability to iterate 
quickly and make changes throughout their software stack — all the way down to the database. NoSQL databases gave them 
this flexibility. 
Cloud computing also rose in popularity, and developers began using public clouds to host their applications and data. They 
wanted the ability to distribute data across multiple servers and regions to make their applications resilient, to scale out instead 
of scale up, and to intelligently geo-place their data. Some NoSQL databases like MongoDB provide these capabilities. 
NoSQL Databases Advantages 
A. Flexible Schema 
1.
 RDBMS has pre-defined schema, which become an issue when we do not have all the data with us or we need to change 
the schema. It's a huge task to change schema on the go. 
B. Horizontal Scaling 
1.
 2.
 Horizontal scaling, also known as scale-out, refers to bringing on additional nodes to share the load. This is difficult with 
relational databases due to the difficulty in spreading out related data across nodes. With non-relational databases, this is 
made simpler since collections are self-contained and not coupled relationally. This allows them to be distributed across 
nodes more simply, as queries do not have to “join” them together across nodes. 
Scaling horizontally is achieved through Sharding OR Replica-sets.
 C. High Availability 
1.
 2.
 NoSQL databases are highly available due to its auto replication feature i.e. whenever any kind of failure happens data 
replicates itself to the preceding consistent state. 
If a server fails, we can access that data from another server as well, as in NoSQL database data is stored at multiple 
servers. 
D. Easy insert and read operations. 
1.
 2.
 Queries in NoSQL databases can be faster than SQL databases. Why? Data in SQL databases is typically normalised, so 
queries for a single object or entity require you to join data from multiple tables. As your tables grow in size, the joins can 
become expensive. However, data in NoSQL databases is typically stored in a way that is optimised for queries. The rule of 
thumb when you use MongoDB is data that is accessed together should be stored together. Queries typically do not require 
joins, so the queries are very fast. 
But difficult delete or update operations.
 E. Caching mechanism. 
F.
 NoSQL use case is more for Cloud applications.
 4. When to use NoSQL? 
1.
 2.
 3.
 4.
 5.
 5.
 Fast-paced Agile development
 Storage of structured and semi-structured data
 Huge volumes of data
 Requirements for scale-out architecture
 Modern application paradigms like micro-services and real-time streaming.
 NoSQL DB Misconceptions 
1.
 2.
 Relationship data is best suited for relational databases.
 1.
 A common misconception is that NoSQL databases or non-relational databases don’t store relationship data well. NoSQL 
databases can store relationship data — they just store it differently than relational databases do. In fact, when compared 
with relational databases, many find modelling relationship data in NoSQL databases to be easier than in relational 
databases, because related data doesn’t have to be split between tables. NoSQL data models allow related data to be 
nested within a single data structure. 
CodeHelp
 NoSQL databases don't support ACID transactions.
1.
 Another common misconception is that NoSQL databases don't support ACID transactions. Some NoSQL databases like 
MongoDB do, in fact, support ACID transactions. 
6. Types of NoSQL Data Models 
1.
 Key-Value Stores 
1.
 2.
 3.
 4.
 5.
 6.
 T
 he simplest type of NoSQL database is a key-value store. Every data element in the database is stored as a key value pair 
consisting of an attribute name (or "key") and a value. In a sense, a key-value store is like a relational database with only 
two columns: the key or attribute name (such as "state") and the value (such as "Alaska"). 
Use cases include shopping carts, user preferences, and user profiles. 
e.g., Oracle NoSQL, Amazon DynamoDB, MongoDB also supports Key-Value store, Redis. 
A key-value database associates a value (which can be anything from a number or simple string to a complex object) with 
a key, which is used to keep track of the object. In its simplest form, a key-value store is like a dictionary/array/map object 
as it exists in most programming paradigms, but which is stored in a persistent way and managed by a Database 
Management System (DBMS). 
Key-value databases use compact, efficient index structures to be able to quickly and reliably locate a value by its key, 
making them ideal for systems that need to be able to find and retrieve data in constant time. 
T
 here are several use-cases where choosing a key value store approach is an optimal solution: 
a) Real time random data access, e.g., user session attributes in an online application such as gaming or finance. 
b) Caching mechanism for frequently accessed data or configuration based on keys. 
c) Application is designed on simple key-based queries. 
2. Column-Oriented / Columnar / C-Store / Wide-Column 
1.
 2.
 3.
 3.
 T
 he data is stored such that each row of a column will be next to other rows from that same column. 
While a relational database stores data in rows and reads data row by row, a column store is organised as a set of columns. 
T
 his means that when you want to run analytics on a small number of columns, you can read those columns directly 
without consuming memory with the unwanted data. Columns are often of the same type and benefit from more efficient 
compression, making reads even faster. Columnar databases can quickly aggregate the value of a given column (adding up 
the total sales for the year, for example). Use cases include analytics. 
e.g., Cassandra, RedShift, Snowflake. 
Document Based Stores 
1.
 2.
 3.
 4.
 T
 his DB store data in documents similar to JSON (JavaScript Object Notation) objects. Each document contains pairs of 
f
 ields and values. The values can typically be a variety of types including things like strings, numbers, booleans, arrays, or 
objects. 
Use cases include e-commerce platforms, trading platforms, and mobile app development across industries. 
Supports ACID properties hence, suitable for Transactions. 
e.g., MongoDB, CouchDB. 
4. Graph Based Stores 
1.
 2.
 3.
 4.
 7.
 A graph database focuses on the relationship between data elements. Each element is stored as a node (such as a person 
in a social media graph). The connections between elements are called links or relationships. In a graph database, 
connections are first-class elements of the database, stored directly. In relational databases, links are implied, using data to 
express the relationships. 
A graph database is optimised to capture and search the connections between data elements, overcoming the overhead 
associated with JOINing multiple tables in SQL. 
Very few real-world business systems can survive solely on graph queries. As a result graph databases are usually run 
alongside other more traditional databases. 
Use cases include fraud detection, social networks, and knowledge graphs. 
NoSQL Databases Dis-advantages 
1.
 Data Redundancy 
1.
 2.
 Since data models in NoSQL databases are typically optimised for queries and not for reducing data duplication, NoSQL 
databases can be larger than SQL databases. Storage is currently so cheap that most consider this a minor drawback, and 
some NoSQL databases also support compression to reduce the storage footprint. 
Update & Delete operations are costly. 
3.
 4.
 5.
 All type of NoSQL Data model doesn’t fulfil all of your application needs 
1.
 Depending on the NoSQL database type you select, you may not be able to achieve all of your use cases in a single 
database. For example, graph databases are excellent for analysing relationships in your data but may not provide what 
you need for everyday retrieval of the data such as range queries. When selecting a NoSQL database, consider what your 
use cases will be and if a general purpose database like MongoDB would be a better option. 
Doesn’t support ACID properties in general. 
Doesn’t support data entry with consistency constraints. 
CodeHelp
8. SQL vs NoSQL 
Data Storage Model
 SQL Databases
 Tables with fixed rows and 
columns
 NoSQL Databases
 Document: JSON documents, 
Key-value: key-value pairs, Wide
column: tables with rows and 
dynamic columns, Graph: nodes 
and edges
 Development History
 Developed in the 1970s with a 
focus on reducing data 
duplication
 Examples
 Oracle, MySQL, Microsoft SQL 
Server, and PostgreSQL
 Primary Purpose
 Schemas
 Scaling
 ACID Properties
 JOINS
 General Purpose
 Developed in the late 2000s with 
a focus on scaling and allowing 
for rapid application change 
driven by agile and DevOps 
practices.
 Document: MongoDB and 
CouchDB, Key-value: Redis and 
DynamoDB, Wide-column: 
Cassandra and HBase, Graph: 
Neo4j and Amazon Neptune
 Document: general purpose, Key
value: large amounts of data with 
simple lookup queries, Wide
column: large amounts of data 
with predictable query patterns, 
Graph: analyzing and traversing 
relationships between connected 
data
 Fixed
 Flexible
 Vertical (Scale-up)
 Horizontal (scale-out across 
commodity servers)
 Supported
 Not Supported, except in DB like 
MongoDB etc.
 Typically Required
 Typically not required
 Data to object mapping
 Required object-relational 
mapping
 Many do not require ORMs. 
MongoDB documents map 
directly to data structures in most 
popular programming languages.
 CodeHelp
LEC-16: Types of Databases 
1.
 Relational Databases 
1.
 2.
 Based on Relational Model.
 Relational databases are quite popular, even though it was a system designed in the 1970s. Also known as relational database 
management systems (RDBMS), relational databases commonly use Structured Query Language (SQL) for operations such as 
creating, reading, updating, and deleting data. Relational databases store information in discrete tables, which can be JOINed 
together by fields known as foreign keys. For example, you might have a User table which contains information about all your 
users, and join it to a Purchases table, which contains information about all the purchases they’ve made. MySQL, Microsoft SQL 
Server, and Oracle are types of relational databases. 
3.
 4.
 5.
 6.
 7.
 8.
 they are ubiquitous, having acquired a steady user base since the 1970s
 they are highly optimised for working with structured data. 
they provide a stronger guarantee of data normalisation
 they use a well-known querying language through SQL 
Scalability issues (Horizontal Scaling). 
Data become huge, system become more complex.
 2. Object Oriented Databases 
1.
 2.
 3.
 T
 he object-oriented data model, is based on the object-oriented-programming paradigm, which is now in wide use. 
Inheritance, object-identity, and encapsulation (information hiding), with methods to provide an interface to objects, are 
among the key concepts of object-oriented programming that have found applications in data modelling. The object-oriented 
data model also supports a rich type system, including structured and collection types. While inheritance and, to some extent, 
complex types are also present in the E-R model, encapsulation and object-identity distinguish the object-oriented data model 
from the E-R model. 
Sometimes the database can be very complex, having multiple relations. So, maintaining a relationship between them can be 
tedious at times. 
1.
 2.
 In Object-oriented databases data is treated as an object.
 All bits of information come in one instantly available object package instead of multiple tables.
 Advantages 
1.
 2.
 3.
 4.
 Data storage and retrieval is easy and quick.
 Can handle complex data relations and more variety of data types that standard relational databases.
 Relatively friendly to model the advance real world problems
 Works with functionality of OOPs and Object Oriented languages.
 4. Disadvantages 
1.
 2.
 3.
 5.
 High complexity causes performance issues like read, write, update and delete operations are slowed down.
 Not much of a community support as isn’t widely adopted as relational databases.
 Does not support views like relational databases.
 e.g., ObjectDB, GemStone etc. 
3.
 NoSQL Databases 
1.
 2.
 3.
 4.
 5.
 6.
 7.
 NoSQL databases (aka "not only SQL") are non-tabular databases and store data differently than relational tables. NoSQL 
databases come in a variety of types based on their data model. The main types are document, key-value, wide-column, and 
graph. They provide flexible schemas and scale easily with large amounts of data and high user loads. 
T
 hey are schema free.
 Data structures used are not tabular, they are more flexible, has the ability to adjust dynamically.
 Can handle huge amount of data (big data).
 Most of the NoSQL are open sources and has the capability of horizontal scaling.
 It just stores data in some format other than relational.
 Refer LEC-15 notes…
 4. Hierarchical Databases 
1.
 2.
 3.
 4.
 5.
 As the name suggests, the hierarchical database model is most appropriate for use cases in which the main focus of information 
gathering is based on a concrete hierarchy, such as several individual employees reporting to a single department at a 
company. 
T
 he schema for hierarchical databases is defined by its tree-like organisation, in which there is typically a root “parent” 
directory of data stored as records that links to various other subdirectory branches, and each subdirectory branch, or child 
record, may link to various other subdirectory branches. 
T
 he hierarchical database structure dictates that, while a parent record can have several child records, each child record can only 
have one parent record. Data within records is stored in the form of fields, and each field can only contain one value. Retrieving 
hierarchical data from a hierarchical database architecture requires traversing the entire tree, starting at the root node. 
Since the disk storage system is also inherently a hierarchical structure, these models can also be used as physical models.
 T
 he key advantage of a hierarchical database is its ease of use. The one-to-many organisation of data makes traversing the 
database simple and fast, which is ideal for use cases such as website drop-down menus or computer folders in systems like 
CodeHelp
Microsoft Windows OS. Due to the separation of the tables from physical storage structures, information can easily be added or 
deleted without affecting the entirety of the database. And most major programming languages offer functionality for reading 
tree structure databases. 
6.
 7.
 5.
 T
 he major disadvantage of hierarchical databases is their inflexible nature. The one-to-many structure is not ideal for complex 
structures as it cannot describe relationships in which each child node has multiple parents nodes. Also the tree-like 
organisation of data requires top-to-bottom sequential searching, which is time consuming, and requires repetitive storage of 
data in multiple different entities, which can be redundant. 
e.g., IBM IMS. 
Network Databases 
1.
 2.
 3.
 4.
 5.
 6.
 7.
 8.
 Extension of Hierarchical databases 
T
 he child records are given the freedom to associate with multiple parent records. 
Organised in a Graph structure. 
Can handle complex relations. 
Maintenance is tedious. 
M:N links may cause slow retrieval. 
Not much web community support. 
e.g., Integrated Data Store (IDS), IDMS (Integrated Database Management System), 
Raima Database Manager, TurboIMAGE etc.
 CodeHelp
LEC-17: Clustering in DBMS 
1.
 2.
 Database Clustering (making Replica-sets) is the process of combining more than one servers or instances connecting a single database. 
Sometimes one server may not be adequate to manage the amount of data or the number of requests, that is when a Data Cluster is needed. 
Database clustering, SQL server clustering, and SQL clustering are closely associated with SQL is the language used to manage the database 
information. 
Replicate the same dataset on different servers. 
3. Advantages
 1. Data Redundancy: Clustering of databases helps with data redundancy, as we store the same data at multiple servers. Don’t confuse this 
data redundancy as repetition of the same data that might lead to some anomalies. The redundancy that clustering offers is required and is 
quite certain due to the synchronisation. In case any of the servers had to face a failure due to any possible reason, the data is available at other 
servers to access.
 2.
 Load balancing: or scalability doesn’t come by default with the database. It has to be brought by clustering regularly. It also depends on the 
setup. Basically, what load balancing does is allocating the workload among the different servers that are part of the cluster. This indicates that 
more users can be supported and if for some reasons if a huge spike in the traffic appears, there is a higher assurance that it will be able to 
support the new traffic. One machine is not going to get all of the hits. This can provide scaling seamlessly as required. This links directly to 
high availability. Without load balancing, a particular machine could get overworked and traffic would slow down, leading to decrement of 
the traffic to zero. 
3. High availability: When you can access a database, it implies that it is available. High availability refers the amount of time a database is 
considered available. The amount of availability you need greatly depends on the number of transactions you are running on your database 
and how often you are running any kind of analytics on your data. With database clustering, we can reach extremely high levels of availability 
due to load balancing and have extra machines. In case a server got shut down the database will, however, be available. 
4. How does Clustering Work?
 1.
 In cluster architecture, all requests are split with many computers so that an individual user request is executed and produced by a number of 
computer systems. The clustering is serviceable definitely by the ability of load balancing and high-availability. If one node collapses, the 
request is handled by another node. Consequently, there are few or no possibilities of absolute system failures.
 CodeHelp
LEC-18: Partitioning & Sharding in DBMS (DB Optimisation) 
1.
 2.
 A big problem can be solved easily when it is chopped into several smaller sub-problems. That is what the partitioning technique does. It divides a 
big database containing data metrics and indexes into smaller and handy slices of data called partitions. The partitioned tables are directly used by 
SQL queries without any alteration. Once the database is partitioned, the data definition language can easily work on the smaller partitioned slices, 
instead of handling the giant database altogether. This is how partitioning cuts down the problems in managing large database tables. 
Partitioning is the technique used to divide stored database objects into separate servers. Due to this, there is an increase in performance, 
controllability of the data. We can manage huge chunks of data optimally. When we horizontally scale our machines/servers, we know that it gives us 
a challenging time dealing with relational databases as it’s quite tough to maintain the relations. But if we apply partitioning to the database that is 
already scaled out i.e. equipped with multiple servers, we can partition our database among those servers and handle the big data easily. 
3. Vertical Partitioning
 1.
 2.
 Slicing relation vertically / column-wise. 
Need to access different servers to get complete tuples. 
4. Horizontal Partitioning
 1.
 2.
 Slicing relation horizontally / row-wise. 
Independent chunks of data tuples are stored in different servers. 
5. When Partitioning is Applied?
 1.
 2.
 Dataset become much huge that managing and dealing with it become a tedious task. 
The number of requests are enough larger that the single DB server access is taking huge time and hence the system’s response time become 
high. 
6. Advantages of Partitioning
 1.
 2.
 3.
 4.
 5.
 Parallelism 
Availability 
Performance 
Manageability 
Reduce Cost, as scaling-up or vertical scaling might be costly. 
7. Distributed Database
 1.
 2.
 3.
 A single logical database that is, spread across multiple locations (servers) and logically interconnected by network. 
This is the product of applying DB optimisation techniques like Clustering, Partitioning and Sharding. 
Why this is needed? READ Point 5. 
8. Sharding
 1.
 Technique to implement Horizontal Partitioning. 
2.
 The fundamental idea of Sharding is the idea that instead of having all the data sit on one DB instance, we split it up and introduce a 
Routing layer so that we can forward the request to the right instances that actually contain the data. 
3. Pros
 1.
 2.
 4. Cons
 1. 
2.
 Scalability 
Availability 
Complexity, making partition mapping, Routing layer to be implemented in the system, Non-uniformity that creates the necessity of Re
Sharding 
Not well suited for Analytical type of queries, as the data is spread across different DB instances. (Scatter-Gather problem)
 CodeHelp
Database Scaling Patterns
 Step by Step Scaling
 CodeHelp- Lakshay
What will you learn?
 • Step by Step manner, when to choose which Scaling option.
 • Which Scaling option is feasible practically at the moment.
 CodeHelp
A Case Study
 Cab Booking APP
 • Tiny startup.
 • ~10 customers onboard.
 • A single small machine DB stores all customers, trips, locations, booking 
data, and customer trip history.
 • ~1 trip booking in 5 mins.
 CodeHelp
Your App becoming famous, but…
 The PROBLEM begins
 • Requests scales upto 30 bookings per minute.
 • Your tiny DB system has started performing poorly.
 • API latency has increased a lot.
 • Transactions facing Deadlock, Starvation, and frequent failure.
 • Sluggish App experience.
 • Customer dis-satisfaction.
 CodeHelp
Is there any solution?
 • We have to apply some kind of performance optimisation measures.
 • We might have to scale our system going forward.
 CodeHelp
Pattern 1
 Query Optimisation & Connection Pool Implementation
 • Cache frequently used non-dynamic data like, booking history, payment 
history, user profiles etc.
 • Introduce Database Redundancy. (Or may be use NoSQL)
 • Use connection pool libraries to Cache DB connections.
 • Multiple application threads can use same DB connection.
 • Good optimisations as of now.
 • Scaled the business to one more city, and now getting ~100 booking per 
CodeHelp
 minute.
Pattern 2
 Vertical Scaling or Scale-up
 • Upgrading our initial tiny machine.
 • RAM by 2x and SSD by 3x etc.
 • Scale up is pocket friendly till a point only.
 • More you scale up, cost increases exponentially.
 • Good Optimisation as of now.
 • Business is growing, you decided to scale it to 3 more cities and now getting 
300 booking per minute.
 CodeHelp
Pattern 3
 Command Query Responsibility Segregation (CQRS)
 • The scaled up big machine is not able to handle all read/write requests.
 • Separate read/write operations physical machine wise.
 • 2 more machines as replica to the primary machine.
 • All read queries to replicas.
 • All write queries to primary.
 • Business is growing, you decided to scale it to 2 more cities.
 • Primary is not able to handle all write requests.
 CodeHelp
 • Lag between primary and replica is impacting user experience.
Pattern 4
 Multi Primary Replication
 • Why not distribute write request to replica also?
 • All machines can work as primary & replica.
 • Multi primary configuration is a logical circular ring.
 • Write data to any node.
 • Read data from any node that replies to the broadcast first.
 • You scale to 5 more cities & your system is in pain again. (~50 req/s)
 CodeHelp
Pattern 5
 Partitioning of Data by Functionality
 • What about separating the location tables in separate DB schema?
 • What about putting that DB in separate machines with primary-replica or 
multi-primary configuration?
 • Different DB can host data categorised by different functionality.
 • Backend or application layer has to take responsibility to join the results.
 • Planning to expand your business to other country.
 CodeHelp
Pattern 6
 Horizontal Scaling or Scale-out
 • Sharding - multiple shards.
 • Allocate 50 machines - all having same DB schema - each machine just hold 
a part of data.
 • Locality of data should be there.
 • Each machine can have their own replicas, may be used in failure recovery.
 • Sharding is generally hard to apply. But “No Pain, No Gain”
 • Scaling the business across continents.
 CodeHelp
Pattern 7
 Data Centre Wise Partition
 • Requests travelling across continents are having high latency.
 • What about distributing traffic across data centres?
 • Data centres across continents.
 • Enable cross data centre replication which helps disaster recovery.
 • This always maintain Availability of your system.
 • Now, Plan for an IPO :p
 CodeHelp
LEC-20: CAP Theorem 
1.
 2.
 3.
 4.
 5.
 Basic and one of the most important concept in Distributed Databases. 
Useful to know this to design efficient distributed system for your given business logic. 
Let’s first breakdown CAP 
1.
 2.
 3.
 Consistency: In a consistent system, all nodes see the same data simultaneously. If we perform a read operation on a consistent system, it 
should return the value of the most recent write operation. The read should cause all nodes to return the same data. All users see the same data 
at the same time, regardless of the node they connect to. When data is written to a single node, it is then replicated across the other nodes in 
the system. 
Availability: When availability is present in a distributed system, it means that the system remains operational all of the time. Every request 
will get a response regardless of the individual state of the nodes. This means that the system will operate even if there are multiple nodes 
down. Unlike a consistent system, there’s no guarantee that the response will be the most recent write operation. 
Partition Tolerance: When a distributed system encounters a partition, it means that there’s a break in communication between nodes. If a 
system is partition-tolerant, the system does not fail, regardless of whether messages are dropped or delayed between nodes within the 
system. To have partition tolerance, the system must replicate records across combinations of nodes and networks. 
What does the CAP Theorem says, 
1.
 The CAP theorem states that a distributed system can only provide two of three properties simultaneously: consistency, availability, and 
partition tolerance. The theorem formalises the tradeoff between consistency and availability when there’s a partition. 
CAP Theorem NoSQL Databases: NoSQL databases are great for distributed networks. They allow for horizontal scaling, and they can quickly scale 
across multiple nodes. When deciding which NoSQL database to use, it’s important to keep the CAP theorem in mind. 
1.
 2.
 3.
 CA Databases: CA databases enable consistency and availability across all nodes. Unfortunately, CA databases can’t deliver fault tolerance. In 
any distributed system, partitions are bound to happen, which means this type of database isn’t a very practical choice. That being said, you still 
can find a CA database if you need one. Some relational databases, such as MySQL or PostgreSQL, allow for consistency and availability. You can 
deploy them to nodes using replication. 
CP Databases: CP databases enable consistency and partition tolerance, but not availability. When a partition occurs, the system has to turn 
off inconsistent nodes until the partition can be fixed. MongoDB is an example of a CP database. It’s a NoSQL database management system 
(DBMS) that uses documents for data storage. It’s considered schema-less, which means that it doesn’t require a defined database schema. It’s 
commonly used in big data and applications running in different locations. The CP system is structured so that there’s only one primary 
node that receives all of the write requests in a given replica set. Secondary nodes replicate the data in the primary nodes, so if the 
primary node fails, a secondary node can stand-in. In banking system Availability is not as important as consistency, so we can opt it 
(MongoDB).
 AP Databases: AP databases enable availability and partition tolerance, but not consistency. In the event of a partition, all nodes are available, 
but they’re not all updated. For example, if a user tries to access data from a bad node, they won’t receive the most up-to-date version of the 
data. When the partition is eventually resolved, most AP databases will sync the nodes to ensure consistency across them. Apache Cassandra is 
an example of an AP database. It’s a NoSQL database with no primary node, meaning that all of the nodes remain available. Cassandra allows 
for eventual consistency because users can re-sync their data right after a partition is resolved. For apps like Facebook, we value availability 
more than consistency, we’d opt for AP Databases like Cassandra or Amazon DynamoDB. 
CodeHelp
LEC-21: The Master-Slave Database Concept 
1. Master-Slave is a general way to optimise IO in a system where number of requests goes way high that a single DB server is not able to handle it 
efficiently. 
2. Its a Pattern 3 in LEC-19 (Database Scaling Pattern). (Command Query Responsibility Segregation) 
3. The true or latest data is kept in the Master DB thus write operations are directed there. Reading ops are done only from slaves. This architecture 
serves the purpose of safeguarding site reliability, availability, reduce latency etc . If a site receives a lot of traffic and the only available 
database is one master, it will be overloaded with reading and writing requests. Making the entire system slow for everyone on the site. 
4. DB replication will take care of distributing data from Master machine to Slaves machines. This can be synchronous or asynchronous depending 
upon the system’s need. C