APJ ABDUL KALAM TECHNOLOGICAL UNIVERSITY
07 THRISSUR CLUSTER
FIRST SEMESTER M.TECH DEGREE EXAMINATION DECEMBER 2017
Computer Science and Engineering
(Common to M.Tech CSE and Cyber Security)
07CS6105- TOPICS IN DATABASE SYSTEM AND DESIGN
Max. Marks: 60 Duration: 3 Hours
Q no. Module 1 Marks
1a. Differentiate relational model constraints and relational database schema 4
Answer b or c
b. Briefly explain the unary relational operations with example 5
c. Consider the following database with primary keys underlined 5
EMPLOYEE (employee-name, Ssn, Address, salary, super-ssn, Dno)
DEPARTMENT (Dname, Dnumber, Mgr-ssn, Mgr-start-date)
WORKS- ON (Essn, Pno, hours)
PROJECT (Pname,Pnumber,Ploction,Dnum)
DEPT-LOCATIONS (Dnumber, Dloction)
DEPENDENT (Essn, Dependent-name, sex, Relationship)
Write the relational algebra for the following:
i. Retrieve the name and address of all employees who work for the 'Research' department.
ii. Find the names of employees who work on all the projects controlled by department
Number 5
iii. List the names of all employees with two or more dependents
iv. Retrieve the names of employees who have no dependents
v. List the names of managers who have at least one dependent
Q no. Module 2 Marks
2a. Explain the rules for generating closure of functional dependencies. 4
Answer b or c
b. Discuss about the multivalued dependency and fifth normal. 5
c. Discuss BCNF with example. 5
Q no. Module 3 Marks
3a. Explain INSERT, DELETE AND UPDATE statements in SQL 4
Answer b or c
b. Consider the data base with following tables 5
STUDENT (name, student-number, class, major)
Course (course-name, course-number, credit, hours,department)
Section Section-identifier, course-number, semester, year, instructor)
Grade report (student-number, section identifier, grade)
Write the SQL for the following
Retrieve the names of all students majoring in
Retrieve the names of all courses taught by professor King in 2007 &2008
For each section taught by professor King retrieve the course number, semester, year, and number
section who took the section
Change the class of student to 2
Delete the record for the student whose name is 'smith 'and whose student number is 17
c. Define Triggers. Explain the types benefits of Triggers 5
Q no. Module 4 Marks
4a. Discuss about the object oriented relational features. 4
Answer b or c
b. Illustrate factory objects and data base objects using interfaces 5
c. Generate ODL schema for university data base 5
Q no. Module 5 Marks
5a Explain the association rule with example 5
Answer b or c
b. Define clustering. Explain the K-means clustering algorithm. 7
c. Discuss about the data models for data warehouse 7
Q no. Module 6 Marks
6a. What is PL/SQL?Explain the program units of PL/SQL 5
Answer b or c
b. Discuss the features advantages of PL/SQL 7
c. Develop a PL/SQL program to display the six highest paid employees from the
EMPLOYEE table. The EMPLOYEE table has the attributes Ename, EmpNo and 7
Salary