To all fellow geeks and to whom have prior knowledge in database design, I'd like your consultation on several issues I've faced in my project. So first I'll give a brief explanation about the it:
We are supposed to develop the following software:
Semi Automated ER diagram generator
Input:
Meta data (data dictionary) of an existing relational database including:
•Tables' names
•Columns' names
•Constraints:
-Primary key
-Foreign key and the table that it references
Process:
Given the above data the system derives the relational schema of that database (logical model), then applies an algorithm that was based on the heuristics of mapping a conceptual model (ER diagram) to logical model (relational schema).
Output:
ER diagram that represents an approximation of the original ERD that the database was built upon it.
My Dilemma:
1. Normalization: the system we've suggested does not reverse the effect of normalization in the design process. This is based on several reasons:
a. One of the objectives of normalization is to get the optimal grouping of attributes and a solid database free of updates anomalies. So why should I denormalize??
b. In order to normalize we need determine the functional dependencies between attributes which we could not derive them from the metadata. In addition functional dependency is based on semantics that no other than a user expert can define.
2. Is it possible for an entity to be weak for two different entities? If yes, then what is the difference between mapping it and a M : N relationship.
3. If a weak entity is mapped to logical model, is it mandatory to put a determinant as a part of the primary key.
4. I've stumbled on some slides on the internet that explains the process of mapping ERD to logical model and it says that all weak relationships are 1 : M is it true?
5. Last but not least how can I map a sub class that has several super classes(generalization)
The reasons I mentioned above (a and b ) may lack proper scientific explanation, but that was I concluded from what I read and my humble knowledge. But I'm here to acquire a better understanding, to develop better software.
Thanxx for reading my long and tedious post…
I'm counting on you titans of database design around the globe.