Hi,
I want to maintain fields such as surname, middle name, firstName and other name of shareholders, but i'm confused to modify my earlier table, which has the following coloumns:

tblshareholders[shareholderID*, GroupID, ShortName, FullName, Address]

the problem is that shareholders either be an Individual or an institution (an organization), however I would like to maintain the individuals Surname, Middle Name, First Name and Other Name as well, but how am I gonna insert the values for an Institution?

Assume that there is an individual named Micheal Van stumm which is his full name. the table updates him as with the following values.


ShareholderID=1,
GroupID=1,
ShortName = "M.V. Stumm",
FullName = "Micheal Van Stumm",
Address = "USA".

if the shareholder is an institution named as "HSBC Emerging Markets Pvt Ltd", then the table updated it with the following values:


ShareholderID=2,
GroupID=2,
ShortName = "HSBC Emerging Markets Pvt Ltd",
FullName = "HSBC Emerging Markets Pvt Ltd",
Address = "UK".

Is there anyone who could give me a hint to create/modify this table. the table already has more than 500 records.

awaiting anyone's response in anticipation.

Thanks in advance.

Hifni

You can add a column Shareholder_Type to the table, with values 'P' for Person or I for Institution (for example). Then you can either allow the new name columns to be null when the Shareholder is an Institution (use a check constraint), or you can put them in a separate table with a 1:1 correspondence to the Shareholder table, but only populated for people - e.g.

create table person_shareholder
( shareholderID references shareholders primary key
, surname ...
, middle_name ...
, first_name ...
, other_name ...
);

Tony wrote:


The second suggestion is better because the first one violates normalization rules. The name column depends on a non-key column (shareholder_type).

Of course you could make shareholder_type part of the key.

Ravi

I don't think that's quite right. The name still depends on the ShareholderID and nothing but the shareholderID - i.e. given just a value for ShareholderID, we always know the name. It is just inapplicable for some shareholders.

It is not relationally pure, because it uses nulls, which are forbidden in a true relational database - but SQL is not trully relational and loves nulls!

In a SQL DBMS (like Oracle for example) I would probably choose the single table for simplicity.

Hi Tony,

Here is the relevant portion of your original message:

The value of the name column can be either null or not null. If it is an institution, then it can be null, else it must be present.

Clearly, the very presence of a value in the name column depends upon the shareholder_type. Therefore, the normalization rule that a column must depend on the whole key, is violated. It most definitely is not in 3NF.

If shareholder_type becomes a part of the key, then the uniqueness of shareholder_id can not be guaranteed.

Unfortunately, there is no elegant solution to this problem.

For super types and sub-types, it would be nice if we could treat the creation of the super_type and sub_type as a single transaction, just like C.J. Date suggests in some of his writings.


Ravi

Hi Tony,


On rereading your mail, I can understand your argument.

But it still bothers me that the presence of a value in the name column is dependent on a non-key column enforced using a check constraint. I am still not convinced that it is in 3NF.

What do you think about the following structure?

Shareholder: (ShareholderID, ...)
Institution: (Institution_id, location, ...)
[Instituion_id references shareholder]
Person: (person_Id, name, ...)
[Person_id references shareholder]

Here, too, it is not so easy to enforce the 1 to 1 mutually exclusive relationship between shareholder and all its sub-types.

As C.J. Date suggests somewhere, if we could ensure that whenever a super-type is created, then exactly one of its sub-types is also created, then it is easy to enforce.

Alternatively, we could "hide" the shareholder table, and use a trigger to ensure that whenever a new sub-type is created, then a new shareholder_id gets created first. But this approach requires that we have triggers on all sub-type tables. That is too much work for me, and can lead to errors.

Could we think of a DBMS that lets you specify super-types and their sub-types and whether the association is exclusive or not, and then whenever you create a super-type, it insists that at least one sub-type must be created? All this specification should be declarative, not through SQL + code.


Ravi

Yes, it would be good to be able to enforce such rules declaratively. The constraints available in SQL DBMSs such as Oracle are very limited in power. In theory it should be possible to create constraints like (not true syntax):
Such a constraint would have to be deferred until the end of the transaction, of course (since it will not be true during the insert of the supertype row).

The SQL standard allows for constraints along these lines (the syntax is probably different), but as far as I know no vendor has implemented them.

The constraint I came up with above is just one of many ways to achieve the same end; since subtypes are used quite often it might make sense to have a shorthand for it, in the same way that foreign key constraints are a shorthand for a check that could be done in SQL.

Yes, I was thinking more in terms of the shorthand notation.

Like the ability to declare athat an entity is a super-type and also that it is mutually exclusive.

Something like:

create table super_t (col1 ...) as super_type with mutually exclusive sub_types;

create table sub_a (cola ...) as sub_type of super_t;

create table sub_b (cola ...) as sub_type of super_t;

...


With this, the usage is completely declarative. A DBMS may enforce it any way it wants.

The advantage of this notation is that whenever a new sub-type is added, there is no need to change any underlying SQL.


Additionally, the tools that give you information about the tables (like SQL*Plus) must be able to spit out this information. That is, the "describe <table>" coomand must show the super_type/sub_type info.

PostgreSQL already has a very meaningful describe functionality. Its "\dt <table>" shows not just the table's columns, but also the constraints and the indexes on the table.

Ravi

Hi,
According to some of your kind suggestions, the shareholder table breaks for 2 tables. However there is another problem here. I've created an application to maintain shareholder List's sent by particular companies where my boss have invested in the security issued by that particular company. There is a form in my application where the received shareholder List is Entered to the following Master-Detail Tables:

tbl_Lists(ListID*, Company_Name, Security_Name, Issued_Date, Total_Shareholdings)
tbl_ShareBalance(ListID*, ShareholderID*, Share_Quantity)

tbl_Lists is the Master and tbl_Sharebalance is the detail.

I've place a lookupfield to tbl_Sharebalance's ShareholderID field to get the respective ShortName of the tbl_Shareholders. So the user who enters the list doesn't need to go for 2 tables to get the shareholder. All Shareholders are listed in the ShortName field (Persons and Institutions).

Now if I'm to break the tbl_Shareholders(shareholderID*, ShortName, FullName, Address) to 2 tables then the problem appears to come from the lookupfield for Shareholder's Name. For this I suggested to do a structure like this:

tbl_Shareholders[shareholderid*, GroupID, Shareholder_Type, Surname, Initials, Title, FullName, Address]

if the Shareholder is a person then the Surname, Initials and Title field would be filled and leaving no nulls to any fields mentioned above. However if the shareholder is an Instituiton then the Surname field and the Initials field would be null. I could add the value "M/S" to the Title field. I could do to concatnate the values of Surname and Inititials field to fill up the FullName Value.

How about that?

Thanx,

Hifni

N.B: Shareholder List's is issued for a certain period, indicating the top 100 or 50 shareholders who have held the largest securities of a particular company. Securities may be ordinary shares, non voting shares and debentures etc.