Relational Database Design
6 important questions on Relational Database Design
Consider the following statement:
"Let R be a relation schema, and let α ⊆ R and β ⊆ R."
What does ⊆ mean and what is the connection of α and β to R?
- Subset of.
- ⊆ Means that the variable on the left is a subset of the variable on the right.
- Subsets α and β.
- α and β are both sets of attributes that belong to the same relation R.
- R is a relation schema, and therefore represents a set of all attributes.
- α and β are subsets of R and have some of the attributes of R.
- I.e., all attributes in α and β always have to be in R as well.
You have a set of functional dependencies that we call F.
What is its closure ( F+ ) would contain?
- Closure.
- All functional dependencies that can be logically derived from the FD set F, without being explicitly stated.
- When you have a set of FDs, they can imply other dependencies that you could logically derive from F.
- E.g., F contains A → B and B → C.
- We can logically infer that this means that A → C.
- This FD would be part of F+.
What is a canonical cover?
- 'Cleaned-Up' F.
- A canonical cover ( Fc ) is a simplified version of a set of functional dependencies ( F ).
- It differs from 'F' in that all redundant information is removed.
- Every FD in 'Fc' and every attribute in it is necessary to preserve the same meaning as the original 'F'.
- F+ = Fc+.
- The closure of Fc is equal to the closure of F.
- 3NF.
- Canonical covers are used in the 3NF decomposition algorithm.
- Higher grades + faster learning
- Never study anything twice
- 100% sure, 100% understanding
For my 1st exam, I got a C. Then, I started looking for ways to learn better, and I came across Study Smart. Since then, I have scored an A+, A, A-, and another A. I highly recommend it to everyone who wants to hear it. I am so happy with it!!
I was struggling to finish all my first-year subjects for three years. Then I discovered Study Smart, which helped me to complete all of them within three months.
Because of StudySmart, things are going well in many ways. Before, I always had study stress and no time for anything. Now I feel calm and confident (because of the program). I used to score a B, and now I usually score an A or A+.
Hey Chris, I really want to thank you very much for developing the platform, I usually got around C for my exams, now I scored an A+, an A and a A- !!
Since using Study Smart, my average has increased significantly. I even completed a statistics course with an A+ while I'm bad at statistics. I took on extra classes this year because of Study Smart. Thanks so much!
Ever since I started studying with StudySmart, I have passed all my exams! No more re-sits! At first, I was happy with a pass, and now I only get good grades. Many thanks!
I aced all the courses I studied with Study Smart. For law, I first scored a D, and now an A! I’m 100% sure that I’ll pass all the courses I study with Study Smart.
I always thought I studied well. My grades were fine. I could never have imagined that applying these study skills would have such a huge and direct impact on each stage, my speed, and the pleasure I have in learning.
For the first time, I finally feel confident about the way I learn. I have the perfect overview and have made significant progress in terms of speed and the grades I get. Thanks, Chris!
I immediately started to enjoy learning again, and my grades soared. All of a sudden, everything went better. I highly recommend Study Smart to all students..
Being able to create my materials online made the process of studying so much smoother and quicker. I no longer have to spend a considerable amount of my time developing my summaries.
At first, my notes were a mess. Now I use Study Smart to take my notes in a perfectly structured way. It helps me to save a lot of time and do other things I enjoy.
Students that study with Study Smart get better grades. The system applies all the learning methods so that both well-performing students and students who struggle perform much better.
Hi Chris, I would like to thank you very much. Last year I scored C on a course, and this year I got an A+ thanks to your method! Thank you very much!
The most considerable effect is that my child is more enthusiastic about school! Every parent wants their children to be happy at school. Study Smart made it happen.
In high school, I failed several exams, and that has really changed now. I actually needed this tool very much back then. Since I started studying with StudySmart, I haven't had any Ds. My average score is now an A, and those are results I would have never had in the past.
With my old method, I passed only 3 out of 8 courses. However, since I started taking my notes digitally in Study Smart, I have passed all my lessons on the first try. For me, StudySmart takes away the stress of wondering if I will give or not.
Thanks to StudySmart, I passed all my exams and with better grades than before! On top of that, I have mastered a perfect study method now, which I am confident will help me earn my degree.
How do BCNF and 3NF differ on the following features:
Redundancy.Lossless-Join.Dependency Preservation.Practical Choice.
- Redundancy.
- BCNF.
- Eliminates all redundancy discoverable via FDs.
- 3NF.
- Allows some redundancy if a non-key attributes determines a prime attribute.
- Lossless-Join.
- Both BCNF and 3NF decompositions are always lossless-join.
- Dependency Preservation.
- BCNF.
- Decomposition may not be dependency-preserving.
- 3NF.
- Decomposition is always dependency-preserving.
- Practical choice.
- BCNF.
- Often preffered: Because SQL doesn't easily enforce FDs that are not keys, dependency preservation is less valuable in practice.
- 3NF.
- Chosen when efficient checking of all FDs without joins is a critical requirement.
Sometimes, a schema can be in BCNF but still contain redundancy. Can you describe such a situation in which BCNF still contains redundancy.
- E.g., an 'instructor_info' relation schema with attributes (ID, child-name, phone_number.
- An instructor can have multiple children and multiple phone numbers.
- These 2 sets of values are also independent.
- To represent the data correctly in BCNF,
Normalisation of a a database design gives you clean, redundancy-free data. However, in some cases, normalisation rules are intentionally
violated by introducing redundancy — a process called denormalisation.
Can you explain why database designers would do this?
- Slow joins.
- Normalisation may give you many small tables that must be joined together to answer common queries.
- These joins can be slow when you work with large databases.
- E.g., To show all student grades you need to join relations:
- Students, course, Enrolment, Instructor, and Department.
- Creating Redundancy.
- You combine relation tables or store repeated information.
- This makes those queries a lot faster but because of the redundancy updates become risky.
- E.g., if a course's name is altered, you must update it in multiple rows.
The question on the page originate from the summary of the following study material:
- A unique study and practice tool
- Never study anything twice again
- Get the grades you hope for
- 100% sure, 100% understanding
