Consider the following database schema with the following relations: Student (SID, Name, Address, Telephone, Age) Course (CourseNo, Title, Department, NumberOfCredits, CourseFees) Registration (SID, CourseNo, startDate, CompleteDate, Grade) Consider the following queries: List the student numbers and names of students who received a grade greater or equal to 70% in the course COMP418, sorted by age ascending.

List the course numbers and titles of courses that have more than 10 students getting a grade lower than 50. [(Use group by courseNo and count(SID)].

List the course numbers and titles of courses whose course fees are between 400 and 600 dollars.

List all courses in the database. Update all the course fees by adding 6 dollars to each course.

Your task is to implement this database using PostgreSQL or any other DBMS of the list specified in the course (Oracle, MySQL, DB2, SQL server) then compare the performance of the system before creating the indexes and after creating the indexes. Make sure that you create indexes that support the queries. You should use test data to identify performance issues: the more data, the better. Make sure there is sufficient test data in your system to be able to run queries that can return at least a dozen rows of data even when using the queries. Unless there is a fair amount of test data, you will not be able to see much difference in query execution times. Decide on the type of indexing that would be most appropriate. This will require you to read about the different indexing options in your DBMS. The PostgreSQL 9 manual on the subject is available at http://www.postgresql.org/docs/9.1/interactive/indexes.html. Most DBMSs, including PostgreSQL, provide an 'ANALYZE,' 'EXPLAIN' or similar command that can be used to help tune your database and make recommendations on indexing that you may find very useful. Check the performance of the queries before adding indexes. If using PostgreSQL, you will likely find the EXPLAIN command useful in accomplishing this. There is a visual EXPLAIN tool available as part of some versions of PgAdmin that you may want to try. Information on reading and interpreting the results as well as on how to use the tool is available at http://www.postgresonline.com/journal/index.php?/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html Add your indexes. You will probably be using the CREATE INDEX command for this, but do feel free to use other DBMS tools if they are available.

Write a short report (1-2 pages maximum) that summarizes your findings during the experiment. The report should include: A description of your implementation of the database. Include the SQL code for implementing the tables. How many records did you enter in each table? A description of the execution time of the queries before creating the indexes. A description of the created indexes, and a justification of why you think those indexes would improve the system performance for the specified queries. A table comparing both execution times before and after indexing for each query. A conclusion that summarizes your analysis of the performance.