Define transitive dependencies, Database Management System

Assignment Help:

What do you understand by transitive dependencies? Define with an example any two problems that can take place in the database if transitive dependencies are available in the database.

 Ans:  Assume X, Y, and Z are the set of attributes. If X → Y and Y → Z afterwards the functional dependency X → Z is a transitive functional dependency. Alternatively, if a nonkey attribute is as well functionally dependent on the other nonkey attribute(s), then there is a transitive functional dependency. Transitive functional dependencies are as well cause the data redundancies and the other consequences. For instance, consider the subsequent relation:

CREATE TABLE CONTACTS

(CONTACT_ID NUMBER (5) PRIMARY KEY,

 L_NAME   VARCHAR (20),

 F_NAME   VARCHAR (20),

 COMPANY_NAME VARCHAR (20),

 COMPANY_LOCATION VARCHAR (50))

 In the following relation, all the nonkey attributes are functionally dependent on the primary key, that is CONTACT_ID. But, there is as well a transitive functional dependency exists in the relation - CONTACT_ID  COMPANY_LOCATION because CONTACT_ID → COMPANY_NAME and COMPANY_NAME →COMPANY_LOCATION.  The result of transitive dependency, there are anomalies in the above relation like this:

  • Insertion Anomaly - A new company cannot be inserted till a contact person has been assigned to that company.
  • Deletion Anomaly - If a company has just only one contact person and is deleted from the table, we will lose the information about that company, as the company information is related with that person.
  • Update Anomaly - If a company changes its location we will should make the changes in all the records in which the company name appears.

Related Discussions:- Define transitive dependencies

Design a database table, A database named yourusernameFMA has been set up f...

A database named yourusernameFMA has been set up for you to use for this assignment.  For the FMA you must create the required tables and run the queries using this database.  If y

horizontal fragmentation, Horizontal Fragmentation- Horizontal fragmentati...

Horizontal Fragmentation- Horizontal fragmentation groups together the tuples in a relation that are collectively used by the main transactions. A horizontal fragment is produced b

Er diatgrams, Ask q“A database will be made to store information about pati...

Ask q“A database will be made to store information about patients in a hospital. On arrival, each patient’s personal details (name, address, and telephone number) are recorded wher

Illustrate the collaboration and relationship diagram, Illustrate the colla...

Illustrate the collaboration and relationship diagram A collaboration is the society of interfaces, classes and other elements which work together to provide some cooperative b

Define entity integrity and referential integrity, Define entity integrity ...

Define entity integrity and referential integrity.  How does SQL allow specification of  these? Ans: Entity Integrity Rule- If the attribute A of relation R is a prime attribut

Lock, intent locking

intent locking

Explain the lock based protocol, Lock Based Protocol A lock is nothing ...

Lock Based Protocol A lock is nothing but a mechanism that tells the DBMS whether a particular data item is being used by any transaction for read/write purpose. As there are t

Ddl compiler-database management system, DDL Compiler The DDL compile...

DDL Compiler The DDL compiler changes the data definition statements (such as CREATE TABLE .... in SQL) into a set of tables having metadata tables. These tables have infor

Two types of blocks in the fixed -length representation, What are the two t...

What are the two types of blocks in the fixed -length representation? Define them. Anchor block: Have the first record of a chain. Overflow block: Have the records other tha

Why dml is used for, Why DML is used for? DML is used to Manipulation and...

Why DML is used for? DML is used to Manipulation and processing of database. DML is provided for manipulation & processing of database. (Data stored in the database is processed

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