MSCI 3005 Lecture Notes - Lecture 15: Foreign Key, Unique Key
22 May 2018
School
Department
Course
Professor
Info Systems lecture
I. Midterm details
a. Next Tuesday
b. Wednesday will do sample questions
i. Do them before Wednesday
c. Review will not be comprehensive
d. Multiple choirce
e. Book chapters 1,2 and part of 5 (up till 5.4)
f. 40-50 questions
II. Relationships
a. A relationship is a natural business association that exists between one or more entities
i. Example: a customer can submit many orders
ii. Relationships are implicitly bi-directional
b. Relationships are formed by movement of keys
i. How entity relationship diagrams are read.
1. Two entities are customer and order.
a. One customer can have one or more orders
b. One order can have exactly one customer
c. 1 table that has customer data and another table that has order data
i. How do you make the connection between a row in one table and row in
another table
ii. Solution: putting a column called customer ID in the order table
1. Only one customer will be associated with an order
iii. Minimal redundancy in key values
iv. A primary key value never repeats in the table. They will always be a unique
value because otherwise you couldn’t distinguish between the two
v. Using primary key from one table and putting it in the other table
vi. Example: customer id in the order table is called the foreign key
d. Foreign key: the customer id from the customer table is replicated in the order table.
i. Coming from somewhere else, and it is a primary key from the table it is coming
from
ii. Foreign key value can repeat
iii. Avoiding redundancy is not about saving space. It is about avoiding
inconsistency
III. Entity relationship diagram with course, section, instructor
a. Start with course:
i. One course can have multiple sections.
b. Start with section ad go to course
i. One section is related to 1 course
c. With section: one section is associated with one instructor
d. One instructor can teach one or many sections
e. A symbol of parallel, vertical lines = 1
find more resources at oneclass.com
find more resources at oneclass.com
