CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

CMIS 320 Lab 3 Homework 3 Data Modeling and SQL script

Homework Submission Requirements:
Homework should be submitted as an attached file in your assignment folder. Text for coding and result of execution and MS Word format for documenting are preferred. Data modeling diagram could be copied and pasted into a Word doc.
You must name your file to include your first initial of your first name and last name.
Part I is 4 points and Part II is 1 points. Total is 5. Quiz is not required for grading.

Part I Data Modeling
You are working for country club with thousands of members. You have been tasked with designing a database to keep track of the members and their guests.
The club keeps track of mail and telephone contact information, name and membership number. When a member joins the club they can become a social member with pool, racket ball and weight room privileges or golf member which includes all of the social member privileges plus access to the golf course.
Develop, document and design an EER for this situation.
Submit your word document no later than the due date.

Part II Lab 3
In lab 2, you will learn how to edit and run a SQL script in your schema in NOVA Oracle database. You also continue to learn simple SQL commands, such as select and insert …

Build and execute SQL script
In this lab, we learn how to write a SQL script that can be run on NOVA Oracle database to execute the SQL commands included in the file. You are provided with SQL Statements and sqlplus commands to add into the script. What you are requested to do is to reorganize the commands in proper order to avoid runtime errors.
How to write a script: The script file is a text file which contains executable commands. For SQL script, it contains SQL statements which can be executed against a proper database. For our SQL script that runs against Oracle database. Therefore, we include some of Oracle specific SQLPlus commands that help us make output clear and understandable and allow us to save the output file.
Get the txt file below that contains SQL and SQLPlus commands. Spool commands are for getting the execution results. Set echo on or off are for getting executed commands printed on the screen before the execution result. Others are all SQLcommands. These commands should work as they are without modification.
Download lab3.txt
There are a few simple suggested rules that can make your script robust.
Use remark, rem, to add comment or reminder in the script anything followed rem is not executed.
Spool on command should be the first command and followed
Set echo on should be before any execution commands begin.
Set echo off and spool off at the end of the file.
For SQL DDL commands. Drop the table before create it. this gives you clean database to begin with. The table created last should be dropped first.
For SQL DML, delete the records first before insert them into the table. This allow you begin with empty table. delete the records from child table first if primary key and foreign key constraints exist between the two tables. However, insert into parent table first then insert into child table. Don’t forget the child record needs a refernce key value from the parent table. Always end with commit command to save the data.
you can use vi text editor to edit the script on NOVA or use a text editor at local to create and edit the script and upload to Nova using sFTP. The script MUST be saved with .sql extension in order for it to be run in SQLPLus
After you successfully create and executed your script. Transfer your output file back and Submit it through assignment for grading.