authoradba
Kanchana Selvakumar
17 Sep 2014 . 3 min read

Designing Tables in Relational Database Systems


Share the Article :

https://img.sheroes.in/img/default_img.jpg https://img.sheroes.in/img/default_img.jpg

Apple has unveiled iPhone 6, a larger handset of 4.7 inches in gold and dark black colours and the iPhone 6 plus, recently. It also has unveiled a rectangular wrist watch and ‘Apple pay’ after two years. The watch is a comprehensive health and fitness device, synched to the internet and precise to 50 milliseconds said the company. It also allows users to dictate messages via a microphone. There are two sizes of the watch, dubbed as male and female versions.

Now to referential integrity.

The Transaction table (refer May article for the table definitions) has columns as shown

TRANSACTION

Transaction_no
Customer_no
Amount
Desc
Time_Stamp
Balance

CUSTOMERSWhen a record of  a transaction is entered the row should have a valid Customer_no in the customers table .The CUSTOMERS table’s structure is as below which we have seen in the previous article.

Customer_no
Cust_name
Mid_name
Last_name
Street Name
City 
Pincode
Email

How to ensure that such a logical violation does not happen and that data integrity is maintained? The relationship between CUSTOMERS table and TRANSACTION table is one too many. HereThe system should not allow a transaction without a valid customer record in the CUSTOMERS table to be entered in the TRANSACTION table. Otherwise, the solution is to create  the customer record in the CUSTOMERS table and then the transaction gets entered.

Customer_no the foreign key in TRANSACTIONS should have a corresponding primary key in the CUSTOMERS table. (Refer May article)  For one customer there may be many rows in the transaction table according to  the transactions she makes. For every transaction row in the TRANSACTION table there should exist a corresponding row in CUSTOMERS that identifies the customer. In Oracle the ‘References Constraint’ is used to ensure that a valid customer exists before the dependent row is added in the TRANSACTIONS table. In Oracle the transaction* of inserting the record in TRANSACTION table is rolled back if the customer did not exist in the CUSTOMERS table. Rolling back means the transaction does not happen (or is not committed).

*Here the activity of entering the row in the table is referred to as a transaction. The transaction getting committed means the activity was successful and rolling back the transaction means it was not successful.

Therefore the logical relationship between the two tables is ensured or maintained using this constraint. This concept is called Referential Integrity and is critical to the building of logical relationships between tables and ensuring that they are maintained in a relational database system.

Suppose a customer record is deleted in the CUSTOMERS table then all the corresponding transactions of the customer should be removed from the TRANSACTION table. It is strenuous to remove them manually. Again the referential integrity concept is applied and a Casacading Delete is done. This means the corresponding rows of that customer in the TRANSACTION table get deleted automatically when such an event happens. The same constraint helps to implement cascading effects in updation of records. This means that if a primary key value, that is customer number changes in the CUSTOMERS table, the corresponding records in the TRANSACTION table should reflect that. Hence, Referential integrity ensures logical consistency in the relationship between the tables in insert, update and delete operations.

The syntax to create the constraint is as below

ALTER TABLE transactions

ADD

CONSTRAINT (cust_no_fk) FOREIGN KEY (customer_no) REFERENCES customers(customer_no); the statement means that we are adding a referential integrity constraint called cust_no_fk to the transactions table. It refers to the primary key in customers table to ensure data integrity.

Referential integrity is an important concept in relational databases and enforces business rules and ensures data integrity. The designers of the tables should be well aware of this concept and implementation.


Customer_database_kanchana_sept
Kanchana Selvakumar
Kanchana is a passionate Creative Writer. She has immense interest in visualising technology through creativity. Her creative technical articles are posted at OradbaIndia . She is basically an electronics engineer with 15+ years of Information Technology experience which includes working as a freelancer and remote working. She is also passionate about English Poetry. Poetry including picture poetry can be read P4Poetry .If not in the vast world of software and systems, she likes to be lost in the exciting tiny world of her kids.

Explore more on SHEROES

Share the Article :

Responses

    Similar Articles You love
    Download App

    Get The App

    Experience the best of SHEROES - Download the Free Mobile APP Now!