This set of Database Problems focuses on “Reduction to Relational Schemas”.
Consider the following relational schemas and answer the questions below
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 |
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
View Answer
Explanation: 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
View Answer
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.
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
Explanation: 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
View Answer
Explanation: 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’;
View Answer
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
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
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
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
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
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]
- Practice Programming MCQs
- Check DBMS Books
- Practice Computer Science MCQs
- Check Computer Science Books
- Check Programming Books