Database Management Systems: Concepts and Applications
A database management system (DBMS) is a software package designed to define, manipulate, retrieve, and manage data in a database.
Characteristics of DBMS
- Provides security and removes redundancy (duplication)
- Insulation between programs and data abstraction
- Support of multiple views of the data
- Sharing of data and multiuser transaction processing
- DBMS allows entities and relations among them to form tables.
Objective of DBMS
- Eliminate redundant data.
- Make access to the data easy for the user.
- Provide for mass storage of relevant data.
- Protect the data from physical harm and un-authorized systems.
- Allow for growth in the database system.
Importance of DBMS
A database management system is important because it manages data efficiently and allows users to perform multiple tasks with ease. A database management system stores, organizes, and manages a large amount of information within a single software application. Use of this system increases efficiency of business operations and reduces overall costs. Database management systems are important to businesses and organizations because they provide a highly efficient method for handling multiple types of data.
Advantages of DBMS
- DBMS offers a variety of techniques to store & retrieve data.
- DBMS serves as an efficient handler to balance the needs of multiple applications using the same data.
- Application programmers are never exposed to details of data representation and storage.
- A DBMS uses various powerful functions to store and retrieve data efficiently.
- Offers Data Integrity and Security
Disadvantages of DBMS
Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization. Most database management systems are often complex systems, so the training for users to use the DBMS is required.
Database Design Process
Database systems are designed to manage large bodies of information. Database design mainly involves the design of the database schema. The design of a complete database application environment that meets the needs of the enterprise being modeled requires attention to a broader set of issues.
Data Abstraction
A database system is a collection of interrelated data and a set of programs that allow users to access and modify these data. A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained.
Data Model
A collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. A data model provides a way to describe the design of a database at the physical, logical, and view levels. The data models can be classified into four different categories
Relational Model
Entity-Relationship Model
The entity-relationship (E-R) data model uses a collection of basic objects, called entities, and relationships among these objects.
Object-Based Data Model
Object-oriented programming (especially in Java, C++, or C#) has become the dominant software-development methodology
Semistructured Data Model
The semistructured data model permits the specification of data where individual data items of the same type may have different sets of attributes.
Database Languages
A database system provides a data-definition language to specify the database schema and a data-manipulation language to express database queries and updates.
Database Security
Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious threats and attacks.
Database Security Issues
- No Security Testing Before Deployment
- Poor Encryption and Data Breaches Come Together
- Stolen Database Backups
- Flaws in Features 1
- Weak and Complex DB Infrastructure
Types of Database Security
Access Protection/Control User Account and Database Audits Mandatory access control (MAC) Discretionary access control (DAC)
xdklsadjpsdsfhldb
sjbsdnbsjd lsbds
Query by Example (QBE)
Query by example is a query language used in relational databases that allows users to search for information in tables and fields by providing a simple user interface where the user will be able to input an example of the data that he or she wants to access.
DBMS Structure
A database system is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into the storage manager and the query processor components
Query Processor
DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary. DML compiler, which translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. A query can usually be translated into any of a number of alternative evaluation plans that all give the same result. The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives.
Storage Manager
A storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. It is responsible for storing, retrieving, and updating data in the database.
Transaction Manager
A transaction is a collection of operations that performs a single logical function in a database application. Each transaction is a unit of both atomicity and consistency. Thus, we require that transactions do not violate any database-consistency constraints. That is, if the database was consistent when a transaction started, the database must be consistent when the transaction successfully terminates
DBMS Architecture
DBMS architecture helps in the design, development, implementation, and maintenance of a database. A database stores critical information for a business. Selecting the correct Database Architecture helps in quick and secure access to this data.
Data Independence
The main purpose of data abstraction is achieving data independence in order to save time and cost required when the database is modified or altered.
Physical Level Data Independence
It refers to the characteristic of being able to modify the physical schema without any alterations to the conceptual or logical schema
Logical Level Data Independence
It refers to the characteristic of being able to modify the logical schema without affecting the external schema or application program
Strong Entity Set Vs Weak Entity Set
- The basic difference between a strong entity and a weak entity is that the strong entity has a primary key whereas, a weak entity has the partial key which acts as a discriminator between the entities of a weak entity set.
- A weak entity always depends on the strong entity for its existence whereas, a strong entity is independent of any other entity’s existence.
- A strong entity is denoted with a single rectangle and a weak entity is denoted with a double rectangle.
- The relationship between two strong entities is denoted with a single diamond whereas, a relationship between a weak and a strong entity is denoted with a double diamond called Identifying Relationship.
Generalization
is like a bottom-up approach in which two or more entities of a lower level combine to form a higher-level entity if they have some attributes in common. In generalization, an entity of a higher level can also combine with the entities of the lower level to form a further higher-level entity.
Structure of RDBMS
A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database. MySQL, SQL-Server, MS-Access, and Oracle are some of the popular RDBMS
Database Schema
A database schema is the skeleton structure that represents the logical view of the entire database. It defines how the data is organized and how the relations among them are associated.
Keys
A DBMS key is an attribute or set of an attribute which helps you to identify a row (tuple) in a relation (table).
Relational Algebra
Every database management system must define a query language to allow users to access the data stored in the database. Relational Algebra is a procedural query language used to query the database tables to access data in different ways.
z
Introduction to SQL
SQL is a standard language for accessing and manipulating databases. SQL stands for Structured Query Language SQL lets you access and manipulate databases
What Can SQL Do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
SQL AND, OR, and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE. The NOT operator displays a record if the condition(s) is NOT TRUE
AND Syntax
SELECT column1, column2, FROM table name WHERE condition1 AND condition2 AND condition3 …;
OR Syntax
SELECT column1, column2, FROM WHERE conditionl OR condition2 OR condition3 …;
NOT Syntax
SELECT column1, column2, FROM.table name WHERE NOT condition;
Stored Procedure Syntax-MySQL
DELIMITER //
CREATE PROCEDURE procedure_name(paraml,param2,…)
BEGIN
Write Query
END //
DELIMITER;
SQL Triggers
A trigger is a stored procedure in the database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.
Syntax:
create trigger [trigger_name] [before | after] {insert | update | delete} on [tablename] [for each row] [triggerbody]
BEFORE and AFTER of Trigger: BEFORE triggers run the trigger action before the triggering statement is run. AFTER triggers run the trigger action after the triggering statement is run.
Query Processing
Query Processing is a translation of high-level queries into low-level expression. It is a stepwise process that can be used at the physical level of the file system, query optimization, and actual execution of the query to get the result. It requires the basic concepts of relational algebra and file structure.
Selection Operation
File scan-search algorithms that locate and retrieve records that fulfill a selection condition. Algorithm A1 (linear search). Scan each file block and test all records to see whether they satisfy the selection condition
Sorting Operation
We may build an index on the relation, and then use the index to read the relation in sorted order. May lead to one disk block access for each tuple.
Join Operation
Several different algorithms to implement joins -Nested-loop join -Block nested-loop join -Indexed nested-loop joiry
Query Optimization
A single query can be executed through different algorithms or re-written in different forms and structures. A query optimizer is a critical database management system (DBMS) component that analyses Structured Query Language (SQL) queries and determines efficient execution mechanisms.
Importance of Query Optimization
The goal of query optimization is to reduce the system resources required to fulfill a query, and ultimately provide the user with the correct result set faster. First, it provides the user with faster results, which makes the application seem faster to the user. Secondly, it allows the system to service more queries in the same amount of time, because each request takes less time than unoptimized queries. Thirdly, query optimization ultimately reduces the amount of wear on the hardware (e.g. disk drives), and allows the server to run more efficiently
Types of DBA
- Administrative DBA = maintains the servers and databases and keeps them running. Concerned with backups, security, patches, replication.
- Development DBA- works on building SQL queries, stored procedures, and so on, that meet business needs. 3. es indexes, data structures, and relationships.
nhvfdlndskvckds
ACID Properties
A transaction is a very small unit of a program and it may contain several low-level tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability – commonly known as ACID properties
- Atomicity This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none.
- Consistency The database must remain in a consistent state after any transaction.
- Isolation
In a database system where more than one transaction is being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. 4) Durability The database should be durable enough to hold all its latest updates even if the system fails or restarts.
Serializability
When multiple transactions are running concurrently then there is a possibility that the database may be left in an inconsistent state. Serializability is a concept that helps us to check which schedules are serializable.
Types of Serializability
- Conflict Serializability Conflict Serializability is one of the types of Serializability, which can be used to check whether a non-serial schedule is conflict serializable or not.
- View Serializability View Serializability is a process to find out that a given schedule is view serializable or not. Two schedules S1 and S2 are said to be view equal if below conditions are satisfied:
Deadlock Handling
Deadlock refers to a specific situation where two or more processes are waiting for each other to release a resource or more than two processes are waiting for the resource in a circular chain.
Deadlock Avoidance
When a database is stuck in a deadlock state, then it is better to avoid the database rather than aborting or restating the database. This is a waste of time and resource.
Deadlock Prevention
Deadlock prevention method is suitable for a large database. If the resources are allocated in such a way that deadlock never occurs, then the deadlock can be prevented.
Deadlock Detection
In a database, when a transaction waits indefinitely to obtain a lock, then the DBMS should detect whether the transaction is involved in a deadlock or not.
Database normalization, or simply normalization, is the process of restructuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity
Advantages of Normalization //A smaller database can be maintained as normalization eliminates the duplicate data.// Overall size of the database is reduced as a result.// As databases become lesser in size, the passes through the data becomes faster and shorter thereby improving response time and speed. ·Avoid redundant fields or columns. ·//More flexible data structure i.e. we should be able to ad new rows and data values easily .//Better understanding of data.
Disadvantages of Normalization //Database systems are complex, difficult, and time-consuming to design. //Substantial hardware and software start-up costs. //Initial training required for all programmers and users. //1On Normalizing the relations to higher normal forms i.e.4NF,5NF the performance degrades.
Types of Functional Dependencies
Multivalued Dependency
: Multivalued dependency occurs when two attributes in a table are independent of each other but, both depend on a third attribute
Trivial Functional Dependency
The Trivial dependency is a set of attributes which are called trivial if the set of attributes are included in that attribute.
Non-Trivial Functional Dependency
If a functional dependency X->Y holds true where Y is not a subset of X then this dependency is called non-trivial Functional dependency.
Transitive Dependency
If non-primary key attributes depend upon other non-primary key attributes than there occurs transitive dependency.
Database Anomalies
Database anomalies are the problems in relations that occur due to redundancy in the relations. They can occur in poorly planned, un-normalized databases where all the data is stored in one table. These anomalies affect the process of inserting, deleting, and modifying data in the relations.
. What is a Stored Procedure? A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.