DBMS QUESTIONS AND ANSWERS
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. - Answers -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 ).) - Answers -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 ) - Answers -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. - Answers -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) - Answers -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) - Answers -∏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) - Answers -person_cellphone
person_ssn
person_email
Select major steps that you would take in setting up a database for a particular
enterprise. - Answers -Six major steps in setting up a database for a particular
enterprise are:
• Define the high level requirements of the enterprise (this step generates a document
known as the system requirements specification.)
• Define a model containing all appropriate types of data and data relationships.
• Define the integrity constraints on the data.
• Define the physical level.
• For each known problem to be solved on a regular basis (e.g., tasks to be carried out
by clerks or Web users) define a user interface to carry out the task, and write the
necessary application programs to implement the user interface.
• Create/initialize the database.
Chapter 1 has described several major advantages of a database system.
What are two disadvantages? - Answers -a. Setup of the database system requires
more knowledge, money, skills,
and time.
b. The complexity of the database may result in poor performance.
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. - Answers -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 ).) - Answers -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 ) - Answers -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. - Answers -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) - Answers -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) - Answers -∏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) - Answers -person_cellphone
person_ssn
person_email
Select major steps that you would take in setting up a database for a particular
enterprise. - Answers -Six major steps in setting up a database for a particular
enterprise are:
• Define the high level requirements of the enterprise (this step generates a document
known as the system requirements specification.)
• Define a model containing all appropriate types of data and data relationships.
• Define the integrity constraints on the data.
• Define the physical level.
• For each known problem to be solved on a regular basis (e.g., tasks to be carried out
by clerks or Web users) define a user interface to carry out the task, and write the
necessary application programs to implement the user interface.
• Create/initialize the database.
Chapter 1 has described several major advantages of a database system.
What are two disadvantages? - Answers -a. Setup of the database system requires
more knowledge, money, skills,
and time.
b. The complexity of the database may result in poor performance.
