1 / 4

Finding All the Keys

Finding All the Keys. Computationally, finding all the keys can be done by exhaustive search: Given a table with 6 attributes, the number of all possible combinations of attributes is:.

cullen-head
Download Presentation

Finding All the Keys

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. Finding All the Keys • Computationally, finding all the keys can be done by exhaustive search: • Given a table with 6 attributes, the number of all possible combinations of attributes is: • Since testing if a set of attributes is a candidate key or not is not difficult, trying out all 63 possibilities is a breeze for a computer Department of Computer Science and Engineering, HKUST Slide 1

  2. Heuristics to Reduce the Possibilities • Of course, students have to do it by hand (in exams)! • Go back to the example in chap6.ppt: R = (A, B, C, G, H, I)F = A  B A  C CG  H CG  I B  H • Heuristics can cut down the total combinations from 63 to a few: • Attributes that no other attributes determine must be part of ANY candidate key (i.e., A and G) • Attributes that don’t determine any other attributes but are determined by other attributes should not belong to ANY candidate key (i.e., H and I, which does not conflict with our previous conclusion) • Only 4 possibilities remain: AG, AGB, AGC, AGBC • Since A->B and A-> C, so AG is the only key for R Department of Computer Science and Engineering, HKUST Slide 2

  3. FDs require just Logical Reasoning • The above deductions are just logical reasoning, which are not unique to database design • Other “obvious” results can be deduced by reasoning: • R(A,B) is always in BCNF, regardless of what FDs are given • Consider all 4 possibilities: • Case 1: no FDs, R must be in BCNF (since no FD can violate BCNF definition!) • Case 2: Only A->B, then A is a candidate key, FD does not violate BCNF • Case 3: Only B->A, then B is a candidate key, FD does not violate BCNF • Case 4: Both A->B and B->A, both A and B are candidate keys; neither FD violates BCNF • There are other “interesting” properties that can be proven by reasoning • In real life, FDs may not be very complicated but knowing why FDs affects data redundancy and updates, how to reason on FDs and how to decompose a table help to get a better database design Department of Computer Science and Engineering, HKUST Slide 3

  4. To Normalize or Not to Normalize, That is the Question • Good practice: All tables must be in 3NF, in BNCF if possible • Problems with having a lot of tables: • Computational cost is high (a lot of joins, but can create a physical view) • Insertion cost COULD BE high, inserting a set of values into the database may cause several tables to be updated (likewise for deletion) • When NOT to normalize (i.e., use a big table)? • High retrieval speed is required • When a table is never updated (e.g., access log for a website), inconsistency and update anomaly due to data redundancy are not concerns • Each transaction generates a large group of data (e.g., IP address, cookies, time, date, URL, etc., in an access log table), appending all the data into a table is more efficient than updating several tables • Although a non-normalized table is much bigger than the normalized tables, searching a large table is still much faster than doing joins Department of Computer Science and Engineering, HKUST Slide 4

More Related