Example of primary key, Database Management System

Assignment Help:

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.


Related Discussions:- Example of primary key

FIELDS, 4. Describe three ways to handle missing field values

4. Describe three ways to handle missing field values

Er diagram, Draw an ER diagram for company''s database schema

Draw an ER diagram for company''s database schema

Which is the properties of entities, Which is the properties of entities? ...

Which is the properties of entities? Attributes is the properties of entities.

Identify all renters who have viewed all properties, Consider the subsequen...

Consider the subsequent relations RENTER(rno, fname, lname, address, tel_no, pref_type, max_rent) VIEWING(rno, pno, date, comment) PROPERTY_FOR_RENT( pno, street, area ,city,

Types of indexes, Types Of Indexes One of the words used during the fil...

Types Of Indexes One of the words used during the file organisation is the term index. In this part, let us describe this term in more detail. We find the index of keywords

Structure of client server systems, Structure of Client Server Systems: In ...

Structure of Client Server Systems: In client/server architecture, clients show users who require services while servers give services. Both client and server are a combination of

What is disadvantages of dbms, What is Disadvantages Of DBMS ? 1. Cos...

What is Disadvantages Of DBMS ? 1. Cost of Software and Hardware and Migration - A important disadvantage of the DBMS system is cost. 2. Reduced Response and Throughpu

Distributed and client server databases, Distributed And Client Server Data...

Distributed And Client Server Databases Introduction This unit tells the distributed database systems which are primarily relational and one important execution model: the

Relational database, Convert the following ER - diagram (Entity Relationsh...

Convert the following ER - diagram (Entity Relationship Diagram) into a relational database (the primary keys are underlined): 2160_Convert the following ER - diagram into a

Explain the deadlock avoidance, Define deadlock avoidance. An alternat...

Define deadlock avoidance. An alternative method for avoiding deadlocks is to needs additional information about how resources are to be requested. Each request requires the s

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd