Relational Model

1
Relational Model
2
Outline

• Relational Model
• History
• Concepts
• Constraints

3
Relational Model History

• Introduced by Ted Codd in 1970
• Ted Codd was an IBM Research Fellow
• Laid the foundation for database theory
• Many database concepts products based on this
  model

4
Why is the relational model so popular?

• supported by a mathematical model
• relations (tables) are a good tool to use when
  communicating information to users and developers
• efficient implementations exist for the storing
  of relational information in the form of
  Relational DBMSs (RDBMSs)

5
What is a Relation?

• A Relation is a 2-dimensional table of values
  (rows and columns)
• each row, or tuple, is a collection of related
  facts
• the degree of the relation is the number of
  attributes in the relation
• each column represents an attribute
• each row is an instance of the relation

6
What is a Relation (contd)?

• So, a relation is a big table of facts.
• Each column contains the same attribute data with
  the same data type
• Each row describes a real-world instance of the
  relation
• A Relational database contains one or more
  relations (or tables).

7
Schema vs. Instance

• the name of the relation and the set of
  attributes is called the schema (or the
  intension)
• the current values in the relation represent an
  instance (or extension) of the data

8
Relational Database Model

• Enables us to view data logically rather than
  physically.
• Reminds us of simpler file concept of data
  storage
• Terminologies
• Relational Database
• Relation
• Attribute
• Domain
• Tuple

9
Relational Database Terminology

• Relational Database
• Is a collection of normalized relations with
  distinct relation names.
• Relation
• Is a two-dimensional structure (table) composed
  of rows and columns.
• Relation is also called a table because the
  relational models creator, Codd, used the term
  relation as a synonym for table
• Each cell of a table contains exactly one atomic
  (single) value.
• Table name is distinct from all other table names
  in the database.
• Each table must have an attribute or a
  combination of attributes that uniquely
  identifies each row.

10
Relational Database Terminology

• Tuple
• A record in a relation.
• Each record is distinct there are no duplicate
  records.
• Order of records has no significance,
  theoretically.
• Each table row (tuple) represents a single entity
  occurrence within the entity set.
• Domain
• The set of allowable values for one or more
  attributes.
• Values of a column are all from the same domain.

11
Relational Database Terminology

• Attribute
• A named column of a relation. Each column
  represents an attribute.
• Each column has a distinct name within a table.
• Order of columns has no significance.
• All values in a column must conform to the same
  data format. For example, if the attribute is
  assigned an integer data format, all values in
  the column representing that attribute must be
  integer.
• Each column has a specific range of values known
  as the attribute domain.

12
Alternative Terminology

• Relation, attribute, tuple
• Table, column, record
• File, field, row

13
Summary of the Characteristics of a Relational
Table
14
Example Attribute Domains
15
STUDENT Table Attribute Values
16
Relational Keys 1/3

• Consists of one or more attributes that determine
  other attributes.
• Primary key (PK) is an attribute (or a
  combination of attributes) that uniquely
  identifies any given entity (row)
• Keys role is based on determination
• If you know the value of attribute A, you can
  look up (determine) the value of attribute B
• Composite key is a key consists of more than one
  attribute.
• Key Attribute is any attribute that is part of a
  key.

17
Relational Keys 2/3

• Super key
• A column, or a set of columns, that uniquely
  identifies a record within a table.
• Candidate Key
• A superkey that contains only the minimum number
  of attributes necessary for unique identification
  of each entity occurrence.
• In each record, values of K uniquely identify
  that record (uniqueness).
• No proper subset of K has the uniqueness property
  (irreducibility).

18
Relational Keys 3/3

• Primary Key
• Candidate key selected to identify records
  uniquely within table.
• Alternate Keys
• Candidate keys that are not selected to be
  primary key.
• Foreign Key
• Column, or set of columns, within one table that
  matches candidate key (primary key) of some
  (possibly same) table.
• Secondary key
• Key used strictly for data retrieval purposes.
• Does not necessarily yield a unique number.

19
Keys

• Controlled redundancy (shared common attributes)
  makes the relational database work.
• The primary key of one table appears again as the
  link (foreign key) in another table.
• If the foreign key contains either matching
  values or nulls, the table(s) that make use of
  such a foreign key are said to exhibit
  referential integrity.

20
Keys

• A key helps define entity relationships.
• The keys role is based on a concept known as
  determination, which is used in the definition of
  functional dependence.
• The attribute B is functionally dependent on A if
  A determines B.
• An attribute that is part of a key is known as a
  key attribute.
• A multi-attribute key is known as a composite
  key.
• If the attribute (B) is functionally dependent on
  a composite key (A) but not on any subset of that
  composite key, the attribute (B) is fully
  functionally dependent on (A).

21
Relational Database Keys
22
Integrity Rules Revisited
23
Example on Relational Keys
24
Null Values

• Can be taken to mean unknown, which means that
  the current value for a column at a certain
  record is unknown or not applicable.
• Can represent
• An unknown attribute value
• A known, but missing, attribute value (no data
  entry).
• A not applicable condition
• It is not the same as zero or spaces, which are
  values.
• Not permitted in primary key
• Should be avoided in other attributes

25
Relational Integrity Constraints 1/2

• In addition to that every column has an
  associated domain (domain constraints), there are
  two important relational integrity rules
  (constraints) which ensures that the data is
  accurate Entity Integrity and Referential
  Integrity.
• Entity Integrity
• Is applied to the primary keys.
• In a base table, no column of a primary key can
  be null.
• If null values are allowed, then the key is not
  sufficient to provide unique identification of
  records.

26
Relational Integrity Constraints 2/2

• Referential Integrity
• Is applied to the foreign keys.
• If FK exists in a table, either FK value must
  match a candidate key value of some record in its
  home table or FK value must be wholly null.
• Its not be possible to create a staff record
  with branch number B300, for example, unless
  there is already a record for a branch number
  B300 in the branch table.
• However, we could create a new staff record with
  a null branch number.
• Business Rules
• Its also possible for users to specify
  additional constraints that that the data must
  satisfy.
• Rules that define or constrain some aspect of the
  organization.

27
Example on Integrity Rules
28
Relational Languages

• Two main languages that have emerged for
  relational DBMSs are
• SQL (Structured Query Language), standardized by
  ISO.
• QBE (Query-by-Example), alternative graphical
  point-and-click way of querying database.

29
Data Dictionary System Catalog

• Data dictionary
• Used to provide detailed accounting of all tables
  found within the user/designer-created database
• Contains (at least) all the attribute names and
  characteristics for each table in the system
• Contains metadatadata about data
• Sometimes described as the database designers
  database because it records the design decisions
  about tables and their structures

30
A Sample Data Dictionary
31
Data Dictionary The System Catalog

• System catalog
• Contains metadata
• Detailed system data dictionary that describes
  all objects within the database
• Terms system catalog and data dictionary are
  often used interchangeably
• Can be queried just like any user/designer-created
  table

32
More formally

• A domain D is a set of atomic values
• local phone number The set of 7-digit numbers
• names The set of names of persons
• date of birth Possible dates of birth for
  people
• A relation schema R(A1, A2, , An) is a
• relation name (R)
• list of attributes (A1, A2, , An)
• each attribute Ai is the name of a role played by
  some domain D in the relational schema R

33
More formally (contd)

• a relation r(R) is a subset of
• dom(A1) X dom(A2) X X dom(An)
• each element in a relation, called a tuple, is a
  collection of n values

34
Student (name, address, phone number)
Attribute
Name Address Phone Number
Bob Johnston St. 533-3333
Mary Union St. 533-4444
Fred Clarence St. 533-5555
Tuple
35
Types of Constraints

• Domain constraints
• Key constraints
• Integrity constraints
• Entity Integrity Constraint
• Referential Integrity Constraint
• Semantic Integrity Constraint

36
Domain Constraints

• The value of each attribute, A, must be an
  atomic value from the domain of A
• So, if an attribute is from the domain of a phone
  number, then the attribute must be a phone number.

37
Key constraints

• value of a key uniquely identifies a tuple in a
  relation
• a superkey K is subset of attributes of R such
  that
• no 2 tuples have same values for K
• Every relation has at least one superkey what is
  it?

38
Keys (contd)

• A key is a minimal superkey a superkey from
  which we cannot remove any attributes and still
  be able to uniquely identify tuples in a relation
• common keys ID number, Social Insurance Number,
  etc.

39
Keys (contd)

• A relational schema may have more than one key
• each key called a candidate key
• one designated as the primary key

40
Integrity Constraints

• Integrity constraints are specified on a schema
  and hold for every instance of the schema
• Entity integrity constraint
• no primary key value can be null
• Referential integrity constraint
• if R1 refers to R2 then t1 ? r1(R1) must refer to
  an existing t2 ? r2(R2)

41
Foreign Keys

• a foreign key in R is a set of attributes FK in R
  such that FK is a primary key of some other
  relation R
• a foreign key is used to specify a referential
  integrity constraint
• Example?

42
Key examples

• Department (code, name, phone)
• Faculty (name, number, office, dept_code)
• FK dept_code ? department (code)
• Course (name, number, dept_code)
• FK dept_code ? department (code)

43
Referential Integrity

• A constraint involving two relations (the
  previous constraints involve a single relation).
• Used to specify a relationship among tuples in
  two relations the referencing relation and the
  referenced relation.
• Tuples in the referencing relation R1 have
  attributes FK (called foreign key attributes)
  that reference the primary key attributes PK of
  the referenced relation R2. A tuple t1 in R1 is
  said to reference a tuple t2 in R2 if t1FK
  t2PK.
• A referential integrity constraint can be
  displayed in a relational database schema as a
  directed arc from R1.FK to R2.

44
Referential Integrity Constraint

• Statement of the constraint
• The value in the foreign key column (or columns)
  FK of the the referencing relation R1 can be
  either
• (1) a value of an existing primary key value
  of the corresponding primary key PK in the
  referenced relation R2,, or..
• (2) a null.
• In case (2), the FK in R1 should not be a part of
  its own primary key.

45
Rules for Referential Integrity Constraints
46
Specifying Referential Integrity Actions

• If default referential integrity constraint is
  too strong, overriding the default referential
  integrity enforcement could be defined during
  database design
• The policy will be programmed into triggers
  during implementation
• Two referential integrity overrides
• Cascading updates automatically change the value
  of the foreign key in all related child rows to
  the new value
• Cascading deletions automatically delete all
  related child rows

47
Enforcing Minimum Cardinality

• If the minimum cardinality on the child is one,
  at least one child row must be connected to the
  parent
• A required parent can be specified by making the
  foreign key value not null
• A required child can be represented by creating
  update and delete referential integrity actions
  on the child and insert referential integrity
  actions on the parent
• Such referential integrity actions must be
  declared during database design and trigger codes
  must be written during implementation

48
Representing ID-Dependent Relationships

• To represent ID-dependent relationships, primary
  key of the parent relation is added to the child
  relation
• The new foreign key attribute becomes part of the
  childs composite primary key
• Referential integrity actions should be carefully
  determined
• For cascading updates, data values are updated to
  keep child rows consistent with parent rows
• If the entity represents multi-value attributes,
  cascading deletions are appropriate
• Check user requirements when designing more
  complex situation

49
Example ID-Dependent Relationship
50
Example ID-Dependent Relationship
51
Example Cascading Deletion
52
Semantic Integrity Constraints

• Constraints on data values such as
• The salary of an employee must not exceed that of
  his supervisor.
• The total of available seats must be gt 0 in order
  for a reservation to be made.
• A persons date of birth must be before the
  current date.

53
Update Operations on Relations

• INSERT a tuple.
• DELETE a tuple.
• MODIFY a tuple.
•  
• Integrity constraints should not be violated by
  the update operations.
• Several update operations may have to be grouped
  together.
• Updates may propagate to cause other updates
  automatically. This may be necessary to maintain
  integrity constraints.

54
Update Operations on Relations

• In case of integrity violation, several actions
  can be taken
• Cancel the operation that causes the violation
  (REJECT option)
• Perform the operation but inform the user of the
  violation
• Trigger additional updates so the violation is
  corrected (CASCADE option, SET NULL option)
• Execute a user-specified error-correction routine

55
Example Referential Integrity
Faculty
Department
Code Name Office
ID Name Salary Dept_code
Course
Number Dept_code Title Professor_ID
Enrolled
Student
ID Name Date of Birth
Course Dept_Code Student_ID
56
Insert

• Provide a list of attribute values to be inserted
  (ie. A new tuple)
• Example
• insert values (554433, Bob, 25143.56, ENGL)
  into faculty

57
Insert (contd)

• Inserts may violate constraints.
• Key Constraint
• insert values (554433, Bob, 25143.56, ENGL)
• into employee
• (Will fail if the employee number 554433 is
  already in the table)
• Entity Integrity Constraint
• insert values (NULL, Bob, 25143.56, ENGL)
• into employee
• (primary key cannot be NULL)

58
Insert (cont)

• Referential Integrity Constraint
• insert values (554433, Bob, 25143.56, ENGL)
• into employee
• (Will fail if the ENGL is not a code for a
  department)

59
Delete
Faculty
ID Name Salary Dept
1234 Mary 2345.67 ENGL
2345 Jane 3246.87 HIST
3456 Fred 2876.32 COMP

• delete the faculty tuples with nameFred
• Why is this not a good idea?

60
Delete (cont)

• The only constraint which can be violated is the
  referential integrity constraint (i.e. A tuple in
  another relation references the tuple that is
  slated for deletion).
• delete from Faculty where name Fred
• (referenced by tuples in Course)
• Also, what if there are two people named Fred?

61
Modify

• Change the value for one or more attributes in a
  relation
• Example
• modify SALARY of Faculty where ID 1234 to
  30000
• Modifying a primary key is like deleting a tuple
  and adding a new one. (Same violations may
  apply).