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.
Booking Charges can never be zero or negative.
A minimum number of books available cannot be zero at any point in time.Books under ‘high valued books’ category should be held for lesser number of days than that of lesser valued books.
The rules to run my business are unique and cannot be generalised or applied to any other business. Business rules are an important part of integrity enforcement.
The source for formulating business rules for a database?
The policy makers who are most likely the department managers, heads, company managers etc will ensure the creation of a document of standard operating systems and procedures in the form of manuals. Some of these are all also derived out of feedback from end users .Every organisation has its own unique set of business rules. That which is applicable to all organisations does not exist. These rules are formulated based on each organisation’s code of business conduct and data and information requirements.