Problem1. Write the SQL code that will create ALL the tables as given in the appendix. Your answer should not contain the primary key and foreign key constraint declaration. The data type for each attribute should be as specified in the Appendix. Assume that the tables will be associated with the dbo schema. The tables must be created on the Primary Filegroup and the table will NOT make use of the IDENTITY feature to automatically increment the Primary Key value.
Problem2. Assuming that all the tables have been created as given in the relational schema in the Appendix, write SQL code to make the following changes in the structure of the tables. Explain in which order the following constraint will have to be created so that the SQL Script does not generate any errors.
• Make the S_ID the Primary Key and the F_ID as the Foreign Key in the STUDENT table.
• Create a constraint in the STUDENT table for the S_CLASS column, so that it will only accept the following values: FR, JR, SR AND SO
• Make the F_ID the Primary Key and the LOC_ID as the Foreign Key in the FACULTY table.
• Create a constraint in the FACULTY table for the F_RANK column, so that it will only accept the following values: ASSO, ASST, FULL and INST
• Make the LOC_ID the Primary Key of the LOCATION table.
• Create a constraint for the F_PIN in FACULTY table, so that no two people will have the same PIN number.