DATABASES
Compiled by Amidu Nashiru
DATABASES
A database is a structured collection of logically related data.
A database is an organized collection of data that allows users to
efficiently store, retrieve, and manage information. Databases
play a crucial role in various fields, including business,
healthcare, finance, education, and technology.
Compiled by Amidu Nashiru
USES OF DATABASES
• Data Management: Efficiently storing and retrieving data for applications
such as banking systems, online shopping, and enterprise resource
planning.
• Data Integrity and Security: Enforcing rules to maintain data consistency
and restricting unauthorized access.
• Data Sharing: Allowing multiple users to access the same data
simultaneously.
• Data Backup and Recovery: Protecting data against loss and corruption.
• Decision Making: Providing analytical tools for business intelligence and
reporting.
Compiled by Amidu Nashiru
File-Based Approach vs. Database Approach
File-Based Approach Database Approach
• Stores data in separate files • Stores data in a centralized database
managed by a Database Management
managed by the operating system. System (DBMS).
• Each application manages its own • Eliminates redundancy and
set of files. inconsistency through normalization
and indexing.
• Data redundancy and inconsistency • Provides multi-user access with
are common. concurrency control.
• Supports complex queries and
• Difficult to maintain and update as efficient data retrieval.
the system grows. • Offers better data security and backup
• Limited data sharing capabilities. mechanisms.
Compiled by Amidu Nashiru
FEATURES OF DATABASES
Feature File-Based Approach Database Approach
Data Redundancy High Low
Data Consistency Low High
Data Sharing Limited Extensive
Data Security Minimal Strong
Querying Flexibility Limited Extensive
Compiled by Amidu Nashiru
Relational Database Concepts
A Relational Database is a type of database that stores data in
tables (relations) with predefined relationships between
them.
Relational Database Design focuses on structuring data
efficiently to reduce redundancy and improve consistency.
Compiled by Amidu Nashiru
KEY CONCEPTS OF RELATIONAL DATABASES
• Table (Relation): A collection of related data organized in rows and
columns.
• Tuple (Row): A single record in a table.
• Attribute (Column): A specific piece of information stored in a table.
• Primary Key: A unique identifier for each record in a table.
• Foreign Key: A field in one table that establishes a relationship with
the primary key of another table.
• SQL (Structured Query Language): The language used to interact with
relational databases.
Compiled by Amidu Nashiru
Relational Database Design
Relational Database Design focuses on structuring data efficiently to
reduce redundancy and improve consistency.
Compiled by Amidu Nashiru
Steps in Designing a Relational Database:
• Identify Entities and Attributes: Determine the key objects (entities)
and their properties (attributes).
• Establish Relationships: Define how entities are related using foreign
keys.
• Normalize the Database: Apply normalization techniques to eliminate
redundancy.
• Define Constraints: Implement primary keys, foreign keys, and
integrity constraints.
• Create Schema: Develop tables based on the logical design.
Compiled by Amidu Nashiru
Examples of Simple Schemas:
• Students (StudentID, Name, Age, CourseID)
• Courses (CourseID, CourseName, Instructor)
Compiled by Amidu Nashiru
Data Normalization
Data Normalization is a process used to organize data in a database
efficiently by reducing redundancy and improving integrity.
Compiled by Amidu Nashiru
Normalization Forms
Normalization is a systematic approach of decomposing tables to
eliminate data redundancy and undesirable characteristics such as
insertion, update and delete anomalies. It is a multi-step process that
puts data in to tabular form by removing duplicated data from the
relational table.
Normalization is used mainly for 2 purposes:
- Eliminating redundant data
- Ensuring data dependencies makes sense. ie:- data is stored logically
Compiled by Amidu Nashiru
PROBLEMS WITHOUT NORMALIZATION
S_Id S_Name S_Address Subjects_opted
401 Adam Colombo-4 Bio
402 Alex Kandy Maths
403 Steuart Ja-Ela Maths
404 Adam Colombo-4 Chemistr
Compiled by Amidu Nashiru
Updation Anomaly – To update the address of a student who
occurs twice or more than twice in a table,we will have to
update S_Address column in all the rows, else data will be
inconsistent.
Insertion Anomaly – Suppose we have a student (S_Id), name
and address of a student but if student
has not opted for any subjects yet then we have to insert null,
which leads to an insertion anomaly.
Deletion Anomaly – If (S_id) 401 has opted for one subject
only and temporarily he drops it, when we
delete that row, entire student record will get deleted.
Compiled by Amidu Nashiru
1st Normal Form – No two rows of data must contain
repeating group of information. Ie. Each set of
column must have a unique value, such that multiple columns
cannot be used to fetch the same row.
Each row should have a primary key that distinguishes it
uniquely.
Primary Key – The primary key is usually a single column, but
sometimes more than one column can be
combined to create a single primary key
Compiled by Amidu Nashiru
First Normal Form
Before Normalization After Normalization
Student Age Subject Student Age Subject
Adam 20 Bio
Adam 20 Bio, Maths
Adam 20 Maths
Alex 21 Maths
Alex 21 Maths
Steuart 19 Maths
Steuart 19 Maths
Compiled by Amidu Nashiru
In 1st normal form, any row must not have a column in which
more than one value is saved. However in 1st normal form,
data redundancy will increase as there will be many columns
with the same data in multiple rows, but each row as a whole
will be unique.
Compiled by Amidu Nashiru
2nd normal form
In the 2nd normal form there should not be any partial
dependency of any columns on primary key. A table that has
concatenated primary key, each column in the table that is not
part of the primary key must depend upon the entire
concatenated key for its existence.
Compiled by Amidu Nashiru
SECOND NORMAL FORM
BEFOR NORMALIZATION AFTER NORMALIZATION
Student Age Subject Student Age Student Subject
Adam 20 Bio Adam Bio
Adam 20
Adam 20 Maths Adam Maths
Alex 21
Alex 21 Maths Alex Maths
Steuart 19 Maths Steuart 19
Steuart Maths
Compiled by Amidu Nashiru
While the candidate key is (Student, Subject), Age of student
only depends on Student column.
Compiled by Amidu Nashiru
3rd normal form
Every non-prime attribute of table must be dependent
on primary key. The transitive functional
dependency must be removed from the table
Compiled by Amidu Nashiru
In this table Student_Id is the primary key, but street and city depends
on Zip. The dependency between Zip and other fields is called
transitive dependency. Hence, to apply 3rd normal form, we need to
remove Street, City and State to a new table with Zip as a primary key.
Example – Student_Detail table (before 3rd normal form)
Student_Id Student_Name DOB Street City Zip
Compiled by Amidu Nashiru
After 3rd Normal Form
Student_Id Student_Name DOB
Zip Street City State
Compiled by Amidu Nashiru
Entity-Relationship Diagrams (ERD)
An Entity-Relationship Diagram (ERD) is a visual representation of
entities and their relationships within a database.
Compiled by Amidu Nashiru
Key Components of ERD
• Entities: Objects that store data (e.g., Students, Courses, Teachers).
• Attributes: Properties of an entity (e.g., StudentID, Name, Age).
• Relationships: Connections between entities (e.g., A Student enrolls
in a Course). There are 4 types of relationships that could exist
between entities
• One-to-one (Eg:-Husband and Wife)
- One-to-many (Eg:-Student and Student_Detail)
- Many-to-one( Children to Father)
- Many-to-many (Eg:-Student and Address)
• Primary and Foreign Keys: Unique identifiers that define
relationships.
Compiled by Amidu Nashiru
ERD Notation
• Rectangle: Represents an entity.
• Oval: Represents an attribute.
• Diamond: Represents a relationship.
• Lines: Connect entities to attributes and relationships.
Compiled by Amidu Nashiru
Compiled by Amidu Nashiru
TYPES OF KEYS IN RELATIONAL DATABASES
• Candidate Key: A key that can uniquely identify a record in a table. A
candidate key could be simple or composite.
• Super Key: A set a attributes that can uniquely identify a record.
• Primary Key: is a set of attributes that uniquely identifies a tuple. A
primary key can not contain null values.
• Alternate Key: Any other candidate key apart from the primary key is
an alternate key.
• Foreign Key: is a key that is being referenced by another entity in a
database.
Compiled by Amidu Nashiru
INTRODUCTION TO A DBMS
A Database Management System (DBMS) is
software that enables users to store, retrieve,
manage, and manipulate data efficiently. It
provides a structured approach to organizing data
and ensures data integrity, security, and
consistency.
Compiled by Amidu Nashiru
FEATURES OF A DBMS
• Data Storage & Retrieval – Stores and retrieves data efficiently.
• Data Security & Integrity – Ensures data accuracy and access control.
• Multi-user Access – Supports multiple users accessing the database
simultaneously.
• Backup & Recovery – Provides mechanisms for data recovery in case
of failures.
• Data Abstraction – Hides complex database structures from users.
Compiled by Amidu Nashiru
TYPES OF DBMS
• Relational DBMS (RDBMS) – Uses tables with relationships (e.g.,
MySQL, SQL Server, PostgreSQL).
• NoSQL DBMS – Works with non-relational data (e.g., MongoDB,
Cassandra).
• Object-Oriented DBMS (OODBMS) – Works with objects rather than
tables.
• Hierarchical & Network DBMS – Older models used for specific
applications.
Compiled by Amidu Nashiru
MYSQL AS A DBMS
MySQL is an open-source Relational Database
Management System (RDBMS) that is widely used for
web applications and enterprise solutions. It follows
the SQL standard and provides robust features like
transactions, replication, and indexing.
Compiled by Amidu Nashiru
Implementing Databases
• Database Implementation Process
• Requirements Analysis – Understanding what data needs to be stored.
• Database Design
• Conceptual Design – Entity-Relationship (ER) modeling.
• Logical Design – Table structures, relationships, constraints.
• Physical Design – Storage and indexing strategies.
• Normalization – Eliminating data redundancy using normalization
techniques.
• Implementation – Creating tables, relationships, and constraints in a
DBMS.
• Testing & Optimization – Ensuring performance and correctness.
• Deployment & Maintenance – Making the database live and managing
updates.
Compiled by Amidu Nashiru
Structured Query Language (SQL)
SQL (Structured Query Language) is a programming
language used for storing and managing data in
RDBMS. All RDBMS (SQL Server,
Oracle, MySQL, MS Access) use SQL as the standard
database language. SQL is used to perform all types
of operations in a database.
Compiled by Amidu Nashiru
SOME DATA TYPES (STRINGS)
• CHAR String (0 - 255)
• VARCHAR String (0 - 255)
• TEXT String (0 - 65535)
Compiled by Amidu Nashiru
DATA TYPES DATE AND TIME
• DATE YYYY-MM-DD
• DATETIME YYYY-MM-DD HH:MM:SS
• TIMESTAMP YYYYMMDDHHMMSS
• TIME HH:MM:SS
Compiled by Amidu Nashiru
DATA TYPES (NUMERIC)
• INT Integer (-2147483648 to 214748- 3647)
• BIGINT Integer (-9223372036854775808 to 9223372036854775807)
• FLOAT Decimal (precise to 23 digits)
• DOUBLE Decimal (24 to 53 digits)
Compiled by Amidu Nashiru
CATEGORIES OF SQL COMMANDS
Data Definition Language
Data Manipulation Language
Data Control Language
Transaction Control Language
Compiled by Amidu Nashiru
Data Definition Language (DDL)
Command Description
CREATE To create new table or database
ALTER For alteration
TRUNCATE Delete data from table
DROP Drop a table
RENAME To rename a table
Compiled by Amidu Nashiru
CREATE – Creates a new database or table.
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT);
Compiled by Amidu Nashiru
ALTER – Modifies a table structure.
ALTER TABLE students ADD email
VARCHAR(100);
Compiled by Amidu Nashiru
DROP – Deletes a table or database.
DROP TABLE students;
Compiled by Amidu Nashiru
Data Manipulation Language (DML)
Command Description
INSERT To create new table or database
UPDATE For alteration
DELETE Delete data from table
MERGE Drop a table
SELECT Selects an entity or attribute
Compiled by Amidu Nashiru
INSERT – Adds new records.
INSERT INTO students (name, age) VALUES ('John', 20);
UPDATE – Modifies existing records.
UPDATE students SET age = 21 WHERE name = 'John';
Compiled by Amidu Nashiru
DELETE – Removes records.
DELETE FROM students WHERE age < 18;
Compiled by Amidu Nashiru
Data Control Language (DCL)
Command Description
GRANT Grant permission of right
REVOKE Take back permission
Compiled by Amidu Nashiru
•GRANT – Provides permissions.
GRANT SELECT ON students TO 'user1';
•REVOKE – Removes permissions.
REVOKE SELECT ON students FROM 'user1';
Compiled by Amidu Nashiru
Transactional Control Language (TCL)
Command Description
COMMIT To permanently save
ROLLBACK To undo change
SAVEPOINT To save temporarily
Compiled by Amidu Nashiru
Data Query Language (DQL)
Command Description
SELECT Retrieve records from one or more tables
Compiled by Amidu Nashiru
To be continued……………………….
Compiled by Amidu Nashiru
