DBMS Midterm Exam - Database Management Systems:
Questions With Solutions
Consider the time slot relation. Given that a particular time slot can meet more
than once in a week, explain why day and start time are part of the primary
key of this relation, while end time is not. Right Ans - The attributes day
and start time are part of the primary key since a particular class will most
likely meet on several different days, and may even meet more than once in a
day. However, end time is not part of the primary key since a particular class
that starts at a particular time on a particular day cannot end at more than
one time.
What is the result of first performing the cross product of student and advisor,
and then performing a selection operation on the result with the predicate
s_id = ID? (Using the symbolic notation of relational algebra, this query can be
written as σs_id=ID(student × advisor ).) Right Ans - The result attributes
include all attribute values of student followed by all attributes of advisor. The
tuples in the result are as follows. For each student who has an advisor, the
result has a row containing that students attributes, followed by an s_id
attribute identical to the students ID attribute, followed by the i_id attribute
containing the ID of the students advisor. Students who do not have an
advisor will not appear in the result. A student who has more than one advisor
will appear a corresponding number of times in the result.
Consider the following expressions, which use the result of a relational
algebra operation as the input to another operation. For each expression,
explain in words what the expression does.
σyear ≥2009 (takes ) ▹◃ student
σID ,name ,course_id (student ▹ ◃ takes ) Right Ans - For each student who
takes at least one course in 2009, display the students information along with
the information about what courses the student took. The attributes in the
result are:
ID, name, dept_name, tot_cred, course_id, section_id, semester, year, grade
Provide a list of consisting of
ID, name, course id
of all students who took any course in the university.
, Consider the foreign key constraint from the dept name attribute of instructor
to the department relation.
Will inserting a tuple:
(10111, Ostrom, Economics, 110,000)
cause a violation of the foreign key constraint. Right Ans - True
Consider the bank database of given schema below. Give an expression in the
relational algebra for each of the following queries.
Find the names of all branches located in "Chicago".
Find the names of all borrowers who have a loan in branch "Down- town".
Given Schema:
branch(branch_name, branch_city, assets)
customer(customer_name, customer_street, customer_city)
loan(loan_number, branch_name, amount)
borrower(customer_name, loan_number)
account(account_number, branch_name, balance)
depositor(customer_name, account_number) Right Ans - 1. ∏branch_name
(σbranch_city = "Chicago" (branch))
2. ∏customer_name (σbranch_name= "Downtown" (borrower ▹ ◃ loan ))
Consider the relational database shown below. Give an expression in the
relational algebra to express each of the following query:
Find the names of all employees who live in "Miami" and whose salary is
greater than $100,000.
Given Schema:
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city) Right Ans - ∏name( σcity="Miami" ^
salary>100000 (employee))
Consider the schema definition below. What are the appropriate primary
keys? Select all that applies
employee (person_id, person_ssn, person_name, person_email, person_street,
person_city, person_cellphone) Right Ans - person_cellphone
person_ssn
person_email
Questions With Solutions
Consider the time slot relation. Given that a particular time slot can meet more
than once in a week, explain why day and start time are part of the primary
key of this relation, while end time is not. Right Ans - The attributes day
and start time are part of the primary key since a particular class will most
likely meet on several different days, and may even meet more than once in a
day. However, end time is not part of the primary key since a particular class
that starts at a particular time on a particular day cannot end at more than
one time.
What is the result of first performing the cross product of student and advisor,
and then performing a selection operation on the result with the predicate
s_id = ID? (Using the symbolic notation of relational algebra, this query can be
written as σs_id=ID(student × advisor ).) Right Ans - The result attributes
include all attribute values of student followed by all attributes of advisor. The
tuples in the result are as follows. For each student who has an advisor, the
result has a row containing that students attributes, followed by an s_id
attribute identical to the students ID attribute, followed by the i_id attribute
containing the ID of the students advisor. Students who do not have an
advisor will not appear in the result. A student who has more than one advisor
will appear a corresponding number of times in the result.
Consider the following expressions, which use the result of a relational
algebra operation as the input to another operation. For each expression,
explain in words what the expression does.
σyear ≥2009 (takes ) ▹◃ student
σID ,name ,course_id (student ▹ ◃ takes ) Right Ans - For each student who
takes at least one course in 2009, display the students information along with
the information about what courses the student took. The attributes in the
result are:
ID, name, dept_name, tot_cred, course_id, section_id, semester, year, grade
Provide a list of consisting of
ID, name, course id
of all students who took any course in the university.
, Consider the foreign key constraint from the dept name attribute of instructor
to the department relation.
Will inserting a tuple:
(10111, Ostrom, Economics, 110,000)
cause a violation of the foreign key constraint. Right Ans - True
Consider the bank database of given schema below. Give an expression in the
relational algebra for each of the following queries.
Find the names of all branches located in "Chicago".
Find the names of all borrowers who have a loan in branch "Down- town".
Given Schema:
branch(branch_name, branch_city, assets)
customer(customer_name, customer_street, customer_city)
loan(loan_number, branch_name, amount)
borrower(customer_name, loan_number)
account(account_number, branch_name, balance)
depositor(customer_name, account_number) Right Ans - 1. ∏branch_name
(σbranch_city = "Chicago" (branch))
2. ∏customer_name (σbranch_name= "Downtown" (borrower ▹ ◃ loan ))
Consider the relational database shown below. Give an expression in the
relational algebra to express each of the following query:
Find the names of all employees who live in "Miami" and whose salary is
greater than $100,000.
Given Schema:
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city) Right Ans - ∏name( σcity="Miami" ^
salary>100000 (employee))
Consider the schema definition below. What are the appropriate primary
keys? Select all that applies
employee (person_id, person_ssn, person_name, person_email, person_street,
person_city, person_cellphone) Right Ans - person_cellphone
person_ssn
person_email
