sql server programming: Here is my DDL:


create table person(
UPN char(13) not null primary key
check (ucn between '0000000000000' and '9999999999999'),
personal_name varchar(40) not null,
sex char(1) not null check(sex in ('M', 'F')),
date_of_birth date not null
)

insert into person(UPN, personal_name, sex, date_of_birth)
values ('1502972840016', 'Mijatov Srdjan', 'M', '1972-02-15');
insert into person(UPN, personal_name, sex, date_of_birth)
values ('0902977840019', 'Mijatov Dragan', 'M', '1977-02-09');
insert into person(UPN, personal_name, sex, date_of_birth)
values ('0909972845025', 'Bata Eva', 'F', '1972-09-09');


In my country every person have uniqu personal number (UPN). ucn is
formated as DDMMYYYRRSNNN

DD - day of birth
MM - month of birth
YYY - last 3 digits of year of birth
RR - region of birth
S - sex (0 = Male, 5 = Female)
NNN - serial number

My first thinking is: UPN is natural key and model is at least in 2NF.

But, date_of_birth is functionally dependent on part of the key (first 7
digits of UPN are telling abaut date_of_birth), sex is functionally
dependent on part of the key (10. digit).

Similar problem is with ISBN and publishers.

I have two questions:

1. Is this model violating 2NF?
2. Is it better to CHECK atributes date_of_birth and sex to meet facts in
UPN or is better to CHECK UPN to meet facts stored in date_of_birth and
sex?


Srdjan Mijatov

[quoted text, click to view]

Since you are treating UPN as an atomic value there is no violation of 2NF.
This seems reasonable if UPN is an accepted standard.

[quoted text, click to view]

All the values are non-NULL and therefore have to be inserted simultaneously
so it doesn't make much difference. From a performance point-of-view 1
constraint is better than 2. From the user's point of view I would say that
they are most likely to mis-key the UPN (because it's a single long number)
so that is where I would be inclinded to highlight the error in the UI but
that has no bearing on the choice of your CHECK constraints.

Doesn't the UPN include a check digit that you can verify as well? If the
last three digits are just allocated serially then you can't validate
against keying errors.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

The table has a single column key which makes any partial key dependency
irrelevant. So you are correct in stating that the table is in 2NF.

However, in this case UPN, rather than being a pure natural key, can be
considered as an intelligent key, at least partially. A minor difference
between an intelligent key & a natural key is that an intelligent key
generally contains overloaded meanings while a natural key contains singular
meaning.

[quoted text, click to view]
digits of UPN are telling abaut date_of_birth), sex is functionally
dependent on part of the key (10. digit). <<

Not really; in a relational table ( i.e. a table in 1NF ), all columns have
scalar values. Thus, UPN is as scalar as any other value in your table.
Being an intelligent key does not make the values in the column non-scalar
and thus there is no such thing as "part of the key" as far as the logical
model is concerned. The meaning associated with the format or some part of
the key is user-assigned. So it has no relevance in determining functional
dependencies.

[quoted text, click to view]

As mentioned before, the table has a single column key and so partial key
dependencies are out of question. So 2NF is not violated.

[quoted text, click to view]
UPN or is better to CHECK UPN to meet facts stored in date_of_birth and sex?
<<

By definition an intelligent key has a meaning in itself, however in this
case UPN is partly intelligent and partly surrogate -- serial number being
unfamiliar & meaningless. However, the key has some meaning of values of
other attribute(s) embedded in it, it has to rely on such attribute values.
Thus, ignoring the serial number part, the value of the UPN follows the
value of date_of_birth and gender and not vice-versa.

So, it is more sensible to enforce a CHECK constraint on UPN to match the
desired values of the attributes date_of_birth and sex.

--
Anith

[quoted text, click to view]

No, this is called a vector code. It has parts, but it has meaning only
as a whole. ISO tire sizes are another example of a vector code. It
has three parts: <width i centimeters><material used in alpha
code><diameter in inches>.

If you can get a copy of my book DATA & DATABASES, it has a chapter on
the design of encoding schemes.

[quoted text, click to view]
facts in UPN or is better to CHECK UPN to meet facts stored in
date_of_birth and sex? <<

The CHECK() can stand by itself in the DDL; it does not have to go onto
any column.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]

I allready ordered ona few days ago when I found that book will be (or is?)
out of print.

[quoted text, click to view]

Good point. Thanks