DataBase Management System

Data and Data Types

Data refers to raw, unorganized facts and figures that are processed and interpreted to become meaningful information.

Numeric

Used for storing numerical values, such as integers (e.g., INT, BIGINT) and floating-point numbers (e.g., FLOAT, DECIMAL).

Character/String

Used for storing text-based data, including fixed-length strings (e.g., CHAR) and variable-length strings (e.g., VARCHAR).

Date and Time

Used for storing date and time information (e.g., DATE, TIME, DATETIME).

Boolean

Represents logical values, either TRUE or FALSE.

Binary

Used for storing sequences of bytes (e.g., BINARY, VARBINARY).

Large Objects (LOBs)

Used for storing large amounts of data, such as images, audio, or video (e.g., BLOB, CLOB).

DataBases

A database is a structured collection of data that is stored and accessed electronically. It is designed to efficiently store, manage, retrieve, and update data. Databases are used in a wide range of applications, from banking systems to websites, to keep information organized and easily accessible

Relational Database

A relational database is a type of database which stores data in the form of tables called relations.Each table consists of rows called records and columns called attributes. In this, keys are used to to establish and enforce relationships between tables thus data integrity is maintained and redundancy is reduced.

Hierarchial Database

A hierarchical database is a type of database management system (DBMS) that organizes data in a tree-like structure. This structure is based on parent-child relationships, much like an organizational chart or a file system on your computer.

Network model Database

The Network Model evolved from the hierarchical model, primarily to overcome its inability to directly represent complex many-to-many relationships. Instead of a strict tree structure, the network model uses a more generalized graph structure, allowing records (or nodes) to have multiple "parent" and multiple "child" records. Data is organized into records and sets, where a set defines a relationship between an "owner" record type and one or more "member" record types

Object-Oriented Database

The Object-Oriented Database (OODB), also known as an Object Database Management System (OODBMS), takes a different approach by storing data as objects, directly mirroring concepts from object-oriented programming (OOP) languages. In an OODB, data is organized into objects that are instances of classes, which define both the object's attributes (data) and its methods (behavior).

Domain Specific Softwares

Domain-specific software refers to computer programs or applications that are specially designed to perform tasks, solve problems, or provide services within a specific field or industry. Unlike general-purpose software, domain-specific software is tailored to the unique requirements, workflows, and regulations of a particular sector.

DataBase Management System

A Database Management System (DBMS) is software used to create, manage, and manipulate databases. It helps users store, retrieve, and update data efficiently and securely. Instead of manually storing data in files, DBMS organizes data into structured tables and manages relationships between them.

Accounting System

An Accounting System is software that helps businesses track financial transactions such as sales, purchases, income, and expenses. It automates accounting tasks, ensures accurate financial reporting, and helps with budget planning.

Attendance System

An Attendance System is a software application that helps record and manage the presence, absence, and working hours of students or employees. It may use methods like manual input, biometric devices, RFID, or online check-in.

Database Software Development

Database software development encompasses the entire process of designing, building, testing, and maintaining database systems and the applications that interact with them. It's a critical aspect of nearly all modern software, from small mobile apps to large enterprise systems, as databases are the backbone for storing, organizing, and retrieving information.

Database Design

Database design and database administration are two distinct but highly interconnected roles crucial for the effective functioning of any data-driven organization. Think of it like building and maintaining a sophisticated house: the database designer is the architect, drawing up the blueprints and ensuring the structure is sound and functional, while the database administrator (DBA) is the property manager, ensuring the house remains habitable, secure, and performs optimally over time.
Database Design focuses on the planning and structuring of the database itself. This involves understanding the information needs of an organization and translating those requirements into a logical and physical data model. Key responsibilities of a database designer include:

Database Administration

Database Administration (DBA) is concerned with the ongoing management, maintenance, performance, security, and availability of the database system once it has been designed and implemented. DBAs are the guardians of the data, ensuring it remains accessible, secure, and performs optimally. Their key responsibilities typically include:

Tools

Server

A server is a powerful computer or software that provides services or resources to other computers (clients) over a network. It can host websites, manage files, run applications, or store databases. Common types include web servers, database servers, and file servers.

Xampp

XAMPP is a free and open-source software package that provides a local server environment for web development. It includes X (cross-platform), Apache (web server), MySQL (database), PHP, and Perl. Developers use XAMPP to test websites or applications on their own computer(i.e. locally) before deploying them online.

Structured Query Language(SQL)

SQL stands for Structured Query Language and is the standard programming language used to manage and manipulate relational databases. It allows users to perform various operations on the data stored in databases, such as retrieving, inserting, updating, and deleting records.

CRUD Operations

CRUD operations refer to the four basic functions used to interact with data in a database. They stand for:

Create — Adding new data (usually done with INSERT in SQL)
Read — Retrieving or viewing data (done with SELECT)
Update — Modifying existing data (done with UPDATE)
Delete — Removing data (done with DELETE)

CRUD forms the foundation of most database applications and aligns closely with SQL commands for data manipulation.

Keys in Database

Keys are special fields (or combinations of fields) in a database table that help identify, access, and maintain relationships between data records.

Primary Key:
A primary key is a field (or a combination of fields) that uniquely identifies each record in a table. It cannot have duplicate or NULL values.

Example: In a Student table:

StudentID	Name	Class
1	Ram	12
2	Sita	11

Here, StudentID is the primary key because it uniquely identifies each student.

Foreign Key:
A foreign key is a field in one table that refers to the primary key of another table. It is used to establish a relationship between two tables.

Example: In a Marks table:

StudentID	Subject	Marks
1	Math	85
2	English	90

Here, StudentID is a foreign key because it refers to StudentID in the Student table.

Candidate Key:
A candidate key is any field or combination of fields that can uniquely identify a record in a table. A table can have multiple candidate keys, but only one is selected as the primary key.

Example: In a Student table:

StudentID	Email	Phone
1	ram@gmail.com	9800000001
2	sita@gmail.com	9800000002

Here, StudentID, Email, and Phone are all candidate keys because each can uniquely identify a student. One of them (e.g., StudentID) is chosen as the primary key.

Constraints in Database

Constraints are rules applied to database columns to enforce data integrity, accuracy, and consistency by restricting the type of data that can be stored.

Anomalies in Database

Anomalies are problems or inconsistencies that occur in a database when inserting, updating, or deleting data due to poor database design or lack of normalization.

Normalization in Database

Normalization is the process of organizing data in a database to reduce redundancy (duplicate data) and ensure data integrity. It involves dividing large tables into smaller, related tables and linking them using keys. This makes data easier to maintain, reduces the chance of anomalies (like update or deletion issues), and improves database efficiency.

First Normal Form (1NF):
A table is in 1NF if:
– All values in each column are atomic (indivisible).
– Each record is unique.

Example (Not in 1NF):

StudentID	Name	Subject
1	Ram	Math,English
2	Sita	Science,Math

Conversion to 1NF:

StudentID	Name	Subject
1	Ram	Math
1	Ram	English
2	Sita	Science
2	Sita	Math

Second Normal Form (2NF):
A table is in 2NF if:
– It is in 1NF.
– All non-key attributes are fully functionally dependent on the primary key (no partial dependency).

Example (In 1NF not 2NF):

StudentID	Subject	Teacher
1	Math	Mr. Sharma
1	English	Ms. Koirala
2	Science	Mr. Lama

Here, TeacherName depends only on Subject, not the whole composite key (StudentID + Subject).

Conversion to 2NF:
Main Table:

StudentID	Subject
1	Math
1	English
2	Science

Subject-Teacher Table:

Subject	TeacherName
Math	Mr. Sharma
English	Ms. Koirala
Science	Mr. Lama

Third Normal Form (3NF):
A table is in 3NF if:
– It is in 2NF.
– All attributes are only dependent on the primary key, and not on any other non-key attribute (no transitive dependency).

Example (Not in 3NF):

StudentID	StudentName	Department	HOD
1	Ram	Science	Dr. Sharma
2	Sita	Arts	Dr. Joshi

Here, HOD depends on Department, not directly on StudentID.

Conversion to 3NF:
Student Table:

StudentID	StudentName	Department
1	Ram	Science
2	Sita	Arts

Department Table:

Department	HOD
Science	Dr. Sharma
Arts	Dr. Joshi

Normalization Process Summary:
1. Remove repeating groups → 1NF
2. Remove partial dependencies → 2NF
3. Remove transitive dependencies → 3NF

By following these steps, the database becomes more structured, efficient, and easier to maintain.

SQL (Structured Query Language) is a standard language used to manage and manipulate data in a relational database. It allows users to create, read, update, and delete data easily using commands.

SQL is divided into several important components based on what they do:

1. DDL (Data Definition Language):
These commands are used to define the structure of database objects like tables.
– CREATE: Creates a new table or database.
– ALTER: Modifies the structure of an existing table (e.g., adding a column).
– DROP: Deletes a table or database completely.

2. DML (Data Manipulation Language):
These commands are used to manage data inside tables.
– INSERT: Adds new data to a table.
– UPDATE: Changes data in a table.
– DELETE: Removes data from a table.

3. DQL (Data Query Language):
This part is used to query or view data from the database.
– SELECT: Retrieves specific or all records from one or more tables.

4. DCL (Data Control Language):
These commands are used to control who can access or change the data.
– GRANT: Gives permission to a user.
– REVOKE: Takes back permission from a user.

Database Security

Database Security refers to the measures used to protect a database from unauthorized access, misuse, corruption, or theft. It ensures that only authorized users can access, modify, or manage the data while keeping it safe from threats.