Database Management System - 10

Part-1 : DBMS

Some Basic Terminologies:

1. Data : Data is the raw material of the digital and scientific world: unprocessed facts, figures, observations, or symbols.
2. Information : Information is processed, organized, and structured data that provides meaning, context, and utility to a receiver.
3. Database : A database is an organized, electronic collection of structured information or data, typically stored and accessed from a computer system.
4. Database Management System : A Database Management System (DBMS) is a software system that serves as an interface between a database and its users or applications. It allows users to create, retrieve, update, and manage data efficiently while ensuring security and consistency. Examples : MySql, FoxPro, MariaDB, etc.
5. Data redundancy : Data redundancy refers to the storage of the same data in multiple, separate locations or systems.

Difference between Manual and Computerized Database:

Manual Database	Computerized Database
Hard file storage system which is based on paper as all the dara is weitten on paper.	Computerized databases are computerized file systems which consists of digital records that are stored in storage device.
Difficult to maintain data redundancy.	System automatically maintains data redundancy.
Difficult to maintain security and privacy.	Easy to maintain security and privacy.
Doesn't require any software.	Requires DBMS software.

Advantages of DBMS

• Prevents data redundancy
• Faster operation of data
• Data security and privacy
• Recovery and Backup
• Data Integrity

Disadvantages of DBMS

• Increases Cost
• Database Failure
• Frequent Upgrade
• Highyl Complex
• Huge Size

Data Types

1. Numeric Data Type
To store numeric values. Here is the list of numeric data types in increasing order of their sizes.
INT -> TINYINT -> SMALLINT -> MEDIUMINT -> BIGINT -> FLOAT -> DECIMAL.
2. String Data Type
To store strings like text or character-based information. Here is the list of string data types in increasing order of their sizes.
CHAR(n) -> VARCHAR(n) -> TINYTEXT -> TEXT(size) -> MEDIUMTEXT -> LONGTEXT.
3. Date and Time
To store date as well as time.

• DATE - Store Date.
• TIME - Store Time.
• DATETIME - Store date and time combination.
• TIMESTAMP - Used to record events automatically.

Components of Database

1. Table - Table is defined as the organized structure of data in rows and columns.
2. Field/Attribute - Field is defined as the data heading of a column in a table that stores data of a defined category of a record.
3. Record/Tuple - Record is defined as the individual information of a kind that has possible data in all fields of the table.

Types of Keys

1. Primary Key
Primary Key is defined as a field or attribute which uniquely identifies each record(tuple) in a table. Does not accept null value and repeated(duplicate) value and cannot be left empty.
The importance of primary key are

1. It helps to identify each record uniquely.
2. It helps to create a link between two or more tables.
3. It helps to prevent data redundancy that is duplication of data.
2. Foreign Key
Foreign key is a field or group of fields in one table that refers to the primary key field of another table. Mainly used to establish and enforce a relationship between two tables in a database.
The importance of foreign key are

1. It helps to establish relationship between tables.
2. It helps to prevent invalid data in the database.
3. It is used to maintain referential integrity, which means that the value in the foreign key column always matches an existing value in the referenced primary key column.
3. Composite Key
Composite key is a key consisting of two or more attributes that uniquely identify a record. Helps when single column primary key is not enough to uniquely identify each record in a table.
The importance of composite key are

1. A composite key is formed from multiple columns.
2. It ensures uniqueness when the combined values of the columns are considered together.
3. Cannot contain null values.

Relationships in a table The process of creating a link between two or more tables is called relationship. It's types are:

1. One to One(1:1) - Each record in the first table relates to exactly one record in the second table, and vice versa.
2. One to Many(1:M) or Many to One(M:1) - A single record in the primary table can relate to multiple records in the related table, but the related record can only link to one record in the primary table and vice-versa.
3. Many to Many(M:M) - Multiple records in one table can be associated with multiple records in another table, and vice versa.

Part-2 : SQL

Components of DBMS

1. Table
A table is the foundational building block of a database used to store information. It organizes data in a structured, grid-like format consisting of horizontal rows (records or tuples) and vertical columns (fields or attributes).
2. Form
A form is a user interface that allows users to easily enter, view, and update data in a database.
3. Query
A query is a request or a "question" asked to the database to extract, sort, and manipulate specific data stored in tables.
4. Report
A report is a highly structured, formatted presentation of database information designed specifically for reading, printing, or digital sharing.

Structured Query Language(SQL)
SQL (Structured Query Language) is the standard programming language used to interact with and manage data in a Relational Database Management System (RDBMS). While a DBMS (Database Management System) is the software that manages the data, SQL is the specific language used to send commands to that software to store, retrieve, or modify information.
Out of many categories of SQL commands, we are focusing on two, they are as follows:

1. Data Definition Language(DDL) commands/statements
Data Definition Language (DDL) statements are the statements that are used to define or manage the structure of the database. It's commands are:
1. CREATE : Helps to create new database object(database, table, index, view).
2. DROP : Helps to drop/remove any database object (database, table, index, view).
3. TRUNCATE : Helps to clear out the table of all the data in it.
4. ALTER : Helps to alter the structural components of the table.
2. Data Manipulation Language(DML) commands/statements
Data Manipulation Language(DML) statements are the statements that are used to manage or modify the data stored within existing database tables. It's commands are:
1. SELECT : Helps to display filtered out or whole data from the table.
2. UPDATE : Helps to make changes in the existing information of selected/all records.
3. DELETE FROM : Helps to remove records from the table.
4. INSERT INTO : Helps to insert new records in the table.

Syntax of DDL statements:

• CREATE DATABASE database_name;
• CREATE TABLE table_name(
  Col1 data1 constraint1,
  … ,
  PRIMARY KEY(column_name));
• DROP DATABASE database_name;
• DROP TABLE table_name;
• ALTER TABLE table_name ADD new_col new_dt;
• ALTER TABLE table_name DROP COLUMN col_name;
• ALTER TABLE table_name ADD PRIMARY KEY (col_name);
• ALTER TABLE table_name RENAME COLUMN old_colname TO new_colname;
• ALTER TABLE table_name MODIFY colx dtx;
• TRUNCATE TABLE table_name;
• DESC table_name; # Gives the information about the table structure

Syntax of DML statements:

• UPDATE table_name SET col_name=updated_val WHERE condition;
• DELETE FROM table_name WHERE condition;
• SELECT col_name/* FROM table_name;
• INSERT INTO table_name VALUES(list of record1),(list of record2),…;

WHERE Clause
“Where” Clause is used to keep custom conditions in the commands in SQL.
We can add multiple keywords like IN, BETWEEN, AND, LIKE, ORDER BY, etc.

Some Keywords in SQL

1. LIKE
Purpose: Search Patterns
Wild cards: %(for many characters), _(for one character)
Syntax: SELECT * FROM table WHERE field LIKE 'condition';
Example: SELECT * FROM Students WHERE Name LIKE 'S%'; (Name Starting from S)

2. ORDER BY
Purpose: Sorts records
Key Attributes: ASC, DESC
Syntax: SELECT * FROM tableORDER BY column_name ASC/DESC;
Example: SELECT * FROM Students ORDER BY Marks DESC; (Sort records in descending order of Marks)

3. BETWEEN
Purpose: Select Values within a range
Syntax: SELECT * FROM table WHERE column BETWEEN value1 AND value2;
Example: SELECT * FROM Students WHERE Marks BETWEEN 50 AND 80; (Marks ranging 50-80)

4. AND
Purpose: Combine conditions. Need all conditions true
Syntax: SELECT * FROM table WHERE condition1 AND condition2;
Example: SELECT * FROM Students WHERE Class = 10 AND Marks > 80; (Student of class 10 whose marks is greater than 80)

5. OR
Purpose: True if atleast one condition true
Syntax: SELECT * FROM table WHERE condition1 OR condition2;
Example: SELECT * FROM Students WHERE City = 'Kathmandu' OR City = 'Pokhara'; (Students from Ktm or Pkr)

6. NOT
Purpose: Reverse a condition
Syntax: SELECT * FROM table WHERE NOT condition;
Example: SELECT * FROM Students WHERE NOT Class = 10; (not from class 10)

7. IN
Purpose: Check Multiple values in list
Syntax: SELECT * FROM table WHERE column IN (value1, value2, value3);
Example: SELECT * FROM StudentsWHERE City IN ('Kathmandu', 'Lalitpur', 'Bhaktapur'); (From ktm ltp bktp)

8. IS NULL
Purpose: Search empty values
Syntax: SELECT * FROM table WHERE column IS NULL;
Example: SELECT * FROM Students WHERE Phone IS NULL; (Students who didn't submit phone numbers)