Unit 4: Database Management Systems · 27 questions · about 32 minutes. Press End Mock any time to see your score and the correct answers.
An alternate key in database table is also called
Correct answer: B. Candidate key
An alternate key is a candidate key that was not chosen as the primary key.
Directions (Q96–100): A company is implementing a new database to manage customer information. It holds tables for customers, orders, products and sales representatives. Each customer can place multiple orders and each order can contain multiple products. Each order is associated with a specific sales representative who manages the account. The design aims to improve tracking of sales and customer interactions.
Which of the following tables is likely to have a foreign key referencing another table in the database system implemented by the company ?
Correct answer: B. Orders
Orders references customers (customer_id) and sales representatives, so the orders table holds foreign keys.
Match List - I with List - II.
Correct answer: B. (a)-(IV), (b)-(I), (c)-(II), (d)-(III)
BCNF: every FD is implied by the candidate keys. 5NF: every join dependency is implied by them. 1NF: all domains hold atomic (scalar) values. 4NF: every MVD is implied by the keys.
Directions (Q96–100): Relations X(S, Si, C) with tuples (J, 1, M), (B, 2, N), (R, 3, H), (T, 4, G), and Y(S, P, D) with tuples (J, S1, CA), (B, P1, AB), (R, D1, DC), (A, H1, MD).
Find the number of tuples obtained by applying the right outer join X ⟖ (X.S = Y.S) Y.
Correct answer: C. 4
J, B and R match in both relations (3 tuples). A right outer join also keeps Y's unmatched tuple A, padded with NULLs: 4 tuples.
The result of the operation X ⟖ (X.S = Y.S) Y (right outer join) will be:
Correct answer: B. Columns S, Si, C, P, D: (J,1,M,S1,CA), (B,2,N,P1,AB), (R,3,H,D1,DC), (A,NULL,NULL,H1,MD)
The matching rows J, B and R are joined, and Y's unmatched row A appears with NULL in X's columns (Si, C) — option B. Option A is the left outer join.
Which of the following join is used to get all the tuples of relation X and Y with Null values of corresponding missing values?
Correct answer: D. Full outer join
A full outer join keeps all tuples of both relations, filling missing values with NULL.
Number of tuples by applying right outer join on relation X and Y is/are :
Correct answer: D. 4
3 matching tuples (J, B, R) plus Y's unmatched tuple A gives 4.
Given below are the two statements:
Statement I : Any retrieval request that is specified in the basic relational algebra can also be specified in relational calculus.
Statement II : Any retrieval request that is specified in the relational calculus can also be specified in basic relational algebra. In the light of the above statements, choose the correct answer from the options given below:
Correct answer: A. Both Statement I and Statement II are true.
Codd's theorem: basic relational algebra and safe relational calculus have the same expressive power, so each can express every query of the other.
Identify the incorrect statement(s).
Correct answer: B. A candidate key for which no proper subset is also a candidate key is called a super key.
A super key uniquely identifies tuples; a minimal super key (no proper subset is a super key) is a candidate key. Statement B reverses this, so it is incorrect.
Consider the two relations below. The primary keys are underlined. Identify all possible foreign key(s) from the options based only on the two Q relations. EMP (eid, ename, did) DEPT (did, dname)
Correct answer: B. did
EMP.did refers to DEPT's primary key did, so did is the foreign key.
Directions (Q31–35): Sailors S(sid, sname, rating, age): (22, Dustin, 7, 45), (29, Brutus, 1, 33), (31, Lubber, 8, 55.5), (32, Andy, 8, 25.5), (58, Rusty, 10, 35), (64, Horatio, 7, 35), (71, Zorba, 10, 16), (74, Horatio, 9, 35), (85, Art, 3, 25.5), (95, Bob, 3, 63.5). Reserves R(sid, bid, day): (22, 101), (22, 102), (22, 103), (22, 104), (31, 102), (31, 103), (31, 104), (64, 101), (64, 102), (74, 103). Boats B(bid, bname, color): (101, Interlake, blue), (102, Interlake, red), (103, Clipper, green), (104, Marine, red).
Which relational algebra query computes the names of sailors who have reserved all boats?
Correct answer: B. ρ(Tempsids, π_sid,bid Reserves / π_bid Boats); π_sname(Tempsids ⋈ Sailors)
Division needs the (sid, bid) pairs from Reserves divided by all bids: π_sid,bid(Reserves) ÷ π_bid(Boats) gives sids paired with every boat. Join with Sailors for the names.
Which relational algebra query computes the names of sailors who have reserved a red and a green boat?
Correct answer: A. ρ(Tempred, π_sid((σ_color='red' Boats) ⋈ Reserves)); ρ(Tempgreen, π_sid((σ_color='green' Boats) ⋈ Reserves)); π_sname((Tempred ∩ Tempgreen) ⋈ Sailors)
Compute the sids that reserved a red boat and the sids that reserved a green boat separately, intersect them, then join with Sailors. Intersecting boats (no boat is both colours) or selecting color = red ∧ green gives nothing.
Given a fixed‐length record file that is ordered B on the key field. The file needs disk blocks to R store number of records. Find the average access time needed to access any record of the given file using binary search.
Correct answer: D. log₂ B
Binary search over B ordered blocks needs about log₂ B block accesses.
Given the following STUDENT‐COURSE scheme: STUDENT (Rollno, Name, courseno) COURSE (courseno, coursename, capacity), where Rollno is the primary key of relation STUDENT and courseno is the primary key of relation COURSE. Attribute coursename of COURSE takes unique values only. Which of the following query(ies) will find total number of students enrolled in each course, along with its coursename. *
A. SELECT coursename, count( ) 'total' from STUDENT natural join COURSE group by
coursename; *
B. SELECT C.coursename, count( ) 'total' from STUDENT S, COURSE C where
S.courseno=C.courseno group by coursename; *
C. SELECT coursename, count( ) 'total' from COURSE C where courseno in (SELECT courseno
from STUDENT);
Correct answer: A. A and B only
A (natural join) and B (explicit join on courseno) both group by coursename and count. C has no GROUP BY, so it returns one aggregated row (and is invalid with coursename).
Given STUDENT(Rollno, Name, Courseno) and COURSE(Courseno, Coursename, Capacity) with 5 and 3 records, R = STUDENT × COURSE is computed. Match List I with List II. List I: A. Degree of table R, B. Cardinality of table R, C. Foreign key of relation STUDENT, D. Foreign key of relation COURSE. List II: I. 15, II. NIL, III. 6, IV. Courseno.
Correct answer: A. A ‐III , B ‐I , C ‐IV , D ‐ II
Degree = 3 + 3 = 6 attributes; cardinality = 5 × 3 = 15 tuples; STUDENT.Courseno references COURSE; COURSE has no foreign key.
Consider a relational schema S = (U, V , W, X, Y , Z) on which the following functional dependencies hold: {U → V , V W → X, Y → W, X → U} Which are the candidate keys among following options?
Correct answer: D. UYZ, VYZ, XYZ
Y and Z never appear on a right side, so every key has YZ (YZ+ = Y, Z, W). Adding U, V or X each reaches all attributes (U → V, VW → X, X → U), giving UYZ, VYZ and XYZ.
Given two tables R1(x,y) and R2(y,z) with 50 and 30 numbers of tuples respectively. Find the maximum number of tuples in the output of natural join between tables R1 and R2 i.e. R1 * R2. (* - Natural Join)
Correct answer: D. 1500
If every tuple has the same y value, every R1 tuple matches every R2 tuple: 50 × 30 = 1500.
Directions (Q96–100): An organization maintains a database with five attributes A, B, C, D, E. Consider the universal relation R(A, B, C, D, E) with functional dependency set F = {A → BC, D → E, BC → D, A → D}. All attributes are simple and atomic.
Identify primary key of table with F functional dependency set
Correct answer: C. A
A determines B, C, then D, then E, and appears on no right side, so A is the key.
Which of the following has same expressive power with regard to relational query language?
(a) Relational algebra and domain relational calculus
(b) Relational algebra and tuples relational calculus
(c) Relational algebra and domain relational calculus restricted to safe expression
(d) Relational algebra and tuples relational calculus restricted to safe expression
Correct answer: B. (c) and (d) only
Relational algebra equals tuple and domain relational calculus restricted to safe expressions; unrestricted calculus can express unsafe (infinite) queries.
With respect to relational algebra, which of the following operations are included for mathematical set theory?
(a) Join
(b) Intersection
(c) Cartisian product
(d) Project
Correct answer: B. (b) and (c)
Union, intersection, difference and Cartesian product come from set theory; join and project are relational operations.
Which of the following key constraints is required for functioning of foreign key in the context of relational databases?
Correct answer: B. Primary key
A foreign key normally references the primary key of the parent table.
Note: Strictly, a foreign key may reference any candidate (unique) key; the official key is primary key.
In relational database management, which of the following is/are property/properties of candidate key? P : Uniqueness Q : Irreducibility
Correct answer: C. Both P and Q
A candidate key must be unique and irreducible (minimal).
A table has attributes Employee_id (primary key) and Manager_id (foreign key referencing Employee_id with ON DELETE CASCADE), with tuples (20, 40), (25, 40), (30, 35), (35, 20), (40, 45), (45, 25). On deleting the tuple (20, 40), which other tuples must be deleted to maintain referential integrity?
Correct answer: B. (30, 35) and (35, 20) only
Deleting employee 20 cascades to (35, 20), whose manager was 20; deleting 35 cascades to (30, 35). No one has manager 30.
Consider a relation schema R = (A, B, C, D, E, F) on which the following functional dependencies hold :
A → B B, C → D
E → C
D → A What are the candidate keys of R?
Correct answer: D. AEF, BEF and DEF
E and F never appear on a right side, so every key contains EF; E gives C. AEF: A → B, BC → D gives all. BEF: BC → D → A. DEF: D → A → B. So AEF, BEF and DEF.
In RDBMS, which type of Join returns all rows that satisfy the join condition ?
Correct answer: A. Inner Join
An inner join returns exactly the row pairs that satisfy the join condition; outer joins add unmatched rows.
A many-to-one relationship exists from entity set r₁ to entity set r₂. If Pk(r) denotes the primary key of relation r, how is it represented using functional dependencies?
Correct answer: A. Pk(r₁) → Pk(r₂)
Each r₁ entity relates to at most one r₂ entity, so r₁'s key determines r₂'s key.
Let R₁(a, b, c) and R₂(x, y, z) be relations in which a is a foreign key of R₁ referring to the primary key of R₂. Consider: (a) insert into R₁, (b) insert into R₂, (c) delete from R₁, (d) delete from R₂. Which is correct about the referential integrity constraint?
Correct answer: D. Operations (d) and (a) will cause violation.
Inserting into the referencing table R₁ can introduce a value with no match, and deleting from the referenced table R₂ can orphan rows. Operations (a) and (d) can violate it.
