DATABASE MANAGEMENT SYSTEM - ESSENTIAL POINTS
- Database Management System or DBMS in short refers to the technology of storing and retrieving user's data with utmost efficiency along with appropriate security measures.
- A modern DBMS has the following characteristics -
- Real-world entity - A modern DBMS is more realistic and uses real-world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use students as an entity and their age as an attribute.
- Relation-based tables - DBMS allows entities and relations among them to form tables. A user can understand the architecture of a database just by looking at the table names.
- Isolation of data and application - A database system is entirely different than its data. A database is an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process.
- Less redundancy - DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Normalization is a mathematically rich and scientific process that reduces data redundancy.
- Consistency - Consistency is a state where every relation in a database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of data storing applications like file-processing systems.
- Query Language - DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data. Traditionally it was not possible where file-processing system was used.
- ACID Properties - DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability (normally shortened as ACID). These concepts are applied on transactions, which manipulate data in a database. ACID properties help the database stay healthy in multi-transactional environments and in case of failure.
- Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them.
While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set,
general attributes and constraints.
ER Model is based on -- Entities and their attributes.
- Relationships among entities.
- An entity in an ER Model is a real-world entity having properties called attributes. Every attribute is defined by its set of values called domain.
- The logical association among entities is called relationship. Relationships are mapped with entities in various ways such as:
- One to One: One entity from entity set A can be associated with at most one entity of entity set B and vice versa.
- One to Many:One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.
- Many to One: More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.
- Many to Many:One entity from A can be associated with more than one entity from B and vice versa.
- Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.
- Super Key - A set of attributes (one or more) that collectively identifies an entity in an entity set.
- Candidate Key - A minimal super key is called a candidate key. An entity set may have more than one candidate key.
- Primary Key - A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.
- Going up in this structure is called generalization, where entities are clubbed together to represent a more generalized view.
- Specialization is the opposite of generalization. In specialization, a group of entities is divided into sub-groups based on their characteristics.
- Inheritance is an important feature of Generalization and Specialization. It allows lower-level entities to inherit the attributes of higher-level entities.
- Relational algebra is a procedural query language, which takes instances of relations as input and yields instances of relations as output. It uses operators to perform queries.
- SQL is a programming language for Relational Databases. It is designed over relational algebra and tuple relational calculus. SQL comes as a package with all major distributions of RDBMS.
- SQL comprises both data definition and data manipulation languages. Using the data definition properties of SQL, one can design and modify database schema, whereas data manipulation properties allows SQL to store and retrieve data from database.
