Relational Database Systems - Next Step

‘June’ is the in thing, though it is August. This latest jewellery, an E- Jewellery protects you from SunBurns. Netatmo, the French company that designed the product says that the device monitors your exposure through the day, communicates with your smart phone and gives real time advice to protect you from sun. It can be worn as a bracelet and the associated iOS app does a lot of interesting things.This app can figure out the skin sensitivity, daily weather forecast ,warn you when your skin is going to burn and a list of things to carry with you that day .It tracks your activity with a timeline called ‘Sundose’.If that crosses 100, beware of burns.

Now, answers to last month’s questions.

Check Constraints are used to check and limit the range of values entered in a table’s column.For the Transaction table whose columns we defined earlier (refer June article) with column names as listed below “Transaction_no, Customer_no FK, Acc_no, Amount, Desc , Time_stamp ,Balance”

The table definition in Sql server can be

CREATE TABLE TRanxns

{

T_Id int NOT NULL UNIQUE,

…

Amount int CHECK (Amount > 0 )

..

}

The CHECK that follows the definition of ‘Amount’ says that value in amount field cannot be negative.

Every transaction has to be identified uniquely and we have the unique constraint that ensures that when included in the definition of the table. That is why the T_Id column as defined above ’ T_Id int NOT NULL UNIQUE ‘ has the UNIQUE tag .It ensures the value entered in not null and is unique.

These constraints like check,unique and more are called integrity constraints.The term as it implies means that they ensure that the data that gets stored in a database in a relational model is consistent and correct.

So defining the domain of a column ,that is identifying the data type and also if there are any limits on allowable values ,is a very important step in ensuring consistent data.

What then are business rules?

Let us say am running a Lending Library.Some rules to run the business would be like. Only books in the available list, currently can be booked.

Return date cannot be less than today’s date or order date.If the return date gets greater than the date in the recorded date then the fine amount is not zero.