Exercise 2 - Courseweb

assistantashamedΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 10 μήνες)

230 εμφανίσεις

CSIT 70 A/B Advanced Database Systems Lab


----------------------------------------------------------------------------------------

Part A. Familiarization with PostgreSQL

-----------------------------------------------------------------------------------
-----


Access the PostgreSQL Web site at
http://www.postgresql.org

and read the
interactive documentation at
http://www.postgresql.org/idocs/

especially the “Tutorial” and the “User’s Guide” section:


What is PostgreSQL? (Briefly describe its features/func
tionality).

What are the advanced features of PostgreSQL?

Compare and contrast PostgreSQL with MySQL.


Do a telnet login to appserver.cs.xu.edu.ph and create a Postgres database by typing
'
createdb
' at the Linux prompt (i.e., this command will create a dat
abase named
after your username in the PostgreSQL system).

Run the interactive PostgreSQL command line client by typing ‘
psql
’ at the Linux
prompt. Familiarize with the psql command line interface and issue some queries on
the system catalog (i.e., pg_* r
elations):


What is the difference between

\
h’

and

\
?’

commands?

What are the results if you execute the following SELECT statements:

SELECT * from pg_database;

SELECT * from pg_user;


Submit:
2
-
4 page writeup

(Part A)

Due on: 09

July 2004 (Friday)


---
-------------------------------------------------------------------------------------

Part B. Using PostgreSQL
-

Advanced Features (and DD)

----------------------------------------------------------------------------------------


1.

Login to appserver.cs.
xu.edu.ph (using telnet) and run psql. I presume that you
had already created your database per our preparatory activity Part A of this
exercise.


2.

Create the EMPLOYEE table (i.e., a superclass) using the SQL CREATE
statement below:


CREATE TABLE employe
e


( fname VARCHAR(15) NOT NULL,


minit CHAR,


lname VARCHAR(15) NOT NULL,


ssn CHAR(9) NOT NULL,


bdate DATE,


address VARCHAR(30),


sex CHAR,


salary DECIMAL(10,2),


superssn CHAR(9),


dno INT NOT NULL,


PRIMARY KEY (ssn));


3.

Check whether you have successfully created the EMPLOYEE table (i.e., issue a
‘SELECT

* FROM employee command’)


4.

Create the SECRETARY, TECHNICIAN and ENGINEER tables (i.e., subclasses)
using the SQL CREATE statements below:


CREATE TABLE secretary


( typingspeed INT NOT NULL


) INHERITS (employee);


CREATE TABLE

technician


( tgrade char NOT NULL


) INHERITS (employee);


CREATE TABLE engineer


( engtype char(4) NOT NULL


) INHERITS (employee);


5.

Check whether you have successfully created the SECRETARY, TECHNICIAN
a
nd ENGINEER table (i.e., issue a ‘SELECT * FROM employee command’).
What did you observed?


6.

Create the DEPARTMENT using the SQL CREATE statement below:


CREATE TABLE department


( dname VARCHAR(15) NOT NULL,


dnumber I
NT NOT NULL,


mgrssn CHAR(9) NOT NULL,


mgrstartdate DATE,


dlocation VARCHAR(15)[],


PRIMARY KEY (dnumber),


unique (dname));


7.

Check whether you have successfully created the DEPARTMENT table
. Note that
the DEPARTMENT table has an attribute of type array (i.e., the DLOCATION
attribute).


8.

Add records to the EMPLOYEE, SECRETARY, TECHNICIAN, ENGINEER
and DEPARTMENT tables using the values given below using the SQL INSERT
command:


EMPLOYEE Rel
ation

FNAME

MINIT

LNAME

SSN

BDATE

ADDRESS

SEX

SALARY

SUPERSSN

DNO

John

B

Smith

123456789

09
-
JAN
-
55

731 Fondren, Houston, TX

M

30000

333445555

5

Franklin

T

Wong

333445555

08
-
DEC
-
45

638 Voas, Houston, TX

M

40000

888665555

5

Alicia

J

Zelaya

999887777

19
-
JU
L
-
58

3321 Castle, Spring, TX

F

25000

987654321

4

Jennifer

S

Wallace

987654321

20
-
JUN
-
31

291 Berry, Bellaire, TX

F

43000

888665555

4

Ramesh

K

Narayan

666884444

15
-
SEP
-
52

975 FireOak, Humble, TX

M

38000

333445555

5

Joyce

A

English

453453453

31
-
JUL
-
62

5631

Rice, Houston, TX

F

25000

333445555

5

Ahmad

V

Jabbar

987987987

29
-
MAR
-
59

980 Dallas, Houston, TX

M

25000

987654321

4

James

E

Borg

888665555

10
-
NOV
-
27

450 Stone, Houston, TX

M

55000

Null

1


Example: Add EMPLOYEE record


INSERT INTO EMPLOYEE VALUES (‘Joh
n’, ‘B’, ‘Smith’, ‘123456789’,
’09
-
JAN
-
55’, ‘731 Fondren, Houston, TX’, ‘M’, 30000, ‘333445555’,
5);


SECRETARY Relation

FNAME

MINIT

LNAME

SSN

BDATE

ADDRESS

SEX

SALARY

SUPERSSN

DNO

Alicia

J

Zelaya

999887777

19
-
JUL
-
58

3321 Castle, Spring, TX

F

25000

987654
321

4

Jennifer

S

Wallace

987654321

20
-
JUN
-
31

291 Berry, Bellaire, TX

F

43000

888665555

4


Example: Add SECRETARY record


INSERT INTO SECRETARY VALUES (‘Alicia’, ‘
J
’, ‘Zelaya’,
‘999887777’, ’19
-
JUL
-
58’, ‘3321 Castle, Spring, TX’, ‘F’, 25000,
‘98754321’, 4
,80);


TECHNICIAN Relation

FNAME

MINIT

LNAME

SSN

BDATE

ADDRESS

SEX

SALARY

SUPERSSN

DNO

John

B

Smith

123456789

09
-
JAN
-
55

731 Fondren, Houston, TX

M

30000

333445555

5

Ahmad

V

Jabbar

987987987

29
-
MAR
-
59

980 Dallas, Houston, TX

M

25000

987654321

4


INSERT I
NTO TECHNICIAN VALUES (‘John’, ‘
B
’, ‘Smith’, ‘123456789’,
’09
-
JAN
-
55’, ‘731 Fondren, Houston, TX’, ‘M’, 30000, ‘333445555’,
5,’A’);


ENGINEER Relation

FNAME

MINIT

LNAME

SSN

BDATE

ADDRESS

SEX

SALARY

SUPERSSN

DNO

Franklin

T

Wong

333445555

08
-
DEC
-
45

638 Voas
, Houston, TX

M

40000

888665555

5

Ramesh

K

Narayan

666884444

15
-
SEP
-
52

975 FireOak, Humble, TX

M

38000

333445555

5

Joyce

A

English

453453453

31
-
JUL
-
62

5631 Rice, Houston, TX

F

25000

333445555

5


INSERT INTO ENGINEER VALUES (‘Franklin’, ‘
T
’, ‘Wong’, ‘333
44555’,
’08
-
DEC
-
45’, ‘638 Voas, Houston, TX’, ‘M’, 40000, ‘888665555’,
5,’CoE’);


DEPARTMENT Relation

DNAME

DNUMBER

MGRSSN

MGRSTARTDATE

DLOCATION

Research

5

333445555

22
-
MAY
-
78

{Bellaire, Sugarland, Houston}

Administration

4

987654321

01
-
JAN
-
85

{Stafford
, Dallas}

Headquarters

1

888665555

19
-
JUN
-
71

{Houston}


INSERT INTO DEPARTMENT VALUES (‘Research’, 5, ‘33344555’, ’22
-
MAY
-
78’, ‘{“Bellaire”, “Sugarland”, “Houston”}’);



9.

Check whether you have successfully added the records for the EMPLOYEE,
SECRETARY
, TECHNICIAN ENGINEER, and DEPARTMENT tables by using
the SQL SELECT command:


10. Create a view EMP_DEPT joining the EMPLOYEE and DEPARTMENT table based
on the equality of DNO attribute in EMPLOYEE and the DNUMBER attribute in
DEPARTMENT.


Submit: Will ch
eck your work online (Part B)

Due on: 16 July 2004 (Friday)


----------------------------------------------------------------------------------------

Part C. Using PostgreSQL
-

Advanced Features (and DM)

--------------------------------------------------
--------------------------------------

1.

Continue playing around with PostgreSQL/psql and the database you
created earlier in Part B of this Exercise

2.

What is the difference between:

SELECT * FROM employee;

and

SELECT * FROM employee*;?

3.

What is the d
ifference between:

SELECT * FROM secretary;

and

SELECT * FROM secretary*;?

4.

How do you retrieve specific values of array or multi
-
valued
attributes in PostgreSQL. For example, formulate a query to retrieve
the first location of the Research department fr
om the
DEPARTMENT table.


Submit:
1
-
2 page writeup

(Part C)

Due on:
23 July 2003 (Friday)