The assignment is in the photo and the files are below

createCONTACT_INFO:

CREATE TABLE contact_info( contactID int, firstname varchar(15), middleinitial char(1), lastname varchar(25), suffix_description varchar(5), title_description varchar(5), jobtitle varchar(35), department varchar(30), email varchar(40), url varchar(55), IMaddress varchar(25), phone_number varchar(20), phonetype_description varchar(8), birthday date, notes varchar(255), companyname varchar(35), street1 varchar(45), street2 varchar(45), city varchar(25), state_province varchar(20), zip_postalcode varchar(10), country_region varchar(15), company_url varchar(45), company_phone varchar(12), CONSTRAINT contact_info_pk PRIMARY KEY(contactID) );

insertContact_Info:

INSERT INTO contact_info (contactID, firstname, middleinitial, lastname, suffix_description, title_description, jobtitle, department, email, url, IMaddress, phone_number, phonetype_description, birthday, notes, companyname, street1, street2, city, state_province, zip_postalcode, country_region, company_url, company_phone) VALUES (1, 'William', 'W', 'Destler', 'I', 'Dr.', 'President', 'Office of the President', 'destler@something.com', 'http://president/index.php', 'destler', '585-475-2396', 'work', '1946-08-26', 'President since July 1, 2007.', 'Institute', 'Memorial Drive', 'Building 5', 'New York', 'NY', '14623-5603', 'USA', 'www.somemail.com', '585-475-6991');

Part 2 - Modifying the CONMAN Database Before you can start Run the createCONTACT_INFO.sql and insertContact Info.sql scripts in MySQL to create the CONMAN database, the CONTACT_INFO table, with President Destler's record inserted. Create a script called 'yourlastname_hw3p2.sql' that will include comments stating the task number with the statement(s) for that task following. Task 1 - Add new data into CONTACT INFO Create the statements needed to add the data in Appendix A to the CONTACT_INFO table AND a record for yourself. This will result in a total of five records in the CONTACT_INFO table (President Destler's, the three from Appendix A, and your record). Task 2 - Add an attribute to the CONTACT INFO table Create a statement that will add an attribute called 'nickname' to the CONTACT_INFO table. The attribute should be a variable length string capable of holding up to 20 characters. This attribute does not have to have a value. When a value isn't specified 'To Be Determined' should be used. You may NOT drop the table and recreate it with this new attribute, you must add the attribute to the existing table. Task 3 - Add constraints to the CONTACT INFO table Create the statements to modify the firstname and lastname attributes so that a value is required to be entered when new records are added. You may NOT drop the table and recreate it with a primary key, you must add this constraint to the existing table. Task 4 - Update an existing record You will create a statement that will modify the existing record for President Destler to add a nickname of 'Bill' for him. You may NOT delete the record and re-insert it with the new attribute value, you must add the value to the existing record. Task 5 - Delete records Create a single statement that will delete the record of any contact that is associated with the Concor International, Inc.' company. Task 6 - Alter table Create statements that will drop the current primary key constraint and recreate the whole "CONTACT_INFO" table to have the AUTO INCREMENT" setting on the ContactID" attribute. Appendix A. Data for CONTACT_INFO table Attribute Name Record #1 Record #2 Record #3 firstname Eli Eve Carson middleinitial T B lastname Wallowby Sampson Campbell suffix_description III. III title_description Mr. Mrs. Dr. jobtitle Director Assistant to Finance Chief of Medicine Director department Finance Finance Geriatrics email etwallowby@concor.com esampson@concor. cbc232@mvch.org om url www.concor.com/-wallowby IMaddress etwallowby esampson phone_number 505-546-3322 ext. 23 505-546-3322 ext. 585-222-2121 30 phonetype_description work work Home birthday 1956-03-26 1972-05-11 1955-01-05 notes All meetings must be Very helpful. Wife: Lisa Kids: scheduled through his Lucas, Lucy, and assistant. Lucinda. companyname Concor International, Inc. Concor Mountain View International, Inc. Hospital street 1 152 North Main Street 152 North Main Street street2 city Beverly Hills Beverly Hills state province CA CA zip postalcode 90210-3715 90210-3715 country region USA USA company url WWW.concor.com Www.concor.com www.mych.org company phone 323-555-6115 323-555-5000