DBMS Unit I — Introduction & Relational Model Flashcards

Front

DBMS

Back

A Database Management System (DBMS) is a general-purpose software system that defines, constructs, manipulates, and shares databases among multiple users and applications. It manages interrelated data and provides programs to access, query, and update that data while ensuring integrity, security, and concurrent access control.

Front

Database

Back

A database is a logically coherent collection of related data that represents some aspect of the real world, often called the miniworld or universe of discourse. It is designed, built, and populated for a specific purpose with an intended group of users and applications.

Front

Universe of Discourse

Back

The universe of discourse (miniworld) is the portion of the real world that a database models and represents. It defines the scope and meaning of the data stored in the database.

Front

File-Processing System

Back

A file-processing system stores permanent records in operating-system files and requires separate application programs to read, add, and update records. It lacks centralized control, leading to duplication, multiple file formats, and difficulty in data access and maintenance.

Front

File System Drawbacks

Back

File systems suffer from data redundancy and inconsistency, difficulty in accessing data, and the need to write new programs for new tasks. They also face isolation, integrity enforcement embedded in application code, and poor concurrent and failure handling.

Front

Data Redundancy

Back

Data redundancy occurs when the same piece of information is duplicated in multiple files or locations, causing wasted storage and potential inconsistencies. Redundancy increases the risk of conflicting or outdated copies of data across the system.

Front

Data Isolation

Back

Data isolation refers to the difficulty of accessing and integrating data stored in different files or formats across multiple applications. It makes combined queries and cross-file consistency checks hard to implement.

Front

Integrity Problems

Back

Integrity problems arise when data validity rules (constraints) are enforced in application code rather than centrally, making them hard to maintain and change. This can lead to inconsistent or invalid data if rules are not uniformly applied.

Front

Atomicity

Back

Atomicity is the property that a group of database operations (a transaction) must either complete fully or have no effect, preventing partial updates. Without atomicity, failures may leave the database in an inconsistent state, for example during fund transfers between accounts.

Front

Concurrent Access

Back

Concurrent access refers to multiple users or processes accessing and updating the database at the same time for performance and availability. Proper concurrency control is needed to avoid anomalies and ensure consistency when simultaneous operations occur.

Front

Security Problems

Back

Security problems in simple file systems stem from lack of centralized control over who can access or modify data, leading to unauthorized access or data breaches. A DBMS provides mechanisms for authentication, authorization, and auditing to mitigate these risks.

Front

Advantages of DBMS

Back

DBMSs provide data independence, efficient data access through indexes and query optimization, central data integrity and security enforcement, and support for concurrent access and crash recovery. They also reduce application development time and help administer data centrally.

Front

Database Applications

Back

Database systems are used across many domains such as banking (transactions), airlines (reservations), universities (registration and grades), retail (orders and recommendations), manufacturing (inventory), and human resources (employee records). They power systems needing reliable storage, querying, and transaction support.

Front

DBMS Architecture

Back

DBMS architecture defines how users and applications interact with the database for reads, writes, and updates, and includes the arrangement of client, application server, and database server. Common architectures include 1-tier, 2-tier, and 3-tier models tailored for performance, scalability, and separation of concerns.

Front

1-Tier Architecture

Back

In a 1-tier architecture the client application, server processes, and database all run on the same system as a single application. This model is simple but lacks separation and scalability for multi-user distributed environments.

Front

2-Tier Architecture

Back

The 2-tier architecture follows a client-server model where the client application directly communicates with the database server using APIs like ODBC or JDBC. The server handles query processing and transaction management while the client handles presentation and some business logic.

Front

3-Tier Architecture

Back

In a 3-tier architecture an application server sits between clients and the database server, so clients do not communicate directly with the database. This separation allows centralized business logic, improved scalability, maintainability, and enhanced security.

Front

Three-Schema Architecture

Back

The three-schema architecture is a three-level abstraction that separates the database system into physical (internal), logical (conceptual), and view (external) levels. This layered design hides implementation details and supports data independence between levels.

Front

Physical Level

Back

The physical (internal) level describes how data are stored on storage media, including file formats, indexing, and physical storage structures. It exposes the internal schema that optimizes storage and access without affecting higher-level schemas.

Front

Logical Level

Back

The logical (conceptual) level describes what data are stored and the relationships among those data for a community of users. The conceptual schema focuses on entities, attributes, relationships, constraints, and operations while hiding physical storage details.

Front

View Level

Back

The view (external) level consists of external schemas or user views that describe parts of the database relevant to particular user groups. Each view hides irrelevant details and presents data in a user-appropriate format.

Front

Schema vs Instance

Back

A database schema is the design and structure of the database specified during design and rarely changed, while a database instance (state) is the data stored in the database at a particular moment. Schemas are metadata describing tables and constraints; instances are current tuples and values.

Front

Data Independence

Back

Data independence is the ability to change the schema at one level of the database system without requiring changes at higher levels. This property is supported by the three-level architecture and separates physical storage concerns from logical design and user views.

Front

Physical Data Independence

Back

Physical data independence is the ability to change how data is physically stored or organized (e.g., indexing or storage format) without affecting the logical schema or application programs. For example, replacing a flat-file with a B-tree index should not force changes in application queries.

Front

Logical Data Independence

Back

Logical data independence is the ability to change the conceptual schema (tables, attributes, relationships) without affecting external schemas or application programs. It is harder to achieve than physical independence but allows reorganizing data structures without breaking user views.

Front

Achieving Independence

Back

Data independence is achieved via the internal, conceptual, and external levels of the three-level architecture, with mappings between adjacent levels. These mappings (metadata) allow changes at one level to be translated and hidden from other levels, preserving application behavior.

Front

Database Languages

Back

Database languages are used to define, manipulate, and control access to data; major categories include Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). SQL (Structured Query Language) is the most common practical language covering these categories.

Front

Data Models

Back

A data model shows the logical structure of a database, including entities, relationships, and constraints, and is often accompanied by a diagram. Common data models include hierarchical, network, relational, entity-relationship, object-oriented, and object-relational models.

Front

Hierarchical Model

Back

The hierarchical data model organizes data in a tree-like structure with parent-child relationships where each child has a single parent. It supports one-to-many relationships, uses pointers to navigate, and can be problematic for deletion and many-to-many relationships.

Front

Network Model

Back

The network data model generalizes the hierarchical model by allowing records to have multiple parents, forming a graph structure. It supports many-to-many relationships and multiple paths to the same record, enabling flexible data connections and faster navigation for some queries.

Front

Relational Model

Back

The relational model represents data as a collection of relations (tables) where each relation has named attributes and tuples as rows. Domains define permissible values for attributes, primary keys uniquely identify tuples, and foreign keys reference primary keys in other tables.

Front

Entity-Relationship Model

Back

The E-R model captures the logical structure of a domain using entities (objects), attributes (properties), and relationships among entities. E-R diagrams graphically represent the schema, showing entity sets, relationship sets, and cardinalities to guide database design.

Front

Object-Oriented Model

Back

The object-oriented model defines a database as a collection of objects with associated attributes and methods, supporting encapsulation, inheritance, and complex data types. It is suitable for multimedia, hypertext, and applications requiring richer data semantics than relational tables alone.

Front

Object-Relational Model

Back

The object-relational model blends relational table structures with object-oriented features, allowing complex types and methods within relational tables. It extends relational systems to support advanced functionality like user-defined types and inheritance while retaining familiar tabular abstractions.

Front

Relation Schema

Back

A relation schema specifies the relation name and a list of attributes with their domains and is denoted as $R(A_1, A_2, \dots, A_n)$. Each attribute $A_i$ is associated with a domain $\mathrm{dom}(A_i)$ that defines the permissible values for that attribute.

Front

Relation Instance

Back

A relation instance (state) $r(R)$ is a finite set of tuples $\{t_1, t_2, \dots, t_m\}$ that conform to the relation schema $R$. Formally, $r(R) \subseteq (\mathrm{dom}(A_1) \times \mathrm{dom}(A_2) \times \dots \times \mathrm{dom}(A_n))$, and its cardinality is the number of tuples it contains.

Front

Degree and Cardinality

Back

The degree (or arity) of a relation is the number of attributes in its schema, while the cardinality of a relation is the number of tuples (rows) it contains. Degree measures column count; cardinality measures row count.

Front

Domain

Back

A domain is the set of permissible values for an attribute and defines its datatype and value constraints (e.g., integer, string, date). Domains help ensure attribute values are valid and interpretable for queries and operations.

Front

Attribute

Back

An attribute is a named property or characteristic of an entity represented as a column in a relation. Each attribute has a domain specifying the allowed values and contributes to identifying and describing tuple data.

Front

Tuple

Back

A tuple is a single row in a relation that represents a record or an instance of an entity, expressed as an ordered list of attribute values. Relation instances are sets of tuples with no duplicate rows.

Front

Table

Back

In the relational model a table is a physical representation of a relation with rows (tuples) and columns (attributes). Tables store data and relationships in a structured, queryable form.

Front

Relation Key

Back

A relation key is one or more attributes that uniquely identify a tuple within a relation; one such key is selected as the primary key. Keys enable efficient lookup and establish links (via foreign keys) between related tables.

Front

Integrity Constraints

Back

Integrity constraints are rules specified on a database schema that restrict allowed data to preserve correctness and consistency. They include key, domain, entity, and referential constraints that prevent invalid, duplicate, or inconsistent data.

Front

Domain Constraint

Back

A domain constraint specifies the valid set of values for an attribute, enforcing data type and permissible value restrictions. It ensures attribute values conform to expected formats like integer, string, date, or currency.

Front

Entity Integrity

Back

Entity integrity requires that the primary key attribute(s) of a relation cannot be NULL, ensuring each tuple can be uniquely identified. Other attributes may be NULL unless restricted by additional constraints.

Front

Referential Integrity

Back

Referential integrity requires that a foreign key value in one table must either be NULL or match an existing primary key value in the referenced table. This constraint preserves consistency of relationships between related tables.

Front

Key Constraints

Back

Key constraints define one or more candidate keys for an entity set that uniquely identify its tuples; one candidate is chosen as the primary key. These constraints prevent duplicate records and aid in establishing relationships across tables.

Front

Constraint Importance

Back

Constraints enforce data accuracy, consistency, and business rules, preventing errors like duplication or invalid values and improving query reliability. They increase user trust in the database and support meaningful reporting and decision-making.