5 Replies - 1262 Views - Last Post: 17 December 2008 - 11:14 AM Rate Topic: -----

#1 BEGIN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 20-October 08

Database Design and reverse engineering

Posted 12 December 2008 - 02:42 PM

To all whom have a 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 project:

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Database Design and reverse engineering

#2 RodgerDJr  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 14-December 08

Re: Database Design and reverse engineering

Posted 15 December 2008 - 04:32 PM

Begin,

OK I am new to this form, but not new to database design. I only had one class on database design and that was 10 years ago, so I am probably not going to have the answer to a lot of the questions you posed because I am not even sure what they mean.

What is the issue you are having and let's do them one at a time. Let me see if I can get what you want to accomplish first and then we can move on from there.

Is your assignment to create and EMR for a hospital emergency department? Are you having problems creating the tables? Where are you having an issue with normalization. Do you have a list of tables and fields?

Let me know. I have developed many database for the medial industry.

Rodger
Was This Post Helpful? 0
  • +
  • -

#3 KeyboardKowboy  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 20
  • View blog
  • Posts: 142
  • Joined: 15-December 08

Re: Database Design and reverse engineering

Posted 15 December 2008 - 10:09 PM

Why are you so concerned with normalization in an approximated ER diagram? In the typical ERDs that I've been subject to, they lack attributes all together. With a lack of attributes, normalization is pretty much impossible.

You should be able to create an approximate ERD from the table names and key constraints given to you in the meta data.

Also, concerning your generalization question, mapping a subclass that has several superclasses... the sublcasses superclass is merely a subclass of its own superclass. Are any of these superclasses abstract, or are they all concrete?

P.S. On a side note... you mentioned

Quote

But I'm here to acquire a better understanding, to develop better software.

I'm glad people like you exist... I wish more (and especially in the professional realm) felt the same as you and I about quality and bettering ourselves and the tight knit engineering community in which we reside. Cheers to you.
Was This Post Helpful? 0
  • +
  • -

#4 BEGIN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 20-October 08

Re: Database Design and reverse engineering

Posted 16 December 2008 - 01:17 PM

Finally someone replied!!
I'm thankful that such people joined the forum .. I appreciate your help

KeyboardKowboy,

Do you mean by concrete that:
not all records in the super class should be in the sub class
if so then the term that I'm used to is Optional. Can we use it cuz it's easier for me to understand ^_^

what if all super classes were concrete then how can we map them?
Was This Post Helpful? 0
  • +
  • -

#5 KeyboardKowboy  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 20
  • View blog
  • Posts: 142
  • Joined: 15-December 08

Re: Database Design and reverse engineering

Posted 16 December 2008 - 02:15 PM

By concrete i mean that the class is able to be instantiated. An abstract superclass, on the other hand, cannot be instantiated and simply exists to aid in navigation visibility (inheritance) to its subclasses. In order to map these subclasses to the superclass, again refer to the key constraints that are in place. The foreign keys of the subclasses should be enough to allow for a proper map back to the superclass.
Was This Post Helpful? 1
  • +
  • -

#6 BEGIN  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 20-October 08

Re: Database Design and reverse engineering

Posted 17 December 2008 - 11:14 AM

Correct me if I'm wrong:

If we had the following tables:

Concrete super classes:
A( A1 , a2 , a3 )
B( B1 , b2 , b3 )
C ( C1 , c2 , c3)

Sub( a , b , c , s1, s2)

a references ( A )
b references ( B )
c references ( C )

Then can I say that this is the result of mapping a sub/super relationship where entities A, B, and C
are super classes of the entity Sub (generalization) ?

If that was true then what is the difference between the previous case and a turnery relationship??

best regards,
BEGIN
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1