Relational Model
• In the relational model, all data is logically
structured within relations (tables). Each relation
has a name and is made up of named attributes
(columns) of data. Each tuple (row) contains one
value per attribute
Terminology- Relational Model
• Relational Data Structure
– Relation - A relation is a table with columns and
rows.
– Attribute - An attribute is a named column of a
relation.
– Domain- A domain is the set of allowable values
for one or more attributes
– Every attribute in a relation is defined on a domain
Terminology- Relational Model
Terminology- Relational Model
• Degree- The degree of a relation is the number
of attributes it contains.
• Cardinality -The cardinality of a relation is the
number of tuples it contains.
• Relational database- A collection of normalized
relations with distinct relation names.
Terminology- Relational Model
• Alternative terminology
Terminology- Relational Model
• Mathematical Relations
– D and D ,
1 where D = {2, 4} and D ={1, 3, 5}.
2 1 2
– The Cartesian product
D1 × D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}
• Any subset of this Cartesian product is a relation. For example, we could
produce a relation R such that:R = {(2, 1), (4, 1)}
•R = {(x, y) | x ∈D1, y ∈D2, and y = 1}
•S = {(x, y) | x ∈D1, y ∈D2, and x = 2y}
Terminology- Relational Model
• D1 = {1, 3} D2 = {2, 4} D3 = {5, 6}
• D1 × D2 × D3 = {(1, 2, 5), (1, 2, 6), (1, 4, 5), (1,
4, 6), (3, 2, 5), (3, 2, 6), (3, 4, 5), (3, 4, 6)}
• and is usually written as:
Terminology- Relational Model
• Database Relations
– Relation - A named relation defined by a set of
attribute and domain name pairs.
• Let A , A , . . . , A be attributes with domains D ,
1 2 n 1
D , . . . , D . Then the set {A :D , A :D ,. . . , A :D } is a
2 n 1 1 2 2 n n
relation schema
– (A1:d1, A2:d2, . . . , An:dn) such that d1 ∈D1, d2 ∈D2,
. . . , dn ∈Dn
– {(B005, 22 Deer Rd, London, SW1 4EH)}or more
correctly:
– {(branchNo: B005, street: 22 Deer Rd, city: London, postcode:
SW1 4EH)}
Terminology- Relational Model
• Relational database schema-A set of relation schemas,
each with a distinct name.
• Properties of Relations
– the relation has a name that is distinct from all other
relation names in the relational schema;
–
each cell of the relation contains exactly one atomic
(single) value;
– each attribute has a distinct name;
– the values of an attribute are all from the same domain;
– each tuple is distinct; there are no duplicate tuples;
–
the order of attributes has no significance;
– the order of tuples has no significance, theoretically. (
Terminology- Relational Model
• Relational Keys
• Superkey - An attribute, or set of attributes, that
uniquely identifies a tuple within a relation.
• Candidate- A superkey such that no proper subset
is a superkey within the relation.
• A candidate key, K, for a relation R has two
properties:
– uniqueness – in each tuple of R, the values of K
uniquely identify that tuple;
– irreducibility – no proper subset of K has the
uniqueness property.
Terminology- Relational Model
• Primary Key-The candidate key that is selected
to identify tuples uniquely within the relation.
• Foreign Key - An attribute, or set of attributes,
within one relation that matches the key
candidate of some (possibly the same) relation.
Representing Relational Database
Schemas
Integrity Constraints
• Which ensure that the data is accurate.
• integrity rules, which are constraints or restrictions
that apply to all instances of the database.
• Integrity rule
– entity integrity
– referential integrity
Integrity Constraints
• NULL
– Represents a value for an attribute that is currently unknown
or is not applicable for this tuple.
• Entity Integrity
– In a base relation, no attribute of a primary key can be null.
• Referential Integrity
– If a foreign key exists in a relation, either the foreign key
value must match a candidate key value of some tuple in its
home relation or the foreign key value must be wholly null.
Integrity Constraints
• UNIQUE - Ensures that each row for a column
must have a unique value
• PRIMARY KEY - A combination of a NOT NULL
and UNIQUE.
• FOREIGN KEY - Ensure the referential integrity of
the data in one table to match values in another
table
• CHECK - Ensures that the value in a column
meets a specific condition
• DEFAULT - Specifies a default value for a column
• CREATE TABLE Persons
(
P_Id int NOT NULL,
phoneNumber int
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (phonenumber)
PRIMARY KEY (P_Id)
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') or
CHECK (P_Id>0 AND City='Sandnes')
)
Create table dept
{
Deptno int
P_id int
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
}
• ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
• ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
• ALTER TABLE Persons
ADD UNIQUE (P_Id,LastName)
• ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0
AND City='Sandnes')
• ALTER TABLE Persons
DROP CONSTRAINT chk_Person
• CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
• ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES‘
• ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
Views
• Base relation -A named relation corresponding
to an entity in the conceptual schema, whose
tuples are physically stored in the database.
• View - A view is a virtual relation that does not
necessarily exist in the database but can be
produced upon request by a particular user, at
the time of request.
Views
• The view mechanism is desirable for several
reasons
– security mechanism by hiding parts of the
database from certain users
– It permits users to access data in a way that is
customized to their needs
– It can simplify complex operations on the base
relations.
• CREATE VIEW [CL] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued=No
• SELECT * FROM [Current Product List]
• CREATE OR REPLACE VIEW [Current Product
List] AS SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No