Question

Answer following questions briefly

1. Explain difference between Disjoint and Overlap constraint rules for super-type and sub-type entity with example of each.
2. Explain in brief: Primary Key, Candidate Key, and Surrogate Key
3. Explain the difference between WHERE and HAVING clause of SQL

Answer 1

i.) The disjoint rule states an entity instance of a supertype can only be a member of one subtype i.e. The disjoint constraint only applies when a superclass has more than one subclass. If the subclass is disjoint, then an entity occurrence can be a member of only one of the subclasses, e.g. student or instructor, you cannot be both.

The overlap rule states an entity instance of a supertype can be a member of multiple subtypes i.e. The overlapping constraints applies when a superclass has more than one subclass. On the other hand, If the subclass is overlapping, then an entity occurrence can be a member of both subclasses, e.g. Consider an employee work-team and assume that certain employees participate in more than one work team. A given employee may therefore appear in more than one of the team entity sets that are lower-level entity sets of employees.

ii.) PRIMARY KEY is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. A table cannot have more than one primary key.

Rules for defining Primary key:

• Two rows can't have the same primary key value
• It must for every row to have a primary key value.
• The primary key field cannot be null.
• The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.

In the following example, StudID is a Primary Key.

StudID	Roll No	First Name	LastName	Email
1	11	Tom	Price	[email protected]
2	12	Nick	Wright	[email protected]
3	13	Dana	Natan	[email protected]

CANDIDATE KEY is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.

Properties of Candidate key:

• It must contain unique values
• Candidate key may have multiple attributes
• Must not contain null values
• It should contain minimum fields to ensure uniqueness
• Uniquely identify each record in a table

Example: In the given table Stud ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.

StudID	Roll No	First Name	LastName	Email
1	11	Tom	Price	[email protected]
2	12	Nick	Wright	[email protected]
3	13	Dana	Natan	[email protected]

SURROGATE KEY is an artificial key which aims to uniquely identify each record is called a surrogate key. This kind of partial key in dbms is unique because it is created when you don't have any natural primary key. They do not lend any meaning to the data in the table. Surrogate key is usually an integer. A surrogate key is a value generated right before the record is inserted into a table.

Fname	Lastname	Start Time	End Time
Anne	Smith	09:00	18:00
Jack	Francis	08:00	17:00
Anna	McLean	11:00	20:00
Shown	Willam	14:00	23:00

Above, given example, shows shift timings of the different employee, a surrogate key to uniquely identify each employee.

Surrogate keys in sql are allowed when

• No property has the parameter of the primary key.
• In the table when the primary key is too big or complicated.

iii.) WHERE Clause:
WHERE Clause is used to filter the records from the table or used while joining more than one table.Only those records will be extracted who are satisfying the specified condition in WHERE clause. It can be used with SELECT, UPDATE, DELETE statements.

HAVING Clause:
HAVING Clause is used to filter the records from the groups based on the given condition in the HAVING Clause. Those groups who will satisfy the given condition will appear in the final result. HAVING Clause can only be used
with SELECT statement.

The key differences are:

SR.NO.	WHERE CLAUSE	HAVING CLAUSE
1.	WHERE Clause is used to filter the records from the table based on the specified condition.	HAVING Clause is used to filter record from the groups based on the specified condition.
2.	WHERE Clause can be used without GROUP BY Clause	HAVING Clause cannot be used without GROUP BY Clause
3.	WHERE Clause implements in row operations	HAVING Clause implements in column operation
4.	WHERE Clause cannot contain aggregate function	HAVING Clause can contain aggregate function
5.	WHERE Clause can be used with SELECT, UPDATE, DELETE statement.	HAVING Clause can only be used with SELECT statement.
6.	WHERE Clause is used before GROUP BY Clause	HAVING Clause is used after GROUP BY Clause
7.	WHERE Clause is used with single row function like UPPER, LOWER etc.	HAVING Clause is used with multiple row function like SUM, COUNT etc.