Databases Questions and Answers
Databases Questions and Answers
Databases Questions and Answers
Problem: 1
Problem 2:
Your queries might vary from these proposed ones. Its important to know that there are
many
different ways to design these.
PART 1: Solution
a) SELECT Name FROM STUDENT
WHERE major = CSC AND Class = 5
b) SELECT COURSE.CourseName FROM COURSE , SECTION
WHERE COURSE.CourseNumber = SECTION.CourseNumber
AND SECTION.Instructor= Osama Abushama
AND SECTION.year = 1985
AND SECTION.year = 1986
c) SELECT SECTION.CourseNumber , SECTION.Year,
COUNT(GRADE_REPORT.StudentNumber) FROM SECTION, GRADE_REPORT
WHERE SECTION.Instructor = Osama Abushama
AND SECTION.Identifier = GRADE_REPORT.SectionNumber
d) SELECT STUDENT.Name, COURSE.CourseName, COURSE.CourseNumber,
COURSE.CreditHours, SECTION.Semster,SECTION.Year AND
GRADE_REPORT.Grade
FROM STUDENT,SECTION, COURSE, GRADE_REPORT
WHERE GRADE_REPORT.StudentNumber=STUDENT.StudentNumber
AND GRADE_REPORT.SectionIdentifier=SECTION.SectionIdentifier
AND SECTION.CourseNumber = COURSE.CourseNumber
AND STUDENT.Class = 5
AND Major = CSC
e) SELECT STUDENT.NAME, STUDENT.Major FROM SYUDENT,
GRADE_REPORT
WHERE STUDENT.StudentNumber = GRADE_REPORT.SrudentNumber
AND GRADE_REPORT.Grade = A
f) SELECT STUDENT.NAME, STUDENT.Major FROM SYUDENT,
GRADE_REPORT
WHERE STUDENT.StudentNumber = GRADE_REPORT.SrudentNumber
AND GRADE_REPORT.Grade <> A
PART 2: Solution
a) SELECT EMPLOYEE.FNAME FROM EMPLOYEE, WORKS_ON, PORJECT
WHERE PROHECT.DNUM = EMPLOYEE.DNUM
AND WORKS_ON.PNUM= PROJECT.PNUMBER
AND PROJECT.PNAME = PROJECT X
AND WORKS_ON.HOURS > 10
AND EMPLOYEE.DNUM = 5
b) SELECT EMPLOYEE.FNAME FROM EMPLOYEE, DEPENDENT
WHERE DEPENDENT.ESS = EMPLOYEE.SSN
AND DEPENDENT.DEPENDENT NAME = EMPLOYEE.FNAME
c) SELECT E.FNAME FROM EMPLOYEE, E, D
WHERE E.SUPER SSN = D.SSN
AND D.FNAME = FRANKLIN
AND D.LNAME = WONG
/* In the above query, SELF JOIN is used.*/
d) SELECT PNAME, WORKS_ON.HOURS FROM PROJECT, WORKS_ON
WHERE WORKS_ON.PNUM = PROJECCT.PNUMBER
e) SELECT EMPLOYEE.FNAME,EMPLOYEE.LNAME FROM EMPLOYEE
WHERE SSN IN(SELECT ESSN FROM WORKS_ON )
WHERE PNUMBER IN (SELCCT PNUMBER FROM PROJECT)
g) SELECT AVG( SALARY.EMPLOYEE) FROM EMPLOYEE
WHERE EMPLOYEE.SEX =FEMALE
h) SELECT EMPLOYEE.FNAME, EMPLOYEE.LNAME, EMPLOYEE.ADDRESS
FROM
EMPLOYEE, PROJECT, DEPT_LOCATIONS
WHERE EMPLOYEE.DNUM = PROJECT.DNUM
AND EMPLOYEE.DNUM =DEPT_LOCATIONS.DNUMBER
AND PROJECT.PLOCATION = HOUSTAN
AND DEPT_LOCATIOS.DLOCATION = NULL
i)
SELECT EMPLOYEE.FNAME, EMPLOYEE.LNAME FROM EMPLOYEE,
DEPENDENT, WORKS_ON
WHERE EMPLOYEE.SSN = DEPARTMENT.MGRSSN
AND EMPLOYEE.SSN = WORKS_ON.ESSN
AND DEPENDENT.ESSN = WORKS_ON.ESSN
AND DEPENDENT.ESSN = NULL
Problem 3:
Consider the following two relational schemas and sets of functional dependencies:
i.
ii.
For each of the two schemas, do the following: Decompose the relations, as
necessary, into collections of relations that are in BCNF. Show all of your work
and explain which dependency violations you are correcting by your
decompositions. You have to turn in a description of your decomposition steps.
Show: which is the relation that you are decomposing, what functional
dependency do you apply, and which are the two resulting relations.
i.
Both dependencies violate BCNF.
Fixing D -> B gives us (ACDE), (DB)
Fixing CE -> A gives us(CDE), (CEA), (DB) which is the final decomposition.
ii.
All given dependencies violate BCNF.
Fixing BC -> A gives us (BCDE), (BCA). The dependency (A -> E) is also gone.
Thus, this was not a dependency preserving decomposition.
Fixing DE -> B gives us (CDE), (DEB), (BCA) which is the final decomposition.
An alternative approach is, fixing A -> E gives us (AE), (ABCD). The dependency DE ->
B is also gone.
Fixing BC -> A gives (ABC), (BCD), (AE) which is the final decomposition.
Another alternative approach is, fixing BC -> A gives us (ABC), (BCDE). The
dependency A -> E is also gone.
Fixing DE -> B gives (BDE), (CDE), (ABC) which is the final decomposition.
Problem 4:
We say a set of attributes X is closed (with respect to a given set of functional
dependencies) if X+=X. Given the closed attribute sets, this gives us some information on
the underlying functional dependencies.
Consider a relation with schema R(A,B,C,D) and an unknown set of functional
dependencies. For each closed attribute set below, give a set of functional
dependencies that is consistent with it.
All sets of attributes are closed.
a. All sets of attributes are closed.
b. The only closed sets are {} and {A,B,C,D}.
a.
b.
c.