Standard speaker notes to accompany the powerpoint presentation called primforeign:
Using relational database notes to clarify information on primary and foreign keys.
These are the fields I am going to include in my database. Obviously in reality there would be a lot more fields in this system.
The first problem that we face is that one textbook can have many authors and one author can write many textbooks. This means that textbooks and authors are in a many to many relationship. The information about the authors cannot be carried in the textbook file and the information about the textbooks cannot be carried in the author file. I would not be in third normal form if I did this because it is repeating information. Therefore I created a bridge file to bridge between the authors and the text.
Upon analyzing this, we discover we need: (1) a student file that contains information about the student, (2) a major file that contains the major name and department chair, (3) a course file that contains the course number and name of all courses offered by the college, (4) a student course file that contains the courses taken by students.
In forming the relational database, we needed to remove everything about the major except the major code. We also needed to remove the course name and we needed to remove the repeating groups (the courses the students have taken) to put this into third normal form.
The lines drawn are showing the foreign keys that can be developed in this database. The foreign keys that I established assure that a student cannot have a major that does not exist in our major file. A student cannot have an entry in the student course file unless the student exists in the student file. A course cannot be in the student course file unless the course exists in the course file.
This is a basic order entry system. This would be for a business that has a known customer base.
If you look at this a cust # cannot be entered in the order header if they do not have a cust # in the customer table. Also an order# cannot be entered in the order line table unless there is an order# in the order header. And an item # cannot be entered in the order line table unless the item # is in the inventory file. And finally a vendor number cannot be in the inventory file if the vendor # is not in the vendor file.