Metadata

 

What is Meta Data?

Metadata is data about the data or documentation about the information which is required by the users. In data warehousing, metadata is one of the essential aspects.

Metadata includes the following:

1. The location and descriptions of warehouse systems and components.
2. Names, definitions, structures, and content of data-warehouse and end-users views.
3. Identification of authoritative data sources.
4. Integration and transformation rules used to populate data.
5. Integration and transformation rules used to deliver information to end-user analytical tools.
6. Subscription information for information delivery to analysis subscribers.
7. Metrics used to analyze warehouses usage and performance.
8. Security authorizations, access control list, etc.

Metadata is used for building, maintaining, managing, and using the data warehouses. Metadata allow users access to help understand the content and find data.

everal examples of metadata are:

1. A library catalog may be considered metadata. The directory metadata consists of several predefined components representing specific attributes of a resource, and each item can have one or more values. These components could be the name of the author, the name of the document, the publisher's name, the publication date, and the methods to which it belongs.
2. The table of content and the index in a book may be treated metadata for the book.
3. Suppose we say that a data item about a person is 80. This must be defined by noting that it is the person's weight and the unit is kilograms. Therefore, (weight, kilograms) is the metadata about the data is 80.
4. Another examples of metadata are data about the tables and figures in a report like this book. A table (which is a record) has a name (e.g., table titles), and there are column names of the tables that may be treated metadata. The figures also have titles or names.

What are the Functions and Service of DBMS

 (i) Data Storage Management: It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the operating system to access the physical storage.

(ii) Data Manipulation Management: A DBMS furnishes users with the ability to retrieve, update and delete existing data in the database.

(iii) Data Definition Services: The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mappings in source form.

(iv) Data Dictionary/System Catalog Management: The DBMS provides a data dictionary or system catalog function in which descriptions of data items are stored and which is accessible to users.

(v) Database Communication Interfaces: The end-user’s requests for database access are transmitted to DBMS in the form of communication messages.

(vi) Authorization / Security Management: The DBMS protects the database against unauthorized access, either international or accidental. It furnishes mechanism to ensure that only authorized users an access the database.

{vii) Backup and Recovery Management: The DBMS provides mechanisms for backing up data periodically and recovering from different types of failures. This prevents the loss of data,

(viii) Concurrency Control Service: Since DBMSs support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMSs ensure that the database kept in consistent state and that integrity of the data is preserved.

(ix) Transaction Management: A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database. Therefore, a DBMS must provide a mechanism to ensure either that all the updates corresponding to a given transaction are made or that none of them is made.

(x) Database Access and Application Programming Interfaces: All DBMS provide interface to enable applications to use DBMS services. They provide data access via Structured Query Language (SQL). The DBMS query language contains two components: (a) a Data Definition Language (DDL) and (b) a Data Manipulation Language (DML).

Applications of DMBS

Application of DBMS

There are different fields where a database management system is utilized. Following are a few applications which utilize the information base administration framework – 

1. Railway Reservation System –
   In the rail route reservation framework, the information base is needed to store the record or information of ticket appointments, status about train’s appearance, and flight. Additionally, if trains get late, individuals become acquainted with it through the information base update.  
    
2. Library Management System –
   There are lots of books in the library so; it is difficult to store the record of the relative multitude of books in a register or duplicate. Along these lines, the data set administration framework (DBMS) is utilized to keep up all the data identified with the name of the book, issue date, accessibility of the book, and its writer.  
    
3. Banking –
   Database the executive’s framework is utilized to store the exchange data of the client in the information base.
     
4. Education Sector –
   Presently, assessments are led online by numerous schools and colleges. They deal with all assessment information through the data set administration framework (DBMS). In spite of that understudy’s enlistments subtleties, grades, courses, expense, participation, results, and so forth all the data is put away in the information base.  
    
5. Credit card exchanges – 
   The database Management framework is utilized for buying on charge cards and age of month to month proclamations.  
    
6. Social Media Sites –
   We all utilization of online media sites to associate with companions and to impart our perspectives to the world. Every day, many people group pursue these online media accounts like Pinterest, Facebook, Twitter, and Google in addition to. By the utilization of the data set administration framework, all the data of clients are put away in the information base and, we become ready to interface with others.  
    
7. Broadcast communications – 
   Without DBMS any media transmission organization can’t think. The Database the executive’s framework is fundamental for these organizations to store the call subtleties and month to month postpaid bills in the information base.  
    
8. Account –
   The information base administration framework is utilized for putting away data about deals, holding and acquisition of monetary instruments, for example, stocks and bonds in a data set. 
    
9. Online Shopping – 
   These days, web-based shopping has become a major pattern. Nobody needs to visit the shop and burn through their time. Everybody needs to shop through web based shopping sites, (for example, Amazon, Flipkart, Snapdeal) from home. So all the items are sold and added uniquely with the assistance of the information base administration framework (DBMS). Receipt charges, installments, buy data these are finished with the assistance of DBMS.  
    
10. Human Resource Management – 
    Big firms or organizations have numerous specialists or representatives working under them. They store data about worker’s compensation, assessment, and work with the assistance of an information base administration framework (DBMS).  
     
11. Manufacturing – 
    Manufacturing organizations make various kinds of items and deal them consistently. To keep the data about their items like bills, acquisition of the item, amount, inventory network the executives, information base administration framework (DBMS) is utilized.  
     
12. Airline Reservation System – 
    This framework is equivalent to the railroad reservation framework. This framework additionally utilizes an information base administration framework to store the records of flight takeoff, appearance, and defer status.  
     

 

DDL AND DML

 

DDL

DDL is Data Definition Language and is used to define the structures like schema, database, tables, constraints etc. Examples of DDL are create and alter statements.

DML

DML is Data Manipulation Language and is used to manipulate data. Examples of DML are insert, update and delete statements.

Following are the important differences between DDL and DML.

Sr. No.	Key	DDL	DML
1	Stands for	DDL stands for Data Definition Language.	DML stands for Data Manipulation Language.
2	Usage	DDL statements are used to create database, schema, constraints, users, tables etc.	DML statement is used to insert, update or delete the records.
3	Classification	DDL has no further classification.	DML is further classified into procedural DML and non-procedural DML.
4	Commands	CREATE, DROP, RENAME and ALTER.	INSERT, UPDATE and DELETE.

Structure of Database Management System

 The database system is divided into three components: Query Processor, Storage Manager, and Disk Storage. These are explained as following below. 

Architecture of DBMS

1. Query Processor : 
It interprets the requests (queries) received from end user via an application program into instructions. It also executes the user request which is received from the DML compiler. 
Query Processor contains the following components – 

• DML Compiler – 
  It processes the DML statements into low level instruction (machine language), so that they can be executed. 
   
• DDL Interpreter – 
  It processes the DDL statements into a set of table containing meta data (data about data). 
   
• Embedded DML Pre-compiler – 
  It processes DML statements embedded in an application program into procedural calls. 
   
• Query Optimizer – 
  It executes the instruction generated by DML Compiler.

2. Storage Manager : 
Storage Manager is a program that provides an interface between the data stored in the database and the queries received. It is also known as Database Control System. It maintains the consistency and integrity of the database by applying the constraints and executes the DCL statements. It is responsible for updating, storing, deleting, and retrieving data in the database. 
It contains the following components – 

• Authorization Manager – 
  It ensures role-based access control, i.e,. checks whether the particular person is privileged to perform the requested operation or not. 
   
• Integrity Manager – 
  It checks the integrity constraints when the database is modified. 
   
• Transaction Manager – 
  It controls concurrent access by performing the operations in a scheduled way that it receives the transaction. Thus, it ensures that the database remains in the consistent state before and after the execution of a transaction. 
   
• File Manager – 
  It manages the file space and the data structure used to represent information in the database. 
   
• Buffer Manager – 
  It is responsible for cache memory and the transfer of data between the secondary storage and main memory. 
   

3. Disk Storage: It contains the following components – 

• Data Files – 
  It stores the data. 
   
• Data Dictionary – 
  It contains the information about the structure of any database object. It is the repository of information that governs the metadata. 
   
• Indices – 
  It provides faster retrieval of data item.

DBMS

 

What is Database

The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

• Database management system is a software which is used to manage the database. For example: MySQL, Oracle, etc are a very popular commercial database which is used in different applications.
• DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database and a lot more.
• It provides protection and security to the database. In the case of multiple users, it also maintains data consistency.

DBMS allows users the following tasks:

• Data Definition: It is used for creation, modification, and removal of definition that defines the organization of data in the database.
• Data Updation: It is used for the insertion, modification, and deletion of the actual data in the database.
• Data Retrieval: It is used to retrieve the data from the database which can be used by applications for various purposes.
• User Administration: It is used for registering and monitoring users, maintain data integrity, enforcing data security, dealing with concurrency control, monitoring performance and recovering information corrupted by unexpected failure.

Characteristics of DBMS

• It uses a digital repository established on a server to store and manage the information.
• It can provide a clear and logical view of the process that manipulates data.
• DBMS contains automatic backup and recovery procedures.
• It contains ACID properties which maintain data in a healthy state in case of failure.
• It can reduce the complex relationship between data.
• It is used to support manipulation and processing of data.
• It is used to provide security of data.
• It can view the database from different viewpoints according to the requirements of the user.

Advantages of DBMS

• Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.
• Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.
• Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.
• Reduce time: It reduces development time and maintenance need.
• Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required.
• multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces

Disadvantages of DBMS

• Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software.
• Size: It occupies a large space of disks and large memory to run them efficiently.
• Complexity: Database system creates additional complexity and requirements.
• Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever.

Difference Between File System and DBMS

File System	Database Management System
A file system is a software application that organizes and maintains files on a storage device. It manages the storage and retrieval of data.	A database management system, or DBMS, is a software application that allows you to access, create, and manage databases.
The file system is more related to an operating system and is shipped as a part of the operating system itself.	The database is a software application and is not shipped as a part of the operating system. It is more about organizing the data and implementing techniques to keep the data consistent and to have faster access to the data.
The data format and storage details are provided by the file system.	A database management system (DBMS) provides an abstract representation of data that conceals the specifics.
A file system does not allow for efficient data storage and retrieval.	The use of a database management system is efficient because there are numerous techniques for storing and retrieving data.
It does not provide data recovery services.	In DBMS, there is a backup recovery option for data.
There is no crash recovery mechanism in the file system.	A crash recovery method is provided by DBMS.
It’s difficult to keep a file system safe.	A good protection method is provided by DBMS.
Data redundancy is higher in a file management system.	Redundancy is minimized to a great extent in a database management system.
The file system has a higher level of data inconsistency. Same data might be stored at multiple places yet have different values leading to data inconsistency.	A database management system has a low level of data inconsistency.
You can store data as discrete data files and entities using the File System.	Data, as well as defined restrictions and interrelationships, are stored in a database management system.
Support for complex transactions is not available.	Complex transactions are simple to implement.
In a File Management System, the centralization process is difficult.	In a database management system, centralization is simple to achieve.
In the file system, there is no efficient query processing.	In a database management system, data can be retrieved based on the requirements and can be easily filtered from the database.
Concurrency is not available in this system.	A concurrency facility is provided by a database management system.
It is less expensive than a database management system.	It is costlier than that a file system.
Data consistency is less reliable in the file system.	The process of normalization results in more data consistency.
It is difficult to implement integrity constraints in file systems.	Integrity constraints can be easily implemented in DBMS.
The system is less complex than a database management system. Complex transactions are not supported by file systems.	As compared to file systems, DBMS is more complex to handle. Complex transactions can be implemented easily in DBMS.
Data independence is not possible in file systems.	DBMS provides data independence.
File systems allow only one user to access data at a time.	Data can be accessed simultaneously by multiple users in a DBMS.
A file system provides less security than a database management system.	DBMSs are more secure than file systems since they have a wider range of security mechanisms.
The data is spread across many files, so sharing it is difficult.	Since data is centralized, it is easy to share.
Data storage and representation details are provided.	By hiding the internal details of the database, it makes it more secure.
Examples – NTFS (New Technology File System), FAT (File Allocation Table), EXT (Extended File System), etc.	Examples – My SQL, Oracle, SQL Server, PostgreSQL, Microsoft SQL Server, Microsoft Access, etc.

introduction to data models

 Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.

The very first data model could be flat data-models, where all the data used are to be kept in the same plane. Earlier data models were not so scientific, hence they were prone to introduce lots of duplication and update anomalies.

Entity-Relationship Model

Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them. While formulating real-world scenario into the database model, the ER Model creates entity set, relationship set, general attributes and constraints.

ER Model is best used for the conceptual design of a database.

ER Model is based on −

• Entities and their attributes.

• Relationships among entities.

These concepts are explained below.

• Entity − An entity in an ER Model is a real-world entity having properties called attributes. Every attribute is defined by its set of values called domain. For example, in a school database, a student is considered as an entity. Student has various attributes like name, age, class, etc.

• Relationship − The logical association among entities is called relationship. Relationships are mapped with entities in various ways. Mapping cardinalities define the number of association between two entities.

  Mapping cardinalities −

  • one to one
  • one to many
  • many to one
  • many to many

Relational Model

The most popular data model in DBMS is the Relational Model. It is more scientific a model than others. This model is based on first-order predicate logic and defines a table as an n-ary relation.

The main highlights of this model are −

• Data is stored in tables called relations.
• Relations can be normalized.
• In normalized relations, values saved are atomic values.
• Each row in a relation contains a unique value.
• Each column in a relation contains values from a same domain.

The ER model defines the conceptual view of a database. It works around real-world entities and the associations among them. At view level, the ER model is considered a good option for designing databases.

Entity

An entity can be a real-world object, either animate or inanimate, that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity.

An entity set is a collection of similar types of entities. An entity set may contain entities with attribute sharing similar values. For example, a Students set may contain all the students of a school; likewise a Teachers set may contain all the teachers of a school from all faculties. Entity sets need not be disjoint.

Attributes

Entities are represented by means of their properties, called attributes. All attributes have values. For example, a student entity may have name, class, and age as attributes.

There exists a domain or range of values that can be assigned to attributes. For example, a student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be negative, etc.

Types of Attributes

• Simple attribute − Simple attributes are atomic values, which cannot be divided further. For example, a student's phone number is an atomic value of 10 digits.

• Composite attribute − Composite attributes are made of more than one simple attribute. For example, a student's complete name may have first_name and last_name.

• Derived attribute − Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database, instead it can be derived. For another example, age can be derived from data_of_birth.

• Single-value attribute − Single-value attributes contain single value. For example − Social_Security_Number.

• Multi-value attribute − Multi-value attributes may contain more than one values. For example, a person can have more than one phone number, email_address, etc.

These attribute types can come together in a way like −

• simple single-valued attributes
• simple multi-valued attributes
• composite single-valued attributes
• composite multi-valued attributes

Entity-Set and Keys

Key is an attribute or collection of attributes that uniquely identifies an entity among entity set.

For example, the roll_number of a student makes him/her identifiable among students.

• Super Key − A set of attributes (one or more) that collectively identifies an entity in an entity set.

• Candidate Key − A minimal super key is called a candidate key. An entity set may have more than one candidate key.

• Primary Key − A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.

Relationship

The association among entities is called a relationship. For example, an employee works_at a department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.

Relationship Set

A set of relationships of similar type is called a relationship set. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes.

Degree of Relationship

The number of participating entities in a relationship defines the degree of the relationship.

• Binary = degree 2
• Ternary = degree 3
• n-ary = degree

Mapping Cardinalities

Cardinality defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set.

• One-to-one − One entity from entity set A can be associated with at most one entity of entity set B and vice versa.



• One-to-many − One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.



• Many-to-one − More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.



• Many-to-many − One entity from A can be associated with more than one entity from B and vice versa.

Let us now learn how the ER Model is represented by means of an ER diagram. Any object, for example, entities, attributes of an entity, relationship sets, and attributes of relationship sets, can be represented with the help of an ER diagram.

Entity

Entities are represented by means of rectangles. Rectangles are named with the entity set they represent.

Attributes

Attributes are the properties of entities. Attributes are represented by means of ellipses. Every ellipse represents one attribute and is directly connected to its entity (rectangle).

If the attributes are composite, they are further divided in a tree like structure. Every node is then connected to its attribute. That is, composite attributes are represented by ellipses that are connected with an ellipse.

Multivalued attributes are depicted by double ellipse.

Derived attributes are depicted by dashed ellipse.

Relationship

Relationships are represented by diamond-shaped box. Name of the relationship is written inside the diamond-box. All the entities (rectangles) participating in a relationship, are connected to it by a line.

Binary Relationship and Cardinality

A relationship where two entities are participating is called a binary relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.

• One-to-one − When only one instance of an entity is associated with the relationship, it is marked as '1:1'. The following image reflects that only one instance of each entity should be associated with the relationship. It depicts one-to-one relationship.



• One-to-many − When more than one instance of an entity is associated with a relationship, it is marked as '1:N'. The following image reflects that only one instance of entity on the left and more than one instance of an entity on the right can be associated with the relationship. It depicts one-to-many relationship.



• Many-to-one − When more than one instance of entity is associated with the relationship, it is marked as 'N:1'. The following image reflects that more than one instance of an entity on the left and only one instance of an entity on the right can be associated with the relationship. It depicts many-to-one relationship.



• Many-to-many − The following image reflects that more than one instance of an entity on the left and more than one instance of an entity on the right can be associated with the relationship. It depicts many-to-many relationship.

Participation Constraints

• Total Participation − Each entity is involved in the relationship. Total participation is represented by double lines.

• Partial participation − Not all entities are involved in the relationship. Partial participation is represented by single lines.