Terminology & Semantics

 

 

RELATIONAL MODEL

CONCEPTUAL REALM

 

Relation schema

·         logical design of a relation; 

·         consists of relation name, attribute names, and attribute domains

Relation          

·         a subset of all possible combinations of all the values of all the attributes for a specific relation schema

Relation Instance

·         current values of a relation over all the tuples

·         set of tuples that actually exists in a relation at a specific point in time

·         A specific set of tuples

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

o   domain

o   data type

o   particular values allowed (eg, Male, Female)

o   referential integrity

o   value for a given set of attributes in one relations appears in a set of attributes in another relation

o   assertions

o   any condition that the database must always satisfy

o   authorization

o   types of access permitted on valirous data values in the database

RELATIONAL ALGEBRA and OPERATIONS

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

o   ie, a RELATION

·         Every row in the table has the same structure

·         Rows are ORDERED

o   Although order of the rows cannot be assumed

ER Diagrams

CONCEPTUAL REAL

 

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

o   The PK attributes of each of the entity sets that are being related

 

Cross Reference:  Relational Database Semantics

 

Relational Schema

Relational Database

ER Modeling

relational model

DB Schema

ER Diagram

relation schema

table definition

entity set

relation

table

entity set

tuple

row

entity

attribute

column

attribute

domain

data type

domain

predicate

predicate

predicate

relation instance

table instance

extension of entity set

join

join

relationship set

constraints

constraints

constraints