I have a greenfield project involving a SQL database that is under development. There is no production data involved yet. There is a rather long list of specifications which I expect can be implemented with a collection of SQL tables, views, functions, and procedures. The overview of the inner loop of the development process is basically the following: 1. drop and recreate the database 2. create the tables, views, etc 3. import test data 4. run tests 5. make changes to code 6. repeat beginning at (1) The development has already involved a number of refactorings. The results of these refactorings are good. However, they usually precipitate a period of [dependency hell](https://en.wikipedia.org/wiki/Dependency_hell#:~:text=Dependency%20hell%20is%20a%20colloquial,versions%20of%20other%20software%20packages.) in step (2) during which I have to manually re-resolve the order of the files defining the objects are executed. That file order is currently hard-coded as a list of file names in a script. Each file has exactly one object defined in it. The process of manually resolving dependencies works, but is labor-intensive. For C and .Net projects in the past I have used [make](https://en.wikipedia.org/wiki/Make_(software)#:~:text=In%20software%20development%2C%20Make%20is,to%20derive%20the%20target%20program.) and [MSBuild](https://en.wikipedia.org/wiki/MSBuild) to manage dependencies, respectively. I can almost imagine how to manage SQL dependencies with those tools, but I haven't seen it done and I'd rather not re-invent the wheel if there is a method that is already proven. A search of stackoverflow for [makefile](https://stackoverflow.com/questions/tagged/makefile+sql) and [MSBuild](https://stackoverflow.com/questions/tagged/msbuild+sql) with SQL didn't produce any results that were obviously on-point. Is there a best practice for resolving dependencies of SQL objects for build during development?

I have a relation schema R = {A, B, C} and the following functional dependencies: - A → B - A → C - B → A - B → C - C → A - C → B How many different minimal covers can I derive from this relation schema? I have found the following, but I am not really sure if those are all: - A → C - B → C - C → A - C → B Also, I am not sure if there is some rule on how to know if one has found all possible minimal covers. Thanks a lot for any help!

I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out **all** of the tables and columns used by a stored procedure of the user's choice. This stored procedure will have its name passed as an argument to sproc_deps. The problem is that I'm getting columns that the stored procedure doesn't actually use when I combine sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. In order to get the information I want, I've JOINed a few other things on as well: - sys.objects(for object IDs, and type_desc) - sys.tables(to match against tables contained in sys.sql_expression_dependencies) - sys.views (because I'm interested in both views and tables) - sys.columns (to pull columns for each table or view involved) Here is the actual JOIN: sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id) INNER JOIN sys.dm_sql_referenced_entities (N'dbo.DummySprocName', 'OBJECT') s ON s.referenced_entity_name = sed.referenced_entity_name Using just sys.sql_expression_dependencies nets a small list of tables that I can't really decipher, and using sys.dm_sql_referenced_entities yields a **partial** list of tables and columns used by the procedure. Is it possible for sproc_deps to return the correct list of tables and columns used by a procedure **using just T-SQL**? If so, how? --- Here is my full code as requested (still working on reproducible example for missing dependencies): CREATE PROCEDURE [dbo].[usp_v9_SprocDocInfo_FullDependency_SingleSproc] @SprocName NVARCHAR(150) = '' AS BEGIN DECLARE @ObjName NVARCHAR(128) = NULL DECLARE @rowCount INT = 0 DECLARE @HasNulls BIT = 0 DECLARE @DepExists BIT = 0 --temp table to hold output --match this against view!!! CREATE TABLE #TempData ( FullName NVARCHAR(300) not null, ShortName NVARCHAR(128) not null, TableName NVARCHAR(128), ObjectName NVARCHAR(128), column_name NVARCHAR(128), [definition] NVARCHAR(MAX), LastUpdated DATETIME, [Type] NVARCHAR(60), [object_id] INT, SprocNo BIGINT ) --temp table to hold pure dependencies CREATE TABLE #Sproc_FullTableCols ( RefEntity NVARCHAR(256), TableName NVARCHAR(256), ColName NVARCHAR(256), TypeDesc NVARCHAR(256) ) --first, grab known dependency data for this particular sproc from the correct view INSERT INTO #TempData SELECT FullName, ShortName, TableName, ObjectName, column_name, [definition], LastUpdated, [Type], [OBJECT_ID], SprocNo FROM v9_Sproc_DocInfo WHERE ShortName = @SprocName --next grab any data not covered in the previous query --this will be tables/views and ALL columns for objects found in sproc INSERT INTO #Sproc_FullTableCols --https://www.sqlrx.com/using-sys-sql_expression_dependencies-as-a-single-source-to-find-referenced-and-referencing-objects/ SELECT DISTINCT OBJECT_NAME(referencing_id) AS referencing_entity_name, CASE WHEN t.name is null then V.name when V.name is null THEN t.name ELSE NULL END, c.name, case when t.name is not null then t.type_desc when v.name is not null then v.type_desc else o.type_desc end AS referencing_desciption FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id) INNER JOIN sys.dm_sql_referenced_entities (N'dbo.' + @SprocName, 'OBJECT') s ON s.referenced_entity_name = sed.referenced_entity_name WHERE referencing_id = OBJECT_ID(N'dbo.' + @SprocName) --clean up DELETE FROM #Sproc_FullTableCols WHERE (TableName IS NULL OR ColName IS NULL)-- OR MinorRef IS NULL) WHILE @HasNulls = 0 BEGIN --pull the first row of junk data from the v9 results SET @ObjName = (SELECT TOP 1 ObjectName FROM #TempData WHERE (TableName IS NULL AND column_name IS NULL)) SET @DepExists = CASE WHEN @ObjName IN (SELECT TableName FROM #TempData) THEN 1 ELSE 0 END --see if pull was successful; if so update flag accordingly IF (@ObjName IS NOT NULL) SET @HasNulls = 1 ELSE BREAK IF @HasNulls = 1 AND @DepExists = 0 BEGIN INSERT INTO #TempData SELECT DISTINCT (N'dbo.' + @SprocName) AS FullName, @SprocName AS ShortName, @ObjName AS TableName, '-' AS ObjectName, z.ColumnName AS column_name, z.Description AS [definition], GETDATE() AS LastUpdated, '*' + (N'' + c.TypeDesc) AS [Type], OBJECT_ID(N'dbo.' + @SprocName) AS [object_id], 0 AS SprocNo FROM z9_BaseTables_Columns z INNER JOIN #Sproc_FullTableCols c ON (z.[Table] = c.TableName collate Latin1_General_CI_AI AND z.ColumnName = c.ColName collate Latin1_General_CI_AI) WHERE z.[Table] = @ObjName collate Latin1_General_CI_AI END --clean out row used for input DELETE TOP (1) FROM #TempData WHERE ObjectName = @ObjName AND TableName IS NULL AND column_name IS NULL SET @HasNulls = 0 SET @ObjName = '' END --finally, print results then discard the temp table SELECT * FROM #TempData order by [Type] asc, FullName asc, ObjectName asc, TableName asc, column_name asc, LastUpdated asc DROP TABLE #TempData DROP TABLE #Sproc_FullTableCols END This will create a procedure that grabs known dependency data for a procedure from a view (v9_Sproc_DocInfo) that uses only sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. Then I pull the full list of columns for any table used by the procedure, filter out anything already contained in v9_Sproc_DocInfo, and combine this data with the view data as output.

Simplified example of a data modeling problem that I have: I have a domain model with an image table, a camera table, a machine table, a foreign key constraint between image and camera (taken_by_camera), and a FK between camera and machine (installed_into_machine). image taken_by_camera camera camera installed_into_machine machine All of this is based on the data, we get from customers. However, in our application we utilize the data almost always in terms of sets of images. So, we also have a set table. Each image belongs to a set and for that we have a belongs_to_set FK on the image table. image belongs_to_set set My problem is that I would like to restrict the sets on containing images of a specific machine only, e.g. set restricted_to_machine machine With just these FKs in place, I have no constraint that ensures that all images that are connected via camera to a specific machine also end up being connected to the same machine via the set. image_1 taken_by_camera camera_1 camera_1 installed_into_machine **machine_1** image_1 belongs_to_set set_1 set_1 restricted_to_machine **machine_2** How can I model something like this (logically but also in postgres). PS: It should be possible to have images taken by different cameras in a set as long as these cameras are installed in the same machine.

Suppose we have a relation R(A, B, C) with the FD A -> B. Let's say that, for two tuples t and u in R, if both t[C] and u[C] satisfy a boolean condition - say less than a constant C - then t[B] = u[B]. Does this constitute a functional dependency? On a more practical sense, how to normalize this relation and eliminate the clear redundancy in R(A, B, C) and allow for lossless joins?

if AB -> C and B -> A does that mean B -> C? B -> B is trivial and if we do composition with B->A, it becomes (B-> BA), then by transitivity does that mean B->C? but if B -> C is correct, it contradicts with the fact that B needs A to determines C

I have several SQL Server (2008 forward) and Oracle (12c forward) legacy databases running on a server... I know for a fact that various stored procedures point to other databases, like select * from OtherDB.mySchema.Products, which makes a database dependent on OtherDB... Is there a easy way to map this "interdependencies", ideally generating a chart?

I have a relation: R4 = {{T,U,V}, {T → U, U → T, T → V}} I know from looking at the answer key that this relation is in BCNF. I'm going through the process of rigorously determining what normal form the relationship adheres to. It's clear to me why the relationship is in 1NF and 2NF, and if I assume it's in 3NF, BCNF follows easily. However, the definition of 3NF states: >Every non-prime attribute is non-transitively dependent on every candidate key in the table. But, as far as I can tell, both {T} and {U} are candidate keys of the table, and {V} is thus transitively dependent on {U}. There is an alternate definition of 3NF available on wikipedia: >A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds: >* X contains A (that is, X → A is trivial functional dependency) * X is a superkey * Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each column in A-X is contained in some candidate key) And the relation is clearly in 3NF by this definition (all of the functional dependencies are covered by "X is a superkey"). So why the discrepancy? How am I misapplying the definition? Please don't give me shortcuts that give me the answer in a way I don't want, unless you also help me understand why my application of 3NF (as described) is inaccurate.

> A relation is in 3NF (Third Normal Form) if it is in 2NF (Second Normal Form) and all the FDs (functional dependencies) in the relation are in the form X -> Y, where X is a superkey (a set of attributes that uniquely identifies a tuple in the relation) and Y is a non-prime attribute (an attribute that is not a part of the primary key).

SP (Sno, Sname, Pno, Qty)

Functional dependencies:

Sno, Pno -> Qty
Sname, Pno -> Qty
Sno -> Sname
Sname -> Sno

The candidate keys are Sno, Pno and Sname, Pno. So the first 2 are in the form quoted above while the last two aren't. For instance in the case of Sno -> Sname, Sno is not a superkey and Sname is a prime attribute. So why is the relation in 3NF?

i have this query to get all sp's and depending(nested) sp's on those sp's. I need to create all these procedures on another database . Any idea how to get them in the correct order. select distinct procs.NAME AS ProcedureName ,OBJDEP.NAME as DEP_ProcedureName_NAME FROM sysdepends INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID inner join sys.procedures procs on sysdepends.id = procs.object_id where OBJDEP.type='P' AND OBJ.type='P'

I believe that 2nd and 3rd normal form definitions could obviously be specified with less complexity, but they're not. The definition found in textbooks is something like this: **2NF: No partial FD between prime and non-prime attributes.** This means there is no such situation as:

-> B -> C

where

is a key,

its subset, and

a non-prime. As

-> B

always holds for every B, this could be simplified by removing

-> B

to get

-> C

, or: **2NF-new: No FD between a subset of a key and a non-prime.** and, **3NF: No indirect FD between a key and non-prime attributes.** This means there is no such thing as:

-> NP1 -> NP2

. Again, as

-> X

for every X, this could be simplified to get

-> NP2

leading to: **3NF-new: No FD between two non-primes.** However, I have not seen this simplified version anywhere, except > Equivalently, a transitive dependency exists when a nonprime > attribute determines another nonprime attribute. What makes me is more doubtful is the word *typically* in > A nonprime attribute determines another nonprime attribute. Here we > typically have a transitive dependency that violates 3NF. By Elmasri and Navathe. So why not *always*? Are my definitions wrong? If not, why are they not used?

I'm having trouble understanding the logic in use for identifying functional dependancies. Looking at the sample relation below, I understand fd1 - fd3. But when I look at fd4 and fd5, its logic makes me believe that fd6 and fd7 would also be possible, but it's not according to the book I'm studying. The logic that operates fd4 and fd5 is to me: > We conclude that unique combination of values in in columns A and B such as (a, b) is associated with a single value in column E, which in this example is "q". In other words attributes (A, B) functionally determines attribute E, and this is shown as fd4 in the sample relation. We also conclude that attributes (B, C) functionally determine attribute E using the same reasoning described earlier, and this functional dependancy is shown as fd 5 in the sample relation. So why is fd6 and fd7 not true? 

If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?

R(A,B,C) where {AB -> C , C -> A}. The candidate keys are {A,B} and {C,B}. This is in 3NF but not BCNF because of {C->A} . Now it seems like this can't even be split into a lossless BCNF, let alone a dependency preserving one. Is there some way to prove it? Is there some result that says that if the result of the 3NF algorithm does not give you a BCNF decomposition, then a further BCNF decomposition is not possible? Edit: I have since figured out that every schema can be split into a lossless BCNF one. It is preserving dependency that is uncertain. Here the lossless decomposition would be R(B,C), R(C,A)

The definition in my book says: > *The set of all functional dependencies logically implied by F is the closure of F.* My question is, do we include FDs in F in F closure as well ? If I have: - F = {A→B, B→C} - a) F+ = {A→C, A→BC, A→B, B→C} - b) F+ = {A→C, A→BC} Which one would be correct a) or b) ?

If A ↠ B then AC ↠ B is it true or false and if so can have example why?

Thats the ERD. I'm studying for an upcoming test and having trouble getting my head around converting this ERD into dependency diagrams. This is from a previous exam that the Lecturer told us to study just btw so he said it will be pretty similar to this. We have to - - Convert the ERD into a dependency diagram. - And then, convert that diagram into a 3NF model, with no transitive or partial dependencies. Any help would be much appreciated as I'm struggling to get my head around it and need to pass this test :/ Thanks in advance!

> 2. [5 Marks] Let R(A,B,C,D,E) be decomposed into relations with the following three set of attributes {A,B,C}, {B,C,D}, and {A,C,E}. For each of the following sets of FD's, use the chase test to tell whether the decomposition of R is lossless. For those that are not lossless, give an example of an instance of R that returns more than R when projected onto the decomposed relation and rejoined. > > a. A→D, CD→E and E→D. > > b. A→D, D→E and C→D. For the question above, my work for each of the questions is below the concerns. Here are my main concerns: 1. Does the order in which you use the relations matter? 2. Can you end up with less tuples with the chase test? 3. Is my approach correct? -----------------------Part A below---------------------------------- InitialTableau = T₁ ⋈ T₂ ⋈ T₂

+----+----+----+----+----+
| A  | B  | C  | D₁ | E₁ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₂ | E  |
+----+----+----+----+----+

-----------------------ANSWER TO QUESTIONS START NOW----------------------- a) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.

+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₁ | E  |
+----+----+----+----+----+

- Since we do not have an unsubscribed row, this relation is lossy/not lossless. Example of an instance R (Were going to use the final tableau): R₁(A,B,C)

+----+----+----+
| A  | B  | C  |
+----+----+----+
| A₂ | B  | C  |
+----+----+----+
| A  | B₂ | C  |
+----+----+----+

R₂(B,C,D)

+----+----+----+
| B  | C  | D₁ |
+----+----+----+
| B  | C  | D  |
+----+----+----+
| B₂ | C  | D₁ |
+----+----+----+

R₃(A,C,E)

+----+----+----+
| A  | C  | E₂ |
+----+----+----+
| A₂ | C  | E₂ |
+----+----+----+
| A  | C  | E  |
+----+----+----+

After Joining the above relations, we get:

+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D₁ | E₂ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

- **since we have 2 more rows than the original tableau, this decomposition is not lossless.** b) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.

+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E  |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

- Since we have an unsubscribed row, this decomposition is lossless.

I'm having trouble identifying the combination of attributes which make up Functional Dependencies(FDs) of a particular table. While I understand how Normalization process works when given the FDs, I'm unable to translate and identify FDs for a given table. I've been tasked to create a database of a company which tracks all expenses of sales/packages etc. Naturally that means storing Customer information for the company to identify all consumers.

CREATE TABLE Customers (
    id        integer primary key,
    address   text not null,
    name      text not null,
    email     text unique not null,
    phone     text unique not null,
    unique(name, address)
);

I've chosen to represent the info for the customer table as such, given that each phone number and email is uniquely tied to the customer. The unique constraint allows for multiple people from the same household address to be present in the table. I want to see if I can normalize this table further, however I am unable to identify the attributes to determine the FDs needed.

In an exercise I have the following text which should be translated into a functional dependency: "A module realizes a business case and is developed by an author" My reasoning is that the business case and author exist before the module therefore the author and business case determine the module: author, business case -> module However my script gives the solution: module -> author, business case Can somebody help why the latter is true and not the former?