1
9/3/20
Practical Database Concepts
Lecture 2: Relational Database Model Santha Sumanasekara
March 2020
Overview of the lecture
– Why Relational Database Model?
– Basic elements of the relational database model – Concepts
– Peek into a sample database – Integrity Constraints
– Concepts
– Implement them in SQL
2
1
3
9/3/20
Why Relational Database Model?
– Outrageously simple!
– But, extremely powerful
– Address almost all the issues raised in the last lecture
– Schema – DML and DDL
– Integrity Constraints
– Serialisability/ Concurrency/ Transaction Management
– Security
– Efficiency
– So, what is the relational database model?
So, what is the Relational Database Model?
– Wikipedia explains it as “The relational model for database management is an approach to managing data using a structure and language consistent with first-order predicate logic, first described in 1969 by Edgar F. Codd, where all data is represented in terms of tuples, grouped into relations. “
– You said simple. Doesn’t sound like that.
– Listen to Chapter 1 of our lynda.com tutorial.
4
2
5
9/3/20
So, what is the Relational Database Model?
So, why is it NOT called Tabular Database Model?
– Relations or Tables?
– These two terms are used incorrectly very frequently.
– Understand their correct usage before moving any further!
– Every relation is a table
– Every table cannot be a relation!
6
3
7
9/3/20
What is a relation?
– The term “Table” is used loosely when data are structured into rows
and columns.
– The term “Relation” is used for a data table with few additional characteristics
– Every cell contains a single value
– Columns store a single type of information
– Column names are unique
– Order of rows and columns is insignificant
– Rows are unique
Some Terminology
Table
Relation
table header
relation schema
column
attribute
row
tuple
cell
tuple component
8
4
9
9/3/20
Some Terminology
– The name of a relation and the set of attributes for the relation is
called the schema for the relation. For example, Student (Name, Student_no, Age, GPA)
– Each attribute has a domain — the set of all possible values allowed for the attribute.
– The rows of a relation are tuples comprising components that are atomic — a component can not be broken into smaller components.
– Each component of a tuple is a value from the corresponding attribute domain.
An instance of Student Relation
10
5
11
9/3/20
An instance of Student Relation
An instance of Student Relation
12
6
13
9/3/20
Keys of a Relation
Keys of a Relation
– You are familiar with the concept of “Primary Key”.
– no two students can have the same student number;
– Every course has a unique course code;
– Primary key is unique.
– What are the other keys in the context of relational database model?
– Superkey
– Minimal Superkey
– Candidate Key
– Foreign Key
14
7
15
9/3/20
–
– An attribute, or set of attributes, that uniquely identifies a tuple within a relation.
– E.g.
– Candidate Key is a minimal superkey, i.e. all attributes are essential to make it unique
– The above examples are not minimal superkeys
–
–
– What other candidate key here?
Keys of a Relation – Superkey
Keys of a Relation – Another example
– In the above Enrol relation, what are the candidate keys?
–
– In Student relation, it was a minimal superkey (candidate key), but not here. It does not uniquely identify tuples (uniqueness).
–
– No. Still does not uniquely identify tuples
–
–
16
8
17
9/3/20
Keys of a Relation – Primary Key
– “A” Candidate Key selected to identify tuples uniquely within relation.
– Alternate Keys
– Candidate keys that are not selected to be the primary key.
– Foreign Key
– Attribute, or set of attributes, within one relation that matches a candidate key of some (other) relation.
Integrity Constraints
To protect the database from becoming incomplete, inaccurate or inconsistent.
– Key Constraint
– Entity Integrity Constraint
– Referential Integrity Constraint
– Applications can enforce more constraints, here we only focus on constraints that can be enforced by the relational database model.
18
9
19
9/3/20
Key Constraint
– An attribute or list of attributes are a Candidate Key of a relation if no
two tuples of the relation may agree in all the attribute(s) on the list.
– Key Constraint enforces the above requirement.
– Consider the following example
Student (Name, Student_No, email, Age, GPA)
– We agreed (based on semantics of the data)
– So, Key Constraint dictates us that two tuples cannot have the same Student_no values.
– Also, two tuples cannot have the same email values.
– Let’s see how this is done in practice.
Key Constraint
then
and
We have violated the key constraint
CREATE TABLE student (
name VARCHAR(50), student_no VARCHAR(10), email VARCHAR(50), age DECIMAL(3), gpa DECIMAL(4,2)
);
INSERT INTO student VALUES (‘Benjamin’, ‘3056124’, ‘ben@a.com’, 30, 3.75);
INSERT INTO student VALUES (‘Benjamin’, ‘3056124’, ‘ben@a.com’, 30, 3.75);
20
10
21
9/3/20
Key Constraint
CREATE TABLE student
(
name VARCHAR(50), student_no VARCHAR(10), email VARCHAR(50), age DECIMAL(3),
gpa DECIMAL(4,2) gpa DECIMAL(4,2),
);
PRIMARY KEY (student_no)
);
Entity Integrity Constraint
– The entity integrity constraint states that primary key attributes can’t
be null.
– There must be a proper value in the primary key attribute(s).
– This is because the primary key is used to uniquely identify individual tuples in a relation.
– Consider the following example
Student (Name, Student_No, email, Age, GPA)
– Let’s choose Student_no as the primary key.
– Can there be a student tuple in the relation without a student number? Say a brand new student with no student number assigned yet?
– This constraint dictates we cannot do such an insertion.
22
11
23
9/3/20
Entity Integrity Constraint
then
We have violated the entity integrity constraint
CREATE TABLE student (
name VARCHAR(50), student_no VARCHAR(10), email VARCHAR(50), age DECIMAL(3), gpa DECIMAL(4,2), PRIMARY KEY (student_no)
);
Note that student_ no is absent here
INSERT INTO student (name, email, age, gpa) VALUES (‘Benjamin’, ‘ben@a.com’, 30, 3.75);
Entity Integrity Constraint
CREATE TABLE student (
name VARCHAR(50), student_no VARCHAR(10) NOT NULL, email VARCHAR(50),
age DECIMAL(3),
gpa DECIMAL(4,2),
PRIMARY KEY (student_no)
);
then
Will result in:
Note that student_no is absent here
INSERT INTO student (name, email, age, gpa) VALUES (‘Benjamin’, ‘ben@a.com’, 30, 3.75);
24
12
9/3/20
Referential Integrity Constraint
– A referential integrity constraint is defined as part of an association
between two relations.
– For referential integrity to hold in a relational database, a foreign key can contain either a null value, or only values from parent relation’s candidate key.
Student (Name, Student_No, email, Age, GPA)
Enrol (Student_no, Course_Code, Semester, Result)
– In this situation, any student number entered into Enrol relation must be an existing value in Student relation. Or, it can be a NULL value (if that doesn’t violate any other constraints).
25
Referential Integrity Constraint
Nothing to refer to
26
13
9/3/20
Referential Integrity Constraint
then
CREATE TABLE student (
name VARCHAR(50), student_no VARCHAR(10) email VARCHAR(50), age DECIMAL(3), gpa DECIMAL(4,2), PRIMARY KEY (student_no)
);
NOT NULL,
Student No in enrol refers to student No in student
course_code, semester),
CREATE TABLE enrol ( student_no varchar(10), course_code varchar(10), semester varchar(10),
result decimal(3),
PRIMARY KEY (student_no, FOREIGN KEY (student_no)
);
REFERENCES student(student_no)
27
Referential Integrity Constraint
insert into enrol
values(’3232312′,’COSC1285′,’Sem 1 17′,80)
Then will return an error message:
[14:00:51] Error while executing SQL query on database ‘Movies’: FOREIGN KEY constraint failed
This Student No does not exist in Student relation
28
14
9/3/20
Next week….
– We explore conceptual database design using Entity Relationship
Model.
– Many different notations – we use UML notation this semester
– Readings: Chapter 4: Relational Database Fundamentals lynda.com tutorial by Adam Wilbert
– Chapter 4: Relational Database Design and Implementation, 4th Edition By: Jan L. Harrington (This book is available on Safari Books for free)
– Get familiarised with LucidChart (cloud-based diagramming tool – – https://www.lucidchart.com)
29
15