Define a Foreign key. Why is the concept needed? How does it play a role in the join operation?
Foreign key- A relation, say, r1, may include among its attributes the primary key of another relation, say, r2. This attribute in called a foreign key from r1, referencing r2. The relation r1 is also called the referencing relation of the foreign key dependency, and r2 is called the referenced relation of the foreign key. The foreign key concepts obey the rule of referential integrity constraints. A referential integrity constraint requires that the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation.
When we create a database to manage information for a business, it is common for tables in the database to have columns in common. While foreign keys do not uniquely identify records in their tables, they do provide an important benefit. They enable you to create links between tables that share fields.
Relational databases are usually normalized to eliminate duplication of information such as when objects have one-to-many relationships. For example, a Department may be associated with a number of Employees. Joining separate tables for Department and Employee effectively creates another table which combines the information from both tables. This is at some expense in terms of the time it takes to compute the join. While it is also possible to simply maintain a denormalized table if speed is important, duplicate information may take extra space, and add the expense and complexity of maintaining data integrity if data which is duplicated later changes.
All subsequent explanations on join types in this article make use of the following two tables. The rows in these tables serve to illustrate the effect of different types of joins and join-predicates. In the following tables the DepartmentID column of the Department table (which can be designated as Department.DepartmentID) is the primary key, while Employee.DepartmentID is a foreign key.
Employee table
|
Lastname |
DepartmentID |
|
Rafferty |
31 |
|
Jones |
33 |
|
Heisenberg |
33 |
|
Robinson |
34 |
|
Smith |
34 |
|
Williams |
Null |
Department table
|
DepartmentID |
DepartmentName |
|
31 |
Sales |
|
33 |
Engineering |
|
34 |
Clerical |
|
35 |
Marketing |
What are the differences between Primary Index, Secondary Index, and Clustering Index?
Describe the Three-Schema Architecture of DBMS. Distinguish Physical Data Independence and Logical Independence.
State the advantages of using database system over file-based information system.
Discuss about Staffing
Write a C program to find the value of Sine(X).
What is a priority queue ?
