Karteikarten
Week 3 - Relational Database Model
Karten in diesem Stapel (18)
- 1Domain- Set of atomic (indivisible) values
- Specify name, data type, data format
- E.g. address domain - 30 character string containing street, town & postcode - 2Relation components1. Relation heading (schema) - consists of a fixed set of attributes, each corresponding to one domain
2. Relation body (instance) - state of the relation at any point in time - 3Relation cardinality and degreeLet r(R) = {t1, t2, ..., tm}, where each tuple is of the form, t =
- Cardinality = m = number of tuples = number of rows
- Degree = n = number of values in tuple = number of attributes - 4Relation properties- No duplicate tuples
- No ordering of tuples within a relation => can only be accessed by content
- No ordering of attributes within a tuple
- Tuple values are atomic
- Tables and relations are not the same (tables allow duplicate rows, and the rows/columns are ordered) - 5Functional dependenceA --> B = "A determines B" = "B is functionally dependent on A"
- A given value of attribute A determines exactly one value of attribute B
- Full functional dependence => attribute is functionally dependent on a composite key but not on any subset of the key - 6Super key- Attribute or combination of attributes that uniquely identifies each entity in table
- Uniqueness property => t1[superkey] ≠ t2[superkey] for any t1, t2
- Many possible superkeys - 7Candidate key (CK)- A minimal superkey => does not contain a subset of attributes that is itself a superkey
- One candidate key is chosen to be the primary key (PK) of a relation
- Remaining candidate keys are termed alternate keys (AK) - 8Selecting a good PK- Non-intelligent => no extra semantic meaning
- No change over time
- Security-compliant
- Preferably numeric
- Preferably a single attribute => if the natural key has too many attributes, may be better to add a surrogate key - 9NULL valuesAbsence of an attribute value
Reasons:
- Value not applicable e.g. commission only - Value unknown
- Value does not exist
- Value undefined e.g. divide by zero - 10Foreign key (FK)- Attribute or combination of attributes that exist in the same relation, or another relation, as a PK
- Must either match the PK in another table or be NULL - 11Data integrity- Entity integrity => PK must be unique and must not be NULL
- Referential integrity - values of FK must either match value of a full PK in the related relation or be NULL
- Column/domain integrity - all values in a given column must come from the same domain (same data type and range) - 12Relational algebra- Relationally complete => closure property => queries on relations produce relations
- Procedural
- Operators apply to at most two relations at a time - 13Relational set operators: SELECT, PROJECTSELECT (σ) - yields subset of rows
PROJECT (π) - yields subset of columns, excluding duplicate values - 14Relational set operators: INTERSECT, UNION, DIFFERENCENB: only for two union-compatible tables
UNION - merges two tables, dropping the duplicate rows
INTERSECT - yields rows common to both tables
DIFFERENCE - yields rows from one table that are not in the other able - 15Relational set operators: PRODUCT, DIVIDE- PRODUCT - yields all possible pairs of rows from two tables (Cartesian product)
- DIVIDE - answers queries about one set of data being associated with all values of data in another set
- 16Relational set operators: natural join- JOIN (⨝) - yields rows from two tables based on common attribute(s)
- Natural join - selects only the rows with common values in their common attribute(s); consists of PRODUCT, SELECT rows with matching attribute values, PROJECT away duplicate columns - 17Relational set operators: equijoin and theta join- Equijoin - joins tables based on an equality condition that compares specified columns; does not remove duplicates
- Theta join - uses a comparison operator (=, <, >, <=, >=) in the join condition - 18Relational set operators: inner vs outer join- Inner join - only rows that meet a given criterion are selected e.g. natural join, equijoin, theta join
- (Full) outer join - matched pairs are retained and any unmatched values are left NULL; e.g. left outer join (⟕) yields all rows in the left table, including those with no matching values in the right table
Informationen
- Beschreibung
- Computer Science (FIT2094: Databases) Karteikarten am Week 3 - Relational Database Model, erstellt von Melina Mackey am 31/10/2020.
- Erstellt von
- Melina Mackey
- Aufrufe
- 0
- Verlauf
-
- Erstellt von Melina Mackey · 19 Okt 2020
- Kopiert von Melina Mackey · 25 Okt 2020
- Kopiert von Melina Mackey · 31 Okt 2020
- Bewertung