Hi,

I am taking an introduction to operating systems class and we have to answer a question that I am not sure of the answer to. Hopefully someone here can help!

The question is "Can a database contain two identical records without a negative effect on the integrity of the database? Why or WHy not? "

Any help or opinions would be greatly appreciated. Thanks.

The short answer is no. First, if you have dupicate records there would be confusion as to which one you're refering to in an any statement that modifies the database. Second, but related, duplicate records would allow for the possibility that one record would be updated, but the other would be unaffected. This would mean you have inconsistent data, which in a database is a Bad Thing.

I disagree.

I disagree.
Ok, I'm willing to admit I might be looking at this the wrong way, but you're going to have to provide some specifics. How else can anyone understand your disagreement?

I agree with the disagree(r).

insert into diet_log (person_id, calories) values (123, 576);
insert into diet_log (person_id, calories) values (123, 576);
insert into diet_log (person_id, calories) values (123, 576);

There you go, three identical records. Had you been keeping a log of how many Big Mac's person id 123 had consumed, this data is perfectly valid.

(Incidentally, apparently Big Mac's get over half their calories from fat. Bad bad bad! Update, the official McDonald's site lists the cal count as 560, 270 from fat)

The data may not be all that useful; such a table would likely have a datetime column as well, but that's not relevant to the question posed.

They want you to say "no" - because the relational database should store no duplicate records - in theory that is. In real life when dealing with large amounts of data you will sometimes duplicate some stuff in order to decrease amount of tables you link in the queries. The question makes even less sense as single database can hold multiple identical tables with identical records in them which - when not used - make no impact on data integrity or performance.

So in short, the answer is "yes", but you should say "no".

because the relational database should store no duplicate records

I'm going to have to disagree because its really not clear what the original poster was referring to. We don't know if we are discussing database normalization or not. What the original poster asked is if two "identical" records can exist within a database, and the clear answer to that question is: one record identical to another is certainly permissible, and as I've shown, might even happen in real life. Here's another example:
insert into transaction_log (inventory_id, quantity, purchase_date) values (10234, 1, '2006-10-26');
insert into transaction_log (inventory_id, quantity, purchase_date) values (10234, 1, '2006-10-26');
insert into transaction_log (inventory_id, quantity, purchase_date) values (10234, 1, '2006-10-26');
insert into transaction_log (inventory_id, quantity, purchase_date) values (10234, 1, '2006-10-26');

Just imagine a grocery store teller scanning cans of Chicken Soup, one at a time, and you might end up with data something like that.

Its probably a good time for the original poster to step in and clarify exactly what they are asking.

I suppose a longer answer would be:

It depends on the structure of the database/table in question.
If the table has no defined key then the answer is, "Yes." Because you can have multiple identical records without any negative impact to the database as shown above.
If the table has a defined key then the answer is, "No." Because if somehow the database did contain duplicate identical records when a key existed (external corruption?)... there would be/have been a negative effect on the integrity of the database.

By the way, if your teacher gives you a hassle about the answer "Yes, there can be identical records in a database", tell the teach that a sequence of tuples is perfectly permissible, and even likely to be encountered.
[(10234,1,'2006-10-26'),
(10234,1,'2006-10-26'),
(10234,1,'2006-10-26'),
(10234,1,'2006-10-26'),]

If your table has what is commonly implemented as an auto incrementing numeric key, then by definition the records are not the same.

[(1,10234,1,'2006-10-26'),
(2,10234,1,'2006-10-26'),
(3,10234,1,'2006-10-26'),
(4,10234,1,'2006-10-26'),]

The question is poorly put, by your teacher. Depending on what you've been taught to date they may be expecting that you'll "assume" there is a unique key, perhaps using an autoincrement feature, in place. But the question as it stands does not lay out that precondition, so the answer has to be YES, identical records are permissible within a database without compromising database integrity.

If you want extra points, add AND... and summarize the provisos discussed by one and all here.

edit: Sometimes it doesn't pay to be too smart with your instructors. For all we know the instructor has a poor grounding in database theory...