Relational Database -Assignment 1d Solved

Assignment 1d

Continue to work with your partner and the script you created in Assignments 1a, 1b and 1c. You are adding new code to the pre-existing script that already has code for Assignments 1a, 1b and 1c and submitting me the complete script. PLEASE MODIFY THE PURPOSE OF THE SCRIPT AT THE HEAD as you build this script.

PLEASE SUBMIT A RUNNING SCRIPT WITH NO ERRORS TO ME. IF THE SCRIPT DOES NOT RUN COMPLETELY, I WILL ONLY MARK UNTIL WHERE IT RUNS OR THE OUTPUT(S) I CAN SEE. I WILL NOT CORRECT YOUR SCRIPT OR MARK THE CODE AFTER THE STATEMENT CAUSING THE ERROR, EVEN IF THEY ARE CORRECT. THEREFORE, IF SOMETHING DOES NOT WORK IN YOUR SCRIPT, REMOVE THE CODE BUT PLEASE GIVE ME AN ERROR FREE COMPLETELY RUNNING SCRIPT.

You have created a table of your choice with relevant fields (Assignment 1a), retrieved data from this table (Assignment 1b) and updated and deleted data of/from this table (Assignment 1c).  You are now looking to modify the structure of the table. You need to add new field(s), change, modify and drop fields.

Important Note: Use literal text data in quotes, numeric data without quotes and CAST literals to DATE wherever required. You will lose marks if you do not follow this instruction.

I need you to comment the functionality of ALL new statements you code in this assignment.

Please code ALTER TABLE statement(s) that satisfy at least the following requirements.  

1.              At least one instance of the ‘Add Column’ to add a new field of type string – adding a new column is just like adding a field definition in the Create Table command. Ensure it has all the properties you need, for example NOT NULL etc. You may add more than one column if required, see following update requirement.

Now update the existing records to populate this new column(s) with relevant data using one or more string functions. Hint: You could always split an existing string field data into more than one fields, for example a name or address field.

2.              At least one instance of the ‘Modify Column’. Recall you have to rewrite all properties you want to keep, for example NOT NULL etc. Move the column to a different position too.

3.              At least one instance of the ‘Change Column’. Recall you have to rewrite all properties you want to keep, for example NOT NULL etc. Move the column to a different position too.

4.              At least one instance of the ‘Drop Column’ to drop an irrelevant field in the table. You could always drop the column that you have ‘split’ in requirement 2, since you are not losing any data. If you do not have any column to drop, add a ‘fake’ column and then drop it.

5.              Just change the name of a field using the ‘Rename Column’ command.

6.              Adding a UNIQUE constraint to one of the pre-existing fields or to a new field, not to be combined with requirements 1 to 3 above.

 

Now display the structure of the table and all its data after you have completed the above. This will help me ensure that all the requirements have been met.