Advertisements
Advertisements
Question
Explain the use of ‘Foreign Key’ in a Relational Database Management System. Give example to support your answer.
Solution
A foreign key is used to set or represent a relationship between two relations ( or tables) in a database. Its value is derived from the primary key attribute of another relation.
For example: In the tables TRAINER and COURSE given below, TID is the primary key in the TRAINER table but the foreign key in the COURSE table.
TRAINER
| TID | TNAME | CITY | HIRE DATE | SALARY |
| 101 | Mohan | Delhi | 1987-09-05 | 57000 |
| 102 | Sohan | Goa | 1997-11-15 | 70000 |
| 103 | Jyoti | Mumbai | 2000-08-21 | 75000 |
| 104 | Jayesh | Nagpur | 2005-06-10 | 65000 |
| 105 | Leena | Agra | 1990-11-05 | 85000 |
| 106 | Juhi | Chandigarh | 2004-09-25 | 50000 |
COURSE
| CID | CNAME | FEES | START DATE | TID |
| C201 | Sujay | 12000 | 2018-07-02 | 101 |
| C202 | Kartik | 15000 | 2018-07-15 | 103 |
| C203 | Deepa | 10000 | 2018-10-01 | 102 |
| C204 | Anagha | 9000 | 2018-09-15 | 104 |
| C205 | Yash | 20000 | 2018-08-01 | 101 |
APPEARS IN
RELATED QUESTIONS
Minimal superkeys are known as?
The attributes which have all the properties of primary key
The primary key can be?
______ refers to the attribute that can uniquely identify tuples within the relation.
Attributes that are candidates for primary keys are called ______.
The attribute chosen by the database designer to uniquely identify tuples is known as ______.
An attribute in a table whose value is derived from primary key of another table is known as ______.
Which of the following is true for a tuple and attribute?
Why foreign keys are allowed to have NULL values? Explain with an example.
Differentiate between Primary key and foreign key.
In a multiplex, movies are screened in different auditoriums. One movie can be shown in more than one auditorium. In order to maintain the record of movies, the multiplex maintains a relational database consisting of two relations viz. MOVIE and AUDI respectively as shown below:
Movie(Movie_ID, MovieName, ReleaseDate) Audi(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice)
Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.
In a multiplex, movies are screened in different auditoriums. One movie can be shown in more than one auditorium. In order to maintain the record of movies, the multiplex maintains a relational database consisting of two relations viz. MOVIE and AUDI respectively as shown below:
Movie(Movie_ID, MovieName, ReleaseDate) Audi(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice)
Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest an appropriate primary key.
In a multiplex, movies are screened in different auditoriums. One movie can be shown in more than one auditorium. In order to maintain the record of movies, the multiplex maintains a relational database consisting of two relations viz. MOVIE and AUDI respectively as shown below:
Movie(Movie_ID, MovieName, ReleaseDate) Audi(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice)
- Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.
- Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest an appropriate primary key.
- Is there any foreign key in any of these relations?
| 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 | ||
| PROJECT ASSIGNED | ||||
| Registration_ID | ProjectNo | |||
| IP-101-15 | 101 | |||
| IP-104-15 | 103 | |||
| CS-103-14 | 102 | |||
| CS-101-14 | 105 | |||
| CS-101-10 | 104 | |||
- Name the primary key of each table.
- Find foreign key(s) in table PROJECT-ASSIGNED.
- Is there any alternate key in table STUDENT? Give justification for your answer.
- Can a user assign duplicate value to the field RollNo of STUDENT table? Justify.
| 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 | ||
| PROJECT ASSIGNED | ||||
| Registration_ID | ProjectNo | |||
| IP-101-15 | 101 | |||
| IP-104-15 | 103 | |||
| CS-103-14 | 102 | |||
| CS-101-14 | 105 | |||
| CS-101-10 | 104 | |||
For the above-given database STUDENT-PROJECT, can we perform the following operations?
- Insert a student record with a missing roll number value.
- Insert a student record with a missing registration number value.
- Insert a project detail without a submission date.
- Insert a record with registration ID IP-101-19 and ProjectNo 206 in the table PROJECT-ASSIGNED.
| 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 | ||
| PROJECT ASSIGNED | ||||
| Registration_ID | ProjectNo | |||
| IP-101-15 | 101 | |||
| IP-104-15 | 103 | |||
| CS-103-14 | 102 | |||
| CS-101-14 | 105 | |||
| CS-101-10 | 104 | |||
For the above-given database STUDENT-PROJECT, can we perform the following operation?
Insert a student record with a missing registration number value.
Explain the concept of “Alternate Key” in a Relational Database Management System with an appropriate example.
The school has asked their estate manager Mr Rahul to maintain the data of all the labs in a table LAB. Rahul has created a table and entered data from 5 labs.
| LAB NO | LAB_NAME | INCNCHARGE | CAPACITY | FLOOR |
| L001 | CHEMISTRY | Daisy | 20 | I |
| L002 | BIOLOGY | Venky | 20 | II |
| L003 | MATH | Preeti | 15 | I |
| L004 | LANGUAGE | Daisy | 36 | III |
| L005 | COMPUTER | Mary | 37 | II |
Based on the data given above answer the following questions:
(i) Identify the columns which can be considered as Candidate keys.
(ii) Write the degree and cardinality of the table.
(iii) Write the statements to:
(a) Insert a new row with appropriate data.
(b) Increase the capacity of all the labs by 10 students which are on
the 'I' Floor.
OR
(iii) Write the statements to:
(a) Add a constraints PRIMARY KEY to the column LABNO in the table.
(b) Delete the table LAB.
Which of the following statements is FALSE about keys in a relational database?
Give one difference between alternate key and candidate key.
