Lecture-2: Relational Database Modeling & Design

Course CSB2204: Database Systems

Assoc. Prof. Mohammed Gadelrab

School of Artificial Intelligence & Data Management
Badr University in Assiut (BUA)
Outline

Database Concepts:
– Data Models
– Database Design
– Database Schema & Instances
– Database Languages
– Database Users

Relational Database

Database Design Process

Entity Relationship (ER) Model
Spring 2025 BUA-AI & DM School 2
 DB Concepts: Data Models

A collection of tools for describing
– Data
– Data relationships
– Data semantics
– Data constraints

Example of data models:
– Relational model
– Entity-Relationship data model (mainly for database design)
– Object-based data models (Object-oriented and Object-relational)
– Semi-structured data model (XML)

Spring 2025 BUA-AI & DM School 3

 DB Concepts: DB Design
The process of designing the general structure of the database:

Logical Design – Deciding on the database schema. Database design requires that we find a
“good” collection of relation schemas.
– Business decision – What attributes should we record in the database?
– Computer Science decision – What relation schemas should we have and how should the
attributes be distributed among the various relation schemas?

Physical Design – Deciding on the physical layout of the database

Spring 2025 BUA-AI & DM School 4

 DB Concepts: DB Schema & Instances

Schema & instances in database are similar to types and variables in programming languages.

Logical Schema (Conceptual Level): the overall logical structure of the database
– Example: The database consists of information about a set of customers and accounts in a
bank and the relationship between them
●
Analogous to type information of a variable in a program

Physical schema (Internal): the overall physical structure of the database

Instance: the actual content of the database at a particular point in time
– Analogous to the value of a variable

Physical Data Independence – the ability to modify the physical schema without changing the
logical schema
– Applications depend on the logical schema
– In general, the interfaces between the various levels and components should be well
defined so that changes in some parts do not seriously influence others.
Spring 2025 BUA-AI & DM School 5
 DB Concepts: DB Languages: DDL

Data Definition Language (DDL): for defining the database schema
– Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))

DDL compiler generates a set of table templates stored in a data dictionary

Data dictionary contains metadata (i.e., data about data):
– Database schema
– Integrity constraints: Primary key (ID uniquely identifies instructors)
– Authorization: Who can access what

Spring 2025 BUA-AI & DM School 6

 DB Concepts: DB Languages: DML

Data Manipulation Language (DML): for accessing and updating the actual data in DB
– DML is also known as query language
– A SQL query takes as input one or several tables and always returns a single table.
– Example: to find all instructors in Comp. Sci. dept
select name from Students where dept_name = 'Comp. Sci.'

A transaction is a collection of operations (queries) that performs a single logical function in a
database application.

Transaction-management:
– Ensures that the database remains in a consistent (correct) state despite system failures
(e.g., power failures and operating system crashes) and transaction failures.
– Controls the interaction among the concurrent transactions, to ensure the consistency of
the database.

Spring 2025 BUA-AI & DM School 7

 DB Concepts: DB Users

Naive Users: who use the application and access DB through application interface.

Sophisticated Users (analysts): who use query tools and can interact directly with the DB.

Database Administrator (DBA): A person who has central control over the system.
– Tasks of a DBA include:
●
Schema definition
●
Storage structure and access-method definition
●
Schema and physical-organization modification
●
Granting of authorization for data access
●
Routine maintenance
●
Periodically backing up the database
●
Ensuring that enough free disk space is available for normal operations, and
upgrading disk space as required
●
Monitoring jobs running on the database

Spring 2025 BUA-AI & DM School 8

 Relational Database

Spring 2025 BUA-AI & DM School 9

 Example of Relational Database

Spring 2025 BUA-AI & DM School 10

 Relation Schema & Instance

Database schema -- is the logical structure of the database.

Database instance -- is a snapshot of the data in the database at a given instant in time.

A relation is normally represented as a table. The columns correspond to attributes and the
rows correspond to data instance. Order of tuples is irrelevant (tuples may be stored in an
arbitrary order).

Each row must have a “Key”, which is the attribute that uniquely identifies a row in the table.

Candidate Key, Primary Key, Foreign Key

Example:
schema: instructor (ID, name, dept_name, salary)
attributes

Instance as shown in the figure:
(or columns)
tuples
(or rows)

Spring 2025 BUA-AI & DM School 11

 Definition Summary

Informal Terms Formal Terms

Table Relation

Column Header Attribute

All possible Column Values Domain

Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation

Spring 2025 BUA-AI & DM School 12

 Relational Query Languages

Procedural versus non-procedural, or declarative

“Pure” languages:
– Relational algebra
– Tuple relational calculus
– Domain relational calculus

The above 3 pure languages are equivalent in computing power

We will concentrate in this chapter on relational algebra
– Not Turing-machine equivalent
– Consists of 6 basic operations

Spring 2025 BUA-AI & DM School 13

 Relational Algebra

A procedural language consisting of a set of operations that take one or two relations as input
and produce a new relation as their result.

Six basic operators
– select: 
– project: 
– Cartesian product: x
– union: 
– set difference: –
– rename: 

Spring 2025 BUA-AI & DM School 14

 Select Operation

The select operation selects tuples that satisfy a given predicate.

Notation:  p (r)

p is called the selection predicate

Example: select those tuples of the instructor relation where the instructor is in the “Physics”
department.
– Query

 dept_name=“Physics” (instructor)
– Result


We allow comparisons using: =, , >, . <. 

We can also use logic operators:  (and),  (or),  (not)
Spring 2025 BUA-AI & DM School 15
 Project Operation

TA unary operation that returns its argument relation, with certain attributes left
out.
 Notation:  A (r)
1,A2,A3 ….Ak

where A1, A2, …, Ak are attribute names and r is a relation name.


The result is defined as the relation of k columns obtained by erasing the
columns that are not listed.

Duplicate rows removed from result, since relations are sets.

Example: eliminate the dept_name attribute of instructor
Query: ID, name, salary (instructor)

The result of a relational-algebra operation is relation and therefore of
relational-algebra operations can be composed together into a relational-
algebra expression.

Consider the query -- Find the names of all instructors in the Physics
department.
name( dept_name =“Physics” (instructor))

Spring 2025 BUA-AI & DM School 16

 Cartesian-Product Operation

The Cartesian-product operation (denoted by X) allows us to combine information from any two
relations.

Example: the Cartesian product of the relations instructor and teaches is written as:
instructor X teaches

We construct a tuple of the result out of each possible pair of tuples: one from the instructor
relation and one from the teaches relation (see next slide)

Since the instructor ID appears in both relations we distinguish between these attribute by
attaching to the attribute the name of the relation from which the attribute originally came.
– instructor.ID
– teaches.ID

Spring 2025 BUA-AI & DM School 17

 Cartesian-Product
Operation Result

Spring 2025 BUA-AI & DM School 18

 Join Operation

The Cartesian-Product
instructor X teaches
– associates every tuple of instructor with every tuple of teaches.
– Most of the resulting rows have information about instructors who did NOT teach a
particular course.

To get only those tuples of “instructor X teaches “ that pertain to instructors and the courses
that they taught, we write:
 instructor.id = teaches.id (instructor x teaches ))

– We get only those tuples of “instructor X teaches” that pertain to instructors and the
courses that they taught.

The result of this expression, shown in the next slide

Spring 2025 BUA-AI & DM School 19

 Join Operation Result

The table corresponds to:
 instructor.id = teaches.id (instructor x teaches ))

It is equivalent to the join operation:

Spring 2025 BUA-AI & DM School 20

 Database Design Process

Two main activities:
– Database design
– Applications design

Focus in this lecture on conceptual database design
– To design the conceptual schema for a database application.

Applications design focuses on the programs and interfaces that access the database. Generally,
it is considered as part of software engineering.

Methods for conceptual DB design:
– Entity Relationship (ER) Diagrams.
– The UML Class Diagrams are popular in industry to document conceptual database designs.
– Use of Design Tools in industry for designing and documenting large scale designs.

Spring 2025 BUA-AI & DM School 21

 Major Steps in Building Databases

Six major steps in building a database for a particular enterprise:
1. Define the high-level requirements of the enterprise (this step generates a
document known as the system requirements specification.
2. Define a model containing all appropriate types of data and data
relationships.
3. Define the integrity constraints on the data.
4. Define the physical level.•
5. For each known problem to be solved on a regular basis (e.g., tasks to be
carried out by clerks or web users), define a user interface to carry out the
task, and write the necessary application programs to implement the user
interface.
6. Create/initialize the database.
Spring 2025 BUA-AI & DM School 22
 Entity Relationship (ER) Model

Entities, Attributes and Relationships

Entity is a basic concept for the ER model. Entities are specific things or objects in the mini-
world that are represented in the database.
– For example the EMPLOYEE, the Research DEPARTMENT, the ProductX PROJECT

Attributes are properties used to describe an entity.
– For example an EMPLOYEE entity may have the attributes: Name, SSN, Address,
BirthDate.

A specific entity will have a value for each of its attributes.
– For example a specific employee entity may have Name=‘Hassan Patrick’, S S
N=‘123456789’, Address =‘731, Abnob, Assiut’, BirthDate=‘09-JAN-2005’

Each attribute has a value set (or data type) associated with it – e.g. integer, string, date,
enumerated type, etc.
Spring 2025 BUA-AI & DM School 23
 Entity Set

An entity is an object that exists and is distinguishable from other objects.
– Example: person, company, event, plant

An entity set is a set of entities of the same type that share the same properties.
– Example: set of all persons, companies, trees, holidays

An entity is represented by a set of attributes; i.e., descriptive properties possessed by all
members of an entity set.
– Example:
Student = (ID, name, birth_date, level )
course= (course_id, title, credits)

A subset of the attributes form a primary key of the entity set; i.e., uniquely identifying each
member of the set.


Spring 2025 BUA-AI & DM School 24

 ER Diagrams
●
Entity sets can be represented graphically as follows:
– Rectangles represent entity sets.
– Attributes listed inside entity rectangle
– Underline indicates primary key attributes
●
Diamonds represent relationship sets:
– Advisor could be the relationship between a student and an instructor.

Spring 2025 BUA-AI & DM School 25

 Relationship Degree

Binary relationship
– involve two entity sets (or degree two).
– most relationship sets in a database system are binary.

Ternary relationship:
– Example: students work on projects under the guidance of an instructor.
– relationship proj_guide is a ternary relationship between instructor, student, and project

Spring 2025 BUA-AI & DM School 26

 Types of Attributes

Simple
– Each entity has a single atomic value for the attribute. For example, SSN or Sex.


Composite
– The attribute may be composed of several components. For example:
– Address(Apt#, House#, Street, City, State, ZipCode, Country), or
– Name(FirstName, MiddleName, LastName).
– Composition may form a hierarchy where some components are themselves
composite.

Multi-valued
– An entity may have multiple values for that attribute such as phone_numbers,
for example.

Derived Attributes: Can be computed from other attributes. For example age can be
derived from date_of_birth.


Domain: the set of permitted values for each attribute.

Spring 2025 BUA-AI & DM School 27

 Cardinality Constraints

Express the number of entities to which another entity can be associated via a relationship set.

For a binary relationship set the mapping cardinality must be one of the following types:
(a) (b)
– One to one (a)
– One to many (b)
– Many to one (c)
– Many to many (d)

min..max notation:
– Instructor can advise 0 or more students (c) (d)
– A student must have 1advisor; cannot have multiple
advisors

Spring 2025 BUA-AI & DM School 28

 Strong Entity and Weak Entity
●
A weak entity set is one whose existence is dependent on another entity (identifying entity).
●
Weak entity set is said to be existence dependent on the identifying entity set.
●
The relationship associating the weak entity set with the identifying entity set is called the identifying
relationship.
●
Consider a section entity, which is uniquely identified by a course_id, semester, year, and sec_id.
●
Clearly, section entities are related to course entities. Suppose we create a relationship set sec_course
between entity sets section and course.
●
Note that the information in sec_course is redundant, since section already has an attribute course_id, which
identifies the course with which the section is related.
●
One option to deal with this redundancy is to get rid of the relationship sec_course; however, by doing so the
relationship between section and course becomes implicit in an attribute, which is not desirable.
Alternatively, we do not store the attribute course_id in the section entity and to only store the remaining
attributes section_id, year, and semester.

Spring 2025 BUA-AI & DM School 29

 Summary for ER Diagram Symbols

Spring 2025 BUA-AI & DM School 30

 Simplified Example of ER Diagram

Spring 2025 BUA-AI & DM School 31

 ER Diagram for a
University Enterprise

Spring 2025 BUA-AI & DM School 32

 Creating Relation Schemas

Entity sets and relationship sets can be expressed uniformly as relation schemas that represent the
contents of the database.

A database which conforms to an ER diagram can be represented by a collection of schemas.

For each entity set and relationship set there is a unique schema that is assigned the name of the
corresponding entity set or relationship set.

Each schema has a number of columns (generally corresponding to attributes), which have unique
names.

A strong entity set reduces to a schema with the same attributes:
student (ID, name, tot_cred)

A weak entity set becomes a table that includes a column for the primary key of the identifying strong
entity set:
section ( course_id, sec_id, sem, year )

Spring 2025 BUA-AI & DM School 33

 ???

BUA-AI & DM School 34