Learn about dbms models ER model and relationships

Traditional Data models

 
There are three traditional data models
 
  1. Relational model
  2. Hierarchical model
  3. Network model
 

Relational model

In relational model, data is organized in the form of rows and columns in the table.
In the relational model we use there terms for the following.
 
  • Table - Relations
  • Records/Rows - Tuple
  • Column - Attributes
 

Advantages of relational models

 
can be used efficiently.
  1. Require a computer with limited memory and capability.
  2. usefull for the small databaes.
  3. it is easy to use and learn.
  4. it is only concerned about data not the structure.
 

Keys in relational models.

 

Primary key

A field is unique identifier for a table that is useful to identify the record uniquely.
 

Composite key

Group of fields that are useful to identify the record uniquely like user_id and email.
 
 

Operations on relational model.

  1. Fetch/Retrieve the records
  2. Insert record
  3. Delete record
  4. Update record
 

Attribute of an entity

Every table or relation has three components.
 
  1. Name
  2. Degree
  3. Cardinality
  4. Domain
 

Name

Name of attributes.
 

Degree

Number of columns associated with the relation or table is called the degree.
 

Cardinality

Number of rows in the table is called cardinality.
 

Domain

Possible values for the attribute is called the domain.
 

Entity

Real word person or an object that has properties and behaviour is called the entity.
In the database every record is referred as entity.
 

Entity sets

Collection of all same type records or entity is called entity sets.
 

Properties of relational model

  1. Represents data in the form of tables
  2. Not require for the user to has understanding of physical implementation.
  3. Provide information about its content and structure.
  4. Support concepts of NULL values.
 

The 12 Rules for and RDBMS (CODD'S 12 Rules)

 
A system is called truely RDBMS when it follow atleast 6 rules from the 12 rules given by the E.F CODD for RDBMS in 1970.
 

Information representation

Data stored in the tabular form.
 

Guaranteed access

There must be data or null for the column in a relation.
 

Systematic treatment of NULL values

In database system null values should be supported.
 

Database description rule

The description of database is stored and maintained in the form of tables.
 

Comprehensive data sub language.

The RDBMS must be manage by the its own SQL extension and SQL must support the data defintion, views, data manipulation, integrity constraints.
 

View updating

System must support updation of views.
 

Support insertion, deletion and updation

System must support insertion, deletion and updation of records.
 

Physical data independence.

Changes made to physical storage representation or access methods do not require change to be made to the application programs.
 

Logical data independence

Application programs should not affected by the changes made to the base tables.
 

Distribution rule

System must be accessible from the different computers.
 

Non subversion

Prevent bypassing entity-integrity or referential-integrity constraints that may be defined for the database.
 

Integrity rule

Must apply the integrity rules in the system.
 
 

Concept's of keys in the DBMS.

Super key

Collection of columns that specify uniqueness of record in the table
 

Primary key

A column that identify the record uniquely is called the primary key.
it is also called minimal super key.
 

Candidate key

Collection of columns that specify uniqueness of record in the table.
 

Composite key/Concatenated key.

A composite key is a group of fields that uniquely identify the reocrd. when we use more than one column as part of primary key to identify record uniquely is called composite key
 

Secondary key

In a relation, secondary key is called which are not composite key.
 

Foreign key.

A column in a relation pointing to the primary key in other table is called the foreign key.
 
 
 

Integrity rules

Integrity rules check the some condition for what kind of data storing in the database.
 

Entity integrity(Integrity rule 1)

  1. Make sure every table is using primary key and should be unique.
  2. primary key can not be null.
 

Referential Integrity (Integrity rule 2)

 
Referential Integrity rules is concerned about foreign key, in RDBMS mostly tables has relation with other table and other table also containing primary key of first table, on second table that column is called foreign key.
 
 

Entity Relationship Data Model(E-R Model)

This model is used to define data elements and relationship between different relations and system.
 
E-R model uses following componenents.

Rectangle 

used to represent entity sets.
 

Oval

used to Representing attributes
 

Diamond

Represent relationships among entity sets.
 

Line

Linkes attributes to entity sets and entity sets to relationships.
 
 

Entity 

Any object, person, event, system is called entity.
 

Entity sets

Collection of entitye is called entity sets.
 

Relationships

Relation between two or more entity is called the relationship.
 
There are three basic relationships exist among entities.

One-to-One

Where one entity belong to single entity like one person has one contact.
 

One-to-Many

Where one entitye belong to the multiple entity like one person can add more than one post.
 

Many-to-Many

Where multiple entity belongs to the multiple entity like a person can order multiple pizza and pizza can order by the multiple persons.
 

Strong entity

When an entity/table is not dependent on other entity/table is called strong entity.
like a table for organization information.
 

Weak entity

When an entity dependent on other entity/table is called weak entity.
Weak entity existance depend upon existance of other entity.
Note: weak entity represented by the double line rectangle in the E-R diagram.
 

Hierarchical models

In the hierarchical models, records are stored in the form of tree where every new record create node and fields created branches.
 
it is developed by the IBM in 1968, and introduced in IMS(Information management system).
 

Disadvantage of hierarchical models

  1. Can not represet all the relationship that occur in the real world.
  2. CRUD implementation of data become very difficult.
 

Operation on hierarchical models

  1. Record retrieval.
 

Network model

it was the improvement over the hierarchical models.
 

Disadvantage of network data model

  1. All the records are recorded using pointers that makes database system more complex.
  2. CRUD operation on data require pointer adjustment.
© 2016 - 2022, All Rights are Reserved.