Relational Database Management System -III b.Com(CA)- unit-3

Q) Define the terms a)Entity b)Entity Set c)Entity Instance d)Entity type

a) Entity: An entity may be an object with a physical existence—a particular person, car, house, or employee (or) it may be an object with a conceptual existence—a company, a job, or a university course.  Entities are represented by means of rectangles. Rectangles are named with the entity set they represent.  For example, in a school database, student, teachers, class and course offered can be considered as entities.

b) Entity Set:  Entity Set is nothing but the set of entities of same type.

let us consider an entity by name student with attributes stno, stname, fname, city. A record of student say 1001, Ramana, Prasad, Kurnool, it is called as Entity and collection of these kind of entities that is collection all the student entities is called as Entity Set.

c) Entity instance: Entity instance is defined as the data that is stored at a point of time in an entity. It looks same as Entity but is little bit different. Entity instance comes into picture when we start accessing the data from the database.

d) Entity type: Entity type is nothing but the structure of the entity that is nothing but the list of attributes and its properties. For any entity there can be any number entity instances but there will be only one Entity type. For example consider an entity

                                            

1001	Ramana	Prasad	Kurnool
1002	Krishna	Ranga	Anantapur
1003	Rahim	Abdur	Hyderabad
1004	Sunil	Samuel	Cuddapah

Q) Explain about an attributes and its type with example

Attributes:

·         Attributes are properties of entities.

o   For example, a student entity may have name, class, age as attributes.           

·         Attributes are represented by means of eclipses. Every eclipse represents one attribute and is directly connected to its entity (rectangle).

·         Following are some typical entities and their attributes

                       

          STUDENT        (Student_Id, Student_Name, Address, Phone_No)

                EMPLOYEE        (Employee_Number, Employee_Name, Deptno, City)

                CUSTOMER       (Cust_No, Cust_Name, Bill_No, Amount)

Types of attributes:

1.       Simple attribute: 

·         Simple attributes are atomic values, which cannot be divided further.

·         For example, student's phone-number is an atomic value of 10 digits.

2.       Composite attribute: 

·         A Composite attribute is an attribute that is further divided that is the attribute will be a combination of multiple attributes.

·         Composite attributes can form a hierarchy

·         For example, Name can be subdivided into three simple attributes, First_Name, Middle Name, Last_Name.

3.       Single-valued attribute:

·         Single valued attributes contain on single value.

·         For example: Roll_No.

4.       Multi-value attribute:

·         Multi-value attribute may contain more than one values.

·         For example, a person can have more than one phone numbers, email_addresses etc.

·         Multivalued attributes are depicted by double eclipse.

5.       Derived attribute:

·         Derived attributes are attributes, which do not exist physical in the database, but there values are derived from other attributes presented in the database.

·         For example, age can be derived from data_of_birth.

·         Derived attributes are depicted by dashed eclipse

Q) Define  Relationship and Relationship Set. Explain briefly about Relationship Cardinality (OR) Mapping Cardinalitiy.

Ans: Relationship: The association among entities is called relationship. For example, employee entity has relation works_at with department. Another example is for student who enrolls in some course. Here, Works_at and Enrolls are called relationship.  Relationships are represented by diamond shaped box. Name of the relationship is written in the diamond-box. All entities (rectangles), participating in relationship, are connected to it by a line.

Relationship Set:

Relationship of similar type is called relationship set. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes.

Binary relationship and cardinality :

A relationship where two entities are participating, is called a binary relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.

1)  One-to-one : one entity from entity set A can be associated with at most one entity of entity set B and vice versa. When only one instance of entity is associated with the relationship, it is marked as '1'. This image below reflects that only 1 instance of each entity should be associated with the relationship. It depicts one-to-one relationship

2) One-to-many :

One entity from entity set A can be associated with more than one entities of entity set B but from entity set B one entity can be associated with at most one entity.

When more than one instance of entity is associated with the relationship, it is marked as 'N'. This image below reflects that only 1 instance of entity on the left and more than one instance of entity on the right can be associated with the relationship. It depicts one-to-many relationship

3) Many-to-one :

More than one entities from entity set A can be associated with at most one entity of entity set B but one entity from entity set B can be associated with more than one entity from entity set A. When more than one instance of entity is associated with the relationship, it is marked as 'N'. This image below reflects that more than one instance of entity on the left and only one instance of entity on the right can be associated with the relationship. It depicts many-to-one relationship

4) Many-to-many :

one entity from A can be associated with more than one entity from B and vice versa.

This image below reflects that more than one instance of entity on the left and more than one instance of entity on the right can be associated with the relationship. It depicts many-to-many relationship.

Q) Explain briefly about Degree of relationship

The Degree of a Relationship is the number of entity types that participate in that relationship. The three most common relationship degrees in E_R models are

• Unary Relationship = Degree 1
• Binary Relationship = degree 2
• Ternary Relationship = degree 3

1. Unary Relationship:

A unary relationship is the relationship b/w the instances of a single entity type. This relationship is also called Recursive Relationship.

For example consider a relationship Employee and a situation where we would like to represent the relationship of a manager to employee that is a manger is also an employee and who will be part of the Employee entity itself and hence we need to use the same Entity EMPLOYEE two times as shown the figure.

2. Binary Relationship:

A binary relationship is a relationship between the instances of two entity types and is the most common type of relationship in data modeling.

The first example given in the above indicates that an employee is assigned one parking place and each parking place is assigned to one employee.

The third example shows that a student may register for more than one course and each course may have any no. of students.

3. Ternary Relationship:

A ternary relationship is a simultaneous relationship among the instances of three entity types. In the above example vendors can supply various parts to warehouses. Thus there are three entity types in the association and also there are two attributes on the relationship. So the ternary relationship is an associative entity.

Q) Explain E-R Diagram notation with example

Q) Explain about Strong Entity and Weak Entity.

Strong Entity is an entity type which exists independently of other entity types. That is the Entity itself where the instances of the entity can be accessed directly from it.  That is Strong Entity is an entity which will have a primary key define and all the tuples can be distinguished from each other.

Weak Entity is an entity which does not have a Primary Key and is dependent on another Entity that has a Primary Key. The Weak Entity does not have any business meaning directly in any ER model but it has its value or identification only when it is joined with the entity on which it depends.

The Entity on which the weak entity depends is called as Identifying owner and relationship is called as identifying relationship.  Identifying relationship depicted using a double diamond. The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set. The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set discriminator.

We depict a weak entity set by double rectangles. We underline the discriminator of a weak entity set with a dashed line. payment-number . discriminator of the payment entity set. Primary key for payment . (loannumber,payment-number)

Q) Explain about Extended E -R Diagrams/ EER Diagrams

Generalization is a bottom-up approach in which two lower level entities combine to form a higher level entity. In generalization, the higher level entity can also combine with other lower level entity to make further higher level entity.

Specialization is opposite to Generalization. It is a top-down approach in which one higher level entity can be broken down into two lower level entity. In specialization, some higher level entities may not have lower-level entity sets at all.

Aggregration is a process when relation between two entity is treated as a single entity. Here the relation between Center and Course, is acting as an Entity in relation with Visitor

Q: ER Model For a college DB:

Assumptions :

• A college contains many departments
• Each department can offer any number of courses
• Many instructors can work in a department
• An instructor can work only in one department
• For each department there is a Head
• An instructor can be head of only one department
• Each instructor can take any number of courses
• A course can be taken by only one instructor
• A student can enroll for any number of courses
• Each course can have any number of students

Steps in ER Modeling:

• Identify the Entities
• Find relationships
• Identify the key attributes for every Entity
• Identify other relevant attributes
• Draw complete E-R diagram with all attributes including Primary Key

Step 1: Identify the Entities:

• DEPARTMENT
• STUDENT
• COURSE
• INSTRUCTOR

Step 2: Find the relationships:

• One course is enrolled by multiple students and one student enrolls for multiple courses, hence the cardinality between course and student is Many to Many.
• The department offers many courses and each course belongs to only one department, hence the cardinality between department and course is One to Many.
• One department has multiple instructors and one instructor belongs to one and only one department , hence the cardinality between department and instructor is one to Many.
• Each department there is a “Head of department” and one instructor is “Head of department “,hence the cardinality is one to one .
• One course is taught by only one instructor, but the instructor teaches many courses, hence the cardinality between course and instructor is many to one.

Step 3: Identify the key attributes

• Dept name is the key attribute for the Entity “Department”, as it identifies the Department uniquely.
• Course# (CourseId) is the key attribute for “Course” Entity.
• Student# (Student Number) is the key attribute for “Student” Entity.
• Instructor Name is the key attribute for “Instructor” Entity.

Step 4: Identify other relevant attributes

For the department entity, the relevant attribute is location

• For course entity, course name, duration, prerequisite
• For instructor entity, room#, telephone#
• For student entity, student name, date of birth

Q:ER model for Banking Enterprise:

Assumptions :

• There are multiple banks and each bank has many branches. Each branch has multiple customers
• Customers have various types of accounts
• Some Customers also had taken different types of loans from these bank branches
• One customer can have multiple accounts and Loans

Step 1: Identify the Entities
• BANK
• BRANCH
• LOAN
• ACCOUNT
• CUSTOMER
Step 2: Find the relationships
• One Bank has many branches and each branch belongs to only one bank, hence the cardinality between Bank and Branch is One to Many.
• One Branch offers many loans and each loan is associated with one branch, hence the cardinality between Branch and Loan is One to Many.
• One Branch maintains multiple accounts and each account is associated to one and only one Branch, hence the cardinality between Branch and Account is One to Many
• One Loan can be availed by multiple customers, and each Customer can avail multiple loans, hence the cardinality between Loan and Customer is Many to Many.
• One Customer can hold multiple accounts, and each Account can be held by multiple Customers, hence the cardinality between Customer and Account is Many to Many

Step 3: Identify the key attributes
• Bank Code (Bank Code) is the key attribute for the Entity “Bank”, as it identifies the bank
uniquely.
• Branch# (Branch Number) is the key attribute for “Branch” Entity.
• Customer# (Customer Number) is the key attribute for “Customer” Entity.
• Loan# (Loan Number) is the key attribute for “Loan” Entity.
• Account No (Account Number) is the key attribute for “Account” Entity. 

Step 4: Identify other relevant attributes
• For the “Bank” Entity, the relevant attributes other than “Bank Code” would be “Name” and “Address”.
• For the “Branch” Entity, the relevant attributes other than “Branch#” would be “Name” and “Address”.
• For the “Loan” Entity, the relevant attribute other than “Loan#” would be “Loan Type”.
• For the “Account” Entity, the relevant attribute other than “Account No” would be “Account Type”.
• For the “Customer” Entity, the relevant attributes other than “Customer#” would be “Name”, “Telephone#” and “Address”.

Q) Explain the conversation of ER diagrams into tables (OR) Transforming EER diagrams

In the transformation process there are basically three types of entities are involved. They are

Regular Entities: These are the entities that have an independent existence and generally represent real-world objects such as persons and products.

Weak Entity: these are the entities that cannot exist except with an identifying relationship with an owner entity type. Weak entities are identified by a rectangle with a double line.

Associative entities: These are formed from many-to-many relationships between other entity types. Associative entities are represented by a rectangle with a single line that encloses the diamond relationship symbol.

Step 1: Map Regular Entities:

Case (i) : Regular entity with Simple attribute:

Each regular entity in an ER diagram is transformed into a relation. The name of the entity type becomes the name of the relation. Each simple attribute of the entity type becomes an attribute of the relation. The identifier of the entity type becomes the primary key of the corresponding relation.

In this example CUSTOMER entity type has only three simple attributes. Cust_ID is the identifier which is represented as a primary key in the relation.

Case (ii) : Regular entity with composite attribute:

When a regular entity has a composite attribute, only the simple component attributes of the composite attributes are included in the new relation.

Ex: Customer entity type with composite attribute.

Case (iii) : Regular entity with Multi-valued attribute:

When the regular entity type contains a multi-valued attribute, two new relations are created. The first relation contains all of the attributes of the entity type except the multi-valued attribute. The second relation contains two attributes that form the primary key of the second relation. The first of these attributes is the primary key in the first relation. The second is the multi-valued attribute. The name of the second relation should include the meaning of the multi-valued attribute.

Ex: Employee entity type with multivalued attribute.

Here, EMPLOYEE ghas Skill as a multivalued attribute. This is represented as two relations.

1. EMPLOYEE which has the primary key Emp_ID.

2. EMPLOYYEE_SKILL, which has two attributes Emp_ID and Skill,which form the primary key.

Step 2: Map Weak Entities:

A weak entity type does not have an independent existence, but exists only through an identifying relationship with another entity type called the owner. A weak entity type does not have a complete identifier, but must have an attribute called a partial identifier that permits distinguishing the various occurrences of the weak entity for each owner entity instance.

For weak entity type, create a new relation and include all of the simple attributes as attributes of this relation. Then include the primary key of the identifying relation as a foreign key attribute in this new relation. The primary key of the new relation is the combination of this primary key identifying and the partial identifier of the weak entity type.

Q) WHY WE NEED NORMALIZATION? (OR) What are the features of Good Relational Designs

Normalization is a step by step, set of rules by which data is put in its simplest forms. The inventor of the relational model, Edgar Codd proposed the theory of normalization. We normalize the relational database management system because of the following reasons: 

1. Minimize data redundancy i.e. no unnecessarily duplication of data.
2. To make database structure flexible i.e. it should be possible to add new data values and rows without reorganizing the database structure.
3. Data should be consistent throughout the database i.e. it should not suffer from following anomalies.

1.     Insert Anomaly - Due to lack of data i.e., all the data available for insertion such that null values in keys should be avoided. This kind of anomaly can seriously damage a database

2.     Update Anomaly - It is due to data redundancy i.e. multiple occurrences of same values in a column. This can lead to inefficiency.

3.     Deletion Anomaly - It leads to loss of data for rows that are not stored elsewhere. It could result in loss of vital data.

4. Complex queries required by the user should be easy to handle.
5. On decomposition of a relation into smaller relations with fewer attributes on normalization the resulting relations whenever joined must result in the same relation without any extra rows. The join operations can be performed in any order. This is known as Lossless Join decomposition.
6. The resulting relations (tables) obtained on normalization should possess the properties such as each row must be identified by a unique key, no repeating groups, homogenous columns, each column is assigned a unique name etc.

The process of normalization is based on the concept of normal forms. Each and every normal form has its own set of properties and constraints. Different normal forms are,

1. First Normal Form(1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)
7. Sixth or Domain-key Normal form (DKNF)

Q) Explain Atomic Domains and First Normal Form with an example.

Atomic Domains/Atomic Values: An atomic value is a value that cannot be divided.

For example, Table_Product

ProductID	Color	Price
1	Red,Green	150
2	Yellow	230
3	Green	175
4	Yellow,Blue	900
5	Red	290

In the above table, the values in the [Color] column are non-atomic values because these values can be split into more than one filed. i.e, in the first row can be divided into "red" and "green".

First Normal Form (1NF ):

A database is in first normal form if it satisfies the following conditions:

• Contains only atomic values
• There are no repeating groups
• For each group of related data there should be separate tables, in which identify each row uniquely by a column or set of column

Example:  EmployeeMaster

Emp Code	Emp Name	Emp City One	Emp City Two	Basic Salary	Var Salary	Total Salary
EMP001	Mahaboob Shaik	Delhi	Gurgaun	10000	5000	15000
EMP002	Salman Khan	Mumbai	Pune	15000	8500	23500

An atomic value is a value that cannot be divided. For example, in the table shown above, the values in the [Emp Name] column in the first row can be divided into "Mahaboob" and "Shaik", hence [EmployeeMaster] is not in 1NF.so, We divide the Emp Name in two fields such as Emp First Name and Emp Last Name as shown below.

Emp Code	Emp First Name	Emp Last Name	Emp City One	Emp City Two	Basic Salary	Var Salary	Total Salary
EMP001	Mahaboob	Shaik	Delhi	Gurgaun	10000	5000	15000
EMP002	Salman	Khan	Mumbai	Pune	15000	8500	23500

A repeating group means that a table contains two or more columns that are closely related. For example, in the above table [Emp City one] and [Emp City Two] are closely realted with each other. So the above table is not in 1NF.so, we group city in one column as shown below.

Emp Code	Emp First Name	Emp Last Name	Emp City	Basic Salary	Var Salary	Total Salary
EMP001	Mahaboob	Shaik	Delhi	10000	5000	15000
EMP001	Mahaboob	Shaik	Gurgaun	10000	5000	15000
EMP002	Salman	Khan	Mumbai	15000	8500	23500
EMP002	Salman	Khan	Pune	15000	8500	23500

Now the table is in 1NF

Q) Explain Fully functionally dependent and its types.

Functional dependencies are the result of interrelationship between attributes or in between tuples in any relation

Functional Dependency:

A functional dependency can be described as follows:

·        An attribute is functionally dependent if its value is determined by another attribute.

·        That is, if we know the value of one data items, then we can find the value of another.

·        Functional dependencies are expressed as X àY, where X is the determinant and Y is the functionally dependent attribute.

Employee

EID	Name	Salary
1	Aditya	15000
2	Manoj	16000
3	Sandeep	900
4	Vikas	10000
5	Manoj	9000

If employee relation, EID is primary key. Suppose you want to know the name and salary of any employee. If you have EID of that employee, then you can easily find information of that employee. So, Name and salary attributes depend upon EID attribute.

Here, X is (EID) and Y is (Name, Salary)

X (EID)àY(Name,salary)

The determinant is EID. Suppose x has value 5 then Y has value (Manoj,9000)

The graphical representation is as shown below

Types of functional dependencies

There are four major types of FD’s

1. Partial dependency and fully functional dependency
2. Transitive Dependency and non – Transitive dependency
3. Single valued Dependency and multi valued dependency
4. Trival Dependency and Non-Trival Dependency:

1. Partial dependency and fully functional dependency:

Partial dependency: Suppose you have more than one attributes in primary key. Let A be non-prime key attribute. If A is not dependent upon all prime key attributes then partial dependency exists.

Fully functional dependency:Let A be the non-prime key attribute and value of A is dependent upon all prime key attributes. Then A is said to be fully functional dependent.

Consider a realtion student having prime key attributes (RollNo and Game) and non-prime key attribute(Grade,Name and Fee)

As shown in fig. Name and fee are partically dependent because you can find the name of the student by his rollno. And fee of any game by name of the game.

Grade is fully functionally dependent because you can find grade of any student in a particular game if you know Rollno and Game of that student. Partical dependency is due to more than one prime key attribute.

2. Transitive Dependency and Non Transitive Dependency:

Transitive dependency: Transitive dependency is due to dependency between non-prime key attributes. Suppose in a relation R, XàY (Y depends upon X),YàZ(Z depends upon Y), then XàZ(Z depends upon X). Therefore, Z is said to be transitively dependent upon X.

Non-Transitive dependency: any functional dependency which isnot transitive is known as non-transitive dependency. Non-transitive dependency exists if there is no dependency between non-prime key attributes.

Consider a relation student having primary key attribute (RollNo) and non-prime key attributes (Name,Semseser,Hostel)

For each semester there is different hostel. Here hostel is transitively dependent upon RollNo. Semester of any student can be find by his RollNo. Hosterl can find out by semester of student.

Here, Name is non-transitively dependent upon RollNo.

3. Single valued Dependency and multi valued dependency:

Single valued Dependency: In a relation R, if for a particular value of X,Y has single value then it is known as single valued dependency.

Multi valued dependency: In a relation R, if for a particular value of X,Y has more than one value then it is known as multi valued dependency. It is denoted by XààY

Consider the relation Teacher ID Teacher Class Days 1Z Sam Computer 1 2Z John Computer 6 1Z Sam Electronics 3 2Z John Mechanical 5 3Z Nick Mechanical 2

There is MVD between Teacher and Class because a teacher can take more than one class. There is another MVD between Class and Days because a class can be on more than one day.

There is single valued dependency between ID and Teacher because each teacher has a unique ID.

4. Trival Dependency and Non-Trival Dependency:

Trival Dependency:In any relation R, XàY is trival if Y is subset of X

Non-Trival Dependency:  In any relation R, XàY is non- trival if Y is not subset of X

                         Supplier _Product

S#(Supplier ID)	City	P# (Product ID)	Qunatity
1	Delhi	1P	100
2	Calcutta	8P	200
3	Pune	3P	50
4	Pune	5P	75
5	Calcutta	1P	99
6	Hyd	5P	105

Here (S#,P#) S# is trival FD

Q) Explain briefly about Second Normal Form (2NF).

Second Normal form (2NF): A Relation is in second normal form if it is in 1NF and all non-primary key attributes must be fully functionally dependent upon primary key attributes.

Consider the relation student as shown in figure:

Student RollNo. Game Name Fee Grade 1 Cricket Amit 200 A 2 Badminton Dheeraj 150 B 3 Cricket Lalit 200 A 4 Badminton Parul 150 C 5 Hockey Jack 100 A 6 Cricket John 200 C

The primary key is (RollNo.,Game). Each student can participate in more than one game. Relation student is in 1NF but still contains anomalies.

1.     Deletion anomaly: Suppose you want to delete student Jack. Here you loose information about game Hockey because he is the only player participated in hockey.

2.     Insertion anomaly: Suppose you want to add new game Basket Ball having no student participated in it. You cannot add this information unless there is a player for it.

3.     Updation anomaly: Suppose you want ot change fee of cricket. Here, you have to search all the students participated in cricket and update fee individually otherwise it produces inconsistency.

The solution of his problem is to separate Partial dependencies and Fully functional dependencies. So, divide Student relation into three relations student(RollNo.,Name), Games(game,fee) and Performance( RollNo.,Game,Grade) as shown below.

STUDENT RollNo. Name 1 Amit 2 Dheeraj 3 Lalit 4 Parul 5 Jack 6 John		GAMES Game Fee Cricket 200 Badminton 150 Hockey 100
	PERFORMANCE RollNo. Game Grade 1 Cricket A 2 Badminton B 3 Cricket A 4 Badminton C 5 Hockey A 6 Cricket C

Now, Deletion, Insertion and Updation operations can be performed without causing inconsistency.

Q) Explain briefly about Third Normal Form (3NF).

A relation is in third Normal Form if it is in 2NF and non-primary key attributes must be non-transitively dependent upon primary key attributes.

Consider the relation student as shown in fig.

The primary key is (RollNo.). the condition is different hostel is allotted for different semester. Student relation is in 2NF but still contains anomalies.

1.     Deletion anomaly: if you want to delete student Gaurav. You loose information about Hostel H2 because he is the only only  student staying in hostel H2.

2.     Insertion anomaly: if you want to add a new Hostel H8 and this is not alloated to any student. You cannot add this information.

3.     Updation anomaly: if you want to change hostels of all students of first semester. Ou have to search all the students of first semester and update them individually otherwise it causes inconsistency.

The solution of this problem is to divide relation Student into two relations STUDENT(RollNo.,Name,Semeseter) and Hostels (Semseter, Hostel) as shown in fig.

Now, Deletion, Insertion and Updation operations can be performed without causing inconsistency.

Q) Explain Boyce-Codd Normal form with an example.

BCNF is a strict format of 3 NF. A relation is in BCNF if and only if all determinants are candidate keys. BCNF deals with multiple candidate keys.

Relations in 3NF also contain anomalies. Consider the relation student as shown below.

Student RollNo. Subject Teacher 1 C T1 2 C++ T2 3 C T1 4 JAVA T3 5 JAVA T3 1 ORACLE T5 6 ORACLE T5 3 C++ T2 7 VB T4 8 Oracle T6

Assumptions:

·        Student can have more than 1 subject.

·        A teacher can teach only 1 subject

·        A subject can be taught by more than 1 teacher.

There are two candidate keys(RoolNo.,subject) and (Rollno.,Teacher)

1.     Deletion anomalies: if you delete student whose RollNo. is 7. You will also loose information that teacher T4 is teaching the subject VB.

2.     Insertion Anomalies: if you want to add a new subject VC++., you cannot dot that until a student chooses subject VC++ and a teacher teaches subject VC++.

3.     Updation anomalies: suppose you want to change teacher for subject C. you have to search all the students having subject C and update each record individually otherwise it causes inconsistency.

In relation, student, candidate key is overloaded. You can find teacher by Rollno. And subject. You can also find subject by Rollno. And Teacher. Here Rollno. Is overloaded. You can also find subject by teacher.

The solution of this problem is to divide relation student in two relations Stu-Teac and Teac-Sub as shown below.

STU-TEAC RollNo. Teacher 1 T1 2 T2 3 T1 4 T3 5 T3 1 T5 6 T5 3 T2 7 T4 8 T6

TEAC-SUB Teacher Subject T1 C T2 C++ T3 JAVA T5 ORACLE T4 VB T6 Oracle

In this solution all determinants are candidate keys.

Q) Explain Fourth Normal Form(4NF) with example

A relation is in 4NF if it is in BCNF and for all Multi Valued Functional Dependencies (MVD) of the form XààY either xày is atrival MVD or X is a super key of relation.

Relations in BCNF also contains anomalies. Consider the relation project-work as shown below.

Programmer	Project	Module
P1	1	M1
P2	1	M2
P3	2	M1
P1	3	M1
P4	3	M2

ASSUMPTIONS:

A programmer can work on any number of projects

A project can have more than one module.

Relation Project_work is in BCNF but still contains anomalies.

1)     Deletion anomaly : if you delete project2 . You lose information about programmer P3.

2)     Insertion anomaly: if you want to add a new project 4. You cannot add this project until is is assigned  to any programmer.

3)     Updation anomaly: if you want to change name of project1. Then you have to search all programmers having project 1 and update them individually otherwise it causes inconsistency.

Dependencies in Relation Project-Work are

Programmerà à Project.

Projectà àModule.

The solution of this problem is to divide relation Project-Work into two relations Prog-Prj(Programmer, Project) and Prj-Module(Project,Module) as shown below.

Proj-Prj Programmer Project P1 1 P2 1 P3 2 P1 3 P4 3	Prj_Module Project Module 1 M1 1 M2 2 M1 3 M1 3 M2
Here Programmer is the super key	Here Project is the super key

Q) Explain 5NF with example (OR) Explain PJNF (OR) Explain Loss Less decomposition.

Project Join Normal Form (%NF) and Join Dependency:

Join Dependency: Let R be a given relation upto $NF and it decomposes (Projected or Divided) into {R₁,R₂,R₃..,R_N}. The relation R satisfy the join dependency * {R₁,R₂,R₃..,R_N} if and only if joining of R₁ to R_n = R.

Consider the relation XYZ with attributes X#(customer_ID),Y#(Account_ID) and Z#(Branch_ID) as shown below.

Realtions XY, YZ and ZX are in 5NF and  XYZ satisfy the join dependency

When joined these three relations give original form of XYZ. So relation XYZ satisfy the join dependency.

Project Join Normal Form(5NF): Let R is a relation and D is a set of all dependencies (multivalued dependency, functional dependency, join dependency etc.)The relation R is in 5NF w.r.t. D if for every Join Dependency, Join Dependency is trivial. Relations XY, YZ and ZX are in 5NF.