Using the same subset of 4 (four) tables from the Project 1 ERD you used for your Access database, develop and run the SQL DDL (data definition language) statements necessary to construct an Oracle database on the UMUC Nova Server that exhibits the proper relational database design practices of integrity and normalization.
You should use a re-runable SQL script file for this step (e.g. “p2_step1.sql”), which may be called from parent SQL script file (e.g. “project2.sql”). By “re-runnable” it is meant that your SQL script can be run over and over again without any modification, so it will need to include the necessary DROP TABLE statements prior to corresponding CREATE TABLE statements.
Eventually you need to run your SQL script file while logged on to the Nova server via iSQL*Plus or SQL*Plus, but you may develop your SQL script on any suitable Oracle host. Your created database tables must reside on Nova in your own schema.
Your script files must contain all necessary SQL*Plus commands for echoing SQL statements and capturing output in a SPOOL file. Via the SQL CONSTRAINT keyword, you must explicitly name all tables’ constraints for primary keys, foreign keys, check and unique constraints (you do not need to name NOT NULL constraints). Note that there is no SPOOL command for iSQL*Plus so if you use that option, simply redirect your SQL execution output to an HTML file and submit that in lieu of a SPOOL file.
Using the appropriate SQL DML (data manipulation language) statements, populate each table in your Oracle database on the Nova server with at least two rows of data each. You should do this via a re-runable SQL script file (e.g. “p2_step2.sql”), which would be called from your parent SQL script file (e.g.”project2.sql”) immediately following the calling of your Step #1 script file.
Be sure to include a COMMIT statement in your SQL script file.
Once all tables are populated, demonstrate via SQL “SELECT *” statements in your script file that your tables are properly populated. You should plan to develop your SQL script file on the Nova server, via a remote connection via SQL*Plus, or iSQL*Plus. Note that there is no SPOOL command for iSQL*Plus so if you use that option, simply redirect your SQL execution output to an HTML file and submit that in lieu of a SPOOL file
Take a screen snapshot of your 4 (four) tables on Nova. This can be accomplished via the SQL statement SELECT table_name FROM user_tables . Also, do a DESC (i.e. DESCRIBE) of each table and then take a screen snapshot of the columns of each table show in the output.
Take screen snapshots of the contents your 4 (four) tables on Nova. This can be accomplished via the SQL statement SELECT * FROM your_table_name_here .