Fill in the blanks: a) ______ declares that an index in one table is related to that in another table. i) Prim

Exercises · Q3

Q. Fill in the blanks:

a) ____________ declares that an index in one table is related to that in another table.

i) Primary Key

ii) Foreign Key

iii) Composite Key

iv) Secondary Key

b) The symbol Asterisk (*) in a select query retrieves ____________.

i) All data from the table

ii) Data of primary key only

iii) NULL data

iv) None of the mentioned

CBSENCERTMCQ· 1mImportance★★★★★est

64% · 29/45 Questions

✓ Free question

Concept understanding — Database Relationships

Database Relationships: From Intuition to Precision

Imagine you're building a school library system. You have a list of students and a list of books. A student borrows a book. How do you connect these two separate lists without rewriting everything every time a book changes hands?

That connection is a database relationship — it's how rows in one table are logically linked to rows in another table. Without relationships, your data is just isolated piles of information.

The Core Intuition: Why Not Just One Big Table?

You could put everything — student name, book title, borrow date — into a single table. But then:

If a student borrows three books, their name repeats three times. Change their address? You must update three rows.
If a book has no borrower yet, you leave half the columns empty.
If you delete a borrowed book, you might accidentally delete the student's record too.

This is redundancy and anomaly — the enemies of good data. Relationships solve this by keeping tables separate and linking them only when needed.

The Three Fundamental Types

Relationships are defined by how many rows on one side can match how many on the other.

1. One-to-One (1:1)

Each row in Table A matches exactly one row in Table B, and vice versa.

Example: A student has one library card. That card belongs to only that student.

Students:  [ID, Name, Class]
Cards:     [CardNo, IssueDate, StudentID]

The StudentID in the Cards table is a foreign key pointing to the primary key in Students. Because each student gets at most one card, no two rows in Cards share the same StudentID.

Note

One-to-one is rare. Often it means two tables should probably be one table, unless you're splitting for security (e.g., storing sensitive data separately) or performance.

2. One-to-Many (1:N) — The Most Common

One row in Table A can match many rows in Table B, but each row in B matches exactly one row in A.

Example: One student can borrow many books. Each book is borrowed by at most one student at a time.

Students:  [StudentID, Name]
Books:     [BookID, Title, BorrowerID]

Here BorrowerID in Books is the foreign key. A single student's ID can appear in many book rows. This is the workhorse of database design.

Tip

To spot a one-to-many relationship, ask: "Can this thing have multiple of those?" A student can have multiple books. Yes — one-to-many.

3. Many-to-Many (M:N)

Many rows in Table A can match many rows in Table B.

Example: A student can attend many workshops. A workshop can have many students.

You cannot represent this with a single foreign key in either table — that would force a one-to-many. Instead, you need a junction table (also called a linking table or associative entity).

Students:    [StudentID, Name]
Workshops:   [WorkshopID, Topic]
Enrollments: [StudentID, WorkshopID, DateEnrolled]

The Enrollments table has two foreign keys — one to Students, one to Workshops. Each row says "this student attended that workshop." The pair (StudentID, WorkshopID) is usually the primary key.

Important

Many-to-many relationships always require a third table. Never try to cram multiple values into a single cell — that breaks the first rule of database design (atomicity).

The Precise Statement

A database relationship is a logical association between two tables, enforced by a foreign key in one table that references the primary key of another. The cardinality (1:1, 1:N, M:N) describes how many rows in one table can correspond to rows in the other.

Foreign Key Rule: A foreign key in Table B must either be NULL or match an existing primary key value in Table A. This is referential integrity — it prevents orphaned records.

A Quick Visual

Relationship	Table A	Table B	How It's Stored
1:1	Student	Library Card	Foreign key in either table, with a UNIQUE constraint
1:N	Student	Book	Foreign key in the "many" side (Book)
M:N	Student	Workshop	Junction table with two foreign keys

Why This Matters for Exams

You will be asked to:

Identify the relationship type from a description ("A teacher teaches many subjects, each subject is taught by one teacher" → 1:N)
Draw an ER diagram showing relationships
Explain why a junction table is needed for M:N
Spot the foreign key in a given schema

The key is always: count the sides. One student, many books? That's 1:N. Many students, many workshops? That's M:N. One student, one card? That's 1:1.

Relationships are not just about connecting data — they are about preserving its integrity while eliminating redundancy. That is the entire point.

A Foreign Key is the constraint that declares that a column (index) in one table is related to a column in another table.
The asterisk * in a SELECT is shorthand for all columns — all data from the table.

a) "____ declares that an index in one table is related to that in another table."

That is the definition of a FOREIGN KEY: a column (or set of columns) in one table whose values must match the primary key of another table, formally linking the two.

CREATE TABLE MATCH_DETAILS (
    MatchID     VARCHAR(3) PRIMARY KEY,
    FirstTeamID INT,
    FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID)
);

The FOREIGN KEY ... REFERENCES line is exactly the "related to another table" declaration — MATCH_DETAILS.FirstTeamID may now hold only values that exist in TEAM.TeamID.

Why the other options fail:

(i) Primary Key identifies rows within its own table — it declares nothing about another table.
(iii) Composite Key just means a key made of multiple columns; still within one table.
(iv) Secondary Key is a non-key attribute used for retrieval — again, no cross-table relationship.

Correct: (ii) Foreign Key.

b) "The symbol Asterisk (*) in a select query retrieves ____."

SELECT * FROM student;

name	student_id	gender
Suhana	109	F
Rivaan	102	M

* expands to every column of the table, so the query returns all data from the table — every column of every row (subject only to any WHERE filter, absent here).

(ii) primary-key-only would need SELECT student_id ...; (iii) NULLs are not specially selected by *; (iv) is ruled out because (i) is true.

Correct: (i) All data from the table.

✓Final answer

a) (ii) Foreign Key — it declares that a column in one table references (is related to) a column in another table. b) (i) All data from the table — * is shorthand for every column.

←Previous questionQ2 — Choose appropriate answer with respect to the following code snippet. …→Next questionQ4 — Consider the following MOVIE database and answer the SQL queries based…

Q.Fill in the blanks:

🎓Unlock everything free for 14 days

Unlock everything free for 14 days