Fundamentals of DB Lab

1
 What is database?

A database is a structured collection of data stored

on a computer.
 There are two common database types

- RDBMS: This is the most common database type,

organizing data into tables with rows and columns.

- Document databases (or NoSQL databases): These types

of database stores data as documents.

SQL is a standard language for interacting with RDBMS.

 SQL contains three main parts

DDL: deals with the database structure creation and

modification. {CREATE, ALTER, DROP} table.

DML: provides statements to query data. INSERT, UPDATE,

and DELETE statements.

DCL: includes statements, working with user authorization

and security. GRANT and REVOKE statements
SQL is a Declarative Language: It specifies what data to retrieve or
manipulate, not how to perform the operation.
Basic Structure: Most SQL statements follow the pattern:​

- Verb (Action): The operation to perform, such as SELECT, INSERT, UPDATE,

or DELETE.​

- Subject (Target): The database object, like a table, on which the action is
performed.​

- Condition (Filter): Criteria that specify which data to act upon.

SELECT first_name
FROM students
WHERE department = ‘Software
Engineering’;
 SQL Building Blocks
Literals: are constants you use in SQL statements. Literals are case-
sensitive.

- Strings: are enclosed in single quotes like 'Anthony'

and 'Blue'

- Numbers: SQL supports integers, decimals, and floats.

- Booleans: are true and false.

- Dates: date literals are in the format ‘yyyy-mm-dd’

- Times: time literals are in the format 'hh:mm:ss'

- Timestamps: timestamps include both date and time.

 SQL Building Blocks

Keywords: are reserved words in SQL.

They have special meanings. e.g SELECT, FROM, WHERE, CREATE,
INSERT, etc.
 SQL Building Blocks
Expressions (Combinations): Combinations of literals, identifiers, and
operators that produce a value

e.g birth_date < ‘2000-01-01' (checks if the datedate is

before January 1, 2000).
 SQL Building Blocks
Comments are notes you add to your SQL code to explain why it does
a specific task.

- Single-line comments start with two hyphens (--) and

is followed by a comment.
- Multi-line comments start with /* and end with */.
The text between these is a comment.
 Create database DatabaseName
Example
Create Database
Create database aait
 Create table
Syntax
Create table table_name(Attribute data type,……..);
Example
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_year INT CHECK (enrollment_year >= 2000),
major VARCHAR(50),
gpa DECIMAL(3, 2) CHECK (gpa BETWEEN 0.00 AND 4.00)
);
12
 Auto Increment
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_year INT CHECK (enrollment_year >= 2000),
major VARCHAR(50),
gpa DECIMAL(3, 2) CHECK (gpa BETWEEN 0.00 AND 4.00)
);

13
 Inserting values In to Table
 You can insert values in to the table using
Syntax;
Insert into Tablename(attribute1,atttribute2……)
Values(val1,val2,val3….)
Example:
INSERT INTO students (student_id, first_name, last_name, email, enrollment_year,
major, gpa) VALUES
(2, 'Kebede', 'Tesfaye', 'kebede.t@example.com', 2022, 'Mathematics', 3.5),
(3, 'Selam', 'Wolde', 'selam.w@example.com', 2024, 'Physics', 3.9),
(4, 'Selam', 'Abebe', 'selam.a@example.com', 2021, 'Physics', 3.4);

14
 Inserting Values into Auto_increment Key
 You can skip the auto increment attribute during data insertion
Syntax;
CREATE TABLE Employees (

IDNO INT AUTO_INCREMENT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

Email VARCHAR(100)

);

INSERT INTO Employees (FirstName, LastName, Email)

VALUES ('John', 'Doe', 'john.doe@example.com');

15
 Filtering
Practical Queries
a. Select Names of All Students:

SELECT first_name, last_name FROM students;

b. Select all columns from students

SELECT * FROM students;

DISTINCT – Retrieves distinct values from a result set.

SELECT DISTNICT first_name FROM students;

 Equal Opeator

SELECT first_name, last_name, enrollment_year

FROM students
WHERE enrollment_year = 2023;
 AND Operator

The AND operator returns true if both expressions are

evaluated to true

SELECT first_name, last_name, major, gpa

FROM students
WHERE major = 'Computer Science' AND gpa > 3.5;
 OR Operator
• The OR operator returns false if and only if
both expressions are false.

Syntax: expression1 OR expression2

 Between
• Syntax: expression BETWEEN low AND high;

Meaning: >= low and <= high

Example: SELECT first_name, last_name, gpa

FROM students WHERE gpa BETWEEN 3.5 and 3.9;
 Like
• The LIKE operator returns true if a value
matches a pattern or false otherwise.

Example: SELECT first_name, last_name

FROM students
WHERE first_name LIKE 'S%';
NOT LIKE...
 Wild-cards
• It matches a single character.
Example: SELECT first_name, last_name
FROM
students
WHERE
first_name LIKE 'Se___'
ORDER BY Clause – Sorts the rows in a result
set.

SELECT first_name, last_name, gpa

FROM students
ORDER BY gpa DESC;
GROUP BY: groups rows that have the same values in specified columns
and perform aggregate functions (like COUNT, SUM, AVG) on each
group.

SELECT major, COUNT(*) AS total_students

FROM students
GROUP BY major;
LIMIT – Limits the number of rows a query returns.

SELECT first_name, last_name, gpa

FROM students
ORDER BY gpa DESC
LIMIT 2;
OFFSET: skip a specific number of rows in the result set, often used
with LIMIT for pagination.

SELECT first_name, last_name

FROM students
ORDER BY student_id
LIMIT 3 OFFSET 2;
IS NULL: Use case: detect null values.

SELECT first_name, last_name

FROM students
WHERE major IS NULL;
 Deletion
DELETE FROM students
WHERE first_name=’Chala’;

Update

UPDATE students
SET first_name= ‘Abebe’
WHERE id=02;
 DDL
• Alter a table: Add A Column
ALTER TABLE students
ADD department_id INT;
 DDL
• Alter Table: Delete a column
ALTER TABLE students
DROP COLUMN gpa;
 DDL
• Alter a table: Rename a column
ALTER TABLE students
RENAME COLUMN email TO student_email;
 DDL
• Truncate table: Delete All rows, but keep table
strature intact.
TRUNCATE TABLE students;
 DDL
• Rename table: change table name.
RENAME TABLE students to student_data;
 Dropping table
To delete or drop a table from a schema
• DROP TABLE table_name;
 Entity-Relationship (ER) Modeling
• It is a high-level, conceptual data modeling
technique used to describe the structure of a
database.
 ER Modeling
• Simplifies complex database systems into understandable
components.

• Serves as a communication tool between technical teams

and clients.

• Helps identify problems early before building the actual

database.

• Ensures proper data integrity by enforcing relationships

and constraints.
 Entity
• An Entity is anything in the real world that has
a distinct and independent existence.
• Entities represent "nouns" like Student,
Employee, Course, Book, etc.
 Entity Set
• An Entity Set is a collection of similar entities
that share the same attributes.

• An Entity Set refers to the whole group of

similar entities (e.g., all students in the
database).
 Types of Entities

1. Strong Entity (Regular Entity)

• Can exist independently of other entities.

• Has a primary key (unique identifier).

• Represented by single rectangles in ER

diagrams
 Types of Entities
2. Weak Entity
• Cannot exist without being connected to another (strong)
entity.

• Does not have a full primary key on its own; relies on the
primary key of the related entity.

• Requires an identifying relationship (shown with a double

diamond in the diagram).

• Represented by double rectangles.

 Attribute
• An attribute is a property or characteristic that
describes an entity or a relationship in the
Entity-Relationship (ER) model.
 Attribute
• Entities → described by attributes

• Each attribute becomes a column in the

relational schema

• Attributes help define the structure of the

data stored in the database
 Types of Attributes
1. Simple (Atomic) Attributes
• Cannot be divided into smaller components.

• Directly hold single values.

• first_name, age, salary, email

• These map directly to table columns without any

need for breakdown.
 Types of Attributes
2. Composite Attributes
• Can be broken down into smaller sub-attributes.

• Still considered one attribute in the ER diagram but

internally has structure.

• full_name → can be divided into first_name and

last_name

• address → may have street, city, postal_code

 Types of Attributes
3. Multivalued Attributes
• Can hold multiple values for the same entity.
• Represented by double ovals in ER diagrams.

• phone_numbers for a single employee might

include more than one number: 0923232323,
0934343434
 Types of Attributes
4. Derived Attributes
• Not directly stored but calculated from other
stored values.
• Shown with a dashed oval in ER diagrams.

• age derived from birth_date

 Types of Attributes
5. Stored Attributes
• Directly stored in the database (not
calculated).

• These are the actual data columns in the

table.

• birth_date, name, email, gpa

 Types of Attributes
Example
Attribute Type Description

Simple (Atomic) Cannot be divided further name, age, salary

Composite Can be broken into sub-parts full_name → first + last

Contains multiple values

Multivalued phone_numbers, hobbies

Calculated from other stored

Derived attributes age from birth_date

Stored birth_date, email

Directly saved in the database
 Keys
• An attribute used to uniquely identify an
entity within an entity set or a tuple (row)
within a relation (table).

• Link
 Relationships (in ER Modeling)
• A relationship is an association between two
or more entity sets that connects related data
together.
 Relationships
• Student enrolls in Course

• Employee works in Department

• Doctor treats Patient

• In an ER diagram, relationships are represented

by diamonds between entities (rectangles).
 Types of Relations (Cardinality Ratios)

• CR is the number of entities in one entity set

that can be associated with entities in another
entity set through a relationship.

• It defines the maximum number of

relationship instances that can exist between
the entities involved.
 Types of Relations (Cardinality Ratios)

One-to-One(1:1)
• One entity in Entity A matches exactly one
entity in Entity B
• Each Employee has one parking spot

• Each Department has one HOD

 Types of Relations (Cardinality Ratios)

One-to-Many (1:N)
• One entity in A matches many in B
• Department → Students
 Types of Relations (Cardinality Ratios)

Many-to-Many (M:N)
• Many in A relate to many in B
• Student ↔ Course
 Participation Constraints

Total Participation
• Every entity must participate in the
relationship (mandatory)
• Represented by Double Line

• Every student must enroll in at least one

course (Total Participation).
 Participation Constraints

Partial Participation
• Participation is optional (not all entities
participate)
• Represented by Single Line

• Some instructors may not be teaching any

course (Partial Participation).
 Degrees of Relationship
• The degree of a relationship refers to the
number of entity sets that participate in a
relationship.

• It describes how many entities are involved in

a particular relationship type.
 Degrees of Relationship
1. Unary (Recursive)
• Relationship between the same entity set
• A recursive relationship connects an entity to
itself.

• An Employee supervises another Employee.

• Both participating entities come from the

Employee entity set.
 Degrees of Relationship
2. Binary Relationships (Degree = 2)
• The most common relationship type in
database design.
• Student enrolls in Course
• Instructor teaches Course
• Participation Constraints can also be used
(total or partial).
 Degrees of Relationship
3. Ternary Relationships (Degree = 3)
• A relationship where three different entity sets are
involved simultaneously.

• Supplier supplies Parts to Project.

• You can know that Supplier X supplied Part Y specifically

for Project Z.

• Instructor advices Students on a project

 Example Scenario

Entity A Relationship Entity B Cardinality Participation

Student enrolls Course Many-to-Many Total (Student side)

Instructor teaches Course One-to-Many Partial

Total (Course
Department offers Course One-to-Many side)
Cardinalities
 One-to-Many
• This is the most common one.
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
 One-to-one
• One record in Table A is linked to only one in
Table B
CREATE TABLE student_profiles (
student_id INT PRIMARY KEY,
bio TEXT,
photo_url TEXT,
FOREIGN KEY (student_id) REFERENCES
students(student_id)
);
 Many-to-Many
• Records in Table A can relate to many in Table B, and vice versa.
-- Courses table
CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(100)
);

-- Enrollment junction table

CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
 Exercise
• Create all company database tables with their respective attributes
• Identify their attributes
• Create relationship
• Populate each record with at least 10 records
• Manipulate the data using delete,update….
• Try to delete a record from the department table while it is being
referenced by a record from employee
• Try to delete a record from the department table while it is being
referenced by a record from project table
• Try to delete a record from the department while it is being
referenced by a record from project table
• Try to delete a record from the department while it is being
referenced by a record from project table