Database Management System Functions and Structure

Database Management System (DBMS) Functions

A DBMS is a set of programs that provide access to data. Its primary functions include:

Core DBMS Capabilities

1. Facilitate Data Access: Enables easy access to data, including graphical representations.
2. Data Consistency and Integrity:
   • Consistency: Ensures any change respects the database integrity rules.
   • Integrity: Enforces restrictions (e.g., Laboral Age > 16).
   • Registration: Prevents duplicate entries (e.g., student not repeated).
   • Table Constraints: Limits on table size (e.g., N students per group < 35).
   • Relationships: Ensures referential integrity (e.g., a producer must have a valid vendor code existing in the suppliers table).
3. Database Security:
   • Making and retrieving Backups.
   • Reaction to system failures.
   • User Access Control: Login/password management and explicit permissions (GRANT / REVOKE).
4. Concurrency Control: Managing parallel access to data (e.g., simultaneous income (S) and receipt (S) in a checking account (CC)).
5. Administration Facilitation: Providing tools for database management (the DBMS itself).

DBMS Components

A DBMS typically includes:

• Management Tools for Administration: Tools to handle the database (creation, editing) and manage users (creation, privileges).
• Programming Tools for Users.

Types of Programming Languages

• Data Definition Language (DDL): Used for defining data structures (e.g., creating tables).
• Data Control Language (DCL): Used for managing users and permissions.
• Data Manipulation Language (DML): Used for manipulating records (insert, delete, modify, and view records). SQL is a primary example.

Data Integrity in Detail

Definition: Ensuring that all data in a database are CORRECT.

Integrity is enforced at two levels:

• Syntactic Integrity
• Semantic Integrity

Relational Database Elements

Database Definition (BD)

A database is a group of related data stored in a structured context that clearly expresses the relationships between them.

Relational Model Concepts

The Relational Model is one way to explain data structure, alongside Hierarchical and Network models.

Value and Relationships

A Value is an existing connection between two different tables (e.g., Students cycles linked to Professors_Cycles, or Credits (MP) linked to Specialties).

Cardinality of a Relationship

Defines the maximum number of elements in each table that can be related.

Example: Specialty $\leftrightarrow$ Credits. Patient $\leftrightarrow$ Doctor.

• 1 $\rightarrow$ 1 (One-to-One)
• M $\leftarrow$ 1 (Many-to-One)

How is a relationship established? By including the key field of one table in another table that it relates to.

Example: Football Fields (name, capacity, date, etc.) related to Soccer Equipment (name, equipment, etc., including the field name).

Field (Table Attribute)

An attribute is defined by:

1. Name: Must be unique within the table.
2. Data Type: The kind of data it can store:
   • Text (alphabetical, numeric)
   • Numeric (integer, real, etc.)
   • Logical (yes/no)
   • Date/Time
3. Maximum Size: The maximum space occupied.