1,865 reputation
713
bio website
location
age
visits member for 3 years, 7 months
seen 19 mins ago

Nov
7
comment Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?
There is nothing purely "philosophical" about the need to identify real things accurately and to guarantee the uniqueness of meaningful data through key constraints. Natural keys are a very practical necessity.
Mar
21
comment Confusion with Foreign Keys
There's no reason in principle why primary key attributes shouldn't be foreign keys.
Mar
5
comment Column suitable for natural key?
@mircale, my point was that the reason for key constraints is to guarantee that infomration is not duplicated. If you don't enforce a natural key on some attribute then the values of that attribute may be duplicated on multiple tuples with different surrogate key values. That is apparently not what is wanted here.
Jan
4
comment Primary Key Question
"if I have a key of FirstName, LastName, Email on my Student table, and then have to repeat that information in my Classes table then I'm duplicating data in that table and breaking one of the normal forms. Is that not correct?" That is not correct. In any case it's a poor example because it seems highly unlikely that firstname and lastname would be part of a key for students in any real or practical sense.
Nov
1
comment If all variables are a subset of the candidate key, is the database design 5NF?
Damir, you are right and my answer is wrong. I don't know what I was thinking of - BCNF maybe. Thanks anyway.
Oct
31
comment Is normalization process needed?
Perhaps it would be better to say that we should normalize the logical structure of the database. The phrase "logical model" is often (mis)understood to mean merely a preliminary design for a database rather than something actually existing in a database system. The important point surely is that what you implement ought to be normalized, not just what you sketch on paper :)
Oct
15
comment SQL Server transaction log file size and auto grow
When did you last back up the log? That is what determines the lifetime of the log. You should back up the log more often than you take full backups. Check that your log backups are working as you expect.
Oct
15
comment SQL Server transaction log file size and auto grow
SQL Server works similarly to Oracle. If you back up the log the space will be reused. If you have already backed up the log then create a new log file to make more space.
Jun
16
comment how to convert a table into second normal form
Without any explanation of what dependencies the table is supposed to satisfy all anyone can do is make guesses based on the attribute names.
Dec
4
comment The four properties of a primary key
Perhaps you were taught wrong. Those four conditions are neither necessary nor sufficient to make a key. Keys must be irreducibly unique (a minimal superkey) and non-nullable. Non-partial dependence is a requirement of 2nd Normal Form but not a fundamental requirement of keys. Indexing also has nothing to do with whether a set of attributes qualify as a key or not.
Aug
29
comment Why should a CTE start with a semi-colon?
"Why should a CTE start with a semi-colon?" In Standard SQL every statement is supposed to end with a semi-colon. The problem is that T-SQL only enforces that rule in a few places and one of those is for any statement that comes before a CTE. If there is no statement before WITH then you don't need ;.
Jul
18
comment Primary key should be chosen so as attributes never change?
You can't have read what I wrote. I was not "praising" anything. I've certainly changed surrogate key values plenty of times. In fact I'm surprised if you haven't encountered the need to update surrogate keys. Anyway, the question is about keys in general, not just surrogates. If you meant your answer to apply only to surrogate keys then you ought to say so.
Jul
18
comment Primary key should be chosen so as attributes never change?
What is the relevance of saying the identity of an "entity" cannot change? By "entity" I understand you to mean "the thing about which information is recorded in the table". In practice the identifying attributes of things do sometimes change. In any case, the process and effect of changing keys is exactly the same whether you say a thing is the same entity afterwards or not. Enforcing immutability of keys is practically impossible in very many cases and unlikely to be helpful.
Jun
29
comment Is database normalization dead?
@Nupul, It seems very unlikely that the specified example of a surrogate key and a key consisting of an email address would be in violation of 2NF or BCNF. 2NF is violated only where there is a partial-key dependency.
Apr
18
comment What normal form does a surrogate key violate?
5NF is "obscure"?! More to the point, the "nothing but the key" cliché isn't a very precise description of 3NF. 3NF is concerned equally with all the candidate keys of a relation, not just one. A surrogate key won't violate 3NF unless it gives rise to some partial key dependency. Most people will understand "surrogate" to mean a simple (non-composite) key consisting only of arbitrary values, so a partial key dependency involving a surrogate is extremely unlikely.
Apr
16
comment Using an RDBMS for querying tens of Terabytes of time-series data?
Exadata, Netezza, Greenplum, etc. These are some of the popular SQL DBMSs for handling 10s or 100s of Terabytes of data.
Feb
20
comment Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
One nullable foreign key is one too many.
Jan
25
comment Shrinking log file
If your log files are growing regularly then shrinking like that is a massively counter-productive thing to do. Don't do it. By doing this you are invalidating the log backup sequence. My guess is you are using FULL recovery when you might be better off using SIMPLE.
Jan
25
comment Is an anomaly free relation necessarily normalized?
If "all normal forms" includes DKNF then the answer is no. A relation in 6NF does not have undesirable update, insertion or deletion anomalies in the usual sense but it still may not satisfy DKNF. On the other hand DKNF is not very important and is frequently irrelevant or unachievable.
Jan
9
comment SQL Server : primary keys advice to my whitepaper needed
It is premature optimization to allow the choice of clustered index to dictate the primary key of your tables. Very premature and very unnecessary!