WHAT IS A DATABASE?

A database is a collection of information that's related to a particular subject or

purpose, such as tracking customer orders or maintaining a record. If your database
isn't stored on a computer, or only parts of it are, you may be tracking information
from a variety of sources that you have to coordinate and organize yourself.
For example, suppose the phone numbers of your suppliers are stored in various
locations: in a card file containing supplier phone numbers, in product
information files in a file cabinet, and in a spreadsheet containing order
information. If a supplier's phone number changes, you might have to update that
information in all three places. In a database, however, you only have to update
that information in one place the supplier's phone number is automatically updated
wherever you use it in the database.
Indeed a database is a collection of logically related data files which is integrated
and organized so as to provide single comprehensive file system. Database system
design is concerned with the problem of organizing the database, accessing data
on it, using it, updating it and providing output (reports or responses to file
interrogations) to a variety of users; e. g. a students’ file, Marks file, Payment’s
file. The Marks’ file will comprise records of marks scored by the students.

ELEMENTS OF A DATABASE FILE

A file is a collection of logically related records; e.g students file, stock file.
A record is a collection of logically related data fields; e. g Data relating to
students in a file. In a database table records are usually in rows.
A field is consecutive storage position of values. It is a unit of data within a
record e.g student’ s number, Name, Age. In a database table fields are usually in
columns.
S/NO NAME ADDRESS AGE
ABDULLAHI YUSUF 13, JOS ROAD 45
OLADELE AYINDE 5, IKEJA ROAD 35
NWACHUKWU OJI 24, ENUGU ROAD 40
A Database table showing fields and records
Database Management System (DBMS): refers to software that ‘builds,
manages and provides access to a database’. Examples are Microsoft Access,
MySQL, FoxPro, Clipper, Oracle, Foxbase.
FEATURES OF DBMS

1. Data Definition: Allows defining the structure of the database, including

tables, schemas and constraints.
2. Data Manipulation: Enables adding, updating, deleting and querying data
within the database.
3. Data Integrity: Maintains the accuracy and consistency of data through
constraints, validations, and referential integrity.
4. Concurrency control: Manages simultaneous access to data by multiple
users to ensure consistency and avoid conflicts.
5. Backup and Recovery: Provide mechanism for backing up data and
restoring it in case of system failures or data corruption.
6. Security
7. Query optimization
8. Data Independence
9. Scalability
10. Transaction Management

DBMS APPLICATIONS AND THEIR FUNCTIONS

1. MySQL:
11. Relational Database Management System (RDBMS): MySQL is a popular
open-source RDBMS, providing a robust plat

 form for managing relational databases.

 Data Storage and Retrieval: It allows efficient storage and retrieval of data using
structured query language (SQL).
 Concurrency Control: MySQL handles multiple users accessing the database
simultaneously, ensuring data consistency and integrity.
 Security: Provides user authentication, access control, and encryption features to
secure the database.
 Scalability: Designed to handle large datasets and high transaction volumes,
making it suitable for both small-scale and enterprise-level applications
2. MS Access:
 Desktop Database Management System: Microsoft Access is a desktop-based
DBMS designed for small to medium-sized projects.
 Graphical User Interface (GUI): Access provides a user-friendly interface for
designing databases, making it accessible to users with varying levels of technical
expertise.
 Rapid Application Development (RAD): Enables quick development of database
applications with forms, reports, and queries.
 Integration with Microsoft Office: Seamlessly integrates with other Microsoft
Office applications, facilitating data sharing and reporting.
 Suitable for Small Projects: Best suited for projects with moderate data
requirements and simpler database structures.
3. SQL (Structured Query Language):
 Query Language: SQL is a standardized language for managing and manipulating
relational databases.
 Data Definition Language (DDL): Defines the structure of the database, including
creating, altering, and deleting tables and other objects.
 Data Manipulation Language (DML): Manages the data itself, including inserting,
updating, and deleting records.
 Data Query Language (DQL): Retrieves data from the database using queries.
 Transaction Control Language (TCL): Manages transactions to ensure database
consistency and integrity.

TOOLS FOR DESIGN IN DBMS

In a Database Management System (DBMS), tools and menus play a crucial role in
facilitating the interaction between users and the database. The specifics can vary based
on the DBMS software, but here are common tools and menus you might encounter:
1. Query Tools:
 SQL Query Editor: Allows users to directly write and execute SQL queries to
interact with the database.
 Query Builders: Graphical tools that help users build complex SQL queries
without necessarily writing the code.
2. Database Design Tools:
 Table Designers: Tools for creating and modifying database tables, specifying
columns, data types, and constraints.
 Relationship Designers: Enable the definition of relationships between tables,
such as foreign keys.
3. Data Import/Export Tools:
 Data Import Wizards: Assist in importing data from external sources into the
database.
 Data Export Wizards: Facilitate exporting data from the database to external
formats.
4. Administration Tools:
 User Management: Tools for managing user accounts, permissions, and access
levels.
 Backup and Restore Tools: Enable the creation of database backups and the
restoration of data in case of failures.
5. Monitoring and Performance Tools:
 Database Monitoring Dashboards: Provide real-time insights into the performance
of the database, including resource usage and query execution.
 Performance Tuning Wizards: Help optimize database performance by suggesting
improvements based on usage patterns.
6. Security Tools:
 Security Managers: Allow administrators to set up and manage security features,
including roles, permissions, and encryption.
 Audit Tools: Track and log activities within the database for security and
compliance purposes.
7. Menu Structure:
 File Menu: Options for opening, saving, and managing database files.
 Edit Menu: Typically includes options for copying, pasting, and undoing actions
in the database design.
 View Menu: Configures the layout and visibility of different elements within the
DBMS interface.
 Tools Menu: Contains various tools and utilities for database management and
development.
 Help Menu: Provides access to documentation, support, and additional resources.
8. Object Explorer/Navigator:
 A panel that displays the structure of the database, including tables, views, stored
procedures, and other objects. Allows easy navigation and management.

DIFFERENT DATA TYPES IN DBMS

In the context of databases, different data types are used to define the kind of data
that can be stored in a column of a table. Each database management system
(DBMS) may have its own set of data types, but there are common ones that are
widely used.

These data types allow for the specification of what kind of data can be stored in
each column of a table, helping to ensure data integrity and efficiency in storage.
The choice of data types depends on the nature of the data being stored and the
requirements of the application. Here are some common data types:

1) Integer (INT): Represents whole numbers without a fractional component.

Example: 1, 42, -10.

2) Decimal/Numeric: Represents numbers with a fixed number of digits both

before and after the decimal point. Example: 3.14, -0.005.

3) Char/Character: Represents fixed-length character strings. The number

(N) specifies the maximum number of characters. Example: 'Hello', '123'.

4) Date: Represents a date without a time component. Example: '2022-01-01'.

5) Time: Represents a time without a date component. Example: '14:30:00'.

6) Datetime/Timestamp: Represents both date and time. Example: '2022-01-

01 14:30:00'.

7) Boolean/Bit: Represents binary data, often used for true/false or 0/1 values.
Example: 1 (true), 0 (false).

8) Clob (Character Large Object): Represents character data, often used for
storing large amounts of text.

9) Enum (Enumeration): Represents a set of predefined values. Example:

'Red', 'Green', 'Blue'.

10) Array: Represents an ordered collection of elements. Some databases

support arrays as a data type.

DIFFERENT OPERATIONS IN DBMS

Here are examples of common Database Management System (DBMS) operations:

1) Insert: Adds new records to a table. Example SQL: INSERT INTO

Employees (EmployeeID, FirstName, LastName, Salary) VALUES (1,
'John', 'Doe', 50000);
 2) Update: Modifies existing records in a table. Example SQL: UPDATE
Employees SET Salary = 55000 WHERE EmployeeID = 1;
3) Delete: Removes records from a table. Example SQL: DELETE FROM
Employees WHERE EmployeeID = 1;
4) Select (Query): Retrieves data from one or more tables. Example SQL:
SELECT FirstName, LastName, Salary FROM Employees WHERE Salary
> 50000;
5) Sorting (ORDER BY): Arranges the result set in a specified order.
Example SQL: SELECT FirstName, LastName, Salary FROM Employees
ORDER BY Salary DESC;
6) Filtering (WHERE): Selects a subset of records based on specified criteria.
Example SQL: SELECT FirstName, LastName, Salary FROM Employees
WHERE Salary > 50000;
7) Grouping (GROUP BY): Groups rows based on the values in one or more
columns. Example SQL: SELECT Department, AVG(Salary) as AvgSalary
FROM Employees GROUP BY Department;

FEATURES OF MS-ACCESS DBMS

1) Fields: Fields are the smallest units of data storage in a database. They
represent individual pieces of information, such as a person's name, age, or
address. Example: In a "Person" table, fields could include "Name," "Age,"
and "Address."

2) Records: A record is a collection of related fields that represent a single

instance or entry in a database table. It contains all the information about a
specific entity. Example: In a "Person" table, a record could be a row that
contains the name, age, and address of an individual.

3) Tables: Tables are structured collections of records, organized in rows and

columns. Each column represents a field, and each row represents a record.
A "Person" table could have columns like "Name," "Age," and "Address,"
with each row containing information about a different person.
4) Forms: Forms are user interfaces that provide a way to input or display data
in a database. They allow users to interact with the database by entering,
editing, or viewing records. A form might be used to input data for a new
person, and it would have fields like "Name," "Age," and "Address."

5) Views: are virtual tables derived from the result of a SELECT query. They
don't store the data themselves but provide a dynamic way to present a
subset or transformation of data from one or more tables. A view could be
created to display only the "Name" and "Age" columns from the "Person"
table, making it easier to see specific information without accessing the
entire table.