Database management system |Relational Data Model Concepts

7.1 Relational Data Structure

In relational data model, a database is represented as a collection of relations. A relation can be considered as a table of values. Each row in a table corresponds to a real world entity or a relationship. A relation has;

• A name: to identify the relation in the database uniquely

• An unchanging set of columns: Named and typed to identify each column in the relation uniquely and to specify the type of data that can be stored in that column (this specifies the value domain of the corresponding attribute)

• A set of rows: Changed over the time, which represents the entities

7.2 Relational Model Terminology

There are several standard terms used in relational data models which will be used through out the course, and also whenever you work with databases practically.

Therefore it is of utmost important to know what these terms are, and be able to use the terms appropriately. Let’s take them one by one and see how they go par with the relational concept.

Attribute

− A column of a relation

− Name of the attribute specified the role of the column in the relation

− Bound with a domain which represents the set of values it may take.

Domain

− A set of atomic values which are invisible to the user

− Represents a meaning (Eg: set of Student Index Numbers

− Reveals a format (type) (Eg: a 6 digit integer within the range of 054001 to 054114

Tuple

− A row of a relation

− a set of values which are instances of the attributes of a relation

− represents a real world entity

Relation Schema

− used to describe the relation

Eg: Student(StID, Name, Address, Course)

Relational database schema

− Set of relation schemas together with a set of Integrity Constraints. (Note: We will discuss about the integrity constraints in the next lesson)

Relation

− The set of tuples which accords with the relation Schema (defined on a number of attributes)

Degree of Relation

− The number of attributes in the given relation schema

Cardinality of Relation

− The number of Tuples in the relation

Keys

− A set of attributes whose value uniquely identify each tuple of a relation

Candidate Key

− Any attribute which satisfy the above definition (which is unique for each row)

− There may be many keys for a given relation

Eg: Student(StID, Name, Address, Course, NICNo)

StID and NICNo are candidate keys

Primary key

− One candidate key is chosen to identify the tuples uniquely

− Underlined

Eg: Student(StID, Name, Address, Course, NICNo)

Alternate Keys

− Candidate keys other than the primary key field

Eg: In the student relation, NICNo is an alternate key

Composite Key

− When more than one attribute is required to identify a tuple uniquely, the resulting primary key is referred to as concatenated primary key or a composite

key

Eg: BookCopy(BookID,CopyID, Description…)

Foreign Key

− Considered with relationships among relations

− Attributes used to cross-reference tuples using their primary key values

− Primary key of the referenced table is the foreign key in the referencing table (table in which it is embedded

7.3 Characteristics of a Relation

Though it is not prominently indicated, the relational concept is very much backed up by the set theory, and therefore most of the characteristics can be discussed with related to the set theory. In addition, there will be some other characteristics to consider as well. Let’s take one by one and discuss.

1. Tuples cannot be duplicated

− Tuples are a set. Elements in a set are distinct

− Must be checked for the uniqueness when;

• a new tuple is added
• a value is modified
• a new relation is created as a restriction of an old one

− Primary key should always exists

2. Tuples are unordered

− Tuples are a set. Elements in a set are not ordered

− A table is only a representation of a relation

− How ever, physical storage of a relation is a file of records, therefore must have an order

3. Values within a Tuple are unordered

− At logical level, ordering of the values is not important

− Tuple is an ordered list of values corresponds to attributes in the relational schema. Therefore ordering is important specially at the implementation level

4. All values are atomic

− Multi-valued attributes should be further simplified into atomic valued attributes

5. Unknown values are represented as Null Values in Tuples

− A special value used to represent the values in attributes which are either unknown or not applicable to the tuple. Indicates one of the following

o Value Unknown

o Value exist but not available

o Attribute does not apply to this tuple