Use the CollegeDB database I have posted in BB.

Step 1

Add the PROFESSOR relation (table) to CollegeDB database. The columns are PENO, PNAME, PRANK.

• Add a professor column to COURSE table:
• Enforce referential integrity between COURSE and PROFESSOR tables. Do it before entering the data.
• Fill in the following data into Professor Table:

PENO PNAME PRANK

124 Tom Jones Prof

210 John Dove Prof

350 Bill Gate Inst

800 Amy Jones Prof

• Here are the courses taught by professors:

CIS2100, CIS2200, CIS9000 by Tom Jones

FIN2000, FIN2200, FIN9000 by John Dove

FIN9001 by Bill Gate

MKT2000, MKT4000, by Amy Jones

Step 2: Implement the following queries.

Query 1: What are the names of CIS majors?

Query 2: What are the names and majors of students who got an A in CIS2200?

Query 3: What are the names and majors of students who got B or better in CIS2200?

Query 4: What are the names and majors of students who got B or better in courses whose credits are less than 4?

Query 5: What are the names of students taught by Prof. John Dove?

Step 3: (BONUS) Design a new table that shows the prerequisites of courses. Add this table to CollegeDB database and insert some data (your own) in it.

CollegeDB:

VIL puvuru JUILUIHILLE All Access ... COURSE X CNO CName Credit Level Click to Add - Search... 0 Tables DBMS HH COURSE RE ENROLLED STUDENT + CIS2100 + CIS2200 + CIS9000 + FIN2000 + FIN2100 + FIN9000 + FIN9001 # MATH2000 + MKT2000 + MKT4000 3 U 3 U 3 G 3 U 1 U 3 Queries Int to IS DBMS Finance Int to Business 00 Finance Advanced Finan Algebra Marketing Consumer Beha Q3 3 Query1 4 Query2 2 REE Query3-Incorrect 2 0 COURSE ENROLLED X