Database Management System
The software which is used to manage database is called Database Management System (DBMS). For example, MySQL, Oracle, etc., are popular commercial DBMS used in different applications. DBMS allows users the following tasks:
- Data Definition: It helps in creation, modification, and removal of definitions that define the organization of data in database.
- Data Updation: It helps in insertion, modification, and deletion of the actual data in the database.
- Data Retrieval: It helps in retrieval of data from the database which can be used by applications for various purposes.
- User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control and recovering information corrupted by unexpected failure.
Need for DBMS
A Database Management System (DBMS) is a piece of software that allows for the simple, efficient, and dependable processing and management of data. It can be used for the following purposes:
- Making of a database.
- Information retrieval from a database.
- Updating the database.
- Managing a database.
It provides numerous features and is more advantageous than the standard file system in the following ways:
1) Query Processing and Object Management
We cannot store data in the form of objects in typical file systems. Data is saved in objects rather than files in real-world applications. So, in a file system, some application software maps data saved in files to objects that may then be used.
In a database management system, we can directly store data in the form of objects. To manage, store, and scan through data on a file system, application-level code must be created, whereas a DBMS allows us to query the database.
2) Redundancy and inconsistency minimized
The term "redundancy" refers to several instances of the same data. A database system controls redundancy, whereas a file system may store the same data many times.
For example, if a student is enrolled in two separate educational programs at the same college, say, Engineering and History, his information, such as phone number and address, may be recorded twice, once in the Engineering department and once in the History department. As a result, it lengthens the time required to access and store data. This may potentially result in inconsistent data states in both locations. A database management system (DBMS) employs data normalization to reduce redundancy and duplicates.
3) Effective Memory Management and Indexing
Complex memory management is simplified by DBMS. Files are indexed instead of objects in file systems; hence query operations need whole file scans, whereas in a DBMS, object indexing occurs efficiently through database design based on any attribute of the data or a data-property. This facilitates the quick retrieval of data depending on the indexed attribute.
4) Transaction Management and Concurrency Control
Several applications allow users to access data at the same time. If files are used, this may result in data inconsistency.
Consider two withdrawal transactions X and Y, in which an amount of 100 and 200 is withdrawn from account A, which started with 1000. Because various transactions are occurring concurrently, different transactions may update the account in different ways.
- X reads 1000, debits 100, and updates account A to 900.
- Y reads 1000, debits 200, and updates account A to 800.
Account A has incorrect information in both circumstances. As a result, there is inconsistency in the data. A DBMS provides tools to deal with this type of data inconsistency while allowing users to access data concurrently.
A DBMS employs ACID (atomicity, durability, isolation, and consistency) features to ensure efficient transaction management without data corruption.
5) Data Access Control and Ease of Access
A DBMS can allow access to different users and choose which parts and how much data they can access from the database, hence eliminating redundancy. Otherwise, in a file system, separate files containing the quantity of data that each user can access must be generated for each user.
Furthermore, if a user wishes to extract specific data from a file system, he will require a code/application to perform that task.
Example: Assume a manager requires a list of all employees with salaries greater than X. If the data is saved in files, we must create business logic for it. In a DBMS, it allows for quick access to data via queries (e.g., SELECT queries), and the entire logic does not need to be rewritten. Users have the ability to specify exactly what they wish to extract from the data.
6) Integrity Constraints
Data stored in databases must satisfy integrity constraints. Consider a database schema that includes the numerous educational programs offered by a university, such as (B.Tech/M.Tech/B.Sc/M.Sc/BCA/MCA), and so on. Then we have a list of students that are enrolled in these programs.
A DBMS ensures that the student is only enrolled in one of the programs offered by the schema, and not in anything out of the blue. As a result, database integrity is preserved.
Additional DBMS Features
- Multiple User Interface
- Data Scalability, Expandability, and Flexibility: We can change the schema of the database; all schema will be updated accordingly.
- Reduced Development Time: Overall, the time for developing an application is reduced.
- Security: Simplifies data storage as it is possible to assign security permissions allowing restricted access to data.
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. It formulates all the constraints that are to be applied on the data.
A database schema defines its entities and the relationships among them. It contains a descriptive detail of the database, which can be depicted by means of schema diagrams. It’s the database designers who design the schema to help programmers understand the database and make it useful.
Types of Database Schema
- Physical Database Schema: Pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in secondary storage.
- Logical Database Schema: Defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.
Schema Change
A schema change is an alteration made to a collection of logical structures (or schema objects) in a database. Schema changes are generally made using Structured Query Language (SQL).
Star Schema
A star schema is the elementary form of a dimensional model, in which data are organized into facts and dimensions.
- Fact: An event that is counted or measured, such as a sale or login.
- Dimension: Reference data about the fact, such as date, item, or customer.
The star schema is a relational schema representing a multidimensional data model. It is called a star schema because the entity-relationship diagram simulates a star, with points diverging from a central table. The center consists of a large fact table, and the points of the star are the dimension tables.
Fact Tables
- Contain facts and are connected to dimensions.
- Columns include facts and foreign keys to dimension tables.
- Primary key is generally a composite key of all foreign keys.
- May include detailed or aggregated facts.
Dimension Tables
- Usually composed of hierarchies that categorize data.
- Primary keys form part of the composite primary keys of the fact table.
- Attributes are descriptive and textual.
- Typically smaller than fact tables.
Characteristics of Star Schema
- Denormalized database for quick query response.
- Flexible and easily updatable design.
- Matches end-user thinking patterns.
- Reduces metadata complexity.
Advantages of Star Schema
- Easy for users and applications to navigate.
- Queries run faster due to limited tables and clear joins.
- Referential integrity is built-in.
- Simple loading of large batches of data.
Snowflake Schema
- Expansion of the star schema with normalized dimension tables.
- Diagram resembles a snowflake.
- Improves query performance and reduces redundancy.
Disadvantages:
- Increased maintenance due to more tables.
- More complex queries.
- More joins leading to longer query execution times.
Fact Constellation Schema
- Two or more fact tables sharing one or more dimensions.
- Also called Galaxy schema.
- Sophisticated design, difficult to summarize.
- Can implement aggregate fact tables or decompose complex fact tables.
Disadvantage:
- Challenging design due to multiple aggregation variants.
Information Processing
- Deals with querying, statistical analysis, and reporting via tables, charts, or graphs.
- Modern data warehouses provide low-cost, web-based access tools.
Analytical Processing (OLAP)
- Supports drill-down, roll-up, and pivoting.
- Processes historical data in summarized and detailed formats.
- Provides multidimensional views for decision support.
Data Mining
- Analyzes hidden patterns and associations.
- Constructs models, classification, and prediction.
- Uses visualization tools for results.
Data mining involves selection, exploration, and modeling of large datasets to determine unknown patterns and provide useful results.
