Example of primary key, Database Management System

Suppose that in an organisation, an employee may do different roles in dissimilar projects. Say, RAM is doing coding in one project and doing designing in another. Suppose that the information is set by the organisation in 3 different relations named EMPLOYEE, PROJECT and ROLE. The ROLE relation tells the different roles needed in any project.

Suppose that the relational schema for the above 3 relations are:

EMPLOYEE (EMPID, Name, Designation)

PROJECT (PROJID, Proj_Name, Details)

 ROLE (ROLEID, Role_description)

In the relations over EMPID, PROJID and ROLEID are not NULL and unique, respectively. As we can clearly see, we can recognize the complete instance of the entity set employee by the attribute EMPID. Thus EMPID is the primary key of the relation EMPLOYEE. Likewise PROJID and ROLEID are the primary keys for the relations PROJECT and ROLE respectively.

Let ASSIGNMENT is a relationship among entities EMPLOYEE and PROJECT

and ROLE, Explain which employee is working on which project and what the

role of the employee is in the given project. Figure shows the E-R diagram for these entities and relationships.

821_Example of Primary Key.png

Figure: E-R diagram for employee role in development team

Let us consider sample relation instances as

 PROJECT

PROJID

Proj_name

Details

TCS

Traffic Control

System

For traffic

shaping.

LG

Load Generator

To simulate load

for input in TCS.

B++1

B++_TREE

ISS/R turbo sys

 

EMPLOYEE  

EMPID

Name

Designation

101

RAM

Analyst

102

SITA

Receptionist

103

ARVIND

Manager

ASSIGNMENT

ROLEID

Role_descrption

1000

Design

2000

Coding

3000

Marketing

 

 

Role

PROJID

Proj_name

Details

101

TCS

1000

101

LG

2000

102

B++1

3000

We can describe the relational scheme for the relation ASSIGNMENT as follows: ASSIGNMENT (EMPID, PROJID, and ROLEID)

Please note down now that in the relation ASSIGNMENT (as per the definition to be taken as R2) EMPID is the foreign key in ASSIGNMENT relation; it references the relation EMPLOYEE (as per the definition to be taken as R1) where EMPID is the primary key. Likewise ROLEID and PROJID in the relation ASSIGNMENT are foreign keys referencing the relation ROLE and PROJECT respectively.

Now after defining the theory of foreign key, we can proceed to talk about the real integrity constraints namely Entity Integrity and Referential Integrity.

Posted Date: 3/6/2013 6:36:27 AM | Location : United States







Related Discussions:- Example of primary key, Assignment Help, Ask Question on Example of primary key, Get Answer, Expert's Help, Example of primary key Discussions

Write discussion on Example of primary key
Your posts are moderated
Related Questions
What are the advantages of by using an index and what are its disadvantages ? In difference, if the search key of a secondary index is not a candidate key, it is not sufficient

What are the advantages of Logical Databases? It offers a simple-to-use selection screens.  You can change the pre-generated selection screen to your needs.  It offers check f

Discuss the differences among the candidate keys and the primary key of a relation. Give instance to describe your answer? A candidate key is one that can be used as primary ke

What is a view? How it is related to data independence? A view might be thought of as a virtual table, that is, a table that does not really exist in its own right but is inste

Attributes having a one value for a particular entity. For instance, Age is a single valued attribute of a student entity. Attributes  that  have  more  than  single  values

Briefly describe the different kinds of users of a DBMS.  Ans: Different sorts of DBMS users are: (a)  Software Engineers: These are the people accountable for developing ap

Designing the fields in the data base The following are the considerations one has to remain in mind while designing the fields in the data base. Coding, compression, en

Relationship between Security and Integrity: Database security usually refers to access, while database integrity refers to avoidance of accidental loss of consistency. But usually

Question: Describe briefly the three parts of the Relational Model. (a) The term integrity refers to the accuracy or correctness of the data in the database. Define the fol

What are stand-alone procedures? Procedures that are not part of a package are called as stand-alone because they independently defined. A good example of a stand-alone procedu