Create the following sql queries and run them the database

Fall 2010 CS 3200 Class Project: Milestone 5
The goals for this milestone are (1) to create data to fill our database tables and (2) to practice SQL.

This milestone is to be completed individually (i.e., no teams). You can discuss problems with other students, but you have to create all deliverables yourself from scratch. In particular, it is not allowed to copy somebody else’s code or text and modify it.

3.Delete attributes lender, borrower, ItemID, and DateID from table Rating.

4.Add attribute exchangeID to table Rating and set up a foreign key reference to the same attribute in table Exchange.

The goal for the data generator is to create a good amount of interesting and realistic data. Here are a few suggestions and requirements to achieve this:

1.People, rooms, buildings: Your data generator should create meaningful tuples. Create at least 3 dorm buildings and enough rooms for each building to satisfy the application requirements from Milestone 2. For each building, create at least 10 students, exactly one manager, and at least one counselor. Make sure some buildings have more than 20 and some have fewer than 20 students. Create at least 4 janitors and assign them to dorm buildings, so that at least one janitor works in more than one building. When assigning students to dorm rooms, make sure at least one room in each building has more than one student. At least one room in each building should be empty. For non-students, fill some off-campus address strings in, but make sure at least one manager, counselor, and janitor have NULL addresses.

Notice that in your source code you can hard-code certain examples to make sure you satisfy the requirements. But it should be easy to produce larger data sets, if necessary.

For up to 3 extra points, you can also submit a solution for a bonus assignment. The extra points can be used to compensate for points lost in the mandatory part of this assignment, but your score cannot exceed 100. The bonus assignment is to also populate the alternative design tables with example tuples. You can try to do this through SQL queries on the “standard” tables.

4.For each item (item’s ID), who (person’s ID) is its owner and who (person’s ID) has the item currently?

5.Which person currently holds on to the most borrowed items?

Once you have set up the tunnel, you should be able to use SQL Server Management Studio from your personal computer. If you do not have it, you can get it from MSDNAA for free. Just make sure it is the 2008 version. Other database clients might also work fine, but we have not tested them. For Linux you will have to use another client, because there is no version of SQL Server Management Studio for Linux.

A Web search should help, or you can send an email to the class, asking if anybody else has any suggestions.