デッキが消える前に保存しよう
このフラッシュカードはまだ保存されてないよ — 離れると消えちゃう。無料アカウントを作ると保存できて、下の機能も全部使えるようになるよ。
- 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
What is a Relational Database Management System (RDBMS)?
Who formalized the relational model and in what year was the key paper published?
Before relational systems, which two data models were commonly used?
What is a relation (table) in the relational model?
What is a tuple (row)?
What is an attribute (column)?
What is a domain in the relational model?
What is a primary key?
What is a foreign key?
What is a database schema?
What is a database instance?
What does NULL represent in a relational database?
What role does relational algebra play in relational databases?
Show a visual excerpt of core terminology for relations, tuples, and attributes.
What does the relational algebra operation 'Selection (σ)' do?
What does the relational algebra operation 'Projection (π)' do?
What is the result of the Union (U) operation in relational algebra?
What does the Intersection (∩) operation return?
What does the Difference (-) operation return in relational algebra?
What is the Cartesian Product (×) operation?
What is a Join (⋈) in relational algebra?
What does the Division (÷) operation find in relational algebra?
What is the entity integrity rule for primary keys?
What is the referential integrity rule for foreign keys?
What does domain integrity require?
How are business rules enforced as user-defined integrity?
What is the requirement of First Normal Form (1NF)?
What is the requirement of Second Normal Form (2NF)?
What is the requirement of Third Normal Form (3NF)?
What is the requirement of Boyce-Codd Normal Form (BCNF)?
What is the requirement of Fourth Normal Form (4NF)?
What is the requirement of Fifth Normal Form (5NF)?
Where can an illustrative excerpt of normal forms be found (image)?
What does the acronym 'ACID' guarantee for transactions?
What is Atomicity in the context of transactions?
What is Durability for a committed transaction?
What is SQL?
What is the purpose of DDL (Data Definition Language) and some key commands?
What is the purpose of DML (Data Manipulation Language) and its key commands?
What is the purpose of DCL (Data Control Language) and its key commands?
What is the purpose of TCL (Transaction Control Language) and its key commands?
Where can an illustrative excerpt of the SQL sub-languages table be viewed?
What is PostgreSQL described as?
Name two qualities PostgreSQL is known for.
What is the default network port for PostgreSQL?
List four platforms PostgreSQL runs on.
Give three examples of PostgreSQL advanced data types.
What are PostgreSQL range types used for and give an example?
Which indexing method is PostgreSQL's default and what does it handle?
Name a PostgreSQL index type used only for equality comparison.
Provide core property facts about PostgreSQL (name, license, initial release, primary language) and include a lecture excerpt image.
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.
- 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:
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:
(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
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.
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).
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.
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.