Computer Studies / Data Processing · Lesson 6

Database models, relational design, Access, SQL and database security

JAMBWAECNECO

What the syllabus asks

JAMB: Section C 3 Database package (definition and examples: dBase, FoxBase, MS Access, Oracle; basic terms: file, record, field, key, form, table; database organisation: hierarchical, network, relational; application areas; features of database format in MS Access; creating a database: define structure, field types, enter and save data; operations: searching, modifying, sorting, reporting, selecting,…

WAEC: Database (definition, examples, basic terms, types of organisation, features of format, steps in creating, basic operations)

NECO: Database (definition, examples, basic terms, types of organisation, features of format, steps in creating, basic operations)

What you will learn

Define a database and DBMS and give examples of database packages.
Define the basic database terms.
Describe hierarchical, network and relational models.
Explain entities, attributes, primary keys, foreign keys and relationships.
State the steps in creating a database in MS Access and choose field types.
Carry out operations: search, sort, select, insert, modify, delete, report.
Write and interpret simple SQL queries and describe QBE.
State uses of databases in different organisations.
Explain database security and the role of the DBA.
Explain parallel and distributed databases.

Key points

Database: organised collection of related data; DBMS: software to manage it (Access, Oracle, MySQL, dBase, FoxBase).
Advantages: less redundancy, consistency, sharing, security, easy retrieval; disadvantages: cost, complexity, single point of failure.
Terms: table, field/column/attribute, record/row/tuple, key, form, query, report.
Models: hierarchical (tree, one parent), network (many parents), relational (linked tables).
Primary key: unique, not null. Foreign key: refers to another table’s PK. Composite key: two or more fields.
Relationships: 1:1, 1:M, M:N (resolved by a linking table); referential integrity enforces valid links.
Access field types: Short Text, Long Text, Number, Currency, Date/Time, Yes/No, AutoNumber, Attachment.
Create database: blank database → name → Design View fields and types → PK → save → enter data → relationships.
Operations: search, sort, filter/select, insert, modify, delete, report.
SQL: SELECT … FROM … WHERE … ORDER BY; COUNT, AVG, MAX, MIN; JOIN; INSERT, UPDATE, DELETE (always with WHERE).
QBE builds queries in a grid; Access generates the SQL.
Security: access control, passwords, permissions, encryption, backups, audit logs; DBA manages all of these.
Distributed: data at several sites; parallel: many processors for speed.
Normalisation removes redundancy by splitting tables linked by keys.
Integrity: entity, referential and domain rules.

Start here: Databases and database management systems

A database is an organised collection of related data stored so it can be easily accessed, managed, updated and shared. A school may keep students, staff, subjects, results and fees in one database instead of separate notebooks.

A database management system (DBMS) is the software that creates and manages the database and lets users store, retrieve, update and protect data. Examples: Microsoft Access, Oracle, MySQL, Microsoft SQL Server, PostgreSQL, LibreOffice Base, and older dBase and FoxBase/FoxPro.

Advantages of a database over separate files

Reduced redundancy: a student’s address is stored once, not in every department’s file.
Consistency and integrity: one update corrects it everywhere; rules reject invalid data.
Sharing: many users can use the data at once.
Security: passwords and permissions control who sees what.
Easy retrieval: queries answer questions quickly.
Backup and recovery are centralised.
Data independence: programs are less affected when storage changes.

Disadvantages

Cost of software, hardware and trained staff; complexity; if the central database fails or is hacked, everything is affected; needs reliable power and backups.

Application areas

Sector	Example of database use
Education	Student records, results, JAMB registration, library catalogues
Health	Patient records, drug stock, laboratory results
Banking	Customer accounts, BVN, transactions
Government	NIN identity database, voters’ register, tax records, vehicle licensing
Commerce	Stock control, customers, online shops
Agriculture	Farmer registers, input distribution, crop yields
Home/office	Contacts, inventories, mailing lists

Continue this lesson in the free app →

Practice questions

1. A database is

an organised collection of related data
a single document
a spreadsheet formula
an operating system

Show answer

A: an organised collection of related data
Managed by a DBMS.

2. In a relational database, data is stored in

trees only
networks of pointers only
paragraphs
tables

Show answer

D: tables
Rows are records; columns are fields.

3. A primary key must be

blank
a picture
unique for each record
repeated

Show answer

C: unique for each record
It identifies each row.

4. Which database object prints formatted output?

Macro
Report
Table
Form

Show answer

B: Report
Reports present data for printing.

5. A query is used to

retrieve records that meet given conditions
draw pictures
format disks
type letters

Show answer

A: retrieve records that meet given conditions
E.g. all students in SS3.

Key words

Database: An organised collection of related data.
DBMS: Database management system: software for creating, maintaining and using databases.
Table: A set of rows and columns holding data about one entity.
Entity: A person, thing or event about which data is stored, e.g. Student.
Attribute: A property of an entity; a column/field in a table.
Tuple: A row (record) in a relational table.
Primary key: A field (or fields) with a unique value identifying each record.
Foreign key: A field in one table that refers to the primary key of another table.
Composite key: A primary key made of two or more fields together.
Relationship: A link between tables based on matching key values.
Form: A screen layout for entering and viewing records one at a time.
Query: A request to retrieve or change data that meets given conditions.

Learn Database models, relational design, Access, SQL and database security step by step, free.
The Cara's Tips app teaches every topic from the very basics, with quick checks, worked examples and exam practice. It works offline.
Open this lesson in the free app Download for Android