Chapter 3: Introduction to Database Management System
 |
| Introduction to Database Management System |
Part - II
Short Answers
1. Define Data Model and list the types of data model used.
A database model is a type of data model that determines the logical structure of a database and
fundamentally determines in which manner data can be stored, organized and manipulated.
2. List few disadvantages of file processing 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.
3. Define Single and multi valued attributes.
A single valued attribute contains only one value for the attribute and they don’t have multiple numbers
of values
A multi valued attribute has more than one value for that particular attribute
4. List any two DDL and DML commands with its Syntax.
DDL Statement: CREATE, ALTER, DROP,RENAME and TRUNCATE
Syntax1: CREATE database databasename;
Syntax2: DROP database databasename;
DML Statement: INSERT, UPDATE, and DELETE.
Syntax1: DELETE from tablename WHERE columnname=”value”;
Syntax2:INSERT INTO tablename (column1,column2, column3) VALUES (value1, value2, value3);
5. What are the ACID properties?
The acronym stands for Atomicity, Consistency, Isolation and Durability
6. Which command is used to make permanent changes done by a transaction?
Transaction Control Language - COMMIT command is used to make permanent changes done by a
transaction
7. What is view in SQL?
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more
real tables in the database.
Commands are: CREATE VIEW,REPLACE VIEW and DROP VIEW.
8. Write the difference between SQL and MySQL.
SQL MYSQL
Database Language A Software
Commands are fixed Is Updated on frequent basis
Query Language Use SQL Language to query the
Database
Closed Source Open Source
9. What is Relationship and List its types.
Relationship exists between two entities. Three types of relationships are available
1. One-to-One relationship(1:1) 2. One-to-Many relationship(1:N) 3. Many-to-Many
relationship(M:N)
10. State few advantages of Relational databases.
The features of RDBMS are
● High Availability ● High Performance ● Robust Transactions and support
● Ease of management ● Less cost
Part - III
Explain in Brief Answer
1. Explain on 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.
2. What is relationship in databases? List its types.
Relationship exists between two entities.
A situation that exists between two relational database tables when one table has a foreign key that
references the primary key of the other table.
Relationships allow relational databases to split and store data in different tables.
Three types of relationships are available
1. 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
2. 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
3. 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
3. Discuss on Cardinality in DBMS.
It is defined as the number of items that must be included in a relationship.
i.e number of entities in one set mapped with the number of entities of another set via the relationship.
Three classifications in Cardinality are one-to-one, one-to-many and Many-to-Many.
4. List any 5 privileges available in MySQL for the User.
Privileges Action Performed (If Granted)
Select_priv User can select rows from database tables.
Insert_priv User can insert rows into database tables
Update_priv User can update rows of database tables
Delete_priv User can delete rows of database tables
Create_priv User can create new tables in database
Alter_priv User can make changes to the database structure
5. Write few commands used by DBA to control the entire database.
1. USE Database – This command is used to select the database in MySQL for working.
Syntax: mysql > use test;
2. SHOW Databases – Lists all the databases available in the database server.
Syntax: mysql > show databases;
3. SHOW Tables – Lists all the tables available in the current database we are working in.
Syntax: mysql > show tables;
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.
Syntax: mysql > show columns from Table;
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.
Part - IV
Explain in detail
1. Discuss on various database models available in DBMS.
The database technology came into existence in terms of models with relational and object-relational
behavior.
The major database models are listed below:
Hierarchical Database Model
The famous Hierarchical database model was IMS (Information Management System), IBM’s first
DBMS.
In this model each record has information in parent/ child relationship like a tree structure.
The collection of records was called as record types, which are equivalent to tables in relational model.
Advantages: less redundant data, efficient search, data integrity and security.
Disadvantages: complex to implement and difficulty in handling many to many relationships.
Network model
The first developed network data model was IDS (Integrated Data Store) at Honeywell.
Network model is similar to Hierarchical model except that in this model each member can have more
than one owner.
The many to many relationships are handled in a better way.
This model identified the three database components Network schema, Sub schema and Language for
data management.
Advantages: the ability to handle more relationship types, easy data access, data integrity and
independence.
Disadvantages: difficulty in design and maintenance.
Relational model
Oracle and DB2 are few commercial relational models in use.
A relation (table) consists of unique attributes (columns) and tuples (rows).
Relational model is defined with two terminologies Instance and Schema.
Instance – A table consisting of rows and columns
Schema – Specifies the structure including name and type of each column.
Object-oriented database model
This model incorporates the combination of Object Oriented Programming(OOP’s) concepts and
database technologies
Its serves as the base of Relational model.
Its uses small, reusable software known as Objects.
Advantages: Manages large number of different data types.
2. List the basic concepts of ER Model with suitable example.
The basic concepts of ER model consists of
1. Entity or Entity type 2. Attributes 3. Relationship
Entity or Entity type
An Entity can be anything a real-world object or animation which is easily identifiable by anyone even
by a common man.
Types of Entity: 1. Strong Entity 2. Weak Entity 3. Entity Instance
Strong Entity: A Strong entity is the one which doesn’t depend on any other entity on the schema or
database and a strong entity will have a primary key.
Weak Entity: A weak entity is dependent on other entities and it doesn’t have any primary key like
the Strong entity. It is represented by double rectangle.
Entity Instance: Instances are the values for the entity if we consider animals as the entity their
instances will be dog, cat, cow… Etc.
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
Relationship:
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.
1. One-to-One relationship 2. One-to-Many relationship 3. Many-to-Many relationship
3. Discuss in detail on various types of attributes in DBMS.
Types of attributes:
1. Key Attribute 2. Simple Attributes
3. Composite Attributes 4. Single Valued Attribute
5. Multi Valued Attribute
Key Attribute
Generally a key attribute describes a unique characteristic of an entity.
Simple Attribute
The simple attributes cannot be separated it will be having a single value for their entity.
Eg: Name as the attribute for the entity employee (single Value)
Composite Attributes
The composite attributes can be subdivided into simple attributes without change in the meaning of
that attribute.
Eg: attribute Name which are sub-divided into two simple attributes first and last name.
Single Valued Attributes:
A single valued attribute contains only one value for the attribute and they don’t have multiple numbers
of values.
Eg:Age- It is a single value for a person as we cannot give n number of ages
Multi Valued Attributes:
A multi valued attribute has more than one value for that particular attribute.
Eg:Degree - A person can hold n number of degrees
4. Write a note on open source software tools available in MySQL Administration.
Types of software tools
Many open source tools are available in the market to design the database in a better and efficient
manner. PhpMyAdmin is most popular for Web Administration. The popular Desktop Application tools
are MySQL Workbench and HeidiSQL.
PHPMYADMIN (Web Admin)
This administrative tool of MySQL is a web application written in PHP. They are used predominantly in
web hosting. The main feature is providing web interface, importing data from CSV and exporting data
to various formats. It generates live charts for monitoring MySQL server
activities like connections, processes and memory usage. It also helps in making the complex queries
easier.
MySQL Workbench (Desktop Application)
It is a database tool used by developers and DBA’s mainly for visualization. This tool helps in data
modeling, development of SQL, server configuration and backup for MySQL in a better way. Its basic
release version is 5.0 and is now in 8.0 supporting all Operating Systems. The SQL editor of this tool is
very flexible and comfortable in dealing multiple results set.
HeidiSQL (Desktop Application)
This open source tools helps in the administration of better database systems. It supports GUI
(Graphical User Interface) features for monitoring server host, server connection, Databases, Tables,
Views, Triggers and Events.
5. Explain in detail on Sub Queries with suitable examples.
Sub queries
Here the SQL query is written within a main Query. This is called as Nested Inner/ SubQuery.
The sub query is executed first and the results of sub query are used as the condition for main query.
The sub query must follow the below rules:
1. Subqueries are always written within the parentheses.
2. Always place the Subquery on the right side of the comparison operator.
3. ORDER BY clause is not used in sub query, since Subqueries cannot manipulate the
results internally.
Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with expression
operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.
Syntax:
SELECT column_name FROM table_name
WHERE column_name expression operator
(SELECT COLUMN_NAME FROM TABLE_NAME WHERE ... );
Eg: SELECT * FROM Employee WHERE EmpID IN
(SELECT EmpID FROM Employee WHERE Salary < 20000);
Book Question
1.What are Concept of Network model?
Network schema – schema defines all about the structure of the database.
Sub schema – controls on views of the database for the user
Language – basic procedural for accessing the database.
2. Different between Strong and Weak Entity
Strong Entity Set Weak Entity Set
Strong entity set always has a primary key. It does not have enough attributes to build a
primary key.
It is represented by a rectangle symbol. It is represented by a double rectangle symbol.
It contains a Primary key represented by the
underline symbol.
It contains a Partial Key which is represented by a
dashed underline symbol.
The member of a strong entity set is called as
dominant entity set.
The member of a weak entity set called as a
subordinate entity set.
Primary Key is one of its attributes which
helps to identify its member.
In a weak entity set, it is a combination of primary
key and partial key of the strong entity set.
In the ER diagram the relationship between
two strong entity set shown by using a
diamond symbol.
The relationship between one strong and a weak
entity set shown by using the double diamond
symbol.
The connecting line of the strong entity set
with the relationship is single.
The line connecting the weak entity set for
identifying relationship is double.
0 Comments