Skip to main content
Share Your Experience: Take the 2024 Developer Survey
11 votes

How to safely replace the old table with its cleaned copy?

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 ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Why a dropped index is still there, and does it prevent a table from being altered?

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 ...
Ronaldo's user avatar
  • 5,091
4 votes

Dependency preserving decomposition

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 ...
Renzo's user avatar
  • 4,080
4 votes
Accepted

T-SQL to check whether or not a indexed view can be created on tableX?

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 ...
Tom V's user avatar
  • 15.7k
4 votes

Is the inverse of Armstrong's Axioms valid as well?

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 ...
miracle173's user avatar
  • 7,682
4 votes
Accepted

Why is this relation not in Boyce-Codd Normal Form (BCNF)?

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 ...
Laurenz Albe's user avatar
4 votes

How can I prove / disprove If A ↠ B then AC ↠ B

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 ...
mustaccio's user avatar
  • 26.1k
3 votes
Accepted

Question concerning minimal/cannonical cover

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 ...
Renzo's user avatar
  • 4,080
3 votes
Accepted

Is F included in F+?

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 ...
Renzo's user avatar
  • 4,080
3 votes
Accepted

Is the inverse of Armstrong's Axioms valid as well?

No, the inverse is not true. The assumption: if XZ → YZ then X → Y is false. You could have only XZ → Y for example, from which you can infer XZ → YZ and a few others. But not X → Y.
ypercubeᵀᴹ's user avatar
3 votes
Accepted

Nullable, dependent attributes

I would model this using a single table for the Professions, and a related table for Specializations. USE tempdb; DROP TABLE IF EXISTS dbo.Users; DROP TABLE IF EXISTS dbo.Specializations; DROP TABLE ...
Hannah Vernon's user avatar
  • 70.1k
3 votes
Accepted

How to safely recreate a MS SQL table that has other objects depending on it?

The simple way around this is to change some options in Management Studio. Go to Tools, Options, Designers, Table and Database Designers. Uncheck "Prevent saving changes that require table re-...
db2's user avatar
  • 9,658
3 votes
Accepted

Get list of functions used in a view

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 ...
Erwin Brandstetter's user avatar
3 votes

checking the normal form of the current table

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 ...
Renzo's user avatar
  • 4,080
3 votes
Accepted

How to prove "A->B,C then A->B and A->C" from Armstrong Axioms

Just two steps: you can use reflexivity for B C -> B and then transitivity from A -> B C and the previous dependency. Analogous solution for the second case.
Renzo's user avatar
  • 4,080
3 votes

Functional dependencies proving method

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 ...
Renzo's user avatar
  • 4,080
3 votes
Accepted

all the dependants of a stored procedure or function recursively - but shown only once

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 ((...
Scott Hodgin - Retired's user avatar
2 votes
Accepted

Redundancy of information and algorithm to do lossless decompositions

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 ...
Renzo's user avatar
  • 4,080
2 votes
Accepted

Applying second and third Normal Form

The only candidate key of your relation is {D DA HA L NF} (perhaps with R you mean D?) You can verify this by calculating the closure of those attributes, {D DA HA L NF}+, and seeing that it contains ...
Renzo's user avatar
  • 4,080
2 votes

Identifying functional dependencies

Given C --> A, adding an attribute to left-hand side would result in a true functional dependency: C D --> A. Perhaps your book was trying to say that C D --> A was not part of the minimal ...
user248822's user avatar
2 votes
Accepted

Transitive dependency with foreign keys

While I would say you are definitely correct, and this was the correct design, your tutor may be technically correct (it's difficult to tell, especially, if you're translating form another language - ...
RDFozz's user avatar
  • 11.6k
2 votes

Equivalent functional dependencies

the answer provided at the bottom of that page states that F1 is not equivalent. That is correct. F2 and F3 are equivalent but F1 is not. F2 is equivalent to F3 because from A → C we can infer that ...
ypercubeᵀᴹ's user avatar
2 votes

How to safely recreate a MS SQL table that has other objects depending on it?

You can modify the ntext columns to nvarchar(max) using T-SQL. I'd only recommend doing this if the table/database is not actively being used. If the table is very small, this method will be quite ...
Hannah Vernon's user avatar
  • 70.1k
2 votes
Accepted

What a closure `F` of a family of functional dependencies is?

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. ...
Renzo's user avatar
  • 4,080
2 votes
Accepted

Does the definition of functional dependency permit derivative values?

I found an excellent blog post by Ken Downs that clarifies everything: Database Skills: Third Normal Form and Calculated Values Basically, the tables above aren't 3NF on account of the derived ...
user107157's user avatar
2 votes

Trick question about FD's?

I see several non trivial dependencies (9 if I count right!) How to find them: Since, the relation has few attributes, we can examine each possible dependency separately, taking the respective ...
ypercubeᵀᴹ's user avatar
2 votes
Accepted

Can anyone help me understand the following paragraph?

I do not know if the following can be useful to explain the involuted phrase that you cited, however all the following facts are true. A relation must have at least a candidate key and can have more ...
Renzo's user avatar
  • 4,080
2 votes

Show that functional dependency pseudotransivity holds using closure test

The definition of Pseudotransitivity given above is equivalent to say the following. From: (1) A1 A2 ... An -> B1 B2 ... Bm (2) B1 B2 ... Bm E1 E2 ... Ej -> D we can derive: (3) A1 A2 ... An ...
Renzo's user avatar
  • 4,080
2 votes
Accepted

list of views that depend on a given table and column

SQL Server provides a system view, sys.sql_dependencies, which maps the dependencies consumed by objects such as views. So, to view all the tables and columns associated with the views in your ...
Hannah Vernon's user avatar
  • 70.1k

Only top scored, non community-wiki answers of a minimum length are eligible