Relational Databases

Database:
● Defined as a collection of interrelated data stored together to serve multiple applications.

DBMS(Database Management System):

● Refers to a Software that is responsible for storing, maintaining and utilising Databases.
● A database along with a DBMS is referred to as a database system.
● It’s a System/ Software that stored and manages data for an organization.
● It keeps data safe organised and easily accessible.

Need for DBMS:

● Different applications need different types of data.
● DBMS helps all applications access the right data easily and avoid confusion.

Benefits:

Avoid Data Redundancy:

● Redundancy means repeating the same data in multiple places DBMS stores it once and
shares it when needed.

Avoids Data Inconsistency:

● In consistency is when same data has different values in different locations / places.
● DBMS avoids this by having a central place to manage and update data.

RELATIONAL DB MODEL:
● IT stores data in tables (just like an Excel sheet).
● Each table is called a relation.
● Each row is called a tuple and each column is an attribute.
Hence
○ The table is relation
○ Rows are records (tuples)
○ Columns are fields (attributes)

Sample tables in Relational model:

● Suppliers (SuppNO, Supp_Name, Status, City)
● Items (ItemNo, Item_Name, Price)
● Shipments ((SuppNo,Item No,City_supplied)
● Each table stores a different kind of Info but can be linked using keys.

Primary Key:
 ● A field that is uniquely identifies in a row in a table.
Sample DB in Relational Form
This table lists Suppliers
Each row shows unique SuppNo, Supp_Name, Status, City

This table lists different items with Item No, Name of item, Price of Item

which supplier supplied which item, Qty__ Quantity of item supplied

Some terminologies to remember:
● Table: group of logically connected records
● Record: one complete Entry.
● Field: Each attribute like “firstname”, etc
● Data item : single cell value.
● Primary key: A key which is used to identify each row uniquely.

Important points about table:

● All row are different
○ no two rows in a table can be exactly same.
○ Every row must be unique.
● Rows are not stored in any specific order
○ Row1 doesn’t always come before Row2.
○ there is no fixed order of rows.
● Columns have Unique names
○ You can’t have two columns with the same name.
○ The order of columns doesn’t matter.

A Relation need to have :

● Same type of data in each column
○ In a column all values must be of same type

Atomic values:
● Each column should have only one value per row.

Domain:
● A Domain is a set of values from which actual values in a column came from.

Degree:
 ● No of Columns in a table.

Cardinality:
● No of Rows in a table.

View( Virtual Table):

● It is created using data from a table based on condition.
● A view doesn’t store data by itself, it just shows selected data from an existing table.

Importance of view:
○ Shows only specific data to user
○ Hide sensitive or unnecessary information
○ Simplifies the complex queries.

Base table:
● Real tables with stored data.

View:
● Virtual tables created from base tables using a condition.
● Views are not permanent but are created when needed using a query.

Key:
● A key is one or more attributes used to uniquely identify rows in a table.

Primary key:
● A primary key is used to uniquely identify each row in a table.

Composite Key:
● If the primary key is made using more than one attribute it is called a composite key.

Non – Key attribute:

● Attributes that are not part of the primary key.

Candidate key:
● When a table has more than one attribute those can be used as a primary key , all those are
called candidate keys.

Alternate key:
● A candidate key which is not selected as a primary key.

Foreign Key:
● Field in one table that refers to the primary key in another table.

Referential Integrity:
● Rules that ensure relationship between two tables is stays correct.

Conditions to set referential integrity:

● The matching field in the primary table is a primary key or has a unique index.
● The related fields have the same data type.
● Both tables must be in the same database.
Rules enforced when referential integrity is ON:
● You cannot enter a foreign key value that doesn’t exist in the primary table.
● You cannot delete a record from the primary table if it has related records in the foreign table.
● You cannot change a primary key value if it has related records.

MySQL:
● It is a free and open-source RDBMS.
● It uses SQL(Structured Query Language) to manage and query data.
● It allows you to store data in tables where each table holds related data.

MySQL Database System:

● It’s a system designed to store, manage and retrieve large amounts of data efficiently.
● It works in Client-Server setup.

Server:
● The software that runs on the computer and holds all the actual data.

Client:
● These are the other applications or programs that wants to access the data.

Features of MySQL:
Features on MySQL:
● High Speed – MySQL processes queries and retrieves data very fast.
● Easy to Use – MySQL has simple commands and is easy to learn and manage.
● Free and Open Source – MySQL is free to use and its source code is publicly available.
● Uses Standard Language – MySQL uses SQL (Structured Query Language) to manage data.
● Portable – MySQL can run on different operating systems like Windows, Linux, and Mac.
● Data Types – MySQL supports different data types like INT, VARCHAR, DATE, etc.
● Security – MySQL provides password protection and user access control.
● Scalability and Limits – MySQL can handle small to very large databases efficiently.
● Connectivity – MySQL can connect with many programming languages like Python, Java,
and PHP.
● Localization – MySQL supports different languages and character sets.
● Clients and Tools – MySQL provides tools like MySQL Workbench to manage databases
easily.

SQL Has 7 main Capabilities:

● SQL is a powerful language and can be used by both:
○ Beginners
○ Advanced users

Data Definition Language(DDL)

● Used to define structure of table or database
● You can Create, Delete and Modify tables.

Interactive Data Manipulation Language(DML)

● Used to update, delete and read data.

Embedded DML
 ● Allows you to use SQL inside programming language like C, C++, Java, Python, etc..

View
● Used to create Virtual tables

Authorization
● You can Control access by giving or restricting rights to users.

Integrity
● Used to maintain correct and valid data.

Transaction Control
● Used to manage Transactions(Group of SQL Operations).

DDL:
● DDL commands do not deal with data instead they deal with how the data is stored and
organized.
● Defines how your database looks and works, What tables are there, what columns they have
and what rules apply to them, etc..

Functions of DDL:
● Identify types of data divisions
● Give unique names to each data division
● Specify proper data types
● Relate datatypes to make structure
● Define Encoding methods
● Define length of data item
● Define range of values
● Check for errors in data
● Prevent unauthorized access
● Logical Data definition only

Classification of SQL statements:

● SQL is the language used to interact with databases, it is used to create, manage and
manipulate data.

SQL statements are grouped into different categories:

○ DDL – Data Definition Language
○ DML – Data Manipulation Language
○ TCL – Transaction Control Language
○ DCL – Data Control Language
○ SCL – Session Control Language
○ SCC – System Control Commands

DDL Commands:
● CREATE: Create a new table/ object
● ALTER : Modify an existing table
● DROP : Delete a table or object
● GRANT
 ● REVOKE
● CHECK : Verifies the integrity of a table and checks for errors.
● REPAIR : Fixes a corrupted table to restore it to a usable state.
● RESTORE : Recovers a database or table from a backup.

DML Commands:
● DML Commands are used to manage the data within tables.
● They do not affect the structure , Only the data.
● INSERT : Add new data
● UPDATE : Modify Existing data
● DELETE : Removes data from a tables based on a condition.

DCL Commands: Gives Access, Removes Access

TCL:
● Used to manage transactions.
● Help to make changes permanent, Undo changes if needed, Ensure consistency.
● COMMIT : Save changes permanently.
● ROLLBACK : Undo Changes.
● SAVEPOINT : Mark a point in Transaction.
● SET TRANSACTION : Set properties of a transaction.