Database Systems!

Fundamentals of Databases

Dr. Seema Jehan
 Outline!
•  Relational Database model’s logical structure
•  ANSI/SPARC Architecture
 Relational Data Model!
•  Data Model
•  Data Structures
•  Constraints
•  Operations
•  Keys and Identifiers
•  Integrity and Consistency
•  Null Values
•  Surrogates
 The Relational Model!
•  The logical view of the relational database is based on a logical
construct called a relation.

•  A relation or table is perceived as a two - dimensional structure

composed of rows and columns.

•  For example, a STUDENT table consists of a collection of student

instances.

•  A data model consists of three aspects:

•  Data Structures
•  Integrity Constraints
•  Operations
 Relational Models- Data
Structures!
•  Each table column represents an attribute, and each
column has a distinct name.

•  Each table row represents a single entity occurrence

•  Each intersection of a row and column represents a single
data value.

•  The order of rows and columns is immaterial to DBMS

Example: A relational table!
•  Schema: table name, column name, datatypes constitute
the schema.

•  Schema represents the aspects of a table that are stable

over time.

•  Rows represent the state of reality that can change over

time.
Student

CMS_ID LAST_NAME FIRST_NAME DOB EMAIL ADDRESS HOMETOWN GPA

INTEGER Varchar(50) Varchar(50) DATETIME Varchar(50) Varchar(100) Varchar(50) INTEGER

aali.bscs18seecs@se
100 ALI AYESHA 2002-10-2 H.N34, ST,63,I-10/3, ISLAMABAD LAHORE 3.54
ecs.edu.pk

iahmad.bscs18seecs H.No43, ST. 5, SEC J, DHA-Phase1,

101 AHMAD IFRAH 2001-11-22 KARACHI 3.14
@seecs.edu.pk ISLAMABAD

eomer.bese18seecs H.23, ST.37,SEC J, Phase-2, Bahria town

102 OMER EISHA 2000-09-10 RAWALPINDI 2.83
@seecs.edu.pk - phase 2, ISLAMABAD
Relational Model- Constraints!
•  Constraints represent rules that can not be expressed by
the data structures alone.

•  Example:
•  Email must be unique
•  Email’s are not allowed to be NULL.
•  GPA must be less than 4.0
•  DOB must be greater than 1900-01-01
Relational Model- Operations!
•  Operations support change and retrieval of data
select Email, DOB
from Student
Where HOMETOWN=‘Lahore’ and GPA>3.0

DOB
CMS_ID LAST_NAME FIRST_NAME EMAIL ADDRESS HOMETOWN GPA
DATE
INTEGER Varchar(50) Varchar(50) Varchar(50) Varchar(100) Varchar(50) INTEGER
TIME

aali.bscs18see
2002-
100 ALI AYESHA cs@seecs.edu. H.N34, ST,63,I-10/3, ISLAMABAD LAHORE 3.54
10-2
pk

iahmad.bscs18
2001- H.No43, ST. 5, SEC J, DHA-
101 AHMAD IFRAH seecs@seecs.e KARACHI 3.14
11-22 Phase1, ISLAMABAD
du.pk

eomer.bese18
2000- H.23, ST.37,SEC J, Phase-2, Bahria
102 OMER EISHA seecs@seecs.e RAWALPINDI 2.83
09-10 town - phase 2, ISLAMABAD
du.pk
 Relational Model- Keys and
Identifiers!
•  Keys are uniqueness constraints
•  Making Email the primary key in Student will force all
Emails to be unique in Student.
Student

EMAIL ADDRESS HOMETOWN GPA

Varchar(50) Varchar(100) Varchar(50) INTEGER

aali.bscs18seecs@seecs.edu.
H.N34, ST,63,I-10/3, ISLAMABAD LAHORE 3.54
pk

iahmad.bscs18seecs@seecs.
H.No43, ST. 5, SEC J, DHA-Phase1, ISLAMABAD KARACHI 3.14
edu.pk

eomer.bese18seecs@seecs.e H.23, ST.37,SEC J, Phase-2, Bahria town - phase 2,

LAHORE 2.83
du.pk ISLAMABAD
 Integrity and Consistency!
•  Integrity: Does the Database reflect reality well?
•  Consistency: Is the database without internal conflicts?

Regular Student
Student

EMAIL DOB LAST_NAME FIRST_NAME CURRCITY EMAIL ADDRESS

Varchar(50) DATETIME Varchar(50) Varchar(50) Varchar(50) Varchar(50) Varchar(100)

stud.bscs18seecs
2002-10-2 ALI AYESHA LAHORE stud.bscs18seecs@seecs.edu.pk H.N34, ST,63,I-10/3, ISLAMABAD
@seecs.edu.pk

iahmad.bscs18see H.No43, ST. 5, SEC J, DHA-Phase1,

2001-11-22 AHMAD AKHTAR KARACHI aahmad.bscs18seecs@seecs.edu.pk
cs@seecs.edu.pk ISLAMABAD

eomer.bese18see H.23, ST.37,SEC J, Phase-2, Bahria

2000-09-10 OMER EISHA RAWALPINDI eomer.bese18seecs@seecs.edu.pk
cs@seecs.edu.pk town - phase 2, ISLAMABAD
Exercise!
 NULL VALUES!
•  A null is no value at all; it does not mean zero or no value at all
•  Null values are problematic in the relational model.
•  A null is the absence of any data value; null values can never
become part of Primary key

•  A null value can be one of the following:

•  An unknown attribute value
•  A known but missing attribute value
•  A “not applicable” condition
 NULL VALUES!
EMAIL DOB HOMETOWN FIRST_NAME LAST_NAME GENDER ACM-W
Varchar(50) DATE Varchar(50) Varchar(50) Varchar(50) Varchar(50) Varchar(50)

stud.bscs18seecs
2002-10-2 LAHORE AYESHA ALI F YES
@seecs.edu.pk

iahmad.bscs18see
2001-11-22 KARACHI IFRAH AHMAD F UNKNOWN
cs@seecs.edu.pk

eomer.bese18seec
2000-09-10 LAHORE EISHA OMER F NO
s@seecs.edu.pk

zali.bese18seecs@
2001-09-10 ISLAMABAD ZOHAIB ALI M Inapplicable
seecs.edu.pk
Surrogates-Things and Names!
Employee
Employee

Name! Address!
Email Name Address

Email!

Name-based Representation

Employee

Name! Address! EmpID Email Name Address

Email!
Surrogate-based Representation
Exercise!
 ANSI/SPARC 3-level DB
Architecture!
•  A database is divided into schema and data
•  This separation allows data access more efficient and
effective.

•  We can query the data based on the schema.

Database System!
Database System! !
! !
! Schema!!
Database!
! !
!
!
Data! !
 ANSI/SPARC 3-level DB
Architecture!
•  If database only consisted of schema and data then
schema would have to describe all aspects of what the
meaning of the data is, how is it used, and how it is
internally represented.

•  What would be the implication?

Internal
Schema!
Schema!

Data!
 ANSI/SPARC 3-level DB
Architecture!
•  Likewise, for each application running on the schema we
can create an external schema.

External Conceptual Internal

Schema! Schema! Schema!

Data!
 ANSI/SPARC 3-level DB
Architecture!
External External External
Schema! Schema! Schema!

Conceptual
Schema!

Internal
Schema!

Database!
 Conceptual Schema!
•  Describes all conceptually relevant, general, time-
invariant structural aspects of reality

•  Excludes aspects of data representation and physical

organisation and access.

•  Applications can only view these structures.

Student

EMAIL DOB CITY NAME GENDER GPA

select EMAIL, NAME, CITY

From Student
Where GENDER=‘F’ and GPA>3.0;
 External Schema!
•  Describes parts of the information in the conceptual
schema in a form that is convenient to a particular user
group’s view.
create view HighAchieversFemales as
select EMAIL, NAME, CITY
From Student
Where GENDER=‘F’ and GPA>3.0
OrderBY FNAME;

EMAIL NAME CITY

EMAIL DOB CITY NAME GENDER GPA

 Internal Schema!
•  Describes how the information described in the
conceptual schema is physically represented to provide
the best performance.
STUDENT

EMAIL DOB CITY NAME GENDER GPA

STUDENT
EMAIL DOB CITY NAME GENDER GPA

STUDENT sorted on NAME

Physical Data Independence!
•  Physical data independence is a measure of how much the
internal schema can change without affecting the application
program.
External External External
Schema! Schema! Schema!

Conceptual
Schema!

Internal
Schema!

Database!
Logical Data Independence!
•  Logical data independence is a measure of how much the
conceptual schema can change without affecting the
application program.
External External External
Schema! Schema! Schema!

Conceptual
Schema!

Internal
Schema!

Database!
ANSI/SPARC DBMS ARCHITECTURE!
Exercise!
 Summary!
•  Major aspects of a relational data model are data
structures,integrity constrains and operations.

•  The schema is a description of a database that does not

change very often whereas the database state changes every
time data is inserted, deleted or updated.

•  The three level schema DBMS architecture consists of an

internal schema, conceptual schema and an external schema.

•  DDL language is used to define the conceptual schema

•  DML is used for specifying database retrievals and updates.

 References!
•  Chapter 2, Fundamentals of Database systems, Ramez
Elmasri and Shamkant Navathe, 7th Edition, 2016
•  Udacity course on “Fundamentals of Databases” by
Georgia Tech:
•  https://classroom.udacity.com/courses/ud150/