RELATIONAL MODEL
CONCEPTUAL REALM
|
|
Relation schema
|
Logical design of a relation;
consists of relation name, list of named attributes, and their domains
Corresponds to the concept of a class definition in Java
|
|
Relation
|
The entire set of data that is POSSIBLE for that relation where the values of A,B,C,D,E in each row (tuple) are logically related and conform to the proper domains
Corresponds to the concept of a declaration of a class variable in a Java
|
|
Relation Instance
|
Current set of values for all the tuples in a relation
The set of tuples that actually exists in a relation at a specific point in time
Corresponds to the concept of an object instance in Java
|
|
Tuple
|
UNORDERED set of related attribute VALUES for a particular relation.
Each value is identified solely by the attribute name and never by its position within the tuple
|
|
Attribute
|
Attribute name and domain
UNORDERED
|
|
Domain
|
Permitted set of values
Corresponds to data type and other constraints
|
|
Value
|
A specific valid value conforming to the domain of that attribute
Values must be atomic.
|
|
Atomic
|
Indivisible; NOT composite
|
|
Superkey
|
One or more attributes in a relation such that the values of those attributes are sufficient to uniquely identify the particular tuple in which they appear for each and every tuple in the relation
|
|
Candidate key
|
a minimal superkey;
no attributes appear in the key that do not help to identify the tuple
|
|
Primary key
|
a candidate key chosen to be the ONE primary means of identifying tuples in the relation
|
|
Foreign key
|
primary key of one relation included in another relation
|
|
Referential integrity
|
the value of the FK in the referencing relation MUST appear as a PK value in the referenced relation
|
|
Constraints
|
values stored must satisfy certain limitations
domain
data type
particular values allowed (eg, Male, Female)
referential integrity
value for a given set of attributes in one relations appears in a set of attributes in another relation
assertions
any condition that the database must always satisfy
authorization
types of access permitted on valirous data values in the database
|
RELATIONAL ALGEBRA
PHYSICAL REALM
|
|
Relational Algebra
|
Set of operations that take one or more relations as input and return a relation as an output
|
|
Join
|
Merging of two relations by combining pairs of tuples, one from each relation, into a single tuple
Pairwise combining of the tuples in one relation with the tuples in another relation
Attributes of resulting relations are all the attributes of the first relation appended with all the attributes of the second
|
|
Cartesian /
Cross product
|
Every tuple in r1 is combined with every other tuple in r2
|
|
Natural Join
|
tuples in r1 are combined with tuples in r2 where the values match on all attributes that have the same name
|
|
Outer joins
|
returns ALL tuples from the outer side of the join whether they match with a tuple on the other side or not
|
|
Selection
|
returns tuples of the input relation that satisfy the predicate
|
|
Projection
|
returns specified attributes from all tuples, removing duplicates
|
|
Union
|
all the tuples from r1 appended with all the tuples from r2
|
|
Except / Minus / Difference
|
all tuples in r1 that do NOT appear in r2
|
|
Intersect
|
all tuples in r1 that ALSO appear in r2
|
|
Symmetric difference
|
all tuples in r1 and r2 EXCEPT that tuples that appear on both
|
RELATIONAL DATABASE
PHYSICAL REALM
|
|
Database schema
|
Logical design and structure of the DB, eg, table definitions
|
|
Relational Database
|
Collection of tables
|
|
DB Instance
|
Snapshot of all data in all the tables at a specific point in time
|
|
Table
|
A visual / physical representation of a relation;
Made up of a heading (columns) & body (rows)
Collections of rows (tuples)
|
|
Columns
|
Correspond to attributes in a relation schema
Are ORDERED
May be UNNAMED
May have DUPLICATES
|
|
Rows
|
Each row in a table represents a set of related data values
ie, a RELATION
Every row in the table has the same structure
Rows are ORDERED
Although order of the rows cannot be assumed
|
ER Diagrams
DESIGN REALM
|
|
Entity
|
represents a thing or object in the real world that is distinguishable from all other objects
Uniqueness - every entity in the set must be unique based on the value of its attributes
No duplicates are allowed in the set
Loosely corresponds to the concept of an OO object instance
|
|
Entity set
|
set of entities of the same type that share the same properties
Loosely corresponds to the concept of an OO Class
|
|
Attributes
|
Descriptive properties possessed by each member of an entity set
Each entity has a value for each of its attributes
|
|
Relationship
|
Association among several entities
Deals with particular INSTANCES / entities
|
|
Relationship set
|
A set of relationships of the same type
Each relationship in the set must be unique (no duplicates)
Attributes of a relationship set are
The PK attributes of each of the entity sets that are being related
|