What is basic SELECT statement?

Image
SELECT Statement
A SELECT statement retrieves data from the database. With SELECT statement PROJECTION, SELECTION and JOIN can be performed on database tables.
As data in relational database is stored in TABLES. TABLE has ROW and COLUMN structure. With SELECT statement all rows and all columns can be retrieved, this is known as PROJECTION. But, if rows and columns are retrieved on the basis of some condition, it is known as SELECTION. And suppose if some information is needed and that is stored in two or more different tables but are related, use JOINS to retrieve it from two or more tables.
In SQL, basic SELECT statements are as follows:
SELECT * FROM [table_name];
SELECT[column1][column2][column3],..... FROM  [table_name];
SELECT and FROM are known as clauses. 
SELECT clause allows to specify columns to be selected from the database table. 
FROM clause allows to specify table name that has those column to be selected.
Semicolon at the end of SELECT statement specify the end of SQL statemen…

What is E-R model? What are the various symbols used to draw E-R diagram?

E-R Model (Entity-Relationship Model)

E-R model is a high-level data model. It is used in conceptual design phase of a database designing process for defining a conceptual schema. E-R model comprises of entities, relationships and attributes.

E-R Diagram: A graphical or diagrammatic presentation of all the concepts of E-R model. 

Entity: A real world object or thing is known as entity. For example EMPLOYEE, STUDENT, TEACHER, SUBJECT, COMPANY, PROJECT etc. are entities in real world. It is designated as small letters  for EMPLOYEE,  for STUDENT etc..

Attribute: A property that describes an entity in a real world is known as attribute. For example an EMPLOYEE entity have attributes like Employee Number, First Name, Last Name, Date of Birth, Address, Gender, Mobile Number etc. An entity have particular values for each attribute. 

Entity Type: A set of entities having same attributes. It describes the schema or intension of entities that share the same structure. It is designated as capital letters E for EMPLOYEE etc.

Entity Set: A set of same entity type that share the same attributes. It is also known as extension of the entity type. It is also designated as capital letters for EMPLOYEE etc.

In E-R modeling, both entity type and entity set are designated with same name E.

An entity type in E-R diagram is represented by rectangle. Attributes are enclosed in ellipses and are connected to their entity type by straight lines
                                           
Types of Attributes
Simple Attribute: Attribute that are not divisible into sub parts. e.g. First Name
Composite Attribute: Attribute that are divisible into sub parts. e.g. Address can be divisible into House Number, Colony, Sector, City, State, Country and Postal Code

Single Valued Attribute: Attribute that has a single value. e.g. Gender, either Male (M) or Female (F).

Multi Valued Attribute: Attribute that has multiple values. e.g. Color of mobile phone, Certification of an employee etc.
Derived Attribute: An attribute that is derived form any other attribute. In some cases two or more attributes are related. e.g. Age attribute of an an entity type Employee can be calculated using the Date of Birth attribute. So, Age is a derived attribute and Date of Birth is a stored attribute.
NULL Value: If an entity does not have a value for any attribute then it takes NULL as a value. NULL means the value does not exist or unknown. e.g an Employee may have no middle name.

Key Attribute: It defines the uniqueness constraint on attributes. One or more attributes with distinct values for individual entities are known as key attributes. It is used to identify each entity uniquely. It is a constraint that do not allow any two entities to have same value for the key attribute at the same time. For example, Employees have unique employee numbers in an organization, students have unique roll numbers in an examination, each person has unique UID or Aadhar number and PAN number etc. 

In E-R diagram key attributes are denoted by underline in an ellipses.
Sometimes an entity can be identified uniquely by more than one attribute. Then the combination of attributes uniquely identify the entity. These are known as composite key attribute. For example, if an employee has two addresses then ADDRESS is identified by by the combination of EMP_NO and ADD_NO.
Relationships
In E-R model, entities are related to each other. It is also known as association between entity sets. For example an EMPLOYEE works in a DEPARTMENT.

Relationship Type: It is defined as associations between entity types. For example EMPLOYEE works in DEPARTMENT. So, if relationship type is designated with R, then WORKS_IN is a relationship type.

Relationship Set: It is defined as the set of associations between the entity sets. It is a set of relationship of same type For example each entity of EMPLOYEE set works in an entity of DEPARTMENT set. Relationship set is also designated with R,  then WORKS_IN is a relationship set of same type of relationships between an EMPLOYEE and DEPARTMENT. 

In E-R modeling, both relationship type and relationship set are designated with same name R

Mathematically, is a set of ri ,where ris n relationships r1, r2, … rn between n entities e1, e2, … ein an entity set E.
In E-R diagrams, relationship is designated by diamond box. It is connected to rectangular entity type boxes with straight lines.
Relationship Degree: The number of entity types that are participating in a relationship is known as a degree of a relationship type. For example, in WORKS_IN relationship type two entity types EMPLOYEE and DEPARTMENT are participating, so the degree of this relationship type is 2. 

If relationship type degree is 2, it is known as binary relationship, like WORKS_IN is a binary relationship.

If relationship type degree is 3, it is known as ternary relationshipLet’s assume one statement that employees Ram Kumar and Shyam Kumar are managing AccountingXpress project in Account department. This kind of relationship is known as ternary relationship, as three entity types EMPLOYEE, DEPARTMENT and PROJECT are participating in a MANAGE_PROJECT relationship.
Recursive Relationship: In any relationship set, entity type plays some role. For example Amesh Dubey is a manager of Inventory department. So, Employee is playing a role of a manager in Inventory department. 

Sometimes an entity type recursively associated to itself, for example Sapna Dubey is a manager of executives Sachin Pathak and Ashish Joshi. All are entities of entity type EMPLOYEE.

Constraints
Constraints enforce the limit on number of combinations between entity types that participate in a relationship set. Two basic constraints are Cardinality and Participation. Together, they are known as structural constraints.

Cardinality: Also known as cardinality ratio or mapping cardinality. Mostly used in case of binary relationships. Defines the number of entities associated with another entity in a relationship set. 

In E-R diagrams cardinality ratios represented displaying 1, M, and N on the diamond boxes.

1:1 (one-to-one): An entity in X is associated with at most one entity in Y, and an entity in Y is associated with at most one entity in X.
For example, A DEPARTMENT has only one MANAGER.
1:N (one-to many): An entity in X is associated with any number (zero or more) of entities in Y, and an entity in Y is associated with at most one entity in X.
For example, A DEPARTMENT has many EMPLOYEE.
N:1 (many-to-one): An entity in X is associated with at most one entity in Y. An entity in Y, however, can be associated with any number (zero or more) of entities in X.
For example, many EMPLOYEE have one manager.
N:M (many-to-many): An entity in X is associated with any number (zero or  more)of entities in Y, and an entity in Y is associated with any number (zero or more) of entities in X.
For example, many EMPLOYEE are working on more than one PROJECT.
Participation: Defines the involvement of entities in a relationship. It states whether the existence of an entity depends on another entity. 

Total Participation (Existence Dependency): If every entity of an entity set participates in a relationship with entities of another entity set. For example, consider the situation in an organization, if every employee should work in a department. Then EMPLOYEE is totally participating in a relationship WORKS_IN with DEPARTMENT.

Partial Participation: If few entity of an entity set participates in a relationship with entities of another entity set. For example, not every employee manages a department, but all department should have a manager. That means few employees are managers (role) and are managing a particular department. So, EMPLOYEE is partially participating in a relationship MANAGES with DEPARTMENT, and DEPARTMENT is totally participating in a MANAGES relationship with EMPLOYEE.
Weak Entity Type
Entity type without any key attribute is known as weak entity type (or an entity without any key attribute is known as weak entity).


Entity type with key attribute is known as strong entity type (or an entity with key attribute is known as strong entity)

Weak entity type is dependent on the entity type with which it is making any relationship. This associated entity type with weak entity type helps to uniquely identify the weak entity type and known as identifying entity type or owner entity type. Relationship between these two is known as identifying relationship. So, a weak entity type always has a total participation in a relationship with owner entity type. Weak entity type can not be identified without owner entity type.

In E-R diagram weak entity type is designated with double lined rectangle boxes. A relationship between weak entity type and owner entity type is designated with double lined diamond box.

For example, FAMILY members are dependent on EMPLOYEE. FAMILY is a weak entity in an organization and can be identified uniquely with EMPLOYEE.
Weak entity type have partial key for unique identification. It is an attribute or a set of attribute that allows the distinction between entities of weak entity type. It is also known as discriminator.

In E-R diagram, discriminator is designated with dashed underline of attribute in ellipses.













Comments

Popular posts from this blog

With the help of block diagram explain the architecture of a DBMS.

What do you mean by data model? What are different types of data models?

What are the different types of database users?