Advanced Database – Lab (2)
What is a Relational Database
Agenda Entity Relationship Diagrams
Task (1)
An information system(IS) is
typically considered to be a set
of interrelated elements or
components that collect (input),
manipulate (processes), and
disseminate (output) data and
What is an information and provide a
feedback mechanism to meet
Information
an objective.
System? An information system is a
combination of software,
hardware, and
telecommunication networks to
collect useful data
Difference between Data and
Information
Data is an individual unit that contains raw materials
which do not carry any specific meaning. Information is a
group of data that collectively carries a logical meaning.
Student Management
System in AAST
Examples of
Information Hotels
Systems
Hospitals
System
Input
System Output
Relational
Databases
Relational Database
Definition: is a type of database that stores and organizes data
in one or more tables. The key feature is that these tables can be
linked, or "related," to one another through shared fields, allowing
you to easily see how different pieces of data connect.
Key Features:
They are designed to
All tables have
Data is stored in maintain the They are built using
relationships linking
Tables. accuracy and SQL.
them together.
consistency of data.
Are there other
kinds of
Databases?
Some other Databases include:
- No SQL Databases
- Object Oriented Databases
Key differences between SQL and noSQL Databases are:
Feature SQL Database NoSQL Database
Tables with a fixed Flexible schema
Data Model
schema (documents, etc.) 📂
Managed by
Explicitly defined with
embedding or
Relationships foreign keys and joins
referencing data (no
🔗
joins) 📝
Eventual consistency
Consistency Strong consistency ✅
⏳
High availability &
Data integrity &
Primary Goal horizontal
complex queries
scalability 🚀
Example Oracle Live MongoDB
Entity Relationship Diagrams - ERD
An Entity-Relationship Diagram (ERD) is a visual tool used to model the structure of
a database. Think of it as a blueprint for a database that shows how different pieces
of information relate to one another.
It uses simple symbols to represent three core components:
• Entities: These are the main "things" or objects you want to store data about, like
a Customer or an Order. They are usually represented by rectangles.
• Attributes: These are the properties or details of an entity, such as a customer's
name or an order's date. They are often shown as ovals linked to their entity.
• Relationships: These show how entities are connected to each other, such as
how a Customer places an Order. They are typically represented by a diamond or
a line.
ERD Notations
The degree of a relationship is the
number of
entity types that participate in
DEGREE OF A that relationship
RELATIONSHIP The 3 common relationships are:
IN AN ERD Unary relationship(Degree 1)
Binary relationship(Degree 2)
Ternary relationship(Degree 3)
A. UNARY RELATIONSHIP
• A relationship between
the instances of a
single
• entity type
• Also called recursive
relationship
• Examples:
• A person is married to
another person
• An employee manages
other employees
B. BINARY RELATIONSHIP
• A relationship between
the instances of two
entity types
• The most common
type of relationships
C. TERNARY RELATIONSHIP
RELATIONSHIP PROPERTIES–
[Link] CARDINALITY
According to cardinality there are three
different
relationship types :
one-to-one
one-to-many
many-to-many.
RELATIONSHIP PROPERTIES-
RELATIONSHIP CARDINALITY(ONE TO ONE)
• Each EMPLOYEE has
one and only one
PARKING PLACE and
each PARKING
PLACE belongs to
only one EMPLOYEE.
This is called a one-
to-one relationship.
RELATIONSHIP PROPERTIES –RELATIONSHIP
CARDINALITY(ONE-TO-MANY)
Each EMPLOYEE has
one and only one
PARKING PLACE and
In this example: Each
EMPLOYEE belongs to
one and only one
DEPARTMENT, but
Each DEPARTMENT
has one or more
EMPLOYEE. This is
called a one-to-many
relationship.
One to many is the
most common type of
relationships
In case of one to many: Add primary key field of one
entity type (Company) as a foreign key in the table that
represents the many entity type (Employee).
Primary and Foreign
key Examples
Why is it necessary
to break many to
many relationships
in the Relational
Database?
The Problem
We break down many-to-many relationships in a relational database because the
tabular structure of relational databases can't handle them directly. A table row is
designed to store a single, unique piece of information, not a list of multiple
related items.
Imagine you have a Students table and a Courses table.
• A student can take many courses.
• A course can have many students.
If you tried to connect these directly, you'd run into problems. Where would you
store the list of courses for a student? You can't put multiple course IDs in a single
course_id column. That violates the fundamental rules of a relational database.
The Solution: The Associative
Entity
To solve this, we introduce a new table called an associative entity (also known
as a junction or bridge table). This table acts as a simple bridge between the two
original tables.
For our example, we would create a new Enrollments table. This table has only
two main columns: student_id and course_id.
• The student_id is a foreign key linking back to the Students table.
• The course_id is a foreign key linking back to the Courses table.
This single Enrollments table now turns the one complex many-to-many
relationship into two simple one-to-many relationships:
1. One student can have many enrollments.
2. One course can have many enrollments.
Sample ERD:
Task (1):
Form Groups of 4-5
Questions? members
choose a project topic