What is the process of transforming an entity in an ER diagram to a logical schema?
How are relationships in an ER diagram transformed into a logical schema?
Why is normalization needed in a database schema?
What is the difference between partial and transitive dependency?
Describe the conditions for achieving third normal form (3NF).
Answers and Descriptions:
Answer: Transforming an entity involves creating a table with attributes as columns and the entity identifier as the primary key.
Description: For example, a Student entity with attributes StudentID, Name, and Age becomes a table: Students(StudentID, Name, Age) with StudentID as the primary key.Answer: Relationships are transformed by including foreign keys in related tables or creating a new table for many-to-many relationships.
Description: For a many-to-many “Enrolls” relationship between Students and Courses, a new table Enrollments(StudentID, CourseID) is created with foreign keys referencing both tables.Answer: Normalization is needed to eliminate redundancies and anomalies (insert, update, delete) to improve database performance and integrity.
Description: Redundant data can lead to inconsistencies, like duplicate entries causing update errors. Normalization organizes data to minimize these issues.Answer: Partial dependency occurs when an attribute depends on only part of a composite primary key, while transitive dependency occurs when a non-key attribute depends on another non-key attribute.
Description: Partial dependency is addressed in 2NF, while transitive dependency is resolved in 3NF, ensuring a more efficient schema.Answer: For 3NF, a table must be in 2NF, and no non-key attribute should depend on another non-key attribute (no transitive dependency).
Description: For example, in a table with StudentID, CourseID, and Instructor, if Instructor depends on CourseID (not StudentID), it’s moved to a separate table to achieve 3NF.