lab1 | accessing the databasesite.iugaza.edu.ps/oradwan/files/lab1-1.pdflab1 | accessing the...

Islamic University of Gaza Faculty of Engineering

Department of Computer Engineering

ECOM 3422: Database Systems [spring 2020] Abeer J. Al-Aydi & Abdallah H. Azzami

Lab1: Accessing the Database

Objectives

1. Setting up a database system.

2. Connecting to database using both of graphical and text-based interfaces.

3. Be familiar with the database system.

4. Populating database with data, and run very simple SQL queries.

Table of Content

Objectives________________________________________________________________________________________ 1

Table of Content __________________________________________________________________________________ 1

1. 1 Lab terminology _______________________________________________________________________________ 2

1.2 Installing PostgreSQL ____________________________________________________________________________ 2

1.3 Installing DBeaver ______________________________________________________________________________ 9

1.4 Import DBeaver license _________________________________________________________________________ 13

1.5 Connecting to the database ______________________________________________________________________ 13

1.5.1 Using graphical interface [DBeaver] ____________________________________________________________ 14

1.5.2 Using default text-based interface [psql] ________________________________________________________ 16

1.6 Create tables _________________________________________________________________________________ 17

1.7 Load sample data ______________________________________________________________________________ 20

1.8 Try out some queries ___________________________________________________________________________ 21

1.8.1 Labwork1 _________________________________________________________________________________ 21

1.8.2 Labwork2 _________________________________________________________________________________ 21

1.9 Homework ___________________________________________________________________________________ 21

1.10 Lab policy ___________________________________________________________________________________ 22

1.11 Grading _____________________________________________________________________________________ 22

Lab1 | Accessing the Database

of 22

1. 1 Lab terminology

Data: is a collection of facts about the object of interest.

For e.g. Data about an employee would include information like name, address, age, educational qualifications, etc.

Database: is a shared collection of logically related data and description of these data, designed to meet the

information needs of an organization.

Database management system [DBMS]: is a software system that uses a standard method of cataloging,

retrieving, and running queries on data. DBMS manages incoming data, organizes it, and provides ways for the data to

be modified or extracted by users or other programs.

DBMSS examples: MySQL - Oracle - SQL Server - Microsoft Access - IBM DB2 - PostgreSQL.

SQL: is a standardized structured query language for communicating with a database. SQL statements are used to

perform certain operations on existing database and also to create a database. Using SQL a client program can generate

queries on database.

1.2 Installing PostgreSQL

PostgreSQL is a powerful, open source object-relational database management system.

https://www.postgresql.org/about/


of 22


of 22

To verify the installation open the SQL Shell “psql”.

Enter all the necessary information such as the server, database, port, username, and password. To

accept the default, you can press Enter.

Note that you should provide the password that you entered during installing the PostgreSQL.

Enter the command SELECT version(); you will see the result as follows:


of 22

1.3 Installing DBeaver

DBeaver is an SQL client and a database administration tool, it is a graphical database management tool for database

developers and administrators.

Why DBeaver EE?

DBeaver EE comes with all existing extensions, you don’t need to install anything else.

Contains data viewer and editor, Mock-Data generator, SQL editor, and ER diagrams.


of 22


of 22

1.4 Import DBeaver license

1.5 Connecting to the database

When a client application (psql shell, pgAdmin, DBeaver, etc.) connects to a Postgres server, it must specify which

database it wants to connect to.

- Initially PostgreSQL database cluster contains a database named postgres, which is meant as a default database for use

by utilities, users, and third-party applications. The database server itself does not require the postgres database to

exist, but many external utility programs assume it exists.

[Note: A database cluster is a collection of databases that is managed by a single instance of a running database server.]

- PostgreSQL has standard system database named template1 which is the default database template for creating new

databases, these are created just like template1.

- There is a second standard system database named template0. This database contains the same data as the initial

contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should

never be changed after the database cluster has been initialized.

Paste your keys here

key starts with - - DBeaver EE LICENSE – and ends

with ==


of 22

1.5.1 Using graphical interface [DBeaver]

As we mention, DBeaver is a graphical interface for database management, it acts as SQL client in our database system.


of 22


of 22

After connecting to PostgreSQL database server, we can view and explore its main database [postgres].

1.5.2 Using default text-based interface [psql]

PostgreSQL comes with its own text-based interface - SQL shell (psql) - that acts in our database system as SQL client.

Psql is the interactive terminal for working with Postgres. Let’s create and connect to database using psql:


of 22

1.6 Create tables

Create a new database “UNI1”.


of 22

Set new database “UNI1” to Active.

Create new schema “uni-space” and set to active.


of 22

Open “DDL.sql” file which is in your downloaded lab folder.

DDL.sql contains sql statements to create tables of the university database that we use in course’s text.


of 22

Press “F5” to refresh database, then explore “uni-space” tables.

1.7 Load sample data

Open “smallRelationsInsertFile.sql” file which is in your download lab folder.

The file smallRelationsInsertFile.sql contains data that matches Appendix A of textbook exactly.

The file contains SQL insert statements to load data into all the tables, after first deleting any data that the tables

currently contain. The data include students taking courses outside their department, and instructors teaching courses

outside their department; this helps detect errors in natural join specifications that accidentally equate department

names of students or instructors with department names of courses.

Press “Alt+X” to execute the script.


of 22

After loading data, double-click any table to view its fields and data records, you can choose other table to view.

1.8 Try out some queries

select * from instructor; select name from instructor where dept_name = 'Comp. Sci.' and salary > 70000;

select * from instructor, department where instructor.dept_name = department.dept_name;

1.8.1 Labwork1

Execute the previous SQL statements using graphical interface - DBeaver –

1.8.2 Labwork2

Execute the previous SQL statements using default text-based interface - psql – Include results screenshots in your lab report.

1.9 Homework

1. Write a brief about differences between oracle and PostgreSQL.

2. What is the DBMS that is used in our university?

3. In UNI1 database, create new schema and name it “uni-largeSpace”

4. Execute script of “DDL.sql” on the new schema.

5. Execute scripts of “largeRelationsInsertFile.sql” on the new schema, to load data.

6. Execute the following query, using text-based interface, and include your result screenshot in your report

SELECT CURRENT_TIMESTAMP, count (*) from “uni-largeSpace”.instructor;


of 22

1.10 Lab policy

A quiz will be given each lab in first 3 minutes. You should be early to the lab session. Reports should be ready on my table in the lab before our lab session. Provide a cover page including your name in English, your ID, your class number, the report title and number,

and the submission date. late submissions will not be accepted. Copiers are penalized. A result of zero will be graded for both.

1.11 Grading

Lab works+ Attendance 6 Quizzes 4 Homework 8 Practical Mid-Exam 7 Projects 25

lab1 | accessing the databasesite.iugaza.edu.ps/oradwan/files/lab1-1.pdflab1 | accessing the...

Documents