What is Functional DependencyFunctional dependency in DBMS, as the name suggests is a relationship between attributes of a table dependent on each other. Introduced by E. F. Codd, it helps in preventing data redundancy and gets to know about bad designs.To understand the concept thoroughly, let us consider P is a relation with attributes A and B. Functional Dependency is represented by -> (arrow sign)Then the following will represent the functional dependency between attributes with an arrow sign −A -> BAbove suggests the following:ExampleThe following is an example that would make it easier to understand functional dependency −We have a ... Read More

The 5NF (Fifth Normal Form) is also known as project-join normal form. A relation is in Fifth Normal Form (5NF), if it is in 4NF, and won’t have lossless decomposition into smaller tables.You can also consider that a relation is in 5NF, if the candidate key implies every join dependency in it.ExampleThe below relation violates the Fifth Normal Form (5NF) of Normalization −EmpNameEmpSkillsEmpJob (Assigned Work)DavidJavaE145JohnJavaScriptE146JamiejQueryE146EmmaJavaE147The above relation can be decomposed into the following three tables; therefore, it is not in 5NF −EmpNameEmpSkillsDavidJavaJohnJavaScriptJamiejQueryEmmaJavaThe following is the relation that displays the jobs assigned to each employee −EmpNameEmpJobDavidE145JohnE146JamieE146EmmaE147Here is the skills that are ... Read More

A primary key having two or more attributes is called composite key. It is a combination of two or more columns.An example can be −Here our composite key is OrderID and ProductID −{OrderID, ProductID}Let us see another example −StudentIDStudentEnrollNoStudentMarksStudentPercentageS001072172257090S002072179049080S003072176644086Above, our composite keys are StudentID and StudentEnrollNo. The table has two attributes as primary key.Therefore, the Primary Key consisting of two or more attribute is called Composite Key.

Normalization removes data redundancy and update, insert and delete anomalies and gives you a normalized perfect database design that a database administrator love.To normalize a database table, follow the below given steps that highlights the role of normalization forms and its uses −First Normal Form (1NF)1 INF is useful in removing the data redundancy issue and anomalies of a database. All attributes in 1NF should have atomic domains.Second Normal Form (2NF)The Second Normal Form eliminates partial dependencies on primary keys.Third Normal Form (3NF)The Third Normal Form eliminates Transitive Functional Dependency.Fourth Normal Form (4NF)To be in 4NF, a relation should may ... Read More

What is Partial Dependency?Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.The 2nd Normal Form (2NF) eliminates the Partial Dependency.Let us see an example −ExampleStudentIDProjectNoStudentNameProjectNameS01199KatieGeo LocationS02120OllieCluster ExplorationIn the above table, we have partial dependency; let us see how −The prime key attributes are StudentID and ProjectNo, andStudentID =  Unique ID of the studentStudentName = Name of the studentProjectNo = Unique ID of the projectProjectName = Name of the projectAs stated, the non-prime attributes i.e. StudentName and ProjectName should be functionally dependent on part of a candidate key, to be Partial Dependent.The StudentName can be determined by StudentID, which makes the relation ... Read More

Referential Integrity Rule in DBMS is based on Primary key and Foreign Key. The Rule defines that a foreign key have a matching primary key. Reference from a table to another table should be valid.Referential Integrity Rule example −EMP_IDEMP_NAMEDEPT_IDDEPT_IDDEPT_NAMEDEPT_ZONEThe rule states that the DEPT_ID in the Employee table has a matching valid DEPT_ID in the Department table.To allow join, the referential integrity rule states that the Primary Key and Foreign Key have same data types.

Data Dictionary consists of database metadata. It has records about objects in the database.What Data Dictionary consists ofData Dictionary consists of the following information −Name of the tables in the databaseConstraints of a table i.e. keys, relationships, etc.Columns of the tables that related to each otherOwner of the tableLast accessed information of the objectLast updated information of the objectAn example of Data Dictionary can be personal details of a student −ExampleStudent_IDStudent_NameStudent_AddressStudent_CityThe following is the data dictionary for the above fields −Types of Data DictionaryHere are the two types of data dictionary −Active Data DictionaryThe DBMS software manages the active data ... Read More

Entity in DBMS can be a real-world object with an existence. To identify entity from a given problem, follow the below given tips −Under the problem description, try to find the entity.Search for nouns, like Teacher, Doctor, etc.Classify nouns to get a wider picture about the entities.Read the problem description repeatedly.Entities are like Persons, Students, Teachers, Courses.Entities has attributes, that properties describing it, for example, for Professor entity, the attributes are  Professor_Name, Professor_Address, Professor_Salary, etcFor example, Problem DescriptionHospital has doctors and patients. Patients visit the hospital to get a consultation from the doctor. Doctor may suggest tests to examine the condition ... Read More

Database Design Methodologies has phases to guide the designer for assistance. The Methodology has a structured approach to help in the design process.The following are the phases/ models −Conceptual PhaseThe Conceptual phase lets you know the entities and the relation between them. It describes the conceptual schema. The entities & relations are defined here.Logical PhaseLogical data model provides details about the data to the physical phase. The physical process gives ER Diagram, data dictionary, schema, etc that acts as a source for the physical design process.Physical PhaseThe physical database design allows the designer to decide on how the database will ... Read More

A good database design has many benefits and is a goal to achieve for every DBA −Easy Retrieval of InformationIf the design is developed properly, then it would be easier to retrieve information. Correct design means the tables, constraints, and relationships created are flawless.Easier ModificationChanges that you make to the value of a given field will not adversely affect the values of other fields within the table.Easy to MaintainThe database structure should be easy to maintain. The design is perfect if changes in one field is not affecting changes in another field.InformationWith a good design, you can enhance the quality ... Read More