Database-Normalization-and-Postgres-Installation

Slide Link: https://drive.google.com/file/d/1Sp_11zUXL8u6lKK7cJ4s4an8cjJ-JNU7/view?usp=sharing

In this module, we will explore data anomalies and the different types of anomalies in DBMS.

You’ll learn normalization step by step – from 1NF to 3NF – with clear examples.

We will also cover resolving many-to-many relationships and updating ER diagrams using junction tables.

Finally, we’ll introduce PostgreSQL, set it up, and practice essential commands using the psql CLI.

43-1 Data Anomalies and Types of Anomalies in DBMS

Anomalies

• Imbalance in database is called anomalies
• Anomalies in databases refer to inconsistencies or unexpected issues that can occur during data manipulation or retrieval

Types Of Anomalies

• Update Anomalies
• Delete Anomalies
• Insert Anomalies

Update Anomalies

• Here Branch Dhaka and address Rampura came multiple times which is data duplication. suppose the branch name changes to Khilgao we have to update each Dhaka manually
• If there is data duplications in a table we will say there is any of the anomalies

Delete Anomalies

• Hassan works in barisal bagher bazar. If we delete hasan barisal branch information will be gone This is called delete anomalies

Insert Anomalies

• Suppose new person came and we have gave wrong address. Dhaka branch address will be different. we need some validations like it should be Rampura.

• Suppose we have not allocated address and branch. thi will create null. Keeping null resembles anomalies.

• How do we fix this?

• We could separate and make a relationship for avoiding anomalies

• This kind of table separations are called Normalizations

43-2 Normalization and Functional Dependency

• Normalization is a step by step process by using which we can remove any anomalies in our database

Functional Dependency

• Functional dependency in simple terms means that the value of one attribute (or set of attributes) uniquely determines the value of another attribute(s) in a table.

• Here for x value 2 we have two different y value. So this is why its not functionally dependent.

• if we consider x and tell to find y value using x we can find easily and this is functionally dependent. Here Y is functionally dependent to x

lets see another example

• here role can not determine the name. but the role can determine the role. This is called functional dependency

• another example

43-3 1NF Explained | First Normal Form with Easy Example

Normal Forms

• A set of rules applied to a database table to reduce redundancy and avoid anomalies in data by organizing it properly

Type of normal forms

• 0NF
• 1NF
• 2NF
• 3NF

ONF

• If a table is not in a 1NF we can tell its in 0NF

1NF

• Rules :
  1. Atomic Values (like address Broken in small pieces like house, road, sector)
  2. Unique Column Names
  3. Can not have Positional dependency of data
  4. Column should contain data that are of the same type
  5. Determine Primary key

• lets see the table where is the problem and how to convert in 1NF
• So far we have unique column names it ok
• we have no positional dependency of data
• In title we have to keep title not number
• and in course the atomicity is not maintained here is multiple course in one field
• lets fix this

• here comes the 1NF conversion
• Still data duplication came in serial number and title
• here we have to consider composite primary key for determining uniquely
• These will be fixed in 2NF

43-4 2NF Explained | Second Normal Form in DBMS

2NF

• Rules :
  1. Must be 1NF
  2. No non-key attribute should functionally depend on part of a candidate key

• Here we can see the table is in 1NF and has a composite primary key is combination of std_id, c_id. whose are also primary key

• but here is a problem with table that using the partial primary key attribute c_id we can determine c_name(non key attribute). and this is not following the 2NF 2nd rule.here c_id is functionally dependent on c_name. That means c_id can determine c_name

• If we want to convert in 2NF we have to separate the table because according to rule no non key attribute can not depend on the part of a candidate_key

• Here comes another problem lossy decomposition that means there is no relation with stud_id right now. we have to make relation.

• Now it became loss less decompositions

43-5 3NF Explained | Third Normal Form with Examples

3NF

• Rules :
  1. Must be 2NF
  2. Must not contain transitive dependency

transitive dependency

• Suppose X can determine Y and Y can determine Z. Technically we can determine Z indirectly using Y. Thi is Transitive Dependency.
• In this table there is transitive dependency. because using stud_id we can determine each and every field uniquely. In this way using stud_id we can determine state. But here the problem comes state can also determine the country. This is transitive dependency. We have to mange this separating the tables.

43-6 Resolving Many-to-Many Relationship, 43-7 Updating ER Diagram Using Junction Table

• We don't want two tables has many to many relationship because handling it becomes tough

• The table will look like this. this will not work because atomicity is not maintained.

• We have to solve this. We will simply divide in rows

• here problem becomes we can not set the primary key to uniquely identify uniquely
• Lets set a composite primary key

• Here Another problem arose Partial dependency came i mean using the id we cen identify the name. According to the rule of 2 NF we can not let this happen

• Lets solve this and make a different table

• here another problem arose like too many empty fields came we can not keep anything that takes space without any reason
• we have to make Junction Table / bridge table to make the table

• This make sure no many to many relationship between 2 tables came
• What will be the primary key of the bridge table?
• Composite primary key will be the primary key here.
• Lets modify the existing er diagram

43-8 What is PostgreSQL?

• The World's Most Advanced Open Source Relational Database Management System(dbms). More specifically RDBMS

Why Postgres ?

• Open Source
• Advanced Data Types
• Scalability
• ACID Compliance
• Modern
• Indexing

43-9 See Installation in previous modules

43-10 Some Postgres Commands | Add psql to PATH

\l

• This will show all the database of postgres

• postgres is the main database
• template0 and template1 is used for creating another database
• template0 is the main template because all is stored here, and the backup is template1
• clear the terminal

\! cls

• to see connection info

\conninfo

• to see if any table is here

\dt 

• create a table

create table users (id serial primary key, name varchar(50));

• see the table info

select * from users;

• see users in postgres

\du

• to see all the commands of postgres we will use

\?

• for exiting the postgres

\q

• see the version of postgres

select version();

• connect is different database

\c template1

• for running postgres in windows terminal we need to set the env variables (see previous modules)

• go to c drive postgres and bin folder of version 17 then copy the path and set in environment path

• open the windows terminal and run

psql -U postgres -d postgres

• create a database

CREATE DATABASE school;