51 cards generated

在牌组消失前保存

这些记忆卡还没保存——离开页面后会消失。创建一个免费账户来保留它们，并解锁下面的所有功能。

保存并复习
  • Save this deck to your account
  • Study with spaced repetition
  • Export to Anki (.apkg) or PDF
更大更强的生成
  • Process documents up to 100 pages
  • Images extracted from your PDFs
  • Sharper text extraction & a more advanced AI model
Sign up free → Free forever · No credit card
DTS302 2 compressed
  • 信息
    点击 'Save & Edit' 以启用全部功能
    学习
    导出
    编辑
    管理
公开

Flashcards in this deck (51)

正在搜索...

  • What is a Relational Database Management System (RDBMS)?

    An RDBMS is software that organizes and manages data based on the relational model, storing data in tables (relations) composed of rows (tuples) and columns (attributes).

    rdbms definition

  • Who formalized the relational model and in what year was the key paper published?

    Dr. Edgar F. Codd formalized the relational model in his 1970 paper 'A Relational Model of Data for Large Shared Data Banks'.

    history codd

  • Before relational systems, which two data models were commonly used?

    Hierarchical (IMS) and network (CODASYL) models.

    history models

  • What is a relation (table) in the relational model?

    A relation is a named two-dimensional structure of rows and columns.

    terminology relation

  • What is a tuple (row)?

    A tuple is a single record, one instance of the entity in a table (e.g., a student record: (1, Alice, 21)).

    terminology tuple

  • What is an attribute (column)?

    An attribute is a named property or field of the relation (e.g., StudentID, Name, Age).

    terminology attribute

  • What is a domain in the relational model?

    A domain is the set of valid values for an attribute (e.g., Age: integers 0-150).

    terminology domain

  • What is a primary key?

    A primary key is one or more attributes that uniquely identify every tuple (e.g., StudentID).

    terminology primarykey

  • What is a foreign key?

    A foreign key is an attribute in one table that references the primary key of another table (e.g., CourseID in Enrollment table).

    terminology foreignkey

  • What is a database schema?

    A schema is the logical structure or blueprint of a database, including database name, table names, and data types.

    terminology schema

  • What is a database instance?

    An instance is the actual data stored in the database at a given time (e.g., current rows in the Students table).

    terminology instance

  • What does NULL represent in a relational database?

    NULL represents the absence of a known value for an attribute (e.g., a student with no phone number on record).

    terminology null

  • What role does relational algebra play in relational databases?

    Relational algebra provides the mathematical foundation for querying relational data; SQL is a concrete, declarative implementation of relational algebra.

    relationalalgebra sql

  • Show a visual excerpt of core terminology for relations, tuples, and attributes.

    Excerpt showing core terms

    • Relation (Table)
    • Tuple (Row)
    • Attribute (Column)
    • Domain
    • Primary Key
    • Foreign Key
    • Schema
    • Instance
    • NULL
    visual terminology

  • What does the relational algebra operation 'Selection (σ)' do?

    Filters rows that satisfy a condition; equivalent to SQL WHERE clause.

    relational_algebra

  • What does the relational algebra operation 'Projection (π)' do?

    Selects specific columns from a relation; equivalent to SQL SELECT.

    relational_algebra

  • What is the result of the Union (U) operation in relational algebra?

    Combines all tuples from two compatible relations.

    relational_algebra

  • What does the Intersection (∩) operation return?

    Tuples present in both relations.

    relational_algebra

  • What does the Difference (-) operation return in relational algebra?

    Tuples in one relation but not the other.

    relational_algebra

  • What is the Cartesian Product (×) operation?

    Pairs every row of one relation with every row of another.

    relational_algebra

  • What is a Join (⋈) in relational algebra?

    Combines tuples from two relations based on a condition; most common in practice.

    relational_algebra

  • What does the Division (÷) operation find in relational algebra?

    Finds tuples in one relation associated with all tuples in another.

    relational_algebra

  • What is the entity integrity rule for primary keys?

    Primary key values must be unique and NOT NULL.

    integrity

  • What is the referential integrity rule for foreign keys?

    Foreign key values must match a primary key in the referenced table or be NULL.

    integrity

  • What does domain integrity require?

    All column values must fall within the defined domain.

    integrity

  • How are business rules enforced as user-defined integrity?

    Via CHECK constraints, triggers, or application logic.

    integrity

  • What is the requirement of First Normal Form (1NF)?

    All attributes must hold atomic (indivisible) values; no repeating groups.

    normalization

  • What is the requirement of Second Normal Form (2NF)?

    Be in 1NF and have all non-key attributes fully dependent on the entire primary key.

    normalization

  • What is the requirement of Third Normal Form (3NF)?

    Be in 2NF and have no non-key attribute depend on another non-key attribute.

    normalization

  • What is the requirement of Boyce-Codd Normal Form (BCNF)?

    A stronger 3NF: every determinant must be a candidate key.

    normalization

  • What is the requirement of Fourth Normal Form (4NF)?

    Be in BCNF and have no multi-valued dependencies.

    normalization

  • What is the requirement of Fifth Normal Form (5NF)?

    Be in 4NF and have no join dependency that is not implied by candidate keys.

    normalization

  • Where can an illustrative excerpt of normal forms be found (image)?

    Illustration of Normal Forms: Normal Form excerpt

    normalization image

  • What does the acronym 'ACID' guarantee for transactions?

    Database reliability even in the presence of errors, power failures, or concurrent access.

    acid transactions

  • What is Atomicity in the context of transactions?

    • A transaction is all-or-nothing.
    • If any part fails, the entire transaction is rolled back.
    atomicity transactions

  • What is Durability for a committed transaction?

    Once a transaction is committed, its changes survive system failures (stored to persistent storage).

    durability transactions

  • What is SQL?

    SQL (Structured Query Language) is the standard declarative language for managing relational databases, standardized as ANSI SQL.

    sql definition

  • What is the purpose of DDL (Data Definition Language) and some key commands?

    • Purpose: Define and modify schema objects
    • Key commands:
    • CREATE
    • ALTER
    • DROP
    • TRUNCATE
    sql ddl

  • What is the purpose of DML (Data Manipulation Language) and its key commands?

    • Purpose: Manipulate data within tables
    • Key commands:
    • SELECT
    • INSERT
    • UPDATE
    • DELETE
    sql dml

  • What is the purpose of DCL (Data Control Language) and its key commands?

    • Purpose: Control access and permissions
    • Key commands:
    • GRANT
    • REVOKE
    sql dcl

  • What is the purpose of TCL (Transaction Control Language) and its key commands?

    • Purpose: Manage transactions
    • Key commands:
    • COMMIT
    • ROLLBACK
    • SAVEPOINT
    sql tcl

  • Where can an illustrative excerpt of the SQL sub-languages table be viewed?

    Illustration: excerpt showing SQL sub-languages (The image supplements the SQL sub-languages and commands.)

    sql image

  • What is PostgreSQL described as?

    PostgreSQL is a powerful, open-source, object-relational database system (ORDBMS).

    postgresql overview

  • Name two qualities PostgreSQL is known for.

    • Standards compliance
    • Extensibility
    postgresql features

  • What is the default network port for PostgreSQL?

    The default port is 5432.

    postgresql configuration

  • List four platforms PostgreSQL runs on.

    • Linux
    • macOS
    • Windows
    • FreeBSD
    postgresql platforms

  • Give three examples of PostgreSQL advanced data types.

    • UUID
    • JSON/JSONB
    • XML
    postgresql datatypes

  • What are PostgreSQL range types used for and give an example?

    Range types store intervals of values; example: int4range.

    postgresql datatypes

  • Which indexing method is PostgreSQL's default and what does it handle?

    Default index: B-Tree; it handles equality and range comparisons.

    postgresql indexing

  • Name a PostgreSQL index type used only for equality comparison.

    The Hash index supports equality comparison.

    postgresql indexing

  • Provide core property facts about PostgreSQL (name, license, initial release, primary language) and include a lecture excerpt image.

    • Full Name: PostgreSQL
    • License: PostgreSQL License (open source, permissive)
    • Initial Release: 1996
    • Primary Language: C

    Lecture excerpt image

    postgresql properties
学习笔记

UNIT 1 — Foundations of Relational Database Systems

1.1 Introduction and history

  • RDBMS: software implementing the relational model—data stored in named tables (relations) of rows (tuples) and columns (attributes).
  • Invented by Dr. Edgar F. Codd (1970); shifted DB design from navigational (hierarchical/network) to declarative access (SQL).
  • Key idea: logical data independence and declarative querying separate logical model from physical storage.

1.2 The relational model — core concepts

  • Relation (table): named 2‑D structure of rows and columns (e.g., Students).
  • Tuple (row): one record/instance (e.g., (1, Alice, 21)).
  • Attribute (column): named field (e.g., StudentID, Name, Age).
  • Domain: permitted values for an attribute (e.g., Age ∈ integers 0–150).
  • Primary key (PK): attribute(s) uniquely identifying tuples; must be NOT NULL.
  • Foreign key (FK): attribute that references a PK in another table (enforces relationships).
  • Schema: logical blueprint of database objects (tables, types, constraints).
  • Instance: actual data at a point in time (current rows).
  • NULL: unknown or missing value; distinct from empty string or zero.

Core relational terms (table, tuple, attribute)

1.2.2 Relational algebra (mathematical operators)

  • Selection (\(\sigma\)): filter rows that satisfy a predicate (SQL: WHERE).
  • Projection (\(\pi\)): choose columns (SQL: SELECT column_list).
  • Union (\(\cup\)): tuples in either relation (compatible schemas required).
  • Intersection (\(\cap\)): tuples common to both relations.
  • Difference (\(-\)): tuples in one relation but not the other.
  • Cartesian product (\(\times\)): pair every row of one relation with every row of another.
  • Join (\(\bowtie\) or \(\Join\)): combine related tuples from two relations on a condition (most-used operation).
  • Division (\(\div\)): find tuples associated with all tuples of another relation.

1.2.3 Integrity constraints

  • Entity integrity: PK values are unique and NOT NULL.
  • Referential integrity: FK values must match a referenced PK or be NULL.
  • Domain integrity: values must be within the declared domain/type.
  • User-defined integrity: business rules via CHECK, triggers, or application logic.

1.3 Normalization (reduce redundancy, avoid anomalies)

  • Purpose: eliminate update/insert/delete anomalies by decomposing tables while preserving data and allowing lossless joins.
  • Functional dependency notation: \(A \rightarrow B\) means A determines B.
  • Summarized normal forms:
  • 1NF: attributes atomic (no repeating groups).
  • 2NF: in 1NF and no partial dependency on part of a composite PK.
  • 3NF: in 2NF and no transitive dependency (non-key → non-key).
  • BCNF: every determinant is a candidate key (stronger than 3NF).
  • 4NF: no multi-valued dependencies.
  • 5NF: no join dependency beyond keys (resolves complex decomposition anomalies).

Normalization summary (1NF–5NF)

1.4 ACID properties (transaction reliability)

  • Atomicity: transaction is all-or-nothing; either fully applied or rolled back.
  • Consistency: transactions move DB from one valid state to another, preserving constraints.
  • Isolation: concurrent transactions behave as if executed serially (avoid seeing intermediate states).
  • Durability: once committed, changes survive crashes (persisted to storage).

1.5 SQL — Structured Query Language

  • Standard declarative language for relational DBs (ANSI SQL).
  • Sub-languages and common commands:
  • DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
  • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
  • DCL (Data Control Language): GRANT, REVOKE.
  • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.
  • SQL expresses relational algebra operations at a higher level (e.g., SELECT … FROM … WHERE … implements projection + selection + join).

UNIT 2 — PostgreSQL

2.1 Overview

  • PostgreSQL (Postgres): open-source, object-relational DBMS; emphasizes standards compliance, extensibility, and robustness.
  • Origins: evolved from Berkeley POSTGRES/INGRES projects; initial public releases in mid-1990s.
  • Key facts: permissive PostgreSQL License, cross-platform, default port 5432, implemented mainly in C.

PostgreSQL properties excerpt

2.2 Key PostgreSQL features

  • Advanced data types: UUID, JSON/JSONB (binary JSON), XML, arrays, composite (row) types, geometric types (point, polygon), range types (int4range, tsrange), full-text types (tsvector, tsquery).
  • Advanced indexing:
  • B-Tree: default; handles equality and range queries.
  • Hash: equality-only (special cases).
  • GIN/GIN and GiST: for full-text, JSONB, and complex types.
  • Extensibility: custom data types, functions, operators, procedural languages (PL/pgSQL, PL/Python, etc.).
  • Strong concurrency: MVCC (multi-version concurrency control) enabling high concurrency with low locking.

Quick study tips

  • Memorize key terms (relation, tuple, attribute, PK, FK, schema, instance, NULL).
  • Understand functional dependencies to decide correct normal form and decompositions.
  • Practice translating SQL queries to relational algebra and vice versa.
  • Experiment with PostgreSQL types and indexes to learn when to use JSONB, arrays, GIN, or GiST.