Page 1 :
SRINIVAS UNIVERSITY COLLEGE OF ENGINEERING, AND TECHNOLOGY, , V SEMESTER, DATABASE MANAGEMENT SYSTEMS, LABORATORY(19SCFL55), LAB MANUAL, , Prepared by:, Dyanila Ferrao (B.E, M.Tech), Assistant Professor

Page 2 :
INSTITUTE VISION AND, MISSION, To be a trendsetter among universities and build students who emerge as, leaders with competence, conscience and compassion by empowering them with, sound education and high standards of ethical and professional behavior enabling, them to build and promote a more humane, just and sustainable world for future, generations., Our mission is to provide an exceptional learning environment where students can, develop and enhance their leadership and teamwork skills, creative and intellectual, powers and passion for learning by providing an uncompromising standard of, excellence in teaching: embodying the spirit of excellence to educate the citizenleaders of society with distinction., , DEPARTMENT VISION AND MISSION, Vision, To emerge as a center of excellence with global reputation with adaption of, rapid advancements in the field of computer specialization., Mission, 1. To provide a strong theoretical and practical background in area of computer, science with an emphasize on software development., 2. To inculcate Professional behavior, strong ethical values, leadership qualities,, research capabilities and lifelong learning., 3. To educate students to become effective problem solvers, apply knowledge with, social sensitivity for the betterment of the society and humanity as a whole., , PROGRAM EDUCATIONAL OBJECTIVES (PE0s), Programme educational objectives are broad statements that describe the career and, professional accomplishments that the programme is preparing graduates to achieve, within 3 to 3 years after graduation. The Programme Educational Objectives of the B., Tech CSE programme are:

Page 3 :
 PEO1: To apply the knowledge of mathematics, basic science and engineering solving the real, world computing problems to succeed higher education and professional careers.,  PEO2: To develop the skills required to comprehend, analyze, design and create innovative, computing products and solutions for real life problems.,  PEO3: To inculcate professional and ethical attitude, communication and teamwork skills, multidisciplinary approach and an ability to relate computer engineering issues with social awareness., , COURSE OBJECTIVES:, This course will enable students to, • Foundation knowledge into database concepts, technology and practice to groom stud, database application developers., • Strong practice in SQL programming through a variety of database problems., • Develop database applications using front-end tools and back-end DBMS., , into well-informed, , COURSE OUTCOMES:, The students should be able to:, • Create, Update and query on the database., • Demonstrate the working of different concepts of DBMS, • Implement, analyze and evaluate the project developed for an application., GUIDELINES TO STUDENTS, 1. Equipment in the lab for the use of student community. Students need to maintain proper decorum in the, computer lab. Students must use the equipment with care Any damage is caused is punishable., 2. Students are instructed to come to lab in formal dresses only., 3. Students are supposed to occupy the systems allotted to them and are not supposed to talk or make noise in, the lab., 4. Students are required to carry their observation book and lab records with completed exercises while, entering the lab., 5. Lab records need to be submitted every week. 6. Students are not supposed to use pen drives in the lab., Do's, 1. Come with completed observation and record., 2. Wear ID card before entering into the lab., 3. Read and understand how to carry out an activity thoroughly before coming to laboratory., 4. Report any broken plugs or exposed electrical wires to your lecturer/laboratory technician immediately., , 5. Write in time, out time and system details in the login register.

Page 4 :
Don'ts, 1. Do not eat or drink in the laboratory., 2. Do not operate mobile phones in the lab., 3. Do not change system settings., 4. Do not disturb your neighboring students. They may be busy in completing tasks., 5. Do not remove anything from the computer laboratory without permission., 6. Do not use pen drives.

Page 5 :
DATABASE MANAGEMENT SYSTEM LABORATORY, , Subject Code: 19SCSL55, Number of Lecture Hours/Week:01I+02P, Total Number of Lecture Hours :40, , IA Marks:50, Exam Marks:50, Exam Hours:03, , COURSE LEARNING OBJECTIVES,  Foundation knowledge in database concepts, technology and practice to groom students into well-informed, database application developers.,  Strong practice in SQL programming through a variety of database problems.,  Develop database applications using front-end tools and back-end DBMS., , Lab Experiments:, A. Consider the following schema for a LibraryDatabase:, BOOK (Book_id, Title, Publisher_Name, Pub_Year), BOOK_AUTHORS (Book_id, Author_Name), PUBLISHER (Name, Address, Phone), BOOK_COPIES (Book_id, Branch_id, No-of_Copies), BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH, (Branch_id, Branch_Name, Address), Write SQL queries to, 1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of, copies in each branch,etc., 2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan, 2017 to Jun2017, 3. Delete a book in BOOK table. Update the contents of other tables to reflect this data, manipulationoperation., 4. Partition the BOOK table based on year of publication. Demonstrate its working with a, simplequery., 5. Create a view of all books and its number of copies that are currently available in the, Library., , B. Consider the following schema for OrderDatabase:

Page 6 :
SALESMAN (Salesman_id, Name, City, Commission), CUSTOMER (Customer_id, Cust_Name, City,, Grade,Salesman_id), ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id,, Salesman_id) Write SQL queries to, 1. Count the customers with grades above Bangalore’saverage., 2. Find the name and numbers of all salesmen who had more than onecustomer., 3. List all salesmen and indicate those who have and don’t have customers in, their cities (Use UNIONoperation.), 4. Create a view that finds the salesman who has the customer with the highest, order of a day., 5. Demonstrate the DELETE operation by removing salesman with id 1000. All his, orders must also bedeleted., C. Consider the schema for MovieDatabase:, ACTOR (Act_id, Act_Name, Act_Gender), DIRECTOR (Dir_id, Dir_Name, Dir_Phone), MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id) MOVIE_CAST (Act_id, Mov_id,, Role), RATING (Mov_id, Rev_Stars), Write SQL queries to, 1. List the titles of all movies directed by‘Hitchcock’., 2. Find the movie names where one or more actors acted in two or moremovies., 3. List all actors who acted in a movie before 2000 and also in a, movieafter 2015 (use JOINoperation)., 4. Find the title of movies and number of stars for each movie that has at least, one rating and find the highest number of stars that movie received. Sort the, result by movie title., 5. Update rating of all movies directed by ‘Steven Spielberg’ to5., , D. Consider the schema for CollegeDatabase:, STUDENT (USN, SName, Address, Phone, Gender), SEMSEC (SSID, Sem, Sec), CLASS (USN, SSID), SUBJECT (Subcode, Title, Sem, Credits), IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA), Write SQL queries to, 1. List all the student details studying in fourth semester ‘C’section., 2. Compute the total number of male and female students in each semester and, in each section., 3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in allsubjects.

Page 7 :
4. Calculate the FinalIA (average of best two test, marks) and, update the corresponding table for allstudents., 5. Categorize students based on the following criterion: If FinalIA = 17 to 20 then CAT, =‘Outstanding’If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT =, ‘Weak’, Give these details only for 8th semester A, B, and C section students., , E. Consider the schema for CompanyDatabase:, EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo) DEPARTMENT (DNo,, DName, MgrSSN, MgrStartDate), DLOCATION (DNo,DLoc), PROJECT (PNo, PName, PLocation, DNo), WORKS_ON (SSN, PNo, Hours), Write SQL queries to, 1. Make a list of all project numbers for projects that involve an employee, whose last name is ‘Scott’, either as a worker or as a manager of the, department that controls theproject., 2. Show the resulting salaries if every employee working on the ‘IoT’ project is, given a 10 percentraise., 3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as, well as the maximum salary, the minimum salary, and the average salary in this, department, 4. Retrieve the name of each employee who works on all the projects controlled, by department number 5 (use NOT EXISTS operator). For each department, that has more than five employees, retrieve the department number and the, number of its employees who are making more than Rs.6,00,000.

Page 9 :
INSERT INTO BOOK_AUTHORS VALUES(103 ,'JOHN');, INSERT INTO BOOK_AUTHORS VALUES(104 ,'GENRKE');, INSERT INTO BOOK_AUTHORS VALUES(105 ,'JOHN');, INSERT INTO LIBRARY_BRANCH VALUES (101,'PEARSON','BANGALORE');, INSERT INTO LIBRARY_BRANCH VALUES (102,'PEARSON','BANGALORE');, INSERT INTO LIBRARY_BRANCH VALUES (103,'SBC','MANGALORE');, INSERT INTO LIBRARY_BRANCH VALUES (104,'SBC','MANGALORE');, INSERT INTO LIBRARY_BRANCH VALUES (105,'SBC','MANGALORE');, INSERT INTO BOOK_COPIES VALUES(101, 101,200);, INSERT INTO BOOK_COPIES VALUES(102, 102,400);, INSERT INTO BOOK_COPIES VALUES(103, 103,500);, INSERT INTO BOOK_COPIES VALUES(104, 104,600);, INSERT INTO BOOK_COPIES VALUES(105, 105,700);, INSERT INTO BOOK_COPIES VALUES(101, 105,400);, INSERT INTO BOOK_COPIES VALUES(101, 104,500);, INSERT INTO BOOK_LENDING VALUES (101,101,02,'16-AUG-2017','25-AUG-2017' );, INSERT INTO BOOK_LENDING VALUES (102,102,09,'14-AUG-2017','22-AUG-2017' );, INSERT INTO BOOK_LENDING VALUES (103,103,05,'18-AUG-2017','27-AUG-2017' );, INSERT INTO BOOK_LENDING VALUES (104,104,06,'01-AUG-2017','09-AUG-2017' );, INSERT INTO BOOK_LENDING VALUES (105,105,01,'15-AUG-2017','24-AUG-2017' );, INSERT INTO BOOK_LENDING VALUES (101,105,02,'16-JAN-2017','25-FEB-2017' );, INSERT INTO BOOK_LENDING VALUES (101,103,02,'16-FEB-2017','25-FEB-2017' );, INSERT INTO BOOK_LENDING VALUES (103,105,02,'16-FEB-2017','25-FEB-2017' );, INSERT INTO BOOK_LENDING VALUES (104,105,01,'16-APR-2017','25-JUN-2017' );, INSERT INTO BOOK_LENDING VALUES (104,101,02,'16-JAN-2017','25-FEB-2017' );, INSERT INTO BOOK_LENDING VALUES (102,105,02,'01-JAN-2017','25-FEB-2017' );, Query 1:, SELECT B.Book_id,Title,P.Name,Author_Name,Branch_id,No_of_copies, FROM Book B,Book_Authors BA,Publisher P,Book_Copies BC, WHERE B.Book_id=BA.Book_id AND, B.Publisher_Name=P.Name AND, B.Book_id=BC.Book_id;, Output:, Book Title, Name Author Name, Branch Id, 101, 102, 103, , Fundamentals of DBMS Pearson SHAMAKANTH, Micrsoft Visual Studio Pearson, RAMEZ, .NET, SBC, JOHN, , 104 ADE, , SBC, , GENRKE, , No Of Copies, , 101, 102, 103, , 200, 400, 500, , 104, , 600

Page 11 :
Output:, Book_id, 102, 101, 107, 104, 103, , Total_Books, 400, 1100, 700, 600, 500

Page 12 :
EXPERIMENTS NO.2:ORDER Database, 1. Creating the tables, CREATE TABLE SALESMAN (SALESMAN_ID INTEGER, PRIMARY KEY,, NAME VARCHAR (20),, CITY VARCHAR (30),, COMMISSION VARCHAR (20));, , CREATE TABLE CUSTOMER (CUSTOMER_ID INTEGER, PRIMARY KEY,, CUST_NAME VARCHAR (20),, CITY VARCHAR (30),, GRADE INT,, SALESMAN_ID INTEGER,, FOREIGN KEY(SALESMAN_ID)REFERENCES, SALESMAN (SALESMAN_ID), ON DELETE SET NULL);, , CREATE TABLE ORDERS (ORD_NO INTEGER PRIMARY KEY,, PURCHASE_AMT INTEGER,, ORD_DATE DATE,, CUSTOMER_ID INTEGER,, SALESMAN_ID INTEGER,, FOREIGN KEY(SALESMAN_ID)REFERENCES, SALESMAN (SALESMAN_ID), ON DELETE CASCADE,, FOREIGN KEY(CUSTOMER_ID) REFERENCES, CUSTOMER(CUSTOMER_ID));, 2. Inserting the values, INSERT INTO SALESMAN VALUES (1000, 'JOHN','BANGALORE',2000);, INSERT INTO SALESMAN VALUES (1001, 'RAMEZ','BANGALORE',1000);, INSERT INTO SALESMAN VALUES (1002, 'GENRKE','MANGALORE',800);, INSERT INTO SALESMAN VALUES (1003, 'GORGE','MANGALORE',2800);, INSERT INTO SALESMAN VALUES (1004, 'RAMESH','BANGALORE',2890);, INSERT INTO SALESMAN VALUES (1005, 'SUNIL','MANGALORE',3890);, INSERT INTO CUSTOMER VALUES (2000, 'SUNIL','MANGALORE', 1, 1000);, INSERT INTO CUSTOMER VALUES (2001, 'ANIL','BANGALORE', 1, 1000);, INSERT INTO CUSTOMER VALUES (2002, 'JOHN','BANGALORE', 2, 1005);, INSERT INTO CUSTOMER VALUES (2003, 'JOHN','BANGALORE', 2, 1000);, INSERT INTO CUSTOMER VALUES (2004, 'SUNIL','MANGALORE', 3, 1005);, INSERT INTO CUSTOMER VALUES (2005, 'SUNIL','BANGALORE', 1, 1002);, INSERT INTO CUSTOMER VALUES (2006, 'SUNIL','BANGALORE', 1, 1003);

Page 13 :
INSERT INTO CUSTOMER VALUES (2007, 'RAHUL','CHENAI', 2, 1003);, INSERT INTO ORDERS VALUES (101, 7000, '21-AUG-17', 2002, 1005);, INSERT INTO ORDERS VALUES (102, 3000, '21-AUG-17', 2001, 1000);, INSERT INTO ORDERS VALUES (105, 8000, '21-AUG-17', 2000, 1000);, INSERT INTO ORDERS VALUES (108, 9000, '21-AUG-17', 2000, 1000);, INSERT INTO ORDERS VALUES (111, 5000, '29-AUG-17', 2000, 1000);, INSERT INTO ORDERS VALUES (121, 5900, '29-AUG-17', 2003, 1000);, INSERT INTO ORDERS VALUES (221, 5970, '29-AUG-17', 2004, 1005);, INSERT INTO ORDERS VALUES (291, 5970, '29-AUG-17', 2005, 1002);, INSERT INTO ORDERS VALUES (991, 5970, '29-AUG-17', 2006, 1003);, , Query 1:, SELECT COUNT(Customer_id), FROM CUSTOMER, WHERE GRADE>(SELECT AVG(GRADE) FROM CUSTOMER, WHERE CITY='BANGALORE');, Output:, No_Of_Copies, 2, Query 2:, SELECT S.Salesman_id,S.Name, FROM SALESMAN S,CUSTOMER C, WHERE S.Salesman_id=C.Salesman_id, GROUP BY S.Salesman_id,S.Name, HAVING COUNT(Customer_id)>1;, Output:, Salesman_Id, Name, 1003, GORGE, 1000, JOHN, 1005, SUNIL, Query 3:, SELECT S.Salesman_id FROM, SALESMAN S,CUSTOMER C, WHERE S.Salesman_id=C.Salesman_id, AND S.City!=C.City, UNION, SELECT S.Salesman_id, FROM SALESMAN S,CUSTOMER C, WHERE S.Salesman_id=C.Salesman_id, AND S.City=C.City;, Output:

Page 16 :
insert into movies values(11,'Mungaru Male',2008,'Kannada',102);, insert into movies values(12,'Kempegouda ',2010,'Kannada', 103);, insert into movies values( 13,'Premaloka', 1990,'Kannada', 104);, insert into movies values(14,'Apta Mitra',2010,,'Kannada', 105);, insert into movies values(15,'Apta Rakshaka',2012,'Kannada', 105);, Insert Into movies values( 16,'Ranadheera', 1992 'Kannada', 104);, Insert Into movies values( 17,'Ganeshana Maduve', 1990,'Kannada',101);, Insert Into movies values( 18,'The Last Chance', 2015,'English',106);, Insert into movies values( 19,'The War' ,2016,'English', 106);, insert into movies values( 20,'Murder', 2016,'English', 106);, Insert Into movies values(21, 'The Beautiful Bird' ,'English', 107);, insert into movie_cast values( 1,11,'Hero');, insert into movie_cast values( 2 ,12,' Hero');, insert into movie_cast values( 3 ,14,' Hero');, insert into movie_cast values( 5 ,16,' Hero');, insert into movie_cast values( 6 ,17,' Hero');, insert into movie_cast values( 1 ,17,' Hero');, insert into movie_cast values( 1 ,18,' Hero');, insert into movie_cast values( 1 ,19,' Subordinate');, insert into movie_cast values( 2 ,20,' Hero');, insert into movie_cast values( 2 ,21,' Hero');, insert into rating values( 11,5);, insert into rating values( 12,4);, insert into rating values( 13,5);, insert into rating values( 14,4);, insert into rating values( 15,5);, insert into rating values( 16,5);, insert into rating values( 17,5);, insert into rating values( 18,3);, insert into rating values( 19,3);, insert into rating values( 20,1);, insert into rating values( 21,4);, Query 1:, select mov_title, from movies, where dir_id=(select dir_id from director where dir_name = 'Hitchcock');, Output:, Mov_Title, The Last Chance

Page 26 :
Output:, PNO, 102, 105, , Query 2:, select Name,Salary "Old Salary", Salary* 1.1 "New Salary", from employee5 e,works_on5 w, project5 p, where e.ssn =w.ssn and w.pno= p.pno and pname = 'IoT';, Output:, NAME, OLD SALARY, NEW SALARY, JOHN, , 25000, , 27500, , ALICIA, , 23000, , 25300, , PRAKASH, , 85000, , 93500, , Query 3:, select sum(salary) "Total Salary",, Max(Salary) "Maximum Salary" ,, Min(Salary) "Minimum Salary",, Avg(Salary) "Average Salary", from employee5 e, department5 d, where e.dno= d.dno and dname='Accounts';, Output:, TOTAL SALARY, MAX SALARY, MIN SALARY, 50000, , 25000, , Query 4:, select e.name from employee5 e, where not exists, ((select pno, from project5, where dno=5) except, (select pno, from works_on5, where e.ssn= ssn));, , 25000, , AVERAGE SALARY, 25000

Page 28 :
Viva Questions, 1. What is SQL?, Structured Query Language, 2. What is database?, A database is a logically coherent collection of data with some inherent meaning, representing, some aspect of real world and which is designed, built and populated with data for a specific, purpose., 3. What is DBMS?, It is a collection of programs that enables user to create and maintain a database. In other, words it is general-purpose software that provides the users with the processes of defining,, constructing and manipulating the database for various applications., 4. What is a Database system?, The database and DBMS software together is called as Database system., 5. Advantages of DBMS?, ➢ Redundancy is controlled., ➢ Unauthorized access is restricted., ➢ Providing multiple user interfaces., ➢ Enforcing integrity constraints., ➢ Providing backup and recovery., 6. Disadvantage in File Processing System?, ➢ Data redundancy &inconsistency., ➢ Difficult in accessing data., ➢ Data isolation., ➢ Data integrity., ➢ Concurrent access is not possible., ➢ Security Problems., 7. Describe the three levels of data abstraction?, There are three levels of abstraction:, ➢ Physical level: The lowest level of abstraction describes how data are stored., ➢ Logical level: The next higher level of abstraction, describes what data are stored in, database and what relationship among those data., ➢ View level: The highest level of abstraction describes only part of entire database.

Page 29 :
8., , Define the "integrity rules", There are two Integrity rules., ➢ Entity Integrity: States that―Primary key cannot have NULL value□, ➢ Referential Integrity:States that ―Foreign Key can be either a NULL value or, should be Primary Key value of other relation., , 9., , What is extension and intension?, Extension - It is the number of tuples present in a table at any instance. This is time dependent., Intension -It is a constant value that gives the name, structure of table and the constraints laid on it., , 10. What is Data Independence?, Dataindependencemeansthat―theapplicationisindependentofthestoragestructureand access, strategy of data□. In other words, The ability to modify the schema definition in one level, should not affect the schema definition in the next higher level., Two types of Data Independence:, ➢ Physical Data Independence: Modification in physical level should not affect, the logical level., ➢ Logical Data Independence: Modification in logical level should affect the view, level., 11. What is a view? How it is related to data independence?, A view may be thought of as a virtual table, that is, a table that does not really exist in its, own right but is instead derived from one or more underlying base table. In other words, there is, no stored file that direct represents the view instead a definition of view is stored in data, dictionary., Growth and restructuring of base tables is not reflected in views. Thus the view can, insulate users from the effects of restructuring and growth in the database. Hence accounts for, logical data independence., 12. What is Data Model?, A collection of conceptual tools for describing data, data relationships data semantics and, constraints., 13. What is E-R model?, This data model is based on real world that consists of basic objects called entities and, of relationship among these objects. Entities are described in a database by a set of attributes., 14. What is Object Oriented model?

Page 30 :
This model is based on collection of objects. An object contains values stored in instance, variables within the object. An object also contains bodies of code that operate on the object., These bodies of code are called methods. Objects that contain same types of values and the same, methods are grouped together into classes., 15. What is an Entity?, It is an 'object' in the real world with an independent existence., 16. What is an Entity type?, It is a collection (set) of entities that have same attributes., 17. What is an Entity set?, It is a collection of all entities of particular entity type in the database., 18. What is an Extension of entity type?, The collections of entities of a particular entity type are grouped together into an entity set., 19. What is an attribute?, It is a particular property, which describes the entity., 20. What is a Relation Schema and a Relation?, A relation Schema denoted by R(A1, A2, …, An) is made up of the relation, , name R, , and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the, relation which contains set tuples (t1,t2, t3,...,tn). Each tuple is an ordered list of n- values, t=(v1,v2, ...,vn)., 21. What is degree of a Relation?, It is the number of attribute of its relation schema., 22. What is Relationship?, It is an association among two or more entities., 23. What is Relationship set?, The collection (or set) of similar relationships., 24. What is Relationship type?, Relationship type defines a set of associations or a relationship set among a given set, of entity types., 25. What is degree of Relationship type?, It is the number of entity type participating., 26. What is DDL (Data Definition Language)?, A data base schema is specified by a set of definitions. expressed by a special

Page 31 :
language called DDL., 27. What is VDL (View Definition Language)?, It specifies user views and their mappings to the conceptual schema., 28. What is SDL (Storage Definition Language)?, This language is to specify the internal schema. This language may specify the mapping between two, schemas., 29. What is Data Storage – Definition Language?, The storage structures and access methods used by database system are specified by a set of definition, in a special type of DDL called data storage-definition language., 30. What is DML (Data Manipulation Language)?, This language that enable user to access or manipulate data as organized by appropriate data model., ➢ Procedural DML or Low level: DML requires a user to specify what data are, needed and how to get those data., ➢ Non-Procedural DML or High level: DML requires a user to specify what data are needed without, specifying how to get those data., 31. What is DML Compiler?, It translates DML statements in a query language into low-level instruction that the, query evaluation engine can understand., 32. What is Relational Algebra?, It is a procedural query language. It consists of a set of operations that take, two relations as input and produce a new relation., 33. What is Relational Calculus?, , one or

Page 32 :
It is an applied predicate calculus specifically tailored for relational databases, proposed by E.F. Codd. E.g. of languages based on it are DSL, ALPHA,QUEL., 34. What is normalization?, It is a process of analyzing the given relation schemas based on their, , Functional, , Dependencies (FDs) and primary key to achieve the properties, ➢ Minimizing redundancy, ➢ Minimizing insertion, deletion and update anomalies., 35. What is Functional Dependency?, A Functional dependency is denoted by X Y between two sets of attributes X and Y, thataresubsetsofRspecifiesaconstraintonthepossibletuplethatcanformarelationstaterof, R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] =, t2[Y]. This means the value of X component of a tuple uniquely determines the value of, component Y., 36. When is a functional dependency F said to be minimal?, ➢ Every dependency in F has a single attribute for its right hand side., ➢ We cannot replace any dependency X, , A in F with a dependencyY, , A where Y is a, , proper subset of X and still have a set of dependency that is equivalent to F., ➢ We cannot remove any dependency from F and still have set of dependency that is, equivalent to F., 37. What is Multivalued dependency?, Multivalued dependency denoted by X Y pecified on relation schema R, where X and Y, are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1, and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following, properties, ➢ t3[x] = t4[X] = t1[X] =t2[X], ➢ t3[Y] = t1[Y] and t4[Y] =t2[Y], ➢ t3[Z] = t2[Z] and t4[Z] = t1[Z], where [Z = (R-(X U Y)) ], 38. What is Lossless join property?, It guarantees that the spurious tuple generation does not occur with respect to, relation schemas after decomposition.

Page 33 :
39. What is 1 NF (Normal Form)?, The domain of attribute must include only atomic (simple, indivisible) values., 40. What is Fully Functional dependency?, It is based on concept of full functional dependency. A functional dependency X Y is, fully functional dependency if removal of any attribute A from X means that the dependency, does not hold anymore., 41. What is2NF?, A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully, functionally dependent on primary key., 42. What is3NF?, A relation schema R is, , in 3NF if it is in 2NF and for every FD X, , A either of the, , following is true, ➢ X is a Super-key of R., ➢ A is a prime attribute of R., In other words, if every non prime attribute is non-transitively dependent on primary key., 43. What is BCNF (Boyce-Codd Normal Form)?, A relation schema R is in BCNF if it is in 3NF and satisfies additional constraints that for, every FD X A, X must be a candidate key., 44. What is4NF?, A relation schema R is said to be in 4NF if for every Multivalued dependency X, , Y that holds over R, one of following is true, ➢ X is subset or equal to (or) XY =R., ➢ X is a super key., 45. What is5NF?, A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ...,Rn}, that holds R, one the following is true, ➢ Ri = R for somei., ➢ The join dependency is implied by the set of FD, over R in which the left side is key ofR.

Page 34 :
46. What is Domain-Key Normal Form?, A relation is said to be in DKNF if all constraints and dependencies, that should hold on the constraint can be enforced by simply enforcing, the domain constraint and key constraint on the relation.