6.3 KiB
Lecture 1 (13:00)
Candidate Key
- Attribute / Set of attributes that are:
- Irreducible
- A Unique ID for the rows in the table
- If every attribute in the key is required to uniquely identify every row in the relation, it is irreducible.
- If there is a subset of this set that uniquely identifies every row in the table, it is reducible.
- Primary Key is the candidate key chosen to be the identifier.
Primary Key
- Many attributes may be required to form.
Transforming ER to Relational
- Each entity transforms into a table with same attributes and primary key.
- Each relationship transforms into either:
- A foreign key in existing table.
- New table, linked by foreign keys.
- Constraints transform into attribute constraints or table constraints.
Transforming ER Model into Tables
Representing a Relationship Using Foreign Key
Principles of Choosing Foreign Keys
- Should not be null
- Should not have multiple values
- Keep it simple
- Ex.
- All Many to Many relationships are transformed the same way.
[1:1] [o:m] Relationships
- Since a professor does not have to have a department, we should not add
dep#as a foreign key to the professors table, as values may be null. - As a department must have exclusively one professor, the logical choice is to add
p#as the foreign key in the departments table.
[1:1] [o:o] Relationships
- As neither table can fulfil its purpose as a foreign key, we cannot add either relation to either table, as it would break the principles of foreign keys.
- Instead we should create a third table, containing both primary keys, as seen below.
- The primary key for this table may be
p#,dep#, but never both. - There will never be more than one professor to one department, as seen by the lack of crows foot in the diagram.
[1:1] [m:m] Relationships
- Since there must be 1 professor to 1 department, there would never be a null value, and never have multiple values in the foreign key. This way we can add either primary key as the foreign key in the other table.
- If we know the query types that would be most common, we can choose the foreign key that would be most applicable.
Exercise 1
A person must have exactly one birth certificate. Each birth certificate is for just for one person.
[Person] -|---|- [Certificate]
- This is a [1:1] [m:m] relationship, meaning we can have either primary key as the foreign key in the other table.
Answer
Exercise 2
Each birth certificate is for just for one person. A person may have a birth certificate, or may have lost it!
[Person] -o---|- [Certificate]
- This is a [1:1] [o:m] relationship. The person table primary key should be the foreign key in the certificate table.
Answer
[M:1] [m:o] Relationships
- A team can include none, one or many junior doctors, but a junior cannot be part of multiple teams.
- Each junior is in exactly one team, so adding the team primary key to the juniors table is the most acceptable in this scenario.
[M:1] [o:o] Relationships
- Since in this example, a junior may not be in a team, we must have to make a third table, since any solution would lead to multiple or null values, which would violate foreign key principles.
Exercise 3
Part 1
A person must own one or more cars. A car must have exactly one owner.
[Person] -|---|-/\ [Car]
- This is a [1:M] [m:m] Relationship
- person(person#, name…)
- car(car#, …, person#*)
- The person candidate key would be the foreign key in the car table.
Part 2
A person may own none, one or more cars. A car must have exactly one owner.
[Person] -o---|-/\ [Car]
- This is a [1:M] [o:m] Relationship
- person(person#, name, …)
- car(car#, …, person#*)
Lecture 2 (15:00)
Many to Many Relationships
- Since we cannot have either primary key be a foreign key, as we would have multiple values, we would have to make a composite 3rd table.
Transforming Complex ER Diagrams
Flow Chart
Tutorial 1 (15:00)
Exercise 1
Part 1
Project -o---|-/\- Order [1:M] [o:m] Relationship
project(proj#, name, start-date, end-date) order(order#, date, inquiry, proj#*)
Part 2
Supplier -/\-o---o-/\- Part [M:M] [o:o] Relationship
supplier(supplier#, name, tel-no) part(part#, name, price) orderList(supplier#*, part#*)
- We must create a new composite table to contain the relation, since we cannot sustain the principles of foreign keys.
Part 3
Staff -/\-|---|- Department [M:1] [m:m] Relationship
staff(staff-id, name, address, phone-no, dept-id*) department(dept-id, name, location)
- We would create dept-id as a foreign key in the staff table, as multiple values wont occur, and staff must be a part of a department in this scenario.
Part 4
Manager -|---|- Project [1:1] [m:m] Relationship
manager(man-id, name, address, tel-no, proj-id*) project(proj-id, name, start-date, end-date)
- We could use either primary key for the alternate table's foreign key, since they are both mandatory and 1:1, so there would be no multiple values nor would there be null values.
Part 5
Manager -|---|-/\- Team -|---|-/\- Player [1:M] [m:m] (Both) Relationship
manager(man-id, name, address, tel-no) team(team-name, date-founded, address, man-id*) player(player-id, name, address, tel-no, team-name*)
Tutorial 2 (14:00)
Entities & Attributes
| Entities | Attributes |
|---|---|
| Regional Office | regioncode, name, location |
| Branch | branch_no, institution |
| Member | mem_no, name, age, type |
| Role | role_id, level, role |
Relations
Role - [1:M][m:o] - Member Member - [M:1][o:m] - Branch Branch - [M:1][m:m] - Region