Database Users
Database systems serve various types of users, each with different needs, responsibilities, and levels of access. Understanding these user categories is essential for proper database design and administration.
Responsible for authorizing access to the database, coordinating and monitoring its use, acquiring software and hardware resources, controlling its use and monitoring efficiency of operations.
Key Responsibilities:
- User access management
- Performance monitoring
- Backup and recovery
- Resource allocation
Responsible for defining the content, structure, constraints, and functions or transactions against the database. They must communicate with end-users and understand their needs.
Key Responsibilities:
- Conceptual design (ER diagrams)
- Logical design (schema)
- Defining constraints
- User requirements analysis
Users who use the data for queries, reports, and some update the database content. End-users can be categorized into different types based on their interaction patterns.
Types of End-Users
🎯 Casual End Users
Occasionally access the database but may need different ways to specify their requests. Typically middle- or high-level managers or other occasional browsers.
Access database for strategic decisions, reports, and analytics
📱 Naive or Parametric End Users
Make up a sizable portion of database end users. Their main job function revolves around constantly querying and updating the database using standard types of queries called canned transactions.
Examples of Naive User Tasks
- Banking: Bank customers and tellers check account balances and post withdrawals and deposits
- Reservations: Reservation agents or customers for airlines, hotels, and car rental companies check availability and make reservations
- Shipping: Employees at receiving stations for shipping companies enter package identifications via bar codes and update a central database of received and in-transit packages
- Social Media: Social media users post and read items on social media websites
Many of these tasks are now available as mobile apps for use with mobile devices, making database access more ubiquitous and user-friendly.
Database Security
Database security programs are designed to protect not only the data within the database, but also the data management system itself, and every application that accesses it, from misuse, damage, and intrusion.
Database security encompasses tools, processes, and methodologies which aim to maintain the confidentiality, integrity, and availability of sensitive information stored within a database.
Security Risk Sources
A security risk could be from one of the following three sources, each of which has privileged means of entry to the database:
An insider with ill-intent who deliberately attempts to compromise database security.
A person within the organization who exposes the database to attack through careless actions.
An outsider who obtains credentials through social engineering or other methods, or gains access to the database's credentials.
An insider threat is one of the most typical causes of database security breaches, and it often occurs because many employees have been granted privileged user access.
Key Components of Database Security
Restricts access to the database, ensuring only authorized users have permission to view or modify data. This involves user authentication, authorization, and role-based access controls.
Protects data by converting it into a secure format that requires a decryption key for access. This ensures that even if unauthorized access occurs, the data remains unreadable.
Records and monitors database activities, allowing administrators to track who accessed the database, what operations were performed, and when they occurred. This aids in identifying suspicious activities.
Conceals sensitive information when displayed to users without the necessary permissions. This prevents unauthorized users from viewing confidential data.
Regularly backs up database contents to prevent data loss due to accidental deletion, corruption, or security incidents. A robust recovery plan ensures quick restoration in case of a breach.
Regularly updates the database management system and associated software to address known vulnerabilities. This helps in closing potential security loopholes.
Implements network-level security measures to protect the database server from external threats, including firewalls, intrusion detection systems, and secure network configurations.
- Implement the principle of least privilege
- Use strong authentication mechanisms
- Regularly audit database access and activities
- Keep all database software up to date
- Encrypt sensitive data both at rest and in transit
- Conduct regular security training for database users
System Catalog (Data Dictionary)
What is the System Catalog?
A system catalog is available for each database. Information in the system catalog defines the structure of the database. For example, the DDL (Data Definition Language) for all tables in the database is stored in the system catalog.
📊 Database-Specific
Most system catalogs are copied from the template database during database creation and are thereafter database-specific.
🔗 Shared Catalogs
A few catalogs are physically shared across all databases in an installation.
🗃️ Part of Database
The system catalog for a database is actually part of the database itself.
System Catalog Structure
Within the database are objects such as tables, indexes, and views. The system catalog is basically a group of objects that contain information that defines other objects in the database, the structure of the database itself, and various other significant information.
The terms system catalog and data dictionary have been used interchangeably in most situations. We make a distinction in the context of data dictionary system, which is an implementation of the data dictionary or system catalog.
Information Stored in System Catalog
- Relation names
- Attribute names
- Attribute domains (data types)
- Descriptions of constraints (primary keys, secondary keys, foreign keys, NULL/NOT NULL, and other types of constraints)
- Views
- Storage structures and indexes (index name, attributes on which index is defined, type of index, etc.)
Security and Authorization Information
- Authorized user names and passwords
- Each user's privilege to access specific database relations and views
- The creator and owner of each relation
- Privileges granted using GRANT command
Statistical and Descriptive Information
📈 Tuple Statistics
Number of tuples in each relation
🔢 Attribute Values
The different attribute values and their distributions
💾 Storage Methods
Storage and access methods used in relation
⚡ Query Optimization
All such information finds its use in query processing
Accessing the System Catalog
In relational DBMSs, the catalog is stored as relations. The DBMS software is used for querying, updating, and maintaining the catalog. This allows DBMS routines (as well as users) to access the information stored in the catalog upon authorization with the help of query languages such as SQL.
Example: Catalog Structure
Let's look at a catalog structure for base relations with an example showing a relational schema and its corresponding catalog:
Student (RollNo, Name, Address, PhoneNo, DOB, email, CourseNo, DNo)
Course (CourseNo, CourseTitle, Professor)
Dept (DeptNo, DeptName, Location)
Grade (RollNo, CourseNo, Grade)
Catalog Table Example
| Relation_Name | Attribute | Attr_Type | PK | FK | FK_REL |
|---|---|---|---|---|---|
| Student | RollNo | INTEGER(10) | Yes | No | |
| Student | Name | VARCHAR2(30) | No | No | |
| Student | CourseNo | INTEGER(10) | No | Yes | Course |
| Student | DNo | INTEGER(10) | No | Yes | Dept |
| Course | CourseNo | INTEGER(10) | Yes | No | |
| Course | CourseTitle | VARCHAR2(10) | No | No | |
| Grade | RollNo | INTEGER(10) | Yes | Yes | Student |
| Grade | CourseNo | INTEGER(10) | Yes | Yes | Course |
The catalog stores relation names, attribute names, attribute types, and primary key as well as foreign key information.
The system catalog provides a self-describing nature to databases - the database stores not only data but also complete information about its own structure, making it easier for DBMS software to work with different database applications.
ACID Properties in DBMS
Transactions access data using read and/or write operations.
In order to maintain consistency in a database, before and after the transaction, certain properties are followed. These are called ACID properties.
The entire transaction takes place at once or doesn't happen at all
The database must be consistent before and after the transaction
Multiple transactions occur independently without interference
The changes of a successful transaction occur even if system failure occurs
Atomicity - "All or Nothing Rule"
By this, we mean that either the entire transaction takes place at once or doesn't happen at all. There is no midway - transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all.
❌ Abort
If a transaction aborts, changes made to the database are not visible.
✅ Commit
If a transaction commits, changes made are visible.
Transaction T: Transfer of 100 from account X to account Y
| Before: X = 500, Y = 200 | |
|---|---|
| T1 | T2 |
| Read (X) | Read (Y) |
| X := X - 100 | Y := Y + 100 |
| Write (X) | Write (Y) |
| After: X = 400, Y = 300 | |
If the transaction fails after completion of T1 but before completion of T2 (say, after write(X) but before write(Y)), then the amount has been deducted from X but not added to Y. This results in an inconsistent database state.
Solution: The transaction must be executed in its entirety to ensure correctness of database state.
Consistency
This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.
Referring to the example above:
The total amount before and after the transaction must be maintained:
- Total before T occurs = 500 + 200 = 700
- Total after T occurs = 400 + 300 = 700
Therefore, database is consistent. ✓
Inconsistency occurs in case T1 completes but T2 fails. As a result, T is incomplete, and the total would be 400 + 200 = 600 (loss of 100 units).
Isolation
This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of database state. Transactions occur independently without interference.
Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed.
Let X = 500, Y = 500. Consider two transactions T and T".
| T | T" |
|---|---|
| Read (X) | Read (X) |
| X := X * 100 | Read (Y) |
| Write (X) | Z := X + Y |
| Read (Y) | Write (Z) |
| Y := Y - 50 | |
| Write (Y) |
Suppose T has been executed till Read (Y) and then T" starts. As a result, interleaving of operations takes place:
- T" reads correct value of X (50,000) but incorrect value of Y (500)
- Sum computed by T": X + Y = 50,000 + 500 = 50,500
- Correct sum at end of T: X + Y = 50,000 + 450 = 50,450
This results in database inconsistency due to a loss of 50 units.
Solution: Transactions must take place in isolation and changes should be visible only after they have been made to the main memory.
Durability
This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs.
💾 Permanent Storage
Updates become permanent and are stored in non-volatile memory
🔒 Never Lost
The effects of the transaction are never lost, even after system crashes
📝 Written to Disk
Changes are written to persistent storage, not just kept in memory
The ACID properties, in totality, provide a mechanism to ensure correctness and consistency of a database in a way such that:
- Each transaction is a group of operations that acts as a single unit
- Produces consistent results
- Acts in isolation from other operations
- Updates that it makes are durably stored
Summary
These four handouts complement Chapter 1 by providing deeper insights into critical database concepts:
👥 Database Users
Understanding the different types of users (DBAs, designers, end-users) helps in designing appropriate interfaces and access controls.
🔒 Database Security
Security is paramount in protecting sensitive data through access control, encryption, audit trails, and other protective measures.
📚 System Catalog
The self-describing nature of databases through system catalogs enables flexibility and efficient database management.
⚡ ACID Properties
Transaction management through ACID properties ensures data reliability, consistency, and integrity in database systems.
Integration with Chapter 1:
These concepts work together to create robust, secure, and reliable database systems. Users interact with databases that are protected by security measures, described by system catalogs, and managed through ACID-compliant transactions.