For the above given database STUDENT-PROJECT, answer the following:
Student Project Database Table: STUDENT
| Roll No | Name | Class | Section | Registration_ID |
|---|---|---|---|---|
| 11 | Mohan | XI | 1 | IP-101-15 |
| 12 | Sohan | XI | 2 | IP-104-15 |
| 21 | John | XII | 1 | CS-103-14 |
| 22 | Meena | XII | 2 | CS-101-14 |
| 23 | Juhi | XII | 2 | CS-101-10 |
Table: PROJECT
| ProjectNo | PName | SubmissionDate |
|---|---|---|
| 101 | Airline Database | 12/01/2018 |
| 102 | Library Database | 12/01/2018 |
| 103 | Employee Database | 15/01/2018 |
| 104 | Student Database | 12/01/2018 |
| 105 | Inventory Database | 15/01/2018 |
| 106 | Railway Database | 15/01/2018 |
Name primary key of each table.
Primary key of STUDENT table is Roll No and Primary key of PROJECT table is ProjectNo.
Find foreign key(s) in table PROJECT-ASSIGNED.
The foreign key(s) in table PROJECT-ASSIGNED would be Roll No (referencing STUDENT table) and ProjectNo (referencing PROJECT table).
Is there any alternate key in table STUDENT? Give justification for your answer.
Yes, Registration_ID is an alternate key in STUDENT table. Justification: Registration_ID has unique values for all records (IP-101-15, IP-104-15, CS-103-14, CS-101-14, CS-101-10). Since it is a candidate key (unique and non-null) but not chosen as the primary key, it becomes an alternate key.
Can a user assign duplicate value to the field RollNo of STUDENT table? Justify.
No, a user cannot assign duplicate value to RollNo. Justification: RollNo is the primary key of STUDENT table. Primary key constraint ensures uniqueness - no two records can have the same primary key value. This maintains entity integrity in the database.
Explanation
This question tests understanding of relational database concepts - primary keys, foreign keys, alternate keys, and constraints. Part (a) requires identifying unique identifiers in each table. Part (b) requires understanding that PROJECT-ASSIGNED is a linking table connecting STUDENT and PROJECT. Part (c) tests knowledge that alternate keys are candidate keys not selected as primary key. Part (d) tests understanding of primary key constraints.