Database Question?

Question

I'm a little fuzzy on a couple of Database tems, maybe I can get some clarification here.

First, primary keys-- can one table have >1 primary keys? Can an attribute be a primary key in two tables?

Second, foreign keys-- does every primary key have to have a foreign key.

Ex.
Lets say you have three tables:
student(name, studentID, address),
activity(activityID and leaderID) ,
school(schoolID, address),
and another table, say,
info, which links them all (studentID, activityID, schoolID, major).

Would all the entries(aside from major) be foreign keys in the info table?

I can do wikipedia searches too. Please read the question before you copy and paste and cite your informant if it's a website.

Answer 1

In the example given all three of the ID columns are foreign keys, assuming there is a 1:1 correlation between the values in these columns in each table that has them. IE if Albert was entered into your student table with the following record:

Albert, 1, 1234 example drive

and there's an entry for his school in the school table:

1, Random University

then for the primary keys in these tables (studentID and schoolID respectively) to be considered foreign keys in the master table that links everything, there would need to be an entry for Albert in the master table that looked something like this:

1, NULL, 1, NULL

(I punted and placed nulls instead of backtracking and showing examples for each table in the database).

To summarise, the Primary Key is a unique identifier for each individual or whatever in your table. A Foreign Key is is used to show a relationship between data in separate tables using unique identifiers from each table involved in the relationship. I hope this helps clarify things.

Answer 2

Primary and Foreign Keys
All would be be foreign keys except for major. Foreign keys always point to a primary key in another table. The primary key is the unique identifier and you should have one in each table. I'd throw a infoID into your table too. Primary and foreign keys are the most basic components on which relational theory is based. Primary keys enforce entity integrity by uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities. The next step in building the basic data model to identify and define the primary key attributes for each entity validate primary keys and relationships
migrate the primary keys to establish foreign keys
Define Primary Key Attributes
Attributes are data items that describe an entity. An attribute instance is a single value of an attribute for an instance of an entity. For example, Name and hire date are attributes of the entity EMPLOYEE. "Jane Hathaway" and "3 March 1989" are instances of the attributes name and hire date.

The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity.

To qualify as a primary key for an entity, an attribute must have the following properties:

it must have a non-null value for each instance of the entity
the value must be unique for each instance of an entity
the values must not change or become null during the life of each entity instance

Answer 3

All would be be foreign keys except for major. Foreign keys always point to a primary key in another table. The primary key is the unique identifier and you should have one in each table. I'd throw a infoID into your table too.