STUPIDSID
1 (a)
Define the following with examples:
(i) Value set (ii) Complex attributes (iii) Data model (iv) Schema (v) Metadata
(i) Value set (ii) Complex attributes (iii) Data model (iv) Schema (v) Metadata
10 M
1 (b)
Explain the components modules of DBMS and their interaction, with the help of a diagram.
10 M
2 (a)
What are the structural constraints on a relationship type? Explain with examples.
5 M
2 (b)
What is a weak entity type? Explain the role of partial key in design of weak entity type.
5 M
2 (c)
Design an ER-diagram for the movie-database considering the following requirements:
(i) Each Movie is identifies by its title and year of release, it has length in minutes and can have zero of more quotes, language.
(ii) Production companies are indentified by Name, they have address, and each production company can produce one or more movies.
(iii) Actors are identified by Name and Date of Birth, they can act in one or more movies and each actor has a role in a movie. (iv) Directors are indentified by Name and Date of Birth, so each Director can be direct one or more movie and each movie can be directed by one or more Directors.
(v) Each movie belongs to any one category like Horror, action, Drama, etc.
(i) Each Movie is identifies by its title and year of release, it has length in minutes and can have zero of more quotes, language.
(ii) Production companies are indentified by Name, they have address, and each production company can produce one or more movies.
(iii) Actors are identified by Name and Date of Birth, they can act in one or more movies and each actor has a role in a movie. (iv) Directors are indentified by Name and Date of Birth, so each Director can be direct one or more movie and each movie can be directed by one or more Directors.
(v) Each movie belongs to any one category like Horror, action, Drama, etc.
10 M
3 (a)
What is meant by integrity constraint? Explain the importance of Referential integrity constraint. How referential integrity constraint is implemented in SQL?
10 M
3 (b)
Consider the following schema and write the Relational Algebra.
SAILORS (SID, SNAME, RATING, AGE)
BOATS (BID,BNAME, COLOR)
RESERVE (SID, BID, DAY)
(i) Retrieve the sailors names who have reserved red and green boats.
(ii) Retrieve the sailors names with age over 20 years and reserved black boat.
(iii) Retrieve the number of boats which are not reserved
(iv) Retrieve the sailors name who reserved green boats on Monday.
Retrieve the sailors names who is the oldest sailor with rating 10.
SAILORS (SID, SNAME, RATING, AGE)
BOATS (BID,BNAME, COLOR)
RESERVE (SID, BID, DAY)
(i) Retrieve the sailors names who have reserved red and green boats.
(ii) Retrieve the sailors names with age over 20 years and reserved black boat.
(iii) Retrieve the number of boats which are not reserved
(iv) Retrieve the sailors name who reserved green boats on Monday.
Retrieve the sailors names who is the oldest sailor with rating 10.
10 M
4 (a)
Consider the following schema and write the SQL queries:
EMP (SSN, NAME, ADDR, SALARY, SEX, DNO)
DEP (DNO, DNAME, MGRSSN)
DEP_LOCN (DNO, DLOCN)
PROJ (PNO, PNAME, PLOCN, DNO)
WORKSON (SSN, PNO, NOHRS)
DEPENDENT (SSN, DEPNTNAME, DEPNTSEX, DEPNTRELATIONSHIP)
(i) Retrieve the manager name with atleast 1 dependent.
(ii) Retrieve the employee name who work on any of the project that Kumar works.
(ii) Retrieve the pno, pname, no of man hours work done on each project.
(iv) Retrieve the pname which are contolled by Research department.
(v) Retrieve the employee name who work for dept no. 10 and have a daughter.
EMP (SSN, NAME, ADDR, SALARY, SEX, DNO)
DEP (DNO, DNAME, MGRSSN)
DEP_LOCN (DNO, DLOCN)
PROJ (PNO, PNAME, PLOCN, DNO)
WORKSON (SSN, PNO, NOHRS)
DEPENDENT (SSN, DEPNTNAME, DEPNTSEX, DEPNTRELATIONSHIP)
(i) Retrieve the manager name with atleast 1 dependent.
(ii) Retrieve the employee name who work on any of the project that Kumar works.
(ii) Retrieve the pno, pname, no of man hours work done on each project.
(iv) Retrieve the pname which are contolled by Research department.
(v) Retrieve the employee name who work for dept no. 10 and have a daughter.
10 M
4 (b)
Consider the following schema and write the SQL queries:
STUDENT (STUDENT_ID, SNAME, MAJOR, GPA)
FACULTY (FACULTY_ID, FNAME, DEPT, DESIGNATION, SALARY)
COURSE (COURSE_ID, CNAME, FACULTY_ID)
ENROLL ( COURSE_ID, STUDENT_ID,GRADE)
(i) Retrieve the student name who is studying under faculties of "Mechanical dept"
(ii) Retrieve the student name who have enrolled under any of the courses in which 'Kumar' has enrolled.
(iii) Retrieve the faculty name who earn salary which is greater than the average salary of all the faculties.
(iv) Retrieve the Sname who are not been taught by faculty 'Kumar'.
(v) Retrieve the faculty names who are assistant professor of computer science department.
STUDENT (STUDENT_ID, SNAME, MAJOR, GPA)
FACULTY (FACULTY_ID, FNAME, DEPT, DESIGNATION, SALARY)
COURSE (COURSE_ID, CNAME, FACULTY_ID)
ENROLL ( COURSE_ID, STUDENT_ID,GRADE)
(i) Retrieve the student name who is studying under faculties of "Mechanical dept"
(ii) Retrieve the student name who have enrolled under any of the courses in which 'Kumar' has enrolled.
(iii) Retrieve the faculty name who earn salary which is greater than the average salary of all the faculties.
(iv) Retrieve the Sname who are not been taught by faculty 'Kumar'.
(v) Retrieve the faculty names who are assistant professor of computer science department.
10 M
5 (a)
How is view created and dropped? Whar problems are associated with updating views?
8 M
5 (b)
How are trigger and assertions defined in SQL? Explain.
6 M
5 (c)
Explain the concept of stored procedure in brief.
6 M
6 (a)
Consider R={A B C D E F}; FD'S {A ? BC, C ? E, CD ? EF} show that AD ? F.
6 M
6 (b)
FDS { Book_title ? Book_type, publication
Auth_name ? Affiliation
Book_type ? Listprice}. Find the key and normalize.
| Book_title | Auth_name | Book_type | Listprice | Affilication | Publication |
FDS { Book_title ? Book_type, publication
Auth_name ? Affiliation
Book_type ? Listprice}. Find the key and normalize.
8 M
6 (c)
What is set of functional dependencies F said to be minimal? Given an algorithm for finding a minimal cover G for F.
6 M
7 (a)
Consider R={A B C D E F}
FDS { AB ? CD, A ? CF, B ? F, BGD ? E, DE ? F} Find an irreducible cover for this set of FD's
FDS { AB ? CD, A ? CF, B ? F, BGD ? E, DE ? F} Find an irreducible cover for this set of FD's
6 M
7 (b)
Explain the properties of Relational Decomposition.
6 M
7 (c)
Consider R={A B C D E F}
FDS { AB ? C B ? E A ? DF}
Check whether decomposition is lossless.
FDS { AB ? C B ? E A ? DF}
Check whether decomposition is lossless.
8 M
8 (a)
What are ACID properties? Explain.
6 M
8 (b)
What is a schedule? Explain with example conflict Serializable schedule.
8 M
8 (c)
What is two-phase locking protocol? How does it guarantee serializability?
6 M
More question papers from Database Management Systems