REVISION Normalisation and ERD Example Normalisation A publishing

REVISION Normalisation and ERD Example

Normalisation A publishing company requires your assistance with the development of a database after their system has crashed. You manage to retrieve some information from a spreadsheet that contains the authors and the books that they have published in the last few years. The spreadsheet contains information about the authors, the editors that they worked with, the genre of each book published and the names of their publications. Each book published can be uniquely identified by the author that wrote it, the genre of the publication and the year of the publication. You have turned the spreadsheet into (1 NF) which resulted in the following schema: 1 NF(author_id, author_name, author_address, author_ranking, editor_name, editor_ranking, editor_assitant, genre_code, genre_name, genre category, publication_year, publication_edition, book_name).

Questions 1. Write down the names of all the columns that are fully dependent on the composite primary key 2. Write down all PARTIAL dependencies 3. Write down all TRANSITIVE dependencies 4. Remove all PARTIAL dependencies, and write down the resulting table schemas 5. Remove all TRANSITIVE dependencies, and write down the resulting table schemas

Answers 1. Write down the names of all the columns that are fully dependent on the composite primary key author_id, genre_code, publication_year -> book_name 2. Write down all PARTIAL dependencies author_id -> author_name, author_address, author_ranking, editor_name, editor_ranking, editor_assistant genre_code-> genre_name, genre_category publication_year-> publication_edition 3. Write down all TRANSITIVE dependencies editor_name-> editor_ranking, editor_assistant 4. Remove all PARTIAL dependencies, and write down the resulting table schemas AUTHOR (author_id, author_name, author_address, author_ranking, editor_name, editor_ranking, editor_assistant) GENRE (genre_code, genre_name, genre_category) YEAR (publication_year, publication_edition) BOOK (author_id, genre_code, publication_year, book_name) 5. Remove all TRANSITIVE dependencies, and write down the resulting table schemas AUTHOR (author_id, author_name, author_address, author_ranking, editor_name) EDITOR (editor_name, editor_ranking, editor_assistant)

Example ERD Draw a complete Entity Relationship Diagram of the following business rules, using UML notation. Indicate Entities, key attributes, relationships (name and direction), and multiplicities. Use bridge entities to resolve *: * relationships. Note: Chen notation = zero marks. Rules: A DEBTOR opens an account at a BRANCH of Jazz Music Shops. Each BRANCH can have many DEBTORS, but possibly none. A DEBTOR can buy ITEMs from Jazz Music Shops on account and pay for them later. Every time a DEBTOR buys ITEMs from the shop, a TRANSACTION is created. A DEBTOR can have many TRANSCATIONS, at least one. A TRANSACTION belongs to only one DEBTOR. A TRANSACTION may contain many ITEMS, at least one. An ITEM can belong to many TRANSACTIONS. ITEMS are grouped by CATEGORY. An ITEM belongs to only one CATEGORY. A CATEGORY can contain many ITEMS.