Course: Database Systems Discussion 1 (Chapter 6): How can the key and foreign key constraints be enforced by the DBMS? Is the enforcement technique you suggest difficult to implement? Can the cons
Replies for the below posts:
Post 1:
16 hours ago
Aparna
Week- 3.1
COLLAPSE
Top of Form
Enforcement of key constraint in DBMS (Database management System):
Key constraint:
The method often used to check for the primary constraint effectively is to build an index on the variations of attributes that make up each key (primary or secondary). Before adding a new record (tuple), each database is scanned to ensure that no value matching the new record's primary value already exists in the index. If the check is successful, the record is inserted (Microsoft, 2017).
Foreign key constraint:
The foreign key constraint is checked using the index on the primary key of each referred connection, which makes the search reasonably effective.
When a new record is being inserted into a referencing relation, the foreign key value is used to check the archive for the referenced relation's primary key. If the referenced record persists, the new record will be successfully inserted into the referencing relation.
To efficiently decide if some records reference the record being removed while deleting a referenced record, it is helpful to provide an index on the foreign key of each referring relation (Microsoft, 2017).
Implementation of enforcement technique:
No, the index-based enforcement technique makes it simple to find redundant data records. If another structure, such as hashing, is used instead of the index on key restriction, it only performs linear searches to check for restrictions, making the check inefficient (Microsoft, 2017).
Efficient constraints checks
Yes, constraint tests are performed effectively when inserting or deleting records from the archive. Using the index to impose the main restriction eliminates data record replication, enabling product vendors to gain greater data storage and management. As a result, using the index to perform constraint checks is successful.
References
Microsoft. (2017, July 25). Microsft.com. Retrieved from Primary and Foreign Key Constraints: https://docs.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver15
Bottom of Form
Post 2:
Tera
COLLAPSE
Top of Form
Week 3 Discussion 1
How can the key and foreign key constraints be enforced by DBMS? Is the enforcement technique you suggest difficult to implement? Can the constraint checks be executed in an efficient manner when updates are applied to the database?
Enforcement of the Key Constraints by the DBMS are managed through efficient verification of the index of any attributes that form the key constraint. An example would be to ensure that you have the index value correctly set for each record inserted and ensuring that the index matches the key value for the inserted record. If the search can successfully obtain the information, then the record is returned. If the search is unable to obtain the information, then no record is returned. Essentially the primary key will maintain a single attribute and will allow for data to follow the attribute directly (Elmasri, Navathe 2017)
Enforcement of the foreign key constraint by DBMS are managed through verifying that the primary key of each relation will return an efficient check. An attempt to add any records in a relation will verify that the foreign key values searched the primary key for any of the relational reference and if a relational record exists the authority to add the new records it approved. The same concept goes for deletion of any relational referenced records. The verification needs to ensure that none of the relational primary or foreign keys have a relational reference to the record that is being selected for deletion.
The enforcement technique of verification through index is not difficult to implement, in fact is it easy to utilize in the attempt of identifying any duplication of records and ensuring that critical relational reference records are maintained to ensure the integrity of the data you are searching and maintaining.
Constraint checks are an efficient way to ensure that adding or deleting records from the database do not impact the results of other data held within the database that may have a relation to the records being added or deleted. By using the index mentioned above, the constraints are established to prevent duplication of records that already exist as well as preventing records that are necessary to other relational records from being deleted.
References
Elmasri, R., & Navathe, S. B. (2017). Fundamentals of Database Systems (7th ed.). Pearson.
Bottom of Form
