Education4U

Database Management Systems(DBMS)

× Fail to Delete - User Id should not be empty for review

Lec-01 : Introduction

Description

Basic concepts of DBMS such as its architecture, data models, data schemas, data independence, E-R model, relational database design, and storage and file structure, and much more.

Prerequisite

Better to have a well understanding of basic computer concepts such as primary memory, secondary memory, and data structures and algorithms.

Topics Covered

• ER Diagrams & Relational Model • Keys • Relational Algebra • B tree & sorting

• SQL Joins & Commands • PL/SQL & Triggers • Normalization

• Relational Calculus • Deadlock • Time stamping • Indexing

For whom

• This DBMS tutorial will especially help computer science students in understanding the basic-to-advanced concepts.

• For undergraduate students of Computer Science and Information Technology as well as those preparing for GATE exams.

• For those students who are preparing them for their Engineering Syllabus.

Reference books

1. “Database System Concepts” by Abraham Silberschatz and S Sudarshan

2. “Database Management Systems” by Raghu Ramakrishnan

3. “An Introduction to Database Systems” by Bipin Desai

4. “Principles of Database Systems” by J D Ullman

Q & A
Exam Questions

Q1 . What do you understand by the view and mention the steps to create, update, and drop a view?
Q2 . What is 2-Tier architecture? What is the 3-Tier architecture?
Q3 . What are the different types of relationships in the DBMS? Explain in brief with examples.
Q4 . Describe the concept of indexes.
Q5 . Explain the transaction in detail. Draw a state diagram of the transaction.
Q6 . Explain Data abstraction or 3 schema architecture.
Q7 . How is the pattern matching done in the SQL?
Q8 . How can you get the alternate records from the table in the SQL? Explain with an example.
Q9 . Explain different Relational set operators.
Q10 . Define and explain the different types of distribution transparency.

The E-R model is called the entity-relationship model in the DBMS, which is based on the concept of entities and the relationships that exist between these entities.

This is a constraint that is useful in describing the relationship between the different attributes in a relation. Example: If there is some relation ‘R1’ which has 2 attributes as Y and Z then the functional dependency among these 2 attributes can be shown as Y->Z which states that Z is functionally dependent on Y.

1NF is known as the First Normal Form. This is the simplest form of the normalization process, which states that the domain of the attribute should only have atomic values. The purpose is to delete duplicate columns that exist in the table.

A Join is one of the SQL statements which is used to join the data or the rows from 2 or more tables based on a common field/column among them.

There are 4 types of SQL Joins: • Inner Join: This type of join is used to fetch the data among the tables which are common in both the tables. • Left Join: This returns all the rows from the table which is on the left side of the join but only the matching rows from the table which is on the right side of the join. • Right-Join: This returns all the rows from the table which is on the right side of the join but only the matching rows from the table which is on the left side of the join. • Full Join: This returns the rows from all the tables on which the join condition has put and the rows which do not match hold null values.

The RDBMS is a relational database management system, which contains data in the form of tables and accesses data based on common fields between tables. There are three degrees of relation they are one-to-one (1:1), one-to-many (1: M), many-to-one (M: M).

The RDBMS is a relational database management system, which contains data in the form of tables and accesses data based on common fields between tables.

A deadlock is a situation that occurs when two transactions are waiting for locked resources or other transactions to keep. By enabling all transactions to acquire all locks at the same time, deadlocks can be prevented.

Normalization is the process of analyzing relational patterns, which are based on their respective functional dependencies and primary keys to achieving certain attributes. The properties include: • To minimize the redundancy of the data. • To minimize the Insert, Delete, and Update Anomalies.

Basically, there are 3 languages in DBMS, as described below: • DDL: DDL is a data definition language used to define the database and architecture structure by using some SQL queries (such as CREATE, ALTER, TRUNCATE, DROP, and RENAME). • DCL: DCL is a data control language, used to control the access of users within the database by using a set of SQL queries (such as GRANT and REVOKE). • DML: DML is a data manipulation language, used to perform certain operations in the database, such as insert, delete, etc., by using some SQL queries (such as SELECT, INSERT, DELETE, and UPDATE).

SQL stands for Structured Query Language, and its main purpose is to interact with relational databases in the form of inserting and updating/modifying data in the database. The subqueries are queries contained in other queries, and can also be referred to as inner queries found in outer queries.

There are three levels of data abstraction in a DBMS. They include: • Physical level: This is the lowest level of data abstraction, which specifies how data is stored in the database. • Logical level: This is the next level of data abstraction, which describes the type of data and the relationship between the data stored in the database. • View level: This is the highest level in data abstraction, it only displays/states a part of the database.

Drop: The DROP command is a DDL command used to delete/delete an existing table, database, index, or view from the database. The main differences between DROP, TRUNCATE, and DELETE commands are: DROP and TRUNCATE commands are DDL commands used to delete a table from the database, and once the table is deleted, all privileges and indexes related to the table will also be deleted. These two operations cannot be rolled back, so use them only when necessary. On the other hand, the DELETE command is a DML command, it is also used to delete rows from the table, and can be rolled back.

With the help of the LIKE operator, pattern matching is possible in the SQL.’%’ is used with the LIKE operator when it matches with the 0 or more characters, and ‘_’ is used to match the one particular character. Example: SELECT * from Emp WHERE name like ‘b%’; SELECT * from Emp WHERE name like ‘abc_’;

A stored procedure is a set of SQL statements in the form of functions, with some unique names, and is stored in a relational database management system (RDBMS), which can be accessed when needed.

• Redundancy control • Restriction for unauthorized access • Provides multiple user interfaces • Provides backup and recovery • Enforces integrity constraints • Ensure data consistency • Easy accessibility • Easy data extraction and data processing due to the use of queries

• Inconsistent • Not secure • Data redundancy • Difficult in accessing data • Data isolation • Data integrity • Concurrent access is not possible • Limited data sharing • Atomicity problem

An entity is a set of attributes in the database. Entities can be real-world objects that exist in this world. All entities have their attributes, which are regarded as characteristics of objects in the real world. For example: In the employee database of a company, the employee, department, and the designation can be considered as the entities. These entities have some characteristics which will be the attributes of the corresponding entity.

There are the following types of keys: • Primary key: The Primary key is an attribute in a table that can uniquely identify each record in a table. It is compulsory for every table. • Candidate key: The Candidate key is an attribute or set of an attribute which can uniquely identify a tuple. The Primary key can be selected from these attributes. • Super key: The Super key is a set of attributes that can uniquely identify a tuple. Super key is a superset of the candidate key. • Foreign key: The Foreign key is a primary key from one table, which has a relationship with another table. It acts as a cross-reference between tables.

This is a process managing simultaneous operations in a database so that database integrity is not compromised. The following are the two approaches involved in concurrency control: • Optimistic approach – Involves versioning • Pessimistic approach – Involves locking

The ACID stands for Atomicity, Consistency, Isolation, and Durability. It is used to ensure that the data transactions are processed reliably in a database system. • Atomicity: Atomicity refers to those transactions which are completely successful or failed. Here each transaction refers to a single logical operation of data. So, even if one part of any transaction fails, the entire transaction fails and the database state is left unchanged. • Consistency: Consistency ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state. • Isolation: The main goal of isolation is concurrency control. • Durability: Durability means that if a transaction has been committed, it will occur whatever may be the scenario.

Hash join Merge join Nested loops The hash join is used when you have to join large tables. Merge join is used when projections of the joined tables are sorted on the join columns. The nested loop consists of an outer loop and an inner loop.

Specialization: Specialization is the process of defining subcategories of a set of entity types. Here, each subclass will contain all the attributes and relationships of the parent entity. In addition to this, a subclass may contain other attributes and relationships specific to itself.

Generalization: Generalization is a process used to find relationships, the public attributes of a specific entity set; finally, define a common superclass for them.

There are two main integrity rules in DBMS. They are: • Entity integrity: This illustrates a very important rule, that is, the value of the primary key can never be a NULL value. • Referential integrity: This rule is related to foreign keys. The rule indicates that the value of the foreign key is a NULL value, or it should be the primary key of any other relationship.

Sohag kar

saksham

could you please provide the pdf notes of DBMS...

Pranav Reddy

Good course

Review :

3 | 4 Reviews

Course Feature

Category Computer Science
Language English
Price 79.00
Validity 1 Year
Rating 3 | 4 Reviews
Q & A 25
Exam Questions 10
Full Course PDF View
Wishlist

Add to Cart

Computer Science

Database Management Systems(DBMS)

Lec-01 : Introduction

Sohag kar

Sohag kar

saksham

Pranav Reddy

Course Feature

Share

Data Communication

Distributed Systems

Adhoc & Sensor

Operating Systems(OS)

Useful Links

Download

Computer Science

1.Introduction & Structure

2.ER diagrams & Relational model

3.Keys

4.Relational Algebra

5.SQL joins

6.SQL commands

7.PL/SQL & Triggers

8.Normalization

9.Relational Calculus

10.DeadLock

11.Time stamping

12.Indexing

13.B - Tree & Sorting

Database Management Systems(DBMS)

Lec-01 : Introduction

1.Introduction & Structure

2.ER diagrams & Relational model

3.Keys

4.Relational Algebra

5.SQL joins

6.SQL commands

7.PL/SQL & Triggers

8.Normalization

9.Relational Calculus

10.DeadLock

11.Time stamping

12.Indexing

13.B - Tree & Sorting

Q1 . What is the E-R model in the DBMS?

Q2 . What is a functional dependency in the DBMS?

Q3 . What is 1NF in the DBMS?

Q4 . What is a join in SQL?

Q5 . What are the different types of joins in SQL?

Q6 . What is a degree of Relation?

Q7 . What is RDBMS?

Q8 . Explain what is a deadlock?

Q9 . What is the purpose of normalization in DBMS?

Q10 . What are the different types of languages that are in the DBMS?

Q11 . What is the purpose of SQL? What is the concept of sub-query in terms of SQL?

Q12 . What are the different levels of abstraction in the DBMS?

Q13 . What is the use of the DROP command and what are the differences between DROP, TRUNCATE, and DELETE commands?

Q14 . How is the pattern matching done in the SQL?

Q15 . Explain the Stored Procedure.

Q16 . What are the advantages of DBMS?

Q17 . What are the disadvantages of file processing systems?

Q18 . What is an entity?

Q19 . Describe the types of keys?

Q20 . What is concurrency control?

Q21 . What are the ACID properties in DBMS?

Q22 . What are the differences between Hash join, Merge join, and Nested loops?

Q23 . Explain the term specialization.

Q24 . Explain the term generalization.

Q25 . What integrity rules exist in the DBMS?

Sohag kar

Sohag kar

saksham

Pranav Reddy

Course Feature

Share

Related Courses

Data Communication

Distributed Systems

Adhoc & Sensor

Operating Systems(OS)

Useful Links

Download