Introduction to Database

 Database

A database is an organized collection of data that can be easily accessed, managed, and updated. Think of it like a digital filing system that allows users to store large amounts of information in a structured way. Example: A school stores student records (names, grades, attendance) in a database instead of keeping piles of paper.

In a database, data is stored in tables with rows and columns, making it easy to organize, sort, and search. With file systems, data is often stored in unstructured or semi-structured formats (like text files or spreadsheets), which can become messy and hard to manage as the volume grows.

 Advantages of Using Databases Over File Systems

Databases offer a range of benefits compared to traditional file-based systems:

®      Reduced Data Redundancy: Data is stored once and referenced across related tables, preventing unnecessary duplication—unlike file systems that may store the same data in multiple locations.

®      Faster Data Retrieval: Using queries (e.g., SQL), databases allow quick searches and filtering, while file systems often require manual opening and scanning.

®      Multi-User Access with Security: Databases are designed to support multiple users at once with role-based access control, reducing risks of data conflicts and unauthorized access.

®      High Data Integrity: Validation rules ensure data remains accurate and consistent, unlike file systems where errors are more likely.

®      Support for Data Relationships: Databases can link related data (e.g., customers and orders), enabling complex data structures that are not possible with flat files.

®      Better Scalability: As data grows, databases can handle large volumes efficiently, while file systems struggle with performance and management.

®      Automated Reports and Analysis: Built-in tools allow databases to generate summaries, reports, and visual insights with ease, unlike the manual efforts required in files.

®      Reliable Backup and Recovery: Databases support systematic backup and recovery processes, providing data protection in case of failure.

®      Transaction Management: Databases can perform complex operations securely using transactions to ensure data accuracy—something file systems can’t guarantee.

Applications of Databases

Databases are used almost everywhere in daily life and industries. Some key applications include:

®      Schools: Storing student details, fees, exam results.

®      Hospitals: Managing patient records, appointments, medicine stock.

®      Banks: Handling account information, transactions, loan records.

®      E-commerce Websites: Managing product catalogs, customer details, orders.

®      Airlines: Keeping flight schedules, passenger booking information.

Examples of Databases

®      Microsoft Access – Desktop database software.

®      MySQL – Open-source relational database used on websites.

®      Oracle Database – Used in large enterprises.

®      SQLite – Lightweight database used in mobile apps.

®      MongoDB – NoSQL database used for handling unstructured data.

Types of Databases

i. Flat File Database

®      Stores data in a single table (like a spreadsheet).

®      Simple and suitable for small tasks.

®      Not efficient for complex data relationships.

Example: A list of members in a club with columns like Name, Age, and Membership Number.

ii. Relational Database

®      Stores data in multiple related tables.

®      Uses keys to link related data.

®      More powerful and efficient for managing large and complex data.

Example: A student table (student ID, name) and a marks table (student ID, subject, score) linked by the student ID.

 Data Types

The term 'Data Type’ refers to the type of data used, for example it could be text, numbers, dates or time, Boolean (Yes/No), Currency or an object such as an image or link. Each data type has its own data format, for example a data might be written DD/MM/YY or MM/DD/YY. Imagine the problems that could occur if you did not defining the date data format when you set up the database. Giving the date 11th Jan 2021 as an example, an English person might enter this data as 11/01/21 an American person might enter this data as 01/11/21. Each column in a database table has a data type which defines the kind of data it can hold.

®      Text: For names, addresses.

®      Number: For numerical values.

®      Date/Time: For storing dates like birthdates or due dates.

®      Boolean/Yes-No: For true/false or yes/no values.

®      Currency: For storing money-related data.

 Tables

A table is a structure in a database that stores data in rows and columns. A table is what holds the records, fields and defines the structure of the database. A record is all of the data about one item. A field refers to each type of data held.

®      Rows = Records (e.g., each TeamName)

®      Columns = Fields (e.g., TeamName, Driver_1, Points)

Think of a table as a spreadsheet where each row is a person or item, and each column is a characteristic of that item.

Primary Key

A primary key is a field that uniquely identifies each record in a table. Example: In a "Students" table, the Student ID can be a primary key because no two students have the same ID.

Foreign Key

A foreign key is a field in one table that links to the primary key in another table. It creates a relationship between two tables. Example:

®      Table A (Students): StudentID (Primary Key)

®      Table B (Marks): StudentID (Foreign Key)

This setup links each mark to a specific student.

Relationships (With Examples)

Relationships are connections between tables using keys. The main types are:

a. One-to-One (1:1)

Each record in Table A matches exactly one record in Table B.

Example: One student has one locker.

b. One-to-Many (1:M) – Most Common

One record in Table A can relate to many records in Table B.

Example: One teacher teaches many students.

®      Table A: Teacher (TeacherID)

®      Table B: Student (StudentID, TeacherID)

 c. Many-to-Many (M:N)

Records in Table A relate to many in Table B, and vice versa. This needs a junction table.

Example: Students can enroll in many courses, and each course has many students.

Forms

A form is a user-friendly interface for entering, editing, or viewing data in a table.

®      Makes data entry easier and more secure.

®      Prevents direct access to raw table data.

Example: A librarian uses a form to enter new book details without touching the raw table.

Queries

A query is a request to retrieve specific information from one or more tables.

Types of Queries:

®      Select Query: Retrieves specific data based on criteria.

®      Action Query: Updates, deletes, or inserts data.

®      Parameter Query: Asks the user for input before running.

 

Example 1 – Simple Select Query:

"Show all students in Grade 12":

SELECT * FROM Students WHERE Grade = 12;

 

Example 2 – Query with Join:

"Get student names and their subjects":

SELECT Students.Name, Subjects.SubjectName

FROM Students

JOIN Subjects ON Students.StudentID = Subjects.StudentID;

Reports

A report is a formatted output of data, often used for printing or presenting information clearly.

®      Summarizes data from tables or queries.

®      Can include totals, grouping, or visual formatting.

Example: A report showing total fees collected per class.

Macros

A macro is a set of automated instructions that perform tasks in a database, such as opening a form, running a query, or printing a report.

• Used to automate repetitive tasks.
• Common in Microsoft Access.

Example: A button in a form that, when clicked, runs a macro to generate a student report.

 Conclusion

Databases are essential tools for storing, managing, and analyzing data efficiently. By understanding key concepts like tables, keys, relationships, and queries, students can design and use databases effectively in real-world scenarios.