Database and DBMS Types

Database and DBMS

• A database is a shared, integrated computer structure storing end-user data (raw facts) and metadata (data about data).
• A DBMS (database management system) is a collection of programs managing database structure and controlling data access.
• DBMS facilitates data sharing among applications/users and enhances data management efficiency.

Types of Databases by Number of Users

• Single-user databases support one user at a time.
• Desktop databases are single-user and run on personal computers.
• Multi-user databases support multiple concurrent users.
• Workgroup databases support small user groups (typically under 50) or single departments.
• Enterprise databases support large user groups or entire organizations (typically hundreds of users).

Types of Databases by Location

• Centralized databases support data located at a single site.
• Distributed databases support data distributed across multiple sites.

Types of Databases by Use

• Transactional (or production) databases support a company's daily operations.
• Data warehouses store data used for tactical or strategic decisions and often contain historical data; this area has evolved into Business Intelligence as its own discipline.

Types of Databases by Model

• Hierarchical databases organize data in a tree-like structure.
• Object databases organize data into objects (Object-Oriented Development).
• Relational databases organize data into table structures, where tables are grouped into relations.

Functions of a DBMS

• A DBMS guarantees data integrity and consistency.
• Data dictionary management defines data elements and relationships (metadata).
• Data storage management stores data, entry forms, and report definitions.
• Data transformation and presentation translates logical requests into physical commands to locate and retrieve data.
• Security management enforces user security and data privacy.
• Multiuser access control: ensures concurrent database access without compromising data integrity.
• Backup/recovery management provides backup and data recovery procedures.
• Data integrity management promotes and enforces integrity rules.
• Database access languages/APIs provide data access through query languages (e.g., VB, C#, Java).
• Database communication interfaces allow accepting end-user requests via various network environments.

File Systems

• Manual file systems involve collections of file folders in a cabinet.
• Folders are organized based on expected data use.
• Such systems are adequate for small data amounts with limited reporting needs.
• Finding and using data in growing file collections becomes cumbersome.

Entity Definition

• An entity is an object that exists and requires no specific action to exist.
• Entities can be things, persons, places, or objects, and data is stored about them.

Table Definition

• A table is a two-dimensional structure with rows and columns.
• The term "relation" is a synonym for table.
• A table is a persistent relation, and its contents can be saved for future use.

Schema Definition

• A relational schema is a textual representation of database tables, described by name and attributes in parentheses.
• A relational schema R is formally defined as R = {a1, a2,...,an}, where a1...an are a set of the attributes belonging to the relation.
• Primary keys are underlined, and foreign keys are marked with an asterisk (*).

Degree and Cardinality

• Degree and cardinality are properties of the relational model.
• A relation with N columns and M rows has a degree of N and a cardinality of M.
• The degree is the number of attributes, while cardinality is the number of tuples.
• The product of degree and cardinality equals the number of attribute values.

Relationships in Relational Databases

• One-to-many (1:*) is the ideal relationship for relational modeling and database design.
• One-to-one (1:1) relationships should be rare.
• Many-to-many (:) relationships cannot be directly implemented and must be changed into two 1:* relationships.

Conceptual Modeling Process

• Data modeling creates an abstract database structure representing real-world objects with clear business understanding and its functional areas.
• All needed data should be included in the model.
• Process involves Data analysis and requirements, Entity relationship modeling and normalization, Data model verification, and Distributed database design

Business Rules

• Business rules are brief, precise, and unambiguous descriptions of policies, procedures, or principles within an organization.
• They apply to any organization storing and using data and describe operations enforcing actions.
• An example of a business rule: "A customer may generate many invoices".
• Business rules must be written, kept up-to-date, are sometimes external, easy to understand, disseminated, and describe data characteristics.

Hierarchical Model Advantages

• Many features laid the foundation for current data models.
• It promotes data sharing and conceptual simplicity through parent/child relationships.
• Older database application advantages are replicated in current database environments.
• It creates a large installed base with programmers who developed tested business applications.

Hierarchical Model Disadvantages

• Hierarchical Model is complex to implement and difficult to manage due to knowledge requirements of the hierarchical path.
• It lacks structural independence, and has implementation limitations (no multi-parent or many-to-many relationships).
• Lack of standards.

Network Model Advantages

• Resembles the hierarchical model with records in 1:* relationships but allows records to have multiple parents.
• Handles more relationship types.
• Conceptual simplicity is at least equal to the hierarchical model, and there is conformance to standards.

Network Model Disadvantages

• It is too cumbersome as information needs grow, requiring more complex databases.
• The lack of ad hoc query capability put heavy pressure on programmers.

Relational Model Advantages

• The RDBMS hides the complexities of the relational model from the user.
• Users see the relational database as a collection of related tables.
• Data can be manipulated and queried intuitively.
• A table is a matrix with rows and columns, also known as relations.

Degrees of Data Abstraction

• It is a way of classifying data models.
• Many processes begin at high abstraction levels and proceed to increasing detail.
• A usable database design follows this process.
• ANSI/SPARC defined a framework for data modeling based on degrees of data abstraction in the 1970s, starting with

The External Model

• The external model represents the end-users' view of the data environment.
• The modeler divides requirements and constraints into functional modules.
• Advantages include simplified data identification, facilitated designer's job with feedback, ensured security constraints, and simplified application development.

The Conceptual Model

• The conceptual model represents a global view of the entire database.
• It represents data as viewed by the entire organization and provides the basis for identifying main data objects.
• Most widely used is the entity-relationship (ER) model, providing an easily understood macro-level view.
• It is independent of software and hardware and does not rely on DBMS software or hardware implementation.

The Internal Model

• Represents the database as "seen" by the DBMS.
• Maps the conceptual model to the DBMS.
• It depicts a specific representation using constructs supported by the chosen database.

The Physical Model

• Describes how data is saved on storage media such as disks or tapes.
• Operates at the lowest level of abstraction.
• Software and hardware dependent, and requires detailed knowledge of the hardware and software used.