Chapter: DBMS - Introducing Databases
What is a Database?
* A database is an organized collection of data.
* It helps in storing, managing, and retrieving information efficiently.
* Think of it like a well-organized digital filing cabinet!
Why use Databases?
* Data Consistency:** Ensures data is accurate and reliable.
* Data Security:** Protects data from unauthorized access.
* Easy Access:** Allows quick retrieval of specific information.
* Data Sharing:** Enables multiple users to access and use data simultaneously.
* Reduces Redundancy:** Avoids storing the same information multiple times.
Database Management System (DBMS)
* A DBMS is software that allows you to create, manage, and interact with databases.
* It acts as an interface between the user and the database.
* Examples: MySQL, PostgreSQL, Oracle, Microsoft Access, LibreOffice Base.
Creating Databases and Tables
# Databases in LibreOffice Base
* LibreOffice Base is a popular, free database management tool.
* When you create a new database in Base, you can choose to:
* **Create a new database:** Stores the database file on your computer.
* **Connect to an existing database:** Links to a database file already present.
* **Connect to an external data source:** Connects to databases like MySQL, PostgreSQL, etc. (usually
requires additional setup).
# Tables: The Building Blocks
* A table is a fundamental structure in a database used to store data in rows and columns.
* Rows (Records):** Represent a single entry or item (e.g., details of one student).
* Columns (Fields):** Represent a specific attribute or piece of information about each entry (e.g., Student
Name, Roll Number, Class).
Creating/Editing Tables in Design View
Design View is where you define the structure of your table.
Steps to Create a Table in Design View:
1. Open your database in LibreOffice Base.
2. Click on "Tables" and then select "Create Table in Design View".
3. **Define Fields:**
**Field Name:** A unique name for each column (e.g., `StudentID`, `FirstName`, `EnrollmentDate`).
**Data Type:** The kind of data a field will hold (e.g., Text, Number, Date, Yes/No).
**Description (Optional):** A brief explanation of the field's purpose.
4. Set Primary Key:**
* A primary key is a field (or combination of fields) that uniquely identifies each record in a table.
* It ensures that no two records are the same.
* Right-click on the field you want to set as the primary key and select "Primary Key".
5. Save the Table:** Click the save icon and give your table a meaningful name (e.g., `Students`, `Courses`).
Editing Tables in Design View:
* You can add new fields, delete existing ones, change data types, or modify field properties.
* Be cautious when editing tables that already contain data, as changes can affect existing records
Queries in Base
# What are Queries?
* Queries are used to ask questions of your database.
* They allow you to retrieve specific data based on certain criteria or conditions.
* You can also use queries to update, delete, or add data.
# Creating Queries in Base:
*Using the Query Wizard:**
1. Go to "Queries" and click "Create Query in Wizard".
2. **Select Fields:** Choose the tables and the fields you want to include in your query.
3. **Set Conditions (Criteria):** Define the rules for filtering your data (e.g., `Class = 'X'` or `Marks > 75`).
4. **Sort Data:** Specify how you want the results to be ordered.
5. **Choose Action:** Decide if you want to view data, modify data, etc.
6. **Save the Query:** Give your query a descriptive name.
* **Using SQL View:** For more advanced users, you can directly write SQL commands to create queries.
SQL (Structured Query Language)
SQL is the standard language for interacting with relational databases.
# DDL (Data Definition Language) Commands
These commands define and manage the database structure.
* `CREATE DATABASE database_name;`**: Creates a new database.
* `DROP DATABASE database_name;`**: Deletes an entire database.
* CREATE TABLE table_name (column1 datatype, column2 datatype, ...);`**: Creates a new table.
* ALTER TABLE table_name ADD column_name datatype;`**: Adds a new column to an existing table.
* ALTER TABLE table_name DROP COLUMN column_name;`**: Deletes a column from a table.
* DROP TABLE table_name;`**: Deletes an entire table.
# DML (Data Manipulation Language) Commands
These commands are used to manage data within database objects.
*`INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);`**: Adds new records (rows) to
a table.
*`SELECT column1, column2, ... FROM table_name WHERE condition;`**: Retrieves data from a table.
* `*` can be used to select all columns.
* `WHERE` clause is used for filtering.
* UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;`**: Modifies existing
records.
* DELETE FROM table_name WHERE condition;`**: Removes records from a table.
# TCL (Transaction Control Language) Commands
These commands manage transactions within the database.
* **`COMMIT;`**: Saves all changes made during the current transaction permanently.
* **`ROLLBACK;`**: Undoes all changes made during the current transaction since the last commit.
* **`SAVEPOINT savepoint_name;`**: Sets a point within a transaction to which you can later roll back.
# Keys in SQL
* **Primary Key:** Uniquely identifies each record in a table. Cannot contain NULL values.
```sql command
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50)
);
```
* **Foreign Key:** A field in one table that refers to the primary key in another table. It establishes a link between
tables and enforces referential integrity.
```sql
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50)
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
# Form Wizard
* Forms provide a user-friendly interface for entering, editing, and viewing data in your database.
* The **Form Wizard** in LibreOffice Base helps you create forms quickly.
* **Steps:**
1. Click "Forms" and then "Create Form in Wizard".
2. **Select Fields:** Choose the fields from your tables or queries that you want on the form.
3. **Arrange Fields:** Decide on the layout (e.g., columnar, tabular).
4. **Apply Styles:** Choose a visual style for your form.
5. **Save the Form:** Give your form a name.
# Report Wizard
* Reports are used to present database information in a formatted, printable way.
* They are ideal for summaries, analyses, and professional-looking printouts.
* The **Report Wizard** guides you through creating reports.
* **Steps:**
1. Click "Reports" and then "Create Report in Wizard".
2. **Select Fields:** Choose the data you want to include.
3. **Group and Sort Data:** Organize your report by grouping related records and sorting them.
4. **Set Summary Options:** Calculate totals, averages, etc.
5. **Choose Layout and Style:** Select how the report will look.
6. **Save the Report:** Name your report.
---
I hope these notes are helpful for your studies! Let me know if you have any more questions.