Database Management Systems: Concepts, Security, and Design

Posted on Aug 3, 2024 in Computers

Advantages of Distributed Databases

Distributed databases offer several benefits:

  • Local Control of Data: Different departments or locations can manage their data locally.
  • Increased Database Capacity: Distributing data across multiple servers increases overall storage capacity.
  • System Availability: If one server fails, the database can often continue operating using other servers.
  • Improved Performance: Queries can be processed faster by distributing the workload.

The DBA’s Role in Access Privileges

The Database Administrator (DBA) plays a crucial role in managing access privileges. The DBA defines access rights for all users, ensuring they can only interact with the database in authorized ways. This involves:

  • Determining and implementing access privileges for all users.
  • Entering authorization rules into the DBMS catalog to enforce access control.
  • Documenting the access privilege policy.
  • Obtaining top-level management approval for the policy.
  • Communicating the policy to both management and all users.

Protecting Data from Hardware Failures and Power Outages

Organizations employ various strategies to safeguard their data:

Hard Drive Failures

Redundant Array of Inexpensive/Independent Drives (RAID) is commonly used to protect against hard drive failures. RAID systems replicate database updates across multiple hard drives, ensuring data availability even if one drive fails.

Power Interruptions and Outages

Uninterruptible Power Supply (UPS) systems provide backup power during short interruptions. For extended outages, organizations often rely on power generators to maintain continuous operation.

Hot Sites vs. Warm Sites for Disaster Recovery

Organizations use backup sites for disaster recovery, with hot sites and warm sites being two common options:

Hot Site

A hot site is a fully operational backup site that can be switched to within minutes or hours in the event of a disaster. It includes duplicate hardware, software, and data, ensuring minimal downtime.

Warm Site

A warm site is a backup site equipped with duplicate hardware and software but not data. Data recovery and synchronization take longer compared to a hot site, resulting in potentially longer downtime.

Exceptions to Production System Access Restrictions

While access to the production system is typically restricted, there are two main exceptions:

  1. Troubleshooting: When software or system issues arise, the DBA and authorized personnel may need access to diagnose and resolve the problem.
  2. Program Development and Modification: Programmers require access to develop, test, and deploy new or modified programs for the production system.

Two-Phase Locking for Transaction Management

Two-phase locking is a concurrency control mechanism used in databases to ensure data consistency. It involves two phases:

  1. Growing Phase: The DBMS acquires locks on required data resources but does not release any locks.
  2. Shrinking Phase: Once all necessary locks are acquired and updates are complete, the DBMS releases all locks. No new locks are acquired during this phase.

Advantages and Disadvantages of Timestamping

Timestamping is another concurrency control technique with its own set of pros and cons:

Advantages

  • Avoids the need for locking, reducing overhead and potential deadlocks.
  • Eliminates processing time associated with lock management.

Disadvantages

  • Requires additional storage space for timestamp values.
  • Incurs extra processing time for timestamp updates.

Handling Integrity Constraints

Integrity constraints ensure data accuracy and consistency. Here are four common approaches to handling them:

  1. Ignoring Constraints: This approach, while simple, risks data integrity issues.
  2. User Responsibility: Users are responsible for ensuring data updates comply with constraints. This approach relies heavily on user diligence.
  3. Programmer Responsibility: Programmers implement logic within applications to enforce constraints, preventing invalid data entry.
  4. DBMS Enforcement: The DBA defines constraints within the DBMS, which automatically enforces them, rejecting any violating updates.

Normalization in Database Design

Normalization is a database design process that aims to organize data efficiently and reduce redundancy. It involves a series of steps (normal forms) to achieve these goals:

  • Goal: To create a set of tables that represent the same information as the original but without update anomalies.
  • Process: Progressing through normal forms, each stage improves data organization and reduces redundancy.

Update Anomalies in First Normal Form

A relation in First Normal Form (1NF) but not in Second Normal Form (2NF) can encounter update anomalies due to partial dependencies:

  • Update Anomaly: Changing a single value might require multiple updates.
  • Inconsistent Data: Redundancy can lead to inconsistencies when data is updated in one place but not another.
  • Addition Anomaly: Inserting new data might require adding redundant information.
  • Deletion Anomaly: Deleting data might result in unintended loss of other information.

Candidate Keys, Primary Keys, and Alternate Keys

Understanding the relationship between these key types is crucial in database design:

  • Candidate Key: A column or set of columns that uniquely identifies a row in a table.
  • Primary Key: One of the candidate keys chosen to be the main identifier for a table.
  • Alternate Key: Candidate keys that are not selected as the primary key.

Second Normal Form vs. Third Normal Form

Normalization progresses through different levels, each addressing specific types of dependencies:

  • Second Normal Form (2NF): A table in 1NF where non-key columns are fully dependent on the entire primary key.
  • Third Normal Form (3NF): A table in 2NF where the only determinants are candidate keys, eliminating transitive dependencies.

Advantages of Views with Limited Fields

Using views to restrict access to specific fields offers several advantages:

  • Simplified User Experience: Users see a focused subset of data, making the database easier to understand and use.
  • Enhanced Security: Fields not included in the view are hidden from the user, protecting sensitive information.

When to Create Indexes

Indexes can significantly improve query performance. Consider creating indexes on fields or combinations of fields when:

  • The field is the primary key of the table.
  • The field is a foreign key used in relationships.
  • The field is frequently used for sorting data.
  • You often need to locate records based on values in this field.

Understanding Null Values

A null value represents the absence of a value. It’s distinct from a zero value or a blank space:

  • Null: Indicates missing, unknown, or inapplicable data.
  • Zero: Represents a specific numerical value.
  • Blank: Typically indicates an empty string.

Entity Integrity vs. Referential Integrity

These integrity rules ensure data consistency and relationships between tables:

  • Entity Integrity: Ensures each row in a table has a unique identity by preventing null values in the primary key.
  • Referential Integrity: Maintains consistency in relationships between tables. Foreign key values must match a primary key in the related table or be null.

Handling DBMS Limitations in Table Structure Changes

When your DBMS lacks the functionality for complex table structure changes, you can use SQL commands to achieve the desired result:

  1. Use the CREATE TABLE command to define the new table structure.
  2. Populate the new table with data from the existing table using the INSERT command with a SELECT clause.
  3. If your SQL dialect supports it, use the SELECT INTO command for a more concise way to create and populate the new table.

Primary Functions of a DBMS

A Database Management System (DBMS) provides essential functionalities for managing data:

  1. Database Structure Creation: Defining fields, tables, and relationships.
  2. Data Manipulation: Adding, modifying, and deleting records.
  3. Querying Data: Retrieving specific information using a query language.
  4. Report Generation: Creating formatted reports from the data.

Guidelines for Database Design

Following these guidelines helps ensure a well-structured and efficient database:

  1. Identify All Necessary Fields: Determine the data points required for the intended purpose.
  2. Decompose Data: Break down information into its smallest useful units.
  3. Group Related Fields: Organize fields into tables based on their relationships.
  4. Define Primary Keys: Choose a primary key for each table to uniquely identify records.
  5. Establish Relationships: Use common fields to create relationships between tables.
  6. Avoid Redundancy: Minimize data duplication to ensure consistency and efficiency.

Advantages of Primary Keys

Primary keys are fundamental to relational databases, offering several benefits:

  • Unique Identification: Each record has a unique identifier.
  • Enforced Uniqueness: The DBMS prevents duplicate values in the primary key field.
  • Guaranteed Presence: Users must provide a value for the primary key field for each record.
  • Default Ordering: Records are typically displayed in primary key order.
  • Improved Performance: Queries based on primary keys are generally faster.

Creating One-to-Many Relationships in Access

One-to-many relationships are common in databases, representing scenarios where one record in a table is related to multiple records in another table:

  • Common Field: Both tables must have a common field, which is the primary key in the”on” table and the foreign key in the”man” table.
  • Relationship Type: One record in the”on” table can be associated with multiple records in the”man” table, but each record in the”man” table can only be linked to one record in the”on” table.
  • Benefits: Once related, data from both tables can be accessed and queried as if they were a single table.