Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme - Dr Binu V P

  Informal Design Guidelines for Relational Schema Before introducing functional dependencies and normalization , relational database design uses informal guidelines to judge whether a schema is good or bad . These guidelines help detect design problems early , such as redundancy, anomalies, NULLs, and spurious tuples. There are four main informal design guidelines . Guideline 1: Impart Clear Semantics to Attributes Meaning A relation schema should have clear and unambiguous meaning . Each relation should represent only one entity type or one relationship type . Why this matters Users must easily understand what each tuple represents Queries become easier and less error-prone Prevents mixing unrelated real-world concepts Good Design Example EMPLOYEE(Ename, Ssn, Bdate, Address, Dnumber) Each tuple = one employee DEPARTMENT(Dname, Dnumber, Dmgr_ssn) Each tuple = one department These relations are easy to explain and interpret. Bad Design Exam...

  Introduction to Normal Forms After introducing functional dependencies , we now move to one of the most important concepts in relational database design: Normal Forms . Normal forms provide a formal methodology for evaluating and improving relation schemas. They help database designers systematically analyze relations and refine them to eliminate redundancy and anomalies. Why Do We Need Normal Forms? In relational design, we often obtain relations in one of two ways: By designing a conceptual schema using models such as ER/EER and then mapping it into relations. By designing relations based on existing files, reports, or legacy systems. However, the resulting relations may contain: Redundant data Insertion anomalies Deletion anomalies Update anomalies To improve the quality of such schemas, we apply the normalization process . What Is Normalization? Normalization is a formal process introduced by Codd (1972) that: Analyzes relations using fu...

  Functional Dependencies (FDs) Why Functional Dependencies Are Introduced Earlier, we discussed  informal design guidelines  (like avoiding redundancy and anomalies). Now, we introduce a  formal tool  to: Analyze relational schemas precisely Detect redundancy problems Define  normal forms  (later in normalization) 👉 The  most important concept in relational schema design theory  is the  Functional Dependency (FD). Universal Relation Assumption To develop the theory formally: We assume the whole database is represented as a single: R = { A 1 , A 2 , . . . , A n } This is called the  Universal Relation Schema . ⚠️ Important: This is  only for theory We do  NOT actually store  the database as one big table It helps define dependencies mathematically Formal Definition of Functional Dependency Definition A  functional dependency  is a constraint between two sets of attributes. It is written as: X → Y Where: X ...

  First Normal Form (1NF) — Detailed Explanation 🔹 What Is First Normal Form (1NF)? First Normal Form (1NF) is a fundamental rule of the relational model. It requires that: Every attribute value must be atomic (indivisible) . Each attribute must contain only a single value from its domain. No multivalued , composite , or nested attributes are allowed. In simple terms: A relation in 1NF cannot contain sets, lists, or relations inside attributes. 🔹 Core Rule of 1NF A relation is in 1NF if: Condition Meaning Atomic values only                Each cell contains one value No repeating groups                No sets like {A, B, C} No composite attributes                No structured attributes inside one column No nested relations                No relation stored inside another relat...