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.

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.

Contributors and Content Editors

Ketkinaina