View source for PostgreSQL-Database/C2/Data-Redundancy-and-Primary-Key/English

'''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.'''

{| border=1
|- 
|| '''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 tableSelect '''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'''.
|- 
|| '''Slide7 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 and 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 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.
|-
|}