COP 6731 Theory and Implementation of Database Systems

Connecting to Academic Network Computers via bast.hpc.fau.edu

To access academic network computers, connect through the bast.hpc.fau.edu server.

Access Instructions: See detailed guide

Request Access: Students may request individual access here

SSH Access Example: ssh yangk@bast.hpc.fau.edu

Accessing the FAU Oracle Database System for SQL Practice

Follow the steps below to connect to the FAU Oracle DBMS:

1) Connect to the bast.hpc.fau.edu server via SSH: ssh your_fau_id@bast.hpc.fau.edu

2) SSH into the Oracle Linux Server: ssh your_fau_id@10.166.96.101 (Note: The default password is oracle.)

3) (Optional) Change Your Linux Password: For security purposes, update your Linux account password using the following command: passwd

4) Start SQL*Plus: Launch the SQL*Plus command-line interface sqlplus your_fau_id (Note: The default password is also oracle.)

5) When prompted, enter your Oracle Database username and password. (The default password is 'oracle')

6) (Optional) Change Your Oracle Password: Once logged in to SQL*Plus, change your Oracle database password: password

Homework Submission

Your submission consists of two parts: 1) all answers for Question and Lab parts (e.g., SQL statements) and 2) spool file for outcomes of queries.

Please note that the first file should include the SQL statements in the Lab part.

How to create a spool file

SPOOL [file_name] : Stores query results in a file.

(e.g., SQL> spool Z00000001_KwangSoo_Yang_HW01_spool.txt)

The SPOOL command creates the spool file on the current directory.

(Optional) You can create the spool file to a specific directory

(e.g., SQL> spool /home/yangk/spool/Z00000001_KwangSoo_Yang_HW01_spool.txt)

See the details: here

How to see the contents of a spool file

CAT [file_name] : Reads data from the file and gives their content as output.

(e.g., cat Z00000001_KwangSoo_Yang_HW01_spool.txt)

Please check if the spool file contains all SQL statemtnes and outputs.

How to download a spool file

Use the sftp command

See the deatils: here

Sample DataSet

1) Reservation

2) Company

Primary and Foreign key

Retrieve primary keys

  SELECT ACC.table_name, ACC.column_name, ACC.position
  FROM all_constraints AC, all_cons_columns ACC
  WHERE AC.constraint_name = ACC.constraint_name AND AC.owner = ACC.owner 
  AND AC.constraint_type = 'P' AND AC.owner ='COP6731'
  ORDER BY AC.table_name, ACC.position;

Retrieve foreign keys

  SELECT ACC.table_name, ACC.column_name, ACC.position
  FROM all_constraints AC, all_cons_columns ACC
  WHERE AC.constraint_name = ACC.constraint_name AND AC.owner = ACC.owner 
  AND AC.constraint_type = 'R' AND AC.owner ='COP6731'
  ORDER BY AC.table_name, ACC.position;

Make everything as simple as possible, but not simpler.

Albert Einstein