Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!cs.utexas.edu!uunet!cimshop!davidm From: cimshop!davidm@uunet.UU.NET (David S. Masterson) Newsgroups: comp.databases Subject: Re: Deducing entity relationships from relational schema Message-ID: Date: 20 May 91 18:02:13 GMT References: Sender: davidm@cimshop.UUCP Distribution: comp Organization: Consilium Inc., Mountain View, California Lines: 40 In-reply-to: sra@ecs.soton.ac.uk's message of 7 May 91 14:23:40 GMT >>>>> On 7 May 91 14:23:40 GMT, sra@ecs.soton.ac.uk (Stephen Adams) said: Stephen> My problem is this: I have a relational database. This Stephen> consists of several relations (or files or tables if you Stephen> want) and some indexes. An index may be unique, that is no Stephen> two items in a relation may have the same values for all of Stephen> the index fields. Stephen> I want to use this information to recover the design Stephen> information. Its not too difficult to get an approximation, but, in the end, it depends on your common sense and knowledge about the situation that the database is being used in. 1. Consider each table to be an entity. 2. Determine the primary key of each entity. 3. Determine foreign key relationships between entities. 4. Any table composed of only foreign keys is probably not an entity, but a relationship of other entities. 5. Any table whose primary key is also a foreign key is probably part of the entity represented by the foreign table. 6. A primary key consisting of only foreign keys is probably a relationship, but its situation dependent (for instance, marriage). 7. Iterate over 2-6 until you feel comfortable with the outcome. BTW, a primary key is that which uniquely identifies a row in a table and a foreign key is that which relates a record in one table to a record in another table (usually, the foreign key is a primary key of another table). Also, in six, the primary key of a marriage is probably the names of the two people involved, so you might think of it as a relationship. On the other hand, you can discuss a marriage in terms of when and where it occurred (etc.), so you might think of it as an entity. This is where a DBA makes his money. -- ==================================================================== David Masterson Consilium, Inc. (415) 691-6311 640 Clyde Ct. uunet!cimshop!davidm Mtn. View, CA 94043 ==================================================================== "If someone thinks they know what I said, then I didn't say it!"