DBMS

(DATABASE MANAGEMENT SYSTEM)

WHY DBMS?

 

  • Less Redudancy
  • Security
  • Recovery
  • Access control

Example Database

Types of Database

  • Hierarchical databases
  • Network databases
  • Relational database
  • NoSQL databases

Relational vs No-SQL

Relational No-SQL
    Deployed in vertical                          fashion.  Deployed in horizontal                      fashion
 ACID(Atomicity, Consistency,Isolation,Durability) properties  Eventually consistent
Fixed schema Flexible schema
      Read scalability only Read and write scalability
    centralized structure Decentralized structure

Transaction

  • Atomicity
  • Consistency
  • Isolation 
  • Durability

More insights about DBMS

  • Keys
  • Relationships
  • ER diagrams

Keys

Candidate key

Super key

Foreign key

Primary key

Relationships

One to Many

Many to Many

One to One

Many to One

E-R Diagrams

  • Making a skeleton structure of how the database would look after completion
  • helps you to analyze data requirements systematically to produce a well-designed database

Main Components of ER diagram

Entities-  Represented by

Attributes-Represented by 

Relationships-Represented by 

Example of making ER diagram

In a university, a Student enrolls in Courses. A student must be assigned to at least one or more Courses. Each course is taught by a single Professor. To maintain instruction quality, a Professor can deliver only one course

Step 1(Identification of Entities)

Relationship Identification

Step 3(Cardinality Identification)

In a university, a Student enrolls in Courses. A student must be assigned to at least one or more Courses. Each course is taught by a single Professor. To maintain instruction quality, a Professor can deliver only one course

Step 4(Identification of Attributes)

Step 5 ( Finalising the diagram)

Weak Entity Set

Entities dependent on strong entities

Referential Integrity

Correspondance between two rows of different tables through a foreign key

Example:- Table1: customer(custID,cusName)

                        Table 2: Order(OrderID,CustID,OrderDate)

Here every orderID from Table2 must match with a valid custID form Table1

Normal Forms

What is Normalization

 

Ans: Reducing data Redundancy from a relation or a set of relations

Major 5 types of normal forms

  • Boyce and Codd Normal Form
  • Third Normal Form
  • Second Normal Form
  • Fourth Normal Form
  • First Normal Form

First Normal Form

  1. It should only have single valued attributes/columns.
  2. Values stored in a column should be of the same domain
  3. All the columns in a table should have unique names.
  4. And the order in which data is stored, does not matter.

Second Normal form

  1. It should be in the First Normal form.
  2. And, it should not have Partial Dependency.

Third Normal Form

  1. It is in the Second Normal form.
  2. And, it doesn't have Transitive Dependency.

Boyce and Codd Normal Form

  • R must be in 3rd Normal Form
  • For each functional dependency ( X → Y ), X should be a super Key.

Fourth Normal Form

  1. It is in the Boyce-Codd Normal Form.
  2. And, it doesn't have Multi-Valued Dependency

SQL

(Structured Query Language)

Playground for practice

SELECT

SELECT <COLUMN_NAME> from <DATABASE_NAME>
//Select a column from a database
SELECT Customers from [CUSTOMERS]
//Select all columns in a database
SELECT * FROM [CUSTOMERS]

Similar to SELECT is SELECT DISTINCT which allows to choose only distinct values

WHERE

SELECT column1, column2, ...
FROM table_name
WHERE condition;
//EXAMPLE
SELECT * FROM Customers
WHERE Country='Mexico';

INSERT INTO

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
//EXAMPLE
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

SUM(),COUNT(),AVG()

//COUNT
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

//AVERAGE
SELECT AVG(column_name)
FROM table_name
WHERE condition;

//SUM
SELECT SUM(column_name)
FROM table_name
WHERE condition;

WILDCARDS

USED with LIKE keyword

syntax may differ with application where SQL database is running

On a SQL server

Playground

GROUP BY

Groups the data based on provided conditions

//General Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Thank You