RDBMS KEYS
1. Observe the following table and answer the question (i) , (ii)
and (iii) TABLE: VISITOR
VisitiorID VisitorName ContactNumber
V001 ANAND 9898989898
V002 AMIT 9797979797
V003 SHYAM 9696969696
V004 MOHAN 9595959595
(i) Write the name of most appropriate columns which can be considered
as Candidate keys
(ii) Out of selected candidate keys, which one will be the best to
choose as Primary Key?
2. From the following Tables (EMP) AND (JOB) answer the question
(i) and (ii) TABLE: EMP
EMPNO ENAME JOB SALARY DEPTNO E001
PETER ADMIN 4500 10
E002 SCOTT SALESMAN 3500 20
E003 ALBERT CLERK 2800 10
E004 RUSSEL CLERK 2900 40
TABLE:JOB
DEPTNO DNAME DLOCATION DHEAD
10 PETER ADMIN 4500
20 SCOTT SALESMAN 3500
30 ALBERT CLERK 2800
40 RUSSEL CLERK 2900
(i) Identify Primary Key from both the tables
(ii) Identify the foreign key column in the table EMP
3.
Consider the following ER diagram. List all superkeys, candid ate keys, and primary keys
for entities sets Students and Courses, and for relationship set Enrolled.
sNam grade cID cName
logID
credits
sID
Student Enrolled Course
Normal Forms
Find out the Error in Each Table and which Normal Form to be
followed :
Provide the solved Table for each given Table
1.EMPLOYEE table:
EMP_ID EMP_NAME EMP_PHONE EMP_STATE
14 John 7272826385, UP
9064738238
20 Harry 8574783832 Bihar
12 Sam 7390372389, Punjab
8589830302
2.TEACHER table
TEACHER_ID SUBJECT TEACHER_AGE
25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38
3. EMPLOYEE_DETAIL table:
EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY
222 Harry 201010 UP Noida
333 Stephan 02228 US Boston
444 Lan 60007 US Chicago
555 Katharine 06389 UK Norwich
666 John 462007 MP Bhopal
4. Suppose a company wants to store the names and contact details of its
employees. It creates a table that looks like this:
emp_id emp_name emp_address emp_mobile
101 Herschel New Delhi 8912312390
8812121212
102 Jon Kanpur
9900012222
103 Ron Chennai 7778881212
9990000123
104 Lester Bangalore 8123450987
5. Suppose a company wants to store the complete address of each
employee, they create a table named employee_details that looks like this:
emp_id emp_name emp_zip emp_state emp_city emp_district
1001 John 282005 UP Agra Dayal Bagh
1002 Ajeet 222008 TN Chennai M-City
1006 Lora 282007 TN Chennai Urrapakkam
1101 Lilly 292008 UK Pauri Bhagwan
1201 Steve 222999 MP Gwalior Ratan
6.Suppose a school wants to store the data of teachers and the subjects they
teach. They create a table that looks like this: Since a teacher can teach more
than one subjects, the table can have multiple rows for a same teacher.
teacher_id subject teacher_age
111 Maths 38
111 Physics 38
222 Biology 38
333 Physics 40
333 Chemistry 40
7.
8.
productI
product Brand
D
1 Monitor Apple
2 Monitor Samsung
3 Scanner HP
4 Head phone JBL
9.
Employee Age Department
Melvin 32 Marketing, Sales
Edward 45 Quality Assurance
Alex 36 Human Resource
10.Suppose a school wants to store the address of each student, they create a
table named student_details that looks like:
Rollno State City
1 Punjab Chandigarh
2 Haryana Ambala
3 Punjab Chandigarh
4 Haryana Ambala
5 Uttar Pradesh Ghaziabad
11. Suppose a training institute wants to store the data of student and
the programming_languages they learn. Since a student can learn more than one
programming_language, the relation can have multiple rows for a same student.
Following relation shows the data of the students:
student_id programming_langauges student_age
101 Computer Network 20
101 JAVA 20
102 Database Management System 20
103 Software Engineering 21
103 Compiler Design 21
