Karteikarten
Databases Topic 3
Karten in diesem Stapel (29)
- 1What is Normalisation?Normalisation is a technique for producing a set of suitable relations that support the data requirements of an enterprise.
- minimal number of attributes
-minimal redundancy - 2Why bother doing Normalisation?The bad/poor/suboptimal designs:
- Have negative impacts on the performance of the database
- Have negative impacts on the ability of the database to be used for decision making. - 3"High level" Design Guidelines-Be simple to understand
- Not have any update anomalies
-Design should ensure the relations can be joined without generating extra records
-Functional dependencies that exist in the original data should be preserved. - 4Modification AnomaliesModification Anomalies may arise when data in a relation containing redundant data are modified.
- 5Modification Anomalies lead to inconsistency in the database.Insertion Anomalies - The need to enter more data than is required.
Update Anomalies - Not all instances of data update
Deletion Anomalies - The unintentional deletion of data - 6Eg. INSERTION ANOMALYINSERTION ANOMALY: To insert a new unit in the example, it is necessary to know a student and an offering of the unit.
- 7Eg. UPDATE ANOMALYUPDATE ANOMALY: If we change Mary's address we need to change two rows.
- 8Eg. DELETION ANOMALYDELETION ANOMALY: If we delete Bruce's enrollment in Databases in Semester 1, 2010, we lose all information regarding that offering.
- 9Functional Dependencies (1/2)Normalisation is a way of transforming an unsatisfactory relation schema (one with modification anomalies) into a 'better' design by reducing redundancy.
- 10Functional Dependencies (2/2)A functional dependency is a relationship between attributes such that the value of one attribute can be found from the value of another.
- 11Why bother with FDs?FDs represent information about the system we are modelling which should be preserved.
Normal forms 1NF, 2NF, 3NF and BCNF are based on functional dependencies between the attributes in a relation. - 12X -> YWe can say that X determines Y if:
There exists at most one value for every value of X.
Eg. StudentNumber -> DateOfBirth - 13Eg. EMPLOYEE ( EmployeeID, Name, Address, DeptNo )Name, Address, DeptNo are functionally dependent on EmployeeID
EmployeeID functionally determines Name, Address, DeptNo
EmployeeID is called the determinant of the FD - 14Writing FDsEmployeeID -> Name, address, DeptNo
OR
EmployeeID -> Name
-> Address
-> DeptNo - 15FD's and PK'sA primary key is simply a more restrictive type of FD
A FD in which the value of the determinant is unique. - 16Eg. EMP_DEPT (EmpID, Name, Address, DeptNo, DeptName)We have the FDs:
EmpID ->Name, Address, DeptNo, DeptName
DeptNo ->DeptName
*In any instance of EMP_DEPT the same DeptNo may occur many times but the value for EmpID (PK) is unique. - 17Deriving PK's from FD'sUse a dependency diagram to identify the minimum combination of attributes from which all others can be found.
- 18Normal FormsWe are most interested in 1NF, 2NF, 3NF and BCNF.
- 191NF -- BCNF1NF: All valid relations are in 1NF
2NF: 1NF PLUS no partial functional dependencies
3NF: 2NF PLUS no transitive functional dependencies
BCNF: 3NF PLUS all determinants are candidate keys - 20First Normal Form (1NF)Needs to be a valid relation
No repeating groups
The relation is NOT in 1NF means it is unnormalized - 21Second Normal Form (2NF)In 1NF
No partial functional dependencies - A partial functional dependency exists where a non-key attribute is dependent on only part of the key, rather than the whole of the key. - 22Eg. Partial Functional DependenciesPK is StdID, OfferingNo
FD's are: StdID -> Name, Course
OfferingNo -> Semester, Year, UnitCode, Title
StdID, OfferingNo -> Grade - 23Con't Eg. Modification Anomalies with PFD'sIf we were to change the title of ICT208, then we would have to update the title for every offering of the unit.
If we were to delete the only offering unit ICT208, we would lose all details of that unit. - 24Con't Eg. SolutionSln (for PFDs) is to create relations based on the FD's where the determinants are components of the primary key:
Relation1(Std,Name,Course)
Relation2(OfferingNo,UnitCode,Title, Semester,Year)
Relation3(OfferingNo,StdID,Grade) - 25Third Normal Form (3NF)In 2NF
No transitive functional dependencies - A TFD exists where a non-key attribute is determined by another non-key attribute. - 26Eg. Relation1 (StdID, Name, Course) Relation2 (OfferingNo, Year, Semester, UnitCode, Title) Relation3 (StdID, OfferingNo, Grade)There's transitive dependency in Relation 2.
OfferingNo -> UnitCode
UnitCode -> Title
TD is OfferingNo -> Title because UnitCode is a non-key attribute. - 27Con't Eg. SolutionCreate a set of relations based on the full and non-transitive dependencies in the original:
Relation2-1(OfferingNo, Year,Semester, Unitcode)
Relation2-2(UnitCode, Title) - 28Higher Normal FormsRecall:
A relation is in 1NF if there are no multi-valued (or nested) attributes.
A relation is in 2NF if it is in 1NF AND there exists no PFDs.
A relation is in 3NF if it is in 2NF AND there exists no TFDs. - 29BCNF - Boyce-Codd Normal FormRedundancies can still exists in 3NF.