Introduction

• A database management system (DBMS) is a software package designed to define, manipulate, retrieve and manage data in a database.
• A collection of programs that enables users to create and maintain databases and control all access to them.
• The primary goal of a DBMS is to provide an environment that is both convenient and efficient for users to retrieve and store information.
• For example: Microsoft SQL, PL/SQL, SQLite, MongoDB, Cassandra, MySQL, Oracle, CouchDB, Neo4j, DB2, etc.

Classification

• Distribution Based
  • Centralized database system, the DBMS and database are stored at a single site that is used by several other systems too
  • Distributed database system, the actual database and the DBMS software are distributed from various sites that are connected by a computer network
  • Homogeneous distributed database systems, use the same DBMS software at multiple sites
  • Heterogeneous distributed database system, different sites might use different DBMS software, but there is additional common software to support data exchange between these sites
• User Based
  • single-user database system, a database management system which supports one user at a time
  • multiuser database system, a database management system which supports multiple users concurrently
• Data Model Based
  • relational data model, Well-known DBMSs like Oracle, MS SQL Server, DB2 and MySQL support this model.
  • object-oriented data models , a database management system in which information is represented in the form of objects as used in object-oriented programming, e.g., O2, ObjectStore and Jasmine.
  • hierarchical data models, a traditional models, data models that preceded the relational model
  • network data models, a traditional models, data models that preceded the relational model

ER Diagram

• Entity
  • An entity may be any object, class, person or place.
  • weak entity, an entity that depends on another entity called a weak entity.
  • In the ER diagram, an entity can be represented as rectangles and weak entity is represented by a double rectangle.
• Attribute
  • The attribute is used to describe the property of an entity. Eclipse is used to represent an attribute.
  • Key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is represented by an ellipse with the text underlined.
  • Multivalued attribute, an attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute.
  • Composite attribute is composed of many other attributes is known as a composite attribute. The composite attribute is represented by an ellipse, and those ellipses are connected with an ellipse.
  • Derived attributes, an attribute can have more than one value. These attributes are known as a multivalued attribute. The double oval is used to represent multivalued attribute.

Normalization

• Normalization is the process of organizing the data in the database.
• Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.
• Normalization divides the larger table into the smaller table and links them using relationship.
• The normal form is used to reduce redundancy from the database table.

Type of Normalization

• 1NF: A relation is in 1NF if it contains an atomic value.
• 2NF: A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.
• 3NF: A relation will be in 3NF if it is in 2NF and no transition dependency exists.
• 4NF: A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued dependency.
• 5NF: A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.

Relationship

• A relationship is used to describe the relation between entities. Diamond or rhombus is used to represent the relationship.
• One-to-One Relationship, when only one instance of an entity is associated with the relationship, then it is known as one to one relationship.
• One-to-many relationship, when only one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then this is known as a one-to-many relationship.
• Many-to-one relationship, when more than one instance of the entity on the left, and only one instance of an entity on the right associates with the relationship then it is known as a many-to-one relationship.
• Many-to-many relationship, when more than one instance of the entity on the left, and more than one instance of an entity on the right associates with the relationship then it is known as a many-to-many relationship.

Generalization

• Generalization is like a bottom-up approach in which two or more entities of lower level combine to form a higher level entity if they have some attributes in common, it is more like subclass and superclass system.
• In generalization, entities are combined to form a more generalized entity, i.e., subclasses are combined to make a superclass.

Specialization

• Specialization is a top-down approach, and it is opposite to Generalization. In specialization, one higher level entity can be broken down into two lower level entities.
• Specialization is used to identify the subset of an entity set that shares some distinguishing characteristics.
• Normally, the superclass is defined first, the subclass and its related attributes are defined next, and relationship set are then added.

Aggregation

In aggregation, the relation between two entities is treated as a single entity. In aggregation, relationship with its corresponding entities is aggregated into a higher level entity.