After reviewing the various ERDs, Trinity College has decided on the following tables and attributes. PROFESSOR Professor ID Department Professor Specialty Professor Rank Professor Last Name Professor First Name Professor Initial Professor Email SCHOOL School ID School Name SCHOOLDEAN School Dean DEPARTMENT Department Code Department Name School DEPARTMENTCHAIR Department Chair STUDENT Student ID Department Student Last Name Student First Name Student Initial Student Email Advisor COURSE Course Code Department Course Title Course Description Course Credits CLASS Class Code Class Section Class Time Course Professor Room ENROLL Class Student Enrollment Date Enrollment Grade BUILDIING Building Name Building Location ROOM Room Code Room Type Building Write the SQL code to drop and then create the above tables. Remember that when creating tables, the tables with foreign keys have to be created after the table that the foreign key points to. When dropping tables, they have to be dropped in the reverse order, so that the tables with foreign keys are dropped before the tables that the foreign keys point to. You can create all the drop SQL statements first followed by all the create tables. The first time you run them the drop statements will work as no tables have been created and therefore no drops will occur. You will need to run them twice to insure the drops will work. Please be sure to review the Phase 1 requirements so you can determine the primary and foreign keys for each table and then determine the order of DROP and CREATE STATEMENTS. Please note that some fields are foreign keys but not identified as such by their names. Also know which fields are required fields and which are not. Use appropriate data types for each field and meaningful field names. The following is an example of the drop table statement that I covered in one of the lectures: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tablename]') AND type in (N'U')) DROP TABLE [dbo].[tablename] All of the SQL statements must be in one file. Do not use Word as your submission file as it wont work in the SQL Management Studio. Use the following steps to create the file and submit it: 1. Open Management Studio and select the database you created 2. Click on New Query 3. Write the first drop and create statement and execute them 4. Click File ? Save As (it will show a generic name of the query) 5. Name the file appropriately and save it somewhere you can find it. Leave it as a .sql file 6. Add the next drop and create statements and execute again. 7. If it works, save the file. If not, make the corrections, re-execute until it works. 8. Continue until all of the drop and create statements are executing and tables are created. 9. Submit the .sql file to the drop box. You can do this in stages if you want. If you have saved the file, the next time you open Management Studio go to File?Open?File and then select the file that you have been saving. It will automatically open in the query window and you can continue working on it.