Mapping Constraints-Cardinality Ratio and Participation Constraint

Mapping Constraints are sub divided into two parts that is Cardinality Ratio and Participation Constraints

Cardinality Ratio: It express the number of entities to which another entity can be associated via a relationship.

One to One: An entity of entity-set A can be associated with at most one entity of entity-set B and an entity in entity-set B can be associated with at most one entity of entity-set A.

One to Many: An entity of entity-set A can be associated with any number of entities of entity-set B and an entity in entity-set B can be associated with at most one entity of entity-set A.

Many to One: An entity of entity-set A can be associated with at most one entity of entity-set B and an entity in entity-set B can be associated with any number of entities of entity-set A.

Many to Many: An entity of entity-set A can be associated with any number of entities of entity-set B and an entity in entity-set B can be associated with any number of entities of entity-set A.



Participation Constraints:-





Total Participation-Shown with double lines.In this figure,employ is having total participation in department.


Partial Participation-Shown with single line.In this figure,department is having partial participation with employ.


NOTE-Weak entity always have total participation with the other entity as it is not having primary key which uniquely identifies its tuple.

Keys - Primary,Candidate,Foreign,Composite,Super,Alternate

Different Type of Keys -

1) Primary Key - A primary key is a column in a table that uniquely identifies the rows/tuple/record in that table with this property it cannot have null value.

In other words ,we can say that primary key is a candidate key that is most appropriate to become main key of the table.

2) Candidate Key - Candidate keys are defined as the set of fields from which primary key can be selected.

In otherwords we can say ,candidate key is known as nominee's for primary key field.

3) Super Key - Super Key is a superset of Candidate key.

4) Alternate Key - The candidate key which are not selected for primary key are known as alternative keys.

Alternate keys are known as Secondary keys.

5) Composite Key - If a table is not having a primary key then we use the composite keys which comes with the combination of two columns which uniquely identify the record or tuple.

In otherwords we can say,key that consists of two or more attributes that uniquely identify an entity occurance is called Composite key.

6) Foreign Key - A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

E-R Diagram

E-R Diagram -
It is combination of entities and relationship among those entities in a pictorial form for database.It is used at the time of database design,which makes easier to developer for understanding the database.

Entity can be defined as a thing or an object in a real word.

In otherwords,we can say entity is a set of attributes.

It is represented as rectangle.
Entity Set can be defined as collection of entites or a set of entites for a particular database.

Relationship can be defined as a relation between two or more entities.

In otherwords ,we can say that association between entities is known as relationship.

It is represented as diamond .
Relationship Set can be defined as collection of relationship or a set of relationship for particular database.

Common Notations Used in E-R Diagram are as follows -

Simple E-R Diagram for Hospital Management System can be showed as follows-

Data Models in DBMS

Data Model

A Database model defines the logical design of data.Data models are fundamentals entities to define how data is connected to each other and how data is processed and stored inside the system.

Data Models can be categorized as follows:-
1)Hierarchical Model
2)Network Model
3)Relation Model
4)E-R Model
5)Object-Based Model
6)Semi-structured/Hybrid Model

Hierarchical Model - In this model,the data is organized in the form of tree like structure.
In other words ,we can say that it provides parent-child relationship where child is inherited with some of the properties of parent.Parent child relationship is one to many relationship.

Advantage-
1)Simplicity
2)Data security&Data integrity

Disadvantage-
1)Implementation complexity
2)Programming complexity

Network Data Model - The data is organized in the form of record which are linked to each other.
The advantage of this model is that it has less duplication as compare to hierarchical model but it is more complex to hierarchical model.
It permitted the modelling of many to many relationship in data. 


Relational Model - The data is organized in the form of tables.Each table have a set of attributes.

Properties of relational table:-
1)Values are atomic.
2)Each row is unique.
3)Column values are of the same kind.
4)Each column has a unique name.


E-R Model - It comprises of entity and relationship,so it is known as entity -relationship model.

Entity can be defined as a thing or an object in a real word.In otherwords,we can say entity is a set of attributes.It is represented as rectangle.

Relationship can be defined as a relation between two or more entities.It is represented as diamond .


Here ,
Entities are Student and Class.
where as
Relationship between them is Enroll(Enrollment).
Entities are represented with set of certain attributes.

Object Based Data Model - Object database management systems (ODBMS) add new object storage capabilities to the relational systems at the core of modern information systems. Thee new facilities such as integrate management of traditional fielded data, complex objects such as time-series and geospatial data and diverse binary media such as audio, video, images, and applets. By encapsulating methods with data structures, an ODBMS server can execute compile x analytical and data manipulation operations to search and transform multimedia and other complex objects.

Semi-Structured Data Model - In semistructured data model, the information that is normally associated with a schema is contained within the data, which is sometimes called ``self-describing''. In such database there is no clear separation between the data and the schema, and the degree to which it is structured depends on the application. Semistructured data has recently emerged as an important topic of study for a variety of reasons. First, there are data sources such as the Web, which we would like to treat as databases but which cannot be constrained by a schema. Second, it may be desirable to have an extremely flexible format for data exchange between disparate databases. Third, even when dealing with structured data, it may be helpful to view it as semistructured for the purposes of browsing.

Database Languages-DDL,DML,DCL,TCL

Database Languages

A DBMS need to provide a language for each category of user by which user can express databse queries and updates.Database language is used to create and maintain database on computer.

There are number of database languages used by different user .User can be said as follows: -

Oracle
Mysql
MS Access
dBase

SQL statements can be categorized as data definition language (DDL), data control language (DCL) and data manipulation language (DML).

DDL-

Data definition language is used to create the structure or schema for the required database.

In other words ,we can say it defines the logical schema which includes relation ,views,etc and the storage schema which includes attributes,integrity constraints,security ,authorization etc in data dictionary.

Some commands used under DDL are as follows:-

• CREATE - It is used to create objects or structure in the database.
• ALTER - It is used to alters the structure of the database.
• DROP - It is used to delete objects from the database.
• TRUNCATE - It is used to remove all records from a table, including all spaces allocated for the records are removed.
• COMMENT - It is used to add comments in the data dictionary.
• RENAME - It is used to rename an object/structure.

DML-

Data manipulation language is used to insert ,retrieve and manipulate the data in the database.

In other words ,we can say that it is used to modify the stored data in the database but cannot modify the struture/schema of database or database objects.

In other words,we can also say that it is used for managing data within the schema object.

DML commands are not auto-committed. It means changes made by DML command are temporary, it can be rollback.

Some commands used under DML are as follows:-

• SELECT - It is used to retreive the data from database.
• INSERT - It is used to insert the data in the tables of database.
• UPDATE - It is used to update the existing values in the table of database.
• DELETE - It is used to delete all records from the table,the space of record remain same.
• CALL - It is used to call a pl/sql or java program.

DCL-

Data control language is used to control access to data stored in the database.

In other words ,we can say that it is used to control the privilege in database.Privilege can be defined as the permission to perform operations on the database.

It comprises of two commands:-

• GRANT - It is used to grant the permission to user for accessing the data.
• REVOKE - It is used to take permission back from user.

TCL-

Transaction control language is used to manage the transaction in database.

In other words ,we can say that it is used to control the transaction processing in a database.

Some commands used under TCL are as follows :-

• COMMIT - It is used to save transaction permanently in the database.
• ROLLBACK - It is used to rollback all the transaction to last committed state.It also uses with a savepoint command to jump to a savepoint in a transaction.
• SAVEPOINT - It is used to save transaction temporarily so that one can rollback it to that save point if it is necessary.

Data Independence

Data Independence

The ability to modify schema definition in one level without affecting schema definition in the next higher level is called data independence.

In other words ,we can say that each higher level of the data architecture is immune to changes of its lower level of the architecture.

For example:-Metadata ,Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another level. We can say that data is mapped to each other but the is data independent.

Data Independence is classified into two categories:

1) Physical Data Independence : - Physical data independence is the ability to change the physical data without impacting the schema or logical level. 

In other words,we can that it is power to modify the physical schema without causing application programs to be rewritten.

Modification are necessary at physical level to improve the performance.

2) Logical Data Independence : - Logical data independence is the ability to modify the conceptual schema or logical level without causing application programs to be rewritten.

Modification is necessary to be done at the logical level whenever the logical structure of the database is altered.

For example: consider two users X & Y. Both are selecting the fields "EmployeeID" and "EmployeeName". If user Y adds a new column (e.g. salary) to his table, it will not effect the external view for user X, though the internal schema of the database has been changed for both users X & Y.

Data Abstraction and Levels

Data Abstraction is used to simplify the database design.Database abstraction layers reduce the complexity by providing a level of work to developer and hide the database specifics behind the interface as much as possible.

The level of work can be divided into three part as follows:-

1)Physical Level
2)Logical Level
3)View Level

Physical Level : The level of abstraction which is known as physical level describes How the data are actually stored. This level is also known as Internal or Lowest level.

Logical Level : The next-higher level of abstraction which is known as logical level describes What data are stored in the database, and What Relationships exist among those data.This level is also known as Conceptual or Middle level.

View Level : The highest level of abstraction which is known as view level describes only part of the entire database.This level is also known as External level.