Unit - 1

Introduction

Q1) What is the purpose of a database system?

A1) It is a set of tools that allows a user to construct and manage databases. In other words, it is general-purpose software that enables users to define, construct, and manipulate databases for a variety of applications.

Database systems are made to handle massive amounts of data. Data management entails both the creation of structures for storing data and the provision of methods for manipulating data. Furthermore, despite system crashes or efforts at illegal access, the database system must preserve the security of the information stored. If data is to be shared across multiple users, the system must avoid any unexpected outcomes.

Fig: Process of transforming data

The figure above depicts the process of transforming data into information, knowledge, and action in a database management system.

The file system was used as the foundation for the database applications.

The goal of a database management system (DBMS) is to transform the following:

1. Data into information.

2. Information into knowledge.

3. Knowledge of the action.

Q2) Write the uses of DBMS?

A2) Uses of DBMS

The main uses of DBMS are as follows −

●      Data independence and efficient access of data.

●      Application Development time reduces.

●      Security and data integrity.

●      Uniform data administration.

●      Concurrent access and recovery from crashes.

Q3) Write the characteristics of DBMS?

A3) Characteristics of DBMS

●      It stores and manages information in a digital repository hosted on a server.

●      It can provide a logical and transparent picture of the data manipulation process.

●      Automatic backup and recovery mechanisms are included in the DBMS.

●      It has ACID qualities, which keep data healthy in the event of a failure.

●      It has the ability to simplify complex data relationships.

●      It's utilized to help with data manipulation and processing.

●      It is used to ensure data security.

●      It can examine the database from several perspectives depending on the user's needs.

Q4) What do you mean by view of data?

A4) View of data in DBMS narrate how the data is visualized at each level of data abstraction? Data abstraction allow developers to keep complex data structures away from the users. The developers achieve this by hiding the complex data structures through levels of abstraction.

There is one more feature that should be kept in mind i.e. the data independence. While changing the data schema at one level of the database must not modify the data schema at the next level. In this section, we will discuss the view of data in DBMS with data abstraction, data independence, data schema in detail.

Data Abstraction

Data abstraction is hiding the complex data structure in order to simplify the user’s interface of the system. It is done because many of the users interacting with the database system are not that much computer trained to understand the complex data structures of the database system.

To achieve data abstraction, we will discuss a Three-Schema architecture which abstracts the database at three levels discussed below:

Three-Schema Architecture:

The main objective of this architecture is to have an effective separation between the user interface and the physical database. So, the user never has to be concerned regarding the internal storage of the database and it has a simplified interaction with the database system.

The three-schema architecture defines the view of data at three levels:

1. Physical level (internal level)
2. Logical level (conceptual level)
3. View level (external level)

1. Physical Level/ Internal Level

The physical or the internal level schema describes how the data is stored in the hardware. It also describes how the data can be accessed. The physical level shows the data abstraction at the lowest level and it has complex data structures. Only the database administrator operates at this level.

2. Logical Level/ Conceptual Level

It is a level above the physical level. Here, the data is stored in the form of the entity set, entities, their data types, the relationship among the entity sets, user operations performed to retrieve or modify the data and certain constraints on the data. Well adding constraints to the view of data adds the security. As users are restricted to access some particular parts of the database.

It is the developer and database administrator who operates at the logical or the conceptual level.

3. View Level/ User level/ External level 

It is the highest level of data abstraction and exhibits only a part of the whole database. It exhibits the data in which the user is interested. The view level can describe many views of the same data. Here, the user retrieves the information using different application from the database.

The figure below describes the three-schema architecture of the database:

Fig - Three-schema architecture

In the figure above you can clearly distinguish between the three levels of abstraction. To understand it more clearly let us take an example:

We have to create a database of a college. Now, what entity sets would be involved? Student, Lecturer, Department, Course and so on…

Now, the entity sets Student, Lecturer, Department, Course will be stored in the storage as the consecutive blocks of the memory location. This is the physical or internal level and is hidden from the programmers but the database administrator is it aware of it.

At the logical level, the programmers define the entity sets and relationship among these entity sets using a programming language like SQL. So, the programmers work at the logical level and even the database administrator also operates at this level.

At the view level, the users have the set of applications which they use to retrieve the data they are interested in.

Q5) Define data model?

A5) Data Model is the modelling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction. Therefore, there are four data models used for understanding the structure of the database:

Fig – Data Models

1) Relational Data Model: This type of model designs the data in the form of rows and columns within a table. Thus, a relational model uses tables for representing data and in-between relationships. Tables are also called relations. This model was initially described by Edgar F. Codd, in 1969. The relational data model is the widely used model which is primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects and relationships among them. These objects are known as entities, and relationship is an association among these entities. This model was designed by Peter Chen and published in 1976 papers. It was widely used in database designing. A set of attributes describe the entities. For example, student_name, student_id describes the 'student' entity. A set of the same type of entities is known as an 'Entity set', and the set of the same type of relationships is known as 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions, encapsulation, and object identity, as well. This model supports a rich type system that includes structured and collection types. Thus, in 1980s, various database systems following the object-oriented approach were developed. Here, the objects are nothing but the data carrying its properties.

4) Semistructured Data Model: This type of data model is different from the other three data models (explained above). The semistructured data model allows the data specifications at places where the individual data items of the same type may have different attribute sets. The Extensible Markup Language, also known as XML, is widely used for representing semistructured data. Although XML was initially designed for including the markup information to the text document, it gains importance because of its application in the exchange of data.

Q6) What is DBMS?

A6) Database management system is software that is used to manage the database.

The database is a collection of interrelated 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.

Q7) Write the advantages of dbms?

A7) 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

Q8) Write the disadvantages of dbms?

A8) 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.

Q9) Explain three schema architectures of dbms?

A9) The architecture of a database management system influences its design. When dealing with a large number of PCs, web servers, database servers, and other networked elements, the same client/server architecture is used.

A client/server architecture is made up of several PCs and a workstation that are all linked by a network.

The design of a database management system is determined by how users link to the database in order to complete their requests.

Fig: Types of architecture

A single tier or multi-tier database architecture can be seen. However, database design can be divided into two categories: 2-tier architecture and 3-tier architecture.

1-tier Architecture:

The database is directly accessible to the user in this architecture. It means that the user can sit on the DBMS and use it directly. Any modifications made here will be applied directly to the database. It does not provide end users with a useful tool.

For the creation of local applications, a 1-tier architecture is used, in which programmers can interact directly with the database for fast responses.

Fig: 1-tier architecture

2-tier Architecture:

The 2-tier architecture is similar to the basic client-server architecture. Client-side applications can interact directly with the database on the server side in a two-tier architecture. APIs such as ODBC and JDBC are used for this interaction.

The client-side runs the user interfaces and application programs. The server side is in charge of providing features such as query processing and transaction management. The client-side application creates a link with the server side in order to communicate with the DBMS.

Fig: 2-tier architecture

3-tier Architecture:

Between the client and the server is another layer in the 3-tier architecture. The client cannot communicate directly with the server in this architecture. The client-side program communicates with an application server, which in turn communicates with the database system.

Beyond the application server, the end user is unaware of the database's presence. Aside from the submission, the database has no knowledge about any other users.

In the case of a wide web application, the 3-tier architecture is used.

Fig: 3-tier architecture

Q10) What are the components of dbms?

A10) You may split the database management system into five main components, which are

1. Hardware
2. Software
3. Data
4. Procedure
5. Database Access Language

In order to see how they all work together to form a database management system, let's have a basic diagram.

Fig: Component of DBMS

1. Hardware

When we say hardware, before any data is successfully stored in the memory, we mean computers, hard discs, I/O channels for data, and any other physical part involved.

When we run Oracle or MySQL on our personal computer, the hard disc of our computer, the keyboard we use to type all the commands, the RAM of our computer, the ROM, all become part of the hardware of the DBMS.

2.     Software

As this is the software that governs everything, this is the key part. More like a wrapper around the physical database, the DBMS programme gives us an easy-to-use interface for storing, accessing and upgrading data.

The DBMS programme is able to understand and translate the Database Access Language into actual database commands in order to execute them on the DB.

3.     Data

Data is the resource for which the DBMS is intended. The motive behind the creation of DBMS was to store and use information.

Data saved by the user is present in a standard database and meta data is stored.

Metadata is data about knowledge. To better understand the data contained in it, this is information stored by the DBMS.

4.     Procedure

Procedures refer to general instructions for using a method to handle a database. This includes procedures for setting up and installing a DBMS, logging in and logging out of the DBMS programme, database maintenance, backups, report creation, etc.

5.     Database Access language

Database Access Language is a basic language designed to access, attach, update and delete data stored in any database by writing commands.

In the Database Access Language, a user can write commands and send them to the DBMS for execution, which is then interpreted and executed by the DBMS.

The user can use the access language to build new databases, tables, insert data, fetch stored data, update data and remove data.

Q11) Write short notes on the ER model?

A11) The Entity-Relationship model (ER model) is a type of entity-relationship model. It's a data model at a high level. The data elements and relationships for a given system are described using this model.

●      It creates a database's conceptual architecture. It also creates a very convenient and easy-to-design data view.

●      The database structure is depicted as a diagram called an entity-relationship diagram in ER modeling.

Let's say we're creating a database for a school. The student will be an object in this database, with attributes such as address, name, id, age, and so on. There will be a relationship between the address and another entity with attributes such as city, street name, pin code, and so on.

Fig: Example of ER

Component of ER model -

The following components are composed of an ER Diagram:

1. Entity
2. Attributes
3. Relationships

Q12) What are attributes?

A12) In an Entity-Relationship Model, an attribute defines an entity's properties or characteristics. It is represented in the ER diagram by an oval or ellipse shape. Each oval shape represents one attribute and is directly linked to the person that is in the shape of the rectangle.

For instance, the attributes defining the Employee form of entity are employee id, employee name, gender, employee age, salary, and mobile no.

Fig: Different attributes of employee

In the ER model, the following categories can be defined by an attribute:

1.Simple attribute:

A simple attribute is called an attribute which contains an atomic value and can not be divided further. The gender and salary of a worker, for instance, is also depicted by an oval.

Fig: Simple attribute

2. Key attribute:

A key attribute is called an attribute that can uniquely identify an entity in an entity set. It represents a primary key in the ER diagram. In an Entity Relationship diagram, the key attribute is denoted by an oval with an underlying line. For example, for each employee, the employee id would be unique.

Fig: Key attributes

3. Composite attribute:

An attribute that is a combination of two or more basic attributes is called a composite attribute. It is defined by an ellipse in an Entity-Relationship diagram, and that ellipse consists of other ellipses. For example, an employee entity type's name attribute consists of first name, second name, and last name.

Fig: Composite attribute

4.Derived attribute:

A derived attribute is considered an attribute which can be derived from other attributes. In an entity-relationship diagram, a dashed oval shape is used to represent these attributes. Employee age is, for example, a derived attribute since it varies over time and can be derived from another DOB attribute (Date of birth).

Fig: Derived attribute

5.Multivalued attributes:

An attribute which for a given entity contains more than one value. For instance, there may be more than one mobile number and email address for an employee.

Fig: Multivalued attribute

Q13) What do you mean by relationship in the ER model?

A13) In the Entity-Relation Model, a relationship is used to define the relationship between two or more entities. In the ER diagram, it is illustrated by a diamond shape.

For example, in college student studies, employees work in a department. Here, the links are 'research in' and 'works in'.

Degree of Relationship

A partnership is called the degree of a relationship in which a variety of different individuals participate.

Degree of relationship can be categorized into the following types:

1. Unary Relationship:

A relationship in which a single group of individuals is involved is referred to as a unary relationship. For instance, in a company, an employee manages or supervises another employee.

Fig: Unary relationship

2. Binary Relationship: When a relationship includes two people, it is considered a binary relationship.

3. Ternary Relationship: When a relationship contains three individual sets, a ternary relationship is called.

Fig: Ternary relationship

4. N-nary Relationship:

If a relationship includes more than three entity sets, an n-ary relationship is named.

Q14) Explain types of entity?

A14) The following are the types of entities in DBMS −

Strong Entity

The strong entity has a primary key. Weak entities are dependent on strong entity. Its existence is not dependent on any other entity.

Strong Entity is represented by a single rectangle −

Continuing our previous example, Professor is a strong entity here, and the primary key is  Professor_ID.

Weak Entity

The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It mainly depends on other entities.

Weak Entity is represented by double rectangle −

Continuing our previous example, Professor is a strong entity, and the primary key is  Professor_ID. However, another entity is Professor_Dependents, which is our Weak Entity.

<Professor_Dependents>

This is a weak entity since its existence is dependent on another entity Professor, which we saw above. A Professor has Dependents.

Example of Strong and Weak Entity

The example of a strong and weak entity can be understood by the below figure.

Fig - Example

The Strong Entity is Professor, whereas Dependent is a Weak Entity.

ID is the primary key (represented with a line) and Name in Dependent entity is called Partial Key (represented with a dotted line).

Q15) What is the notation of an ER diagram?

A15) Database can be represented using the notations. In ER diagram, many notations are used to express the cardinality. These notations are as follows:

Fig: Notations of ER diagram

Q16) Write any example of ER model?

A16) Using notations, the database can be represented, and these notations can be reduced to a set of tables.

Each entity set or relationship set can be represented in tabular form in the database.

The ER diagram is given below:

Fig: ER diagram

There are some points for converting the ER diagram to the table:

●      Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

●      All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table. Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

●      A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key attribute of the entity.

●      The multivalued attribute is represented by a separate table.

A hobby is a multivalued feature inside the student table. Thus, multiple values cannot be expressed in a single column of the STUDENT table. We therefore generate a STUD HOBBY table with the STUDENT ID and HOBBY column names. We create a composite key by using both columns.

●      Composite attribute represented by components.

Student address is a composite attribute in the given ER diagram. CITY, PIN, DOOR#, Path, and STATE are included. These attributes will merge as an individual column in the STUDENT table.

●      Derived attributes are not considered in the table.

In the STUDENT table, the derived attribute is age. It can be determined by measuring the difference between the present date and the date of birth at any time.

You can convert the ER diagram to tables and columns using these rules and assign the mapping between the tables.

The following is the table structure for the given ER diagram:

Fig: Table structure