Welcome to
Database Management Systems IITR 2018
25 Nov 2018 - 11:59pm
##### Course Project Submission Part 2 / Final

Please submit your project report (.pdf) along with your database dump (.sql) inside a single .zip file by 25th November 2018 midnight. Your project report should contain both part 1 and part 2 of your work including ERD, Schema diagram, Database Design, Normalization, SQL (both DDL and DML) to conduct main functionalities of the assigned website, Result screenshots, and any relevant details as may needed to explain your work done.

Submit here
##### Recently, Bob the Builder coded up a Relational Query Language Transla...

Recently, Bob the Builder coded up a Relational Query Language Translator (RQLT). The RQLT is simple and only operates on a single schema as given below:
Tool(tid, brand, cost)
Toolbox(tbid, location)
Holds(tbid, tid

(a) Bob sets the RQLT to translate the following SQL query to Relational Algebra:
SELECT T.tid
FROM Tool T, Holds H, Toolbox B, Jobsite J
WHERE T.tid = H.tid AND H.tbid = B.tbid
AND B.location = J.location AND J.task = `Plumbing'}
What will be the equivalent Relational Algebra query?

(b) Bob switches the RQLT to translate Relational Algebra into Tuple Relational Calculus and passes in:

What will be the equivalent Tuple Relational Calculus Query?

(c) Bob then translates the following Tuple Relational Calculus query:

What will be the equivalent SQL Query?

*Point out if you find some mistake.

Vinayak Aggarwal 23 Nov 2018 09:37 am
In a), don't we have exactly replicate the query? I mean the SQL query wants to find the Cartesian product and then reduce the output table based on the given conditions.
Ranita Biswas 25 Nov 2018 10:59 am
No need to replicate, you just need to find equivalent which means producing same result on same database instance.
##### Consider the following B+ tree index of order 3:(a) Assume we ...

Consider the following B+ tree index of order 3:

(a) Assume we modify the B+ tree by adding the following keys in the

following order: 20, 27, 18, 30, 19. Show every steps to insert these five key values one after another into the initial tree. Assume that we split up a node after insertion of the new element, and the left node gets one more than the right.

(b) Suppose we were to insert all integers in the range 26 to 4112 inclusive (i.e. 26, 27, 28 ... , 4111, 4112) into the initial tree given in the figure, one at a time. At most how many levels would the resulting B+-tree have? (Hint: You should not need to draw a B+-tree to figure this out!)

*Point out if you find some  mistake.

Harsh Jain 23 Nov 2018 04:43 pm
Ma'am, in part (b) I considered level of a tree with single node as 1 which gave me the answer 12 levels
Sakshi Goyal 25 Nov 2018 03:05 am
I have done the same, Ma'am.
Ranita Biswas 25 Nov 2018 10:57 am
It’s absolutely fine. In fact it’s 0th level to 11th, so count is 12.
##### (a) Your cousin, who is studying at a Bay Area university often noted ...

(a) Your cousin, who is studying at a Bay Area university often noted for its sports programs, claims to have learned "Lance's Axioms" for reasoning about Functional Dependencies. He claims that one such axiom, is
Reversitivity: if A → B then B → A
Give an example relation instance that proves that Reversitivity is invalid. (for simplicity, use integers and/or single letters as your attribute values)

(b) After viewing your counterexample for part (a), your cousin launches into a review of the final scores of the "Big Game" over the past decade. He then claims that there is another axiom:
Kindatransitivity: if A → C and AB → C then B → C
Give an example relation instance that proves that Kindatransitivity is invalid.

(c) It turns out however, that your cousin almost got it right. In fact, there is a rule as follows:
Pseudotransitivity: if A → B and BC → D then AC → D
Show that Pseudotransitivity is in fact valid. Be sure to show the steps for your derivation.

(d) Why can't you use an example relation instance to answer part (c), as you did for parts (a) and (b) of this question?

(e) Consider the relation schema R(ABCDE) and the following functional dependencies on R: A → D, BC → E, D → AB
What are the candidate keys of R and what is the highest normal form of R. Explain.

*Point out if you find some mistake.

##### Consider the attributes A B C D E F G which have the following functio...

Consider the attributes A B C D E F G which have the following functional dependencies: AD → F, AE → G, DF → BC, E → C, G → E

(a) List all the candidate keys (not superkeys).

(b) Which of the following dependencies are implied by those above: G → C, A → G, ADF → E, ADC → B, AGF → E

(c) Consider the decomposition into 4 relations: (ADF) (CE) (EG) (ABDG). What is the highest normal form of this decomposition? Is this decomposition dependency preserving? Is it lossless? Justify your answer.

(d) Consider the decomposition into 3 relations: (ADF) (EC) (ABDEG). What is the highest normal form of this decomposition? Is this decomposition dependency preserving? Is it lossless? Justify your answer.

*Point out if you find any mistake.

DIVYANSHU SAXENA 21 Nov 2018 02:26 pm
b) only these two FDs implied: ADC → B, AGF → E
c) (ADF) is in BCNF; (CE) is also in BCNF; (EG) is in
BCNF and(ABDG) is in 2NF as here partial
dependency exist.
It is lossless but not dependency preserving
Vinayak Aggarwal 22 Nov 2018 08:42 pm
For the c part, if you consider the closure of the dependencies given, then AD determine B (which is not fully functional dependent on the key ADG) hence ABDG should be in 1NF.
##### Draw an E-R diagram for the following situation: - This is a simplifi...
Draw an E-R diagram for the following situation:
- This is a simplified model for reserving baseball tickets.
- There are teams, which are identified by the team name. Teams are also located in a city.
- Teams play with each other in games, which occur on a particular date at a particular time. Games are identified by a game ID, and each game has exactly two teams that play in it.
- A game is played in exactly one stadium.
- A stadium is identified by its name, and is also located in a city.
- Stadiums have seats, which have a section number, a row number, and a seat number.
- Ticket holders reserve seats for a game. Ticket holders are identified by their name.
- Some ticket holders are students (students get discounts, but we are not including that in the model).
Using SQL, convert your designed ER diagram to the relational model.
Discuss the solution in the comments section.
##### Consider the following relational schemas:DEPT(dname, location)STU...

Consider the following relational schemas:
DEPT(dname, location)
STUDENT(name,regno,gpa,level,dept)
COURSE(cno,cname,dept)
TAKE(regno,cno)

Write down the relational algebra (RA) and tuple relational calculus (TRC) for the following queries.

(a) The names of the students who take exactly the same courses as Rosie takes.

(b) The names and departments of the students who take the largest number of courses among students in their departments. Students without a department should not be considered.

(c)

Vipul Jain 19 Nov 2018 06:03 pm
Ranita Biswas 19 Nov 2018 07:06 pm
Samagra Sharma 20 Nov 2018 09:13 am

@ranita mam. The first solution(TRC) seems a bit incorrect. It will give me all those students whose courses are a superset of Rosie's courses. I guess a bi-implication is required for the "exactly same" clause.

Ranita Biswas 20 Nov 2018 09:39 am
Thanks Samagra. Yes, you are right! I completely ignored the exactly same part. Replacing the implication by double implication will serve the purpose.
Ranita Biswas 20 Nov 2018 09:45 am
And v should also have forall clause I think. And in the RA, we should go with subtraction, rather than division.
Samagra Sharma 20 Nov 2018 10:46 am
Yes mam. Division will have extra tuples

I am not clear where $\forall$ should come.
I guess I am missing something.

Ranita Biswas 20 Nov 2018 10:52 am
For single implication as we were taking forall the courses taken by Rosie, similarly while using double implication, we should consider forall the courses taken by the other student.
Samagra Sharma 20 Nov 2018 11:08 am

Ohh Okay. Thanks a lot mam. There are a lot of subtleties.

Ranita Biswas 20 Nov 2018 11:32 am
Yes, indeed there is. I am teaching this for last 3 years, and still get confused a lot.
##### Consider the following relational schemas:employee(person-name, st...

Consider the following relational schemas:
employee(person-name, street, city)
works(person-name, company-name, salary)
company(company-name, city)
manages(person-name, manager-name)
Write down the relational algebra (RA) and tuple relational calculus (TRC) for the following queries.

(a) Find the names of all employees who live in the same city and on the same street as do their managers.

(b) Find the names of all employees in this database who do not work for State Bank of India. Assume that there may be information in the database about people who do not work for any company right now.

(c) Assume the companies may be located in several cities. Find all companies located in every city in which HDFC Bank is located.

VEMULA SAI SRI VATHSA 19 Nov 2018 01:34 pm
Ranita Biswas 19 Nov 2018 01:52 pm
Yes, let me check and update the marks first; then I will upload the answers, by 8pm or so it should be done. Will notify.
Vipul Jain 19 Nov 2018 06:00 pm
Vipul Jain 19 Nov 2018 06:01 pm
Nitin Sethi 19 Nov 2018 06:24 pm
Is there a symbol for not equal to in RA, RTC?
Samagra Sharma 19 Nov 2018 09:22 pm
Yes, you can use $\neq$ in both of them.
Ranita Biswas 19 Nov 2018 07:05 pm
Ranita Biswas 19 Nov 2018 10:18 pm
Nitin, use ~ to represent negation in both RA and TRC.
Vipul Jain 20 Nov 2018 10:54 am
@ranita mam, in RA of part(b), when we take left outer join, then we would have NULL values for the cname attribute if an employee doesn't work for any company. So when select operation condition is applied on the cname which basically compares cname <> "SBI", the value returned for NULL values would be NULL, then on what basis they would be selected?
Ranita Biswas 20 Nov 2018 11:01 am
NULL is not equal to ‘SBI’, so the tuples having NULL in cname should be selected.
Samagra Sharma 20 Nov 2018 11:11 am
@ranita mam. Refering to page number 83 in Silberschatz if we try to calculate (NULL == 'SBI'). The answer is unknown(Third logical value) and not false.
Ranita Biswas 20 Nov 2018 11:26 am
Yes, to implement this in SQL, you must use "cname <> 'SBI' or cname IS NULL" in the where clause. However, as RA is a theoretical concept, we do write "a = NULL" or "a <> NULL" in RA SELECT clauses, which also does not produce correct result if used in SQL where clause.
Samagra Sharma 20 Nov 2018 11:29 am
Thanks Mam
Samagra Sharma 20 Nov 2018 11:24 am
https://en.wikipedia.org/wiki/Null_(SQL)#Comparisons_with_NULL_and_the_three-valued_logic_.283VL.29

Mam please have a look. Should we use the Codd's appproach or the normal one used in programming languages?

Ranita Biswas 20 Nov 2018 11:30 am
As SQL provides you with the separate notion of handling NULL values using "IS NULL", "IS NOT NULL" etc. you should be using that. However, it's not mandatory to go into this trouble while using RA or TRC. They are theoretical languages, and their implementation can be handled separately while doing so in SQL.
##### The following functional dependencies hold for relations R(A,B,C) and ...

The following functional dependencies hold for relations R(A,B,C) and S(B,D,E): B → A, and A → C. The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in the natural join R ⋈ S? Explain.

B is the candidate key for R and R ∩ S = {B}. As R contains 200 tuples and S contains 100 tuples, for each tuple of S, there can be atmost 1 tuple in R. Hence, R ⋈ S will contain a maximum of 100 tuples.

Sachin Jain 20 Nov 2018 05:42 am
Are we assuming that all tuples are distinct? Do we ignore duplicate tuples while taking natural join?
Ranita Biswas 20 Nov 2018 08:11 am
We assume relations by default have distinct tuples. If R and S both have distinct tuple, their natural join will also produce distinct tuples in this case.
##### The following B+ Tree has order 3. Answer each of the following questi...

The following B+ Tree has order 3. Answer each of the following questions independently of each other.

(a) What value(s) would be in the root node if we were to insert 0?

(b) What value(s) would be in the root node if we were to insert 6?

(c) Starting with the height 2 tree in the picture above, suppose we start inserting keys 6, 7, 8, … and so on. After inserting what key will the height of the tree become 4?

(a) 0 will be inserted in the leaf node containing 1, and hence, no change will be there in the root node. Root node will contain values (2, 4).

(b) 6 will be inserted in the leaf node containing (4, 5), and hence splitting of node with 5 getting copied in the root node, which splits again resulting in a new root containing only 4.

(c) Insertion of 14 should make the tree reach height 4 if we split the nodes reaching 3 values in 2:1. On the other hand, insertion of 10 should make the tree reach height 4 if we split the nodes reaching 3 values in 1:2. The second case is the solution followed by the standard algorithm given in wikipedia. A step-by-step analysis can be done to prove this value and is required to complete the answer.

Akshat Arora 18 Nov 2018 08:06 pm
maam, my answer for part c) is coming 14. Can you please upload the solution for this part?
Ranita Biswas 19 Nov 2018 10:43 am
Yes, the answer to part (c) will be 14 if you use split after insertion, and will be 10 if you split before insertion. Note that in a real scenario, the second approach is preferred. 16 was obtained by using value sharing with siblings, which is incorrect and not used during insertion algorithm. I will correct it.
Vipul Jain 19 Nov 2018 11:36 am
Ma'am, in Silberschatz book, it's written that we allow leaf nodes to contain as few as ceil( (n-1)/2 ) values while in slides it's written ceil(n/2) -1. Which value should we consider while solving questions?
No Body 20 Nov 2018 06:46 am
both give same result
Ranita Biswas 19 Nov 2018 11:52 am
Min no of pointers = ceil(n/2), No of key values is one less than the no of pointers. So, min no of key values = ceil(n/2) - 1. There are many different conventions for these things. Stick with any one, and to avoid confusion, mention the rule you are following. The rule should ensure similar performance/complexity compared to other standard methods.
##### Prove or disprove the following.(a) (b) ...

Prove or disprove the following.

(a)
(b)

Vipul Jain 19 Nov 2018 09:08 am
When we do the union of relation R(with A attribute) and S(with B attribute), then on what basis we should name the attribute of result? Should we name it A if it's (R union S) and B when (S union R)?
Ranita Biswas 19 Nov 2018 10:48 am
Yes, that should be the usual convention.
##### Prove or disprove the following.(a) (b) ...

Prove or disprove the following.

(a)

(b)

##### In the Timestamp Ordering Protocol, R-timestamp(Q) and W-timestamp(Q) ...

In the Timestamp Ordering Protocol, R-timestamp(Q) and W-timestamp(Q) are defined by the largest timestamp among the transactions which have respectively read and written the data item Q. Consider a variant of the protocol where R-timestamp(Q) and W-timestamp(Q) are defined by the timestamp of the most recent transactions to read and write Q respectively. Does this protocol guarantees conflict-serializability and/or view-serializability? Explain.

The modified protocol does not guarantee conflict or view serializability. Notice the following schedule with transactions T1, T2, and T3 where 1, 2, 3 are their respective timestamps.
S: R3(Q), R1(Q), W2(Q), R3(Q)
This schedule would have not been allowed by the original timestamp ordering protocol as W2(Q) will not be allowed because the first R3(Q) would have set the R-timestamp(Q) = 3 which is greater than 2. However, in this modified protocol, R1(Q) will change back the R-timestamp(Q) to 1 and hence W2(Q) will get executed without any problem, followed by the successful exceution of the second R3(Q) leading to cycles in the precedence graph of conflict-serializability, as well as violating the view-serializability property.

##### Consider the sequence of operations in the following box:(a) U...

Consider the sequence of operations in the following box:

(a) Under two phase locking, what is the earliest step after which we could release our lock on C? Your answer should be a number (e.g., after step "12").

(b) Under strict two phase locking, what is the earliest step after which we could release our lock on C? Your answer should again be a number (e.g., after step "12").

(c) There are conflict serializable schedules that cannot occur under two phase locking. Explain why or why not?

(d) There are conflict serializable schedules that cannot occur under strict two phase locking. Explain why or why not?

(a) Under two phase locking, lock release may happen anytime after the last lock acquiring statement. So, in this case, lock on C can be released as early as after step 7.

(b) Under strict two phase locking, exclusive locks are held till the end of the transaction. Hence, in this case, lock on C can only be released after step 9.

(c) There are conflict serializable schedule that cannot occur under two phase locking --- True. Following is an example schedule which is conflict schedule, but you cannot implement the same using two phase locking.

S = w1(A), r2(A), r1(A).

with lock-unlock sequence: x1(A), w1(A), s2(A) - request not granted.

(d) The same example schedule is true for this as well. There are conflict serializable schedule that cannot occur under strict two phase locking --- True.
S = w1(A), r2(A), r1(A).
Conflict serializable but cannot be implemented using strict 2PL.

DIVYANSHU SAXENA 16 Oct 2018 11:49 pm
@ranita ma'am, so for question (c) and (d) Deadlock is reason for not being able to implement by 2PL. right?
Ranita Biswas 19 Oct 2018 08:33 pm
Not deadlock. Using 2PL or strict 2PL will make the schedule strict in this case resulting in a schedule like w1(A), r1(A), r2(A); because unless T1 has completed its operations on A, T2 will not be allowed to access A. And, hence, the original schedule w1(A), r2(A), r1(A) (which is conflict serializable) is not executed by 2PL or strict 2PL.
##### For each of the following locking protocols, assuming that every trans...
For each of the following locking protocols, assuming that every transaction follows that locking protocol, state and explain which of these desirable properties are ensured: view-serializability, conflict-serializability, recoverability, cascadeless.

(a) Always obtain an exclusive lock before writing; hold exclusive locks until end-of-transaction. No shared locks are ever obtained.

(b) In addition to (a), obtain a shared lock before reading; shared locks can be released at any time.

(c) As in (b), and in addition, locking is two-phase.

(d) As in (b), and in addition, all locks held until end-of-transaction.

(a) As we are always doing commit before read, we will always get cascadeless and recoverable schedules. But, not necessarily serializable. We can have a schedule like following acceptable by this mechanism.
S: r1(A), r2(A), x1(A), w1(A), u1(A), c1, x2(A), w2(A), u2(A), c2
which is recoverable and cascadeless, but not serializable.
(b) If we take shared lock before reading, but we are allowed to release the shared locks at any time then also we cannot ensure serializability. So, the same example of (a) can be written like the following which is acceptable by this mechanism.
S: s1(A), r1(A), u1(A), s2(A), r2(A), u2(A), x1(A), w1(A), u1(A), c1, x2(A), w2(A), u2(A), c2
So, again recoverability and cascadelessness is ensured, but not serializability.
(c) If the locking is two phase and the exclusive locks are held till the end of transaction, then that is same as strict 2PL which ensures recoverability, cascadelessness, and both conflict and view serializability.
(d) If all locks are held until end of transaction (same as rigorous 2PL or SS2PL) then it ensures strict schedules which are recoverable, cascadeless, as well as conflict and view serializable.

##### Consider the following schedule (time increases from left to right):...

Consider the following schedule (time increases from left to right):

Explain whether the above schedule is conflict serializable and/or view serializable. Give suitable justification with dependency graph and conditions.

From the conflicting pairs of operations on resource B, we get the following relations:
T1 → T2, T4
T4 → T2
From the conflicting pairs of operations on resource C, we get:
T2 → T3
From the conflicting pairs of operations on resource E, we get:
T1 → T3
T4 → T3
So, maintaining all the relations we get the conflict equivalent serial schedule as T1 → T4 → T2 → T3. Hence, the given schedule is conflict serializable.

As every conflict serializable schedule is also view serializable, the given schedule is view serializable as well.

Ashish Kumar Goyal 16 Oct 2018 12:57 pm
T1 T4 T2 T3 is a serial schedule equivalent with the above schedule. Hence the schedule is both conflict serializable and view serializable.

Pls correct if i went wrong

Ranita Biswas 16 Oct 2018 01:35 pm
You are correct.
Ashish Kumar Goyal 16 Oct 2018 06:13 pm
@ranita mam, It should be T1->T4 and not T4->T1 due to conflicting pair T1->T4 for B, right? Pls update the answer accordingly
Ranita Biswas 16 Oct 2018 06:21 pm
##### Consider a database with objects X and Y and assume that there are two...
Consider a database with objects X and Y and assume that there are two transactions T1 and T2. Transaction T1 reads objects X and Y and then writes object X. Transaction T2 reads objects X and Y and then writes objects X and Y.

(a) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a write-read conflict.

(b) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a read-write conflict.

(c) Give an example schedule with actions of transactions T1 and T2 on objects X and Y that results in a write-write conflict.

(d) For each of the three schedules, show that Strict 2PL disallows the schedule.

Given that
T1 = r1(X), r1(Y), w1(X) T2 = r2(X), r2(Y), w2(X), w2(Y)
we design the following schedules which have the asked conflicts.

(a) Write-read conflict (which necessarily means reading uncommitted data, aka dirty read which results in cycle in the precedence graph):
S1 = r2(X), r2(Y),w2(X), r1(X), r1(Y), w1(X), w2(Y)
The WR conflict is written in bold. Note that, it is not necessary to have the W and R statement immediately after each other to create a WR conflict.

(b) Read-write conflict (which necessarily means unrepeatable read which causes cycle in the precedence graph):
S2 = r2(X), r2(Y), w2(X), r1(X), r1(Y), w2(Y), w1(X)
The RW conflict is written in bold.

(c) Write-write conflict (which necessarily means overwriting uncommitted data or blind writes resulting in cycle in the precedence graph):
S3 = r2(X), r2(Y), r1(X), r1(Y), w2(X), w1(X), w2(Y)
The WW conflict is written in bold.

(d) If we use strict 2PL the locking and unlocking will be done in two phases and all the exclusive locks will be held till the transaction commits or aborts and shared locks can be released any time during the second phase. If we apply strict 2PL, only serializable schedules will be allowed and all the three schedules listed above will be disallowed. Let us denote taking exclusive lock by x and shared lock by s, and release of lock by u, and commit by c. So, following will be the execution of the schedules where strict 2PL will ensure serializability by not granting locks which may create conflicts.
S1 = s2(X), r2(X), s2(Y), r2(Y), x2(X), w2(X), s1(X)-request not granted, x2(Y), w2(Y), u2(X), u2(Y), c2, s1(X), r1(X), s1(Y), r1(Y), x1(X), w1(X), u1(X), u1(Y), c1.

S2 = s2(X), r2(X), s2(Y), r2(Y), x2(X), w2(X), s1(X)-request not granted, x2(Y), w2(Y), u2(X), u2(Y), c2, s1(X), r1(X), s1(Y), r1(Y), x1(X), w1(X), u1(X), u1(Y), c1.

S3 = s2(X), r2(X), s2(Y), r2(Y), s1(X)-request not granted, x2(X), w2(X), x2(Y), w2(Y), u2(X), u2(Y), c2, s1(X), r1(X), s1(Y), r1(Y), x1(X), w1(X), u1(X), u1(Y), c1.

Ashish Kumar Goyal 16 Oct 2018 11:30 am

(a) R1(X) R2(X) R2(Y) W2(X) W2(Y) R1(Y) W1(X)

(b) R1(X) R1(Y) R2(X) W1(X) R2(Y) W2(X) W2(Y)

(c) R1(X) R1(Y) R2(X) R2(Y) W2(X) W1(X) W2(Y)

Pls help by highligting the error above (if any)...

Ranita Biswas 16 Oct 2018 12:05 pm
These are correct as well. Please see the answer to find a different set of possibilities and how strict 2PL resolves it.
15 Oct 2018 - 11:59pm
##### Course Project Submission Part 1

The first part of your course project needs to be submitted within 15th October midnight. It should be a PDF file (not scanned copy, as it will be part of your final course project report) containing ER diagram, Schema diagram, Normalization of tables with necessary steps of FD detection and minimal cover. You need to describe the assigned website by assuming that there is a relational database behind it, and your design should support the fundamental functionalities of the website. In case of any query, consult me by email or after Monday's class. All the best!

Submit here
##### We want to design a database for storing the information of Indian ath...

We want to design a database for storing the information of Indian athletes and their participations in Olympic games.
- An athlete has his/her ID, the name, and the birth date.
- Each Olympiad has the year, the country, and the city. For example, the 2016 Olympic game was held in Rio de Janeiro, Brazil.
- An athlete can participate in several sports in an Olympiad.
- Every athlete in our database participated in at least one Olympiad. Every Olympiad has at least one athlete.
Draw the ER Diagram with all constraints, list down all the assumptions, list down all the relation schemas from your ER Diagram and write the SQL queries to create the tables in your database with required integrity constraints.

##### You have just been hired as a consultant for a big airplane manufactur...

You have just been hired as a consultant for a big airplane manufacturer. Impressed by your background in databases, they want you to completely redesign their database system. Talking with the people in the company, you get the following information.
- The database contains information about employees, factories and parts.
- Each employee has a social security number (SSN), name and salary. An employee is uniquely identified by his or her SSN.
- Each factory has an id, name and a budget. The id uniquely identifies a factory.
- Each part has an id and a name. The id uniquely identifies a part.
- Each employee reports to at most one other employee.
- Each employee works in at least one factory.
- Each part is manufactured in exactly one factory. Each part is a

component of zero or more other parts.
Draw an ER diagram including all the details and constraints.

##### Consider the relation schema R(A; B; C; D; E; F; G) with functional de...

Consider the relation schema R(A; B; C; D; E; F; G) with functional dependencies F = {AB → CF; CD → EA; E → ABC; B → F; C → D}.
(a) Give all the candidate keys of R as inferred from F.
(b) Is R in BCNF? Is R in 3NF? Explain your answer.
(c) Consider the decomposition of R into R1(A; B; C; D; E; G) and R2(B; F). Is this decomposition (i) dependency-preserving, (ii) lossless-join, (iii) in 3NF, (iv) in BCNF?

##### Consider the relational schemas Student(S), Course(C), Enrolls(S, C)....

Consider the relational schemas Student(S), Course(C), Enrolls(S, C).

(a) Write down an SQL query that returns, for each student present in the database, the student id S and the number of courses s/he is enrolled in. (Hint: this may mean that for some values of S the count is 0).

(b) Write down an SQL query that returns all pairs of students (S1; S2) such that S1 has taken (at least) all the courses that S2 has taken.

Almost correct answer given by Nitin Sethi.

##### 1Comment
Nitin Sethi 13 Sep 2018 06:56 pm
a) select S1.Id, tot.courses
from student as S1,
lateral (select S2.student, count course.id) as tot_courses
from enrolls
group by student.id);

b)
select E1.sid, E2.sid,
from enrolls as e1, enrolls as e2
where not exists (
(select course.id
from enrolls
where sid = E1.sid)
except
(select course.id
from enrolls
where sid = E2.sid)
);

##### Consider the relationsHOUSE(house-id, h-address, h-age),PERSON(per...
Consider the relations
PERSON(person-id, p-name, p-age),
OWNS(house-id, person-id),
LIVES-IN(house-id, person-id)},
with the condition that a person can live in and own multiple houses.
Write SQL queries for the following tasks.
(a) Find the names of people who are part or full owners of at least one of the houses in which they live.
(b) Find the name of the person who owns the most houses.
(c) Find the names of the people who do not live anywhere.
(d) Find the names of all people who live in a house which is the same age as the person's age.

##### Assume that you are considering a new normal form TANF (Totally Awesom...

Assume that you are considering a new normal form TANF (Totally Awesome Normal Form). A constraint satisfies TANF for a relation R if, for every functional dependency X → Y, one of the following is true:
i) X → Y is a trivial FD,
ii) X is a candidate key for R.
Assume you decompose a relation R into TANF in the same way you decompose a relation into BCNF. Does this decomposition for TANF always have the lossless-join property? If yes, provide an argument. If no, provide a counterexample involving at most two FDs.

Detailed answer with explanation by Samagra Sharma:

If there is a non-trivial dependency of the form α → β where α is not a candidate key then we decompose the existing relation R into two parts:
α ∪ β and R − (β − α)
Now, in the above decomposition, we can uniquely identify α from the primary key of R. And then β can be determined from α using the dependency α → β on relation 1. This will guarantee a lossless join at all times.

In cases where α is a superkey. Take γ ⊆ α where γ is a candidate key and decompose into γ ∪ β and R − (β − γ).
So basically this will decompose in a way such that each decomposed table can only contain a single attribute in addition to a particular candidate key. If there are more than one attributes say, a1 and a2, apart from the candidate key α then the dependency α ∪ {a1} → a2 will violate TANF. Hence, there can only be one attribute along with a candidate key.

Samagra Sharma 15 Sep 2018 11:34 pm

If there is a non-trivial dependency of the form where   is not a candidate key then we decompose the existing relation R into two parts :

1.
2.

Now, in the above decomposition, we can uniquely identify from the primary key of R. And then can be determined from using the dependency on relation 1. This will guarantee a lossless join at all times.

Samagra Sharma 16 Sep 2018 12:03 am
In cases where $\alpha$ is a superkey. Take $\gamma \subseteq \alpha$ where $\gamma$ is a candidate key and decompose into $\gamma \cup \beta$ and $R-(\beta -\gamma)$.

So basically this will decompose in a way such that each decomposed table can only contain a single attribute in addition to a particular candidate key. If there are more than one attributes say, $a_1$ and $a_2$, apart from the candidate key $\alpha$ then the dependency $\alpha \cup \{a_1\} \to {a_2}$ will violate TANF. Hence there can only be one attribute along with a candidate key.

##### Consider the following relation and functional dependencies:SupremeC...

Consider the following relation and functional dependencies:
SupremeCourt(Docket, Appellant, Respondent, Oral_argument_time, oPinion_author, appoInted_by, parTy)
E = {PI → T, RP → I, O → ARP, D → O, OA → D}

(i)} Write the lossless-join decomposition of this relation into BCNF, by resolving the constraints that violate BCNF (if any).

(ii) Is this decomposition dependency-preserving?

DIVYANSHU SAXENA 15 Sep 2018 06:06 pm
After understanding relation, I find out that O and D are key of relation. Then I decompose relation into 5 part R(PIT), R(RPI), R(OARP), R(DO), R(OAD). Now for lossless-join R(PIT), R(RPI), R(OARP), R(DO), R(OAD) are full filling condition.
yes, this decomposition is dependency-preserving.
No Body 16 Sep 2018 07:27 am

Isn't R4(D, O) redundant?

Bhagyashree Mukherjee 16 Sep 2018 12:03 pm
The relation can be decomposed into bcnf as:
R1(PIT)
R2(RPI)
R3(DOARP)
The relation is lossless and dependency preserving
##### Consider the Congress relation, and associated functional dependencies...

Consider the Congress relation, and associated functional dependencies:
Congress(Bill, Title, Sponsor, Party, District, Committee, cHairperson, chAirperson_party, heaRing_time)
F = {R → SP, SP → DCH, B → SCT, DH → A, TS → R, SPR → B, S → P}

(i) Find out the minimal cover of F.

(ii) List down all the attribute sets that are candidate keys for the relation above.

(iii) List down all the functional dependencies (if any) from the initial set F which violate BCNF property.

Samagra Sharma 15 Sep 2018 11:27 pm
.
Ranita Biswas 15 Sep 2018 11:51 pm
@samagra, you need to click on the rich editor button in the bottom left of the comment box. That will give you access to the latex equation editor under Fx button.
Samagra Sharma 15 Sep 2018 11:36 pm
Thanks Mam! I got it now.
Bhagyashree Mukherjee 16 Sep 2018 01:23 pm
i) minimal cover: {S->DCH , B->ST, DH->A, ST->R, R->B, S->P}

ii) candidate keys: R, B, ST

iii) FDs violating bcnf SP->DCH, DH->A and S->P

##### A house is identified by a three-part address consisting of a number, ...
A house is identified by a three-part address consisting of a number, street, and city. Each house also has a style (e.g., bungalow, apartment) and a set of colors. A person is identified by a social security number. For each person we record his/her name, age, and sex. Persons who are at least 18 years old may own zero or more houses, and every house is owned by at least one person. Any person (regardless of age) lives in at most one house as his/her principal residence, and a house can have zero or more persons living there.

(a) Use an Entity-Relationship Diagram to depict the above scenario. Explain any additional assumption you make and list any aspect you were not able to depict.
(b) Draw the corresponding Schema Diagram inferred from your designed ERD. Briefly justify the steps involved.

(a) Following are the two possibilities of drawing the ERD. Second one uses generalization which is an extended ERD feature.

(b) The corresponding schema diagrams are following.

*the name attribute in the PERSON table given in first schema diagram has been wrongly underlined.

Samagra Sharma 16 Sep 2018 09:57 am
Is there any way of representing constraints (on particular values) or conditions in ER diagrams?
Ranita Biswas 17 Sep 2018 09:55 am
Usually you can't, unless there is a provision to represent the condition or constraint using some relation.
##### Consider the following relational schemas for a library database:Boo...

Consider the following relational schemas for a library database:
Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)
with the following functional dependencies:
I. Title Author → Catalog_no
II. Catalog_no → Title Author Publisher Year
III. Publisher Title Year → Price
Assume {Author, Title} is the key for both schemas. Which of the following statements is true?
(A) Both Book and Collection are in BCNF.
(B) Both Book and Collection are in 3NF only.
(C) Book is in 2NF and Collection is in 3NF.
(D) Both Book and Collection are in 2NF only.

Considering the first table Book, we have the candidate keys as (Title, Author} and Catalog_no. Table Book is not in 3NF as for the third FD, both RHS is non-prime, and LHS is not a superkey. Hence, Book is in 2NF as there is no partial dependency on the candidate keys. However, for table Collection, the candidate keys are {Title, Author} and Catalog_no. So, there is no non-prime attribute. Table Collection is in BCNF. So, only option (C) is correct.

Nitin Sethi 27 Aug 2018 09:04 pm
Does someone have an explanation for this??
Nitin Sethi 27 Aug 2018 09:10 pm
When Key is given (like in this case), do we use old or general definitions?
Ranita Biswas 27 Aug 2018 10:11 pm
Whenever the FDs are listed find out the candidate keys on your own and use the general/new definition.
RAHUL TIWARI 28 Aug 2018 12:25 pm
Answer is (B) Both Book and Collection are in 3NF only.
Tarun Kumar 13 Sep 2018 11:44 pm
I think candidate key for collection should only be Title and Catalog_no. not {Title,Author}
Ranita Biswas 13 Sep 2018 11:52 pm
“Assume {Author, Title} is the key for both schemas”
It’s given in the question.
Tarun Kumar 14 Sep 2018 01:08 am
Ok I thought it was copy of the ques given in slides
##### Consider the following relational schema:Suppliers(sid:integer, snam...

Consider the following relational schema:
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?
(A) The schema is in BCNF.
(B) The schema is in 3NF but not in BCNF.
(C) The schema is in 2NF but not in 3NF.
(D) The schema is not in 2NF.

As for all the given FDs, LHS are superkeys, the given schema is in BCNF.

Chirayu Asati 13 Sep 2018 05:30 pm
Each supplier has a unique name or each supplier, each street and each city combined has uniqueness. Confused with the language, please elaborate.
Ranita Biswas 13 Sep 2018 06:13 pm
In a particular city, all the suppliers living in that city have unique name, that's why (sname, city) acts as a candidate key. In a particular city, all the streets also have unique name; however, in a particular street there can be several suppliers living, and so (street_name, city) does not form a candidate key.
##### Relation R has eight attributes ABCDEFGH. Fields of R contain only ato...

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies (FDs) that hold for R. How many candidate keys does the relation R have?

Considering the given FDs. The relation R is
(A) in 1NF, but not in 2NF.
(B) in 2NF, but not in 3NF.
(C) in 3NF, but not in BCNF.
(D) in BCNF.

From the fiven FDs, the candidate keys of R are the following:
So, the number of candidate keys is 4.
As we have partial dependencies to the canidate keys, the relation R is (A) in 1NF, but not in 2NF.

##### Which of the following is TRUE?(A) Every relation in 3NF is also in ...

Which of the following is TRUE?
(A) Every relation in 3NF is also in BCNF.
(B) A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R.
(C) Every relation in BCNF is also in 3NF.
(D) No relation can be in both BCNF and 3NF.

Correct answer is (C) Every relation in BCNF is also in 3NF.

##### Consider a relation schema R = (A, B, C, D, E, H) on which the followi...

Consider a relation schema R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A → B, BC → D, E → C, D → A}. What are the candidate keys of R?

As E and H does not get determined by any other attribute, they must be present in the candidate key. So, our candidate keys are AEH, BEH, and DEH.

##### The relation schema Student_Performance(name, courseNo, rollNo, grade)...

The relation schema Student_Performance(name, courseNo, rollNo, grade) has the following functional dependencies:
name → rollNo
rollNo → name
The highest normal form of this relation schema is

From the given FDs, candidate keys of the Student_Performance schema are (name, courseNo) and (rollNo, courseNo). Hence, only non-prime attribute is grade which is fully-dependent on the candidate keys. So, the schema is in 2NF. Also, there is no transitive dependency, so it is in 3NF as well. However, it is not in BCNF, as for the last two FDs, LHS are not superkeys. Therefore, the highest normal form of the given schema is 3NF.

##### Consider the following functional dependencies in a database:Date_of...

Consider the following functional dependencies in a database:
Date_of_Birth → Age
Age → Eligibility
Name → Roll_number
Roll_number → Name
Course_number → Course_name
Course_number → Instructor
The relation (Roll_number, Name, Date_of_birth, Age) is:
(A) In second normal form but not in third normal form.
(B) In third normal form but not in BCNF.
(C) In BCNF.
(D) None of the above.

We see that Roll_number determines Name and vis-versa. However, Date_of_birth does not get determined by any other attribute. Hence, candidate keys for the given schema are (Roll_number, Date_of_birth) and (Name, Date_of_birth). So, from Date_of_birth → Age, the only non-prime attribute of our Age gets partially determined by the candidate keys. Hence, the schema is in 1NF, so option (D) None of the above is correct.

##### From the following instance of a relation scheme R (A, B, C), we can c...

From the following instance of a relation scheme R (A, B, C), we can conclude about the schema that:

A B C
1 1 1
1 1 0
2 3 2
2 3 2

(A) A functionally determines B and B functionally determines C.
(B) A functionally determines B and B does not functionally determine C.
(C) B does not functionally determine C.
(D) A does not functionally determine B and B does not functionally determine C.

The FDs satisfied by the given instance are A → B, B → A, C → A, C → B; however, we cannot conclude anything about the functional dependencies which hold for the schema by just looking at an instance. But, we can conclude about the FDs which does not hold. We can see that the FDs which does not hold for the instance/schema are A → C and B → C. Hence, from the given options (C) B does not functionally determine C is true.

##### Given the following relation instance. X Y Z ...

Given the following relation instance.

X Y Z
1 4 2
1 5 3
1 6 3
3 2 2

Which of the following functional dependencies are satisfied by the instance?
(A) XY → Z and Z → Y
(B) YZ → X and Y → Z
(C) YZ → X and X → Z
(D) XZ → Y and Y → X

The non-trivial FDs satisfied by this instance are the following:
Y → X, Y → Z, Z → X, YZ → X, XY → Z
Hence, the correct option is (B) YZ → X and Y → Z.

##### 1Comment
Aviral Verma 27 Aug 2018 06:11 pm
B, but the relation also satisfies XY->Z.(all valid only for given instance)
##### Let R(A, B, C, D, E, F, G) be a relational schema in which the followi...

Let R(A, B, C, D, E, F, G) be a relational schema in which the following functional

dependencies are known to hold: AB → CD, DE → F, C → E, F → C and B → G. The relational schema R is in ________________.

From the given FDs, the candidate key for R is AB. The last FD B → G is a partial dependency. Hence, R does the satisfy the rule of 2NF that is every non-prime attribute should be fully functionally dependent on the candidate keys. Therefore, R is in 1NF (considering all values are atomic).

##### A table has fields F1, F2, F3, F4, F5 with the following functional de...

A table has fields F1, F2, F3, F4, F5 with the following functional dependencies F1 → F3, F2 → F4, (F1, F2) → F5. In terms of Normalization, this table is in ______________.

From the given FDs, we can see that the canidate key for the table is (F1, F2). Hence, the first two FDs correspond to partial dependencies to the candidate key. The requirement of 2NF says that each non-prime attribute should be fully functionally dependent on the candidate keys. Hence, the given table is in 1NF (considering all values are atomic).

##### There is a table where only one row is fully repeated. Write an SQL qu...
There is a table where only one row is fully repeated. Write an SQL query to find the repeated row.

SELECT *
FROM table_name
GROUP BY *
HAVING count(*) > 1;

RAHUL TIWARI 29 Aug 2018 04:01 pm
SELECT * FROM employee GROUP BY * HAVING COUNT(*)>1;
RAHUL TIWARI 29 Aug 2018 10:49 am
Hey Garvit Dewan , what is the mistake in this query?
Rohan Bhatia 29 Aug 2018 11:31 am
It should be "group by *"
RAHUL TIWARI 29 Aug 2018 04:01 pm
SELECT * FROM employee GROUP BY * HAVING COUNT(*)>1;
##### An Employee table maintained in a company lists the employee ids and n...
An Employee table maintained in a company lists the employee ids and names along with their respective salaries. Write an SQL query to find out the name of the employee(s) who is receiving the second highest salary in that company.

SELECT name
FROM employee
WHERE salary = (SELECT max(salary)
FROM employee
WHERE salary < (SELECT max(salary)
FROM employee));

RAHUL TIWARI 30 Aug 2018 10:47 am
SELECT name,salary
FROM employee
WHERE salary = (SELECT MAX(salary) AS salary
FROM employee
WHERE salary < (SELECT MAX(salary)
FROM employee));
Aviral Verma 29 Aug 2018 09:34 pm
name is a non-aggregated column. wouldn't work
RAHUL TIWARI 30 Aug 2018 10:47 am
SELECT name,salary
FROM employee
WHERE salary = (SELECT MAX(salary) AS salary
FROM employee
WHERE salary < (SELECT MAX(salary)
FROM employee));
RAHUL TIWARI 30 Aug 2018 10:49 am
@aviralverma ,Thanks for correcting me.
Now this query is completely working fine.
5 Sep 2018 - 11:59pm

Submit here
##### A key is simple if it consists of only one attribute. Prove that if a ...
A key is simple if it consists of only one attribute. Prove that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF. Your proof should be general, e.g., it should not assume that R has a fixed number of, say two or three, attributes.

Given: R is in 3NF and every key in R is simple i.e. every key consists of only one attribute.
The general rule for having a relation in 3NF is that for every FD X → A in R
(1) X is a superkey of R, or
(2) A is a prime attribute.
In the given case as every key is simple, hence every prime attribute itself is a key; and hence if A is a key, any other X determining A necessarily is a superkey of R. Therefore, for the given case, every FD X → A implies that X is a superkey, which is infact the condition for a relation being in BCNF. So, R is in BCNF.

Ashish Kumar Goyal 29 Aug 2018 12:02 pm

Given-> R is in 3NF Therefore R do not have any transitive dependencies... i.e; For all dependencies A->B, where A is a key and B is non-key, B->C doesn't exist. Therefore, ∃! any X->Y such that X is a non-prime attribute.

Therefore, R is in BCNF.

nitin sethi 29 Aug 2018 03:02 pm
3NF has two conditions, 1. X is a superkey OR
2. A is prime. But A cannot be if there are only simple keys present. Thus only 1st condition is possible which is BCNF condition,
nitin sethi 29 Aug 2018 03:03 pm
3NF has two conditions, 1. X is a superkey OR
2. A is prime. But A cannot be if there are only simple keys present. Thus only 1st condition is possible which is BCNF condition,
nitin sethi 29 Aug 2018 03:03 pm
3NF has two conditions, 1. X is a superkey OR
2. A is prime. But A cannot be if there are only simple keys present. Thus only 1st condition is possible which is BCNF condition,
##### The maximum number of superkeys for the relation schema R(E, F, G, H) ...

The maximum number of superkeys for the relation schema R(E, F, G, H) with E as the key is _______________.

As the only key given for the schema is E, any superkey must contain it. We have 3 other attributes, namely F, G, and H; that implies 23 = 8 different possibilities. So, the answer is 8, and the superkeys are E, EF, EG, EH, EFG, EFH, EGH, and EFGH.

RAHUL TIWARI 28 Aug 2018 12:45 pm
8
Junaid Khan 30 Aug 2018 05:09 pm
Generalization

Relation R(x1,x2,x3........xn)

If key is made up of "k" attributes then possible number of super keys will be

2^(n-k)

##### Every table with two single-valued attributes is in 1NF, 2NF, 3NF and ...

Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF. True or False?

True. As both the attributes are single-valued, the table is in 1NF. Now, let's assume the attributes are A and B. So, without loss of generality, either A is the candidate key, or AB together is the candidate key, or both A and B are the candidate keys.
Case 1: A is the candidate key:
The only non-trivial FD is A → B, and the LHS is a superkey. Hence, the table is in BCNF.
Case 2: AB together is the candidate key:
There is no non-trivial FD. Hence, the table is in BCNF.
Case 3: Both A and B are the candidate keys:
The non-trivial FDs are A → B and B → A. For both of these FDs, LHS is a superkey. Hence, the table is in BCNF.
As for all 3 cases, we have proved that the table is in BCNF. Therefore, the given statement is true.

No Body 27 Aug 2018 05:57 am
true
No Body 27 Aug 2018 05:58 am
i think
Ranita Biswas 27 Aug 2018 10:17 am
No Body 28 Aug 2018 12:20 pm
1NF as all attrs. are single-valued.
2NF because if one of the attrs. is the cand. key then the other is fully FD on it and if both are present in the key then there are no non-prime attrs.
3NF because transitive FDs are not possible with only two attrs.
3.5NF maybe because the only possible FD has a key as its LHS.
Aviral Verma 27 Aug 2018 05:59 pm
The relation has only 2 attributes, then 4 elements in F+, for each one of them we can prove the relation is BCNF. Hence for any possible FD, the relation is BCNF.So, True.
Ashish Kumar Goyal 27 Aug 2018 06:21 pm
which are those 4 in f+ ?
Ashish Kumar Goyal 27 Aug 2018 06:16 pm

@ranita mam

Reason: 1NF---> since single valued attributes.

2NF ----> Since no partial dependency can occur in 2 attributed table.

3NF  ----> since for transitive we need atleast 3 attributes. ∴No Transitive dependencies.

BCNF ----> Following can be the cases

Case1: A->B(any attribute A is key)... ∴ BCNF

Case2: A->B and B->A ---->both are super keys, ∴ BCNF.

Case3: AB together is super key. ∴BCNF

So, i have assumed that the table contains a key. If there is no key.. i.e; having duplicate records then the table will be having no meaning and will not be even in 1NF.

RAHUL TIWARI 28 Aug 2018 12:34 pm
True
##### The Alipore Zoo has many types of animals. Every type has a unique nam...
The Alipore Zoo has many types of animals. Every type has a unique name. Every animal of the same type has a unique animal ID. Animals in two types may have the same animal ID. Animals also have age and gender. Animals may have diseases. The beginning time and the duration of a disease need to be recorded. A disease has a unique name.

A type keeper takes care of only one type of animals. Every type may have many type keepers. A type keeper may or may not be familiar with diseases. But every disease must be handled by at least one type
keeper. Type keepers have name, employee ID, ssn, address and phone number.

All animals are in cages. Some cage may be empty. Every cage has a cage ID, space and height. A cage keeper may take care of many cages. Every non-empty cage must have at least one cage keeper. Empty cages don’t need any cage keepers. Cage keepers have name, employee ID, ssn, address and phone number.

(a) Model the entities and relationships (including attributes and constraints of relationships) described above in an ER-diagram. Write down any assumptions you make.

(b) Design the schema diagram (clearly showing the relation schemas and the foreign key connections) for this database.

Answer will be updated soon. You are encouraged to solve on your own and discuss the solution in the comments.
##### I am not able to submit..it says due date crossed.......
I am not able to submit..it says due date crossed....
Ranita Biswas 22 Aug 2018 08:14 pm
It’s somehow working for me. I think it’s still some time zone issue. I have informed the developers and they are trying to rectify this. Will post an update when it’s corrected and will possibly extend the deadline as well. Let’s see.
Ranita Biswas 22 Aug 2018 08:16 pm
Ranita Biswas 22 Aug 2018 08:20 pm
It’s working now
Utkarsh Yadav 22 Aug 2018 11:32 pm
I too am not able to submit the assignment. I tried at 11:30 pm.
Ranita Biswas 22 Aug 2018 11:36 pm
I just checked. It seems to be working. Can you please retry?
Siddharth Jain 23 Aug 2018 12:02 am
I am unable to submit. Just passed the deadline. ;( Is there anything I can do?
Ranita Biswas 23 Aug 2018 12:07 am
Send the zip file to my gmail id [email protected]
Vipul Jain 23 Aug 2018 12:11 am
I actually uploaded the wrong zip file in hurry, which i saw just now.So should i also send it to your gmail ?
Ranita Biswas 23 Aug 2018 12:12 am
ritvik singhal 23 Aug 2018 12:12 am
ma'am i am not able to submit ! I was trying from last 15 min.
Ranita Biswas 23 Aug 2018 12:13 am
Send to my gmail id
22 Aug 2018 - 11:59pm
##### Tutorial 3: SQL

The questions and the instructions are given in the attached file. You have two weeks time to submit your solutions in a single compressed file.

Submit here
##### Finding superkeys, Candidate keys, and primary key

Consider a relational table with a single record for each registered student with the following attributes.

Registration_Num: Unique registration number of each registered student

UID: Unique identity number, unique at the national level for each citizen

BankAccount_Num: Unique account number at the bank. A student can have multiple accounts or join accounts. This attribute stores the primary account number.

Name: Name of the student

Hostel_Room: Room number of the hostel

Which one of the following option is INCORRECT?

(A) Registration_Num can be a primary key
(B) UID is candidate key if all students are from the same country
(C) If S is a superkey such that S ∩ UID is NULL then S ∪ UID is also a superkey
(D) BankAccount_Num is candidate key

Let's go through the options one by one to find out which on is incorrect.
Option (A): Correct
As we are storing information about registered students and the table has a single record for each registered student, the unique registration number for each student is a canidate key. Hence, Registration_Num can be a primary key.
Option (B): Correct
As UID is unique identity number at the national level, so unless there are international students, UID can identify each student uniquely. Hence, UID is candidate key if all students are from the same country.
Option (C): Correct
This is also true, because appending any other attribute with an already existing superkey will always be a superkey.
Option (D): Incorrect
As bank accounts can be jointly held, if two students share the same bank account which is their primary account as well, then for both of these students BankAccount_Num column will have same value. Hence, BankAccount_Num can not be a canidate key.

##### Finding candidate key

Consider the relation schema R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies
{{E, F} → {G}, {F} → {I, J}, {E, H} → {K, L}, {K} → {M}, {L} → {N}} on R.
What is a candidate key for R?
 (A) {E, F}
 (B) {E, F, H}
 (C) {E, F, H, K, L}
 (D) {E}