←
Computer Literacy and Digital Awareness · Chapter 7

Databases and Programming Concepts

What to remember

  • A database is an organised collection of data; a DBMS is the software that creates, stores, protects and retrieves it. An RDBMS keeps data in related tables of rows and columns.
  • A primary key uniquely identifies each row and cannot be NULL or repeated. A foreign key links one table to the primary key of another.
  • A program is first planned with an algorithm and a flowchart, then written in a programming language. Machine language is the only language a computer runs directly; compilers and interpreters translate the rest.

Data, database and DBMS

Data means raw facts such as a name, a number or a date. Information is data that has been processed and has meaning. A database is a structured collection of related data kept together so that many users can use it.

A Database Management System (DBMS) is the software between the user and the database. It lets users add, change, delete and search data. It also controls who may see the data, keeps it safe, and avoids duplicate entries (redundancy).

Main advantages of a DBMS over plain files:

  • Less duplicate data and better consistency.
  • Data can be shared by many users at the same time.
  • Security through passwords and permissions.
  • Backup and recovery after a failure.
  • Data independence: programs do not break when storage changes.

Common DBMS products include MySQL, Oracle, Microsoft SQL Server, PostgreSQL and Microsoft Access. Microsoft Access is a desktop DBMS; the others are used for large systems.

Relational model: tables, rows and columns

A Relational DBMS (RDBMS) stores data in tables. The idea was proposed by E. F. Codd.

TermMeaning
Table (relation)Data arranged in rows and columns
Row (record, tuple)One complete entry, e.g. one student
Column (field, attribute)One type of detail, e.g. Name
DegreeNumber of columns in a table
CardinalityNumber of rows in a table
DomainThe set of allowed values for a column

Other database models you may meet: hierarchical (tree shape, parent and child), network (many-to-many links), object-oriented, and NoSQL for very large, flexible data.

Keys

Keys identify rows and connect tables.

KeyWhat it does
Primary keyUniquely identifies a row. No duplicates, no NULL. One per table.
Candidate keyAny column (or set) that could serve as the primary key.
Alternate keyA candidate key that was not chosen as primary.
Composite keyA key made of two or more columns together.
Foreign keyA column that refers to the primary key of another table. It may repeat and may be NULL.
Super keyAny set of columns that uniquely identifies a row.

Example: in a Student table, Roll_No can be the primary key. In a Marks table, Roll_No appears again as a foreign key and links each mark to a student. This link gives referential integrity: you cannot record marks for a student who does not exist.

Normalisation is the step-by-step process of arranging tables to remove redundancy. The common forms are 1NF, 2NF, 3NF and BCNF. A data dictionary stores details about the data, such as names and types of fields.

SQL basics

SQL stands for Structured Query Language. It is the standard language to work with an RDBMS. It is not case-sensitive for commands.

GroupFull formCommandsPurpose
DDLData Definition LanguageCREATE, ALTER, DROP, TRUNCATEBuild or change structure
DMLData Manipulation LanguageINSERT, UPDATE, DELETEChange the data
DQLData Query LanguageSELECTRead data
DCLData Control LanguageGRANT, REVOKEGive or take back permission
TCLTransaction Control LanguageCOMMIT, ROLLBACK, SAVEPOINTManage transactions

Simple examples:

  • `SELECT name FROM student WHERE marks > 50;` shows the names of students with more than 50 marks.
  • `INSERT INTO student VALUES (1, 'Ravi');` adds a row.
  • `UPDATE student SET marks = 60 WHERE roll = 1;` changes a value.
  • `DELETE FROM student WHERE roll = 1;` removes rows.

Useful clauses: WHERE filters rows, ORDER BY sorts, GROUP BY groups rows, HAVING filters groups. Aggregate functions are COUNT, SUM, AVG, MIN and MAX. DROP removes a whole table; DELETE removes rows only; TRUNCATE empties all rows quickly.

A transaction is a group of steps that must all succeed or all fail. The ACID properties are Atomicity, Consistency, Isolation and Durability.

Algorithms and flowcharts

An algorithm is a finite, step-by-step set of instructions to solve a problem. A good algorithm has clear input, clear output, definite steps, and it ends after a limited number of steps.

A flowchart is a picture of an algorithm using standard symbols.

SymbolMeaning
Oval (rounded)Start or End
ParallelogramInput or Output
RectangleProcess or calculation
DiamondDecision (Yes/No)
ArrowDirection of flow
CircleConnector

Pseudocode is an algorithm written in simple English-like statements. The three basic control structures are sequence (one after another), selection (if-else, a choice) and iteration (loop, repeat). A loop that never ends is an infinite loop. Debugging means finding and fixing errors (bugs).

Types of errors: a syntax error breaks the rules of the language and is caught before running; a logical error gives wrong output although the program runs; a runtime error stops the program while it is running.

Programming language types

GenerationLanguageKey point
1GLMachine languageBinary 0s and 1s; runs directly; very fast, hard to write
2GLAssembly languageShort codes (mnemonics); needs an assembler
3GLHigh-level languageClose to English; needs compiler or interpreter; e.g. C, C++, Java, Python
4GLVery high-levelTask-oriented, e.g. SQL, report tools
5GLAI and constraint basedSolve problems from given conditions, e.g. Prolog

Language translators:

  • Assembler: assembly code to machine code.
  • Compiler: translates the whole program at once and lists all errors together. The output runs fast later.
  • Interpreter: translates and runs line by line; stops at the first error. Python and BASIC are usually interpreted.

Source code is what a programmer writes. Object code is the machine code a compiler produces. Low-level languages depend on the machine; high-level languages are portable. Procedural languages (C) use functions; object-oriented languages (Java, C++) use objects, classes, inheritance and polymorphism. HTML is a markup language, not a programming language. A variable stores a value that may change; a constant does not change.

More on normalisation, integrity and storage

Normalisation breaks a large table into smaller related tables. First normal form (1NF) means every cell holds one value only and there are no repeating groups. Second normal form (2NF) removes partial dependence on part of a composite key. Third normal form (3NF) removes dependence on non-key columns. The aim is less redundancy and fewer update errors.

Integrity rules keep data correct:

  • Entity integrity: the primary key is never NULL.
  • Referential integrity: a foreign key value must match an existing primary key value.
  • Domain integrity: values must stay within the allowed type and range.

An ER (entity-relationship) diagram shows tables as entities and links them with relationships. Relationships can be one-to-one, one-to-many or many-to-many. For example, one teacher can teach many students, so teacher to student is one-to-many.

A DBMS has a schema, which is the design of the database, and an instance, which is the data in it at one moment. The Database Administrator (DBA) controls access, backup and tuning. A query is a request to the database. An index works like the index of a book and speeds up searching. A view is a saved query that shows chosen data as a virtual table. NULL means a missing or unknown value; it is not the same as zero or a blank space.

Exam traps

  • Primary key vs foreign key: primary is unique and never NULL; foreign may repeat.
  • DELETE vs DROP vs TRUNCATE: rows only, whole table, empty all rows.
  • Compiler vs interpreter: whole program at once vs line by line.
  • Degree is the count of columns; cardinality is the count of rows.
  • A DBMS is software; a database is the stored data.
  • Assembly language is low-level, but it is not machine language.
  • Diamond is a decision; parallelogram is input/output; oval is start/end.
  • GRANT and REVOKE are DCL, not DML.

One-liners

  • 1. DBMS manages the database; RDBMS stores data in related tables.
  • 2. E. F. Codd proposed the relational model.
  • 3. One table can have only one primary key.
  • 4. SQL stands for Structured Query Language.
  • 5. SELECT is used to read data from a table.
  • 6. WHERE filters rows; HAVING filters groups.
  • 7. COUNT, SUM, AVG, MIN and MAX are aggregate functions.
  • 8. An algorithm must end after a finite number of steps.
  • 9. Flowchart decisions are shown in a diamond.
  • 10. Machine language is the only language the CPU runs directly.
  • 11. A compiler shows all errors after reading the whole program.
  • 12. MySQL, Oracle and PostgreSQL are examples of RDBMS software.

Practice questions

  1. Which software is used to create, store and retrieve data in an organised way?

    1. DBMS
    2. Assembler
    3. Compiler
    4. Browser
    Answer

    A. DBMS

    A DBMS manages databases.

  2. A row in a table is also called a:

    1. Field
    2. Domain
    3. Record
    4. Attribute
    Answer

    C. Record

    Row = record = tuple.

  3. A column in a table is also called a:

    1. Tuple
    2. Field
    3. Cardinality
    4. Record
    Answer

    B. Field

    Column = field = attribute.

  4. The number of columns in a table is called its:

    1. Domain
    2. Schema
    3. Cardinality
    4. Degree
    Answer

    D. Degree

    Degree counts columns; cardinality counts rows.

  5. The number of rows in a table is called its:

    1. Cardinality
    2. Domain
    3. Key
    4. Degree
    Answer

    A. Cardinality

    Cardinality counts rows.

  6. A key that uniquely identifies each row and cannot be NULL is the:

    1. Super key
    2. Alternate key
    3. Primary key
    4. Foreign key
    Answer

    C. Primary key

    Primary key is unique and not NULL.

  7. A key made of two or more columns together is called a:

    1. Null key
    2. Alternate key
    3. Foreign key
    4. Composite key
    Answer

    D. Composite key

    Composite key uses multiple columns.

  8. A candidate key that is not chosen as the primary key is called an:

    1. Composite key
    2. Alternate key
    3. Foreign key
    4. Super key
    Answer

    B. Alternate key

    Unchosen candidate keys are alternate keys.

  9. Who proposed the relational model of data?

    1. Dennis Ritchie
    2. E. F. Codd
    3. Tim Berners-Lee
    4. Charles Babbage
    Answer

    B. E. F. Codd

    Codd proposed the relational model.

  10. SQL stands for:

    1. Sequential Query Language
    2. Standard Question Language
    3. Simple Query Logic
    4. Structured Query Language
    Answer

    D. Structured Query Language

    Standard full form.

  11. Which SQL command reads data from a table?

    1. SELECT
    2. INSERT
    3. GRANT
    4. UPDATE
    Answer

    A. SELECT

    SELECT queries data.

  12. Which SQL command adds a new row to a table?

    1. SELECT
    2. DROP
    3. INSERT
    4. REVOKE
    Answer

    C. INSERT

    INSERT adds rows.

  13. Which command removes a whole table, including its structure?

    1. DROP
    2. SELECT
    3. DELETE
    4. UPDATE
    Answer

    A. DROP

    DROP removes the table itself.

  14. Which SQL clause is used to sort the result?

    1. WHERE
    2. HAVING
    3. INSERT
    4. ORDER BY
    Answer

    D. ORDER BY

    ORDER BY sorts.

  15. GRANT and REVOKE belong to which group of SQL commands?

    1. TCL
    2. DCL
    3. DML
    4. DDL
    Answer

    B. DCL

    They control permissions.

  16. CREATE, ALTER and DROP belong to which group?

    1. DQL
    2. DML
    3. DDL
    4. DCL
    Answer

    C. DDL

    They define structure.

  17. COMMIT and ROLLBACK are examples of:

    1. DQL commands
    2. Aggregate functions
    3. TCL commands
    4. DDL commands
    Answer

    C. TCL commands

    They control transactions.

  18. Which of the following is an aggregate function in SQL?

    1. WHERE
    2. AVG
    3. CREATE
    4. SELECT
    Answer

    B. AVG

    AVG is an aggregate function.

  19. A step-by-step finite set of instructions to solve a problem is a(n):

    1. Database
    2. Protocol
    3. Compiler
    4. Algorithm
    Answer

    D. Algorithm

    Definition of algorithm.

  20. In a flowchart, input and output are shown by a:

    1. Parallelogram
    2. Circle
    3. Oval
    4. Diamond
    Answer

    A. Parallelogram

    Parallelogram = I/O.

  21. In a flowchart, the start and end are shown by an:

    1. Oval
    2. Arrow
    3. Rectangle
    4. Diamond
    Answer

    A. Oval

    Oval = terminal.

  22. The only language a computer CPU can run directly is:

    1. Python
    2. SQL
    3. Assembly language
    4. Machine language
    Answer

    D. Machine language

    Machine code is binary.

  23. Which program converts assembly language into machine code?

    1. Linker
    2. Assembler
    3. Interpreter
    4. Compiler
    Answer

    B. Assembler

    Assembler translates assembly.

  24. A translator that reads the whole program and then reports all errors together is a:

    1. Assembler
    2. Interpreter
    3. Compiler
    4. Editor
    Answer

    C. Compiler

    Compiler translates whole code at once.

  25. A translator that executes a program line by line is an:

    1. Assembler
    2. Interpreter
    3. Loader
    4. Compiler
    Answer

    B. Interpreter

    Interpreter works line by line.

  26. Which of these is a fourth-generation language?

    1. Machine language
    2. Assembly language
    3. SQL
    4. Binary code
    Answer

    C. SQL

    SQL is a 4GL.

  27. An error that breaks the grammar rules of a language is a:

    1. Syntax error
    2. Logical error
    3. Runtime error
    4. Hardware error
    Answer

    A. Syntax error

    Syntax errors break language rules.

  28. A program runs but gives wrong output. This is a:

    1. Compile error
    2. Network error
    3. Syntax error
    4. Logical error
    Answer

    D. Logical error

    Logic mistake gives wrong results.

  29. Which of these is an object-oriented programming language?

    1. Machine language
    2. Java
    3. HTML
    4. SQL
    Answer

    B. Java

    Java is object-oriented.

  30. Which of these is NOT a programming language?

    1. HTML
    2. Java
    3. Python
    4. C++
    Answer

    A. HTML

    HTML is a markup language.

  31. Which of the following pairs is correctly matched?

    1. DROP - removes selected rows only
    2. TRUNCATE - empties all rows quickly
    3. SELECT - changes stored values
    4. DELETE - removes the whole table
    Answer

    B. TRUNCATE - empties all rows quickly

    TRUNCATE empties all rows.

  32. Statement 1: A table can have only one primary key. Statement 2: A primary key may contain NULL values. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    A. 1 only

    A table has one primary key; it can never be NULL.

  33. Statement 1: A foreign key can have repeated values. Statement 2: A foreign key links two tables. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both are true for foreign keys.

  34. Statement 1: A compiler translates the whole program at once. Statement 2: An interpreter stops at the first error it finds. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both describe the translators correctly.

  35. Statement 1: Assembly language is machine language. Statement 2: High-level languages are close to English. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    B. 2 only

    Assembly uses mnemonics, so it is not machine language.

  36. Statement 1: DELETE removes the whole table structure. Statement 2: DROP removes the table itself. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    B. 2 only

    DELETE removes rows only; DROP removes the table.

  37. Statement 1: An algorithm must end after a finite number of steps. Statement 2: A flowchart is a picture of an algorithm. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both are true.

  38. Statement 1: HAVING filters groups of rows. Statement 2: GROUP BY sorts rows in ascending order. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    A. 1 only

    GROUP BY groups rows; ORDER BY sorts them.

  39. Statement 1: Degree is the number of rows. Statement 2: Cardinality is the number of columns. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    D. Neither 1 nor 2

    Both are reversed: degree = columns, cardinality = rows.

  40. Statement 1: MySQL and Oracle are examples of RDBMS. Statement 2: Machine language needs a compiler. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    A. 1 only

    Machine language runs directly, no compiler.

  41. Statement 1: SQL commands are used only to read data. Statement 2: INSERT and UPDATE are DML commands. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    B. 2 only

    SQL also defines and controls data; INSERT/UPDATE are DML.

  42. Statement 1: Sequence, selection and iteration are basic control structures. Statement 2: A loop that never ends is an infinite loop. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both are true.

  43. Statement 1: A database and a DBMS mean exactly the same thing. Statement 2: A DBMS helps reduce duplicate data. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    B. 2 only

    The database is the data; the DBMS is the software.

  44. Statement 1: Source code is written by the programmer. Statement 2: Object code is the machine code a compiler produces. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both are correct.

  45. Statement 1: Machine language is portable across all computers. Statement 2: Python is a high-level language. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    B. 2 only

    Machine language depends on the CPU, so it is not portable.

Page 1 of 1
‹
›