Re: Fails Relational, Fails Third Normal Form

In article <20150215200758.4b07bde9.jklowden_at_speakeasy.net>,  "James K. Lowden" <jklowden_at_speakeasy.net> wrote:

> The rule is that a security is represented by a pair of rows, one in
> Securities and one in the subtype table. There *must* be two rows,
> and only two rows, and the subtype table must be the one indicated by
> Securities.Type.

I like to think of SQL's insert, update and delete as the (quite powerful) assembly-level instructions of a data language. If you can design a model in which a single insert, a single update or a single delete never causes any integrity violation, well, go with them. But in many (most?) cases, a well-designed database requires modifying more than one fact at a time to preserve consistency. In practice, that means that single inserts/updates/deletes are too low-level wrt the semantics of the database. Hence, their execution must be forbidden (by revoking the corresponding privileges in SQL from the users accessing the database), and more complex "primitives" must be defined. These should be given along with the logical schema and implemented as user-defined functions (assuming that such functions are run atomically), whose execution is granted to the above-mentioned users instead of the raw SQL instructions.

If the *only* modification permitted on R(A,B) and S(A,C) is through a function update(a,b,c) that inserts (a,b) in R and (a,c) in S, you don't need any explicit foreign key clause in your SQL tables for the purpose of preserving referential integrity.

Nicola

• news://freenews.netfront.net/ - complaints: news_at_netfront.net ---