GATE Exam: Database Preparation with Sanchit Sir

Introduction to Database Preparation with Sanchit Sir

• Sanchit Sir has 15 years of experience teaching GATE exam courses both online and offline.
• A free, structured GATE exam preparation course is available with time management strategies.
• The course includes concept videos, PYQ solutions, notes, and an exam roadmap.
• The course starts from basic levels, making it suitable for learners without prior knowledge.
• Course content aligns with the GATE exam syllabus.
• Focus is given to high-weightage topics, shortcut tricks, and techniques.
• Video descriptions contain links to pro-level notes and a timeline for chapter navigation.
• Show support by liking the video, subscribing to the channel, and commenting #WithSanchitSir.

Importance and Overview of Databases

• Databases are a core subject in Computer Science and Information Technology (CS/IT).
• GATE allocates 7-8 marks to databases, with the potential for more.
• Typically, there are 5-6 questions, making it a highly important subject.
• The database subject is considered relatively easy.
• It is more scoring and less time-consuming.
• Predominantly features numerical questions, except for the query part.
• Key topics include transactions, ER diagrams, normalization, functional dependencies, keys, and indexing.
• The course aims for comprehensive coverage, with topics designed for high score potential.
• Database knowledge is essential in the industry.

Course Structure and Study Tips

• The course starts with transaction concurrency control, followed by ER diagrams and relational models.
• The syllabus covers functional dependencies, keys, normalization, file organization, indexing, and queries.
• Students should allocate proper time, create notes, attempt homework, and revisit topics multiple times.
• Course completion is expected by August/September.
• Honest feedback and questions are encouraged in the comment section.
• The course content is free, so share it with others.
• Understand data, information, and database with management system in detail.
• Understand the problems with the file system and why professional database systems are required.
• A thorough understanding of "Instances and Schemas" is necessary.
• Notes are provided for a professional-level understanding of data.
• Start with a basic level of understanding, and gradually increase the knowledge level.

Data vs. Information

• Data consists of raw, unorganized facts, figures, or characteristics, usually numerical, collected through observations.
• Information is processed, organized, or meaningful data that provides context or insight.
• Input is generally treated as data.
• A user's understanding or the results of work are treated as information.
• The same data can be treated as information by different people.

Database Definition & Purpose

• A database is an organized and interrelated collection of data stored and accessed electronically.
• Databases avoid random data collection.

Database Management System (DBMS)

• A DBMS is software providing end-users with mechanisms to store, modify, and extract information from a database.
• It should facilitate data storage, modification, and access.
• It incorporates administration, storage, efficiency, consistency, authorization, security, and recoverability.
• A DBMS is not just a database but a commercial system.

Problems with File Systems

• Data redundancy: The same data is stored in multiple locations.
• Data inconsistency: Conflicting versions of the same data exist.
• Difficulty in accessing data occurs due to a lack of query languages.
• Data isolation: Data is scattered across different files/systems.
• Integrity problems arise without enforced constraints (e.g., primary keys).
• Atomicity issues: Operations are only partially executed.
• Concurrent access anomalies: Issues arise with simultaneous data access.

Database Instance and Schema

• An instance is the collection of information stored in a database at a specific moment.
• It reflects the current state of the data in the database.
• A schema is the overall design and structure of the database (e.g., table structure, data types, constraints).
• The design of a database is called Schemas.

Transactions: Ensuring Data Integrity

• Transactions are fundamental for maintaining data consistency and reliability in databases.
• A transaction is a set of logically related instructions forming a logical unit of work.
• Transactions guarantee that either all instructions are executed or none, preventing partial execution problems.
• A transaction can contain single or multiple instructions.

Database Operations and Consistency

• The two main database operations are read (accessing data) and write (modifying data).
• The goal is to maintain database consistency through all types of operations.
• A database is considered consistent at the start but can be affected during modification.

ACID Properties of Transactions

• Atomicity: All transactions must be completed entirely or not at all.
• Consistency: A transaction takes the database from one valid state to another.
• Isolation: Transactions should appear isolated.
• Durability: Changes become permanent once a transaction is committed.
• Atomicity is ensured by the Recovery Control Manager or Transaction Control Manager.

Transaction States

• Active state: A transaction is executing.
• Partially committed: After the final instruction, the transaction is saved locally.
• Committed: The transaction is permanently written to the database.
• Failed state: A transaction faces a failure but is handled.
• Aborted: The system cannot work anymore and returns to its initial state.

Concurrency for Efficiency

• Concurrency allows multiple transactions simultaneously, enhancing speed and resource utilization.
• Benefits include shortened waiting times, optimized CPU throughput, and improved response times.

Concurrency Issues

• Major concurrency problems include Dirty Read, Unrepeatable Read, Lost Update, and Phantom Read.
• Loss of updates arises when multiple people update the same data, leading to data loss.
• A Dirty Read occurs if someone does not commit.
• An Unrepeatable Read refers to the same field returning different values multiple times.
• A Phantom Read refers to data being deleted by someone else, creating confusion.

Schedules: Managing Concurrent Transactions

• A schedule: A structure used to observe more than one transaction sequentially, which is followed using concurrency.

Types of Schedules

• Types are categorized as Serial and Non-Serial.
• A Serial Schedule has no concurrency that makes this is safe.
• A Serial Schedule can make factorial N schedules.
• A Non-Serial Schedule doesn't ensure consistency and needs to be tested.

Conflicting and Non-Conflicting Operations

• Conflicting Operations cannot be swapped.
• Non-Conflicting Operations can be swapped, maintaining a good understanding.
• Conflicting operations require different transactions and at least one write operation.

Conflict Serializability

• Approach: Swap instructions and find out if any issues arise.
• Conflicting operations cannot be swapped.
• Process: Find a Serial-type scheduling, then Non-Serial.
• Conflict is an important aspect of this concept.

Precendence Graph

• A graph is constructed to show transaction dependencies.
• Complications: A high possibility of high-level issues.
• The presence of a cycle indicates the scheduling would face conflict.
• It has a time complexity of n square, but the concept is very important.

View Serializability

• This ensures consistency and is considered better than Conflict Serializability.

Strategies For view Serializability

• Use if non-conflicting operations are found.
• A view must be a consistent point between schedules, including the initial read.
• Commits must also be constant.

Problems with view Sealizability

• It is time-consuming because all test cases must be applied.

Recoverable and Non-Recoverable Schedules

• Recoverable: A concept to ensure some recoverability of transactions when a failure happens.
• A principle to remember is to have a good way to clean the process.
• Redo operations are a good example.
• Non-Recoverable: Can get into trouble because it does not consider rollback.

Cascadeless Schedules

• Crucial for data recovery; it is the heart of Cascadeless Scheduling.

Concurrency Control Techniques

• These are the best practices to minimize rollbacks.

Important considerations

• Performance is the main factor while using concurrency, so it needs to be a priority.
• Easy-to-understand implementation improves usability.

Time Stamp Protocol

• The basic principle is that the transaction with the smallest serial number goes first.
• If data has already been written in the database, there are no options.
• The most recent version of the transaction has the most power.

Themes Write Rule

• This states that rollbacks are not allowed because the most recent write is allowed, and the previous write is ignored.

Two Phase Locking Protocol

• It has two parts: Growing and Shrinking.
• No more locks can be acquired after the shrinking phase begins.
• It suffers from deadlocks but guarantees some security aspects.
• To avoid deadlocks, it is better to acquire all the locks available.
• Other types include Conservative, and Regressive, which are important.

Entity Relationship (ER) Model

• A basic, non-technical diagram can create a database.

What to Look For in Entity

• One of the core aspects is it being a real-world object.
• Then, it is distinguishable among others.

Types of entities

• Tangible entities physically exist.
• Intangible entities exist only in the computer world.
• Entities are not represented in ER diagrams.

What is Entity Set?

• Similar collections of entities constitute entity sets.

Attributes: Describing Entities

• Columns in a table, each with a single name or multiple names.
• When designing, one must choose the data type that will be implemented.

Types of Attributes

• Single: Has only one data point, like a name.
• Multi-valued: Has multiple data points, like phone numbers.
• Use a separate table to separate this data.

Simple and Composite Attributes: Organizing Data

• Simple = Single data.
• Composite = Data like an Address being divided by different areas and localities.
• Composite data will have a connected oval shape.

Stored and Derived Attributes: Managing Information

• Stored = Saved in the database.
• Derived = Data that is calculated at the exact time to avoid data inconsistency.

Descriptive Attribute

• It helps in the big picture and has other values attached to it.
• Keep in mind the integration issues as well.

Desirable Properites

• Must be in the schema.
• Has to be non-null.
• It is always in existence.

Data vs Schemas

• Data reflects how it has been used; Schemas refers to design choices.
• Follow this in the back end when working.
• Relations can contain more or fewer relationships but are simplified to avoid problems.

Degree of a relationship

• A term to express how many entities are connected within it.

Types of DEgrees

• Unary: Where there is only 1 Entity.
• Binary: The most well-known entities that people go for.

More Properties

• Both sides can be not too specific as well.

Cardinality Constraints

• A good understanding of it is very important, as was previously understood about transactions.
• Minimum and maximum values should work together to avoid data corruption.
• Always draw ER diagrams in all cases.

From ER Diagrams to the Relational Model

• Understanding how different models convert to relational models, like tables, is crucial.

Convert a few Examples

• Convert into easy-to-understand form.
• Use real-world examples.
• Know how relationships transfer.

Keys and Functional Dependencies

• The most important step is creating the diagram and then transferring that to the rest.
• Integrity can be lost, so working slowly is better than working fast.

Redundancy and Integrity

• These issues must be fixed first.
• Then, try to make it as easy as possible for people.

Partial Function

• Occurs when a perfect solution isn't available and some kind of work needs to be done, but the current implementation isn't perfect. For example, trying to get all phone numbers when some phones might be missing.
• A function exists from x to y; determine variables from there.
• Create all the steps from there.

Function

• Test to ensure functions can't fail any type of checks, like when creating accounts.

Lossless Functions

• It's a design choice because one has to be careful; otherwise, data can be lost.
• Take note and get the design checked to avoid data loss.

Normal Forms!

• 1NF = All unique values and must have atomic types.
• 2NF = Must have 1NF and meet any partial requirements.
• Problems are identified and fixed to ensure everything works.
• 3NF = No more non-key dependencies, or non-prime attributes at all.

Decompostions

• Make designs cleaner and more effective for the product.

BCNF

• Great, but not as good as 3NF in terms of performance from the user's perspective, like speed and scalability. The key is having both well-designed systems without data loss and scaling as needed.
• Ensure it also works with very high-performance data.

Multi Valued Dependencies

• These values must exist; for example, A needs to be only in the schema.
• Can only be in 4NF.

Views also need to meet same aspect

• It has to provide the same experience for all users to avoid complications.

SQL or Relational Algebra?

• It is a combination of both. SQL is very important, so knowing it is beneficial.
• Practice and know how to use it.

Joins

• Know how joins work.
• Joins are all unique table joins.
• They are not too hard or deep.

Time Stamps

• Understand how timestamps affect all this data.

SQL

• It is made for queries
• And used to read data from the database.

Basic Key note

• A common way a type of structure is made.

Select Statement

• The results can be ordered to give the result in the format specified.
• Select statements have limited value.
• Where clauses are important for meeting conditions for different problems.

With this, the main structure has being cover but try to read it again to make it click and work.

Prepare for the GATE exam with Sanchit Sir's free database course. The course covers core CS/IT concepts, focusing on high-weightage topics and efficient problem-solving. Learn time management strategies and access notes for exam success.