Loading....
Coupon Accepted Successfully!

 

Constraints of relational databases are

  • Domain - the value of each attribute of a relation must be an indivisible value and must be drawn out of possible values associated with its domain. The value of an attribute, therefore, must conform to the data type associated with the domain.

  • Key Constraints - a key is part of a tuple (one or more attributes) that uniquely distinguishes it from other tuples in a given relation. Of course, in the extreme, the entire tuple is the key since each tuple in the relation is guaranteed to be unique. However, we are interested in smaller keys, if they exist, for a number of practical reasons. First, keys will typically be used as links, ie., key values will appear in other relations to represent their associated tuples. Thus, keys should be as small as possible and comprise only non - redundant attributes to avoid unnecessary duplication of data across relations. Second, keys from the basis for constructing indexes to speed up retrieval of tuples from a relation. Small keys will decrease the size of indexes and the time to look up an index.

Customer

     

C#

Cname

Ccity

CPhone

1

Amar

New Delhi

2263035

2

Mithilesh

Jaipur

5555910

3

Deepak

New Delhi

2234391

 

The customer number (C#) attributes is clearly designed to uniquely identify a customer. Thus, we would not find two or more tuples in the relation having the same customer number and therefore it can serve as a unique key to tuples in the relation.

However, there may be more than one such key in any relation, and these keys may arise from natural attributes of the entity represented (rather than a contrived one, like customer number). Examining again, no two or more tuples have the same value combination of Ccity and Cphone. If we can safely assume that no customer will share a residence and phone number with any other customer, then this combination is one such key. Next, note that Cphone is not alone - there are two tuples with the same Cphone value (telephone numbers in different cities that happen to be the same). And neither is Ccity alone, as we may expect many customers to live in a given city.

 

Customer

     

C#

Cname

Ccity

Cphone

1

Amar

New Delhi

832551

2

Mithilesh

Jaipur

832551

3

Deepak

New Delhi

183451

C# Key - 1

 

Ccity & Cphone - Key2

 

 

While a relation may have two or more candidate keys, one must be selected and designated as the primary key in the database schema. In the example given above, C# is the obvious choice as a primary key for the reasons stated earlier. When the primary key values of one relation appear in other relations, they are termed foreign keys. Note that foreign keys may have redundant occurrences in a relation, while primary keys may not.

 

For example, C# in Transaction is a foreign key and the key value '1' occurs in two different tuples. This is allowed because a foreign key is only a reference to a tuple in another relation, unlike a primary key value, which must uniquely identify a tuple in the relation.
  • Entity Integrity Constraint - Entity integrity constraint requires that primary key value cannot be null because it is used for the identification of the individual tuple in a relation. Null value shows non -identification or duplicity or failure to distinguish such tuples.
  • Referential Integrity Constraint - We have discussed above the key and entity constraints which are specified on individual relations. The referential integrity constraint, on the other hand, is specified between two or more relations. This constraint is specified to maintain consistency among the tuples of such relations.

Operations and Constraint Violations


Relational model has two operations ie., updates and retrieval. The following three basic operations, when applied, should enforce integrity constraints specified on relational database:

Insert - This command is used to add a new tuple in a relation or, we can say, another record of an account with data values corresponding to Code, Name and its Type to Accounts relation shall be made by this command. It is capable of violating any of the four constraints discussed above.

Delete - This command is used to remove a tuple from a relation. A particular data record from a table can be removed by using this command.

Modify - It is used to change the values of some attributes existing in tuples. This is useful in modifying the existing values of an accounting record in a data table.

Designing Relational Database Schema


In the context of ER model, the following specific steps are required to cause its transformation into relational data model:

Create a relation for every strong entity - For each strong entity type in ER schema, choose a separate relation that includes key attributes of such an entity as the primary key for this relation, or choose a set of simple attributes that uniquely identify this entity as the primary key of the relation thus created. A separate relation for Employee as given below has been created:

Employee (EmpId, Fname, Lname, Address, PhoneNo, SuperId). Like that, separate relations need to be created for the following strong entities whose Primary key attributes have been given.

Accounts (Code, Name, Type)
Vouchers (Vno, Vdate, amount, narration)
Accounts Type (CatId, Category)

Create a separate relation for each weak entity type: Every weak entity is related to a main entity though its relationship. We have already discussed that a voucher is not meaningful without its support. In this context, Support Entity, with Vouchers as its owner or main Entity, does not have a primary key of its own. It has a partial key which is the Sno assigned to each document. Therefore, the Primary key of Vouchers, Vno, along with Sno is designed as a composite key for support entity and the relation so formed is shown as under:

Support (vNo, Sno, dName, sDate)

Interacting with Database


SQL (Standard Query Language) is the most popular query language for relational DBMS, and most of the commercial relational database management systems use SQL or a variant of SQL. The original version of SQL was called SEQUEL. SEQUEL is a relational query language based on neither relational algebra nor relational calculus. It is a non - procedural language where the concept of universal quantifiers is not used. A block - structured format of English key words is used in this query language. It is intended for interactive use by people who are not specialists in computers.

Many versions of SEQUEL are available today for commercial applications. Almost all of these versions include facilities for query formulation, insertion, deletion and update operations, as well as the ability to create new relations and to control the set of indexes maintained on the database.

We will introduce you with the query processing facilities of SEQUEL, by citing a series of examples.

Consider the following database:
Employees (e_no, e_name, address, basic_salary, job_status)
Projects (p_no, p_name, nos_of_staff)
Work_in (p_no, e_no, p_job)

Commands for Data Definition


We can create the above database using the following commands of SQL.

CREATE TABLE employees (e_no NUMBER (5), e_name CHAR (25), address CHAR (30), basic _ salary NUMBER (7,2), job_status CHAR (15));

CREATE TABLE projects (p_no CHAR(5), p_name CHAR (30), nos_of_staff NUMBER(3));

CREATE TABLE work_in (p_no CHAR(5), e_no NUMBER (5), p_job CHAR(12));




Test Your Skills Now!
Take a Quiz now
Reviewer Name