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

From Script | Spoken-Tutorial
Jump to: navigation, search
Time Narration
00:01 Welcome to the Spoken Tutorial on Create a database using pgAdmin.
00:07 In this tutorial, we will learn about how to:

connect to the server,

00:12 database and its objects,

how to create a database,

00:17 Table and its attributes and

how to create a table.

00:23 To record this tutorial, I am using:

Ubuntu Linux 14.04 operating system,

00:30 PostgreSQL 9.3.x and

pgAdmin 1.18

00:38 To follow this tutorial, you should have good understanding of basic computer concepts and

working knowledge of any programming language.

00:48 Let us open the pgAdmin.
00:51 Click Dash Home on the top left corner of the computer desktop.
00:56 In the Search box, type: pgAdmin. The pgAdmin icon appears. Click on it.
01:05 The pgAdmin interface is opened now.
01:09 Here, on the left hand side, we can see the object browser pane.
01:14 The various objects of the database are displayed here.
01:18 The upper right pane shows the relevant properties of each object.
01:23 The lower right pane is the SQL pane where the script of the currently selected object is displayed.
01:30 Let us see how to connect to the server.
01:33 Click on the connection icon on the top left corner of the tool bar.
01:39 New Server Registration window appears.
01:43 In the Properties tab, enter localhost in the Name field and localhost in the Host field.

Keep all the other parameters same.

01:56 The database name, and the Username which are shown here are the names given during installation.
02:03 Enter the password which you gave during installation.

Click on Ok button.

02:10 Small pop-up windows will appear from time to time.

These give additional information for our understanding.

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

I won't explicitly mention this.

02:27 Click on Ok button.
02:30 Here, we can see the connection established to localhost.

Click on the localhost node.

02:38 We can see several objects for this particular server.
02:42 Now, click on the Databases node.

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

02:52 Database is a collection of information or data which is stored in an organized way.
02:59 A database has objects such as tables, views, procedures, functions etc.
03:07 We will learn about these objects in the future tutorials, in this series.
03:12 We can have multiple databases depending upon our application requirement.
03:18 Switch back to pgAdmin interface.
03:21 Now, we will create a new database for our demonstration purpose.
03:26 Right-click on the Databases node and select New Database.
03:31 Type Sampledb as the database name and click Ok.
03:37 Now, we can see the database is listed under the database node, which we created just now.
03:44 Next, we will add a student table to this sampledb database.
03:50 What is table? In RDBMS, we store data in database objects called tables.
03:58 A table is a collection of related data that is stored in rows and columns format.
04:04 This is an example of a STUDENT table.

Tables have columns which are called as fields.

04:11 The fields in the STUDENT table are Student ID, student name, address, city, Phone, Date of birth, CGPA etc.
04:22 A column is a set of value of a particular data type. This is also called as attribute.
04:30 Here, in the STUDENT table, Student Name is one of the attribute that represents names of students.
04:37 Now switch back to the interface.
04:41 To create a new table in Sampledb database, click on the Sampledb node.

Then click Schemas and then Public and Tables.

04:53 Right-click on the table node and select New Table.

Let us give “students” as table name.

05:02 Click on the Columns tab to enter the columns for this table.

Click on the Add button.

05:10 In the new window that opens, enter Student name in the Name field.
05:15 Select character varying as data type from the drop-down list box.
05:20 The data type specifies what type of data the field can hold.
05:25 Enter 50 as the length of the field.
05:29 The length specifies the maximum length of the column of the table.
05:34 Click Ok button to add column to the table.
05:39 We will see what are the common data types used in PostgreSQL.
05:44 Integer, numeric represents numeric data type.
05:48 For example: phone no, age, quantity are of numeric data type.
05:55 numeric(n,d) - is also numeric data type with decimal places.
06:01 Here n is the total digits and d is the number of digits after the decimal.
06:07 It can be used to represent fields like basic salary, unit price etc.
06:14 char and character varying represents string data type.
06:19 Fields such as name, address, email are of string data type.

Text is a string data type with unlimited length.

06:29 Date represents date data type.
06:33 Likewise, I'll add City column with character varying data type and length as 40.
06:41 Add the column Gender as shown.
06:45 Add a Date of Birth column with date data type.
06:49 Next, add a CGPA field. Select the data type as numeric.
06:56 Enter 2 as length and 1 as precision. This is same as the decimal data type.
07:03 We have entered all the columns for the student table. Click on Ok button.
07:11 A window shows some message about Primary keys.

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

07:21 We can see the student table is created in the sampledb database.

Click on it.

07:28 In the SQL pane, we can see the syntax of the create table command generated automatically.
07:35 With this, we come to the end of this tutorial. Let us summarize.
07:40 In this tutorial, we have learnt - how to connect to the server,

database and its objects,

07:47 How to create a database,

Table and its attributes and how to create a table.

07:54 As an Assignment, connect to the server and check the default available database.
08:00 Create a new database Organization. In the Organization database, create a table Emp with the columns Emp name, Address, Date of Birth and Salary.
08:13 The video at the following link summarizes the Spoken Tutorial project.

Please download and watch it.

08:20 The Spoken Tutorial Project team conducts workshops and gives certificates.

For more details, please write to us.

08:28 Please post your timed queries in this forum.
08:32 Spoken Tutorial project is funded by NMEICT, MHRD, Government of India.

More information on this mission is available at this link.

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

Contributors and Content Editors

PoojaMoolya, Sandhya.np14