250+ TOP MCQs on Reduction to Relational Schemas and Answers

Database Problems on “Reduction to Relational Schemas”.

Consider the following relational schemas and answer the questions below

The section relation


Course_id Sec_id Semester Year Building
BIO-101 1 Spring 2010 Painter
CS-102 4 Summer 2009 Packyard
EE-201 3 Fall 2010 Watson
FIN-301 1 Spring 2011 Richard

The teaches relation


Id Course_id Sec_id Semester Year
1001 CS-101 1 Fall 2009
1002 EE-201 2 Spring 2010
1003 FIN-301 3 Fall 2009
1004 BIO-101 1 Summer 2011

1. Which one of the following can be treated as a primary key in teaches relation?
a) Id
b) Semester
c) Sec_id
d) Year

Answer: a
Clarification: Here Id is the only attribute that has to have a unique entry.

2. The primary key in the section relation is
a) Course_id
b) Sec_id
c) Both Course_id and Sec_id
d) All the attributes

Answer: c
Clarification: Both the entries has unique entry.

3.

SELECT * FROM teaches WHERE Sec_id = 'CS-101';

Which of the following Id is selected for the following query?
a) 1003
b) 1001
c) None
d) Error message appears

Answer: d
Clarification: The value CS-101 matches the Course_id but not Id.

4.

SELECT Id, Course_id, Building FROM SECTION s AND teaches t WHERE t.year=2009;

Which of the following Id are displayed?
a) 1003
b) 1001
c) Both 1003 and 1001
d) Error message appears

Answer: c
Clarification: Two rows are select in the above query.

5. The query which selects the Course_id ‘CS-101’ from the section relation is
a) Select Course_id from section where Building = ‘Richard’;
b) Select Course_id from section where Year = ‘2009’;
c) Select Course_id from teaches where Building = ‘Packyard’;
d) Select Course_id from section where Sec_id = ‘3’;

Answer: b
Clarification: The year ‘2009’ should be selected from the section relation.

6.

CREATE TABLE SECTION
(Course_id VARCHAR (8),
Sec_id VARCHAR (8),
Semester VARCHAR (6),
YEAR NUMERIC (4,0),
Building NUMERIC (15),
PRIMARY KEY (course id, sec id, semester, YEAR),
FOREIGN KEY (course id) REFERENCES course);

Which of the following has an error in the above create table for the relation section
a) Primary key (course id, sec id, semester, year)
b) Foreign key (course id) references course
c) Year numeric (4,0)
d) Building numeric (15)

Answer: d
Clarification: It should be replaced by Year Building varchar (15).

7. The relation with primary key can be created using
a) Create table instructor (Id, Name)
b) Create table instructor (Id, Name, primary key(name))
c) Create table instructor (Id, Name, primary key (Id))
d) Create table instructor ( Id unique, Name )

Answer: c
Clarification: The value Name cannot be a primary key.

8. How can the values in the relation teaches be deleted?
a) Drop table teaches;
b) Delete from teaches;
c) Purge table teaches;
d) Delete from teaches where Id =’Null’;

Answer: b
Clarification: Delete table cleans the entry from the table.

9. In the above teaches relation ” Select * from teaches where Year = ‘2010’” displays how many rows?
a) 2
b) 4
c) 5
d) 1

Answer: a
Clarification: There are two tuples with the year is 2009.

10. The relation changes can be got back using ________ command
a) Flashback
b) Purge
c) Delete
d) Getback

Answer: a
Clarification: Purge deletes the table and delete cleans the table entry.

Leave a Reply

Your email address will not be published. Required fields are marked *