1. Draw simple ERD for a bookstore database that includes the following entities:
Book
Author
Publisher
Define the appropriate relatioships between the entities
2. explain the concept of normalisation and list the first 3 normal forms. Apply normalisation to your ERD if necessary and explain the process
1.
Entities:
- Book
- Author
- Publisher
Relationships:
- Book is written by Author (one-to-many relationship)
- Book is published by Publisher (one-to-one relationship)
2.
Normalization is the process of organizing the data in a database in such a way that it reduces redundancy and dependency by dividing information into different tables. The goal is to ensure data integrity and efficiency in storage and retrieval of data.
The first three normal forms are:
1. First Normal Form (1NF): This form ensures that the table has a primary key and each column contains atomic values, i.e., there are no repeating groups or arrays. In the context of the provided ERD, each entity (Book, Author, Publisher) should have a distinct primary key such as Book_ID, Author_ID, Publisher_ID.
2. Second Normal Form (2NF): This form requires that all attributes in a table are fully functional dependent on the primary key. In the ERD, this means that there should be no partial dependencies where an attribute depends on only part of the primary key. For example, in the Book entity, if the Author's name is dependent on both the Book_ID and Author_ID, it needs to be separated into a separate Author table.
3. Third Normal Form (3NF): This form stipulates that there should be no transitive dependencies, meaning that non-key attributes should not depend on other non-key attributes. In the ERD, this would involve breaking down relationships where an attribute depends on another non-key attribute.
Applying normalization to the ERD involves identifying and removing any anomalies such as redundancy, partial dependency, and transitive dependency by splitting the entities into separate tables and establishing correct relationships between them. This process helps in maintaining data integrity and optimizing database performance.