Advertisements
Advertisements
प्रश्न
Explain the use of ‘Foreign Key’ in a Relational Database Management System. Give example to support your answer.
Advertisements
उत्तर
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
संबंधित प्रश्न
Minimal superkeys are known as?
The primary key can be?
______ refers to the attribute that can uniquely identify tuples within the relation.
An Alternate key can be defined as ______.
Attributes that are candidates for primary keys are called ______.
Which of the following is true for a tuple and attribute?
Differentiate between Primary key and foreign key.
The school canteen wants to maintain records of items available in the school canteen and generate bills when students purchase any item from the canteen. The school wants to create a canteen database to keep track of items in the canteen and the items purchased by students. Design a database by answering the following question:
In order to generate the bill, we should know the quantity of an item purchased. Should this information be in a new relation or a part of the previous relation? If a new relationship is required, decide the appropriate name and data type for attributes. Also, identify the appropriate primary key and foreign key so that the following two restrictions are satisfied:
- The same bill cannot be generated for different orders.
- Bill can be generated only for available items in the canteen.
An organisation wants to create a database EMPDEPENDENT to maintain the following details about its employees and their dependent.
EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)
- Name the attributes of EMPLOYEE, which can be used as candidate keys.
- The company wants to retrieve details of the dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
- What is the degree of EMPLOYEE and DEPENDENT relation?
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.
Give one difference between alternate key and candidate key.
