Information Technology

College of Engineering Roorkee

  SECOND NORMAL FORM A table is said to be in its second normal form, when a relation must be in first normal form and each column in the record is not partially dependent which means fully dependent on its primary key. We follow some steps a table converting into a second normal form: ·          Group remove items in the another table ·          Assign the new table with a key i.e part of a whole composite key.   ·          All non-key attributes are fully dependent on the primary key. Example:- Suppose, a college can store the data of teachers and the subject name they teach. A teacher can teach more than one subject.   Teacher_id Subject_name Teacher age 1 C programming 38 1 SAD 38 2 JAVA 40 3 Math 40 3 JAVA 50 Candidate Keys : {teacher_id, subject_name} Non prime attri

University of Engineering & Technology Roorkee ALTERNATIVE KEY It is the set of an attribute in a table that uniquely identifies every tuple (row) in a table. In the alternative key, the set might not be selected primary key but still capable to be used as the primary key. Example:-  Student table         In the above example, the student table contains four columns.                                                    { Roll_no, Name, Age, Mob } Among In these columns, Roll_no is used as a primary key. Using roll number you can find uniquely student record in the table.  Same as mob and email are the alternative keys both columns contain unique identities.     FOREIGN KEY   It is representing the relationship between two or more tables with the help of attributes. Actually, a foreign key is an attribute (column) whose values are derived from the primary key or unique key of another table. Why we use foreign keys? Foreign the key is maintaining the data integrity an

KEYS IN RDBMS (Data Constraints) Many keys are available in the RDBMS, They allow finding the relation between columns and table that help identify a tuple (row) in a table. They help find a unique record from the table or database.      Keys are seven types in RDBMS Primary key Super key Alternate key Foreign key Candidate key Unique key Composite key                                                                     Fig 1.2 KEYS 1.       PRIMARY KEY A primary key is an attribute (columns) of a table or group of columns that uniquely identify each record in a table. This contains a unique value that’s no repetition for any row. It cannot be a null value. A table contains only one primary key. Example:- Roll_no Name class Father name 1 Rohit MCA Sohan pal 2 Priya MCA Jitendra 3 Ritika MCA Bhanu Singh

COLLEGE OF ENGINEERING ROORKEE

                                                                                    THIRD NORMAL FORM A relation is said to be in third normal form when all transitive dependencies are removed from the table or database that is no transitive dependency for the nonprime attributes. 3rd normal form is implementing after the 1 st and 2 nd NF. A general Example:- A, B, C are three columns in table           If C is belonging to B           If B is belonging to A           Then C is indirectly belonging to the A In above example, there are contains transitive dependency.   This problem can be solved using a foreign key that column id(students), id(Subject) must be placed in the third table.   BOYCE-CODD NORMAL FORM This form was developed by R.F. Boyce and E.F. Codd in 1974. It is an advanced version of the 3 rd NF (third normal form). It was found to be stricter than 3NF because every relation in BCNF is also in 3NF. A relation schema R is in BCNF if whenever a nontr

NORMALIZATION Normalization is a process of analysis and organizing data in the database. In this process, reduce the redundancy from relations. Normalization is used to eliminate undesirable features like create, insertion, update, and deletion. It follows some protocols to divide the larger table into the smaller table. Without Normalization      Data take extra memory in the database           Difficulty in updating and organizing the data           Data redundancy problem   TYPES OF NORMALIZATION First Normal form Second Normal form Third Normal form BCNF Fourth Normal form  FIRST NORMAL FORM When a table divided into row and column form that break down a table with all repeating group of data eliminated. Example:- Project number Project name Employee number Employee name Rate category P001 Green E001 Shyam A P001 Blue E