INFORMATION TECHNOLOGY
Week 4 Normalisation
Workshop 2022 Semester 1
Copyright By PowCoder代写 加微信 powcoder
INSERT, UPDATE and DELETE Anomalies
▪ INSERT Anomaly
– When adding data to a relation you are required to add other (related) data
– Danger: other data may not be available so cannot proceed with the insert
▪ UPDATE Anomaly
– Changing a value for an attribute requires multiple tuples to be changed
– Danger: only some tuples will be updated leading to inconsistent data
▪ DELETE Anomaly
– When a tuple in a relation is deleted, all tuple data is removed
– Danger: related data, which may be the only such data will be lost
INSERT, UPDATE and DELETE Anomalies Example
SLSREP_NAME
SLSREP_MOBILE
2379307017
Gemfibrozil
6154866270
Acne Solutions Clarifying
7247448365
6154866270
Prednicarbate
6154866270
2379307017
2379307017
Diflunisal
2379307017
Rigidity HP
4647420304
7247448365
INSERT, UPDATE and DELETE Anomalies Example
▪ INSERT Anomaly
– cannot add a new SLSREP until
they have been assigned a DRUG or add a new DRUG until assigned to a SLSREP
▪ UPDATE Anomaly
– changing a SLSREP mobile number
requires changes to multiple rows
▪ DELETE Anomaly
– delete a DRUG may lose
SLSREP details eg. “Rigidty HP” or deleting a SLSREP may lose DRUG details eg. ” ”
SLSREP_NAME
SLSREP_MOBILE
2379307017
Gemfibrozil
6154866270
Acne Solutions Clarifying
7247448365
6154866270
Prednicarbate
6154866270
2379307017
2379307017
Diflunisal
2379307017
Rigidity HP
4647420304
7247448365
Data Normalisation
▪ Relations MUST be normalised in order to avoid anomalies which may occur when inserting, updating and deleting data.
▪ Normalisation is a systematic series of steps for progressively refining the data model.
▪ A formal approach to analysing relations based on their primary key / candidate keys and functional dependencies.
▪ as a design technique “bottom up design”, and
▪ as a way of validating structures produced via “top down design” (ER model
converted to a logical model – see next week)
▪ for this unit only concerned with conversion to third normal form – higher normal forms exist ( Normal Form, fourth normal form … )
The Normalisation Process Goals
▪ Creating valid relations, i.e. each relation meets the properties of the relational model. In particular:
– Entity integrity
– Referential integrity
– No many-to-many relationship
– Each cell contains a single value (is atomic).
▪ In practical terms when implemented in an RDBMS:
– Each table represents a single subject
– No data item will be unnecessarily stored in more than one table (remember some redundancy still exists – minimal redundancy).
– The relationship between tables can be established (via PK and FK pairs).
– Each table is void of insert, update and delete anomalies.
Representing a form as a relation
▪ This process follows a standard approach:
– arrive at a name for the form which indicates what it represents (its subject)
– determine if any attribute is multivalued (repeating) for a given entity instance of the forms subject
• if an attribute (or set of attributes) appears multiple times then the group of related attributes need to be shown enclosed in brackets to indicate there are multiple sets of these values for each instance
▪ Looking at our DRUG data – Name: DRUG_SLSREP
• DRUG_SLSREP (drug_code, drug_name, slsrep_id, slsrep_name, slsrep_mobile)
– i.e. the form consists of repeating rows (instances) of drugs assigned to sales representatives data
Q1: Representing a form as a relation
Unnormalised Form (UNF)
▪ The UNF representation of a relation is the representation which you have mapped from your inspection of the form
– it is a single named representation (name is not pluralised)
– no PK etc have as yet been identified
▪ PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell (vendor_no, vendor_name, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment))
– Is this a relation • Reasons?
Functional Dependency Revisited
▪ An attribute B is FUNCTIONALLY DEPENDENT on another attribute A, if a value of A determines a single value of B at any one time.
– PRODNO ➔ PRODDESC
– CUSTNUMB ➔ CUSTNAME
– ORDERNO ➔ ORDERDATE
• ORDERNO – independent variable, also known as the DETERMINANT
• ORDERDATE – dependent variable
▪ TOTALDEPENDENCY
– attribute A determines B AND attribute B determines A
• EMPLOYEE-NUMBER ➔ TAX-FILE-NUMBER
• TAX-FILE-NUMBER ➔ EMPLOYEE-NUMBER
Functional Dependency
▪ For a composite PRIMARY KEY, it is possible to have FULL or PARTIAL dependency.
▪ FULLDEPENDENCY
– occurs when an attribute is always dependent on all attributes in the
composite PK
– ORDERNO, PRODNO ➔ QTYORDERED
▪ Lack of full dependency for multiple attribute key = PARTIAL DEPENDENCY
– ORDERNO, PRODNO
➔ PRODDESC, QTYORDERED
– here although qtyordered is fully dependent on orderno and prodno, only prodno is required to determine proddesc
– proddesc is said to be partially dependent on orderno and prodno
Functional Dependency
▪ TRANSITIVE DEPENDENCY
– occurs when Y depends on X, and Z depends on Y – thus Z also depends on
X ie. X ➔ Y ➔ Z
– and Y is not a candidate key (or part of a candidate key) – ORDERNO ➔ CUSTNUMB ➔ CUSTNAME
▪ Dependencies are depicted with the help of a Dependency Diagram.
▪ Normalisation converts a relation into relations of progressively smaller number of attributes and tuples until an optimum level of decomposition is reached – little or no data redundancy exists.
▪ The output from normalisation is a set of relations that meet all conditions set in the relational model principles.
Dependency Diagrams
Figure 6.3 From the text:
For this unit (note dependencies show at each normal form – see following slides):
For ease of drawing we would show, for example, the full dependency above as: proj_num, emp_num → hours
First Normal Form
▪FIRST NORMAL FORM (part of formal definition of a relation) – A RELATION IS IN FIRST NORMAL FORM (1NF)
• a unique primary key has been identified for each tuple/row. • it is a valid relation
– Entity integrity (no part of PK is null)
– Single value for each cell ie. no repeating group (multivalued attribute).
• all attributes are functionally dependent on all or part of the primary key
UNF to 1NF
▪ Note we do not use the approach shown in your text of representing the data in tabular form ie. “flattening the data”
– this works for simple cases but cannot always be used (you must not use this approach)
▪ Move from UNF to 1NF by:
– identifying a unique identifier for the repeating group.
– remove any repeating group along with the PK of the main relation.
– The PK of the new relation resulting from the removal of repeating group will normally have a composite PK made up of the PK of the main relation and the unique identifier chosen in 1. above, but this must be checked.
Q2: UNF to 1NF
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell (vendor_no, vendor_name, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment))
Yields which of the following in moving to 1NF:
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell, vendor_no, vendor_name, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment)
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
PART (part_no, vendor_no, restock_date_purchased, part_name, cat_code, cat_name, part_stock, part_sell, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
PART (part_no, part_name, part_stock, part_sell)
CATEGORY (cat_code, cat_name))
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
None of these
UNF to 1NF
Note show only partial dependencies at 1NF
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell (vendor_no, vendor_name, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment))
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
Partial Dependencies: vendor_no -> vendor_name
1NF to 2NF
▪ A RELATION IS IN 2NF IF –
– all non key attributes are functionally dependent on the primary key
(simple definition)
• used by the textbook in examples
– all non key attributes are functionally dependent on any candidate key (general definition)
• see textbook section 6-3 (last sentence in paragraph immediately below table 6.2), same as simple if only one candidate key
• General is the requirement for our unit
Q3: 1NF to 2NF
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
a. vendor_no -> vendor_name, remove partial
Removing the partial dependency yields:
RESTOCK (part_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment) VENDOR (vendor_no, vendor_name)
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
VENDOR (vendor_no, vendor_name)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment)
VENDOR (vendor_no, vendor_name)
None of these
1NF to 2NF
Note show only transitive dependencies at 2NF
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
– vendor_no -> vendor_name, remove partial
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment)
VENDOR (vendor_no, vendor_name)
Transitive Dependencies: cat_code -> cat_name
2NF to 3NF
▪ A RELATION IS IN 3NF IF –
– all transitive dependencies have been removed – check for non key
attribute dependent on another non key attribute
▪ Move from 2NF to 3NF by removing transitive dependencies
– Remove the attributes with transitive dependency into a new relation.
– The determinant will be an attribute in both the original and new relations (it will become a PK / FK relationship)
– Assign the determinant to be the PK of the new relation.
2NF to 3NF
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment)
VENDOR (vendor_no, vendor_name)
Transitive Dependencies: cat_code -> cat_name
PART (part_no, part_name, cat_code, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment) VENDOR (vendor_no, vendor_name)
CATEGORY (cat_code, cat_name)
Relations in 3NF
PART (part_no, part_name, cat_code, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment)
VENDOR (vendor_no, vendor_name) CATEGORY (cat_code, cat_name)
Full Dependencies:
part_no -> part_name, cat_code, part_stock, part_sell
part_no, vendor_no, restock_date_purchased -> restock_costpu,
vendor_no -> vendor_name cat_code -> cat_name
restock_qtysupplied, restock_payment
Note show ALL full dependencies at 3NF
Q4. The final set of 3NF relations
PART (part_no, part_name, cat_code, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment)
VENDOR (vendor_no, vendor_name) CATEGORY (cat_code, cat_name)
A. 4 PKs and 2 FKs
B. 6 PKs and 3 FKs
C. 4 PKs and 3 FKs
D. None of these is correct
The full process UNF to 3NF
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell (vendor_no, vendor_name, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment))
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, vendor_name, restock_costpu, restock_qtysupplied, restock_payment)
Partial Dependencies: vendor_no -> vendor_name
PART (part_no, part_name, cat_code, cat_name, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment) VENDOR (vendor_no, vendor_name)
Transitive Dependencies: cat_code -> cat_name
PART (part_no, part_name, cat_code, part_stock, part_sell)
RESTOCK (part_no, vendor_no, restock_date_purchased, restock_costpu, restock_qtysupplied, restock_payment) VENDOR (vendor_no, vendor_name)
CATEGORY (cat_code, cat_name)
Full Dependencies:
part_no -> part_name, cat_code, part_stock, part_sell
part_no, vendor_no, restock_date_purchased -> restock_costpu, restock_qtysupplied, restock_payment vendor_no -> vendor_name
cat_code -> cat_name
Q5. Normalisation Exercise – Group Prepare UNF
TRAINING (train_code, train_desc, train_active_mnths, (dt_code, dt_model, dt_manuf), (train_date, trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category, (cust_id, cust_fname, cust_lname, ct_exam_date, ct_date_expiry)))
Removal of repeating groups working:
TRAINING (train_code, train_desc, train_active_mnths) DRONETYPE (dt_code, dt_model, dt_manuf, train_code)
TRAINING_COURSE (train_code, train_date, trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category, (cust_id, cust_fname, cust_lname, ct_date_start, ct_date_expiry)) – still in UNF has repeating group
Q6. Normalisation Exercise – Continue the process to 3NF
TRAINING (train_code, train_desc, train_active_mnths, (dt_code, dt_model, dt_manuf), (train_date, trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category, (cust_id, cust_fname, cust_lname, ct_exam_date, ct_date_expiry)))
TRAINING (train_code, train_desc, train_active_mnths) DRONETYPE (dt_code, dt_model, dt_manuf, train_code)
TRAINING_COURSE (train_code, train_date, trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category)
CKs: (train_code, train_date) and (train_date, trainer_id)
CUST_TRAINING (train_code, train_date, cust_id, cust_fname, cust_lname, ct_exam_date, ct_date_expiry)
Partial Dependency based on Candidate keys (Must use GENERAL definition): trainer_id -> trainer_rego, trainer_fname, trainer_lname, trainer_category cust_id -> cust_fname, cust_lname
TRAINING (train_code, train_desc, train_active_mnths)
DRONETYPE (dt_code, dt_model, dt_manuf, train_code)
TRAINER (trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category) TRAINING_COURSE (train_code, train_date, trainer_id)
CUSTOMER (cust_id, cust_fname, cust_lname)
CUST_TRAINING (train_code, train_date, cust_id, ct_exam_date, ct_date_expiry)
Transitive Dependency No Transitive Dependency
TRAINING (train_code, train_desc, train_active_mnths)
DRONETYPE (dt_code, dt_model, dt_manuf, train_code)
TRAINER (trainer_id, trainer_rego, trainer_fname, trainer_lname, trainer_category) TRAINING_COURSE (train_code, train_date, trainer_id)
CUSTOMER (cust_id, cust_fname, cust_lname)
CUST_TRAINING (train_code, train_date, cust_id, ct_exam_date, ct_date_expiry)
Full dependencies
train_code -> train_desc, train_active_mnths
dt_code -> dt_model, dt_manuf, train_code
trainer_id -> trainer_rego, trainer_fname, trainer_lname, trainer_category train_code, train_date -> trainer_id
cust_id -> cust_fname, cust_lname
train_code, train_date, cust_id -> ct_exam_date, ct_expiry_date
Q7. Post Workshop Task – answer available Sunday 5 PM
Normalise this form:
▪ Things to remember
– Represent form as presented, no interpretation, to yield starting
point (UNF)
– Functional dependency
– Process of removing attributes in relations based on the concept of 1NF, 2NF and 3NF.
• UNF to 1NF define PK & remove repeating group.
• 1NF to 2NF remove partial dependency.
• 2NF to 3NF remove transitive dependency.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com