RelationalDatabase Assignment 1d Solved

Description

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.