Relationship Model Flashcards
Hierarchical Database
1970 IBM Information Management System
1:M relationship
A tree of linked records
child has only one parent (limitation)
Network Database
Intergrated Data Store
Child have multiple parents
Navigational Database
Move around in data via pointers or links
Relational Model
basic structure is the mathematical concept
Relation
Table
Storage
Relation
Abstract object
Heading (fixed)
Body (varies over time)
Table
Pictorial representation
Domain
Set of atomic values which specifies
name, data type and data format
Relation Heading
aka Relation schema
consist of a fixed set of attributes (R)
each attribute (column) corresponds to an underlying domain
Relation cardinality
number of tuples
Relation body
aka Relation instance
Relation instance
State of the relation at any point in time
Consist of tuples (rows)
Relation degree
no of attributes
Tuples in Relation
No duplicate Unique Inordered in a relation Attributes not ordered in a tuple Atomic (cannot be divided) Not multivalued
Functional Dependency
A set of attributes ‘A’ functionally determines an attribute ‘B’ if, and only if,
for each A value, there is exactly one value of B in the relation. It is
denoted as A → B
A determines B
B depends on A
Candidate key
Attribute or set of attributes which exhibits uniqueness at minimum (minimal superkey) - proper subset is not a superkey
Primary Key
One or more candidate key that is chosen to be a primary key (primary key or composite primary key)
Alternate Key
Remaining keys that are not the primary key
Composite key
A primary key comprising of many attributes
How to Select Primary Key
Unique
Nonintelligent
No change over time
Single attribute
Numeric
Security Compliant
Null
no value
Why Null
Value not applicable
Value unknown
Value does not exist
Value undefined
Relational database
A collection of normalised relations
Foreign Key
An attribute or set of attributes in a relation that exists in the same or another relation as a Primary Key
Referential Integrity
A foreign key value must either match the full primary key in a relation or be null
Entity integrity
a condition where each row (entity instance) in the relation has its own unique identity
The primary key must be unique and not be null
Column/Domain integrity
All values in a given column must come from the same domain
Relational algebra
relational
input = relation
output = relation
procedural
operators only apply to at most two relations at a time
8 basic operations of relational algebra
single relation (selection, projection)
cartesian product, join
union
intersection
difference
division
π
pi -> Project
vertical selection
of a relation to create a new relation
only unique tuples are shown in output
σ
sigma -> Select
horizonal selection of a relation
a new relation is then created
selection via content i.e. define data for the corresponding tuple
Union (U)
combines all rows from two relation that are union compatible
excludes duplicate rows
Intersect (∩)
combines rows that appear in two relations
must be union compatible
Difference (-)
rows in one relation that does not appear in another
BUT
Table A-Table B does not equal to Table B-Table A
Product (X)
yields all possible rows from two relations
cartesian product
Cartesian Product
No of rows in relation 1 multiply no of rows in relation 2
every tuple in one relation is paired with every tuple in another relation
Tuple
A table row in relational model
Domain
a set of attributes
Composite primary key
more than one primary key
Key
one or more attributes that determine other attributes
used to establish relationships among relations
determination
Determination
The role of a key
An attributes value can be looked up/determined by another attribute value
Determinant
an attribute whose value determines other values in
Dependent
an attribute whose value is determined by another attribute
Key attribute
an attribute that is part of a primary key
Composite key
a multiple-attribute key
Superkey/Key
an attribute or set of attributes that can uniquely identify any row in a table
Full functional dependency
when an attribute is functionally dependent on an attribute and not any of its proper subset (the determinant is a composite)
Secondary key
for data retrieval i.e. customer wont remember their customer ID but a combination of name and telephone will retrieve customer ID no.
