CSC 3800Fall 2009Mid-Term ExamEach question is worth 20 points for a total of 100 points. 1. Given the relational database on the last page:a. List the names of the relations.b. List the attributes of each relation.c. List one tuple from each relation.2. For the following relational algebra operators, give the name or symbol, define each, and give an example using the tables on the last page:a. Selectionb. ΠcolName,…colName (tableName)c. Xd. Timese. │x│f. Uniong. ∩h. DifferencePage 1Name: __________________________________Due not later than 5 pm on MondayOctober 12, 20093. For the relational database on the last page:a. Use SQL to define (create) the database.b. Use SQL to define (create) the relations. Be sure to include type and all keys and foreign keys in the definitions.4. Define Constraints and Assertions.Page 25. For the relational database on the last page:a. Write a SQL query to find all employee(s) first names with a last name of Smith.b. Write a SQL query to find the first and last manes of all employee(s) working on project name BP.Page 3DepartmentsDeptID DeptA 101B 102C 103D 104EmployeesEmpID First Last DOB CNum DateHire DeptID201 Sam Smith 1/2/1980 C201 12/5/2000 A202 Sue Jones 2/1/1982 C202 1/5/1999 D203 Joe Smith 3/4/1960 C203 4/8/2002 C204 Adam Guidry 4/3/1965 C204 6/6/2006 A205 Mary Smith 10/10/1946 C205 4/1/2002 B206 Sally Rand 8/8/1948 C206 7/7/2001 B207 Joe Adam 9/9/1949 C207 8/8/2002 C208 Bill Brown 11/12/1955 C208 9/9/2003 D209 Sally Jones 12/12/1960 C209 11/11/2004 A210 Dave Landry 1/9/1970 C210 12/12/2005 B211 Fred Martin 2/2/1980 C211 1/15/1999 C212 Greg Norman 3/3/1985 C212 2/2/2006 DEmpProjProjID EmpID StartDate TotalHoursP101 202 6/10/2003 70P101 204 1/1/2001 140P101 206 1/1/2001 140P102 201 1/21/2006 60P102 203 8/1/2007 80P102 205 1/21/2006 120P102 212 10/10/2008 60P103 207 2/18/2006 50P105 206 7/4/2007 80P105 203 7/4/2007 60P105 211 7/4/2007 150P105 209 7/4/2007 140ProjectsProjID Title PStart PEndP101 AP 1/1/2001 12/31/2009P102 BP 1/21/2006 1/20/2010P103 CP 2/18/2006 2/17/2009P104 DP 5/26/2009 12/10/2010P105 EP 7/4/2007 10/10/2010P106 FP 12/1/2008 4/15/2009Page