I'm trying to understand rules of relational model as originally defined by Edgar Codd in 1970. Specifically I'm interested whether referential integrity is part of his relational model or not. I'll try to demonstrate on following example (just to make this question pretty): Customers +------+------------ | Name | Address |------+----...
I have been putting off developing this part of my app for sometime purely because I want to do this in a circular way but get the feeling its a bad idea from what I remember my lecturers telling me back in school. I have a design for an order system, ignoring the everything that doesn't pertain to this example I'm left with: CreditCa...
I'm reading CJ Date's SQL and Relational Theory: How to Write Accurate SQL Code, and he makes the case that positional queries are bad for example, this INSERT: INSERT INTO t VALUES (1, 2, 3) Instead, you should use attribute-based queries like this: INSERT INTO t (one, two, three) VALUES (1, 2, 3) Now, I understand that the first...
Is there a generalized procedure or algorithm for transforming a SQL subquery into a join, or vice versa? That is, is there a set of typographic operations that can be applied to a syntactically correct SQL query statement containing a subquery that results in a functionally equivalent statement without a subquery? If so, what are they (...
Can a Superkey include things that aren't part of the Primary key? ...
I'm looking for mnemonics to help me with databases, the relational model and transaction theory. For example I learned "ACID" to help me remember the properties of a transaction: atomicity, consistency, isolation, and durability. What others are out there? ...
My understanding about Codd's concept of "safe queries" was created to ensure that a query would always terminate. One key ability of a Turing machine is that it can work on infinite calculations (and thus isn't guaranteed to terminate). If the safe query restriction were removed, would relational calculus be Turing-complete since that...
Before I describe my problem, I'd like to get a couple things out of the way: I'm an experienced (though not expert) database designer. I believe I have a good grasp of the relational model. I don't have such a firm understanding of the relational model that I know exactly what to do in every situation. I'm still learning. Let's say ...
Almost every table in every database I've seen in my 7 years of development experience has an auto-incrementing primary key. Why is this? If I have a table of U.S. states where each state where each state must have a unique name, what's the use of an auto-incrementing primary key? Why not just use the state name as the primary key? Seems...
We have three entities called Product, ProductType, and ProductCategory. Let's pretend we have three kinds of ProductType: Book, Music, and Video. We have three different ProductCategory's for Book: Fiction, Novel, Technical. Three different ProductCategory's for Music: Rock, Jazz, Pop. And we have three different ProductCategory'...