163 lines
5.3 KiB
Markdown
163 lines
5.3 KiB
Markdown
## Lecture 1 (13:00) - Normalisation
|
|
|
|
### Functional Dependency
|
|
|
|
- A -> B
|
|
- A functionally determines B
|
|
- B is functionally dependent on A
|
|
This means you cannot have two rows with the same value of A and different values of B
|
|
- ex.
|
|
- StudentID -> StudentSurname
|
|
- u001 -> smith
|
|
- StudentSurname -/> StudentID
|
|
- smith -> u001
|
|
- smith -> u002
|
|
- smith -> u003
|
|
|
|
### 1st NF
|
|
|
|
- First normal form has:
|
|
- No repeating groups
|
|
- Non-key attributes determined by key
|
|
|
|
##### Example 1
|
|
|
|
|
|
In this example, 2 tables are created, since there is a repeating group in the initial table. The separate composite table allows us to remove the repeating group.
|
|
|
|
##### Example 2
|
|
|
|
|
|
In this example, 3 tables are created. A composite table to keep the relations, and 2 tables to contain the repeated groups.
|
|
There are 2 repeating groups, since the attribute Department has multiple values for a value of the key ID, and the attribute Interests does the same.
|
|
As they do not relate to each other, they are not part of the same repeating group.
|
|
|
|
#### Repeating Group
|
|
|
|
- Repeating group is an attribute or group of attributes in a table that occur with multiple values for a single occurrence of the nominated key attributes for that table
|
|
ex.
|
|
|
|
|
|
### Full / Partial Functional Dependency
|
|
|
|
- Attributes may depend on a set of other attributes
|
|
- StudentId, ModuleName -> ExamMark
|
|
- OrderNo, PartNo -> Quantity
|
|
- D is *fully functionally dependent* on A, B and C if
|
|
- A, B, C -> D but A, B -/> and B, C -> D
|
|
- All the attributes on the left are needed to determine the right
|
|
- Partial dependency refers to the attributes which are only dependent on part of the composite primary key.
|
|
|
|
##### Example 1
|
|
|
|
|
|
|
|
These partial dependencies can be removed by doing the following
|
|
|
|
|
|
### 2nd NF
|
|
|
|
- A table is in second normal form if:
|
|
- It is in 1NF
|
|
- There are **no** partial dependencies
|
|
- (every non-key attribute is fully dependent on the primary key)
|
|
- Partial dependencies can only be possible if the primary key is composite, if not then nothing depends on part of the PK, since it has no parts.
|
|
- A 1NF table is automatically 2NF if the PK is atomic ( one attribute )
|
|
|
|
### 3rd NF
|
|
|
|
- A table is in 3rd normal form if:
|
|
- It is in 2NF
|
|
- There are no transitive dependencies
|
|
- (if no non-key attribute is transitively dependent on the PK)
|
|
|
|
#### Transitive Dependency
|
|
|
|
- If A -> B and B -> C, then we can write:
|
|
- A -> B -> C
|
|
- OrderNo -> SupplierNo -> SupplierName
|
|
- We say
|
|
- “C is transitively dependent on A”.
|
|
- “A determines C via B”.
|
|
- So for the supplier table we say:
|
|
- “Supplier name is transitively dependent on OrderNo.”
|
|
- “OrderNo determines SupplierName via SupplierNo.”
|
|
|
|
### Normalisation Venn Diagram
|
|
|
|
|
|
|
|
### Summary
|
|
|
|
|
|
|
|
## Tutorial 1 (15:00) - Normalisation
|
|
|
|
### Exercise 1 - Identifying Design Faults
|
|
|
|
1. Is Table 14.1 in 1NF? Is it in 2NF? Is it in 3NF? Justify your answers.
|
|
|
|
CW Mark and Exam Mark depend on the composite key. Does not contain repeating groups.
|
|
CW Mark and Exam Mark are fully functionally dependent on both StudentID and Module. There are no partial dependencies.
|
|
There are no transitive dependencies. 3NF
|
|
|
|
2. Is Table 14.2 in 1NF? Is it in 2NF? Is it in 3NF? Justify your answers.
|
|
|
|
2NF - CW and CW Outcome depend on the composite key. No repeating groups
|
|
CW Mark and CW Outcome are functionally dependent on both StudentID and Module. No partial Dependencies
|
|
There is a transitive dependency (CW Mark depends on key, CW Outcome depends on CW Mark), so 2NF
|
|
|
|
### Exercise 2 - Restructuring Tables
|
|
|
|
1. Convert Table 14.3 to 1st normal form.
|
|
|
|
|
|
We must first create a composite table.
|
|
|
|
| *country* | saint |
|
|
|-|-|
|
|
| England | George |
|
|
| Wales | David |
|
|
| Scotland | Andrew |
|
|
|
|
Then we can use the cities field as the primary key in the second table
|
|
|
|
| *cities* | country |
|
|
|-|-|
|
|
| Manchester | England |
|
|
| Leeds | England |
|
|
| Newcastle | England |
|
|
| Swansea | Wales |
|
|
| Cardiff | Wales |
|
|
| Glasgow | Scotland |
|
|
| Edinburgh |Scotland |
|
|
|
|
1. Convert Table 14.4 to 2nd normal form.
|
|
|
|
|
|
| student-ID | stud-name |
|
|
|------------|-----------|
|
|
| S001 | Tom |
|
|
| S002 | Dick |
|
|
| S003 | Harry |
|
|
|
|
| module | student-ID | grade |
|
|
|--------|------------|-------|
|
|
|Databases|S001|A|
|
|
|Databases|S002|B|
|
|
|Databases|S003|B|
|
|
|Research Methods|S001|A|
|
|
|Research Methods|S003|B|
|
|
|Java|S001|C|
|
|
|Java|S002|C|
|
|
|Java|S003|C|
|
|
|
|
1. Examine Table 14.5 to see if it contains redundant data, i.e., the same fact stored
|
|
more than once. Suggest how the redundancy might be removed by splitting the
|
|
table into two relations with a primary key/foreign key link.
|
|
|
|
|
|
We could remove the redundant data by creating a composite table of project-manager-id and project-manager-name. project-manager-id then becomes a foreign key in a second table with fields (*project-ID*, part, quantity and project-manager-ID). Since there is no candidate key, we must create a new primary key called project-id with a unique value.
|
|
|
|
![[images/Diagram.svg]]
|