Databases and ER Modeling

• A database is a shared collection of logically related data and a description of this data, designed to meet the information needs of an organization
• A Database Management System (DBMS) is system software that enables users to define, create, maintain, and control access to the database
• A database system includes the database, DBMS, and application programs
• The ANSI-SPARC three-level architecture includes the external, conceptual, and internal levels
• The external level represents the users' view of the database
• The conceptual level represents the global view of the database
• The internal level represents the physical storage of the database
• Logical data independence refers to immunity of the external schemas to changes in the conceptual schema
• Physical data independence refers to immunity of the conceptual schema to changes in the internal schema

Entity-Relationship (ER) Model

• ER modeling is a conceptual data modeling technique
• An entity is a real-world object distinguishable from other objects
• Entities have attributes, which are properties that describe the entity
• A relationship is an association between entities
• An entity type is a collection of entities that share common properties
• An entity set is the collection of all entities of a particular entity type in the database
• Attributes are properties of entities i.e., characteristics that describe the entity
• Each attribute has a domain, which is the set of possible values for that attribute
• Simple attributes are atomic and cannot be divided further
• Composite attributes can be divided into smaller subparts
• Single-valued attributes have only one value for an entity
• Multi-valued attributes can have multiple values for an entity
• Derived attributes can be derived from other attributes
• A key attribute is an attribute or a set of attributes that uniquely identifies an entity
• A composite key is a key that consists of more than one attribute
• An entity type's key attribute is shown underlined
• Relationships are associations between entities
• A relationship type is a set of relationships of the same type
• A relationship set is the collection of all relationship instances of a particular relationship type in the database
• The degree of a relationship type is the number of entity types that participate in the relationship
• A binary relationship has a degree of two
• A ternary relationship has a degree of three
• A unary relationship (recursive relationship) is a relationship that has a degree of one
• Cardinality constraints specify the number of instances of one entity that can be related to instances of another entity
• One-to-one cardinality means an entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A
• One-to-many cardinality means an entity in A is associated with any number of entities in B, and an entity in B is associated with at most one entity in A
• Many-to-one cardinality means an entity in A is associated with at most one entity in B, and an entity in B is associated with any number of entities in A
• Many-to-many cardinality means an entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A
• Participation constraints specify whether an entity instance must participate in a relationship
• Total participation (existence dependency) means every entity in the entity set must participate in at least one relationship in the relationship set.
• Partial participation means that some entity instances may not participate in any relationship in the relationship set
• A weak entity is an entity that cannot be uniquely identified by its own attributes alone and depends on another entity (the identifying or owner entity)
• A weak entity has a partial key, which is the set of attributes that uniquely identifies the weak entity when combined with the key of the identifying entity
• Enhanced ER (EER) modeling extends the basic ER model with concepts like specialization, generalization, and categorization
• Specialization is the process of defining subclasses of an entity type.
• Generalization is the process of defining a superclass from a number of entity types
• Specialization and generalization are inverse processes
• A superclass is a general entity type containing common attributes
• A subclass is a specialized entity type inheriting attributes and relationships from the superclass
• Attributes are inherited by subclasses
• Specialization/Generalization can be total or partial
• Total specialization/generalization means every entity in the superclass must be a member of at least one subclass
• Partial specialization/generalization means an entity in the superclass may or may not be a member of any subclass
• Specialization/generalization can be disjoint or overlapping
• Disjoint specialization/generalization means that an entity can be a member of at most one subclass
• Overlapping specialization/generalization means that an entity can be a member of multiple subclasses
• A category (or union type) is a subclass with multiple superclasses representing a collection of objects that is a subset of the union of distinct entity types

Relational Model

• The relational model represents data as a collection of relations
• A relation is a table with columns and rows
• Each table has a unique name
• Each column (attribute) has a unique name within the table
• Each row (tuple) represents a single entity instance
• All values in a column must be of the same data type
• The order of rows and columns is insignificant
• Each row is unique
• A relational schema defines the structure of a relation, including the name of the relation, the attributes, and their data types
• A relational database schema is a set of relation schemas
• A superkey is a set of attributes that uniquely identifies a tuple in a relation
• A candidate key is a minimal superkey (no proper subset is a superkey)
• A primary key is a candidate key chosen to be the unique identifier for the relation
• A foreign key is an attribute (or set of attributes) in one relation that refers to the primary key of another relation
• Foreign keys establish relationships between tables
• Integrity constraints are rules that ensure data quality and consistency
• Domain constraints specify the permissible values for an attribute
• Entity integrity constraint states that no primary key attribute can be null
• Referential integrity constraint states that a foreign key value must either match an existing primary key value in the referenced relation or be null
• Relational algebra is a formal language for manipulating relations
• Selection operation selects a subset of tuples from a relation based on a condition
• Projection operation selects a subset of attributes from a relation
• Union operation combines the tuples of two relations with compatible schemas
• Intersection operation finds the common tuples between two relations
• Difference operation finds the tuples in one relation that are not in another
• Cartesian product operation combines each tuple of one relation with each tuple of another relation
• Join operations combine tuples from two relations based on a related attribute
• Theta join is a join with a general join condition
• Equijoin is a join with equality as the join condition
• Natural join is an equijoin on all common attributes
• Outer join operations preserve tuples that do not have matching values in the join attribute
• Left outer join preserves all tuples from the left relation
• Right outer join preserves all tuples from the right relation
• Full outer join preserves all tuples from both relations

Tuple Relational Calculus (TRC)

• Tuple Relational Calculus (TRC) is a non-procedural query language
• It specifies what to retrieve, not how to retrieve
• TRC uses tuple variables to range over relations
• A TRC query has the form {t | P(t)} which means "the set of all tuples t such that predicate P is true for t"
• Predicate P can contain atomic formulas:
• R(t): means tuple t is in relation R
• t[x] op u[y]: means the value of attribute x in tuple t is related to the value of attribute y in tuple u by operator op
• t[x] op constant: means the value of attribute x in tuple t is related to a constant value by operator op
• op can be any of the comparison operators (=, ≠, <, >, ≤, ≥)
• Existential quantifier (∃) checks if there exists at least one tuple that satisfies a condition
• Universal quantifier (∀) checks if a condition is true for all tuples

Structured Query Language (SQL)

• SQL is a standard language for relational database management systems
• SQL is both a data definition language (DDL) and a data manipulation language (DML)
• DDL statements are used to define the database schema (CREATE, ALTER, DROP)
• DML statements are used to manipulate the data (SELECT, INSERT, UPDATE, DELETE)
• The SELECT statement retrieves data from the database
• The FROM clause specifies the table(s) to retrieve data from
• The WHERE clause filters the rows based on a condition
• The GROUP BY clause groups rows with the same values in specified columns
• The HAVING clause filters groups based on a condition
• The ORDER BY clause sorts the result set
• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) perform calculations on sets of values
• Subqueries are queries nested inside another query
• INSERT statement adds new rows to a table
• UPDATE statement modifies existing rows in a table
• DELETE statement removes rows from a table
• CREATE TABLE statement creates a new table
• ALTER TABLE statement modifies an existing table
• DROP TABLE statement deletes a table
• Data types define the type of data that can be stored in a column
• Common data types include INTEGER, VARCHAR, DATE, etc.
• Constraints enforce data integrity
• Primary key constraint enforces uniqueness and non-null values for the primary key
• Foreign key constraint enforces referential integrity
• Unique constraint ensures that values in a column are unique
• Not null constraint ensures that a column cannot contain null values
• Check constraint specifies a condition that must be true for each row
• Views are virtual tables based on the result of a query
• Indexes improve query performance
• Transactions group a set of operations into a logical unit of work
• ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable transaction processing