Extensive Data Dictionary and ER Diagram

This is a question for an assignment. Can somebody please help me?

Criteria

1. Extensive data dictionary that contains appropriate data items and all relevant details of each data item.
2. Extensive ER diagram that contains appropriate tables and constraints used. All data items in the data dictionary are reflected in the tables.

Given Data Dictionaries

Finally my question

1. Should I create an extensive data dictionary when there is already a data dictionary?
2. Is this correct what I did below?

You'll probably understand that I cannot sovlve your assignment for you: one day you might write mission critical system for the plane or medical device I'll use and I want to be sure that you'll have all the skills needed ;-)

But here some hints to guide you:

1. The data dictionary provided is not as extensive as it should. So I guess you have to fill the missing cells. For example:

   • if every employee belongs to a department, do you think that Employee.Department_id is nullable ?
   • if several employees may belong to the same department department, do you think that Employee.Department_id is unique ?
   • What with the descriptions and examples?

2. Your second ERD uses Chen notation. These are excellent to show Entities, Relationships, Attributes. They are not meant to replicate tables. While it seems correct at first sight, some improvements are needed:

   • the cardinalities between the entities and relationships are definitively missing.
   • Primary key attributes should be underlined.
   • Foreign keys are usually not shown, since they are deduced from the relationships and cardinality.

3. Your first ERD uses Barker's notation. While it also shows entities, relationships and attributes, it is meant to map entities and attributes to tables, and keys. In this regard, it's better in view of your assignment requirement to show all the attributes of the dictionary. Some improvements are required:

   • Primary keys are well identified. But there are problems with the foreign keys: put a FK only in front of the columns identified as foreign key in data dictionary.
   • between the entities, you should use the right symbols to reflect the cadinality (simple bar on the side where one item corresponds, crowfoot bars on the side where several items corrspond, and o on the side where there could be no item)
   • While it is possible to simply show the relationship between entities by connecting them to the table header or the bottom line, in a detailed diagram showing all the field, it is better to graphically connect the boexes at the level of the primary and foreign keys that implement the relationship.