Normalization is the process of removing redundant data from your tables in order to improve storage efficiency, data integrity and scalability. This improvement is balanced against an increase in complexity and potential performance losses from the joining of the normalized tables at query-time. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
The entity set which does not have sufficient attributes to form a primary key is called as Weak entity set. An entity set that has a primary key is called as Strong entity set. Consider an entity set Payment which has three attributes: payment_number, payment_date and payment_amount. Although each payment entity is distinct but payment for different loans may share the same payment number. Thus, this entity set does not have a primary key and it is an entity set. Each weak set must be a part of one-to-many relationship set.
DBMS A database management system is the software system that allows users to define, create and maintain a database and provides controlled access to the data.
A Database Management System (DBMS) is basically a collection of programs that enables users to store, modify, and extract information from a database as per the requirements. DBMS is an intermediate layer between programs and the data. Programs access the DBMS, which then accesses the data. There are different types of DBMS ranging from small systems that run on personal computers to huge systems that run on mainframes. The following are main examples of database applications:
SQL statements are divided into two major categories: data definition language (DDL) and data manipulation language (DML).
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
Category: DBMS & RDBMS
DBMS stand for Database Management System, which consist n number of tables there is no relationship between another table. RDMBS stand for Relational Database Management System, which having the relationship with other tables. The Relationship between tables in DBMS is Physical and the relationship in RDBMS is Logical.
When multiple transactions are trying to access the same sharable resource, there could arise many problems if the access control is not done properly. There are some important mechanisms to which access control can be maintained. Earlier we talked about theoretical concepts like serializability, but the practical concept of this can be implemented by using Locks and Timestamps.
There are four basic components of Database Management System:
(i) Data: Raw facts which we want to feed in the computer.
(ii) Hardware: On which the data to be processed.
(iii) Software: The interface between the hardware and user, by which the data will change into the information.
(iv) User: There are so many types of users some of them are application programmer, end case users and DBA.
The environment of database is said to be instance. A database instance or an ‘instance’ is made up of the background processes needed by the database software. These processes usually include a process monitor, session monitor, lock monitor, etc. They will vary from database vendor to database vendor.
A typical structure of a DBMS with its components and relationships between them is show. The DBMS software is partitioned into several modules. Each module or component is assigned a specific operation to perform. Some of the functions of the DBMS are supported by operating systems (OS) to provide basic services and DBMS is built on top of it. The physical data and system catalog are stored on a physical disk. Access to the disk is controlled primarily by as, which schedules disk input/output. Therefore, while designing a DBMS its interface with the as must be taken into account.
There are two techniques used for the purpose of data base designing from the system requirements. These are:
• Top down Approach known as Entity-Relationship Modeling
• Bottom Up approach known as Normalization.
When a company asks you to make them a working, functional Database Management System (DBMS) which they can work with, there are certain steps to follow. Let us summarize them here:
1. Gathering information: This could be a written document that describes the system in question with reasonable amount of details.
2. Producing ERD: ERD or Entity Relationship Diagram is a diagrammatic representation of the description we have gathered about the system.
3. Designing the database: Out of the ERD we have created, it is very easy to determine the tables, the attributes which the tables must contain and the relationship among these tables.
4. Normalization: This is a process of removing different kinds of impurities from the tables we have just created in the above step.
How to Prepare an ERD
Let us take a very simple example and we try to reach a fully organized database from it. Let us look at the following simple statement:
A boy eats an ice cream.
This is a description of a real word activity, and we may consider the above statement as a written document (very short, of course).
Now we have to prepare the ERD. Before doing that we have to process the statement a little. We can see that the sentence contains a subject (boy), an object (ice cream) and a verb (eats) that defines the relationship between the subject and the object. Consider the nouns as entities (boy and ice cream) and the verb (eats) as a relationship. To plot them in the diagram, put the nouns within rectangles and the relationship within a diamond. Also, show the relationship with a directed arrow, starting from the subject entity (boy) towards the object entity (ice cream).