⏱ 4 min read
Databases are part of both the Computer Operator Paper II and the practical test (MS Access). This note covers database concepts, DBMS, keys, relationships, normalisation and the basic SQL commands asked in exams.
1. Basic terms
| Term | Meaning |
|---|---|
| Data | Raw facts such as names and numbers. |
| Information | Processed, meaningful data. |
| Database | An organised collection of related data. |
| DBMS | Software to create and manage databases (e.g. MS Access, MySQL, Oracle, SQL Server). |
| Table (relation) | Data arranged in rows and columns. |
| Record (tuple) | One row of a table. |
| Field (attribute) | One column of a table. |
2. Keys
- Primary key: uniquely identifies each record; cannot be empty or duplicate.
- Foreign key: a field that refers to the primary key of another table.
- Candidate key: any field that could serve as a primary key.
- Composite key: a key made of two or more fields.
3. Relationships
- One-to-one (1:1): one citizen – one citizenship number.
- One-to-many (1:M): one department – many employees.
- Many-to-many (M:N): many students – many subjects (needs a linking table).
4. Normalisation
Normalisation organises tables to reduce data redundancy (repetition) and anomalies. Common forms are 1NF (atomic values), 2NF (no partial dependency) and 3NF (no transitive dependency).
5. Basic SQL commands
| Type | Commands | Use |
|---|---|---|
| DDL (Data Definition) | CREATE, ALTER, DROP, TRUNCATE | Define or change structure |
| DML (Data Manipulation) | INSERT, UPDATE, DELETE | Change data |
| DQL (Query) | SELECT | Retrieve data |
| DCL (Control) | GRANT, REVOKE | Give or remove permissions |
| TCL (Transaction) | COMMIT, ROLLBACK | Save or undo transactions |
Example: SELECT name, salary FROM employee WHERE salary > 30000 ORDER BY name;
6. Advantages of a DBMS
- Reduces redundancy and inconsistency.
- Allows data sharing among users.
- Improves security through permissions.
- Supports backup and recovery.
- Maintains data integrity with rules and constraints.
7. Normalisation: a worked example
Suppose an office keeps one big table of staff training:
| Staff_ID | Name | Section | Trainings |
|---|---|---|---|
| 101 | Ram | Accounts | Excel, Nepali typing |
| 102 | Sita | Admin | Excel |
- Problem (not 1NF): the Trainings cell holds two values. To find everyone trained in Excel, you must search inside text.
- 1NF: put one value per cell, with one row per staff member per training: (101, Ram, Accounts, Excel), (101, Ram, Accounts, Nepali typing), (102, Sita, Admin, Excel).
- New problem: Ram’s name and section are now repeated. If his section changes, every row must be edited, or the data becomes inconsistent.
- 2NF/3NF: split into a Staff table (Staff_ID, Name, Section) and a Staff_Training table (Staff_ID, Training). Staff_ID is the primary key in Staff and a foreign key in Staff_Training.
Now each fact is stored once, and the two tables are linked by Staff_ID.
8. SQL examples you should be able to write
| Task | SQL |
|---|---|
| Create a table | CREATE TABLE staff (id INT PRIMARY KEY, name VARCHAR(50), salary INT); |
| Add a record | INSERT INTO staff (id, name, salary) VALUES (1, ‘Ram’, 35000); |
| Show all records | SELECT * FROM staff; |
| Show names with salary above 30,000 | SELECT name FROM staff WHERE salary > 30000; |
| Sort by salary, highest first | SELECT * FROM staff ORDER BY salary DESC; |
| Count staff | SELECT COUNT(*) FROM staff; |
| Increase one person’s salary | UPDATE staff SET salary = 40000 WHERE id = 1; |
| Remove one record | DELETE FROM staff WHERE id = 1; |
Careful: UPDATE or DELETE without WHERE changes or removes every row.
9. MS Access basics for the practical test
| Object | Use |
|---|---|
| Table | Stores the data in rows and columns |
| Query | Finds, filters or calculates data from tables |
| Form | A screen to enter or view data easily |
| Report | A formatted printout of data, with totals |
| Macro | Automates simple repeated tasks |
| Module | Holds VBA program code |
Common Access data types: Short Text, Long Text, Number, Date/Time, Currency, AutoNumber (often used for the primary key) and Yes/No.
10. How this topic is asked in the exam
- Objective: full forms (DBMS, SQL, DDL, DML), types of keys, the relationship types, and which command belongs to which group.
- Short answer: “What is normalisation? Why is it needed?” or “Differentiate between primary key and foreign key.”
- Practical: create a table in MS Access with a primary key, enter records, and make a query or report, as in section 9.
📌 Key points to remember
- Database = organised related data; DBMS = software to manage it.
- Row = record (tuple); column = field (attribute).
- Primary key is unique and not null; foreign key links tables.
- Normalisation reduces redundancy (1NF, 2NF, 3NF).
- DDL: CREATE/ALTER/DROP; DML: INSERT/UPDATE/DELETE; SELECT retrieves data.
- GRANT/REVOKE control permissions; COMMIT/ROLLBACK handle transactions.
📝 Quiz
Test yourself: Database (DBMS) and SQL Basics for Computer Operator
50 questions on Basic terms, Normalisation and Advantages of a DBMS. Each attempt picks 25 at random.
Loading quiz...