Database Management Systems Overview

Database Management Systems (DBMS)

• A DBMS is specialized software for interacting with users, other applications, and the database.
• It manages data definition, creation, querying, updating, and administration of databases.
• Well-known DBMSs include MySQL, Microsoft SQL Server, and Oracle.

Database Approach Characteristics

• Self-describing nature: The database system describes its own structure and constraints.
• Insulation between data and program: Changes to the file structure don't necessitate program changes. This is called data independence.
• Support for multiple user views: Each user has a different view of the database.
• Sharing of data and multiple user transaction processing: Multiple users can access data concurrently with controlled updates.

Advantages of DBMS

• Reduce Redundancy: Centralized data control eliminates unnecessary duplication.
• Eliminate inconsistencies: Reduces data inconsistencies caused by redundant data.
• Shared data: Enables sharing of data among multiple applications and users.
• Data Integrity: Centralized control ensures data accuracy and consistency.
• Security: Protects confidential data from unauthorized access.

Disadvantages of DBMS

• High cost of software/hardware and migration: Upgrading hardware and software is expensive.
• Problem associated with centralization: Backup and recovery in a centralized system is complex.
• Complexity of backup and recovery: Operations are complex, especially in concurrent multi-user systems.

ANSI/SPARC Architecture

• The architecture is divided into three levels: internal, conceptual, and external.
• Internal level: Closest to physical storage, describing how data is physically stored.
• Conceptual level: A level of indirection between internal and external levels, providing a community view.
• External level: Closest to users, concerned with how individual users view data.

Data Independence

• Applications are immune to changes in storage structure and access technique.

Database Administrator (DBA) Functions

• Makes strategic and policy decisions for enterprise data and the database.
• Defines the conceptual schema of the database.
• Determines how data is represented (internal schema).
• Establishes links with users to ensure data availability and writes necessary external schemas.
• Defines security and integrity rules for the database.
• Defines and implements recovery schemes for backups and reloading.
• Organizes the system to achieve optimal enterprise performance.

Data Model

• A data model is a collection of concepts used to describe the structure of a database.
  • Data types, relationships, and constraints are part of the structure.
  • Operations to retrieve and update data are also included.

High-Level/Conceptual Data Models

• Close to the way users perceive data.
• Use concepts like entities, attributes, and relationships.

Representational/Implementation Data Models

• Used in commercial DBMSs.
• Include relational, network, and hierarchical models.

Note: Legacy models are less common today.

Physical Data Models

• Describe how data is stored on the computer.
• Include record formats, ordering, and access paths (for efficient searches).

Database Schemas, Instances, Database State

• Schema: The description of the database, defined during design.
• Schema diagram: A diagram displaying the schema.
• Instance/State: The actual data in the database at a specific moment in time.
• The DBMS keeps descriptions of structures and constraints (meta-data).
• The schema (intension) and the state/instance (extension).

DBMS Languages

• Data Definition Language (DDL): Used by designers to define schemas, including the conceptual and internal schemas. A compiler processes DDL statements, identifies schema constructs and stores the descriptions in the DBMS catalog.
• Data Manipulation Language (DML): Allows users to manipulate data (retrieve, insert, delete, modify).
• High-Level/Nonprocedural DML: Specifies what data is needed, not how to retrieve it (Example: SQL).
• Low-Level/Procedural DML: Provides commands embedded in another programming language.

Database Personnel

• Database Administrators (DBAs): Oversee database management (authorization, coordination, acquisition of resources).
• Database Designers: Define the data to be stored and its representation.
• End Users: Interact with the database for querying, updating, and retrieving information. Multiple categories exist like casual, naive/parametric, sophisticated.

Relational Model

• Represents databases as collections of relations (tables).
• Relations use tuples(rows) and attributes(columns).
• Data values are typically related to real-world entities or relationships.

Key Fields, Candidate Keys, Primary Keys

• Key field(s) uniquely identify a record within a relation.
• Candidate key is a set of fields that uniquely identifies an entity.
• Primary key is a candidate key selected by the database designer as the principal means of entity identification.

Alternative, Secondary, and Composite Keys

• Alternate key: A candidate key not selected as a primary key.
• Secondary key: Attributes used for identifying records (not unique).
• Composite key: A combination of columns used to uniquely identify a row. Uniqueness is guaranteed only when combining the columns.

Foreign Keys and Entities

• Foreign key: A field in one table that references a unique identifier in another table. Maintains relationship between tables.
• Entity: A thing with independent existence, uniquely identifiable (an abstraction).

Weak and Strong Entities

• Weak entity: Existence depends on another entity (strong entity).
• Strong entity: Has a primary key, independent existence.

Attributes (Simple, Composite, Multi-valued, Stored/Derived, Complex)

• Attribute: A property that describes an entity.
• Simple attribute: Consists of a single value (cannot be subdivided).
• Composite attribute: Can be subdivided into simple attributes(e.g., address).
• Multi-valued attribute: Can have multiple values associated.
• Stored attribute: Directly stores a value.
• Derived attribute: Derives a value from other stored attributes(e.g., age from DOB).
• Complex attribute: Combination of simple and/or multivalued composite attributes.

Entity-Relationship (E-R) Model

• Used to model data aspects of a business domain or process in an abstract way suitable for implementation in a database (like a relational database).
• ER diagrams are used to represent entities, relationships, and databases graphically.

Cardinality Ratio (One-to-One, One-to-Many, Many-to-Many)

• One-to-One: Each entity is related to at most one other entity.
• One-to-Many: One entity is related to many other entities, but each other entity is related to at most one entity of the first type.
• Many-to-Many: Multiple entities from one type can relate to multiple entities from another type.

Normalization

• Simplifies data relationships in a database to reduce redundancy, inconsistencies, and anomalies (errors).
• Normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF) ensure data structure optimizes data integrity.

Denormalization

• Introduces redundancy to improve database performance for certain applications.

Structured Query Language (SQL)

• Standard language to work with relational databases, enabling queries.

Explore the essential features and advantages of Database Management Systems (DBMS) in this quiz. Learn about the self-describing nature of databases, data independence, and how DBMS reduces redundancy and inconsistencies. Ideal for students and professionals looking to deepen their understanding of database systems.