Unit – 1
General introduction to database systems
Q1) Give a brief introduction of the database?
A1) Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.
Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum.
In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable.
What is Database?
A database is an organized collection of data, so that it can be easily accessed and managed.
You can organize data into tables, rows, columns, and index it to make it easier to find relevant information.
Database handlers create a database in such a way that only one set of software program provides access of data to all the users.
The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.
There are many dynamic websites on the World Wide Web nowadays which are handled through databases. For example, a model that checks the availability of rooms in a hotel. It is an example of a dynamic website that uses a database.
There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.
Modern databases are managed by the database management system (DBMS).
SQL or Structured Query Language is used to operate on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.
A cylindrical structure is used to display the image of a database.
Fig 1- Image of a database
Q2) What is three schema architecture?
A2) The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks.
The client/server architecture consists of many PCs and a workstation which are connected via the network.
DBMS architecture depends upon how users are connected to the database to get their request done.
Types of DBMS Architecture
Fig 2 - DBMS Architecture
Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two types like: 2-tier architecture and 3-tier architecture.
1-Tier Architecture
● In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
● Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
● The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response.
2-Tier Architecture
● The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API's like: ODBC, JDBC are used.
● The user interfaces and application programs are run on the client-side.
● The server side is responsible to provide the functionalities like: query processing and transaction management.
● To communicate with the DBMS, client-side application establishes a connection with the server side.
Fig 3: 2-tier Architecture
3-Tier Architecture
● The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.
● The application on the client-end interacts with an application server which further communicates with the database system.
● End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
● The 3-Tier architecture is used in case of large web application.
Fig 4: 3-tier Architecture
Q3) What is DBMS and what are its characteristics?
A3) Database management system is software that is used to manage the database.
What is 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.
Q4) What are the advantages and disadvantages of DBMS?
A4) 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
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.
Q5) What is the purpose of the database system?
A5) 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 5: 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.
Q6) What is DBMS architecture and what are its different types?
A6) You may split the database management system into five main components, which are
- Hardware
- Software
- Data
- Procedure
- 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 6: 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.
Q7) Define entity relationship model?
A7) Entity-relationship model
- The logical structure of a database can be expressed by an E-R diagram.
- Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.
- Using relationships, various entities are related.
- To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.
The Main Components of ER Diagram are: -
a) Rectangles - Entity Sets
b) Ellipses - Attributes
c) Diamond - Relation among entity sets
d) Lines - Connects attributes to entity sets and entity sets to relationships.
Fig 7: E-R relationship
The above E-R diagram has two entities: Teacher and Department
Teacher entity has three attributes:
Teacher_id
Teacher_name
Teacher_Subject
Department entity has two attributes: -
Dept_id
Dept_name
Relation Teaches in gives relationship between Teacher, Department.
Relationship can be of the type:
1:1 → One to one
1:M → One to many
M:1 → Many to one
M:M → Many to many
Q8) Explain network model?
A8) Network model
- Network model uses two different data structures: -
a) A record type is used to represent as entity set.
b) A set type is used to represent a directed relationship between two record types.
- This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.
- Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.
- This was the most commonly used database architecture prior to the advent of the Relational Model.
Fig 8: Network model
Q9) Write about relational model?
A9) Relational model
- Relational model uses a collection of tables to represent both data and the relationships among those data.
- This model is useful for constructing a database that can then be converted into relational model tables.
- Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.
- In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.
Therefore, in the relational model, tables are also known as relations.
Sample relational database
A. Teacher Table
Teacher_id | Teacher_name | Teacher_Subject |
1001 | Pritisha | C++ |
1002 | Akon | DBMS |
1003 | Rahul | SE |
1004 | Raj | Data structure |
B. Department Table
Dept_id | Dept_name |
A101 | CSE |
B102 | Electronics |
C103 | Civil |
D104 | Mechanical |
C. Teaches in table
Teacher_id | Dept_id |
1001 | A101 |
1002 | B102 |
1003 | C103 |
1004 | D104 |
Q10) Describe an object oriented data model?
A10) Object oriented data model
The object-oriented database derivation is the integrity of object-oriented
Programming language systems and consistent systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.
Fig 9: Object-oriented model
Object-oriented databases use small, recyclable separated of software called objects. The objects themselves are stored in the object-oriented database. Each object contains of two elements:
1. Piece of data (e.g., sound, video, text, or graphics).
2. Instructions, or software programs called methods, for what to do with
The data.
There are two types of ORM: -
a. Object-Oriented Data Model (OODM)
b. Object-Relational Data Model (ORDM)
These models are used to handle objects:
1) To provide support for complex objects.
2) To provide user extensibility for data types operators and access methods.
3) There should be mechanism to support:
a) Abstract data type.
b) Data of type ‘procedure’.
c) Rules.
Q11) What are the challenges in building a dbms?
A11) Conflicting goals, such as adherence to design standards (design elegance), processing speed, and information requirements, force database designers to make design tradeoffs.
Design standards
Design guidelines must be followed when creating a database. As a result of these standards, you've been able to create logical structures that reduce data redundancy and, as a result, the chance of destructive data anomalies. You've also learned how to avoid nulls as much as feasible according to standards. In a nutshell, design standards allow you to work with well-defined components and evaluate their interactions with precision. It is practically impossible to establish a suitable design process, analyse an existing design, or trace the expected logical consequence of design modifications without design standards.
Processing speed
High processing speeds are often a major priority in database architecture in many organisations, particularly those that generate huge quantities of transactions. Minimal access time is achieved by reducing the quantity and complexity of logically desirable relationships, which can be achieved by increasing processing speed. For example, a "ideal" design might eliminate nulls by utilising a 1:1 relationship, whereas a better transaction-speed design might combine the two tables to minimise the need for an additional relationship and avoid nulls by using dummy entries. If data retrieval speed is a priority, you may be required to include derived attributes in the design.
Information requirements
The desire of real-time data could be the goal of database design. Complex data requirements may necessitate data transformations, as well as an increase in the number of entities and characteristics in the design. To ensure maximal information creation, the database may have to trade some of its "clean" design structures and/or part of its high transaction performance.
An important goal is to create a design that fits all logical requirements and design norms. However, if this flawless design fails to match the customer's transaction speed and/or information needs, the designer will not have done a good job in the eyes of the end user. In the real world of database design, compromises are unavoidable.
Even as the designer concentrates on the entities, characteristics, relationships, and constraints, end-user requirements like as performance, security, shared access, and data integrity should be considered. The designer must take into account processing needs and ensure that all update, retrieval, and deletion options are available. Finally, a design is worthless unless the finished product can meet all of the required query and reporting criteria.
Last but not least, document, document, document! Make a list of all design activities. Then go over what you've written again. Documentation not only keeps you on track during the design process, but it also allows you (or anyone who follows you) to pick up where you left off when it's time to make changes. Despite the obvious need for documentation, one of the most frustrating aspects of database and systems analysis work is that the "put it in writing" criterion is not always followed throughout the design and implementation stages. The creation of organisational documentation standards is a critical component of assuring data compatibility and consistency.
Q12) Write difference between Database and DBMS
A12) Difference between Database and DBMS:
S. No. | Category | Database | DBMS |
1. | Storage | Databases can be kept in physical ledgers, books, or papers in addition to computers. | All of the records in a database management system (DBMS) are kept on a computer. |
2. | Data Retrieval | Information from databases can be retrieved directly, through queries, or using programmes (C, C++, Java, and so on). | Queries made in SQL can be used to get data from the database management system. |
3. | Speed | As databases can be handled manually or via computers, when SQL is not used to retrieve information, it can be very slow. | The retrieval of information is particularly swift when a computer system is involved in a database management system. |
4. | Access | The databases are not intended for a big number of individuals to access data at the same time, but rather for a limited number of people (ideally a few) to access data at different times. | The database management system is intended for a large number of users to have simultaneous access to the data. |
5. | Data Manipulation | In the case of databases, only a small amount of data can be changed at a time. | A database management system (DBMS) allows a large amount of data to be altered at once (as it can have many users using it at the same time). |
6. | Backup and Recovery | The databases do not guarantee that the data will be available in the event of a breakdown. | Even if the system fails, the database management system (DBMS) ensures that the data is always available. |
Q13) What are the levels of abstraction for DBMS?
A13)
Levels of abstraction for DBMS
Complex data structures are present in database systems. Reduce complexity in terms of user usability when retrieving data, and developers utilize levels of abstraction to hide extraneous elements from users to make the system efficient. The use of abstraction levels makes database design easier.
The following are the three levels of abstraction for database management systems:
● Physical or Internal Level
● Logical or Conceptual Level
● View or External Level
Fig 10: Three levels of abstraction
Physical or Internal Level
It is the lowest level of abstraction for DBMSs, defining how data is actually stored, data-structures for storing data, and database access mechanisms. Developers or database application programmers, in fact, decide how to store data in the database.
So, in general, the complete database is specified at the physical or internal level. It is a difficult level to comprehend. Customer information, for example, is stored in tables, and data is stored in blocks of storage such as bytes, gigabytes, and so on.
Logical or Conceptual Level
The logical level is the next higher level or intermediate level. It explains what data is stored in the database and how those data are related to one another. It seeks to explain the full or entire data by describing what tables should be constructed and what the linkages between those tables should be.
It's not as complicated as the physical level. Developers and database administrators use the logical level (DBA). Tables (fields and attributes) and relationships among table attributes make up the logical level.
View or External Level
It is the most advanced level. There are various tiers of views at the view level, and each view only defines a portion of the whole data. It also facilitates user engagement by providing a variety of views or numerous views of the same database.
All users (all tiers' users) can use the view level. This level is the simplest and most straightforward.
For example, a user can interact with a system using a view-level GUI and enter details at the GUI or screen, but the user has no idea how or what data is kept because that information is hidden from the user.
Q14) Explain data independence.
A14)
In addition to users' data, a database system usually includes a lot of data. For instance, to easily locate and retrieve data, it stores information about data, known as metadata. Modifying or modifying a collection of metadata once it is placed in the database is very difficult. But when a DBMS expands, in order to fulfill the users' requirements, it needs to evolve over time. It will become a boring and highly complicated job if the entire data is based.
Metadata itself follows a layered model, such that it does not impact the data at another level when we modify data on one layer. This data is independent, but is mapped to one another.
Types of data independences
● Physical data independence
Physical Data Independence is defined as the ability to make adjustments without affecting the higher-level schemas in the structure of the lowest level of the Database Management System (DBMS). Therefore, the Physical level adjustment does not result in any adjustments to the Conceptual or View levels.
● Logical data independence
Logical Data Independence is defined as the ability to make improvements to the Database Management System (DBMS) middle level structure without affecting the schema or application programmes at the highest level. Changes to the conceptual level should also not result in any changes to the view levels or programmes of the application.
Fig 11: Data independence
Q15) What are the Component of ER model?
A15)
Component of ER model -
The following components are composed of an ER Diagram:
- Entity
- Attributes
- Relationships
Fig 12: Component of ER model
1.Entity
The object, location, person, or event that stores data in the database may be an entity. In an object-relationship diagram, a rectangle represents an entity.
Examples of an individual include a student, course, boss, employee, patient, etc.
Fig 13: Entity
Entity type:
A list or a set of entities having certain common attributes is an entity type. In a database, a name and a list of attributes define each type of entity.
Entity set:
It is a set (or collection) of entities of the same kind that share attributes or related properties.
For example, it is possible to describe the category of individuals who are lecturers at a university as an entity-set lecturer. Similarly, the collection of students of the organization could represent the community of all university students.
2.Attribute
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 14: 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 15: 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 16: 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 17: 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 18: 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 19: Multivalued attribute
3.Relationship
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 20: 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 21: Ternary relationship
4. N-nary Relationship:
If a relationship includes more than three entity sets, an n-ary relationship is named.
Q16) What is SQL? Write the characteristics of SQL.
A16)
Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database. SQL comes in different versions and forms. All of these versions are based upon the ANSI SQL.
Currently, MySQL is the most common programme for database management systems used for relational database management. It is an open-source, Oracle Company - supported database programme. In comparison to Microsoft SQL Server and Oracle Database, the database management system is fast, scalable, and easy to use. It is widely used for building efficient and interactive server-side or web-based business applications in combination with PHP scripts.
Characteristics of SQL
● It is quick to learn SQL.
● For accessing data from relational database management systems, SQL is used.
● SQL will perform database queries against it.
● To describe data, SQL is used.
● SQL is used in the database to describe the data and manipulate it when appropriate.
● To build and drop the database and the table, SQL is used.
● SQL is used in a database to construct a view, a stored procedure, a function.
● SQL allows users to set tables, procedures, and display permissions.
Q17) Write Advantages of SQL.
A17)
Advantages of SQL
● SQL is easy to learn and use.
● SQL is a non-procedural language. It means we have to specify only what to retrieve and not the procedure for retrieving data.
● SQL can be embedded within other languages using SQL modules, pre-compilers.
● Allows creating procedure, functions, and views using PL-SQL.
● Allows users to drop databases, tables, views, procedure and cursors.
● Allows users to set access permissions on tables, views and procedures.
Q18) Explain SQL Data types.
A18)
In order to describe the values that a column can hold, SQL Datatype is used.
Each column is needed in the database table to have a name and data type.
Fig 22: SQL data types
- Binary data types
Three kinds of binary data types are given below:
Data type | Description |
Binary | It has a fixed byte length of 8000. It includes binary data of fixed-length. |
Varbinary | It has a fixed byte length of 8000. It includes binary data of variable-length. |
Image | It has a maximum of 2,147,483,647 bytes in length. It includes binary data of variable-length. |
2. Numeric data types
The subtypes are given below:
Data type | From | To | Description |
Float | -1.79E + 308 | 1.79E + 308 | Used to specify a floating-point value |
Real | -3.40e + 38 | 3.40E + 38 | Specifies a single precision floating point number |
3. Extract numeric data types
The subtypes are given below:
Data types | Description |
Int | Used to specify an integer value. |
Smallint | Used to specify small integer value |
Bit | Number of bits to store. |
Decimal | Numeric value that can have a decimal number |
Numeric | Used to specify a numeric value |
4. Character String data types
Data types | Description |
Char | It contains Fixed-length (max - 8000 character) |
Varchar | It contains variable-length (max - 8000 character) |
Text | It contains variable-length (max - 2,147,483,647 character) |
5. Date and Time data types
Data types | Description |
Date | Used to store the year, month, and days value. |
Time | Used to store the hour, minute, and second values. |
Timestamp | Stores the year, month, day, hour, minute, and the second value. |
Q19) Explain about SQL commands.
A19)
The Structured Query Language (SQL), as we all know, is a database language that allows one to perform certain operations on existing databases as well as build new databases. To complete the tasks, SQL employs commands such as Create, Drop, and Insert.
These SQL commands are primarily divided into four groups:
● DDL (Data Definition Language)
● DML (Data Manipulation Language)
● DQL (Data Query Language)
● DCL (Data Control Language)
● TCL (Transactional control commands)
DDL:
DDL stands for Data Definition Language, and it is a part of SQL that allows a database user to build and restructure database objects, such as tables.
The following are some of the most basic DDL commands that will be addressed in the coming hours:
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX
CREATE VIEW
DROP VIEW
DML:
DML, or Data Manipulation Language, is a part of SQL that allows you to manipulate data within relational database objects.
DML commands are divided into three categories:
INSERT
UPDATE
DELETE
DQL:
Data Query Language (DQL) is the most focused subject of SQL for modern relational database users, despite having only one instruction.
The following is the base command:
SELECT
A query is a request for information from a database. A query to the database is normally sent to it through an application interface or a command line prompt.
DCL:
In SQL, you can use data control commands to limit who has access to data in the database. These DCL commands are typically used to build objects related to user access and to manage privilege distribution among users.
The following are some data access commands:
ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM
TCL:
Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands. Since these operations are automatically committed to the database, they can't be used to create or drop tables.
Here are some examples of TCL commands:
COMMIT Saves database transactions
ROLLBACK Undoes database transactions
SAVEPOINT Creates points within groups of transactions in which to ROLLBACK
SET TRANSACTION Places a name on a transaction
Q20) Explain different operators in SQL?
A20)
An operator is a reserved word or character that is used in the WHERE clause of a SQL statement to perform operations like comparisons and arithmetic operations. These Operators are used to define conditions in SQL statements and to function as conjunctions for multiple conditions in a single statement.
● Arithmetic operators
● Comparison operators
● Logical operators
● Operators used to negate conditions
Arithmetic operator
Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.
Operator | Description | Example |
+ (Addition) | Values are added to both sides of the operator. | a + b will give 30 |
- (Subtraction) | Takes the right hand operand and subtracts it from the left hand operand. | a - b will give -10 |
* (Multiplication) | Values on both sides of the operator are multiplied. | a * b will give 200 |
/ (Division) | Divides the left and right hand operands. | b / a will give 2 |
% (Modulus) | Returns the remainder after dividing the left hand operand by the right hand operand. | b % a will give 0 |
Comparison operator:
Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.
Operator | Description | Example |
= | Checks if the values of two operands are equal, and if they are, the condition is valid. | (a = b) is not true. |
!= | Checks if the values of two operands are equal; if they aren't, the condition is valid. | (a != b) is true. |
<> | Checks if the values of two operands are equal; if they aren't, the condition is valid. | (a <> b) is true. |
> | If the left operand's value is greater than the right operand's value, then the condition is valid. | (a > b) is not true. |
< | Checks if the left operand's value is less than the right operand's value; if it is, the condition is valid. | (a < b) is true. |
>= | If the left operand's value is greater than or equal to the right operand's value, then the condition is valid. | (a >= b) is not true. |
<= | If the left operand's value is less than or equal to the right operand's value, then the condition is valid. | (a <= b) is true. |
!< | If the value of the left operand is greater than the value of the right operand, the condition is valid. | (a !< b) is false. |
!> | If the value of the left operand is less than the value of the right operand, then the condition is valid. | (a !> b) is true. |
Logical operator:
Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.
Sr.No | Operator & Description |
1 | ALL When a value is compared to all of the values in another value set, the ALL operator is used. |
2 | AND The AND operator allows multiple conditions to occur in the WHERE clause of a SQL statement. |
3 | ANY The ANY operator compares a value to any valid value in the list according to the condition. |
4 | BETWEEN The BETWEEN operator is used to find values that are between the minimum and maximum values in a set of values. |
5 | EXISTS The EXISTS operator is used to look for a row in a given table that meets a certain set of criteria. |
6 | IN When a value is compared to a list of literal values that have been defined, the IN operator is used. |
7 | LIKE The LIKE operator compares a value to other values that are identical using wildcard operators. |
8 | NOT The NOT operator flips the definition of the logical operator it's used with. For example, NOT EXISTS, NOT BETWEEN, NOT IN, and so on. It's a negation operator. |
9 | OR The OR operator is used in the WHERE clause of a SQL statement to combine several conditions. |
10 | IS NULL When a value is compared to a NULL value, the NULL operator is used. |
11 | UNIQUE The UNIQUE operator looks for uniqueness in every row of a table (no duplicates). |
Unit – 1
General introduction to database systems
Q1) Give a brief introduction of the database?
A1) Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.
Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum.
In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable.
What is Database?
A database is an organized collection of data, so that it can be easily accessed and managed.
You can organize data into tables, rows, columns, and index it to make it easier to find relevant information.
Database handlers create a database in such a way that only one set of software program provides access of data to all the users.
The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.
There are many dynamic websites on the World Wide Web nowadays which are handled through databases. For example, a model that checks the availability of rooms in a hotel. It is an example of a dynamic website that uses a database.
There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.
Modern databases are managed by the database management system (DBMS).
SQL or Structured Query Language is used to operate on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.
A cylindrical structure is used to display the image of a database.
Fig 1- Image of a database
Q2) What is three schema architecture?
A2) The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks.
The client/server architecture consists of many PCs and a workstation which are connected via the network.
DBMS architecture depends upon how users are connected to the database to get their request done.
Types of DBMS Architecture
Fig 2 - DBMS Architecture
Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two types like: 2-tier architecture and 3-tier architecture.
1-Tier Architecture
● In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
● Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
● The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response.
2-Tier Architecture
● The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API's like: ODBC, JDBC are used.
● The user interfaces and application programs are run on the client-side.
● The server side is responsible to provide the functionalities like: query processing and transaction management.
● To communicate with the DBMS, client-side application establishes a connection with the server side.
Fig 3: 2-tier Architecture
3-Tier Architecture
● The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.
● The application on the client-end interacts with an application server which further communicates with the database system.
● End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
● The 3-Tier architecture is used in case of large web application.
Fig 4: 3-tier Architecture
Q3) What is DBMS and what are its characteristics?
A3) Database management system is software that is used to manage the database.
What is 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.
Q4) What are the advantages and disadvantages of DBMS?
A4) 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
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.
Q5) What is the purpose of the database system?
A5) 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 5: 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.
Q6) What is DBMS architecture and what are its different types?
A6) You may split the database management system into five main components, which are
- Hardware
- Software
- Data
- Procedure
- 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 6: 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.
Q7) Define entity relationship model?
A7) Entity-relationship model
- The logical structure of a database can be expressed by an E-R diagram.
- Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.
- Using relationships, various entities are related.
- To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.
The Main Components of ER Diagram are: -
a) Rectangles - Entity Sets
b) Ellipses - Attributes
c) Diamond - Relation among entity sets
d) Lines - Connects attributes to entity sets and entity sets to relationships.
Fig 7: E-R relationship
The above E-R diagram has two entities: Teacher and Department
Teacher entity has three attributes:
Teacher_id
Teacher_name
Teacher_Subject
Department entity has two attributes: -
Dept_id
Dept_name
Relation Teaches in gives relationship between Teacher, Department.
Relationship can be of the type:
1:1 → One to one
1:M → One to many
M:1 → Many to one
M:M → Many to many
Q8) Explain network model?
A8) Network model
- Network model uses two different data structures: -
a) A record type is used to represent as entity set.
b) A set type is used to represent a directed relationship between two record types.
- This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.
- Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.
- This was the most commonly used database architecture prior to the advent of the Relational Model.
Fig 8: Network model
Q9) Write about relational model?
A9) Relational model
- Relational model uses a collection of tables to represent both data and the relationships among those data.
- This model is useful for constructing a database that can then be converted into relational model tables.
- Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.
- In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.
Therefore, in the relational model, tables are also known as relations.
Sample relational database
A. Teacher Table
Teacher_id | Teacher_name | Teacher_Subject |
1001 | Pritisha | C++ |
1002 | Akon | DBMS |
1003 | Rahul | SE |
1004 | Raj | Data structure |
B. Department Table
Dept_id | Dept_name |
A101 | CSE |
B102 | Electronics |
C103 | Civil |
D104 | Mechanical |
C. Teaches in table
Teacher_id | Dept_id |
1001 | A101 |
1002 | B102 |
1003 | C103 |
1004 | D104 |
Q10) Describe an object oriented data model?
A10) Object oriented data model
The object-oriented database derivation is the integrity of object-oriented
Programming language systems and consistent systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.
Fig 9: Object-oriented model
Object-oriented databases use small, recyclable separated of software called objects. The objects themselves are stored in the object-oriented database. Each object contains of two elements:
1. Piece of data (e.g., sound, video, text, or graphics).
2. Instructions, or software programs called methods, for what to do with
The data.
There are two types of ORM: -
a. Object-Oriented Data Model (OODM)
b. Object-Relational Data Model (ORDM)
These models are used to handle objects:
1) To provide support for complex objects.
2) To provide user extensibility for data types operators and access methods.
3) There should be mechanism to support:
a) Abstract data type.
b) Data of type ‘procedure’.
c) Rules.
Q11) What are the challenges in building a dbms?
A11) Conflicting goals, such as adherence to design standards (design elegance), processing speed, and information requirements, force database designers to make design tradeoffs.
Design standards
Design guidelines must be followed when creating a database. As a result of these standards, you've been able to create logical structures that reduce data redundancy and, as a result, the chance of destructive data anomalies. You've also learned how to avoid nulls as much as feasible according to standards. In a nutshell, design standards allow you to work with well-defined components and evaluate their interactions with precision. It is practically impossible to establish a suitable design process, analyse an existing design, or trace the expected logical consequence of design modifications without design standards.
Processing speed
High processing speeds are often a major priority in database architecture in many organisations, particularly those that generate huge quantities of transactions. Minimal access time is achieved by reducing the quantity and complexity of logically desirable relationships, which can be achieved by increasing processing speed. For example, a "ideal" design might eliminate nulls by utilising a 1:1 relationship, whereas a better transaction-speed design might combine the two tables to minimise the need for an additional relationship and avoid nulls by using dummy entries. If data retrieval speed is a priority, you may be required to include derived attributes in the design.
Information requirements
The desire of real-time data could be the goal of database design. Complex data requirements may necessitate data transformations, as well as an increase in the number of entities and characteristics in the design. To ensure maximal information creation, the database may have to trade some of its "clean" design structures and/or part of its high transaction performance.
An important goal is to create a design that fits all logical requirements and design norms. However, if this flawless design fails to match the customer's transaction speed and/or information needs, the designer will not have done a good job in the eyes of the end user. In the real world of database design, compromises are unavoidable.
Even as the designer concentrates on the entities, characteristics, relationships, and constraints, end-user requirements like as performance, security, shared access, and data integrity should be considered. The designer must take into account processing needs and ensure that all update, retrieval, and deletion options are available. Finally, a design is worthless unless the finished product can meet all of the required query and reporting criteria.
Last but not least, document, document, document! Make a list of all design activities. Then go over what you've written again. Documentation not only keeps you on track during the design process, but it also allows you (or anyone who follows you) to pick up where you left off when it's time to make changes. Despite the obvious need for documentation, one of the most frustrating aspects of database and systems analysis work is that the "put it in writing" criterion is not always followed throughout the design and implementation stages. The creation of organisational documentation standards is a critical component of assuring data compatibility and consistency.
Q12) Write difference between Database and DBMS
A12) Difference between Database and DBMS:
S. No. | Category | Database | DBMS |
1. | Storage | Databases can be kept in physical ledgers, books, or papers in addition to computers. | All of the records in a database management system (DBMS) are kept on a computer. |
2. | Data Retrieval | Information from databases can be retrieved directly, through queries, or using programmes (C, C++, Java, and so on). | Queries made in SQL can be used to get data from the database management system. |
3. | Speed | As databases can be handled manually or via computers, when SQL is not used to retrieve information, it can be very slow. | The retrieval of information is particularly swift when a computer system is involved in a database management system. |
4. | Access | The databases are not intended for a big number of individuals to access data at the same time, but rather for a limited number of people (ideally a few) to access data at different times. | The database management system is intended for a large number of users to have simultaneous access to the data. |
5. | Data Manipulation | In the case of databases, only a small amount of data can be changed at a time. | A database management system (DBMS) allows a large amount of data to be altered at once (as it can have many users using it at the same time). |
6. | Backup and Recovery | The databases do not guarantee that the data will be available in the event of a breakdown. | Even if the system fails, the database management system (DBMS) ensures that the data is always available. |
Q13) What are the levels of abstraction for DBMS?
A13)
Levels of abstraction for DBMS
Complex data structures are present in database systems. Reduce complexity in terms of user usability when retrieving data, and developers utilize levels of abstraction to hide extraneous elements from users to make the system efficient. The use of abstraction levels makes database design easier.
The following are the three levels of abstraction for database management systems:
● Physical or Internal Level
● Logical or Conceptual Level
● View or External Level
Fig 10: Three levels of abstraction
Physical or Internal Level
It is the lowest level of abstraction for DBMSs, defining how data is actually stored, data-structures for storing data, and database access mechanisms. Developers or database application programmers, in fact, decide how to store data in the database.
So, in general, the complete database is specified at the physical or internal level. It is a difficult level to comprehend. Customer information, for example, is stored in tables, and data is stored in blocks of storage such as bytes, gigabytes, and so on.
Logical or Conceptual Level
The logical level is the next higher level or intermediate level. It explains what data is stored in the database and how those data are related to one another. It seeks to explain the full or entire data by describing what tables should be constructed and what the linkages between those tables should be.
It's not as complicated as the physical level. Developers and database administrators use the logical level (DBA). Tables (fields and attributes) and relationships among table attributes make up the logical level.
View or External Level
It is the most advanced level. There are various tiers of views at the view level, and each view only defines a portion of the whole data. It also facilitates user engagement by providing a variety of views or numerous views of the same database.
All users (all tiers' users) can use the view level. This level is the simplest and most straightforward.
For example, a user can interact with a system using a view-level GUI and enter details at the GUI or screen, but the user has no idea how or what data is kept because that information is hidden from the user.
Q14) Explain data independence.
A14)
In addition to users' data, a database system usually includes a lot of data. For instance, to easily locate and retrieve data, it stores information about data, known as metadata. Modifying or modifying a collection of metadata once it is placed in the database is very difficult. But when a DBMS expands, in order to fulfill the users' requirements, it needs to evolve over time. It will become a boring and highly complicated job if the entire data is based.
Metadata itself follows a layered model, such that it does not impact the data at another level when we modify data on one layer. This data is independent, but is mapped to one another.
Types of data independences
● Physical data independence
Physical Data Independence is defined as the ability to make adjustments without affecting the higher-level schemas in the structure of the lowest level of the Database Management System (DBMS). Therefore, the Physical level adjustment does not result in any adjustments to the Conceptual or View levels.
● Logical data independence
Logical Data Independence is defined as the ability to make improvements to the Database Management System (DBMS) middle level structure without affecting the schema or application programmes at the highest level. Changes to the conceptual level should also not result in any changes to the view levels or programmes of the application.
Fig 11: Data independence
Q15) What are the Component of ER model?
A15)
Component of ER model -
The following components are composed of an ER Diagram:
- Entity
- Attributes
- Relationships
Fig 12: Component of ER model
1.Entity
The object, location, person, or event that stores data in the database may be an entity. In an object-relationship diagram, a rectangle represents an entity.
Examples of an individual include a student, course, boss, employee, patient, etc.
Fig 13: Entity
Entity type:
A list or a set of entities having certain common attributes is an entity type. In a database, a name and a list of attributes define each type of entity.
Entity set:
It is a set (or collection) of entities of the same kind that share attributes or related properties.
For example, it is possible to describe the category of individuals who are lecturers at a university as an entity-set lecturer. Similarly, the collection of students of the organization could represent the community of all university students.
2.Attribute
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 14: 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 15: 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 16: 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 17: 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 18: 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 19: Multivalued attribute
3.Relationship
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 20: 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 21: Ternary relationship
4. N-nary Relationship:
If a relationship includes more than three entity sets, an n-ary relationship is named.
Q16) What is SQL? Write the characteristics of SQL.
A16)
Structured Query Language (SQL) is a database query language which is used to store, retrieve and modify data in the relational database. SQL comes in different versions and forms. All of these versions are based upon the ANSI SQL.
Currently, MySQL is the most common programme for database management systems used for relational database management. It is an open-source, Oracle Company - supported database programme. In comparison to Microsoft SQL Server and Oracle Database, the database management system is fast, scalable, and easy to use. It is widely used for building efficient and interactive server-side or web-based business applications in combination with PHP scripts.
Characteristics of SQL
● It is quick to learn SQL.
● For accessing data from relational database management systems, SQL is used.
● SQL will perform database queries against it.
● To describe data, SQL is used.
● SQL is used in the database to describe the data and manipulate it when appropriate.
● To build and drop the database and the table, SQL is used.
● SQL is used in a database to construct a view, a stored procedure, a function.
● SQL allows users to set tables, procedures, and display permissions.
Q17) Write Advantages of SQL.
A17)
Advantages of SQL
● SQL is easy to learn and use.
● SQL is a non-procedural language. It means we have to specify only what to retrieve and not the procedure for retrieving data.
● SQL can be embedded within other languages using SQL modules, pre-compilers.
● Allows creating procedure, functions, and views using PL-SQL.
● Allows users to drop databases, tables, views, procedure and cursors.
● Allows users to set access permissions on tables, views and procedures.
Q18) Explain SQL Data types.
A18)
In order to describe the values that a column can hold, SQL Datatype is used.
Each column is needed in the database table to have a name and data type.
Fig 22: SQL data types
- Binary data types
Three kinds of binary data types are given below:
Data type | Description |
Binary | It has a fixed byte length of 8000. It includes binary data of fixed-length. |
Varbinary | It has a fixed byte length of 8000. It includes binary data of variable-length. |
Image | It has a maximum of 2,147,483,647 bytes in length. It includes binary data of variable-length. |
2. Numeric data types
The subtypes are given below:
Data type | From | To | Description |
Float | -1.79E + 308 | 1.79E + 308 | Used to specify a floating-point value |
Real | -3.40e + 38 | 3.40E + 38 | Specifies a single precision floating point number |
3. Extract numeric data types
The subtypes are given below:
Data types | Description |
Int | Used to specify an integer value. |
Smallint | Used to specify small integer value |
Bit | Number of bits to store. |
Decimal | Numeric value that can have a decimal number |
Numeric | Used to specify a numeric value |
4. Character String data types
Data types | Description |
Char | It contains Fixed-length (max - 8000 character) |
Varchar | It contains variable-length (max - 8000 character) |
Text | It contains variable-length (max - 2,147,483,647 character) |
5. Date and Time data types
Data types | Description |
Date | Used to store the year, month, and days value. |
Time | Used to store the hour, minute, and second values. |
Timestamp | Stores the year, month, day, hour, minute, and the second value. |
Q19) Explain about SQL commands.
A19)
The Structured Query Language (SQL), as we all know, is a database language that allows one to perform certain operations on existing databases as well as build new databases. To complete the tasks, SQL employs commands such as Create, Drop, and Insert.
These SQL commands are primarily divided into four groups:
● DDL (Data Definition Language)
● DML (Data Manipulation Language)
● DQL (Data Query Language)
● DCL (Data Control Language)
● TCL (Transactional control commands)
DDL:
DDL stands for Data Definition Language, and it is a part of SQL that allows a database user to build and restructure database objects, such as tables.
The following are some of the most basic DDL commands that will be addressed in the coming hours:
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX
CREATE VIEW
DROP VIEW
DML:
DML, or Data Manipulation Language, is a part of SQL that allows you to manipulate data within relational database objects.
DML commands are divided into three categories:
INSERT
UPDATE
DELETE
DQL:
Data Query Language (DQL) is the most focused subject of SQL for modern relational database users, despite having only one instruction.
The following is the base command:
SELECT
A query is a request for information from a database. A query to the database is normally sent to it through an application interface or a command line prompt.
DCL:
In SQL, you can use data control commands to limit who has access to data in the database. These DCL commands are typically used to build objects related to user access and to manage privilege distribution among users.
The following are some data access commands:
ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM
TCL:
Only DML commands like INSERT, DELETE, and UPDATE can be used with TCL commands. Since these operations are automatically committed to the database, they can't be used to create or drop tables.
Here are some examples of TCL commands:
COMMIT Saves database transactions
ROLLBACK Undoes database transactions
SAVEPOINT Creates points within groups of transactions in which to ROLLBACK
SET TRANSACTION Places a name on a transaction
Q20) Explain different operators in SQL?
A20)
An operator is a reserved word or character that is used in the WHERE clause of a SQL statement to perform operations like comparisons and arithmetic operations. These Operators are used to define conditions in SQL statements and to function as conjunctions for multiple conditions in a single statement.
● Arithmetic operators
● Comparison operators
● Logical operators
● Operators used to negate conditions
Arithmetic operator
Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.
Operator | Description | Example |
+ (Addition) | Values are added to both sides of the operator. | a + b will give 30 |
- (Subtraction) | Takes the right hand operand and subtracts it from the left hand operand. | a - b will give -10 |
* (Multiplication) | Values on both sides of the operator are multiplied. | a * b will give 200 |
/ (Division) | Divides the left and right hand operands. | b / a will give 2 |
% (Modulus) | Returns the remainder after dividing the left hand operand by the right hand operand. | b % a will give 0 |
Comparison operator:
Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.
Operator | Description | Example |
= | Checks if the values of two operands are equal, and if they are, the condition is valid. | (a = b) is not true. |
!= | Checks if the values of two operands are equal; if they aren't, the condition is valid. | (a != b) is true. |
<> | Checks if the values of two operands are equal; if they aren't, the condition is valid. | (a <> b) is true. |
> | If the left operand's value is greater than the right operand's value, then the condition is valid. | (a > b) is not true. |
< | Checks if the left operand's value is less than the right operand's value; if it is, the condition is valid. | (a < b) is true. |
>= | If the left operand's value is greater than or equal to the right operand's value, then the condition is valid. | (a >= b) is not true. |
<= | If the left operand's value is less than or equal to the right operand's value, then the condition is valid. | (a <= b) is true. |
!< | If the value of the left operand is greater than the value of the right operand, the condition is valid. | (a !< b) is false. |
!> | If the value of the left operand is less than the value of the right operand, then the condition is valid. | (a !> b) is true. |
Logical operator:
Assume that 'variable a' and 'variable b' exist. In this case, 'a' has 20 and 'b' has 10.
Sr.No | Operator & Description |
1 | ALL When a value is compared to all of the values in another value set, the ALL operator is used. |
2 | AND The AND operator allows multiple conditions to occur in the WHERE clause of a SQL statement. |
3 | ANY The ANY operator compares a value to any valid value in the list according to the condition. |
4 | BETWEEN The BETWEEN operator is used to find values that are between the minimum and maximum values in a set of values. |
5 | EXISTS The EXISTS operator is used to look for a row in a given table that meets a certain set of criteria. |
6 | IN When a value is compared to a list of literal values that have been defined, the IN operator is used. |
7 | LIKE The LIKE operator compares a value to other values that are identical using wildcard operators. |
8 | NOT The NOT operator flips the definition of the logical operator it's used with. For example, NOT EXISTS, NOT BETWEEN, NOT IN, and so on. It's a negation operator. |
9 | OR The OR operator is used in the WHERE clause of a SQL statement to combine several conditions. |
10 | IS NULL When a value is compared to a NULL value, the NULL operator is used. |
11 | UNIQUE The UNIQUE operator looks for uniqueness in every row of a table (no duplicates). |
