RDBMS-PostgreSQL/C2/Create-database-using-PgAdmin/English-timed
From Script | Spoken-Tutorial
| 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. |
