Getting to Know the Basics of Databases in Microsoft Excel

Databases are key elements in structured and efficient data management. In today’s digital era, data is a very valuable asset for organizations and individuals. By utilizing databases, information can be stored, organized, and accessed systematically.

It allows users to manage large amounts of data without sacrificing the integrity and accuracy of the information. For example, companies can use databases to store customer information, transactions, and inventory, making strategic decisions easier.

Databases organize data in a structured format, making it easier to access and process information. A table structure consisting of rows and columns allows each entity to be displayed clearly.

For example, in a relational database, customer data can be linked to order information through relationships between tables. This not only improves the efficiency of data search but also allows for more in-depth analysis. For example, a marketing manager can quickly pull up reports on customer buying behavior based on data stored in a database.

Basic Structure of the Database

A database is an organized collection of information, usually stored in a digital format, that makes it easy for users to access, manage, and process data. In everyday life, databases are used in various fields, such as business, health, education, and government.

For example, a company can use a database to store customer data, sales transactions, and product stock. With a database, companies can analyze data to improve marketing strategies and decision-making.

Key Components of a Database

Tabel

Tables are the main elements in a database that serve as data storage structures. Each table consists of rows (records) and columns (fields). A table can be thought of as a folder that contains information about a specific entity, such as a customer or a product.

In a relational database system, data is stored in interconnected tables. For example, a table that stores employee data can be connected to a table that stores salary information.

Columns and Rows

• Columns are sections that show the attributes or characteristics of an entity in a table. For example, in the customer table, the columns can contain names, addresses, and phone numbers.
• Rows are representations of stored data. Each row in the table represents a single entity with all its attributes. For example, a single row in the customer’s table will contain complete information about one particular customer.

Difference between Manual and Digital Databases

Manual databases are usually managed physically using paper or written documents. Users have to search for information manually, such as sorting documents or copying data to a spreadsheet.

Meanwhile, digital databases allow for electronic storage and management of data. This facilitates quick and efficient access to information and allows users to analyze data with software such as SQL. With a digital database, changes to a single entity can be made automatically without the need to manually change each record.

Important Terms in Databases

Record

A record is a single unit of complete information stored in a database table. Each record consists of interconnected data and is arranged in the form of rows. For example, in a customer table, a single record can contain information about a customer, such as name, address, and phone number. Here’s an example of a record in the customer table:

Here, all information about Budi Santoso is a single record in the customer table.

Field

Fields are columns in a table that describe the attributes or characteristics of the stored entity. Each field has a specific name and data type that determines the type of information that can be stored. For example, in a product table, the fields can include the product name, price, and stock. Here’s an example of a product table structure with different fields:

In this example, “Product Name”, “Price”, and “Stock” are fields that store the attributes of each product.

Query

Query is a command used to retrieve or modify data in a database. Usually, queries are written in a specific programming language, such as SQL (Structured Query Language). With queries, users can perform various actions such as retrieving data, updating information, or deleting data from the database.

A simple query example to retrieve all the data from the customer table is:

SELECT * FROM Customer;

This query will return all the records that are in the customer table so that the user can see all the stored information.

The Concept of Relationships Between Tables in Databases

In database management, the relationships between tables are an important basis for organizing data properly. In this way, we can correlate data from different tables to prevent duplication and improve efficiency in data management. Here is the explanation.

One-to-Many relationship

A one-to-many relationship occurs when one record in a table can be associated with many records in another table.

Example 😀 Customer data and invoice management:

• The Customers table stores customer information, such as ID and name.
• The Invoices table stores customer transactions, such as invoice numbers and total payments.

This relationship connects the Customer ID in the Customers table with multiple invoices in the Invoices table.

Customer Table:

Invoice Table:

Here, the customer with ID “001” has two invoices (INV001 and INV002).

One-to-One relationship

A one-to-one relationship occurs when one record in a table is connected to only one record in another table.

Example 😀 Employee database:

• The Employees table stores employee data, such as names and IDs.
• The Employee Details table stores additional details, such as ID number or address.

Employee Table:

Employee TableDetails:

Each employee has only one detailed data.

Many-to-Many relationship

A many-to-many relationship occurs when one record in a table can be associated with many records in another table, and vice versa. This relationship often uses an intermediate table (junction table).

Examples of 😀 Online course systems:

• The Student table stores student data.
• The Courses table stores a list of courses.
• Student’s intermediate tableCoursecourse connects students with the courses they are taking.

Student Table:

Course Table:

Student TableCourse:

A student can take multiple courses, and one course can be attended by many students.

Benefits of Inter-Table Relationships

1. Avoid Data Duplication

By breaking the data into interconnected tables, the same information doesn’t need to be stored repeatedly.

Example: A customer’s name is stored only once in the Customers table, even if the customer has multiple transactions.

2. Data Storage Efficiency

Relationships between tables reduce storage space usage because repetitive data is separated into a single entity.

3. Ease of Data Retention

Changes in data in one table automatically affect relationships in other tables. For example, changing the customer’s name in the Customers table will reflect the change in all related transactions.

4. Data Analysis Flexibility

Relationships between tables make it easier to combine data from multiple sources for analysis purposes, such as sales reports per customer.