Relational Database Operators

Relational Set Operators uses relational algebra to manipulate contents in a database. All together there are eight different types of operators. These operators are SQL commands.
SELECT is the command to show all rows in a table. It can be used to select only specific data from the table that meets certain criteria. This command is also referred to as the Restrict command.

UNION. It combines all of the rows in one table with all of the rows in another table except for the duplicate tuples. The tables are required to have the same attribute characteristics for the Union command to work. The tables must be union-compatible which means that two tables being used have the same amount of columns and the columns have the same names, and also need to share the same domain.

INTERSECT is the second SQL command that takes two tables and combines only the rows that appear in both tables. The tables must be union-compatible to be able to use the Intersect command or else it won't work.

DIFFERENCE in another SQL command that gets all rows in one table that are not found in the other table. Basically it subracts one table from the other table to leave only the attributes that are not the same in both tables. For this command to work both tables must be union-compatible.

PRODUCT command would show all possible pairs of rows from both tables being used. This command can also be referred to as the Cartesian Product.

PROJECT is the command that gives all values for certian attributes specified after the command. It shows a vertical view of the given table.

JOIN takes two or more tables and combines them into one table. This can be used in combination with other commands to get specific information. There are several types of the Join command. The Natural Join, Equijion, Theta Join, Left Outer Join and Right Outer Join

Read More

Codd's Rule

Codd's Rule for Relational DBMS

E.F Codd was a Computer Scientist who invented the Relational model for Database management. Based on relational model, the Relational database was created. Codd proposed 13 rules popularly known as Codd's 12 rules to test DBMS's concept against his relational model. Codd's rule actualy define what quality a DBMS requires in order to become a Relational Database Management System(RDBMS). Till now, there is hardly any commercial product that follows all the 13 Codd's rules. Even Oracle follows only eight and half(8.5) out of 13. The Codd's 12 rules are as follows.

---

Rule zero

This rule states that for a system to qualify as an RDBMS, it must be able to manage database entirely through the relational capabilities.

---

Rule 1: Information rule

All information(including metadata) is to be represented as stored data in cells of tables. The rows and columns have to be strictly unordered.

---

Rule 2: Guaranted Access

Each unique piece of data(atomic value) should be accesible by : Table Name + Primary Key(Row) + Attribute(column).
NOTE: Ability to directly access via POINTER is a violation of this rule.

---

Rule 3: Systematic treatment of NULL

Null has several meanings, it can mean missing data, not applicable or no value. It should be handled consistently. Also, Primary key must not be null, ever. Expression on NULL must give null.

Rule 4: Active Online Catalog

Database dictionary(catalog) is the structure description of the complete Database and it must be stored online. The Catalog must be governed by same rules as rest of the database. The same query language should be used on catalog as used to query database.

Rule 5: Powerful and Well-Structured Language

One well structured language must be there to provide all manners of access to the data stored in the database. Example: SQL, etc. If the database allows access to the data without the use of this language, then that is a violation.

---

Rule 6: View Updation Rule

All the view that are theoretically updatable should be updatable by the system as well.

---

Rule 7: Relational Level Operation

There must be Insert, Delete, Update operations at each level of relations. Set operation like Union, Intersection and minus should also be supported.

---

Rule 8: Physical Data Independence

The physical storage of data should not matter to the system. If say, some file supporting table is renamed or moved from one disk to another, it should not effect the application.

---

Rule 9: Logical Data Independence

If there is change in the logical structure(table structures) of the database the user view of data should not change. Say, if a table is split into two tables, a new view should give result as the join of the two tables. This rule is most difficult to satisfy.

---

Rule 10: Integrity Independence

The database should be able to enforce its own integrity rather than using other programs. Key and Check constraints, trigger etc, should be stored in Data Dictionary. This also make RDBMS independent of front-end.

---

Rule 11: Distribution Independence

A database should work properly regardless of its distribution across a network. Even if a database is geographically distributed, with data stored in pieces, the end user should get an impression that it is stored at the same place. This lays the foundation of distributed database.

---

Rule 12: Nonsubversion Rule

If low level access is allowed to a system it should not be able to subvert or bypass integrity rules to change the data. This can be achieved by some sort of looking or encryption

Read More

Generalization and Specilization in ER Model

The Enhanced ER Model

As the complexity of data increased in the late 1980s, it became more and more difficult to use the traditional ER Model for database modelling. Hence some improvements or enhancements were made to the existing ER Model to make it able to handle the complex applications better.
Hence, as part of the Enhanced ER Model, along with other improvements, three new concepts were added to the existing ER Model, they were:

1. Generalization
2. Specialization
3. Aggregration

Generalization

Generalization is a bottom-up approach in which two lower level entities combine to form a higher level entity. In generalization, the higher level entity can also combine with other lower level entities to make further higher level entity.
It's more like Superclass and Subclass system, but the only difference is the approach, which is bottom-up. Hence, entities are combined to form a more generalised entity, in other words, sub-classes are combined to form a super-class.
For example, Saving and Current account types entities can be generalised and an entity with name Account can be created, which covers both.

Specialization

Specialization is opposite to Generalization. It is a top-down approach in which one higher level entity can be broken down into two lower level entity. In specialization, a higher level entity may not have any lower-level entity sets, it's possible.

Aggregration

Aggregration is a process when relation between two entities is treated as a single entity.

In the diagram above, the relationship between Center and Course together, is acting as an Entity, which is in relationship with another entity Visitor. Now in real world, if a Visitor or a Student visits a Coaching Center, he/she will never enquire about the center only or just about the course, rather he/she will ask enquire about both.

Read More

ER Diagram MODEL

Working with ER Diagrams

ER Diagram is a visual representation of data that describes how data is related to each other. In ER Model, we disintegrate data into entities, attributes and setup relationships between entities, all this can be represented visually using the ER diagram.
For example, in the below diagram, anyone can see and understand what the diagram wants to convey: Developer develops a website, whereas a Visitor visits a website.

Read More

ER MODEL BASIC LEVEL

 DBMS

ER Model

Entity-relationship model is a model used for design and representation of relationships between data.

The main data objects are termed as Entities, with their details defined as attributes, some of these attributes are important and are used to identity the entity, and different entities are related using relationships.
In short, to understand about the ER Model, we must understand about:

• Entity and Entity Set
• What are Attributes? And Types of Attributes.
• Keys
• Relationships

ER Model: Entity and Entity Set

Considering the above example, Student is an entity, Teacher is an entity, similarly, Class, Subject etc are also entities.
An Entity is generally a real-world object which has characteristics and holds relationships in a DBMS.
If a Student is an Entity, then the complete dataset of all the students will be the Entity Set

ER Model: Attributes

If a Student is an Entity, then student's roll no., student's name, student's age, student's gender etc will be its attributes.
An attribute can be of many types, here are different types of attributes defined in ER database model:

1. Simple attribute: The attributes with values that are atomic and cannot be broken down further are simple attributes. For example, student's age.
2. Composite attribute: A composite attribute is made up of more than one simple attribute. For example, student's address will contain, house no., street name, pincode etc.
3. Derived attribute: These are the attributes which are not present in the whole database management system, but are derived using other attributes. For example, average age of students in a class.
4. Single-valued attribute: As the name suggests, they have a single value.
5. Multi-valued attribute: And, they can have multiple values.

ER Model: Keys

If the attribute roll no. can uniquely identify a student entity, amongst all the students, then the attribute roll no. will be said to be a key.
Following are the types of Keys:

1. Super Key
2. Candidate Key
3. Primary Key

ER Model: Relationships

When an Entity is related to another Entity, they are said to have a relationship. For example, A Class Entity is related to Student entity, becasue students study in classes, hence this is a relationship.
Depending upon the number of entities involved, a degree is assigned to relationships.
For example, if 2 entities are involved, it is said to be Binary relationship, if 3 entities are involved, it is said to be Ternary relationship, and so on.

Read More