sap4help@gmail.com +91 8956877466
This website is always under updation with new contents
Unit - III The Relational Model
Unit I
Unit II
Unit III
Unit IV
Unit V
Write command for creating table in “Employee “in SQL and insert data shown below.
Empno		ename		job		   join_date
E1001		ABC		Clerk		    5-jan-05
E1002		PQR 		Manager	       	    10-jan-05
E1003		MNO 		Manager	    	    06-jan-05
E1004		XYZ 		Analyst		    08-jan-05
Ans:
command for creating a table:
SQL> create table employee (empno char (6),
ename varchar2 (15), job varchar2 (15), join_date date));
Command for inserting the first record:
SQL> Insert into employee (empno,ename,job,join_date) values
(‘E1001’,’ABC’, ‘Clerk’,’5-jan-05’);
Command for inserting the second record:
SQL> Insert into employee (empno,ename,job,join_date) values
(‘E1002’,’PQR’, ‘Manager’,’10-jan-05’);
Command for inserting the third record:
SQL> Insert into employee (empno,ename,job,join_date) values
(‘E1003’,’ MNO’,’Manager’,’06-jan-05’);
Command for inserting the fourth record:
SQL> Insert into employee (empno,ename,job,join_date) values
(‘E1004’,’XYZ’,’Analyst’,’08-jan-05’);
what are the basic operations in relational algebra. Explain it with example.
Ans: The relational algebra is a procedural query language. It consists of a collection of operation which takes one or two relations as an input and produce a new relation as their result.
As the relational algebra is the collection of operations they are divided into two groups namely.
(1) Fundamental Operations
(2) Special Operation
The special operations are defined in terms of the fundamental operations. These operations are as follows:
Fundamental operations:
1) Select operation: this operation can be used to extract specified rows from the table that satisfy the given predicate.
2) The sigma (б) is used to denote the selection
3) The predicate appears as the subscript to б. The argument relation is given in parenthesis following the б as given below
б predicate(argument relation)
4) Several predicated can be combined together by using logical connectives (ѵ) and or (Λ) .
5) The comparison operators such as =,!=,==, <,<=,>=, can be used in the selection predicate.
6) For ex. Suppose we want to extract the information of only those customer who lives in pune city then the relation is as shown in fig.

2) Project Operation
1) It is a unary operation used to extract the specified columns from the table.
2) It is denoted by the symbol π.
3) By using project operation we can list those attribute that we want in the resulting relation as a subscript to π and the argument is written in the parenthesis as shown below.

what is relational calculus? Explain tuple calculus with example.
Ans:
Relational calculus:
• It is a query system where queries are expressed as variables and formulas on these variables. These variables are called as tuple variables. The formulas describe the properties of the required result relation without specifying the method of evaluation. Relational calculus means calculus with relations which are based on predicate calculus. Relational calculus may be based on tuple or domain.
• Depending on this there are two types of relational calculus.
o Tuple oriented calculus
o Domain oriented calculus
• It is a non procedural language and uses a formal language. It uses a natural language used to express the logical arguments in arguments in mathematics.
• Tuple oriented calculus is a non procedural query language. It describes the desired information without giving a specific procedure for obtaining that information.
• A query in topple oriented relational calculus is expressed as {t|p(t)}
o It is set of all tuples t such that the predicate P is true for t.
o t[A]- represents value of that tuple (t) on attribute for t.
o TєR – represent that tuple is in relation R.
Example:-
1)	Find the branch name, loan number, customer-name and amount for loans of over Rs 2000.
{t\t є borrow Λ t [amount]>1200}
2)	 Find all customers who have an account at all branches located in pune.
In this example “for all” construct is denoted by Ѵ.
a.	Ѵ t є r(Q(t))
This means Q is true for all tuples in relation r.
Formal definition of tuple oriented calculus:
 A tuple relational calculau expression is of the from 
{t|P(t)}
Where P- formula
	T- tuple variable
Several tuple variable may appear in a formula. A tuple variable is said to be a free variables unless it is 


What is relational database? Explain attributes and domains with example.
Ans: Short for relational database management system and pronounced as separate letters, a type of database management system (DBMS) that stores data in the form of related tables. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways.
An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

Attribute: consider an employee relation. It consists of five columns namely emono, ename, job, sal ,deptno etc. This columns heads as the attributes of the relation.
As an entity in an enterprise is characterized by its properties. These properties are nothing but the attributes of the relation.
Domain
• A domain can be defined as the set of scalar values of the same data type.
• Consider a customer relation shown in fig. For example the domain of customer number is the set of all possible customer numbers. Similarly the domain of balance amount is the set of all integers greater than Zero and less than one million .
• Thus the domain of an attribute can be defined as the set of allowable values from which the actual attribute values can be obtained.
• The values of an attribute must be taken from the domain only. Thus at any given time the set of cust_id# values in Cust relation must be the subset of the set of values appearing in the cust _id domain.
• A domain is of two types:

State integrity rules. Also gives example.
Ans: There are two important integrity rules. These rules are basically constraints or re0strictions that apply to all instances of the database. These rules are as follows:
i) Entity integrity rule.
ii) Referential integrity rules
Entity integrity rule
This rules states that “In a relation or table , the value of attribute of a primary key cannot be null “. A primary key is a minimal identifier that is used to identify tuple uniquely. This means that no subset of the primary key is sufficient to provide unique identification to tuples. If we allow a null for any part of a primary key, we are implying that not all the attributes are needed to distinguish between tuples w hich contradicts the definition of the primary key.
For example in a student relation as given below:
Student (rno, name, address, age). If rno of the student is the primary key of this table then we should not be able to insert a tuple into this student. Relation with a null for the rno attributes. If a user tries to do so then RDBMS reports an error messages.
Referential integrity rules
This rule states that “ If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null. “ . So it is property that insures that values from one column depend upon values in another column. This property can be enforced through this rule.
For ex: we try to create tables to store student data. Normally all data related to student is not stored in a single table. Data that is permanent like his/her date of birth, address; name can be stored in one table referred to as the master table or the parent table. This table will contain only record for every student. On the other hand the marks table, so this table will store that data about examination only. That is stud_addr is the master table and stud_mark is a transaction /child table.

Write short note on SQL.
Ans:
Abbreviation of structured query language, and pronounced either see-kwell or as separate letters. SQL is a standardized query language for requesting information from a database. The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975. SQL was first introduced as a commercial database system in 1979 by Oracle Corporation.
Database management is a complicated process, which has been considerably rationalized by the SQL programming language. As its full name (Structured Query Language) implies, SQL is responsible for querying and editing information stored in a certain database management system.
Historically, SQL has been the favorite query language for database management systems running on minicomputers and mainframes. Increasingly, however, SQL is being supported by PC database systems because it supports distributed databases (databases that are spread out over several computer systems). This enables several users on a local-area network to access the same database simultaneously.
Now into its third decade of existence, SQL offers great flexibility to users by supporting distributed databases, i.e. databases that can be run on several computer networks at a time. Certified by ANSI and ISO, SQL has become a database query language standard, lying in the basis of a variety of well established database applications on the Internet today. It serves both industry-level and academic needs and is used on both individual computers and corporate servers. With the progress in database technology SQL-based applications have become increasingly affordable for the regular user. This is due to the introduction of various open-source SQL database solutions such as MySQL, PostgreSQL, SQLite, Firebird, and many more.
Explain any three data manipulation commands in SQL.
Ans: SQL data manipulation
Data manipulation is essential for SQL tables - it allows you to modify an already created table with new information, update the already existing values or delete them.
With the INSERT statement, you can add new rows to an already existing table. New rows can contain information from the start, or can be with a NULL value.
Syntax:
INSERT INTO tablename (Columnname1, columnname2,………) VALUES (Expression1, Expression2……..);
An example of an SQL INSERT
INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America');
With the UPDATE statement, you can easily modify the already existing information in an SQL table.
Synatx:
UPDATE tablename SET COLUMNNAME=Expression, COLUMNNAME=Expression WHERE WHERE search condition;
An example of an SQL UPDATE
UPDATE phonebook SET address = 'North America', phone = '+1 123 456 7890' WHERE firstname = 'John' AND lastname = 'Doe';
Delete Command:
With the DELETE statement you can remove uneeded rows from a table.
Synatx:
DELETE FROM tablename
Or
DELETE FROM tablename WHERE search condition;
An example of an SQL DELETE
DELETE FROM phonebook WHERE firstname = 'John' AND lastname = 'Doe';
Explain the natural join operation in relational algebra with suitable example.
Ans: Consider tow relations A and B have headings as given below;
A{x1, x2,………xm, Y1,Y2……….Yn}
B{Y1,Y2………Yn,Z1,Z2…………Zp}
Respectively. Attribute Y1,Y2…….Yn are common in the relation A and B at attributes X1,X2,……..Xm of A and attributes Z1,Z2,……Zp are the other attributes of B. Then the natural join of A and B can be represented as

A JOIN B is a relation with heading {X1,X2…….Xm, Y1,Y2…..Yn, Z1,Z2…..Zp} and the body consists of the set of all tuples { X:x , Y:y, Z:z}
A natural join is a binary opearation. It is used to combine certain selections and a Cartesian product into one operation. The natural join operation first find out the Cartesian product of its two arguments then select the attribute which are common in both relations and then finally removes the duplicate columns.
Natural join can be defined as :

Properties of natural join
Explain the Like operator in SQL with a suitable example.
Ans: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The LIKE Operator: The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax:
SELECT column_name(s) FROM table_name
	WHERE  column_name LIKE pattern
LIKE Operator Example
The "Persons" table:

Next, we want to select the persons living in a city that ends with an "s" from the "Persons" table.
We use the following SELECT statement:
Select * from persons where city like ‘%s’
The result-set will look like this:

Explain the Cartesian product in relational algebra with suitable example.
Ans: Cartesian product(X)
• The Cartesian product of two relations is the concatenation of tuples belonging to the two relations.
• It is denoted by X sign. If X and Y are two relations then Cartesian product of these two relation results into a relation R and represented as R=X x Y
• Relation R contain all possible combination of tuples in X and Y.
• For Cartesian product operation compatible operation are not required.
Example:The following are two relations:
Student (enrlNo, Name) and Branch (branch_name)

Consider the following relational scheme of database
Employee ( Empno, name, address)
Project ( P-no, pname)
Workon (empno, p-no)
Part (part-no, partname, qty-on-hnd, size)
Use (empno, p-no, part-no, number)
Answer the following queries in relational algebra.
1) Print the names of the employees who are working on project named “DBMS”
2) Print the names of employee who are not working in any project.
3) Print the names of the employees and the project with which the employees are associated with the for which they have used no part so far.
4) Print the part number and names of the parts used in both the projects “DBMS” and “MIS”
Ans: Query 1):

Explain the domain constraint with suitable example.
Ans: Domain constraints are a user-defined data type which enforces the integrity of the standard data types. A domain consists of all values permitted in a column. In constructing your database you would first select a standard data type such as VARCHAR or INT. You could then constrain or limit the data using NOT NULL, UNIQUE, CHECK, PRIMARY KEY or FOREIGN KEY. For example the CHECK constraint could limit INT data within your domain [column] to numbers less than 5000. You would thus define a custom data type using domain constraints.
The check clause in SQL-92 permits domains to be restricted in powerful ways that most programming language type systems do not permit.
The check clause permits schema designer to specify a predicate that must be satisfied by any value assigned to a variable whose type is the domain.
Examples:
Create domain hourly-wage numeric (5,2)
Constraint wage-value-test check (value >= 4.00))
Note: constraint wage-value-test" is optional (to give a name to the test to signal which constraint is violated).

create domain account-number char(10)
constraint account-number-null-test check(value not null))

create domain account-type char(10) 
constraint account-type-test check(value in (“Checking", “Saving"))
Explain Group by …….Having clause with suitable example.
Ans: Group by …….Having clause :
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;
aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;
Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.
Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;
aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.
Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
Explain the super key, candidate key and primary key with suitable example.
Ans:
super key
• A super key is a set of one or more attributes which are collectively used to identify uniquely an entity in the entity set.
• Super key may have extra attribute i.e if k is a super key then any super set of k is also a super key.
• For ex. In a banking database the enity set CUSTOMER has the attributes such as customer_id, customer_name, customer_address as shown in fig.

• Here customer_id is a super key of the CUSTOMER entity. Similarly (customer_id, customer_name) is a super key but customer_name independently is not a super key. As two or more customers in the set may have same name but they are having a unique id.
Disadvantages : A super key may have extra attributes.
Candiadate key
• The concept of super key is not sufficient as it may contain extraneous attribute i.e. if k is a super key then any super set of K is also a super key.
• Such super keys for which no proper subset is a super key. Such minimal super keys are called as candidate keys.
• Definition: The candidate key can be deifned as when two or more attributes uniquely identifies an instances of an entity set. These attributes or the combination of attributes are called as candidate key.
• Let R be a relation . Then a candiadate key for R is a subset of the set of attributes of R say K such that it satisfies the following properties.
1) Uniqueness:
2) No two distinct tuples of relation R have the same value for k.
Non –reducibility:
• There is no proper subset of K such that it satisfies uniqueness property.
• As the relation do not contain the duplicate tuples. Therfore every relation does have at least one candidate key. Note that several sets of attributes may be served as a candidate key.
• Consider, a customer entity is having the attributes social-secourity, customer-add etc. as shown in fig.

• Suppose that a combination of customer_name, customer_add is sufficient to identify a particular customer from the customer entity set. Both social _security and {customer_name, customer_add} are candidate keys.
Primary key:
• Primary key is a candidate key that is chosen by the database designer for identifying entities within an entity set.
• An entity set which has a primary key is called a strong entity set. It is possible that an entity set does not have sufficient attributes to from a primary key is called as a weak entity set as shown in fig.

• Consider the entity set transaction which has three attributes transaction_numbeer, date and amount. This entity set does not have a primary key because the transaction on different accounts may share the same transaction number.
For example:
• In Customer entity {customer_id } is a primary key.
• The primary key of an entity set is used to distinguish between various relationships of a relationship set.
Consider the following relational database schema
Account(branch_name,acct_no, balance)
Branch (branch_name,branch_city,assets)
Customer(cust_name,cust_street,cust_city)
Depositor(cust_name,act_no)
Loan(branch_name,loan_no,amount)
Borrower(cust_name,loan_no)
Answer the following query in relational algebra:
1) Find those customer who live in ‘Delhi’;
2) Find the names of all bank customers who have either an account or a loan or both.
3) Find the names of all customers who have a loan at ‘Mahal’ branch of Naglur city.
4) Find all customers who have both a loan and account at Bank.
Ans: Query1 : πcustname(бcity=’Delhi’(Customer))
Query2 :

Explain the domain relational calculus.
Ans: There is a second form of relational calculus called domain relational calculus. This is also a non-procedural query language. This from uses domain variables that take on values from an attribute‘s domain rather than values for an entire tuple. The domain relational calculus is closely related to the tuple relational calculus.
Definition:
Ans: An expression in the domain relational calculus is of the from.
{|P(x1,x2….xn)}
 Where x1,x2……xn represents  domain variables.
P- Represents a formula composed of atoms.
An atom in the domain relational calculus has one of the following forms.
1)єr
Where r-relation on n attributes.
X1,x2,….xn –domain  variables or domain constants.


Domain relational calculus queries: Find the branchname, loannumber and amount for loans of over Rs. 1500.
For accessing computer programs go to TECHNOLOGY