Database Systems: ER Diagrams, Modeling, and SQL Fundamentals

Comprehensive vocabulary flashcards covering ER diagram components, attribute classifications, relationship cardinalities, generalization/specialization, constraints, data classifications, DBMS functionality, and SQL statement types.

Entity

A real-world object, either animate or inanimate, that can be easily identified (e.g., Teachers, Students, Classes, or Courses offered in a School Database).

Attribute

A property used to represent an entity, where every attribute has an associated value (e.g., Name, Class, and Age for a Student).

Simple Attribute

An attribute that consists of an atomic value that cannot be divided further (e.g., a student's phone number as an atomic value of 10 digits).

Composite Attribute

An attribute that can be divided into smaller sub-parts (e.g., a student's complete name divided into first_name and last_name).

Derived Attribute

An attribute that is not stored directly in the database but can be computed from another attribute (e.g., age derived from data_of_birth, or average_salary in a department).

Derived Attribute (ER Notation)

An attribute represented in an ER diagram by a dashed ellipse or oval, indicating its value is calculated from another attribute (such as Age derived from Birth_Date).

Single-Value Attribute

An attribute that contains only one single value for an entity instance (e.g., Social_Security_Number).

Multi-Value Attribute

An attribute that may contain more than one value for a single entity (e.g., multiple phone numbers or email addresses for a person).

Multivalued Attribute (ER Notation)

An attribute represented in an ER diagram by a double ellipse or double oval (such as Phone_No for a Student).

Relationship

An association among entities (e.g., Employee works_at department, or Student enrolls in a course).

Relationship Set

A set of relationships of similar type, which can also possess its own attributes.

Descriptive Attributes

Attributes that belong directly to a relationship rather than to any of the participating entities.

Binary Relationship

A relationship in which exactly two entities participate.

Mapping Cardinalities (Cardinality)

The number of instances of an entity in one entity set that can be associated with entities of another set via a relationship set.

One to One ($1:1$)

A relationship cardinality where an entity in set A is associated with at most one entity in set B, and an entity in set B is associated with at most one entity in set A (e.g., Person - Passport, Student - Roll-No).

One to Many ($1:N$)

A relationship cardinality where an entity in set A can be associated with many entities in set B, but an entity in set B is associated with at most one entity in set A (e.g., Mother - Children, Student - Address).

Many to One ($N:1$)

A relationship cardinality where multiple entities in set A are associated with at most one entity in set B (e.g., Students - Teacher).

Many to Many ($M:N$)

A relationship cardinality where an entity in set A can be associated with multiple entities in set B, and vice versa (e.g., Books - Authors).

Generalization

A conceptual hierarchical process where going up in the structure generalizes the view of entities by combining lower-level entities into a broader category (e.g., Sparrow, Pigeon, and Peacock generalized into Birds).

Specialization

The reverse of generalization, where moving down into the hierarchy provides detailed, specific entity types from a generalized entity.

Inheritance (ER Model)

A hierarchical ER structure where specialized entities (such as Student and Teacher) acquire the attributes of a higher-level entity (such as Person) via an 'IS A' relationship.

Key Constraints

Constraints dictating that in a relation with a key attribute, no two tuples can have identical values for key attributes, and a key attribute cannot have NULL values.

Domain Constraints

Rules specifying that every attribute is bound to have a specific range of values (e.g., Age cannot be negative).

Mapping Entity to Relational Model

The process of creating a table for each entity, turning its attributes into fields with respective data types, and declaring a primary key.

Data

A collection of facts (numbers, words, measurements, observations, etc.) translated into a form that computers can process.

Structured Data

Information stored in a database that follows a strict format.

Semi-Structured Data

Data that may have a certain structure, but not all collected information has an identical format, and some attributes may exist in certain entities but not in others.

Unstructured Data

Data with very limited indication of data type, such as a simple text document.

Database

A persistent, organized set or collection of information held in a computer, arranged for speed and ease of search, update, and retrieval.

Database Management System (DBMS)

The software that controls, stores, updates, retrieves, organizes, and protects database information (e.g., Oracle, DB2, MS SQL Server, MS Access, Ingres, PostgreSQL, MySQL, MongoDB).

Data Definition Language (DDL)

The class of SQL statements used to define the database structure, including CREATE, DROP, and ALTER commands.

Data Manipulation Language (DML)

The class of SQL statements used to manipulate data stored in a database, including INSERT, UPDATE, and DELETE commands.

Data Control Language (DCL)

The class of SQL statements dealing with rights, permissions, and other access controls of the database system, including GRANT and REVOKE commands.

Data Dictionary

A component provided by the DBMS that stores metadata describing the database itself.