CS1122

Relational Databases
Prof. Salim Arfaoui
Spring 2021
 Software Architecture

Presentation tier Logic tier Data tier

Query:
Does Admin 123456 Exist?
Authenticate

Return: True
Databases
• A database is a collection of data in a defined structure.
• How that data is organized determines what type of database it is.
Relational Databases
Edgar Frank Codd, Born August 19, 1923, Portland, UK; A researcher
at I. B.M.; recipient of the 1981 ACM Turing Award, applying his
knowledge of mathematical logic, was able to introduce an
abstract model for database management: principles of relational
databases.

Peter Chen Born January 3, 1947, Taichung City, Taiwan; The

originator of the Entity-Relationship Model (ER Model) which serves
as the foundation and the Meta model for information systems. He
invented the concepts and symbols for the ER Model. His modeling
ides has been widely used to depict the data in a relational
database.
Database – An example
• This diagram is an example of an entity relationship diagram (ERD).
• Depicts how the relational database is structured.
Relational Database structure
 A database is composed of interrelated tables.
 A table is composed of columns and rows
Database management System (DBMS)
The Database Management System (DBMS) is the software package used to:
 Define the database structure.
 Manipulate the data.
 Data retrieval.
 Administration.

End-users or Applications DBMS Database

Popular Database management Systems
Data vs Information
• Data is a raw single unit that contains facts and raw numbers which has not yet been
interpreted or put into context.
• Information is an interpreted collection of useful data.

Data: Movies ID, Movie title, Year, Rating, Length, Budget, etc.

Information: The movie with the highest budget.  The Dark Knight
Average budget for all movies.  $41,556,666.67
The count of movies with ratings above 9  4 movies
 Database Development Life Cycle
• Interviewing both the
• Creating and updating producers and users of data.
database. • Requirement specification.
• Operation and monitoring.
• Modifications.

• Conceptual data modeling.

• Normalization

• Transformation of data model

Into relational tables
Requirement Specification - Example
1. A professor teaches zero, one or many classes and a class
is taught by one professor.
2. A course may generate zero, one or many classes and a
class comes from one course.
3. A class is held in one room but a room has many classes.
ER Model – Logical Data Model
The Entity Relationship Model is a
high-level conceptual data model.
Composed of:
Entities
Attributes
Relationships
Cardinality Symbols
ER Model Ingredients:
Movie-Actor Relationship
Types of relationships

There are three types

of relationships:
- Many to many
- One to many
- One to one
Entity and Attributes
 Entity: A person, business, organization, place, thing or event about
which we want to store data.
 ATTRIBUTE: An attribute is a single unit of information that describes
something about an entity.

Identifier
Entity Name
ACTOR

Actor_ID (PK)
Attributes Actor_FName
Actor_LName
Actor_DOB Entity
Entity Instance
An entity instance is a single occurrence of an entity.

Instances of the entity Actor.

From requirement specification to
Logical Model - ERD

1. A professor teaches zero, one or many classes and a class is taught

by one professor.
2. A course may generate zero, one or many classes and a class
comes from one course.
3. A class is held in one room, but a room has zero or many classes.
Requirement specification to ERD
>>> Entities
1. A professor teaches zero, one or many course-sections and a
course-section is taught by one professor.
2. A course may generate zero, one or many course-sections and a
course-section comes from one course.
3. A course-section is held in one room, but a room has zero or many
course-sections.

Professor course-section Course

Room
Requirement specification to ERD
>>> Relationships
1. A professor teaches zero, one or many course-sections and a
course-section is taught by one professor.
2. A course may generate zero, one or many course-sections and a
course-section comes from one course.
3. A course-section is held in one room, but a room has zero or many
course-sections.
Teaches Generates
Professor course-section Course
Comes from
Taught by
Has
Held in

Room
Requirement specification to ERD
>>> Cardinalities
1. A professor teaches zero, one or many course-sections and a
course-section is taught by one professor.
2. A course may generate zero, one or many course-sections and a
course-section comes from one course.
3. A course-section is held in one room, but a room has zero or many
course-sections.
Teaches Generates
Professor course-section Course
Comes from
Taught by
Has
Held in

Room
Requirement specification to ERD
>>> Attributes and Constraints
Course_Section
Professor
Teaches Course
Generates
CS_ID (PK)
P_ID (PK) P_ID (FK) Co_ID (PK)
P_Fname Taught by Co_ID (FK)
Comes from
Co_Description
P_LName R_Num (FK)

Has
Held in

Room

R_Num (PK)
R_Capacity
Logical to Physical Model
Translates a logical model into physical tables and constraints.

Logical Model Physical Model

Entity Table

Attribute Column or field

Entity instance Row in a table (record)

Relationship A shared column in both related tables

Logical to Physical Model

Professor Professor

P_ID (PK) 12345

P_Fname Eugene
P_LName Callahan
An Instance of the Becomes the record (row)
Entity Professor
entity Professor In Table Professor

P_ID P_FName P_LName

12345 Eugene Callahan
 Logical to Physical Model
Professor Room
Course
P_ID (PK) R_Num (PK)
Co_ID (PK)
P_FName R_Capacity
Co_Description
P_LName

P_ID (PK) P_FName P_LName Co_ID (PK) Co_Description R_Num (PK) R_Capacity

Number(5) Varchar(25) Varchar(25) Varchar(5) Varchar(200) Varchar(4) Number(3)

Course_Section Composite PK

CS_ID CS_ID Co_ID (FK) P_ID (FK) R_Num (FK)

PK
Co_ID (FK) Varchar(4) Varchar(25) Number(5) Varchar(3)
P_ID (FK)
R_Num (FK)
Implementation
• DDL: DATA DEFINITION.
• DML: DATA MANIPULATION.
• DQL: DATA QUERY.
• DCL: DATA CONTROL.
Implementation
• SQLITE3 Database Management System

• Oracle APEX Management System