CPS 430 - Database Exam 1

Database Approach vs File Processing

The database approach provides centralized control, data independence, and reduced redundancy; the traditional file approach stores data in separate files for each program, causing redundancy and inconsistency.

Three-Level Database Architecture

Includes the external (view), conceptual (logical), and internal (physical) levels, each with its own schema.

External Level

The user view of the database; shows only relevant parts of data for specific users or applications.

Conceptual Level

Describes the entire logical structure of the database including entities, relationships, and constraints.

Internal Level

The physical storage view of the database; defines how data is stored, indexed, and accessed.

Mapping in Database Architecture

The process of translating requests and data between the three levels of database architecture.

Entities

Real-world objects or concepts represented in a database (e.g., Employee, Department).

Simple Attribute

An attribute that cannot be divided further (e.g., FirstName).

Composite Attribute

An attribute made up of smaller parts (e.g., Name = {FirstName, LastName}).

Multivalued Attribute

An attribute that can have multiple values for one entity (e.g., PhoneNumbers).

Derived Attribute

An attribute calculated from another attribute (e.g., Age derived from DateOfBirth).

Key Attribute

An attribute that uniquely identifies an entity.

Relationships

Associations between entities (e.g., Employee works in Department).

One-to-One Relationship

Each entity in one set is related to at most one entity in another set.

One-to-Many Relationship

One entity in a set is associated with many entities in another (e.g., One department has many employees).

Many-to-Many Relationship

Entities in both sets can be associated with multiple entities in the other set (e.g., Students and Courses).

Structural Constraints

Rules about participation and cardinality in relationships.

Cardinality Constraint

Specifies the number of entity instances that can participate in a relationship (1

Participation Constraint

Specifies whether all or some instances of an entity participate in a relationship.

Weak Entity

An entity that cannot exist without another entity; lacks a unique key.

Existence Dependency

The dependence of a weak entity on a strong entity for its existence.

Identifying Relationship

The relationship that connects a weak entity to its owning strong entity.

Existence Dependency and Referential Integrity

Referential integrity enforces existence dependency by ensuring that foreign keys in weak entities always match valid primary keys in strong entities.

Converting ER Diagram to Relational Schema

Transform entities into tables, attributes into columns, and relationships into foreign keys or new tables for M

Update Commands

SQL commands used to modify data—INSERT, DELETE, and UPDATE.

Entity Integrity

Rule stating that primary key values must be unique and not NULL.

Referential Integrity

Ensures that foreign keys in one table correspond to existing primary keys in another.

Relational Algebra

A formal language of operations used to query relational data.

Relational Algebra Operators

Includes SELECT, PROJECT, UNION, DIFFERENCE, PRODUCT, and JOIN operations.

SQL DDL Commands

Define or alter database structures—CREATE, ALTER, DROP, and integrity constraints.

SQL DML Commands

Used to manipulate data—SELECT, INSERT, UPDATE, DELETE.

SELECT-FROM-WHERE Clause

The core SQL syntax for querying data from one or more tables.

Aliases in SQL

Shortened names for tables or columns used to simplify queries.

DISTINCT Keyword

Removes duplicate rows from a query result.

Subqueries

Nested queries used to filter results; can be correlated (dependent on outer query) or uncorrelated.

NULL in SQL

Represents missing or unknown data; handled with IS NULL or IS NOT NULL.

Aggregate Functions

Functions like SUM, AVG, COUNT, MAX, and MIN used to perform calculations on data.

Grouping in SQL

Organizing data into groups using GROUP BY to apply aggregate functions.

HAVING Clause

Filters groups after aggregation, similar to WHERE but used with GROUP BY.