Constraints - primary and foreign keys in Oracle

1. Constraints - primary and foreign keys in Oracle Please use speaker notes for additional information!

2. Example of third normal form using a text book company: • The textbook company that we are looking at keeps the following information: • ISBN number • Name of textbook • Name of author(s) - frequently there is no primary author • Date of publication • Price of textbook • Publishing company for book • Address/phone etc. information about publishing company • University, company or other credential • Email address for author • Authors current agent • Email address for agent • Address/phone etc. for agent

3. Taking the author id# key as an example, an author id# cannot be entered in the bridge file if that author id# is not in the author file. FK Foreign key The designation of KEY means primary key in these examples. Foreign key Foreign key Taking the agent code as another example, having the agent code in the author file designated as a foreign key means that an agent cannot have an agent code that does not appear in the agent table.

4. Information needed: • Student id# • Name • Major code • Major name • Department chair in major • Address and phone information • Date started at college • Date graduated • Degree awarded • Courses taken - for each course need • Course number • Course name • Credits • Semester/year the course was taken • Grade the student got in the course Data for a simple student record keeping system.

5. Foreign key Foreign key Foreign Key Note that the key on the student course file is student id# + course # + semester/year taken.