Database Management System (DBMS) – SS3 Data Processing Lesson Note (Second Term)

A database is a structured collection of data that is organised and stored for easy retrieval and updating. A database management system (DBMS) is the software used to create and manage databases.

Behavioural objectives

1. Define a database and a DBMS.
2. Name the three types of databases and give examples of each.
3. List common examples of DBMS and say what each is used for.
4. Explain basic database terms such as table, record, field, primary key, foreign key, index, query and normalisation.
5. Describe the main forms of database organisation.

What is a database management system (DBMS)?

A DBMS is software that lets users create, store, retrieve, update and control data in a database.

• Create: sets up tables and other database objects.
• Store: keeps data in an organised way on a computer or server.
• Retrieve: finds the data a user asks for through queries.
• Update: adds, changes or deletes records.
• Control access: allows only authorised users to see or change data.
• Protect data: supports backup and keeps data accurate and consistent.

Where are databases used?

• Business: stock records, customer lists and sales.
• Schools: student records, class lists and results.
• Banks: account details and transactions.
• Websites and apps: user accounts, products and messages.

What are the types of databases?

There are three types of databases: relational, NoSQL and NewSQL.

Relational databases

• Meaning: they store data in tables (relations), and keys define the relationships between tables.
• MySQL: a widely used open-source relational DBMS.
• PostgreSQL: an open-source relational DBMS.
• Oracle Database: a commercial relational DBMS.
• Microsoft SQL Server: Microsoft’s relational DBMS.
• SQLite: a small relational DBMS built into applications.

NoSQL databases

NoSQL databases handle unstructured or semi-structured data and are more flexible than relational databases. There are four main kinds.

• Document stores: keep data as documents, as in MongoDB and Couchbase.
• Key-value stores: keep each item as a key with its value, as in Redis and Amazon DynamoDB.
• Column-family stores: keep data in groups of columns, as in Apache Cassandra and HBase.
• Graph databases: keep data as nodes and links, as in Neo4j and Amazon Neptune.

NewSQL databases

• Meaning: they try to combine the strengths of relational and NoSQL databases.
• Aim: they give the scalability of NoSQL while keeping ACID properties.
• ACID: Atomicity, Consistency, Isolation and Durability.
• Examples: Google Spanner and CockroachDB.

How do the three types compare?

What are examples of a DBMS?

Examples of a DBMS include MySQL, Oracle Database, MongoDB, Redis, Neo4j, SQLite, Cassandra, SQL Server and PostgreSQL.

• MySQL: an open-source relational DBMS.
• Oracle Database: a powerful commercial relational DBMS.
• MongoDB: a popular NoSQL document store DBMS.
• Redis: a high-performance key-value store DBMS.
• Neo4j: a graph database for handling interconnected data.
• SQLite: a lightweight, embedded relational DBMS.
• Cassandra: a distributed NoSQL column-family store DBMS.
• SQL Server: Microsoft’s relational DBMS.
• PostgreSQL: an open-source relational DBMS known for its extensibility.

What are the basic database terms?

The basic database terms describe how data is arranged, linked, searched and cleaned in a database.

What is the difference between a primary key and a foreign key?

• Primary key: a field that identifies each record in its own table uniquely.
• Foreign key: a field that points to the primary key of another table to link the two tables.

What does a simple example look like?

A school can keep students and their results in two linked tables.

Students table (primary key: StudentID)

Results table (primary key: ResultID, foreign key: StudentID)

• Table: Students and Results are two tables.
• Record: each row, such as S001 Amina Bello SS3A.
• Field: each column, such as Name or Score.
• Link: StudentID in Results refers to StudentID in Students.

What are common SQL commands?

SQL is the standard language used to query and manage data in relational databases.

• CREATE: makes a new table or database.
• SELECT: retrieves data from a table.
• INSERT: adds a new record.
• UPDATE: changes existing records.
• DELETE: removes records.

What are the forms of database organisation?

There are seven forms of database organisation: hierarchical, network, relational, object-oriented, document, graph and column-family.

1. Hierarchical database: data is organised in a tree-like structure with parent-child relationships. An example is IMS (Information Management System).
2. Network database: data is organised using a more complex structure of sets and subsets, allowing multiple relationships. CODASYL databases are an example.
3. Relational database: data is organised in tables with rows and columns, and relationships are established through keys.
4. Object-oriented database: data is stored as objects with attributes and methods, suitable for object-oriented programming languages.
5. Document database: data is stored in a document format (for example JSON or XML), suitable for semi-structured data.
6. Graph database: designed to handle data with complex relationships, making it efficient for graph-like structures.
7. Column-family database: designed for storing and retrieving columns of data, often used in big data and NoSQL databases.

How do hierarchical and relational databases differ?

• Hierarchical database: arranges records as a tree, where each child record has one parent.
• Relational database: arranges data in tables that are linked by keys.

How do you choose a database type?

The choice of database type depends on the needs of the application.

• Data volume: how much data must be stored and handled.
• Complexity: how structured the data is and how the items relate.
• Performance: how fast data must be read and written.
• Structured data with clear links: a relational database suits it, such as school records.
• Flexible or unstructured data: a NoSQL database suits it.
• Large scale with strict accuracy: a NewSQL database suits it.

Evaluation

1. What is a database, and what is a DBMS?
2. Name the three types of databases and give one example of each.
3. State the difference between a primary key and a foreign key.
4. What is normalisation?
5. Describe a hierarchical database and a relational database.

Assignment

1. Draw a table with the columns DBMS, Type (relational or NoSQL) and Use. Fill it in for five examples of a DBMS.