Lesson: Database concept

PRESENTED BY
MR. DIFOUO JEAN PATRICE
MAITRE-ES COMPUTER SCIENCE
674371991

Information, the heart and the soul

of any bussiness
 Life situation

 A retail company, "Gadget World," sells electronic

devices and accessories. The company needs a
system to track and manage sale transactions.
 Knowing that each customer can place as many
purchase orders as possible, each order contains
many products each with a sale price and quantity,
each order has a date and a reference number,
 Answer the questions below
 Questions

1. What are the various ways to keep and manage sale

transaction informations of the company?
2. Among the methods listed above, which one was first
used? Give a brief description
3. What are the limitations of the first method listed
above?
4. Describe each of the alternate methods listed in
question 1 and bing out advantages over the first
method as well as fundamental objectives of the
method
5. Differentiate between the first method and any
alternate methods listed in question 1
 Database concept and purpose

 A database is an organized collection of data that is

stored and managed in a way that allows for easy
access, retrieval, and management.
 Databases are designed to hold large amounts of
information and enable users to interact with the
data efficiently.
 Key Components of a Database:

 Data: The actual information stored in the database,

which can be in various formats (text, numbers, images,
etc.).
 Database Management System (DBMS): Software
that facilitates the creation, manipulation, and
administration of databases. Examples include MySQL,
PostgreSQL, Oracle, and Microsoft SQL Server.
 Schema: The structure that defines how data is
organized in the database, including the tables, fields,
and relationships between them.
 Queries: Requests made to the database to retrieve or
manipulate data, typically using a query language like
SQL (Structured Query Language).
 Application of databases

 Databases are widely used across various fields and

industries due to their ability to organize, store, and
retrieve large amounts of data efficiently. Here are
some common applications of databases:
 Application of databases

 1. Business Applications
 Customer Relationship Management (CRM): Storing
customer data, interactions, and sales history to improve
customer service and sales strategies.
 Enterprise Resource Planning (ERP): Integrating core
business processes such as finance, HR, and supply chain
management into a single database.
 2. E-Commerce
 Managing product catalogs, inventory levels, customer
information, and order processing.
 Providing personalized shopping experiences through data
analysis and customer profiles.
 Application of databases

 3. Healthcare
 Storing patient records, treatment histories, and billing
information.
 Supporting clinical research and data analytics for improving
patient outcomes.
 4. Banking and Finance
 Managing accounts, transactions, and customer data securely.

 Enhancing fraud detection and risk management through data

analysis.
 Application of databases

 5. Education
 Maintaining student records, course enrollments, and grades.

 Supporting online learning platforms with user data and

course material management.
 6. Social Media
 Storing user profiles, posts, comments, and interactions.

 Analyzing user behavior and preferences for targeted

advertising.
 Application of databases

 7. Telecommunications
 Managing customer accounts, call records, and billing
information.
 Optimizing network performance and usage analysis through
data collection.
 8. Government
 Maintaining public records such as tax information, voting
data, and licensing.
 Supporting data analysis for policy development and public
services.
 Application of databases

 9. Data Analytics
 Storing large datasets for analysis and reporting.

 Supporting business intelligence tools to derive insights and

make data-driven decisions.
 10. Internet of Things (IoT)
 Managing data generated by IoT devices, such as sensors and
smart devices.
 Supporting real-time data processing and analysis for
automation and monitoring.
 Characteristics of databases

 Structure:
 Data is stored in a structured format (tables with rows and columns in relational databases).
 Supports complex data types and relationships.
 Access:
 Optimized for fast data retrieval using indexes and query languages (e.g., SQL).
 Advanced querying capabilities, including joins, filtering, and aggregation.
 Data Integrity:
 Enforces data integrity through constraints (primary keys, foreign keys).
 Built-in mechanisms for validation, ensuring consistent and accurate data.
 Concurrency:
 Designed for multiple users to access data simultaneously without conflicts.
 Implements locking and transaction management to maintain data integrity.
 Scalability:
 Better suited for handling large volumes of data and complex queries.
 Can be scaled vertically (adding resources to a single server) or horizontally (distributing across
multiple servers).
 Backup and Recovery:
 Provides automated backup and recovery solutions.
 Advanced recovery options, including point-in-time recovery
 Before databases: Files

 A file is a digital unit of storage that contains data or

information on a computer or electronic device.
 It is a collection of related information stored on a
storage medium as a single entity under a name.
 Any user data on a mass storage device is stored in a
file.
 Each file is related to a specific application and the
organization logic is coded is that application
 Files are managed by the OS file system.
 Characteristics of files

 Structure:
 Data is stored in a flat or hierarchical format (e.g., text files, CSV, XML).
 Typically unstructured or semi-structured.
 Access:
 Data access is generally sequential or requires reading the entire file.
 Limited querying capabilities (often requires custom scripts).
 Data Integrity:
 Minimal integrity enforcement; reliance on application logic to maintain data consistency.
 No built-in mechanisms for data validation, constraints, or relationships.
 Concurrency:
 Limited support for multiple users accessing the same file simultaneously.
 Potential for data corruption or loss due to concurrent writes.
 Scalability:
 Difficult to scale for large amounts of data; performance may degrade as file size increases.
 Harder to manage and maintain as data grows.
 Backup and Recovery:
 Typically requires manual backup solutions; less robust.
 Recovery processes can be cumbersome and time-consuming.
 Limitations of Files

 Data Redundancy: Multiple files may store the same data,

leading to duplication and inconsistencies.
 Data Integrity Issues: Ensuring data accuracy and
consistency across files can be challenging, especially when
updates are made.
 Limited Data Sharing: Sharing data across different
applications or users can be cumbersome and error-prone.
 Lack of Security: Files often lack robust security features,
making sensitive data vulnerable to unauthorized access.
 Difficulties in Data Management: Managing and
updating individual files can become complex as the volume
of data grows.
 Limitations of Files

 Inflexibility: Files are often rigid in structure, making it

difficult to adapt to changing data requirements.
 Complexity in Relationships: Managing
relationships between different data entities is
challenging with files, leading to potential data
inconsistency.
 Performance Issues: As the amount of data increases,
accessing and processing files can become slow and
inefficient.
 Backup and Recovery: Ensuring consistent backups
and quick recovery of data from files can be complicated.
 Differences Between Files and Databases
Aspect Files Databases
Typically unstructured or Highly structured with defined
Structure semi-structured; data schemas, tables, and
organization varies. relationships.
High potential for redundancy Reduced redundancy through
Data Redundancy
across multiple files. normalization processes.
Advanced query capabilities
Limited access methods
Access Methods using SQL or other query
(sequential, random).
languages.
Built-in mechanisms for
Harder to enforce integrity and
Data Integrity enforcing data integrity (e.g.,
consistency.
constraints).
Enhanced security measures
Generally lacks robust security
Security (e.g., user authentication,
features.
access controls).

Can become unwieldy as data Designed to scale with large

Scalability
grows. volumes of data and users.
Automated backup and
Manual backup processes;
Backup and Recovery recovery options with
inconsistent recovery.
transaction logs.
Fundamental Objectives of a Database

 Less Redundancy
 Definition: Redundancy refers to the unnecessary
duplication of data within a database.
 Objective: Minimize data duplication to save storage
space and ensure data integrity. This is often achieved
through normalization processes, which organize data
into related tables.
Fundamental Objectives of a Database

 Consistency
 Definition: Consistency ensures that the data in the
database remains accurate and reliable.
 Objective: Maintain data integrity throughout all
transactions. This means that any data change must
adhere to predefined rules and constraints, ensuring that
the database remains in a valid state.
Fundamental Objectives of a Database

 ACID Properties
 Definition: ACID stands for Atomicity, Consistency,
Isolation, and Durability.
 Atomicity: Transactions are all-or-nothing; they either complete
fully or not at all.
 Consistency: Data must remain consistent before and after the
transaction.
 Isolation: Transactions are executed independently, ensuring
that concurrent transactions do not interfere with each other.
 Durability: Once a transaction is committed, it remains so even
in the event of a system failure.
 Objective: Ensure reliable processing of database
transactions.
Fundamental Objectives of a Database

 Multiuser and Concurrent Access

 Definition: Refers to the ability of multiple users to access
and manipulate the database simultaneously.
 Objective: Support concurrent access while maintaining the
integrity of the data. This is crucial for applications with many
users, such as online banking or e-commerce platforms.
 Multiple Views
 Definition: Different users might require different
perspectives or views of the data.
 Objective: Provide tailored views of the database to meet the
specific needs of different users while maintaining the
underlying data integrity. This can be achieved through the use
of views and permissions.
Fundamental Objectives of a Database

 Confidentiality
 Definition: Confidentiality refers to protecting sensitive
data from unauthorized access and ensuring that only
authorized users can view or manipulate specific data.
 Objective: Implement security measures such as user
authentication, encryption, and access controls to safeguard
sensitive information. This is crucial in maintaining user
trust and compliance with data protection regulations.
Fundamental Objectives of a Database

 Integrity
 Definition: Integrity refers to the accuracy, consistency,
and reliability of data within a database.
 Objective: Ensure data accuracy, consistency, and validity
through rules and constraints. This includes enforcing
referential integrity, which maintains valid relationships
between tables, and implementing auditing mechanisms to
track changes.
 Data reliability

 Data reliability is the degree to which data, and the

insights gleaned from it can be trusted and used for
decision-making.
 Data is trustworthy in a database when it is exactly
what the user thinks or believes it is.
 When there is a mismatch between the data in the
database and the user’s thinking about the data, the
data is said to be unreliable .
 So reliability deals with trust.
 Data accuracy

 Data accuracy refers to the correctness of values within a

dataset
 Data accuracy refers to the internal validity of the
information
 Data accuracy refers to the degree to which data is
correct, precise, and free from errors.
 The criteria for data accuracy are determined by data
creators, owners, and users.
 the formatting of dates for the United States is
MM/DD/YYYY (e.g., 08/01/1999) versus Europe. But it
would not be accurate in Europe, where the date format
standard is DD/MM/YYYY (01/08/1999);
 using the wrong format can create many problems.
 Database management system(DBMS)

 A set of programs that allows the

creation of data files and the alteration,
extractioin and deletion of data within
these files.
 Database management system(DBMS) function

 Data Storage, Retrieval, and Update

 Function: A DBMS provides mechanisms for storing data
efficiently, retrieving it quickly, and updating it as needed. This
includes managing how data is physically stored on disk and
accessed by users.
 Data Definition
 Function: The DBMS allows users to define the structure of the
database through Data Definition Language (DDL). This includes
creating, altering, and deleting tables and defining data types and
constraints.
 Data Manipulation
 Function: Users can manipulate data using Data Manipulation
Language (DML). This includes inserting, updating, deleting, and
querying data within the database.
 Database management system(DBMS) function

 Data Security
 Function: A DBMS provides security features to control user access
and protect data. This includes user authentication, access controls,
and encryption to safeguard sensitive information.
 Data Integrity
 Function: Ensures that the data remains accurate and consistent
through integrity constraints. This includes primary keys, foreign
keys, and other rules that enforce data validity.
 Data Backup and Recovery
 Function: A DBMS offers tools for backing up data and recovering
it in case of failures. This includes transaction logs and recovery
procedures to restore data to a consistent state.
 Database management system(DBMS) function

 Multiuser Access Control

 Function: Manages concurrent access to the database by multiple
users, ensuring that transactions are executed in a way that
maintains data integrity and consistency.
 Data Abstraction and Independence
 Function: Provides a level of abstraction that separates the user's
view of the data from its physical storage. This allows changes to the
database structure without affecting applications that use the data.
 Data Relationship Management
 Function: Handles relationships between different data entities,
allowing users to define and enforce relationships through foreign
keys and join operations.
 Database management system(DBMS) function

 Query Processing
 Function: Optimizes and processes queries to retrieve data
efficiently. The DBMS translates high-level queries into low-
level instructions that can be executed against the database.
 Performance Monitoring and Tuning
 Function: Monitors database performance and provides tools
for tuning and optimizing queries and database operations to
enhance efficiency.
 Transaction Management
 Function: Manages transactions to ensure the ACID
properties (Atomicity, Consistency, Isolation, Durability) are
maintained, which is crucial for reliable data processing.
 Database management system(DBMS): types

 common types of DBMSs are Relational and Object

Oriented DBMSs.
Relational DBMSs Object oriented DBMSs
Relational DBMSs are the most this type of DBMS is not very common
common type and are used to create at this time.
Relational databases
A relational database is one in which An Object Oriented DBMS is one in
the data is stored in the form of a which the data is stored in the form of a
table/s with rows and columns. Each set of interacting objects
table should contain data about one
particular subject alone.
Eg. Access., Oracle, MySQL, Ms Eg. ObjectDB, db4o, ZODB, Versant
FoxPro, Dbase, SQL Server. Object Database,
 Importance of databases

1 Streamlined Centralized data can help businesses

operations: streamline their operations by
eliminating the need for manual data
entry and duplication of effort. This can
lead to increased productivity, reduced
costs, and improved efficiency
2 Improved Accurate and up-to-date customer data
customer can help businesses improve customer
service: service by providing personalized
services and targeted marketing
campaigns. This can lead to increased
customer loyalty and satisfaction.
 Importance of databases

3 Facilitated Accurate and reliable data can help

informed businesses make informed decisions by
decision- identifying trends, patterns, and
making: insights. This can lead to strategic
decisions, increased profitability,
reduced costs, and improved
performance.
4 Enhanced data Database management systems provide
security: data encryption, user access controls,
and data backups that help protect data
from unauthorized access, theft, and
loss. This ensures that businesses
maintain the confidentiality and
integrity of their data, protecting both
their reputation and their bottom line.
 Questions

1. What are the various ways to keep and manage sale

transaction informations of the company?
2. Among the methods listed above, which one was first
used? Give a brief description
3. What are the limitations of the first method listed
above?
4. Describe each of the alternate methods listed in
question 1 and bing out advantages over the first
method as well as fundamental objectives of the
method
5. Differentiate between the first method and any
alternate methods listed in question 1
 Data organization in a relational database

 As earlier said, a A relational database is one in which

the data is stored in the form of a table/s with rows
and columns. Each table should contain data about
one particular subject alone.
 Each table is a collection of records or tuples or rows
decribing entities from the same set of entities
 Each column in the table is called a field or an attribute
and is a specific information or data item used to
decribe each entity in the table
 Each field has a specific data type
 A record or tuple is a group of related fields
 The number of tuples or records is the cardinality of
the table
 Example: Student Table

First Name Last Name Address Telephone

junior Tossam Nkwen 667-3429

joyceline Ngum Mankon 667-3429

Field
Records
 Primary Key

 Each table must contain a field that uniquely

identifies each record in a database.
 That field is the primary key field or attribute
 In some cases the primary key can be a combination
of two or more fields. It is a composite key
 In all cases, the primary key must be minimal:
means there is no proper subset of the key which can
also be used as key.
 In normal life persons are also assigned unique
identifiers e.g. birth certificate number, id card
number, credit card number, etc.
 Student Table Redone

Student First Last Name Address Telephone

Number Name
01 junior Tossam Nkwen 667-3429

02 joyceline Ngum Mankon 667-3429

Key field
 Foreign Key

 This is the primary key of a table placed into another

table, so as to create a link between the two tables.
STUDENT_SUBJECT
STUDENT Sub_No Stud_id
Stud_id studname 0795 102
101 Mantho 0777 101
102 Fritz 0795 103
103 Rochelle 0775 104
104 Sani 0795 101

Foreign Key field

Primary Key
 activity1

 Draw a table to record sale transactions of the

« gadget world » company
 Fill in the database designed ten(10) sale
transactions
 do the following:
 Give the primary key
 Give the cardinality of the table
 Give the attributes with their data types
 Sample table to be improved or customized

Sales Transactions
Customer
orderID Product Name Qty Sale Price orderDate
Name
Smartphone
1 John 1 499.99 2025-03-01
Model A
Headphones
1 John 2 49.99 2025-03-01
Model D
2 Jane Laptop Model B 1 899.99 2025-03-02
2 Jane Mouse Model J 1 19.99 2025-03-02
3 Alice Tablet Model C 2 299.99 2025-03-03
Smartwatch
3 Alice 1 199.99 2025-03-03
Model E
4 Bob Camera Model F 1 599.99 2025-03-04
5 Carol Monitor Model G 1 249.99 2025-03-05
5 Carol Keyboard Model I 2 29.99 2025-03-05
5 Carol Printer Model H 1 149.99 2025-03-05
 conclusion

 Database is at the heart of the information system of

every organization
 Reliable and accurate data can greatly support decision
making.
 A Database may be manually created and managed or
may be computerized using a DBMS
 A database must be designed carefully to minimize
redundancy in order to improve the accuracy and
minimize anomalies during insertions, updates, deletion.
 Relational DBMS and Object oriented DBMS are two
types of DBMS used to create and manage databases
 Home work

 Answer the following questions

1. What is a database?
2. What is a file? Give the limitations of files that led to the
creation of databases
3. Differentiate between files and database
4. List and explain the fundamental objective of databases