A functional dependency is exactly what the term implies - the output of the function is always determined by the input. If for example we have a function f(), and provide variable x, and we always receive output y, then y is functionally dependent on x. You can think of this like a simple graphing function
2x + 1 = y
Plugging some sample values into the ...
You're right on the money with the possible candidate keys, vikkyhacks. Overlapping candidate keys are composite (consist of more than one attribute) candidate keys with at least one attribute in common. So your overlapping candidate keys are NM and NO (they share N).
Additional explanation of the above, originally left in comments:
All overlapping candidate ...
By default, the error message tells you more. Like:
ERROR: cannot drop table tbl because other objects depend on it
SQL state: 2BP01
Detail: view v_tbl depends on table tbl
Hint: Use DROP ... CASCADE to drop the dependent objects too.
Include the object mentioned in the Detail line in your script.
Also, to do it safely, wrap all of it in a ...
Why the dropped index is still there?
In fact that index is no longer there, the column is the one still there and what was once a Clustered Index is now a Heap.
Did I got the dependency error above because of this still-existing index?
No, because the index no longer exists.
If not, is there something else I should check? I'd like to avoid
dropping the ...
but the index names are NULL (and index id is 0)
That's because they are heaps. BOL Reference on sys.indexes
One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme. Then once you have those tables off of that partition scheme you should be able ...
There are so many things outside of SQL Server's metadata that may depend on the size of a column, it's not even funny. Here are a few things you'll need to check:
Any variable or parameter declarations in stored procedures, dynamic SQL or ad hoc SQL that may pass values to that column, filter on it, search from it, or get assigned from it.
Any references ...
A candidate key is a set of attributes that constitute a minimal superkey. Two candidate keys, A and B, are said to overlap if they have some attributes in common, i.e.: A ∩ B is non-empty. In your case, MN and NO would be overlapping candidate keys in R.
Because of the minimality (irreducibility) requirement one candidate key can never be a subset of ...
I don't think there's a surefire method to find everything. After all, they could have access to things merely by virtue of being in a specific server or database role, or even a Windows AD group (you didn't specify if this is a SQL auth login or a Windows login). There also isn't a surefire way to identify what might break if this is changed - for example, ...
Under the assumptions that you have some columns (say x and y in your example), that you don't know if they are functionally equivalent or not - and that these columns do not have any NULL values (which would complicate things), you can use:
u, v, w, -- the grouping columns
AVG(z) AS z_avg, -- the non-...
The last two lines are an abbreviated way of solving the problem without recurring to the complete algorithm to check for dependency preservation. In particular the teacher noted that combining the dependencies that you can obtain from R1 and R3 you cannot obtain C, which is essential to get A in the dependency BC → A. This dependency can never be derived ...
Since creating an indexed view is essentially creating a view WITH SCHEMABINDING you can no longer execute any DDL statements against the underlying objects which would affect the view definition. In this case TRUNCATE (a DDL operation) is viewed as view-affecting.
Detecting any queries that would break when you create views with schemabinding would equal ...
No, that is not true.
Armstrong's axioms are a sound and complete axiomatization of the logical implication for functional dependencies.
Here is a relation, where the functional dependency XZ → YZ holds:
X Y Z
x1 y1 z1
x2 y1 z1
x1 y2 z2
x2 y2 z2
But the functional dependency X → Y does not hold, as the tuples x1 y1 z1 and x1 y2 z2 show. ...
I think it follows from the composition property, which says that if A → B and C → D, then AC → BD. If D = ∅ then you arrive at the desired result.
This applies to multivalued dependencies as well: If A ↠ B and D ⊆ C, then AC ↠ BD, which in the case of D = ∅ devolves into AC ↠ B.
Here is a possible derivation:
1. A → CD (given)
2. C → BE (given)
3. CD → BED (by augmentation of 2 with D)
4. A → BED (by transitivity of 1 and 3)
5. B → F (given)
6. B → BF (by augmentation of 5 with B)
7. BED → B (by reflexivity)
8. A → B (by transitivity of 4 and 7)
9. A → BF (by transitivity of 8 and 6)
You are correct, the relation is in 2NF, 3NF, BCNF.
The reason is that the relation has two keys, A and CD.
So the relation is in BCNF (which is a property stronger than 3NF and 2NF) since each determinant of the minimal conver of R1 is a key. Here is one minimal cover:
A → C
A → D
C D → B
C D → A
Views are implemented as table plus rewrite rule in Postgres. Details in the manual, chapter Views and the Rule System.
The tricky part: not the view itself depends on involved functions, only the rewrite rule does. So this query should be your solution:
SELECT r.ev_class::regclass AS view, d.refobjid::regprocedure AS function
FROM pg_rewrite r
I would model this using a single table for the Professions, and a related table for Specializations.
DROP TABLE IF EXISTS dbo.Users;
DROP TABLE IF EXISTS dbo.Specializations;
DROP TABLE IF EXISTS dbo.Professions;
CREATE TABLE dbo.Professions
ProfessionID int NOT NULL
PRIMARY KEY CLUSTERED
Since each dependency implies itself (given the fact that in logic trivially every assertion implies itself), F is included in F+. But note that in your example F+ contains quite a large number of dependencies, in addition to the dependencies that you have listed, like for instance A→A, AB→C, B→B, AC→A, etc.
The algorithm to compute the minimal cover of a set of dependencies requires that, when there are multiple attributes on the left part, we should eliminate the extraneous ones, that is the attributes such that the remaining ones still determine the right part.
In other words, if you have a dependency like BCDE → A, you should in turn try to eliminate each ...
C is a non-prime attribute (not part of the candidate key), and C depends on (A,B), which is a proper subset of the candidate key.
So the table is not in second normal form by definition.
Since BCNF implies 3NF and 3NF implies 2NF, this implies that the table is not in BCNF.
Try this and let me know.
I added one more cte selecting from recurse to add a ROW_NUMBER() over (partition by depentent_type order by depth, row) as rn_type and added at the bottom WHERE ((depentent_type = 'USER_TABLE' AND rn_type = 1) OR depentent_type <> 'USER_TABLE')
The first, you should read the NORMALIZATION concepts (1NF,2NF,3NF,...) after that you can use them to verify your dependency diagram. So, I'm talking about some basic steps to help you convert ERD, which I often do:
1- Identify objects (objects are WHO, WHERE, WHAT, WHEN ), you imagine that they are exited
and can be defined.
2- Identify natural keys of ...
Given that you can not afford to drop and recreate the table, this related answer would be a better fit:
Best way to populate a new column in a large table?
You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable).
And all the general advice for performance optimization applies.
There is not much more ...
A,B and C are sets of attributes.
All examples I've seen in books are like Employee ↠ Project |
Dependent, where Employee simultaneously determines two attributes.
A ↠ B | C is a special notation different from A ↠ BC. It's useful because MVDs always come in pairs. It says that both A ↠ B and A ↠ C hold. This implies that the relation equals AB JOIN AC....
The important thing about the 2NF is that in each (non trivial) dependency the determinant should not be a proper subset of a key. In the example, the determinant of AB->C is the full key, while the determinant of C->D is C, which is no part of any key. So the schema is obviously in 2NF.
When you have a relation R with a set of functional dependencies F and a decomposition of R in R1...Rn, you must consider two different concepts:
The closure of the set of functional dependencies F. This closure, called F+, is the set of all the dependencies derived from F, by applying, until possible, a set of rules called “Armstrong’s axioms”. This set ...
Does the relation create information redundancy?
Yes, for instance you have the information on the file (name, size, data creation, etc.) repeated for different accesses to the same file.
What would a decomposition of R without loss of information and without loss of dependency be?
The following decomposition is both in Third Normal Form and in Boyce-...