RDBMS-PostgreSQL/C2/Create-database-using-PgAdmin/English

From Script | Spoken-Tutorial
Revision as of 12:59, 31 March 2017 by Nancyvarkey (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

>>Title of script: Create a database using pgAdmin


Author: Nirmala Venkat

Keywords: RDBMS, PostgreSQL, pgAdmin, Database, table, field, record, video tutorial

Visual Cue Narration
Slide 1: Welcome to the Spoken Tutorial on Create a database using pgAdmin.
Slide 2:

Learning Objectives

In this tutorial we will learn about how to
  • connect to the server
  • database and its objects
  • how to create a database
  • Table and its attributes and
  • how to create a table
Slide 3:

System requirement

For this tutorial, I am using
  • Ubuntu Linux 14.04 operating system
  • PostgreSQL 9.3.x
  • pgAdmin 1.18
Slide 4:

Pre-requisites

To follow this tutorial, you should have
  • good understanding of basic computer concepts and
  • working knowledge of any programming language.
Let us open the pgAdmin.
Click Dash home Click Dash Home on the top left corner of the computer desktop.
In the search box type, pgadmin In the Search box, type pgAdmin. The pgAdmin icon appears. Click on it.
Point to the new window pgAdmin interface is opened now.

Here, on the left hand side we can see the object browser pane.

The various objects of the database are displayed here.

Highlight the upper right pane The upper right pane shows the relevant properties of each object.
Highlight the lower right pane The lower right pane is the SQL pane where the script of the currently selected object is displayed.
Point to the connection icon in the tool bar. Let us see how to connect to the server.

Click on the connection icon on the top left corner of the tool bar.

Point to the window. New Server Registration window appears.
Point to Properties tab.

Type localhost in the Name field

Type localhost in the Username field

In the Properties tab, enter localhost in the Name field and localhost in the Host field.

Keep all the other parameters same.

Point as per narration. The database name, Username which are shown here are the names given during installation.
Type the password and press Enter >> Click on OK Enter the password which you gave during installation.

Click on Ok button.

Point to the window. Small pop-up windows will appear from time to time.

These give additional information for our understanding.

Always click on this check box 'Do not show this hint again', before you close these windows.

I won't explicitly mention this.

Click on Ok button.

Point to the connection

Click on the localhost node.

Point to the objects under the server

Here, we can see the connection established to localhost.

Click on the localhost node.

We can see several objects for this particular server.

Click on Databases Now click on the Databases node.

The default database 'postgres' that is created during installation is displayed here.

Slide 5:

Database

Image

Database.svg

Database is a collection of information or data, which is stored in an organized way.

A database has objects such as tables, views, procedures, functions, etc.

We will learn about these objects in the future tutorials in this series.

We can have multiple databases depending upon our application requirement.

Back to pgadmin Switch back to pgAdmin interface.
Now, we will create a new database for our demonstration purpose.
Right-click on Databases >> select New Database Right-click on the Databases node and select New Database.
Type Sampledb >> Click Ok Type Sampledb as the database name and click Ok.
Point to the database Now, we can see the database is listed under the database node, which we created just now.
Next we will add a student table to this sampledb database.
Slide:

What is table?

In RDBMS, we store data in database objects called tables.

A table is a collection of related data that is stored in rows and columns format.

Slide:

Show the table image:student.jpg

This is an example of a STUDENT table.

Tables have columns which are called as fields.

The fields in the STUDENT table are Student ID, student name, address, city, Phone, DOB, CGPA.

Slide:

What is a Column?

Show the student_ column.jpg

A column is a set of value of a particular data type. This is also called as attribute.

Here in the STUDENT table, Student Name is one of the attribute that represents names of students.

Back to interface. Now switch back to the interface.
Click on Sampedb >> Schemas >> Public >> Tables To create a new table in Sampledb database, click on the Sampledb node.

Then click Schemas and then Public and Tables.

Right-click on table >> New Table >> Type students Right-click on the table node and select New Table.

Let us give “students” as table name.

Click on Columns >> Add button Click on the Columns tab to enter the columns for this table.

Click on the Add button.

Type Name = Student name In the new window that opens, enter Student name in the Name field.
Select character varying Select character varying as data type from the drop-down list box.

The data type specifies what type of data the field can hold.

Type 50 in length field. Enter 50 in the length field.

The length specifies the maximum length of the column of the table.

Click Ok button Click Ok button to add column to the table.
We will see what are the common data types used in PostgreSQL.
Slide

Data types:

integer, numeric - numeric data type

numeric (n,d) – where n is the total digits and d is the number of digits after the decimal

char (size) - holds a fixed length string

character varying(size), varchar(size) - holds a variable length string

text – variable unlimited length

Date – date data type

Integer, numeric' represents numeric data type'Bold text.

For example, phone no, age, quantity are of numeric data type.

numeric(n,d) - is also numeric data type with decimal places.

Here n is the total digits and d is the number of digits after the decimal.

It can be used to represents fields like basic salary, unit price etc.

char and character varying represents string data type.

Fields such as name, address, email are of string data type.


Text is a string data type with unlimited length.

Date represents date data type.

Add City column Likewise, I'll add City column with character varying data type and length as 40.
Add Gender column Add the column Gender as shown here.
Add Date of Birth column Add a Date of Birth column with date data type.
Add CGPA column Next add a CGPA field. Select the data type as numeric. Enter 2 as length and 1 as precision.

This is same as the decimal data type.

Click Ok We have entered all the columns for the student table. Click on Ok button.
Point to window A window shows some message about Primary keys.

We will see the importance of Primary keys in the future tutorials.

Point to student table. We can see the student table is created in the sampledb database.
Click on students table. Click on it.
Highlight SQL pane In the SQL pane we can see the syntax of the create table command generated automatically.
With this, we come to the end of this tutorial. Let us summarize.
Summary In this tutorial, we have learnt -
  • how to connect to the server
  • database and its objects
  • how to create a database
  • Table and its attributes and
  • how to create a table
Assignment As an Assignment,
  1. Connect to the server and check the default available database
  2. Create a new database Organization.
  3. In the Organization database, create a table Emp with the columns Emp name, Address, DOB and Salary.
Slide 12:

About Spoken Tutorial project

[1]

The video at the following link summarises the Spoken Tutorial project.

Please download and watch it.

Slide 13:

Spoken Tutorial workshops

The Spoken Tutorial Project Team
  • conducts workshops
  • gives certificates

For more details, please write to us.

Slide 13:

Forum for specific questions

Please post your timed queries in this forum.
Slide 14:

Acknowledgement

Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

This is Nirmala Venkat from IIT Bombay, signing off. Thanks for watching.

Contributors and Content Editors

Nancyvarkey, Nirmala Venkat