Database Questions and Answers – Reduction to Relational Schemas

This set of Database Problems focuses on “Reduction to Relational Schemas”.

Consider the following relational schemas and answer the questions below

The section relation

Course_idSec_idSemesterYearBuilding
BIO-1011Spring2010Painter
CS-1024Summer2009Packyard
EE-2013Fall2010Watson
FIN-3011Spring2011Richard

The teaches relation

IdCourse_idSec_idSemesterYear
1001CS-1011Fall2009
1002EE-2012Spring2010
1003FIN-3013Fall2009
1004BIO-1011Summer2011

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
View Answer

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

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
View Answer

Answer: a
Explanation: Here Course_id is the only attribute that has a unique entries. So, Course_id is the primary key in the section relation table.

3.

Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!
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
View Answer

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

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
View Answer

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

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’;
View Answer

Answer: b
Explanation: 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)
View Answer

Answer: d
Explanation: 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 )
View Answer

Answer: c
Explanation: 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’;
View Answer

Answer: b
Explanation: 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
View Answer

Answer: a
Explanation: 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
View Answer

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

Sanfoundry Global Education & Learning Series – Database Management System.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.