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:

Open Management Studio and select the database you created

Click on New Query

Write the first drop and create statement and execute them

Click File Save As (it will show a generic name of the query)

Name the file appropriately and save it somewhere you can find it. Leave it as a .sql file

Add the next drop and create statements and execute again.

If it works, save the file. If not, make the corrections, re-execute until it works.

Continue until all of the drop and create statements are executing and tables are created.

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 FileOpenFile 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.