BASIC 6
Strand: PROGRAMMING AND DATABASES
Sub-strand: Introduction to Databases
A database is an organised collection of data stored in a structured way
so that data can be easily searched, updated, sorted, and retrieved.
Importance of Databases
1. Stores large amounts of data
2. Makes searching fast
3. Easy updating of records
4. Reduces data repetition
5. Keeps records organized
Examples of Databases
1. Hospital patient records
2. School student records
3. Library records
4. Bank records
Medical Patient Database
Stores patient information such as Patient ID, Name, Date of Birth,
Gender, Phone, Doctor, and Room.
Database Structure
A database consists of:
a. File/Table
b. Records
c. Fields
d. Data
File/Table
A table is a collection of records and fields arranged in rows and
columns.
Record
A record is one complete row of information about one person or object.
Field
A field is a column that stores one type of information.
Data
Data are the actual values stored in the database.
Rows and Columns
Rows represent records.
Columns represent fields.
Databases organize data for easy storage, retrieval, and management.
The diagram below shows the structure of a database
Types of databases
All databases have a system of files, records and fields. There are several
types of databases which include
1. flat-file database
2. Relational database
3. Object-oriented database
4. Hierarchical database
5. Network database
Flat-file database
A flat-file database is a type of database that stores data in a single table.
All of the data in the database is stored in this one place. The student
database example, that we looked at in the previous section, is a flat-file
database.
Problems with a flat-file database
1. Redundancy – The main weakness of this type of file is that the data
is repeated. In a relational database system this will not happen. Data
will only be stored once.
2. Data integrity – The user of the data must be able to rely on the
correctness and accuracy of the data. However, with a flat-file
database, data integrity is not reliable.
Advantages of a flat-file database
1. These files are easy to create
2. They are easy to use for small amounts of data.
Disadvantages of a flat-database
1. The computer has more data to read through, so accessing and
searching through them can be slow.
2. Data has to be repeated and that leads to inputting errors and
inconsistencies
3. File sizes can be large due to repeated data.
Relational databases
A relational database is one that contains two or more tables of data,
connected by links called relationships.
Notice that the table contains several items of data that are repeated
over and over again:
Eg. Class (5B), Tutor (Mr. Mensah), Room (56)
Note that every student in class 5B will have these items of data.
Multiple tables
The solution is to split the data. The repeated data is removed from the
main table and placed in a table of its own.
Note: We need to leave the class field in the main table as we still need to
know which class each student belongs to, but the data relating to each
class (Tutor, Room) can be removed.
We have solved the repeating-data problem but we seem to have created
a new problem, how do we know the name of each student’s tutor – it is
no longer in the student table?
Linking tables – relationships
We need to link the table together so that we can connect a student to a
specific tutor and room. We use this field to create a relationship (link)
between the two tables.
The primary key
A primary key field holds unique data. This means that no two records
are the same in this field. The primary key field is used to identify the
record.
The foreign key
When a key field from one table appears in a different table (e.g. the
Class field in the student table), we call this a foreign key. A foreign key is
a field or collection of fields in one table that uniquely identifies a row in
another table.
Homework
Examples of relational database
1. Customer - when a customer joins the library, a record is created. It
stores details such as their first name and surname and includes a
number. The book's ISBN (International Standard Book Number) is 978-
0-19-879911-7. This book was borrowed and also included in the
customer's library. When a customer borrows a book, the lending table
stores the customer's number ID and the books' number ID in a record.
The record could also include additional information such as when the
book was borrowed and when it's due back.
2. Book - each book in the library has a record. It stores details about the
book's title, author, and date. The book's ISBN (International Standard
Book Number) is 978-0-19-879911-7. This book was borrowed and also
included in the customer's library. When a customer borrows a book, the
lending table stores the customer's number ID and the books' number ID
in a record. The record could also include additional information such as
when the book was borrowed and when it's due back.
3. Lending - when a customer borrows a book, the lending table stores
the customer's number ID and the books' number ID in a record. The
record could also include additional information such as when the book
was borrowed and when it's due back.
The customer and book ID are both examples of key fields.
Advantages
1. The books’ details and the customer’s details need only to be entered
into the database once.
2. Mistakes are less likely to happen and if there was a mistake in the
customer’s record for example, correcting it will correct the
mistakes.
3. Duplication is avoided; this keeps the databases so file size down.
4. Details about books and customers are easily accessible using their
number IDs.
5. Queries can be performed and reports generated. Example: a list of
books a customer has borrowed since joining the library.
6. It is expensive to set up and manage it.
7. It is likely to have isolated databases where information cannot be
shared from one system to another.
8. It is risky to have duplicated databases where information cannot be
shared from one object-oriented database.
Disadvantage
1. It is expensive to set up and manage
2. It becomes more complicated as the data grows.
3. It is likely to have isolated databases where information cannot be
shared from one object-oriented database (OOBDB).
Object-Oriented Database (OODB)
An object-oriented database is a database in which information is
represented in the form of objects. An object consists of data and
methods, while classes group objects having similar data and methods.
Object oriented databases are different from relational databases which
are table-oriented. The Data is organized with similarity to object-
oriented programming concepts.
The Hierarchical Database Model
The hierarchical database model organizes data into a tree-like
structure, with a single root, to which all the other data is lined. The
hierarchy starts from the Root data, and expands like a tree, adding
child nodes to the parent nodes. In this model, a child node will only
have a single parent node. This mode powerfully describes many real-
world relationships like the content of a boo or recipes. In the
hierarchical model, data is organized into a tree-like structure with one-
to-many relationships between two different types of data, for example,
one department can have many courses, many professors and of course
many students.
Network database model
This is an extension of the Hierarchical model. In this model, data is
organized more like a graph, and is allowed to have more than one
parent node. In this database model, data is more related. Hence
accessing the data is also easier and faster. This database model was
used to map many-to-many data relationships. This was the most
widely used database model before Relational Model was introduced.
What is Database Management System (DBMS)?
Database management system (DBMS) is a software package designed
to define, manipulate and manage data in a database. A DBMS generally
manipulates the data itself, the data format, field names, record
structure and file structure.
Some other DBMS include MicrosoftAccess, MySQL, SQLServer, Oracle,
dBase, FoxPro, PostgreSQL and SQLite
Types of Data in Databases
Each piece of data in a database is called a field. A field refers to a single piece of data about
a person, object, or event. Some fields will refer to a numeric value, whilst others will refer
to character or textual data. When a table is created, each field must have a data type
assigned so that, the database knows how to process the data.
Common Data Types in Microsoft Access:
Term Meaning
AutoNumber The next number generated in sequence automatically by Access. Used
for key fields.
Currency Numeric field with 2 decimal places and automatically displays the
local currency symbol.
Date/ Time Stores both the date and time.
Hyperlink Special text field to represent hyperlinks.
Number Only numerical data (decimal places can be specified if required).
Text 255 characters used to store letters, numbers, and symbols.
Yes/ No Boolean data that represents true or false.
Fundamental Database Concepts
Structured Query Language (SQL):
SQL is a standard language used to communicate with Relational Database Management
Systems (RDBMS). It allows users to create, drop, and alter database objects such as tables,
procedures, views, and synonyms. SQL is also used to retrieve, insert, update, and delete
records.
Normalization:
Normalization is the process of organizing data to reduce redundancy. It involves dividing a
database into two or more tables and defining relationships between them. The main
normal forms are 1NF, 2NF, and 3NF, with others like BCNF and 4NF.
Denormalization:
Denormalization is the opposite of normalization. It combines two or more tables into one
to improve database performance.
Data Manipulation Language (DML):
DML is used for selecting, inserting, deleting, and updating data.
Examples of DML Commands:
SELECT – retrieves data.
INSERT – adds new records.
DELETE – removes records.
UPDATE – modifies existing records.
MERGE – updates or inserts records based on conditions.
Data Definition Language (DDL):
DDL defines structural changes to database objects. It allows users to create, alter, and
drop schema objects including databases and users.
ALTER: Alter will let you modify existing database objects from your database.
CREATE: It will let you create database objects like Table, View, Synonym, Index, etc. in a
database.
DROP: It will command and drop the database objects like Table, View, Synonym, Index,
etc. from a database.
TRUNCATE: Deletes all the data from schema objects without removing the structure of
these objects. Truncate generates no undo logs, therefore it is faster than a delete but the
transaction cannot be rolled back. Also, TRUNCATE does not invoke delete triggers.
Programming Languages and Their Use
Programs are our way of telling the computer what to do, how to do it and when to do it.
This enables a single computer to perform many different types of task. A computer can be
used to stream videos, write reports, provide weather forecasts and many, many other jobs.
An example of a simple task that can be performed by a computer is the provision of
multiplication tables test.
A computer program is a list of instructions that enables the computer to perform a specific
task. Computer programs can be written in highlevel languages or lowlevel languages,
depending on the task to be performed and the computer to be used. Most programmers
write programs in highlevel languages.
HighLevel Languages
Highlevel languages enable a programmer to focus on the problem to be solved and require
no knowledge of the hardware and instruction set of the computer that will use the
program. Many highlevel programming languages are portable and can be used on different
types of computer. Highlevel languages are designed with programmers in mind;
programming statements are easier to understand than those written in a lowlevel
language. This means that programs written in a highlevel language are easier to:
• read and understand as the language used is closer to human language
• write in a shorter time
• debug at the development stage
• maintain once in use
Example
Sum:= FirstNumber + SecondNumber
There are many different high-level programming languages in use today including scratch,
C++, C#, Delphi, Java, JavaScript, Pascal, Python, VisualBasic (VB) and many more.
The traditional introduction to programming in any language is to display the words ‘Hello
World’ on a computer screen. The programs look very different
Low-level languages
Low-level languages relate to the specific architecture and hardware of a particular type of
computer. Low-level languages can refer to machine code, the binary instructions that a
computer understands, or an assembly language that needs to be translated into machine
code.
Before Starting Programming
You have to install your programming environment that is: Integrated Development
Environment (IDE).
IDE
Integrated Development Environment (IDE)
This software application that provides comprehensive facilities to computer programmers
for writing, debugging and maintaining programs. Most programming languages uses
different IDE.
Assembly languages
Few programmers write programs in assembly language. Those programmers who do that,
do so for the following reasons:
• to make use of special hardware
• to make use of special machine-dependent instructions
• to write code that doesn't take up much space in primary memory
• to write code that performs a task very quickly.
Machine code
Programmers do not usually write in machine code as it is difficult to understand and it can
be complicated to manage data manipulation and storage.
Introduction to Algorithm and Programming.
Steps in Problem Solving
• First produce a general algorithm (one can use pseudocode)
• Refine the algorithm successively to get step by step detailed algorithm that is very
close to computer language.
• Pseudocode is an artificial and informal language that helps programmers develop
algorithms.
• Pseudocode is very similar to everyday English.
Defined Algorithm structure
• Define the input, process and output steps
• Convert each steps into pseudocode.
Pseudocode
Example: Write an algorithm to determine the area of a rectangle
Get input = Read the sizes of the rectangle
Process = Determine the required arithmetic to use
Get output = Print the final answer
Algorithm
Step 1: Input the sizes Width (W), Length (L)
Step 2: Area (LxW)
Step 3: PRINT the answer
Before Starting Programming
Don't forget your algorithm
You have to install your programming environment that is: Integrated Development
Environment (IDE).
What is IDE
Integrated Development Environment (IDE)
This software application that provides comprehensive facilities to computer programmers
for writing, debugging and maintaining programs. Most programming languages uses
different IDE.
An IDE tools includes a text editor, a project editor, a tool bar, and an output viewer. IDE’s
can perform a variety of functions. It comprises of write code, compile code, debug code,
and monitor resources. Examples of IDEs include NetBeans, Eclipse, IntelliJ and Visual
studio.