Database Problems 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
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.