Q#1: With the SQL*Plus command language, we can list all of SH schemas accessible tables:

select table_name SELECT, FROM, etc. clauses on different source

from all_tables lines is preferred coding style

where owner = SH;

all_tables is a view name, meaning that all_tables is an identifier for a pre-defined Oracle view. The DD has thousands of useful views, many (but not all) accessible to all Oracle users.

Important DD note: The Oracle DD stores names (table, view, index, etc.), by default, in upper case; in the above query, sh instead of SH returns 0 rows this is user error, not an Oracle error! (but compare with SQLPLUS_204_s18.doc naming rules section).

Backgrounder: Technically, the SH schema is always part of a typical Oracle RDBMS install.

It is a demo/example of a data warehouse. The purpose of SH is not to house billions of rows of data. Instead, the SH schema layout illustrates a so-called star schema. Tables sales, customers, etc. are NOT in a 3NF schema structure; a pure star schema is quite different from 3NF. We will not work with data warehousing in CSC204 (except to occasionally mention a few things about it). SH used here as a non-trivial data source.

This first question shows the unusual SH schema primary key/foreign key (PK/FK) structure.

Code a SQL query with rowformat: OWNER, CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, CONSTRAINT_TYPE

but only tuples where CONSTRAINT_TYPE is P (for PK) or R (for FK, and, yes, it is R, not F). Sort by TABLE_NAME.

To get oriented, here is a reference (posted in the CSC204 homePage subdirectory /homework) for the basic Oracle DD info about the ALL_TABLES schema:

http://athena/~mitchell/csc204/homework/oracle11gRel2_OracleDatabaseReference_ALL_TABLES.html

= => Browse the web to find similar schema layout for DD views ALL_CONSTRAINTS and ALL_CONS_COLUMNS. Note that column names in the above 5-tuple specification are the exact names of these columns in these two DD views.