Difference between revisions of "PostgreSQL-Database/C2/Data-Redundancy-and-Primary-Key/English"

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with "'''Title of the Script: Data Redundancy and Primary Key''' '''Author''': '''EduPyramids''' '''Keywords''':''' Data redundancy, Query Tool,''' '''use a''' '''primary key, DRO...")
 
Line 3: Line 3:
 
'''Author''': '''EduPyramids'''
 
'''Author''': '''EduPyramids'''
  
−
'''Keywords''':''' Data redundancy, Query Tool,''' '''use a''' '''primary key, DROP TABLE, EduPyramids,
+
'''Keywords''': Data redundancy, Query Tool, use a primary key, DROP TABLE, EduPyramids, Video Tutorial.  
−
Video Tutorial.'''
+
 
  
 
{| border=1
 
{| border=1
Line 11: Line 11:
 
|| '''Narration'''
 
|| '''Narration'''
 
|-  
 
|-  
−
|| '''Slide 1 Title Slide'''
+
|| '''Slide 1'''
 +
 
 +
''' Title Slide'''
 
|| Welcome to the Spoken Tutorial on '''Data Redundancy and Primary Key'''.
 
|| Welcome to the Spoken Tutorial on '''Data Redundancy and Primary Key'''.
 
|-  
 
|-  
−
|| '''Slide 2 Learning Objectives '''
+
|| '''Slide 2'''
 +
 
 +
'''Learning Objectives '''
 
|| In this tutorial, we will learn about,
 
|| In this tutorial, we will learn about,
−
*Data redundancy
+
* Data redundancy
−
*Why data redundancy is a problem
+
* Why data redundancy is a problem
−
*Primary keys, and  
+
* Primary keys, and  
−
*How to create a table with a primary key.
+
* How to create a table with a primary key.
  
 
|-  
 
|-  
−
|| '''Slide 3 System Requirements '''
+
|| '''Slide 3'''
−
|| To record this tutorial, I am using, * '''Ubuntu
+
−
24.04''' LTS
+
'''System Requirements '''
 +
 
 +
|| To record this tutorial, I am using,  
 +
* '''Ubuntu 24.04''' LTS
 
* '''PostgreSQL version 18.1''' and  
 
* '''PostgreSQL version 18.1''' and  
 
* '''pgAdmin 4 version 9.11'''.
 
* '''pgAdmin 4 version 9.11'''.
  
 
|-  
 
|-  
−
|| '''Slide 4 Pre-requisites '''
+
|| '''Slide 4'''
 +
 
 +
''' Pre-requisites '''
 +
 
 
'''https://EduPyramids.org'''
 
'''https://EduPyramids.org'''
−
|| To follow this tutorial, * Learners should have
+
|| To follow this tutorial,  
−
'''PostgreSQL''' and '''pgAdmin 4''' installed in their systems.
+
* Learners should have '''PostgreSQL''' and '''pgAdmin 4''' installed in their systems.
  
 
For the pre-requisite '''PostgreSQL''' tutorials, please visit this website.
 
For the pre-requisite '''PostgreSQL''' tutorials, please visit this website.
 
|-  
 
|-  
−
|| '''Slide 5 Code Files '''
+
|| '''Slide 5'''
 +
 
 +
'''Code Files '''
 +
 
 
drp-codes.txt
 
drp-codes.txt
 
|| The following code file is required to practice this tutorial.
 
|| The following code file is required to practice this tutorial.
 +
 
This file is provided in the Code Files link of this tutorial page.
 
This file is provided in the Code Files link of this tutorial page.
 
|-  
 
|-  
 
|| Open '''pgAdmin 4'''
 
|| Open '''pgAdmin 4'''
−
 
 
|| Let us open '''pgAdmin 4'''.
 
|| Let us open '''pgAdmin 4'''.
 
|-  
 
|-  
Line 51: Line 64:
 
|| Now let’s click on the '''students''' table.
 
|| Now let’s click on the '''students''' table.
 
|-  
 
|-  
−
|| Right click on students tableSelect '''Query Tool'''. Cursor on '''Query''' Editor.
+
|| Right click on students table.
 +
 
 +
Select '''Query Tool'''.  
 +
 
 +
Cursor on '''Query''' Editor.
 
|| I will right click on the '''students''' table and select the '''Query Tool'''.
 
|| I will right click on the '''students''' table and select the '''Query Tool'''.
 +
 
The '''Query''' Editor window opens on the right side.
 
The '''Query''' Editor window opens on the right side.
 
|-  
 
|-  
Line 59: Line 77:
 
'''Show SQL'''
 
'''Show SQL'''
 
|| Let us retrieve the data from the '''students''' table.
 
|| Let us retrieve the data from the '''students''' table.
 +
 
Type '''SELECT space asterisk space FROM space students semicolon'''.
 
Type '''SELECT space asterisk space FROM space students semicolon'''.
 
|-  
 
|-  
 
|| Click '''Execute query '''button.
 
|| Click '''Execute query '''button.
−
Looks like a play button.Highlight the output.
+
 
−
|| Click on the '''Execute''' '''query '''button on the tool bar.The output is displayed in tabular format.
+
Looks like a play button.
 +
 
 +
Highlight the output.
 +
|| Click on the '''Execute''' '''query '''button on the tool bar.
 +
 
 +
The output is displayed in tabular format.
 
|-  
 
|-  
 
|| Point to duplicate names
 
|| Point to duplicate names
 
|| We can see that student names such as '''Ram''' or '''Kishore''' appear more than once.
 
|| We can see that student names such as '''Ram''' or '''Kishore''' appear more than once.
 
|-  
 
|-  
−
|| '''Slide 6 Data Redundancy'''
+
|| '''Slide 6'''
 +
 
 +
'''Data Redundancy'''
 
|| Data redundancy means duplication of data in a database table.
 
|| Data redundancy means duplication of data in a database table.
 +
 
It increases storage usage, causes data inconsistency and affects database performance.
 
It increases storage usage, causes data inconsistency and affects database performance.
 
|-  
 
|-  
Line 75: Line 102:
 
|| To prevent data redundancy, we use a '''primary key'''.
 
|| To prevent data redundancy, we use a '''primary key'''.
 
|-  
 
|-  
−
|| '''Slide7 What is a Primary Key?'''
+
|| '''Slide 7'''
 +
 
 +
''' What is a Primary Key?'''
 
|| A primary key is a column or a set of columns that uniquely identifies each row in a table.
 
|| A primary key is a column or a set of columns that uniquely identifies each row in a table.
 
|-  
 
|-  
−
|| '''Slide 8 Rules of Primary Key'''
+
|| '''Slide 8'''
−
|| A table can have only one primary key, and its values must be unique and not null.Student name cannot
+
 
−
be a primary key as multiple students can have the same name.
+
''' Rules of Primary Key'''
 +
|| A table can have only one primary key, and its values must be unique and not null.
 +
 
 +
Student name cannot be a primary key as multiple students can have the same name.
 
|-  
 
|-  
 
|| '''pgAdmin 4 cursor on Columns'''
 
|| '''pgAdmin 4 cursor on Columns'''
Line 94: Line 126:
 
|| I will select the '''Drop''' option.
 
|| I will select the '''Drop''' option.
  
−
Drop Table pop up box appears with a message.Click the red colored '''Drop button. '''Notice that the table
+
Drop Table pop up box appears with a message.
−
structure along with its data is deleted.
+
 
 +
Click the red colored '''Drop button.  
 +
 
 +
'''Notice that the table structure along with its data is deleted.
 
|-  
 
|-  
−
|| '''DROP TABLE tablename; Add annotation'''
+
|| '''DROP TABLE tablename; '''
 +
 
 +
''Add annotation'''
 
|| The syntax to delete a table is '''DROP TABLE tablename semicolon.'''
 
|| The syntax to delete a table is '''DROP TABLE tablename semicolon.'''
 
|-  
 
|-  
Line 103: Line 140:
 
|| Let us create the '''students''' table with a primary key.
 
|| Let us create the '''students''' table with a primary key.
 
|-  
 
|-  
−
|| Open Right-click on the Tables and select '''Query Tool'''
+
|| Open Right-click on the Tables.
 +
 
 +
Select '''Query Tool'''
 
|| Now let us open the '''Query Tool'''.
 
|| Now let us open the '''Query Tool'''.
 
|-  
 
|-  
 
|| '''Show SQLType CREATE TABLE students ('''
 
|| '''Show SQLType CREATE TABLE students ('''
 +
 
'''studentid VARCHAR(10),'''
 
'''studentid VARCHAR(10),'''
 +
 
'''studentname VARCHAR(50),'''
 
'''studentname VARCHAR(50),'''
 +
 
'''city VARCHAR(40),'''
 
'''city VARCHAR(40),'''
 +
 
'''gender VARCHAR(10),'''
 
'''gender VARCHAR(10),'''
 +
 
'''dob DATE,'''
 
'''dob DATE,'''
 +
 
'''cgpa NUMERIC(2,1),'''
 
'''cgpa NUMERIC(2,1),'''
 +
 
'''PRIMARY KEY (studentid)'''
 
'''PRIMARY KEY (studentid)'''
 
''');'''
 
''');'''
Line 120: Line 166:
 
|| Here, '''studentid''' is defined as the primary key.
 
|| Here, '''studentid''' is defined as the primary key.
 
|-  
 
|-  
−
|| Click on the '''Execute''' '''query button'''.Cursor on green notification:'''The students table is created
+
|| Click on the '''Execute''' '''query button'''.
 +
 
 +
Cursor on green notification:'''The students table is created
 
successfully'''.
 
successfully'''.
 
|| Let us execute this query.The students table is created successfully.
 
|| Let us execute this query.The students table is created successfully.
Line 128: Line 176:
 
|-  
 
|-  
 
|| Type: '''INSERT INTO students VALUES'''
 
|| Type: '''INSERT INTO students VALUES'''
 +
 
'''('S101','Ram','Mumbai','Male','1996-10-23',9.6),'''
 
'''('S101','Ram','Mumbai','Male','1996-10-23',9.6),'''
 +
 
'''('S102','Kishore','Mumbai','Male','1996-08-09',8.5),'''
 
'''('S102','Kishore','Mumbai','Male','1996-08-09',8.5),'''
 +
 
'''('S103','Pooja','Pune','Female','1995-03-25',9.2);'''
 
'''('S103','Pooja','Pune','Female','1995-03-25',9.2);'''
 +
 
|| Let me type the following code in the query window.
 
|| Let me type the following code in the query window.
  
 
|-  
 
|-  
−
|| Click on the '''Execute''' '''query''' button.Show the notifications.
+
|| Click on the '''Execute''' '''query''' button.
−
|| Click on the '''Execute query''' button to execute the code.The records are inserted successfully.
+
 
 +
Show the notifications.
 +
|| Click on the '''Execute query''' button to execute the code.
 +
 
 +
The records are inserted successfully.
 
|-  
 
|-  
 
|| Click on the '''Execute''' '''query '''button.
 
|| Click on the '''Execute''' '''query '''button.
  
 
|| Let us insert the same data again.
 
|| Let us insert the same data again.
 +
 
For that, execute the '''INSERT''' statement once more.
 
For that, execute the '''INSERT''' statement once more.
 
|-  
 
|-  
Line 146: Line 203:
 
|-  
 
|-  
 
|| Point to the error.
 
|| Point to the error.
−
|| This error occurs because the primary key does not allow duplicate values.Now, let us retrieve the data
+
|| This error occurs because the primary key does not allow duplicate values.
 +
 
 +
Now, let us retrieve the data
 
again.
 
again.
 
|-  
 
|-  
−
|| Type:''' SELECT * FROM students;''' Click on the '''Execute '''button.
+
|| Type:''' SELECT * FROM students;
 +
 
 +
''' Click on the '''Execute '''button.
  
 
Show the Output.
 
Show the Output.
−
|| Let me type '''SELECT asterisk FROM students semicolon'''.Execute the following command.Notice that
+
|| Let me type '''SELECT asterisk FROM students semicolon'''.
−
there is no duplication of data in the table.
+
 
 +
Execute the following command.
 +
 
 +
Notice that there is no duplication of data in the table.
 
|-  
 
|-  
−
|| '''Slide 9 Summary'''
+
|| '''Slide 9'''
−
In this tutorial, we will learn about,* Data
+
 
−
redundancy  
+
'''Summary'''
 +
 
 +
In this tutorial, we will learn about,
 +
* Data redundancy  
 
* Why data redundancy is a problem
 
* Why data redundancy is a problem
 
* Primary keys, and  
 
* Primary keys, and  
 
* How to create a table with a primary key.
 
* How to create a table with a primary key.
  
−
|| With this we come to the end of this tutorial.Let us summarise.
+
|| With this we come to the end of this tutorial.
 +
 
 +
Let us summarise.
 
|-  
 
|-  
−
|| * '''Slide 10 As an Assignment,'''
+
|| '''Slide 10'''
 +
 
 +
'''Assignment'''
 +
 
 +
As an Assignment,'''
 
* Drop the '''Emp''' table
 
* Drop the '''Emp''' table
 
* Create it again with '''empno''' as the primary
 
* Create it again with '''empno''' as the primary
Line 172: Line 245:
 
|| As an assignment, please do the following.
 
|| As an assignment, please do the following.
 
|-  
 
|-  
−
|| '''Slide 11 Thank You '''
+
|| '''Slide 11'''
−
|| This Spoken Tutorial is brought to you by '''EduPyramids''' educational services private limited SINE IIT
+
 
−
Bombay.
+
'''Thank You '''
 +
|| This Spoken Tutorial is brought to you by '''EduPyramids''' educational services private limited SINE IIT Bombay.
 
|-
 
|-
 
|}
 
|}

Revision as of 13:05, 9 February 2026

Title of the Script: Data Redundancy and Primary Key

Author: EduPyramids

Keywords: Data redundancy, Query Tool, use a primary key, DROP TABLE, EduPyramids, Video Tutorial.


Visual Cue Narration
Slide 1

Title Slide

Welcome to the Spoken Tutorial on Data Redundancy and Primary Key.
Slide 2

Learning Objectives

In this tutorial, we will learn about,
  • Data redundancy
  • Why data redundancy is a problem
  • Primary keys, and
  • How to create a table with a primary key.
Slide 3

System Requirements

To record this tutorial, I am using,
  • Ubuntu 24.04 LTS
  • PostgreSQL version 18.1 and
  • pgAdmin 4 version 9.11.
Slide 4

Pre-requisites

https://EduPyramids.org

To follow this tutorial,
  • Learners should have PostgreSQL and pgAdmin 4 installed in their systems.

For the pre-requisite PostgreSQL tutorials, please visit this website.

Slide 5

Code Files

drp-codes.txt

The following code file is required to practice this tutorial.

This file is provided in the Code Files link of this tutorial page.

Open pgAdmin 4 Let us open pgAdmin 4.
Click on localhost node. Please ensure that Localhost PostgreSQL server is connected.
Click students table Now let’s click on the students table.
Right click on students table.

Select Query Tool.

Cursor on Query Editor.

I will right click on the students table and select the Query Tool.

The Query Editor window opens on the right side.

Type SELECT * FROM students;

Show SQL

Let us retrieve the data from the students table.

Type SELECT space asterisk space FROM space students semicolon.

Click Execute query button.

Looks like a play button.

Highlight the output.

Click on the Execute query button on the tool bar.

The output is displayed in tabular format.

Point to duplicate names We can see that student names such as Ram or Kishore appear more than once.
Slide 6

Data Redundancy

Data redundancy means duplication of data in a database table.

It increases storage usage, causes data inconsistency and affects database performance.

To prevent data redundancy, we use a primary key.
Slide 7

What is a Primary Key?

A primary key is a column or a set of columns that uniquely identifies each row in a table.
Slide 8

Rules of Primary Key

A table can have only one primary key, and its values must be unique and not null.

Student name cannot be a primary key as multiple students can have the same name.

pgAdmin 4 cursor on Columns Therefore, we will create a new column called studentid and make it as the primary key.
pgAdmin 4 First, we must delete the existing students table.
Right-click on the students table. Let me right click on the students table.A context menu opens.
Select Drop I will select the Drop option.

Drop Table pop up box appears with a message.

Click the red colored Drop button.

Notice that the table structure along with its data is deleted.

DROP TABLE tablename;

Add annotation'

The syntax to delete a table is DROP TABLE tablename semicolon.
Let us create the students table with a primary key.
Open Right-click on the Tables.

Select Query Tool

Now let us open the Query Tool.
Show SQLType CREATE TABLE students (

studentid VARCHAR(10),

studentname VARCHAR(50),

city VARCHAR(40),

gender VARCHAR(10),

dob DATE,

cgpa NUMERIC(2,1),

PRIMARY KEY (studentid) );

Let me type the following code in the query window.
Highlight studentid Here, studentid is defined as the primary key.
Click on the Execute query button.

Cursor on green notification:The students table is created successfully.

Let us execute this query.The students table is created successfully.
Now let us insert data into this table.
Type: INSERT INTO students VALUES

('S101','Ram','Mumbai','Male','1996-10-23',9.6),

('S102','Kishore','Mumbai','Male','1996-08-09',8.5),

('S103','Pooja','Pune','Female','1995-03-25',9.2);

Let me type the following code in the query window.
Click on the Execute query button.

Show the notifications.

Click on the Execute query button to execute the code.

The records are inserted successfully.

Click on the Execute query button. Let us insert the same data again.

For that, execute the INSERT statement once more.

Point to error We get an error message saying duplicate key value violates unique constraint.
Point to the error. This error occurs because the primary key does not allow duplicate values.

Now, let us retrieve the data again.

Type: SELECT * FROM students;

Click on the Execute button.

Show the Output.

Let me type SELECT asterisk FROM students semicolon.

Execute the following command.

Notice that there is no duplication of data in the table.

Slide 9

Summary

In this tutorial, we will learn about,

  • Data redundancy
  • Why data redundancy is a problem
  • Primary keys, and
  • How to create a table with a primary key.
With this we come to the end of this tutorial.

Let us summarise.

Slide 10

Assignment

As an Assignment,

  • Drop the Emp table
  • Create it again with empno as the primary

key

  • Insert a few records and retrieve the data.
As an assignment, please do the following.
Slide 11

Thank You

This Spoken Tutorial is brought to you by EduPyramids educational services private limited SINE IIT Bombay.

Contributors and Content Editors

Ketkinaina, Madhurig

Retrieved from "https://script.spoken-tutorial.org/index.php?title=PostgreSQL-Database/C2/Data-Redundancy-and-Primary-Key/English&oldid=57430"