3.2.9 Databases

Introduction

A set of data organized in a systematic manner, usually in a computer system, is called a database. A database stores, retrieves, and handles a large number of records for multiple users. Databases allow users to locate specific information quickly and accurately.

The textbook explains that the central source of data for every software program is a database. For instance, when a user logs in, shops online, or makes a transaction, a database system saves the relevant data so that it may be retrieved later — for example, what time the user logged in, which items the user purchased, or the transaction to which account took place.

Definition

Database is an organized collection of data stored and accessed electronically, designed to manage, retrieve, and handle large amounts of information efficiently for multiple users.

In simple words: A database is a structured collection of data that allows you to store, search, and manage information easily.

Main Idea

• Removing redundancies and inconsistencies.
• Organizing data for reliability.
• Providing concurrent access (multiple users can work on the same data at the same time).
• Including security measures to safeguard sensitive information.
• Providing effective information retrieval and data analysis.

The textbook describes the key concepts, types, and tools associated with databases.

Core Concept

Types of Databases The textbook identifies two main types of databases:

1. Relational Databases Most databases are relational. They organize data into rows and columns of a table, such that each table represents an individual entity (for example, customers or items). Relational databases use structured query language (SQL) for managing data.

2. NoSQL Databases NoSQL databases provide flexibility and can manage vast amounts of unstructured or semi-structured data. Unlike relational databases, they do not have a fixed table structure.

Key Concepts in Databases

Table A table is the fundamental structure of a relational database and is made up of rows and columns. Each row denotes a record, while each column represents a field.

Record A record is a single table item with information about a specific entity. For example, a client record could include information like his or her name, address, and telephone number.

Field A field is a piece of data within a record that defines the information placed in a certain column. For example, a Name field would store text data, whereas an Age field would store numerical data.

Data Integrity Data integrity refers to the accuracy and consistency of data within a database. It ensures that the data is accurate, dependable, and appropriate for usage.

Primary & Secondary Keys Keys are essential in creating databases to uniquely identify records and create links among tables. The most common types of keys used in normalization are:

Primary Key: A primary key is a minimal set of attributes that is exclusively able to identify a record. It can be a single attribute or a combination of attributes.

Candidate Key: A candidate key is a single or a set of attributes that allow identifying a record uniquely. Though there may be several candidate keys, only one is chosen to be the primary key. The rest are called secondary keys.

Foreign Key: A foreign key is a field in one table that creates a relationship with another table by referring to its primary key.

Composite Key: A composite key is a combination of two or more attributes that distinctively identify a row in a database table.

Data Normalization Data normalization is a key component of relational database management that focuses on minimizing duplicate data. To ensure effective and consistent data storage and speed up database access, the contents are divided into smaller interlinked tables.

Why normalization is needed: Data redundancy (duplicate storage of the same information) is a common problem in databases. It causes problems in maintaining consistency and increases the chances of inaccuracies during updating of data. By dividing the data into more manageable and interlinked tables, normalization solves these problems.

How it works: Every table represents a separate entity with keys defining the relationships among the entities. Without normalization, databases frequently show inconsistencies and abnormalities during updates or deletions.

Example from the book: A database containing customer information with several addresses would require changes in many locations for every address change, increasing the chances of mistakes. Normalized data addresses this by creating various tables for customers and addresses, which are connected by a key like customer-ID.

Database Tools The textbook mentions two popular database tools:

Microsoft Access: Part of the MS Office suite, it is simple to install, easy to use, and offers a user-friendly drag-and-drop interface. It is commonly used by individuals and small organizations for tasks like inventory, project management, and event planning. MS Access also integrates well with other MS Office programs like Excel and Project.

SQLite: A lightweight database system that stores data in a single file. It is ideal for applications needing local storage and is widely used in mobile apps and scenarios that do not require a large database server. SQLite is simple, efficient, and easy to use.

• It occupies small space, making it suitable for applications with limited resources.
• It is easily integrated into applications, making it easier to use and deploy.
• It has a serverless architecture, which eliminates unnecessary dependencies.
• The complete database is saved in a single file.

Intermediate Level

Databases are organized around several key concepts:

The textbook emphasizes that normalization is essential for maintaining data consistency. Without it, updating a single piece of information (like an address) would require changes in multiple places, increasing the risk of errors.

Important Points

• - Database: An organized collection of data stored and accessed electronically.
• - Types: Relational (tables) and NoSQL (flexible, unstructured).
• - Key Concepts: Table, Record, Field.
• - Data Integrity: Accuracy and consistency of data.
• - Keys:
• - Primary Key: Uniquely identifies a record.
• - Candidate Key: Any attribute that could be a primary key.
• - Foreign Key: Links to a primary key in another table.
• - Composite Key: Combination of two or more attributes.
• - Data Normalization: Minimizes duplicate data by dividing contents into smaller interlinked tables.
• - Database Tools:
• - MS Access: User-friendly, drag-and-drop, integrates with MS Office.
• - SQLite: Lightweight, serverless, single-file database.
• - Advantages of SQLite: Small space, easy integration, serverless, single file.

Remember

• - Database = Organized collection of data.
• - Table = Rows and columns.
• - **Record** = One row (one entity).
• - Field = One column (one attribute).
• - Primary Key = Unique identifier.
• - Foreign Key = Link to another table.
• - Data Integrity = Accuracy and consistency.
• - Normalization = Reduce redundancy.
• - MS Access = User-friendly, drag-and-drop.
• - SQLite = Lightweight, serverless, single file.

Common Mistakes

• 1. Confusing primary key with foreign key — Primary key uniquely identifies a record in its own table; foreign key links to a primary key in another table.
• 2. Thinking normalization is about deleting data — Normalization is about organizing data to reduce redundancy, not deleting it.
• 3. Confusing tables with databases — A database contains multiple tables; a table is one structure within a database.
• 4. Ignoring data integrity — Data integrity ensures accuracy and consistency; without it, the database becomes unreliable.
• 5. Assuming NoSQL databases are always better — NoSQL is better for unstructured data, but relational databases are better for structured data with clear relationships.
• 6. Thinking SQLite requires a server — SQLite is serverless; it stores the entire database in a single file.

Quick Revision

• Database: A set of data organized in a systematic manner, usually in a computer system, that stores, retrieves, and handles large amounts of records for multiple users.
• Types:
• Relational: Data organized into tables (rows and columns).
• NoSQL: Flexible, handles unstructured/semi-structured data, no fixed table structure.
• Key Concepts:
• Table: Rows and columns.
• Record: A single item with information about a specific entity.
• Field: A piece of data within a record (column).
• Data Integrity: Accuracy and consistency of data within a database.
• Keys:
• Primary Key: Uniquely identifies a record.
• Candidate Key: Any attribute that could be a primary key.
• Foreign Key: Links to a primary key in another table.
• Composite Key: Combination of two or more attributes.
• Data Normalization: Minimizes duplicate data by dividing contents into smaller interlinked tables. Solves update, insertion, and deletion anomalies.
• Database Tools:
• MS Access: User-friendly, drag-and-drop interface, integrates with MS Office.
• SQLite: Lightweight, serverless, single-file database. Advantages: small space, easy integration, serverless, single file.

60-Second Revision

• Database = Organized collection of data.
• Table = Rows + Columns.
• **Record** = One row.
• **Field** = One column.
• Primary Key = Unique identifier.
• Foreign Key = Link to another table.
• Data Integrity = Accuracy + Consistency.
• Normalization = Reduce redundancy.
• MS Access = Drag-and-drop.
• SQLite = Lightweight, serverless, single file.
• Warning: Primary key identifies in own table; foreign key links to another table.

Frequently Asked Questions (FAQs)

Q1. What is a database? Answer: A database is a set of data organized in a systematic manner, usually in a computer system, that stores, retrieves, and handles a large number of records for multiple users. It allows users to locate specific information quickly and accurately.

• Relational Databases: Organize data into rows and columns of tables. Each table represents an individual entity.
• NoSQL Databases: Provide flexibility and can manage vast amounts of unstructured or semi-structured data. They do not have a fixed table structure.

Q3. What is a table in a database? Answer: A table is the fundamental structure of a relational database and is made up of rows and columns. Each row denotes a record, while each column represents a field.

Q4. What is a record? Answer: A record is a single table item with information about a specific entity. For example, a client record could include information like name, address, and telephone number.

Q5. What is a field? Answer: A field is a piece of data within a record that defines the information placed in a certain column. For example, a Name field would store text data, whereas an Age field would store numerical data.

Q6. What is data integrity? Answer: Data integrity refers to the accuracy and consistency of data within a database. It ensures that the data is accurate, dependable, and appropriate for usage.

Q7. What is a primary key? Answer: A primary key is a minimal set of attributes that is exclusively able to identify a record. It can be a single attribute or a combination of attributes. For example, a student's roll number is a primary key because it uniquely identifies each student.

Q8. What is a foreign key? Answer: A foreign key is a field in one table that creates a relationship with another table by referring to its primary key. For example, a "Class ID" in a Students table could be a foreign key linking to the "Class ID" primary key in a Classes table.

Q9. What is data normalization? Answer: Data normalization is a key component of relational database management that focuses on minimizing duplicate data. To ensure effective and consistent data storage and speed up database access, the contents are divided into smaller interlinked tables.

• It occupies small space, making it suitable for applications with limited resources.
• It is easily integrated into applications, making it easier to use and deploy.
• It has a serverless architecture, which eliminates unnecessary dependencies.
• The complete database is saved in a single file.

Practice Questions

Short Response Questions (SRQs)

• Define a database and explain its purpose.
• What are the two main types of databases? Give examples of each.
• Explain the terms table, record, and field with examples.
• What is data integrity? Why is it important?
• Differentiate between a primary key and a foreign key.

Extended Response Questions (ERQs)

• Discuss the key concepts of databases (table, record, field, primary key, foreign key) in detail. Provide examples of each.
• Explain data normalization. Why is it important in database management? What problems does it solve?
• Compare and contrast MS Access and SQLite as database tools. What are the advantages of each?
• You are designing a database for a school. Describe how you would organize the data into tables, identify primary and foreign keys, and apply normalization.
• Analyze the advantages of using a database over manual record-keeping. How does a database ensure data integrity and consistency?

Multiple Choice Questions (MCQs)

• Q1. What is a database?
• A. A type of file
• B. An organized collection of data stored and accessed electronically
• C. A programming language
• D. A type of network
• Correct Answer: B
• Explanation: A database is an organized collection of data stored and accessed electronically.

• Q2. Which type of database organizes data into rows and columns?
• A. NoSQL database
• B. Relational database
• C. Binary database
• D. Text database
• Correct Answer: B
• Explanation: Relational databases organize data into rows and columns of tables.

• Q3. What is a record in a database?
• A. A column in a table
• B. A row in a table
• C. A type of key
• D. A database tool
• Correct Answer: B
• Explanation: A record is a single table item (row) with information about a specific entity.

• Q4. What is a field in a database?
• A. A row in a table
• B. A column in a table
• C. A type of key
• D. A database tool
• Correct Answer: B
• Explanation: A field is a piece of data within a record that defines the information placed in a certain column.

• Q5. What is a primary key?
• A. A key that links to another table
• B. A unique identifier for a record
• C. A combination of two or more attributes
• D. A type of database
• Correct Answer: B
• Explanation: A primary key is a minimal set of attributes that uniquely identifies a record.

• Q6. What is a foreign key?
• A. A unique identifier for a record
• B. A field in one table that links to the primary key of another table
• C. A combination of two or more attributes
• D. A type of database
• Correct Answer: B
• Explanation: A foreign key is a field in one table that creates a relationship with another table by referring to its primary key.

• Q7. What is data integrity?
• A. The speed of data retrieval
• B. The accuracy and consistency of data
• C. The amount of data stored
• D. The type of database used
• Correct Answer: B
• Explanation: Data integrity refers to the accuracy and consistency of data within a database.

• Q8. What is data normalization?
• A. Deleting duplicate data
• B. Organizing data to reduce redundancy
• C. Encrypting data
• D. Backing up data
• Correct Answer: B
• Explanation: Data normalization focuses on minimizing duplicate data by dividing contents into smaller interlinked tables.

• Q9. Which database tool is lightweight and serverless?
• A. MS Access
• B. SQLite
• C. Oracle
• D. MySQL
• Correct Answer: B
• Explanation: SQLite is a lightweight, serverless database system that stores data in a single file.

• Q10. What is one advantage of SQLite?
• A. It requires a powerful server
• B. It occupies small space
• C. It cannot be integrated into applications
• D. It requires complex installation
• Correct Answer: B
• Explanation: SQLite occupies small space, making it suitable for applications with limited resources.