Elements in a Database
Summary
Databases consists of several key elements that define its structure, functionality and interaction.
Details
Data Model
A data model refers to how data is structured, represented and related at high level. It focuses on how you logically organize and conceptualize data in terms of entities, relationships and constraints, regardless of how or where the data is stored.
- Conceptual Data Model: Focuses on high-level entities and their relationships. It doesn’t get into technical details of their attributes and how the data is stored.
- Logical Data Model: A more detailed model that defines the data entities, attributes and relationships, but are still independent of specific database technologies.
- Physical Data Model: Describes how data will be physically stores in a particular database system. This includes tables, column, data types, indexes and more.
Database model vs. Instances
A database model defines the logical structure of how data is stored, organized and managed in a database. It specifies what kind of data will be stored, their relationships and how the data can be manipulated. It is a more specific concrete implementation of a data model in a DBMS.
Instances are the actual data stored in the database at a particular moment. It’s essentially the real content in the database that fits the structure defined by the database model.
Three-Layer Architecture
The Three-Layer Architecture is a design model commonly used in database systems to organize the interaction between data storage, data structure, and data presentation. It separates the concerns of data storage, schema management, and user interaction into layers, to ensure flexibility, scalability, and security.
Internal layer
This layer deals with the physical storage of data. It is concerned with how data is actually stored on the disk or other storage media. PostgreSQL, mySQL & etc. are used here. Responsibilities:
- Storage Structures: Defines how data files are organized, indexed and stored.
- Access Methods: Handles the details of how the database engine retrieves and updates data.
- Efficiency: Focuses on optimizing storage space and retrieval speed.
Logical Layer
This layer defines the logical structure of the entire database. It is concerned with the schema and how data is organized and related to each other. DBMS are used here. Responsibilities:
- Schema Definition: Defines the structure of data, including tables, views, relationships, constraints and other schema components.
- Data Relationships: Describes how different pieces of data are related.
- Data Integrity: Enforces rules and constraints to ensure data consistency and validity.
External Layer
This layer provides the user views of the data. It defines how data is presented to different users or applications, often through various views or interfaces. Responsibilities:
- User Interfaces: Defines how different users or applications interact with the data, including custom views or queries.
- Security: Manages access control and permission, ensuring that users only see the data they are authorized to view or modify.
- Personalization: Provides different perspectives or formats of the data, depending on the needs of different users or applications.
Catalog
The catalog is a central repository of metadata in a DBMS. It includes definitions of database objects, user and security information, and administrative data. The catalog plays a crucial role in managing and operating the database by providing essential information needed for query execution, security enforcement, and overall database management. (Also known as a data dictionary or system catalog).
Database Users
DBA (Database Administrator)
The DBA is responsible for the overall management and maintenance of the database system. Responsibilities:
- Database Design: Designing and implementing the database schema, including tables, indexes, and relationships.
- Performance Tuning: Monitoring and optimizing database performance, including query optimization and resource management.
- Security Management: Managing user access and permissions, enforcing security policies, and ensuring data protection.
- Backup and Recovery: Performing regular backups, managing disaster recovery processes, and ensuring data availability.
- Configuration: Setting up and configuring the database system, including tuning system parameters and managing database resources.
- Troubleshooting: Identifying and resolving issues related to database operations, performance, and integrity.
Application Programmers
Application programmers (or developers) create software applications that interact with the database to perform various functions. Responsibilities:
- Application Development: Designing and developing applications that interface with the database. This includes creating software that performs CRUD (Create, Read, Update, Delete) operations on the data.
- Database Integration: Ensuring proper integration between the application and the database, including writing queries and handling data transactions.
- Testing: Testing the application to ensure that it interacts with the database correctly and handles data appropriately.
- Maintenance: Updating and maintaining the application to address bugs, improve performance, or adapt to changes in the database schema.
End Users
End users interact with the database to perform their day-to-day tasks and access data. Types of End Users:
- Casual Users: Users who perform simple queries and data retrieval operations through user interfaces or reports. For example, employees accessing their own records or generating basic reports.
- Power Users: Users who have a more in-depth understanding of the database and perform complex queries, analysis, and data manipulation. They might use tools like SQL queries or specialized data analysis applications.
Database Languages
Database languages are used to interact with and manage databases. They are categorized based on their primary functions in the database system.
DDL (Data Definition Language)
DDL is used to define and manage the structure of the database schema. It deals with the creation, modification, and deletion of database objects (not any data / instance related). See also: SQL Data Definition Language (DDL)
DML (Data Manipulation Language)
DML is used for managing and manipulating the data within the database. It deals with querying, inserting, updating, and deleting data. Not any structure related. See also: SQL Data Manipulation Language (DML)