Download Introduction to Database Management System: Chapter 3 and more Summaries Design in PDF only on Docsity!
57
managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.”
What type of data is stored in a database? In a database, we would be grouping only related data together and storing them under one group name called table. This helps in identifying which data is stored where and under what name.
3.1.2 Evolution of DBMS
The concept of storing the data started before 40 years in various formats. In earlier days they have used punched card technology to store the data. Then files were used. The file systems were known as predecessor of database system. Various access methods in file system were indexed, random and sequential access. The file system had more limitations like
3.1 Introduction to Database Management System
DBMS stands for Database Management System, so let us break down the words what they really mean. A database is a place where we store, retrieve and manage data. So what’s a data then? Meaningful information like your name, your favorite color etc to complex data like astronomical data that scientist handle, everything comes under database. The management system refers to a set of programs to manage the data, we have with various actions like storing, retrieving, filtering etc. Some of the popular Database Management System is MySQL, Oracle etc. Giving protection to data, user-friendly for users etc, are some of the notable features of good DBMS.
3.1.1 Introduction to DBMS
Definition: “A database management system (DBMS) is system software for creating and
Introduction to Database Management System
LEARNING OBJECTIVES To understand database concepts, components and its functions. ● ● To know about relational model of data ● ● To understand Query languages for databases. ● ● Enables to write SQL commands and query processing ● ● To enhance the programming skills and Techniques using MySQL
CHAPTER
58 Chapter 3 Introduction to Database Management System
Data Duplication – Same data is used by multiple resources for processing, thus created multiple copies of same data wasting the spaces. High Maintenance – Access control and verifying data consistency needs high maintenance cost. Security – less security provided to the data. So database systems became popular to overcome the above limitations of file system.
3.1.3 DBMS Concepts
There exist few standards that are applicable to all forms of database management Systems like Relational Database Management System (RDBMS) and Object Database Management System (ODBMS). All DBMS adheres to the following two basic concepts. ACID Properties – The acronym stands for Atomicity, Consistency, Isolation and Durability. A tomicity follows the thumb rule “All or Nothing”, while updating the data in database for the user performing the update operation. This update operation is called as transaction and it either commits (successful updating) or aborts (updating failure). C onsistency ensures that the changes in data value to be constant at any
given instance. This property helps in the successful transaction. I solation property is needed during concurrent transaction. When multiple users do the transactions by accessing same object at the same time, the transaction is known as concurrent transaction. To prevent the conflict in database update, the transactions are isolated from other user and serialized. This is also known as Degree of Consistency. D urability is defined as the system’s ability to recover all committed transactions during the failure of storage or the system. Concurrency Control and Locking – It is the DBMSs mechanism used for data sharing. When the same data is shared among multiple users, proper access control is needed and privilege of changing the applications data item is controlled through Locking.
3.2 DBMS Database Models The database technology came into existence in terms of models with relational and object-relational behavior. The major database models are listed below:
3.2.1 Hierarchical Database
Model The famous Hierarchical database model was IMS (Information Management
Company
Users Machines
Marketing Personnel
Devices
= Container = Object
Applications
Figure: 3.1 Hierarchical database model
60 Chapter 3 Introduction to Database Management System
3.4 RDBMS Jargons
3.4.1 Database
The most popular Relational Database is MySQL. It is an open source SQL database supporting different platforms like Windows, Linux and MAC Operating Systems. The other relational databases available are Oracle, MS SQL Server and MS Access. The features of RDBMS are ● ● High Availability ● ● High Performance ● ● Robust Transactions and support ● ● Ease of management ● ● Less cost
3.4.2 Table
In relational database model, table is defined as the collection of data organized in terms of rows and columns. Table is the simple representation of relations. The true relations cannot have duplicate rows
uses small, reusable software known as Objects. These are stored in object oriented database. This model efficiently manages large number of different data types. Moreover complex behaviors are handled efficiently using OOP’s concepts. See Figure 3.
3.3 Relational Database
Management System
Basic RDBMS concepts Any database whose logical organization is based on relational data model is known as Relational Database. A DBMS that manages the relational database is known as Relational Data Base Management System. RDBMS is basis for SQL and for all modern database systems like MySQL, oracle and Microsoft Access. The basic RDBMS concept includes Database, Tables, Tuple, Attribute, Schema and Key which are discussed in RDBMS Jargons.
Name
Age Salary
Works in Department
DeptName Budget
Salesperson M
(^1) Lives 1 At Has Orders
Has Accounts
Account Orders
Address
StName StNumber City
State
Zip
N
N
N
1
N
Figure: 3.4 Object-oriented database model
Chapter 3 Introduction to Database Management System 61
of more than one attribute is called a composite primary key.
3.4.6 Foreign Key
A foreign key is a “copy” of a primary key that has been exported from one relation into another to represent the existence of a relationship between them. A foreign key is a copy of the whole of its parent primary key i.e if the primary key is composite, then so is the foreign key. Foreign key values do not (usually) have to be unique. Foreign keys can also be null. A composite foreign key cannot have some attribute(s) null and others non-null.
3.4.7 Super Key
An attribute or group of attributes, which is sufficient to distinguish every tuple in the relation from every other one is known as Super Key. Each super key is called a candidate key. A candidate key is selected from the set of Super Key. While selecting candidate key, redundant attributes should not be taken. The candidate key is also known as minimal super keys.
3.4.8 Composite Key
A key with more than one attribute to identify rows uniquely in a table is called Composite key. This is also known as Compound Key.
3.5 ER Model Generally we use an ER model to know the concept of database design and this model consists of a collection of entities(real world objects)where each of these entities will be interconnected with each other with conditions and dependencies(i.e. one entity is dependent on another).
where as the table can have. The example of Employee table is shown below in Table 3.1.
Table 3.1 Table Structure ID NAME AGE SALARY 1 Alex 26 22, 2 Divya 25 20, 3 Tulsi 28 30,
3.4.3 Column
The table consists of several rows and columns. Table can be divided into smaller parts, in terms of columns. Each column is known as attributes. In the Employee table four attributes are available namely Id, Name, Age and Salary. The attribute is defined in a table to hold values of same type. This is known as Attribute Domain. In the Employee table, the Name field will hold only characters not the numbers in it.The vertical entity in a table is known as Attribute or Column.
3.4.4 Row
A single entry in a table is called as Row or Record or Tuple. Set of related data’s are represented in a row or tuple. The horizontal entity in a table is known as Record or row. See Table 3.
Table 3.2 Row Structure ID NAME AGE SALARY 1 Alex 26 22,
3.4.5 Key
The candidate key that is chosen to perform the identification task is called the primary key and any others are Alternate keys. Every tuple must have, by definition, a unique value for its primary key. A primary key which is a combination
Chapter 3 Introduction to Database Management System 63
3.5.4.4 Single Valued Attributes: A single valued attribute contains only one value for the attribute and they don’t have multiple number of values. For Example:Age- It is a single value for a person as we cannot give ‘n’ number of ages for a single person,therefore it is a single valued attribute. See Table 3. Table: 3.4 Single Valued Attributes Attribute Values
Age 3 Roll no 85
In the above table are the some examples for single valued attributes. See Figure 3.
Person
Date of Birth
Figure 3.8 Single Valued Attributes
3.5.4.5 Multi Valued Attributes: A multi valued attribute has more than one value for that particular attribute.For Example:Degree - A person can hold n number of degrees so it is a multi-valued attribute. In Table 3.5 are some examples for Multi valued attributes.
Table: 3.5 Attributes and Values Attribute Values
Degree B.Tech, MBA Bank_Account SBI, HDFC
3.5.4 Attributes
An attribute is the information about that entity and it will describe, quantify, qualify, classify, and specify an entity. An attribute will always have a single value, that value can be a number or character or string.
Types of attributes:
1. Key Attribute 2. Simple Attributes 3. Composite Attributes 4. Single Valued Attribute 5. Multi Valued Attribute
3.5.4.1 Key Attribute
Generally a key attribute describes a unique characteristic of an entity.
3.5.4.2 Simple Attribute
Employee
First name
Name
Last name
Figure 3.7 Simple Attribute
The simple attributes cannot be separated. It will have a single value for that entity. For Example: Let us consider the name as the attribute for the entity employee and here the value for that attribute is a single value. See Figure 3.
3.5.4.3 Composite Attributes
The composite attributes can be sub- divided into simple attributes without change in the meaning of that attribute. For Example: In the above diagram the employee is the entity with the composite attribute Name which are sub-divided into two simple attributes first and last name.
64 Chapter 3 Introduction to Database Management System
and the department entity, then Ram works for Comp.sc department, shyam works for electrical department ..etc are relationship instances of the relationship, works for.
3.5.5.2 Degree of a relationship The number of entity types involved is known as Degree of relationship. One – Unary, Two – Binary, Three – Ternary.E.g An employee of an organization acts as manager of few other employees. It also connects one entity to itself as a loop. so manager-of is unary. Similarly employee works-for department, connects two entities and is binary. If a customer purchase an item, it involves shop keeper also and is a ternary relationship.
3.5.5.3 Cardinality It is defined as the number of items that must be included in a relationship.ie number of entities in one set mapped with the number of entities of another set via the relationship. The three classifications in Cardinality are one-to-one, one-to-many and Many-to-Many. See Figure 3.9-3.
Person Vehicle
1 driver^ Drives Figure 3.9 Cardinality In the above example we have two entities Person and Vehicle. If we consider a person driving vwchicle, then we have one-to-one relationship between Person and Vehicle. See Figure 3.
Customer Order
n Places
Figure 3.10 CardinalityRelation 1 to n In the above example, Customer places the Order is a one-to-many relationship.
3.5.5 Relationship Type
In ER Model, relationship exists between two entities. Three types of relationships are available and the Entity- Relationship(ER) diagram is based on the three types listed below. One-to-One relationship : Consider two entities A and B. one-to-one (1:1) relationship is said to exist in a relational database design, if 0 or 1 instance of entity A is associated with 0 or 1 instance of entity B, and 0 or 1 instance of entity B is associated with 0 or 1 instance of entity A. One-to-Many relationship : Consider two entities A and B. one-to-many (1:N) relationship is said to exist in a relational database design, for 1 instance of entity A there exists 0 or 1 or many instances of entity B, but for 1 instance of entity B there exists 0 or 1 instance of entity A. Many-to-Many relationship :Consider two entities A and B. many-to-many (M:N) relationship is said to exist in a relational database design, for 1 instance of entity A there exists 0 or 1 or many instances of entity B, and for 1 instance of entity B there exists 0 or 1 or many instance of entity A. In reality one-to-one are in less usage, where as one-to-many and many-to-many are commonly used. However in relational databases, many-to-many are converted into one-to-many relationships.
3.5.5.1 Relationship instance It is a finite set of liples in the RDBMS systems relation instances never have duplicate. E.g if Works-for is the relationship between the Employee entity
66 Chapter 3 Introduction to Database Management System
is a combination of “My” the name of the founder Monty Widenius’s daughter and “SQL”. A clear definition of database and SQL is mandatory to understand MySQL. In simple, a database is defined as the structured collection of data. Ex. Photo gallery is a database which has collection of photos (data). SQL - structured query language is not a database. It is a standardized language used to access the database and the data’s are processed to turn into efficient information. The SQL standard always refers to the current version and the current version is
- MySQL is open source software that allows managing relational databases. It also provides the flexibility of changing the source code as per the needs. It runs on multiple platforms like Windows, Linux and is scalable, reliable and fast.
3.7.2 Overview of Web Database
Many databases are in existence to meet out the needs of the application. These databases are broadly divided into Heavy and Light databases. Heavy databases support all the desktop applications whereas the web applications are
3.6.2 Example
Fig 3.12 is an example of ER Diagram for the scenario of School Management System. We have many entities like Parent, student, Admin, Teacher, class and subjects. The attributes of few entity is given below: Parent – Name, Id, Fname, Lname. Student – Id, Name, Fname, Lname. Teacher – Name, Id, Phone#, Address. The relationships that exist between the entities are as follows: ● ● Parent HAVE Student ● ● Admin ENROLLS Student ● ● Admin HIRES Teacher ● ● Admin ADDS Subject ● ● Teacher TEACHES Subject ● ● Teacher IS ASSIGNED TO Class Key Attributes are listed with underline.
3.7 Introduction to MYSQL
3.7.1 About MYSQL
MySQL is an open source relational database management system. Its name
Fname Lname ID Name
Fname Lname Name (^1) Have n
ID Relationshipwith student PARENT
Fname (^) Lname Name
Fname Lname
Name
Name
Name
Fname Lname Phone# ID Address
ID (^) ClassID
ID
ID
ADMIN
Class
SUBJECT
Teacher
STUDENT
Enrolls Takes
Adds
Teaches
Is assignedto
Hires
(^11) n n n 0
(^1) n
Figure: 3.12 ER-Diagram
Chapter 3 Introduction to Database Management System 67
3.8 MySQL - Administration
3.8.1 MySQL – Administration
Responsibilities In general there exists a role known as Database Administrators (DBA’s) who takes care of configuration, installation, performance, security and data backup. DBA’s posses the skills on database design, database queries, RDMS, SQL and networking. The primary task is the creation of new user and providing them with access rights.
Creating New User Account to MySQL In MySQL database, there exists a table named user. The newly created account must have an entry in this user table. Consider the admin creates an account with username and password. The user account is activated with various access rights like INSERT, SELECT and UPDATE. Consider the user table has the following fields host, name, password, select_priv, insert_priv and update_priv.
supported by Light databases. Below are the lists of commonly used databases.
● ● DB
● ● MySQL
● ● Oracle
● ● PostgreSQL
● ● SQLite
● ● SQL Server
● ● Sybase
Due to the rapid growth in the web domain, most of the desktop applications are being converted into web applications. These transformations to the web results in the availability of many web applications in the network. Heavy databases did not meet out the network issues efficiently. Light databases were able to handle all the issues raised by the network. So all the light databases that supports the web applications are also known as Web Databases. See Figure 3.
MySQL is the most commonly used database in the world due to its ease of use.
21.2%
30.8%
21.2%
16.8%
13.2%
11.2%
2.5%
MySQL
SQL server
SQLite
PostgreSQL
MongoDB
Oracle
Redis
Cassandra
44.3%
Figure: 3.13 Most Popular databases - Statistics
Chapter 3 Introduction to Database Management System 69
Table 3.11 Column List Field Type Null Key Default Extra Team_id int(5) yes null Team_name varchar (10)
yes null
Team_size int(5) yes null Team_rank int(5) yes null
5. SHOW INDEX FROM tablename – The query shows all the indexes for the given table. Syntax: mysql > show indexes from sports; 6. SHOW TABLE STATUS LIKE tablename\G – This command provides with detailed report on the performance of the table.
3.8.3 MySQL Installation
Download and install XAMPP Server Software from Internet. Refer Figure 3.14 to 3.23.
Figure: 3.14 XAMPP Server executable file Click the Welcome Page Next Button
2. SHOW Databases – Lists all the databases available in the database server. See Table 3.
Syntax:
mysql > show databases;
Table 3.9 Database List Database test mysql employee students parents
3. SHOW Tables – Lists all the tables available in the current database we are working in. See Table 3.
Syntax:
mysql > show tables;
Table 3.10 Table List Tables_in_test school salary employee library sports
4. SHOW COLUMNS FROM tablename – Lists all the attributes, attribute type, Is Null value permitted, key information, default value and other information for the given table. The show columns for sports table is given below in Table 3.11.
Syntax:
mysql > show columns from sports;
70 Chapter 3 Introduction to Database Management System
Figure: 3.15 XAMPP Server Welcome Wizard
Select the Required component along with MYSQL component and click next button
Figure: 3.16 XAMPP Server Component Wizard
72 Chapter 3 Introduction to Database Management System
Installation get started
Figure: 3.19 XAMPP Server setup Progress window
After installing Click finish button and open the XAMMP Control panel
Figure: 3.20 XAMPP Server setup completion
In the Control Panel start the Apache and MySQL Services one by one
Chapter 3 Introduction to Database Management System 73
Figure: 3.21 XAMPP Server Control panel
The two services get started one by one
Figure: 3.22 XAMPP Server Services start option
Chapter 3 Introduction to Database Management System 75
The created database is listed using SHOW command. See Table 3. mysql> show databases;
Table 3.12 Database List Database employee personaldetails sports
● ● Create Table In an application, each page reveals some functionality. Each such functions are designed to a table. For example, in an online shopping site like Amazon, multiple pages are maintained like customer profile, products, orders in cart and payment page. All these can be created as tables like Customer, Products, Order and Payment respectively. ● ● Create Columns Each table will have many columns related to the functionality of the table. This column determines what values are stored in the table. For example, the Customer table contains the columns like firstname, lastname, phone, email, age, address and pincode. These columns hold the Customer information in the table. Each column is assigned with appropriate value type. The efficiency and performance of the table purely depends on the data types assigned to the columns.
● ● Insert Rows Once the database is created, tables and the columns with the appropriate value type are defined. Then records are inserted to the table.
3.10 Designing Databases
The process of creating, implementing and maintaining the enterprise data in a system is known as Designing of databases. Better understanding of the application is necessary before designing the database. The performance and success of an application depends on good database design. MySQL provides performance dashboard, reports and statistics regarding the design of database.
Create Conceptual Design
It is the primary phase in database design, where detailed discussion about the creation of databases, tables, columns and data types is discussed based on the requirement of the application. As an end result the model is framed to attain the expectation of the application’s end user.
● ● Create Database
The three major parts that forms a database are Tables, Queries and Views.
Tables - similar to an excel sheet, containing multiple rows and columns. Where each row is a record and each column is an attribute. Queries – It is a question with multiple conditions posted to the database. The records in the database that satisfies the passed conditions are retrieved. Views – A set of stored queries.
Example : create a database to store the personaldetails.
mysql> create database personaldetails;
Query Ok, 1 row affected
mysql> USE personaldetails;
Database changed.
76 Chapter 3 Introduction to Database Management System
Table: 3.13 SQL DDL COMMANDS List Commands Description
CREATE Used to create database or tables ALTER Modifies the existing structure of database or table DROP Deletes a database or table. RENAME used to rename an existing object in the database TRUNCATE Used to delete all table records
Data Manipulation Language (DML)
These SQL commands deals with the manipulation of data present in the database. Most of SQL commands come under DML. INSERT, UPDATE, and DELETE commands belong to this category. See Table 3.
Table: 3.14 SQL DML COMMANDS List Commands Description
INSERT Adds new rows into database table.
UPDATE modifies existing data with new data in a table.
DELETE Deletes the records from the table.
Data Query Language (DQL)
SELECT is the only SQL command used to fetch or retrieve the data from database tables that come under DQL. See Table 3.
Table: 3.15 SQL DQL COMMANDS List Commands Description
SELECT Retrieve data from the table.
INSERT INTO TABLE_NAME (column1, column2, column3, ... columnN) VALUES (value1, value2, value3, ...valueN);
3.11 SQL SQL- Structured Query Language is a standard language used for accessing and manipulating databases. It is declared as standard by American National Standards Institute (ANSI) and International Organization for Standardization (ISO) in 1986 and 1987 respectively.
About SQL
● ● Though SQL is standard language, different versions are maintained to meet out the requirements. Few major functions performed using SQL are listed below: ● ● Executes queries against a database. ● ● Retrieves data from database. ● ● Inserts and updates records in a database ● ● Delete records from database. ● ● Creates new databases and new tables in a database.
Types of SQL Commands
Different SQL commands are available to perform various functions. SQL commands are classified into five major categories depending on their functionality. See Table 3.
Data Definition Language (DDL)
The DDL commands are used to define database schema (Structure). Also to create and modify the structure of the database object in the database. CREATE, ALTER, DROP,RENAME and TRUNCATE commands belongs to this category.
