Database Design

[Harikrishna-Srinivasan]

A good design, always needs a good compromise

1. Entity Design

• Avoid Redundancy: Each entity should represent a single, unique concept—don’t duplicate data in multiple places.
• Atomic Attributes: Ensure all attributes are atomic. Avoid storing multiple values in one attribute (e.g., separate phone numbers into multiple fields or rows).
• Naming: Use clear, meaningful names for entities and attributes.

2. Relationships

• 1:1 (One-to-One): Rarely needed; use when two entities are tightly coupled. If necessary, place the foreign key in one of the tables.
• 1:N (One-to-Many): The "many" side holds the foreign key pointing to the "one" side.
  • Example: In a Customer-Order relationship, add CustomerID in the Order table.
• M:N (Many-to-Many): Create a junction table with foreign keys from both entities.
  • Example: Students and Courses → Create StudentCourse table with StudentID and CourseID.

3. Normalizations

1NF (First Normal Form):

• Ensure all attributes contain atomic values (no lists, sets, or nested data).
• Eliminate duplicate rows to ensure data integrity.

2NF (Second Normal Form):

• Satisfy 1NF.
• Remove partial dependencies. Non-key attributes must depend on the entire primary key (for composite keys).
  • Example: In a StudentCourse table, StudentName should not depend only on StudentID.

3NF (Third Normal Form):

• Satisfy 2NF.
• Eliminate transitive dependencies. Non-key attributes should only depend on the primary key, not on other non-key attributes.
  • Example: If a Student table has both StudentID and AdvisorName, the AdvisorName should be moved to an Advisors table.

BCNF (Boyce-Codd Normal Form):

• Satisfy 3NF.
• Ensure that every determinant (an attribute that determines another attribute) is a superkey.
  • Example: If StudentID determines StudentName, ensure StudentID is a superkey (if not, split the table).

4NF (Fourth Normal Form):

• Satisfy BCNF.
• Eliminate multi-valued dependencies. Each attribute should depend on the key, not on other attributes that aren’t keys.
  • Example: If a Course entity holds multiple values like Instructor and Location, break this into separate tables to avoid multi-valued dependencies.

5NF (Fifth Normal Form):

• Satisfy 4NF.
• Eliminate join dependencies. A table should not be split if it can be reconstructed without losing data. If decomposing causes data loss, reconsider the decomposition.
  • Example: In a Course-Student-Instructor relationship, break it into three tables: StudentCourse, CourseInstructor, and InstructorStudent to avoid unnecessary join dependencies.

4. Foreign Keys & Referential Integrity

• Foreign keys ensure valid relationships between entities.
• Use cascade updates/deletes if necessary to maintain data consistency.

5. Decomposition Rules

• Decompose tables only if it helps reduce redundancy or improves efficiency.
• Lossless decomposition: Any decomposition must be lossless (able to recreate original data from decomposed tables).
• Dependency preservation: Ensure that all functional dependencies are preserved after decomposition.

6 NF and other Normal Forms are not discussed here, as they are not of (any) higher significance.

Follow these Normalization Forms, and try to design 4NF or 5NF form of Entity Sets. Every Entity Set must at least satisfy 4NF property. Then only it is considered good design.