Advertisements
Advertisements
प्रश्न
Explain the use of ‘Foreign Key’ in a Relational Database Management System. Give example to support your answer.
उत्तर
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
संबंधित प्रश्न
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.
An Alternate key can be defined as ______.
The attribute chosen by the database designer to uniquely identify tuples 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.
A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below. Answer the following:
| Sports Preferences | |
| Roll_no | Preference |
| 9 | Cricket |
| 13 | Football |
| 17 | Badminton |
| 17 | Football |
| 21 | Hockey |
| 24 | NULL |
| NULL | Kabaddi |
- Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field?
- Roll no 17 has given two preference sports. Which property of relational DBMC is violated here? Can we use any constraint or key in the relational DBMS to check against such violations, if any?
- Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?
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)
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.
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.
