Database Systems: ER Model, Design, Backup, and Distributed Databases

Comprehensive practice flashcards covering Entity Relationship modeling, database design principles like normalization and anomalies, backup/recovery strategies, and distributed database concepts including fragmentation and replication.

Entity Relationship Model (ER Model)

A model for identifying entities to be represented in the database and a representation of how those entities are related, specifying the overall logical structure graphically.

Entity

An object, person, place, event, or concept in the real world that can be uniquely identified and stored in a database.

Entity Set

A collection of similar entities that share the same attributes but have different values for those attributes.

Strong Entity

An entity type that has a key attribute and does not depend on another entity in the schema; it is represented by a rectangle.

Weak Entity

An entity type for which key attributes cannot be defined; it is represented by a double rectangle.

Attributes

Properties or characteristics that describe an entity, represented as ovals (ellipses) connected to the entity.

Key Attribute

An attribute which uniquely identifies each entity in the entity set, represented by an oval with underlying lines.

Composite Attribute

An attribute composed of many other attributes, represented by an oval comprising other ovals.

Multivalued Attribute

An attribute consisting of more than one value for a given entity, represented by a double oval (double ellipse).

Derived Attribute

An attribute whose value is calculated or computed from other attributes rather than stored directly; represented by a dashed oval.

Rectangles (ER Notation)

The symbol used to represent Entities in an ER Diagram.

Diamond (ER Notation)

The symbol used to represent Relationships in an ER Diagram.

Enhanced Entity-Relationship (EER) Model

A high-level model that includes all concepts of the ER model plus additional concepts like subclasses, superclasses, categories, and inheritance.

Superclass

A high-level entity (also called a Parent class) that can be further segmented into subclasses or subsets.

Subclass

A low-level entity (also called a child or derived class) that inherits attributes and relationships from its superclass.

Category (Union Type)

A special type of entity formed by combining two or more entity sets that share common characteristics, used when an entity belongs to one or more entity sets.

Attribute and Relationship Inheritance

The mechanism where a subclass automatically acquires the attributes and relationships of its superclass without redefining them.

Minimization of ER Diagram

The process of reducing the quantity of tables in an ER Diagram to increase readability and understandability.

Cardinality

The number of relationships between two entity sets in a relationship model, such as One-to-One, One-to-Many, Many-to-One, or Many-to-Many.

One-to-Many ($1:N$)

A relationship where one instance in entity set A is associated with multiple instances in entity set B, but each instance in B relates to only one in A.

Database Design

A set of procedures or tasks involving steps taken to implement a database while maintaining data consistency, integrity, and low redundancy.

Redundancy

The duplicity of data within a database; design aims to minimize this to prevent inconsistencies.

Schema

A logical container consisting of rows and columns that defines the structure and manages the organization of stored data.

Indexing

A data structure technique used to promote the efficient retrieval of data stored in a database.

Transaction

A single logical unit of work that signifies changes made to the database and must satisfy ACID or BASE properties.

Normalization

A process in database design that identifies anomalies and redundancy to improve efficiency, consistency, and accuracy.

Insertion Anomalies

Occur when data cannot be inserted into a database because required fields are missing or data is incomplete.

Deletion Anomalies

Occur when deleting a record results in the unintentional loss of other data stored in the database.

Updation (Update) Anomalies

Occur when modifying data results in inconsistencies or errors because related records are not simultaneously updated.

First Normal Form ($1NF$)

Ensures that each column contains only atomic values that cannot be divided and each record is unique.

Second Normal Form ($2NF$)

Includes $1NF$ and removes partial dependencies where a non-key attribute depends on only part of a composite primary key.

Third Normal Form ($3NF$)

Includes further normalization by removing transitive dependencies, where non-key attributes depend on other non-key attributes.

Database Backup

A copy of data stored in a database at a specific point in time used to restore the database in case of corruption or failure.

Full Backup

A backup type that copies the entire database including all data, schema, and logs.

Incremental Backup

A backup type that saves only the data that has changed since the last backup (full or incremental).

Differential Backup

A backup type that saves data that has changed since the last full backup.

Mirror Backup

Creates an exact copy of the database in real-time.

Database Recovery

The process of restoring a database to a correct state after data corruption, hardware failure, or accidental deletion.

Point-in-Time Recovery (PITR)

A recovery method that restores the database to a specific moment using transaction logs.

Rolling Backward (Undo)

A recovery process that undoes uncommitted or unwanted changes from the database.

Distributed Database System

A database spread over different sites or multiple computers over a network that appears to users as one single database.

Homogeneous Database

A distributed system where all sites use the same operating system, DBMS, and data structures identically.

Heterogeneous Database

A distributed system where different sites may use different schemas, data models, or DBMS software, often requiring middleware for communication.

Replication

A distributed data storage method where the same data is copied and stored on multiple servers to ensure availability.

Fragmentation

A distributed data storage method where data is split into pieces and stored in different locations.

Horizontal Fragmentation

Splitting a relation by rows so that each tuple is assigned to at least one fragment.

Vertical Fragmentation

Splitting a relation schema by columns, ensuring each fragment contains a common candidate key for lossless joins.

COMMIT

A SQL command used to permanently save the changes made during the current transaction.

ROLLBACK

A SQL command used to undo changes made during a transaction if an error occurs, returning data to its previous state.