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.

Database schema,Metadata and Instance

Database Schema - The description of the database is called database schema or also metadata. In other words we can say that it contains a descriptive detail of the database, which can be depicted by means of schema diagrams. The database schema is defined during the database design process.It’s the database designers who design the schema to help programmers understand the database and make it useful. Database schema changes very rarely afterwards.

A database schema can be divided broadly into two categories −

Physical Database Schema − This schema pertains to the actual storage of data and its form of storage like files, indices, etc. It defines how the data will be stored in a secondary storage.

Logical Database Schema − This schema defines all the logical constraints that need to be applied on the data stored. It defines tables, views, and integrity constraints.

Meta Data - Metadata is literally "data about data", is information that describes another set of data. A common example is a library catalog card, which contains data about the contents and location of a book: It is data about the data in the book referred to by the card.

Instance - The data in the database at the particular in time is called database state or snapshot.It is also called as current state of occurrences or instances in database.

The term instance in database is also used to describe a complete database environment, including the RDBMS software, table structure, stored procedures and other functionality. It is most commonly used when administrators describe multiple instances of the same database.

Examples: An organization with an employees database might have three different instances: production (used to contain live data), pre-production (used to test new functionality prior to release into production) and development (used by database developers to create new functionality).

File System Vs DBMS

Database management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The very well known DBMS are Microsoft Access, Microsoft SQL Server, Oracle, SAP, dBASE, FoxPro, IBM dB2, SQLite etc.


File Management System keep track on the files and also manage them. For Example: Hierarchical Management System using its directories manage the different files in a tree structure.

The disadvantages of file management systems over DBMS are:

a) Data redundancy and inconsistency
b) Difficulty in accessing data
c) Data isolation
d) Integrity problems
e) Atomicity problems

The advantages of DBMS over file management system are:

a) Control redundancy
b) Restrict unauthorized access
c) Provide multiple user interfaces
d) Enforce integrity constraints.
e) Provide backup and recover

File Management System	DBMS
File management system is a DBMS that  allows access to single files at a time.	File management system was the  predecessor for the DBMS which allows   access to multiple files a a time.
File management system accommodate  flat files that have no relation to other files.	DBMS provides replation between the files.
File mangament system doesnot support  multiuser access.	DBMS support multi user access.
Limited to smaller databases.	It fits for the needs of many medium to  large sized organization or database.
Decentralization of data.	Centralized data.
Issues occur such as redundancy and  integrity	No such issue like redundancy and  integrity occurs here.Infact ,it controls the  redundancy and integrity problem which  occurs in File management system.

Overall Structure of DBMS

DBMS acts as an interface between user and the database. DBMS are very large and typically divided into modules :-

DDL Compiler - Converts DDL statements to a set of tables containing metadata stored in a data dictionary.Metadata information can be the name of files,data items,storage details of each file,mapping information and constraints,etc.

DML Compiler and Query Optimizer - DML compiler translates the Data Manipulation Languages into query 

Engine instructions. It might also do optimization for query.
Query processor/optimizer translates statements in a query language into low-level instructions the database manager understands. (It is used to find an equivalent but more efficient form).

Data Manager - The data manager is the central software component of the DBMS. It is sometimes referred to as the database control system.
One of the functions of the data manager is to convert operations in the user's queries coming directly via the query processor or\ indirectly via an application program from the user's logical view to a physical file system. 

The data manager is responsible for interfacing with the file system as show. In addition, the tasks of enforcing constraints to maintain the consistency and integrity of the data, as well as its security, are also performed by the data manager.
It is also the responsibility of the Data. Manager to provide the synchronization in the simultaneous operations performed by concurrent users and to maintain the backup and recovery operations.

Data Dictionary - Data Dictionary is a repository of description of data in the database.A data dictionary contains a list of all files in the database, the number of records in each file and the names and types of each field. Most database management systems keep the data dictionary hidden from users to prevent them from accidentally destroying its content.

Functions of the Data Dictionary-
1. Defines the data element.
2. Helps in the scheduling.
3. Helps in the control.
4. Permits the various users who know which data is available and how can it be obtained.
5. Helps in the identification of the organizational data irregularity.
6. Acts as a very essential data management tool.
7. Provides with a good standardization mechanism.
8. Acts as the corporate glossary of the ever growing information resource.
9. Provides the report facility, the control facility along with the excerpt facility.

Data Files - It stores the database.

Compiled DML - The DML complier converts the high level Queries into low level file access commands known as compiled DML.

End Users - End Users are the people who interact with the database through applications or utilities. The various categories of end users are:

1.  Casual End Users - These Users occasionally access the database but may need different information each time. They use sophisticated database Query language to specify their requests. For example: High level Managers who access the data weekly or biweekly.

2.  Native End Users - These users frequently query and update the database using standard types of Queries. The operations that can be performed by this class of users are very limited and effect precise portion of the database.For example: - Reservation clerks for airlines/hotels check availability for given request and make reservations. Also, persons using Automated Teller Machines (ATM's) fall under this category as he has access to limited portion of the database.

3.Standalone end Users/On-line End Users - Those end Users who interact with the database directly via on-line terminal or indirectly through Menu or graphics based Interfaces.Example:-Library Management System.  

Data,Database,DBMS and Database System

A data can be said as piece of information such as name of your school or college.

A database is a collection of related data organized in a way that data can be easily accessed,managed and updated.Database is actually a place where related information is stored and various operations can be performed on it.

A DBMS is software or tool that allows creation,definition and manipulation of database .In other words we can say it is used to perform various operation on data in database.
DBMS also provides  protection and security to database.
some examples of popular DBMS are as follows:MySql,Oracle,Sybase,MicrosoftAccess,IBM DB2,etc.

Database System :- It consist of four components:

Database System= User + Database Application + DBMS + Database

Users - they can be DBA(database adminstrator),System Developer and end user.

Database Application - It can be categorize into personal,departmental,enterprise and internal.

DBMS - software or tool that allows user to define ,create and manage database access.Ex: Mysql,Oracle,etc

Database - Collection of data or in other words we can say a place where data is stored.