Codd's Rule

Codd's Rule

E.F Codd was a Computer Scientist who invented Relational model for Database management. Based on relational model, Relation 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 out(8.5) 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 : Systemetic treatment of NULL

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

---

Rule 4 : Active Online Catalog

Database dictionary(catalog) must have description of Database. Catalog to be governed by same rule as rest of the database. The same query language to be used on catalog as on application database.

---

Rule 5 : Powerful language

One well defined language must be there to provide all manners of access to data. Example: SQL. If a file supporting table can be accessed by any manner except SQL interface, then its a violation to this rule.

---

Rule 6 : View Updation rule

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

---

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 were 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 conforce 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. This lays 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 rule to change data. This can be achieved by some sort of looking or encryption.

Components of ER Diagram

ER-Diagram is a visual representation of data that describes how data is related to each other.

---

Symbols and Notations

---

Components of E-R Diagram

The E-R diagram has three main components.

1) Entity

An Entity can be any object, place, person or class. In E-R Diagram, an entity is represented using rectangles. Consider an example of an Organisation. Employee, Manager, Department, Product and many more can be taken as entities from an Organisation.

                                      

---

Weak Entity

Weak entity is an entity that depends on another entity. Weak entity doen't have key attribute of their own. Double rectangle represents weak entity.

---

2) Attribute

An Attribute describes a property or characterstic of an entity. For example, Name, Age, Address etc can be attributes of a Student. An attribute is represented using eclipse.

---

Key Attribute

Key attribute represents the main characterstic of an Entity. It is used to represent Primary key. Ellipse with underlying lines represent Key Attribute.

---

Composite Attribute

An attribute can also have their own attributes. These attributes are known as Composite attribute.

---

3) Relationship

A Relationship describes relations between entities. Relationship is represented using diamonds.

There are three types of relationship that exist between Entities.

• Binary Relationship
• Recursive Relationship
• Ternary Relationship

---

Binary Relationship

Binary Relationship means relation between two Entities. This is further divided into three types.

1. One to One : This type of relationship is rarely seen in real world.
   
   The above example describes that one student can enroll only for one course and a course will also have only one Student. This is not what you will usually see in relationship.
2. One to Many : It reflects business rule that one entity is associated with many number of same entity. The example for this relation might sound a little weird, but this menas that one student can enroll to many courses, but one course will have one Student.
   
   The arrows in the diagram describes that one student can enroll for only one course.
3. Many to One : It reflects business rule that many entities can be associated with just one entity. For example, Student enrolls for only one Course but a Course can have many Students.
   
4. Many to Many :
   
   The above diagram represents that many students can enroll for more than one courses.

---

Recursive Relationship

When an Entity is related with itself it is known as Recursive Relationship.

---

Ternary Relationship

Relationship of degree three is called Ternary relationship.

Data Models in DBMS

Data Model in DBMS

A model is an abstraction process that represent essential features without including the background details or explanations. It hides superfluous details while highlighting details pertinent to the application at hand.
A data model is a mechanism that provides this abstraction for database applications. Data modelling is used for representing entities of  interest and their relationships in the data base.
A data model defines the logical structure of a data base means that how data is connected to each other and how they are processed and stored inside a system.

https://en.wikipedia.org/wiki/Data_model#/media/File:Data_modeling_context.svg

A number of models for representing data have been developed. As with programming languages, there is no best choice for all applications but the models maintains the integrity of the by enforcing a set of constraints.
Data models differ in their method of representing the associations amongst entities and attributes. The main models or approach are:

• The Hierarchical Model – Tree Structure
• The Network Model – Plex Structure
• The Relational Model – Normalised Structure
• The ER Model -Conceptual Model

Click on these models to know more .

Data Model Structure and Constraints –

• To define the database structure, Constructs are used
• Constructs typically include elements (and their data types) as well as group of elements (Example- Entity, Record, Table), and relationships among such groups.
• Constraints specify some restriction on valid data; These constraints must be enforced at all times.

Data Model Operations –

These operations are used for specifying database retrievals and updates by referring to constructs of the data model. The Operations may include basic model operations as well as user defined operations.

Basic Model Operations :

• Insert
• Delete
• Update

User Defined Operations Example :

• compute_student_CGPA
• compute_student_marks
• compute_student_SGPA
• update_inventory etc.

The hierarchical model is the oldest DBMS model and the object oriented DBMS being the newest data model.

Entity and Its Types

Entity and its Types – Strong Entity, Weak Entity, Composite Entity

Entity – 

An entity is an object that are represented in the database. For example Mohit, Vasu, CSE306 etc.

An entity is represented or defined by set of attributes. Attributes are the properties used to describe an entity. For example, a STUDENT entity may have a Name, Roll number, Class, Marks etc. where STUDENT is the entity and name roll number class marks are the attributes.

Types of Entity – 

1. Strong Entity Types
2. Recursive Entity Types
3. Weak Entity Types
4. Composite Entity Types or Associative Entity Types
5. SuperType and SubType Entities

Notations Of different Entity Type in ER Diagram 

	Entity
	Strong Entity Type
	Weak Entity Type
	Recursive Entity Type
	Composite Entity Type (or) Associative Entity
	Subtypes and Supertypes

Strong Entity Type – are the entities which has a key attribute in its attribute list or a set that has a primary key. The strong entity type is also called regular entity type. For Example,

The Student’s unique RollNo will identify the students. So, RollNo is set to be the Primary Key of the STUDENT entity, & Hence STUDENT is a strong entity type because of its key attribute.

Recursive Entity Type – It is also called Self Referential Relationship Entity Type. It is an entity type with foreign key referencing to same table or itself. Recursive Entity Type occurs in a unary relationship.

For example, a supervisor and subordinate relationship sets – One Supervisor can supervise multiple subordinates but each subordinate reporting to atmost one supervisor.

In example b) An employee supervises another employee. Let the employee who supervises another one is manager. So, Manager supervises employees. But a manager is also an employee, whose details are in the employee entity. So, to implement this, a foreign key of the employee’s manager number would be held in each employee record i.e “supssn”.
So, the employee entity will contain its attributes as –

• Employee ID (subssn)
• Employee FirstName
• Employee Lastname
• Employee DateofBirth
• and Manager Number (i.e. employee number of the employee’s manager) – (supssn)

The next figure will clear you the employee entity as a recursive entity –

If M:M ==> Reports_To(supssn,subssn)
then 2 tables are made.

Weak Entity Type –

Entity Type with no key or Primary Key are called weak entity Type.
The Tuples of weak entity type may not be possible to differentiate using one attribute of weak entity.For every weak entity, there should be unique OWNER entity type. In the below example, CHILD is a WEAK entity type and Employee is the OWNER entity type.

Example of weak entity type is –

Children Entity is depending upon Strong Entity Employee(as it has a unique ID named SSN). The relationship is established to associate children with their parents for insurance coverage.
Attributes of CHILDREN entity are-

• CNAME (name of Child)
• Age of Child
• Type of Insurance

So, None of the attributes of CHILDREN does not give a unique ID to the entity. And the CHILDREN Entity has to depend on EMPLOYEE entity for identification.

The next table will clear you the child entity as a weak entity and so is represented with employee id(SSN) –

SSN	CNAME	AGE	GENDER
E1	A	8	M
E2	A	10	M
E1	B	10	F
E3	B	10	F

CHILD_BELONGS_TO(SSN,CNAME,AGE,GENDER)

Maximum number of tables = 2
Minimum number of tables = 2

Some Points about weak entity Type –

1) Weak entity Type should always combines with weak relationship set for 
   database schema relation.
 
2) Relationship between OWNER ENTITY Type and Weak Entity Type is also 
   partial. 

3) Participation between weak entity Type and relationship is always TOTAL

4) Cardinality can be Many to Many or One to Many.

Composite Entities –
If a Many to Many relationship exist then we must eliminate it by using composite entities. Composite Entities are the entities which exists as a relationship as well as an entity. The many to many relationship will be converted to 1 to many relationship.
Composite Entities are also called Bridge Entities, because they acts like a bridge between the two entities which have many to many relationship.
Bridge or Composite entity composed of the primary keys of each of the entities to be connected.
A composite entity is represented by a diamond shape with in a rectangle in an ER Diagram.

Supertypes and Subtypes Entities –

A subtype is a subprouping of the entities in an entity type that is meaningful to the organisation. For example, In a University, a STUDENT is an entity type. Two subtypes of STUDENT entity are

• GRADUATE STUDENT
• UNDERGRADUATE STUDENT

A supertype is a generic entity type that has a relationship with one or more sub-types. In the above example, STUDENT entity is a supertype.

Hierarchical, Network and Relational Database Model

Hierarchical Model in DBMS

Hierarchical model is a data model which uses the tree as its basic structure. So, lets define the basics of the tree.

Basics of Tree :

• A tree is a data structure that consists of hierarchy of nodes with a single node, called the root at highest level.
• A node may have any number of children, but each child node may have only one parent node on which it is dependent. Thus the parent to child relationship in a tree is one to many relationship whereas child to parent relationship in a tree is one to one.

Figure 1

• In figure 1, the node at level 1 is called the root node and the nodes at that has no children are called leaves. For example, node 4, 5, 7, 8, 9, 10 and 11.
• Nodes that are children of the same parent are called siblings. For example, nodes 2, 3, 4 are siblings.
• For any node there is a single path called the hierarchical path from the root node. The nodes along this path are called that nodes ancestors.
• Similarly for a given node, any node along a path from that node to leaf is called its descendent.
• For example, suppose we have to find out the hierarchical path of node 10, then it will be 1→2→6→10 and the ancestors of node 10 are 1, 2 and 6.
• The height of tree is the number of levels on the longest hierarchical path from the root to a leaf. The above tree has a height= 4.
• A tree is said to be balanced if every path from the root node to a leaf has the same length.

Figure 2 shows a balanced and an unbalanced tree.

Figure 2

A binary tree is one in which each node has not more than two children.
Figure 3 shows a binary tree

Figure 3

Example of Hierarchical Model :

• Figure 4 shows a data structure diagram for a tree representing the STUDENT, FACULTY and CLASS.
• The root node chosen is faculty, CLASS as a child of faculty and STUDENT as a child of class.
• The cardinality between CLASS and FACULTY is one to many cardinality as a FACULTY teaches one or more CLASS.
• The cardinality between a CLASS and a STUDENT is also one to many cardinality because a CLASS has many STUDENTS.

Figure 4

Figure 5 shows an occurrence of the FACULTY-CLASS-STUDENT.

Figure 5

Another Example :

https://en.wikipedia.org/wiki/Hierarchical_database_model#/media/File:Hierarchical_Model.svg

Operations on Hierarchical Model

1. Deletion- If CS02 is deleted, then all the students in CS02 class will be deleted. So deletion is very difficult. However deletion of leaf nodes that is students does not create difficulty in deletion.
2. Insertion- A new class say, CS03 may not be introduced unless some faculty is available at root level. So insertion is also difficult.
3. Updation- Suppose a student has changed his subject from Hindi to Sanskrit, then firstly a search is performed to find out Hindi subject and then an update is made. A search is a time consuming process here.

So  these problem occurs in all the three operations.

Advantages of Hierarchical Model 

• Easy to understand
• Performance is better than relational data model

Disadvantages of Hierarchical Model 

• Difficult to access values at lower level
• This model may not be flexible to accomodate the dynamic needs of an organisation
• Deletion of parent node result in deletion of child node forcefully
• Extra space is required for the storage of pointers

Network Database or Network Model in DBMS

The network database or network model uses the plex structure as its basic data structure. A network is a directed graph consisting of nodes connected by links or directed arcs. The nodes corresponds to record types and the links to pointers or relationships. All the relationship are hardwired or pre-computed and build into structure of database itself because they are very efficient in space utilization and query execution time.
The network data structure looks like a tree structure except that a dependent node which is  called a child or member, may have more than one parent or owner node.All figure shows the network model –

Figure 1

A diagram called as Bachman Diagram is used to represent a network data structure. The nodes in the network are replaced by rectangles that represent records and links are shown by lines connecting the rectangles.

A plex structure with two record types is shown / Example of Network Database :

Figure 2

Another Example of Network Database is :

https://en.wikipedia.org/wiki/Network_model#/media/File:Network_Model.svg

Operations on Network Model/Network Database

1. Insertion- In the above figure, it is clear that a new part or supplier can easily be inserted.
2. Deletion- For deletion only link is to be removed and no information will be lost. For example, to remove  PART 2, we delete  the connector line between suppliers.
3. Updation- Updation is also easy, for example, suppose SUPPLIER B supplies PART 1 in place of SUPPLIER 2, so, updation will be successfully done by changing the link of SUPPLIER B from PART 2 to PART 1.

Advantages of Network Model/ Network Database :

• Easy access to data.
• Flexible
• Efficient
• This model can be applied to real world problems, that require routine transactions.

Disadvantages of Network Model/ Network Database :

• Complex to design and develop.
• Extra memory is required for storage of pointers
• Performance is infexible and difficult to use.
• Operation and maintenance are time consuming and expensive for large databases.

Relational Model or Relational Database 

The relational model is a lower level model. It is based on the concept of a relation, which is physically represented as a table. A table is a collection of rows & columns . The relational model uses a collection of tables to represent both data and the relationships among those data.
The tables are used to hold information about the objects to be represented in the database. A relation or a table is represented as a two dimensional form in which the rows of the table corresponds to individual records and the columns corresponds to attributes.
Each row is called a tuple and each column is called an attribute.For example, a student relation is represented by the STUDENT table having columns for attributes SID, NAME and BRANCH.

SID : Key
 Number of Records = Cardinality
 Number of Fields = Arity
 Student (SID,Name,Branch) = Relational Schema (Table Abstraction)

The SID here is the primary key as it identifies a student record or tuple uniquely.(A primary key is the key applied on an attribute(SID) which recognize a tuple.
The Cardinality of the Relation or table is defined as the number of records in the STUDENT relation which is 4.
The Arity is defined as the number of fields or columns in the relation.

Domain of an Attribute –

Domain of an attribute is the set of allowable values for that attribute. It is a pool of values from which the actual values appearing in a given column are drawn. For example, the values appearing in the SID column are drawn from the domain of all SID. Domains may be distinct, or two or more attributes may have same domain.

Operations in Relational Model –

1. Insertion – A new student record can be easily inserted in the table.
2. Deletion – An existing student record or tuple can easily be deleted from the STUDENT relation.
3. Updation – An existing student record can be update easily. For example, if a student S2 changes its BRANCH from CS to IT, then it can easily be changed

Advantages of Relational Model –

• Easy to use an understand
• Very flexible.
• Widely used.
• Provides excellent support for adhoc queries.
• Users need not consider issues such as storage structure and access strategy.
• Specify control and authorization can be implemented more easily.
• Data independence is achieved more easily with normalisation structure used in a relational database.

Disadvantages of Relational Model –

• For large databases, the performance in responding to queries is definitely degraded.
• The processing requirements need to construct the indexes. So, the index position of the file must be created and maintained along with the file records themselves.
• The file index must be searched sequentially before the actual file records are obtained. This wastes time.