UNIT-9 DATABASES

DEFINE: DATABASE

A database is a
structured collection of
data that allows people
to extract information
in a way that meets
their needs. The data
can include text,
numbers, pictures;
anything that can be
Why are databases useful?

Databases prevent problems occurring

because:

 if any changes or additions are made it only

has to be done once – data is consistent
 the same data is used by everyone
 data is only stored once in relational
databases which means no data duplication.
Applications of databases
 To store information about people, for

instance:
 patients in a hospital

 pupils at a school.

 To store information about things, for

instance:
 cars to be sold

 books in a library.

 To store information about events, for

instance:
 hotel bookings

 results of races.
 Fields and records – the building blocks
for any database

 Inside a database, data is stored in tables, which

consists of many records . Each record consists
of several fields.

 The number of records in a table will vary as new

records can be added and deleted from a table as
required.

 The number of fields in a table is fixed so each

record contains the same number of fields.

 An easy way to remember this is: each record is a

row in the table and each field is a column in the
• Data types: A field in a database table
can have its own attribute or property.
For example, you can enter data as text,
number, date/time, currency and so on.

• These attributes are defined as data

types.
• A data type helps the computer to identify
similar data for the purpose of sorting,
searching and calculation.
• Mostly data types may be defined
differently in various database systems.
Some common data types are as given in
TYPES OF DATABASES
• You can use different software
applications like Microsoft Access,
Oracle, MySQL, LibreOffice Base to
create a database.

• These applications are also called

Database Management System
(DBMS) applications as they act as an
interface between the user and the
database.

• A database can be of two types — Flat-

file (single-table) database and
1. FLAT-FILE DATABASE (SINGLE TABLE
DATABASE)

 A flat-file database stores simple data

formats. They store only a single table
of data. The
information given by a record is
complete with a fixed number of
fields. This database is not
related to any other table or
database.
2. RELATIONAL DATABASE
 When a database can have mutiple
related tables, it is referred to as a
relational database. When the tables are
linked, the user can work on complex
data structures having multiple tables,
 This method of storing data is not
efficient and may lead to problems like
data duplication, data redundacy and
input errors.
For example, in a student database, you can create
a Student
Information table and a Student Examination table.

In the Student Information table, Student ID will be

unique for
each student record. It thus defines the record of
each student.

This special field is defined as the Primary key for

that table.

Now, when you link the Student Information table and

Student Examination table, the two tables can have a
common field, that is Student IDs.

A relational database does not allow data duplication.

QUERIES?
PRIMARY KEYS
 As each record within a table contains
data about a single item, person, or
event, it is important to be able to
uniquely identify this item. In order to
reliably identify an item from the data
stored about it in a record there needs
to be a field that uniquely identifies the
item. This field is called the primary
key.
 In a Student table, Index no. is the
primary key, which identifies each
record of thePRIMARY
tableKEY
uniquely.

Index Name Age Grade Address

no.
1101 Ali 15 10 Venom

1102 Nadhir 14 9 diamond

1103 Ameen 12 7 Sinarest

DATA VALIDATION
 Data validation rules: these are some rules which
help the user to know that the data being entered in
a database is correct.
 There are certain rules that are followed when data

is typed into a database system called the data

validation rules.
For example, a phone number does not contain a
character or an alphabet.
These
 rules help in accepting the data which are as per

the set format.

 The data entered into a database can sometimes be

entered incorrectly. It can be invalid, inconsistent or

incomplete.
CREATING A TABLE
 By default, the first field is marked as a
primary key (shown by a key icon).
 You can change the primary key by
right-clicking on the desired Field name
and selecting the Primary key option
or
 Just select the Field and click on the
Primary key option under the Design
tab.
You can add a validation rule to a
field using the Field Properties
Pane (Figure 9.11).

Once the data is entered, a

validation check can also be
performed. To do so, select the
Design View Design tab
Click on Test Validation Rules.
USING QUERIES
 Queries are used for working with data in a database.
You can search data by certain criteria such as
“searching the records in which the students have
participated before”. You will get the matching
records in the database.
 Structured Query Language (SQL) is mainly used
for creating and running a query in order to search,
sort or manipulate data.
 However, some DBMS systems like Access also allow
the user to create a query using a Graphical User
Interface (GUI). This is sometimes referred to as
query-by-example.
SQL
Structured Query Language (SQL) is the
standard query language for writing scripts
to obtain useful information from a database.
We will be using SQL to obtain information
from single-table databases.
This will provide a basic understanding of
how to obtain and display only the
information required from a database. SQL is
pronounced as es-queue-el.
For example, somebody needing to
visit a patient would only require the
ward number and the bed number of
that patient in order to find where they
are in the hospital. Whereas a
consultant could need a list of the
names of all the patients that they
care for.
SQL SCRIPTS

An SQL script is a list of SQL commands that

perform a given task, often stored in a file so
the script can be reused.

You can write scripts using SQL commands in

Access. There are many other applications
that also allow you to do this –MySQL and
SQLite are freely available ones.
You will need to be able to understand
and identify the output from the
following SQL statements.
Only the SELECT and FROM commands are
mandatory in an SQL script. All other commands are
optional.

A SELECT statement takes the form:

SELECT Field1, Field2, Field3, etc. – this specifies

the individual fields (columns) to be shown.

SELECT * – this specifies that all fields (columns) are

to be shown.

A FROM statement takes the form:

FROM TableName – this specifies the table to use.

A WHERE statement takes the form:

WHERE Condition – this specifies the condition to
Conditions also require operators to compare
values from fields.
A SUM statement takes the form:

SELECT SUM (Field) – this specifies the

field (column) for the calculation.
The field should be integer or real.

A COUNT statement takes the form:

SELECT COUNT (Field) – this specifies

the field (column) to count if the given
criterion is met.
Example 1: Display consultant’s patients
For example, the following SQL command
for the PATIENT single-table database
would provide a list of all Mr Smith’s
patients showing the hospital number,
first name and family name for each of
his patients.

SELECT HospitalNumber, FirstName,

FamilyName

FROM PATIENT

WHERE Consultant = 'Mr Smith';

EXAMPLE 2: DISPLAY CONSULTANT’S PATIENTS IN ALPHABETICAL
ORDER
FIND THE NAME AND RATING FROM THE MOVIE TABLE
IF THE RATING >8.4 .
SELECT Name, Rating
FROM Movie
WHERE Rating>8.4;

The results of this query would be:

FIND THE NAME AND RATING FROM THE MOVIE TABLE
IF THE GENRE IS ‘FAMILY’ & CERTIFICATE IS ‘U’.

SELECT Name, Rating

FROM Movie
WHERE Genre=”Family” AND
Certificate=”U”;

The results of this query would be:

A database table, Dogs2023, is used to keep a
record of all dogs registered at a veterinary
practice.
Complete the structured query language (SQL)
to return the name and breed of all Female
dogs.

SELECT Name, Breed

________ Dogs2023
WHERE _______;
WRITE AN SQL QUERY TO FIND OUT THE NAME AND
BREED OF ALL DOGS AGED 10 YEARS OLD OR OLDER

SELECT Name, Breed

FROM Dogs2023
WHERE Age>=10;
SELECT Name, Genre, Certificate, Rating
FROM Movie
ORDER BY Name ASC;
The results of this query would be:
SELECT SUM (QuantityInStock)
FROM ProductTable ;
This query will add up all of the numbers in the
QuantityInStock field
The result of this query would be 25
EXAMPLE:
 SELECT COUNT(*)
 FROM ProductTable
 WHERE Price>2;
 This query will count all the records
with a price greater than 2
 The result of this query would be 3
 PAST PAPER QUESTION

In a hospital a patient database is to be created

with the given fields:

Assuming you have to maintain the patient database,

create a database with at least five records
and define the following.
a) Primary key
b) Data type for each field
Patient First Second Contac Blood Date Of Age Doctor
ID Name Name t Group Birth Consult
Details ed
P11 Ali Ahmad 987654 O- 12/9/2002 20 Eddly

P21 Maqsoo Rasheed 789654 A+ 14/2/2000 23 Wahem

d
P13 Rahil Ali 954362 A- 22/5/2003 20 Qassal

P15 Arusha Mohame 768564 O+ 30/5/2000 23 Rahem

m d
P12 Muneer Talha 709856 A+ 21/3/2002 21 Georgr