Today I am post my experiences and some notes of learning postgreSQL from Syed Jafer from “Parotta Salna” youtube channel.

Firstly made discussion about “flat files” and how to store data on flat files.

Flat Files

flat files refers similar to a csv file where data is stored separated by a space. for example,

Let us consider a hospital management of recording patient details.

name date-of birth phone disease
varun 12/12/2003 2837298372 fever
sakthiraj 12/11/2003 8273492989 whooping-cold&fever
saravana 27/09/2003 8237192929 stomach-ache

here all of them are separated with spaces. It is difficult for larger data to handle also we cannot add more than one value inserted, which causes confusion. for example,

sakthiraj 12/11/2003 8273492989 8239102983 whooping-cold&fever

phone number is added twice which causes confusion between phone and disease row.

This can be resolved by adding column names = value showing sample below

[varun]
name=varun
date_of_birth=12/11/2003
phone=2423453455
phone=9384753349
disease=fever

INTRODUCTION TO DATABASES

databases comes into picture, by handling large amounts of data, by handling duplicates or more than one value and can be queried based on the user needs at faster rate.

• Databases contains list of tables which has number of rows and columns.

Database management System (DBMS)

A Database Management System (DBMS) is a software system designed to manage, organize, and provide access to databases in an efficient and secure manner.

Types of Database models

1. Hierarchial database model

• Hierarichial database model is arranged like a “family tree like structure”.
• data will be stored one below another.

- Hi
    * Hello
    * World

2. Network Database model

Network database model stores in a form of graph data structure.

• A graph contains edges and vertices.
• Vertices are the thing are object that are represented in a graph
• Edges are used to connect between two vertices.

Best example: Neo4j is a graph database which has network of people with edges connected between people for a common cause between two people.

3. Relational database model

Relational database model is a organized list of data which is stored in a group of lists which are called tables. Also we make relationship between two tables by calling each of the list in a table.

Example: MySQL, postgreSQL, etc.,

Types of Integrity

• Entity integrity – uniqueness

Entity integrity is defined as the maintainance of records which is unique and can be found with no duplication.

• Referential integrity – verify connection

referential integrity is used to verify connection between relationship on two or more tables.

• User-defined integrity – custom rules for the data

User can set their own rules for the given data to stored. Not feasible using SQL. Only done with programming languages (backend).

• Domain integrity – valid data

domain integrity ensures valid data on each columns in a table.

for example: if we consider a column named ‘date’ then it must contain day/month/year format.