Headings
1. Relational model terminology.
2. Keys.
3. Constraints(restriction).
4. Relational algebra.
Introduction
• The relational data model was introduced by Ted Codd of IBM Research in 1970.• The model uses the concept of a mathematical relation and set theory and first order predicate logic.
• The first commercial implementations of the relational model became available in the early 1980s by IBM and the Oracle DBMS.
Relational Model Terminology
– The relational model represents the database as a collection of relations.
– Each relation resembles a table of values .
– Basic relational model
• Each row in the table represents a collection of related data values.
• A row represents a fact that typically corresponds to a real-world entity or relationship.
• In the formal relational model terminology, a row is called a tuple, a column header is called an attribute, and the table is called a relation.
• Relations: A Relation is a set of tuples defined on a number of attributes.
• Attribute:- Attributes are like the columns of a conventional table. Each attribute has an attribute name and attribute values.
• Tuples: A tuple can be linked to a row in a conventional table. Each table is a set of attribute value one for each of the relation. Furthermore each tuples has the same number of the attribute values.
• Degree: The degree (or arity) of a relation is the number of attributes n of its relation schema.
• Example:-
• STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa).
• A relation student ’s degree is seven.
• Domain:A domain D is a set of atomic values. By atomic we mean that each value in the domain is indivisible as far as the formal relational model is concerned.
– STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa).
– Example for domain: Name, ssn, Home_phone, Office_phone, Age, Gpa.
Keys
– A set of attributes whose values uniquely identify each tuple of relation called ‘Key ’.
– Basically there are four keys available in the DBMS, which are Primary key, foreign key,
Composite key and Candidate key.
• Primary Keys
Primary key values are used to identify uniquely each tuple with in a table.
The value of primary key attribute must each be unique within the domain of that attribute.
Here Student_number is a primary key.
Note primary key should be underline in the database schema.
• Candidate key
Sometimes a relation schema may have more than one key. In this case, each of the keys is called a candidate key. For example, the CAR relation in Figure has two candidate keys: License_number and Engine_serial_number. It is common to designate one of the candidate keys as the primary key of the relation. This is the candidate key whose values are used to identify tuples in the relation.
– However, it is usually better to choose a primary key with a single attribute or a small number of attributes.
• Composite Key
More than one attribute column is need to establish unique identification for tuples within table, the resulting primary key is referred to as a concatenated primary key.
Example: customer_Id and Order_Id.
• Foreign Key
These are the attribute used to cross reference tuple using the tuples primary key values. Primary key for one table in to which it is embedded for the purpose of identifying relationship occurring.
Constraints
• Meaning of Constraint from Oxford dictionary
– Constraint = Restriction
– A thing that limits or restricts something
– A thing that restricts your freedom to dosomething.
• Another way you should follow some rules and regulation.
• Many restrictions or constraints on the values(data) available in a database.
• Three main constraints categories are:
– implicit constraints
– explicit constraints
– application based constraints
• Implicit constraints:- Constraints/restriction that are Permanent(inherent) in the data model. You can’t Change. Some extent we can say default constraint.
Note: Implicit mean not expressed directly in terms of independent variables.
• Explicit constraints:- Constraints/restriction that can be directly expressed by Data Definition Language(DDL).
– Another word schema-based constraints.
• Application based constraints:-Constraints that cannot be directly expressed in the schema s of the data model, and its enforced by the application programs.
– Another words Semantic constraints or business rules.
Explicit Constraints
• The Explicit/schema-based constraints include
– Domain constraints
– Key constraints
– Constraints on NULLs
– Entity integrity constraints
– Referential integrity constraints.
Domain Constraints
• Domain constraints specify that “within each tuple, the value of each attribute A must be an atomic value from the domain dom(A)”.
Key Constraints
• A key constraint is a statement that a certain fields of a relation is a unique identifier for a tuple.
– Consider the Students relation and the Key
constraint that no two students have the same student id
Specifying Key Constraints in SQL
CREATE TABLE Students
(
sid CHAR(20) ,
name CHAR (30) ,
login CHAR(20) ,
age INTEGER,
gpa REAL,
PRIMARY KEY (sid)
)
Nulls Constraints
• A null can be taken to mean the logical value ‘unknown’.
– A value is not applicable
– No value has yet been supplied
– A null is not the same as a zero numeric value
– Text string not same as spaces
Entity Integrity Constraints
• In a base relation, no attribute of a primary key can be null.
– Primary key value is used to identify individual tuples in a relation
• If we allow a null for any part of a primary key which contradicts the definition of the primary key.
Referential Integrity Constraints
• The referential integrity constraint is specified between two relations
• It is used to maintain the consistency among tuples in the two relations.
• If a foreign key exists in a relation,
– Foreign key value must match a candidate key (Primary key) value of some tuple in its home relation
– or the foreign key value must be null.
Relational Algebra
• Relational algebra and the relational calculus theoretical proposed by E.F. Codd while at IBM in 1971 |
| 04 E.F.Codd |
• Relational algebra is a procedural language.
• Relational algebra, create new relation from one or more relations in the database.
• Relational algebra non-user-friendly language.
• Definition of Relational algebra "The relational algebra is a theoretical language with operations that work on one or more relations to define another relation without changing the original relation(s). "
• The relational algebra is a relation-at-a- time(Temporarily showing) language in which all tuples from several relations.
• All Relational Algebra, manipulated in one statement without looping and get several statement
• Provides formal foundation for relational model operation.
• Basic implementation and optimizing query in the RDBMS.
Relational Algebra’s Operation
• Relational algebra has five fundamental operations
– Selection
– Projection
– Cartesian product
– Union
– Set difference
• Additional operation are
– Join
– Intersection
– Division
• These operations use for data retrieval.
• The SELECT operation is used to choose subset of the tuples from a relation that satisfies a selection condition.
• Syntax
• Selection operation result is also a “Relation” with same ‘attribute’(column name) of Relation R.
• Example,
to select the STUDENT tuples whose follow the Dip IT
• In the staff relation, list all staff with a salary greater than 10,000.
The Selection condition
• The selection condition is an Boolean expression.
• The condition result is only ‘true’ or ‘false’.
• selection condition is made up of a number of clauses.
• <attribute name> <comparison op> <constant value>
• The <attribute name> :- the name of an attribute of R.
• The <comparison op>:- One of the operators {=, <, ≤, >, ≥, ≠}
• <constant value>:- constant value of attribute’s .
• Clauses can be connected by the standard Boolean operators and, or, and not to form a general selection condition.
– σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)
The PROJECT Operation(π)
• The PROJECT operation selects certain columns from the table
• We use the PROJECT operation to project the relation over these attributes only.
• Also Extracting the values of specified attributes and eliminating duplicates.
• Syntax
• a1 – attribute 01
• R - Relation
• Project ‘sid’ and name from ‘Students’ relation.
• Produce a list from "Staff" relation, showing only the staffNo, fName, lName, and salary.
Set Operation
• Set operations
– Union
– Intersection
– Set Differences .
Union( U )
• Denoted by R ∪ S: R and S are relation
• It includes all tuples that are either in R or in S or in both R and S.
• Duplicate tuples are eliminated
Intersection( ∩ )
• Denoted by R ∩ S : R and S are relation
• It includes all tuples that are in both R and S
Set difference( - )
• Denoted by R – S: R and S are relation.
• It defines a relation consisting of the tuples that are in relation R, but not in S.
• R and S must be union-compatible.
Cartesian product (R x S)
• It defines a relation that is the concatenation (Linking) of every tuple of relation R with every tuple of relation S.
• Multiplies two relations to define another relation .
• Get all possible pairs of tuples from the two relations.
Summary
The JOIN Operation(⨝)
• The JOIN operation is used to combine related tuples from two relations into single “longer ” tuples.
• Note:- There are three type of joins are available
– Conditional Join.
– Equi Joint.
– Natural Joint.
