CSC 410 – Database Systems

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

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

157 εμφανίσεις

CSC 410


Database Systems


PostgreSQL

Procedures



I.

Each student user is responsible for the following


A.

C
reate
in
her/his own home directory, a

public_
html
” directory


1.

~self:
mkdir public
_
html


2.

Execute
chmod 755 public_
html


3.

The associated URL to the use
r’s web
-
space will be



http://ifrit.centre.edu/
self/etc

, but the user will actually be
working on



/home/self/public_html/etc

on ifrit.


B.

Organizational Suggestions


1.

The Web project should be appropriat
ely managed (organized) within
the ~self/public
-
html directory.


a.

For example


csc410/project


b.

A directory with include functions is also desirable / good
programming practice


2.

Running the .php script from a web browser requires


a.

the URL defined in I
.A
.3.

above and


b.

a database properly designed and populated with appropriate data


C.

Creation of a Working Database


1.

Each db admin must create own database, tables, and sequences, and
populate the database with appropriate data


a.

From the shell


$
createdb db_nam
e



b.

Confirm its existence

$
psql

db_name




PostgreSQL Procedures



Page
2

of
2



2.

Each existing database owner must then grant a complete set of
privileges to user
nobody


$

psql
db_name

dbname
=
#

grant

all on table_name to nobody
;


D.

Create database relations (tables)


1.

Create a flat ascii text

file with SQL schema construction language /
the syntax will be discussed in class.


2.

Execute
$
psql db_name <
source_file.name


E.

Load data into tables.


1.

Copy from file

/ from within the database environment


db_name=
#
copy
table
_name from ‘full path na
me of file’ using
delimiters ‘<ch>’ with null as ‘null string’;


2.

Insert discreet tuples from psql command line


F.

Additional syntax details


1.

If a serially
-
generated row number is desired, use


CREATE SEQUENCE “sequence_key_id” START 1 INCREMENT 1
MAXVALUE 2
147483647 MINVALUE 1 CACHE 1;






MAXVALUE = 2
31



1


2.

Create a relation


CREATE
TABLE table_name


(


seq_attr_id


integer DEFAULT nex
t
val

(‘sequence_key_id’)

NOT
NULL,


2
nd
_attr

data_type2,


3
rd
_attr

data_type3,



. . .


last_attr

data_type_l,


[
“constraint_1,



.

.

.



“constraint_k ]







);