Relational Data Model – Part 1
Schema and State
What is the Relational Data Model?
Introduced by Edgar F. Codd of IBM Research in 1970.
“A Relational Model for Large Shared Data Banks”, Communications of the ACM.
A database contains tables (called relations), and each table is made
up of columns and rows.
Humans have used tables for centuries to keep track of data.
Used as the standard for relational DBMSs (e.g., Oracle, IBM DB2,
Microsofts Access, Microsofts SQL Server, MySQL, postgreSQL, etc.).
Relation
Correspondence of informal and formal terms:
INFORMAL TERMS FORMAL TERMS
Table Relation
Column Attribute
Data type Domain
Row Tuple
Table definition Relation schema
The Basics
Attributes are used to describe the properties of information. In the
relational model, they usually refer to atomic data.
Example: To capture the information of a person, we can use attributes like
Name, Age, Gender, Address and PhoneNumber.
Domains are the sets of all possible values for attributes.
Example:
STRING = {A,B,CD, …};
DATE = {01/01/2005, 03/07/1978, …};
INT = {…,−1, 0, 1, 2, …}.
Recall that, Cartesian product D1 × …× Dn is the set of all possible
combinations of values from the sets D1, …,Dn.
Example: Let D1={book,pen}, D2={1,2} and D3={red}. Then
D1 × D2 × D3 ={(book,1,red),(book,2,red),(pen,1,red),(pen,2,red)}
The Basics
The attributes are StudentID, CourseNo, Semester, Status and EnrolDate.
The domains of attributes are as follows.
dom(StudentID)=INT; dom(CourseNo)=STRING;
dom(Semester)=STRING; dom(Status)=STRING;
dom(EnrolDate)=DATE.
The whole table can be considered as a set {(456, COMP2400, 2016 S2,
active, 25/05/2016), (458, COMP1130, 2016 S1, active, 20/02/2016), (459,
COMP2400, 2016 S2, active, 11/06/2016)}.
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP2400 2016 S2 active 25/05/2016
458 COMP1130 2016 S1 active 20/02/2016
459 COMP2400 2016 S2 active 11/06/2016
Is the above set a subset of
INT× STRING× STRING× STRING× DATE?
Answer: Yes.
The Basics
A relation schema has a relation name and a list of attributes.
Each attribute is associated with a domain.
A relation schema can be expressed by
R(A1, …,An), or
R(A1 : dom(A1), …,An : dom(An)),
where A1, ..,An are attributes of R and dom(Ai) is the domain of Ai .
Example: The relation schema in the previous example is
ENROL(StudentID, CourseNo, Semester, Status, EnrolDate), or
ENROL(StudentID: INT, CourseNo: STRING, Semester: STRING,
Status: STRING, EnrolData: DATE).
The Basics
Let R(A1, …,An) be a relation schema.
A tuple in R is a list t of values, i.e., t ∈ dom(A1)× …× dom(An).
Example: The previous example has the following tuples:
(456, COMP2400, 2016 S2, active, 25/05/2016)∈
INT× STRING× STRING× STRING× DATE.
(458, COMP1130, 2016 S1, active, 20/02/2016)∈
INT× STRING× STRING× STRING× DATE.
(459, COMP2400, 2016 S2, active, 11/06/2016)∈
INT× STRING× STRING× STRING× DATE.
A relation r(R) is a set of tuples r(R) ⊆ dom(A1)× …× dom(An).
Example: The previous example has the following relation:
r(ENROL) ⊆ INT× STRING× STRING× STRING× DATE.
The Basics
A relational database schema S is
a set of relation schemas S = {R1, . . . ,Rm}, and
a set of integrity constraints IC.
A relational database state of S is a set of relations such that
there is just one relation for each relation schema in S, and
all the relations satisfy the integrity constraints IC.
The Basics
Consider a relational database schema STUENROL that has three relation
schemas:
STUDENT(StudentID, Name, DoB, Email).
COURSE(No, Cname, Unit);
ENROL(StudentID, CourseNo, Semester, Status, EnrolDate);
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Semester Status EnrolDate
That is, STUENROL={STUDENT, COURSE, ENROL}.
The Basics
Relational Database State – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
COURSE
No Cname Unit
COMP1130 Introduction to Advanced Computing I 6
COMP2400 Relational Databases 6
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP2400 2016 S2 active 25/05/2016
458 COMP1130 2016 S1 active 20/02/2016
459 COMP2400 2016 S2 active 11/06/2016