Transcription
In the name of AllahIslamic University of GazaFaculty of EngineeringComputer Engineering DepartmentECOM 4113DataBase LabLab # 1Introduction to PostgreSQLEng. Haneen El-masry2013
DataBase LabObjective To be familia with PostgreSQL DBMS.BACKGROUNDWhat is a DataBase?A database is a collection of related data that is known facts that can be recorded and that haveimplicit meaning.Examples: University, Library, ect.A database may be generated and maintained manually or it may be computerized. Acomputerized database may be created and maintained by a database management system(DBMS).What is the DBMS ? DBMS is a collection of programs that enables users to create and maintain a database.DBMS is a general-purpose software system that facilitates the processes of defining,constructing, manipulating, and sharing databases among various users andapplications.DBMS includes protecting the database and maintaining it over a long period of time.DBMS Responsibilities Creating the database.Providing query and update facilities.Multitasking.Managing the security of the database.Maintaining referential integrity.Examples:Oracle, SQL Server, MySQL, PostgreSQL.For DataBase Lab, we choose PostgreSQL 9.2.2Eng. Haneen
DataBase LabDataBase SystemThe database and DBMS software together are called a database system.Figure 1: DataBase SystemPostgreSQL is a full-featured open-source DBMS.It provides a solid relational engine with: Efficient implementation of relational operations. Very good transaction processing (concurrent access). Good backup/recovery (from application/system failure).Already supports several non-standard data types.Allows users to define their own data types.3Eng. Haneen
DataBase LabPostgreSQL Installation on Windows1- Set the installation location on your system Next.2- Set the location of data directory that contains all of the data files for PostgreSQL.3- Enter the password for the database superuser “postgres”, that has a full access to all ofthe system tables and features in PostgreSQL.Note: Save this password carefully.4Eng. Haneen
DataBase Lab4- Select the port number that assigns a specific TCP port to the PostgreSQL server so thatapplications can connect to send queries. The default value for the PostgreSQL port is5432. You can elect to change this value, but it is important that you remember the newvalue you assign, as it must be used for all communications with the PostgreSQL system.5- The Locale parameter is where you configure the language used on the PostgreSQLsystem, leave it a default value click Next.5Eng. Haneen
DataBase Lab6- you’ve finished providing information for the PostgreSQL installer, Click Next.7- Click Finish.6Eng. Haneen
DataBase LabPostgreSQL ApplicationsThere are two applications that are included in the PostgreSQL installation. These applicationshelp you interface with the PostgreSQL server and provide an easy way to administrating andusing of databases.psqlThe psql application provides a command-line interface to the PostgreSQL system. psqlcommands are of two different types: SQL commands: We can issue any SQL statement that PostgreSQL supports to psql, andit will execute it.Internal commands: These are psql commands used to perform operations not directlysupported in SQL. All internal commands begin with a backslash (\).To start psql:Start programs postgreSQL 9.2 sql shell (psql).pgAdmin IIIThe pgAdmin III application is a program that provides a graphical interface for administering aPostgreSQL system. It allows you to perform any database function from a graphical front end.The pgAdmin III program also includes a SQL command interface.To start pgAdmin III:Start programs postgreSQL 9.2 pgAdmin III.7Eng. Haneen
DataBase LabConnection to postgreSQL serverYou need to specify the following five parameters to connect to PostgreSQL: host or host address.port.database name.user.password. Connection using pgAdmin III1- Start pgAdmin III.2- By default, pgAdmin III is configured to connect to a PostgreSQL server running on the localsystem , and use the default PostgreSQL TCP port of 5432.3- To connect to the server, double click on the server or right click Connect.8Eng. Haneen
DataBase Lab4- By default, pgAdmin III will attempt to connect to the server using the standard postgressuperuser account. Enter the password of postgres.5- Now, you are connected to the server .9Eng. Haneen
DataBase Lab6- If you need to change the default login account or the default TCP port number for aserver, right-click the server entry in the main window and select Properties from themenu. In the Properties window you can set the IP address, TCP port, the defaultdatabase name, and the user account to log into the PostgreSQL server with.10Eng. Haneen
DataBase Lab7- If you want to use pgAdmin III to connect to remote PostgreSQL servers: File menu Add Server or click on11icon.Eng. Haneen
DataBase Lab Connection using psqlEnter all the required information such as server, database, port, username and password.If you press Enter, psql will use default values inside the square brackets [].After connection, psql will prompt for commands with a prompt that consists of the name ofthe database we are connected to, followed by # for administrative users, but for other usersthe prompt is replaced with .POSTGRESQL SYSTEMWhen you are connected to a server, the pgAdmin III window splits into three frames.A graphical representationof all the objects containedon the PostgreSQL server.Detailed configurationvalues of the objectcurrently selected in the leftframe.The SQL code used tocreate the object currentlyselected in the left frame.12Eng. Haneen
DataBase LabThere are five basic components that make up the PostgreSQL server:Databases.Tablespaces.Schemas (listed under each individual database).Group Roles.Login Roles.DatabasesDatabases are the core objects in PostgreSQL. They hold all of the data objects used inPostgreSQL.The default database created during the PostgreSQL installation is called postgres. It containsthe default system tables for handling the internal PostgreSQL Data Dictionary. These tables arenot shown in pgAdmin III, but can be accessed via SQL queries.There are two additional databases that are configured by default in PostgreSQL, but not shownin pgAdmin III: template0.template1.These are generic templates that are used to create new databases.TablespacesTablespaces are the physical locations where objects are stored. Objects can be anything fromdatabase tables, indexes, functions, and triggers.By default two tablespaces are created: pg defaultIt is the default location for all database objects created on the PostgreSQL system. pg globalIt is used to hold internal Data Dictionary information for the PostgreSQL system to operate.SchemasSchemas are the most important objects within the database. A schema contains the tables,triggers, functions, views, and other objects for handling data in the database.13Eng. Haneen
DataBase LabSchemas have two purposes: To help manage the access of many different users to a single database.To allow extra tables to be associated with a standard database, but kept separate.By default, the template0 and template1 templates contain a schema called public.Group RolesGroup Roles are used to create access permissions for groups of users.By default, there is one Group Role configured in PostgreSQL. The public group role applies toall users on the PostgreSQL system. You are not able to remove any user account from thepublic Group Role. Because of this, the public Group Role does not appear in the pgAdmin IIIGroup Roles listing.Login RolesLogin Roles are roles that are allowed to log into the PostgreSQL server. They are also known asuser accounts.Create User Account Using pgAdmin III1- Right click on Login Roles New Login Role.14Eng. Haneen
DataBase Lab2- Enter Role name, password and privileges.15Eng. Haneen
DataBase LabNote: By default, only postgres user is allowed to create new Login roles and DataBase. Using psqlUsing sql command:CREATE USER username [WITHPASSWORD 'password'CREATEDB NOCREATEDBCREATEUSER NOCREATEUSERIN GROUP groupname VALID UNTIL 'abstime' ];Modifying UserWe can modify user properties as name, password and privileges. Using pgAdmin IIIRight click on the username Enter new properties.16Eng. Haneen
DataBase Lab Using psqlUsing sql command:ALTER USER username [WITHPASSWORD 'password'CREATEDB NOCREATEDBCREATEUSER NOCREATEUSERIN GROUP groupname VALID UNTIL 'abstime' ];There is also a special variant for renaming a user:ALTER USER username RENAME TO new-username;Deleting User Using psqlUsing sql command:Drop User username; Using pgAdmin IIIRight click on the user name Delete/Drop.Creating New DataBase Using psqlUsing SQL command:CREATE DATABASE dbname [WITH OWNER [ ]owner ][ TEMPLATE [ ] template ][ ENCODING [ ] encoding ][ TABLESPACE [ ] tablespace ] ]17Eng. Haneen
DataBase LabNote: The default owner for the new Database is the user who logged into the server. Using pgAdmin III1- Right click on Databases New Database.2- Enter Database name and its owner.18Eng. Haneen
DataBase LabAltering DataBaseWe can change the name and owner of a database. Using psqlALTER DATABASE dbname RENAME TO newname;ALTER DATABASE dbname OWNER TO newowner; Using pgAdmin IIIRight click on the database properties Enter the new properties.Deleting DataBase Using psqlDrop Database DBName; Using pgAdmin IIIRight click on the DataBase Delete/Drop.SQL Query Tool on pgAdmin IIITo open SQL query tool for database: click on DataBase click onicon.You can quickly change your database connection from one database to another, withoutlaunching another instance of the query tool, following the next steps:1- new connection from the combobox.19Eng. Haneen
DataBase Lab2- Enter Database name and Username.Changing the database connection Using psqlUse the internal command command:\c dbname username20Eng. Haneen
DataBase LabDataBase Backup1- Right click on Database name choose Backup.2- Browse for backup file location.3- Enter the format of the file, encoding and role.21Eng. Haneen
DataBase Lab4- Click Done .DataBase Restore1- Create a new DataBase.22Eng. Haneen
DataBase Lab2- Right click on the new database Restore.3- Select backup file.4- Enter the format and the role click OK.23Eng. Haneen
DataBase Lab5- Click Done .Disconnecting from the server Using psqlUsing the internal command \q. Using pgAdmin IIIRight click on the server Disconnect server.24Eng. Haneen
DataBase LabExercisesUsing psql, Do the following:12345678-Create a new user (U1) with your name and give him a password.Create a new database (DB1) and make it is owned by the new user (U1).Give the new user permissions to create database and user.Create another new user (U2).Change the owner of the database (DB1) to the new user (U2).Connect to the database (DB1) as the first user.Create a new database (DB2).Connect to DB2 as the second user.Using pgAdmin III, Do the following:123456-25Create two new users.Give the second user permission to create database.Create two databases.Make the first user to be the owner of the two databases.Open SQL query tool of the first database as the first user.Open SQL query tool of the second database as the second user.Eng. Haneen
help you interface with the PostgreSQL server and provide an easy way to administrating and using of databases. psql The psql application provides a command-line interface to the PostgreSQL system. psql commands are of two different types: SQL commands: We can issue any SQL statement that PostgreSQL supports to psql, and
