Search results

To be fair to your IT department, it appears that their concerns are actually about the Jet/ACE database and not about Access as an application. There are legitimate reasons why it might be good advice to avoid a Jet/ACE database and use SQL Server instead. The IT Department's concern is...

No. It's quite common to have attributes subject to several different relationships or to have multiple relationships between the same two entities. Unfortunately the "relationships" window in Access makes an awful mess of displaying models of that kind. It insists on displaying a copy...

I'm very glad if you do understand that empty set and NULL are different things. If you simply choose to use them in similar ways then I have nothing much to say about that. Analogously, you might choose to interpret 1s as meaning the same as 0s, or TRUEs as meaning the same as FALSE, and then...

It is of enormous practical importance! It's the difference between getting results that are right rather than wrong and it is just as fundamental and important as 0<1 or 2+2=4. If your DBMS ever returned a null when it should return an empty set then your DBMS would be broken. I'm actually...

It's not a disagreement about terms at all. It's about two fundamentally different concepts: one concept is NULL (in the SQL database sense or in the sense of Codd's "null marks"); the other concept is the empty set, which is not a NULL although it is sometimes referred to by mathematicians as...

You can do that as a table validation rule (AKA check constraint): [FirstName]<>[LastName] It does seem a bit unlikely to be a useful rule though. I expect there are people who genuinely have the same name for first and last name.

What do you intend to use as natural keys in your tables? Tables in general need some kind of user-visible identifier (AKA "natural" keys, "business" keys or "domain" keys) that allows users properly to identify information and relate it to the real things the database is supposed to be...

Hi jlstark1, It is quite possibly going too far to have tables for Roadeo and Colours. Those appear to be things that could just be single attributes in other tables (date of the event, colour of the group). If you had other attributes of Roadeos or Colours then that would be one good reason to...

There's no reason to call it paradoxical if such a constraint reflects reality. Perhaps my example isn't all that likely as a real business rule but the apparent "problem" isn't with the rule itself but is just a consequence of a technical limitation. Database software based on the SQL model...

You shouldn't create a table just because some values are repeated on multiple rows. That would probably be a bad idea, or the very least an entirely unhelpful rule of thumb. A sensible basis for helping you decide what tables to create are the principles of Boyce-Codd Normal Form and 5th...

Exactly but since there is more than one way to interpret what was meant by the term "circular" in the original question, to make things clearer it ought to help if we answer by using less ambiguous words. The reason why the data management profession uses terms like "join dependency" is because...

Possibly you missed the point of the ER diagram I posted. It is a "circular" relationship in the sense that every branch must have a manager and every employee must be assigned to a branch. So the dependency between branch and employee works "both ways at once". This is shown in my diagram...

Your suggestion wouldn't enforce the business rules defined by the ER diagram: that every Branch has exactly one Employee managing it and that every Employee is assigned to at least one Branch, i.e. a "circular" dependency. Of course you are right that you would probably have to implement it by...

Here's one: Unfortunately CJ_London is right. Usually there is some aspect of such a relationship that cannot feasibly be enforced between tables using database constraints (whether by referential integrity constraints or otherwise). This is because most DBMSs evaluate constraints on a...

Example: In this schema, employees can optionally belong to a pension scheme. The attributes for pension roll number, type and contributions apply if and only if an employee belongs to a pension scheme. This is the design required to satisfy BCNF / 5NF if {EmployeeNum} ->...

You propose using nulls in name columns even when the full name is applicable, known and complete? That sounds like a really bad idea to me. It illustrates what I said before: nulls get used for so many different things that even if the intended meaning of a null is documented in each case...

It is certainly possible and there are many successful databases designed without nulls. That does not mean using some value as a default or "surrogate null"; it simply means taking the fact-based modelling approach (e.g. see Halpin/Nijssen). Each table models a set of propositions which are...

It appears that I may have misread / misinterpreted the question here. The question is posted in the database design forum and I read it as a question about database design, i.e.: whether a database designer should permit nulls in tables and why it might not be a good idea to do so. That was the...

Wazniw, I have to agree with Robert that it is a common enough requirement to need to assign more than one kind of identifier to things in a database table. Nothing illogical in that and it certainly is not the case that relational databases don't allow it. If your DBMS supports it then...

You just need to put ORDER BY in your queries. There is no "default" sort order and the only way to be sure is to specify the columns in the ORDER BY clause. If you don't then SQL Server attempts to choose the fastest access path to satisfy the unordered query. That may mean the data is...