1 / 15

Table Design

Table Design. Design Guidelines Normalisation. Informal Design Guidelines. Table Semantics A table should hold information about one and only one entity/concept from the real world Don’t mix information about more things in one table Avoid Redundant Information Waste of storage

rianne
Download Presentation

Table Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Table Design Design Guidelines Normalisation

  2. Informal Design Guidelines • Table Semantics • A table should hold information about one and only one entity/concept from the real world • Don’t mix information about more things in one table • Avoid Redundant Information • Waste of storage • Update Anomalies • Minimise NULL-values • Storage requirements • Multiple interpretations (ambiguity) • Disallowing the generation of spurious tuples when joining tables.

  3. Semantics • Consider this table part of a system to handle loans from a library: Loan: [title, matNo, lno, lname, laddress, date, status] • Information about different things in the same table

  4. Minimise NULL-values 1 1 Employee Car If NULL is the most common value for an attribute, then that attribute may not belong in the table. One out of ten employees has a company car. One out of ten cars are assigned to certain employee. On which side should the foreign key be included?

  5. Spurious Tuples • Again consider this table part of system to handle loans from a library: Loaner: [lNo, fname, lname,…….] Copy: [matNo,…, lname, …] • The relationship between Loaner and Copy is designed by including the loaner’s last name in Copy • When Loaner and Copy are joined over lname spurious tuples probably will be generated since lname hardly is unique. • For instance ’117 Joe Smith’ will be associated with all copies borrowed by someone with last name ‘Smith’, and all other Smiths will be associated with copies borrowed by Joe. • The problem arises when relations are represented by anything else than primary – foreign keys

  6. Normalisation • Normal forms are the formal way to state design guidelines. • Normalisation is the process. • 6 normal forms (NF) are defined: • 1st, 2nd, 3rd, and Boyce-Codd (BCNF). • 4th and 5th NF • BCNF is the one of most practical interest.

  7. First Normal Form (1NF) • A table is on 1NF if • All attributes are atomic • 1NF has become part of the definition of a relation in the relational model and is achieved trivially.

  8. Functional Dependenciesthe foundation of 2NF, 3NF and BCNF Y is functional dependent (FD) of X, if there for any given value of X always is the same value for Y (X and Y being any set of attributes). FD is written X -> Y Y is FD of X or X is determinant for Y If X is a candidate key, then X -> Y for all sets of attributes Y. X -> Y implies nothing about Y -> X. Classic example: in an address city is FD of postalCode (or postalCode determines city).

  9. A Side • Often in literature functional dependencies and normal forms are described using a lot of math and it may seem quite theoretical and complicated BUT FDs are business rules and normal forms are common sense constraints on table design • The theory and the math are very useful building tools

  10. Second Normal Form (2NF) • Is about partial FDs • A FD X->Y is a full functional dependency (FFD), if no attribute can be removed from X without also removing the FD X->Y. • A FD that is not FFD is called partial. • A table is on 2NF if: • It is on 1NF • All non-key attributes are FFD of all candidate keys. Example: Loan: [title, matNo, lno, lname, laddress, date, status]

  11. Third Normal Form (3NF) • Is about transitive FDs • A FD (X->Y) is transitive, if there exists a set of attributes Z satisfying X -> Z and Z -> Y. • A table is on 3NF if: • It is on 2NF • No non-key attribute is transitively dependent of a candidate key. • ”postalCode - city”-problem!

  12. Transitive FD Ssn Dmgr_ssn since Ssn Dnumberand Dnumber  Dmgr_ssn Dnumber is not a key or part of a candidate key for EMP_DEPT

  13. Boyce-Codd Normal Form If a table is on BCNF, then it is also on 1., 2. and 3. NF. A table is on BCNF, if all determinants are candidate keys. That is: only candidate key may determine the value of other attributes

  14. overlap The Difference between 3NF and BCNF • For a table to be on 3NF and not on BCNF it must satisfy: • It has more than one candidate key, and • The candidate keys are overlapping, that is: they have common attributes. • For example: A: [a, b, c, d] candidate keys: (a, b) and (a, d)

  15. Guideline for Normalisation All attributes are to depend on the key, the whole key, and nothing but the key. So help me Codd.

More Related