## Referential Transparency and Interchangeability

Being had spent hours trying to grasp referential transparency, I am finally inclined to regard this concept as confusing one rather than helpful. But maybe the problem is me…

Usually referential transparency is mentioned with regard to a function which can be replaced with another function by a compiler with the aim of incresing effectivenes in some way (let’s restricted ourselves to "functions" in common programming languages sense here).

So what’s the condition to be met to make possible this "replacement"? Let’s consider the line given by cody:

Referential transparency is an operational notion: it describes what happens when you evaluate a same piece of code (typically a function) several times, namely, the return value is the same. In particular, the evaluation context can generally be ignored when considering the operational semantics of a referentially transparent language.

In other words, if a couple of functions always return the same value for the same argument(s), so they are interchangeable (and if they both do not affect "external world" (have no side effects) – otherwise it is supposed a compiler will not able to make the replacement).

Question #1: Is the concept of referential transparency only applicable to pure functions? If so, why is it?

For example, in terms of some logical system (let’s refer this to a specification of some type of compiler or programming language, etc.) these two pretty non-pure functions can be considered interchangeable:

function DoSmthOdd1 (x) {    launchSpaceX(); //a side effect   var sum = K + x; // K is an external variable   return sum; }  function DoSmthOdd2 (x) {   var sum = x + K;   launchSpaceX();    return sum; } 

Our logical system is not interested in the order an expression written by a programmer and it does not get frightened when an external variable is used "in the same (specified) way", so the compiler can replace DoSmthOdd1 with DoSmthOdd2. In the other hand, the very same logical system prescribes that the compiler must not replace two pure functions (of kind of "the same argument – the same return value") if they have different algorithm complexity (for e. g.).

Let’s deem function equality to be interchangeability of two or more functions (= possibility to be replaced with each other) within the framework of a logical system. Then:

Question #2: Is referential transparency another word for conveing meaning of function equality (interchangeability) and with the only possible "the same argument – the same value" rule at that?

The issue is function equality is a binary relationship whereas referential transparency pretends to be an unary one. I can give an example of pure function (pure per se), but what is a (non-)referentially transparent function (if we consider it per se without making a comparsion with other functions)?

There is an example in the Expert Systems: Principles and Programming by Giarratano, Riley:

sum := f(x) + x; // f(x) changes the value of x passed by reference 

The authors claim this function to be a non-rf because

• Depending on how the compiler is written value of x might be the original value if it was saved on a stack, or the new value if x was not saved.
• If one compiler evaluates expressions right to left while another evaluates left to right. In this case, f(x) + x would not evaluate the same as x + f(x) on different compilers.

But wait… all the above is just comparsion of different logical systems ("compilers"). In standard algebra f(x) + x and x + f(x) are equivalent and interchangeable but in the specification of our compiler they are not. So why do we need this analogy at all?

Question #3: Is it true that a function is referentially transparent if it can be replaced in the same way how it can be done in algebra?

## Referential Integrity: Default Payment Method

Below is a fragment of an ERD I am currently working on. Would this be an acceptable ( somewhat correct ) way of representing: Default Payment Method? On my application, accounts will have the ability to specify multiple payment methods ( Bank or Card Accounts ), but there can only be one default payment method per account ( regardless of how many payment methods are there ). My initial idea was to have a flag on the PAYMENT_METHOD table:

default ENUM('false','true') DEFAULT 'false' 

I later removed the flag in favor of the DEFAULT_PAYMENT_METHOD table. I did so after realizing that I did not know of a way to enforce for each account to have only one default payment method. Using the table, I can create a unique constraint on DEFAULT_PAYMENT_METHOD.account_id to enforce uniqueness. But now I have another problem. How can I ensure that payment methods always get associated with the correct account?

Do I have to rely on the application layer strictly? Is there a mechanism to enforce such referential integrity at the database level? Thank you so much in advance!

## Ensuring referential integrity in child, group relationship

I’m not going to lie – Sql Server is not in the top 10 of my skill set, but it’s something that I have to do from time to time.

What’s more, I’m using Entity Framework, so there is a workaround required because it doesn’t yet support foreign key relationships.

In this scenario, I have 3 tables, representing some content, stacked in a ‘grouped’. relationship. I use the term grouped because a child might be in multiple groups rather than just being associated with a single parent.

TopicKeywords

A keyword can only be assigned to a one page but a page can have multiple keywords associated with it.

CREATE TABLE [dbo].[TopicKeyword] (     [Id]      SMALLINT      NOT NULL,     [Keyword] VARCHAR (100) NOT NULL,     [Volume]  INT           NOT NULL,     [PageId]  SMALLINT      NOT NULL,     CONSTRAINT [PK_TopicKeyword] PRIMARY KEY CLUSTERED ([Id] ASC),     CONSTRAINT [AK_TopicKeyword_Keyword] UNIQUE NONCLUSTERED ([Keyword] ASC),     CONSTRAINT [FK_TopicKeyword_PageMeta] FOREIGN KEY ([PageId]) REFERENCES [dbo].[PageMeta] ([Id]) );  GO CREATE NONCLUSTERED INDEX [IX_TopicKeyword_PageId]     ON [dbo].[TopicKeyword]([PageId] ASC);  GO CREATE UNIQUE NONCLUSTERED INDEX [IX_TopicKeyword_Id_PageId]     ON [dbo].[TopicKeyword]([Id] ASC, [PageId] ASC); 

The next two tables represent the parent child relationship.

TopicContent (child)

Contains keywords for a ‘Cluster’.

KeywordId must exist in the TopicKeywords table. ParentKeywordId must exist in the TopicCluster table.

Each ParentKeywordId/KeywordId combination must be unique – also, they can’t both be the same thing.

CREATE TABLE [dbo].[TopicContent] (     [ParentKeywordId] SMALLINT NOT NULL,     [KeywordId]       SMALLINT NOT NULL,     CONSTRAINT [PK_TopicContent] PRIMARY KEY CLUSTERED ([ParentKeywordId] ASC, [KeywordId] ASC),     CONSTRAINT [FK_TopicContent_TopicCluster] FOREIGN KEY ([ParentKeywordId]) REFERENCES [dbo].[TopicCluster] ([KeywordId]),     CONSTRAINT [FK_TopicContent_TopicKeyword] FOREIGN KEY ([KeywordId]) REFERENCES [dbo].[TopicKeyword] ([Id]),     CONSTRAINT [CK_TopicContent_ParentKeywordId] CHECK ([ParentKeywordId]<>[KeywordId]) );  GO CREATE NONCLUSTERED INDEX [IX_TopicContent_KeywordId]     ON [dbo].[TopicContent]([KeywordId] ASC); 

TopicCluster (parent)

Contains the cluster/parent/group entity.

The KeywordId must exist in the TopicKeywords table, and the KeywordId/PageId combination must be valid.

KeywordId must be unique.

A page (PageId) can only own one TopicCluster, so it must in theory, be unique.

CREATE TABLE [dbo].[TopicCluster] (     [KeywordId] SMALLINT NOT NULL,     [PageId]    SMALLINT NOT NULL,     CONSTRAINT [PK_TopicCluster] PRIMARY KEY CLUSTERED ([KeywordId] ASC),     CONSTRAINT [AK_TopicCluster_PageId] UNIQUE NONCLUSTERED ([PageId] ASC),     CONSTRAINT [FK_TopicCluster_TopicKeyword] FOREIGN KEY ([KeywordId]) REFERENCES [dbo].[TopicKeyword] ([Id]),     CONSTRAINT [FK_TopicCluster_TopicKeyword2] FOREIGN KEY ([KeywordId], [PageId]) REFERENCES [dbo].[TopicKeyword] ([Id], [PageId]) );  GO CREATE NONCLUSTERED INDEX [IX_TopicCluster_KeywordId_PageId]     ON [dbo].[TopicCluster]([KeywordId] ASC, [PageId] ASC); 

This is my ‘novice’ attempt. I’d appreciate any advice and suggestions for improvement.

## Doubt regarding foreign key and referential integrity

I am studying database from Database System Concepts.

The author was explaining foreign keys and referential integrity with the help of following schemas:

$$instructor(\underline{ID},\space name,\space dept\text_name,\space salary)$$ $$course(\underline{course\text_id},\space title, \space dept\text_name,\space credits)$$ $$department(\underline{dept\text_name}, \space building,\space budget)$$ $$section(\underline{course\text_id,\space sec\text_id,\space semester,\space year},\space building,\space room\text_no,\space time\text_slot\text_id)$$ $$teaches(\underline{ID,\space course\text_id, \space sec\text_id,\space semester, \space year})$$

The primary keys are underlined.

The relation $$section$$ is used so that each course could be offered multiple times, across different semesters, or even within a semester.

Foreign key was explained with the help of schemas $$instructor$$ and $$department$$. The attribute $$dept\text_name$$ in in $$instructor$$ is a foreign key from $$instructor$$ referencing $$department$$.

It need not be the case that all $$dept\text_name$$ values are referenced by $$instructor$$ relation.

Referential integrity constraint was explained with the help of $$section$$ and $$teaches$$ schema.

If a section exists for a course it must be taught by at least one instructor(it could be taught by more than one instructor)

So all the combinations of $$(course\text_id,\space sec\text_id,\space semester, \space year)$$ that appear in $$section$$ relation must also appear in $$teaches$$ relation.

But we cannot declare a foreign key from $$section$$ to $$teaches$$ since multiple instructors could teach a single section.

We could define a foreign key from $$teaches$$ to $$section$$.

Since we could form a referential integrity constraint from $$instructor$$ to $$department$$ by using foreign key but without foreign key we have referential integrity constraint from $$section$$ to $$teaches$$.

So how is referential integrity different from foreign key constraints?

Also if you know some free resources that could clear database concepts please share.