Equi-Join

Equi-Join

Equi-Join is a joining technique which uses equality operator (=) to match columns of two different tables to join those tables.

Consider the following query which performs equijoin;

SELECT * FROM Student, Course_Registration WHERE Student.RegNo = Course_Registration.RegNo;

Example

Let us take the following tables, Student (which stores personal information about students), Course_registration (which stores information about courses registered by students) to explain equi-join.

RegNo	SName	Gen	Phone
R1	Sundar	M	9898786756
R3	Karthik	M	8798987867
R4	John	M	7898886756
R2	Ram	M	9897786776

Table 1 – STUDENT

RegNo	Courses
R1	Database
R2	Database
R3	Data Structures
R4	Multimedia

Table 2 – COURSE_REGISTRATION

After the execution of query, we will get the following table as the result, where every record of Student is joined with only matching records in Course_Registration.

RegNo	SName	Gen	Phone	RegNo	Courses
R1	Sundar	M	9898786756	R1	Database
R2	Ram	M	9897786776	R2	Database
R3	Karthik	M	8798987867	R3	Data Structures
R4	John	M	7898886756	R4	Multimedia

Table 3 – Joined Table

How the tables are compared internally?

In the join query, you carefully observe the condition given in the WHERE clause.

SELECT * FROM Student, Course_Registration WHERE Student.RegNo = Course_Registration.RegNo;

The WHERE clause includes a condition which matches the RegNo column of STUDENT with RegNo column of COURSE_REGISTRATION. You notice the order of records in Table 1 and Table 2. The records are actually of different order of RegNo values. So, how these two tables are joined in Table 3 correctly?

Joining is done actually using the Join condition attributes RegNo of both tables. Here, to match the student records with the courses registered records, the system matches every RegNo value of STUDENT table with every RegNo value of COURSE_REGISTRATION table. If both values are same, the concerned records are included in the result set. If not, the records are discarded. In other words,

4 (records) X 4 (records) = 16 records

are compared to arrive at the result as given below. Here, the matched records are included in the final result, other records are not. Hence, out of 16 comparisons only 4 are matched correctly, 12 are discarded.

Student.RegNo = Course_Registration.RegNo	Included or not?
1st record of STUDENT R1 = R1	Yes
R1 = R2	No
R1 = R3	No
R1 = R4	No
2nd record of STUDENT R3 = R1	No
R3 = R2	No
R3 = R3	Yes
R3 = R4	No
3rd record of STUDENT R4 = R1	No
R4 = R2	No
R4 = R3	No
R4 = R4	Yes
4th record of STUDENT R2 = R1	No
R2 = R2	Yes
R2 = R3	No
R2 = R4	No

Notes:

1. We are matching all the values of joining attribute of one table with all the values of joining attribute of other table, because in RDBMS we do not impose arranging records in any particular order physically.

2. The joining attributes need not be with same name. They can have different names. Only condition is they both must be of same domain.
3. In the result both columns (joining attributes) are included. See Table 3. In Natural Join, only one will be shown in the final result. The other one will be treated as redundant.
4. The above given equi-join  query can also be written as follows in SQL;

SELECT * FROM Student JOIN Course_Registration WHERE Student.RegNo = Course_Registration.RegNo;

************

Go to Natural Join in SQL page

Go to Multiple choice questions in SQL/Relational algebra page
 

ER Model Quiz 2

DBMS Basics and Entity-Relationship Model - Quiz 2

1. Execution of a Data Definition Language (DDL) statement will

Create a Table
Update Data Dictionary
Both Create a Table and Update Data Dictionary
Define the User Privileges

2. ________________ need an user to specify in a query "What data are required and how to retrieve the required data?"

Procedural DML
Declarative DML
Non-Procedural DML
Transaction Control Language

3. Which of the following users interact with the database directly using Query languages?

Naive Users
Specialized Users
Sophisticated Users
Applicaton Programmers

4. Which of the following is one of the main tasks of Database Administrator (DBA)?

Interface Program Development
Writing DML Precompilers
Schema Definition
None of the above

5. The number of entity sets linked through a single relationship set is

Degree of Relationship set
Degree of Entity set
Degree of Attribute set
All of the above

6. If single record in relation R is associated with zero or more number of records in relation S, and single record in S is associated with any number of records in R, then the relationship is

One-to-One
One-to-Many
Many-to-One
Many-to-Many

7. If single record in relation R is associated with zero or more number of records in relation S, and any single record in S is associated with only one record in R, then the relationship is

One-to-One
One-to-Many from R to S
Many-to-One from R to S
Many-to-Many

8. A minimal super key (i.e, one of the super keys for which no proper subset is a super key) is called

Super Key
Candidate Key
Primary Key
Both Candidate and Primary key

9. An entity set which does not have enough attributes to form a Primary key is

Strong Entity Set
Weak Entity Set
Strong Relationship Set
Weak Relationship Set

10. In an ER Diagram, a double ellipse is used to represent

Simple Attribute
Composite Attribute
Descriptive Attribute
Multi-valued Attribute

Score =

Correct answers:

ER Model - Quiz 1

DBMS Basics and Entity-Relationship Model - Quiz 1

1. ER modeling is primarily used for

Database Programming
Organizing Databases
Designing Databases
None of the above

2. An Entity Set is

a set of entities of the same type that share the same properties
a set of entities of different type that share the same properties
a set of entities of different type that share the different properties
None of the above

3. The set of permitted values for an attribute is called as

Entity Set
Domain
Value Set
Both Domain and Value Set

4. An attribute which can have many values for a single entity is called as

Composite Attribute
Descriptive Attribute
Derived Attribute
Multivalued Attribute

5. If a record in table R is associated with only one record in table S, and a record in S is associated with only one record in table R, then the relationship between R and S is

One-to-Many
Many-to-One
One-to-One
Many-to-Many

6. The first step in database design is

Physical Database Design
Logical Database Design
Conceptual Database Design
Requirements Gathering and Analysis

7. A relationship is an association between

Two or more Entities
One or more Entities
Entities and Attributes
One or more Attributes

8. Which of the following is not the disadvantage of File Processing System?

Data isolation
Atomicity Problems
Data Redundancy
None of the above

9. The status of a relational table at a particular moment is called

Logical Schema
Metadata
Instance
Subschema

10. Data-Manipulation Language (DML) is a language that enables users to

Design the Database
Insert Data into the Database
Control the Transactions in Database
Define User Privileges

Score =

Correct answers:

Conversion of Composite Attribute to Relation Schema

Composite Attribute to Relation Schema

A Composite Attribute can be converted into Relation Schema using the following rule;

• Create separate columns for every component attributes (only simple attributes) of any composite attribute. Do not include separate column for composite attribute itself.

Figure 1

Consider the ER diagram given in Figure 1. Let us consider the Entity Set Employee which has two composite attributes namely, EName and Address with component attributes (FName, LName) and (DoorNo, Street, City, Phone) respectively.

According to the rule, we need to create column for every component attribute and not for composite attribute itself. Hence, the relation schema will like the following;

Employee(EID, FName, LName, DoorNo, Street, City, Phone)

The component attributes are underlined. Note that, the composite attributes are not included.

Conversion of ER Diagram to Relation Schema - Index

Conversion of ER Diagram to Relation Schema - Index

• Strong Entity Set

• Weak Entity Set

• Relationship Set

• Composite Attribute

• Multi-valued Attribute

Click here to go to - ER Diagram to Relational Schema - Solved Exercises

“Any fool can know. The point is to understand.” Albert Einstein

Strong Entity Set

 Strong Entity Set to Relation Schema

A strong entity set is an entity set that has one or more attributes as a key to uniquely identify each entity.

 
A Strong Entity Set can be converted into Relations Schema using the following rule;

• Convert a strong entity set into relation schema with all the simple attributes as columns of the relation schema and name of the entity set as name of the relation schema.

Consider the entity sets Department and Employee in Figure 1. Both are Strong as they have primary keys (DNo for Department, EID from Employee). Let us convert the Department table into relation schema as follows;

Department (DNo, DName, DLocation)

Not all the attributes are simple for Employee, so, we shall discuss it later.

*********

Go to Entity Relationship model home

Exam Questions - RDBMS Basics, ER Model, and Normalization Basics

Some RDBMS Exam Questions:

1. Define the three levels of Data Abstraction.

2. Define Data Independence.

3. What is view?

4. Describe Entity and Entity Sets.

5. Differentiate Strong and Weak Entity Sets.

6. What is Conceptual Schema?

7. How many types of SQL statements are there?

8. Define Transaction.

9. Differentiate Super key, Candidate key andPrimary key.

10. Differentiate subset and proper subset.

11. What is Degree of a Table?

12. How do you define Relationship Type?

13. Define DDL, DML, and TCL.

14. Write down the role of DML Compiler.

15. What do you mean by Cardinality Ratio?

16. How would you define the Cardinality of an Attribute (Column)?

17. How do you map a Strong entity set to relation schema?

18. How do you map a Weak entity set to relationschema?

19. Why do we have Weak Entity sets?

20. Why does the conversion of Weak Entity set into Strong Entity set cause redundancy?

21. What would cause a database to have more relation schemas compared to its entity sets in ER diagram?

22. Why Referential Integrity is important?

23. What are the various attribute types?

24. Define Total participation.

25. When do we need a Descriptive Attribute?

26. What is termed as Discriminator in ER model?

27. How do we represent Attribute Inheritance in an ER diagram?

28. How do you define the Cardinality of a relationship set?

29. How a composite attribute can be converted into relation schema?

30. What is Query?

31. How do we use Data Dictionary?

32. List down the different types of Database users.

33. Why DML queries are compiled and DDL queries are interpreted?

34. What is the role of Concurrency control in database transactions?

35. Why Normalization process is considered important in most database designs?

36. What is Normalization?

37. Define Functional Dependency?

38. When do we say a set of functional dependencies F is Minimal?

39. Describe the Closure (F+) of set offunctional dependencies (F).

40. Describe the Canonical Cover (Fc) ofset of functional dependencies (F).

41. Is it useful to know all the candidate keys of a relation? Justify your reasons.

42. What is Prime attribute?

43. What is non-prime attribute?

44. What is Multi-valued dependency?

45. List the properties of 1NF relation.

46. List the properties of 2NF relation.

47. List the properties of 3NF relation.

48. List the properties of BCNF relation.

49. Describe Armstrong’s axioms.

50. How would Armstrong’s axioms useful in database design?

51. Define Lossless Join Decomposition.

52. List the important facts about serializable schedules

53. What are the important properties of 2PL and its variants?

54. Explain strict two phase locking with examle

55. Explain simple two phase locking with example

56. What are the properties of functional dependencies that are part of a minimal cover?

57.