Question Does a every table in a 3NF DB have to have a PK?

[oZone]

Well said, understood, and 100% agree.

In my studies of normalization thus far I have come to the realization that, in a real-world situation such as this, adding an additional PK field (such as an autonumber) would be the best thing to do.

Rest assured, once I am done with this class I will be doing this. Unfortunately, until then I am bound by his requirements, as strange and unorthadox as they may be.

I've finished, and turned in this assignment. My instructor said I did very well, and got 100%

Thanks to all of you who took the time out of your schedules to help me. I owe a good portion of my grade to your advice and instruction.

My hope is that someone else out there will benefit in some way from this discussion also.

 

[boblarson]

Glad you got something out of this exchange and hopefully it will help you in the future. Good luck with your studies.

 

[Pat Hartman]

It is rare that I contradict Pat and I usually duck afterwards. But sometimes I have to point out a fine point in the issue of pragmatic design.

I don't ever recall throwing things at you

sqldbpool.wordpress.com/2008/03/15/database-conchttpsqldbpoolwordpresscomwp-adminpostphpactioneditpost3epts/

Wow it was hard to post that link. The site kept truncatting it. Prefix with h t t p : / /
The following is an exerpt from that link. It summarizes Codd's original definition of a relational database. Notice point #2.

I don't mean to be pedantic about this but we have this "discussion" here on a regular basis. IMNSHO there is never a reason to not have a PK key. If there is no candidate key, a surogate key is used. What is the big deal?

These rules were defined by Codd in a paper published in 1985. They specify what a relational database must support in order to be relational. These rules have been considerably extended in reference [1].
1. Information rule

* Data are represented only one way: as values within columns within rows.
* Simple, consistent and versatile.
* The basic requirement of the relational model.

2. Guaranteed access rule

* Every value can be accessed by providing table name, column name and key.
* All data are uniquely identified and accessible via this identity.

3. Systematic treatment of null values

* Separate handling of missing and/or non applicable data.
* This is distinct to zero or empty strings
* Codd would further like several types of null to be handled.

4. Relational online catalog

* Catalog (data dictionary) can be queried by authorized users as part of the database.
* The catalog is part of the database.

5. Comprehensive data sublanguage

* Used interactively and embedded within programs
* Supports data definition, data manipulation, security, integrity constraints and transaction processing
* Today means: must support SQL.

6. View updating rule

* All theoretically possible view updates should be possible.
* Views are virtual tables. They appear to behave as conventional tables except that they are built dynamically when the query is run. This means that a view is always up to date. It is not always theoretically possible to update views. Codd himself, did not completely understand this. One problem exists when a view relates to part of a table not including a candidate key. This means that potential updates would violate the entity integrity rule.

7. High-level insert, update and delete

* Must support set-at-a-time updates.
* ie. Transactions
* eg: UPDATE mytable SET mycol = value WHERE condition;
Many rows may be updated with this single statement.

8. Physical data independence

* Physical layer of the architecture is mapped onto the logical layer.
* Users and programs are not dependent on the physical structure of the database.
* (Physical layer implementation is dependent on the DBMS.)

9. Logical data independence

* Users and programs are independent of the logical structure of the database.
* i.e.: the logical structure of the data can evolve with minimal impact on the programs.

10. Integrity independence

* Integrity constraints are to be stored in the catalog not the programs.
* Alterations to integrity constraints should not affect application programs.
* This simplifies the programs.
* It is not always possible to do this.

11. Distribution independence

* Applications should still work in a distributed database (DDB).

12. Nonsubversion rule

* If there is a record-at-a-time interface (eg via 3GL), security and integrity of the database must not be violated.
* There should be no backdoor to bypass the security imposed by the DBMS.


• Is access database a RDBMS?
Yes Access is RDBMS