Bachelor Computer Application (BCA)
Database Management System (DBMS)
Most Important Question Banks
Unit-1 Database System Architecture
Q-1 Give Function of DBMS. Application of DBMS.
Ans:
The DBMS performs several important functions that guarantee the integrity and consistency of data in the database. The current generation DBMS supports many functions, such as the following:
Stores the definitions of data and their relationships (metadata) in a data dictionary, any changes made are automatically recorded in the data dictionary.
Creates the complex structures required for data storage.
Transforms entered data to conform to the data structures.
Creates a security system and enforces security within that system.
Allow multiple user access to the data within the computer network environment.
Performs backup and data recovery procedures to ensure data safety.
Promotes and enforces integrity rules to maintain data consistency.
Provides access to the data via utility programs and from programming languages interfaces.
Application of DBMS:
Database is widely used all around the world in different sectors. Various applications of DBMS in
these sectors are given below:
Airlines: For reservation and schedule information. Airlines were among the first to use database in a geographically disturbed manner-terminals situated around the world accessed the central database system through phone lines and other data networks.
Telecommunications: For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards and storing information about the communication networks.
Universities: For student information, Course registrations and grades.
Sales: For customer, product and purchase information.
Banking: For customer information. Accounts , loans and banking transactions.
Human Resources: For information about employees, salaries, payroll taxes and benefits and for generation of paychecks.
Q-2 What are the Advantages of DBMS over file System.?
Ans:
Advantages of DBMS:
A DBMS manages data and has many benefits.
These are:
Data Redundancy can be reduced: In Non-database systems, each application or department has its own private files resulting in considerable amount of redundancy of the stored data. Thus storage space is wasted. By having a centralized database most of this can be avoided.
Data Inconsistency: Data inconsistency can be avoided. When the same data is duplicated and changes are made at one side, which is not propagated to the other site, it gives rise to inconsistency. Then the two entries regarding the same data will not agree. So, if the redundancy is removed , Chance of having inconsistent data is also removed.
Data independence: Application programs should be as free or independent as possible from details of data representation and storage. DBMS can supply an abstract view of the data for insulating application code from such facts.
Efficient data access: DBMS utilizes a mixture of sophisticated concepts and techniques for storing and retrieving data competently. This feature becomes important in cases where the data is stored on external storage devices.
Data integrity and security: If data is accessed through the DBMS, the DBMS can enforce integrity constraints on the data.
Data administration: When several users share the data, integrating the administration of data can offer significant improvements. Experienced professionals understand the nature of the data being managed and can be responsible for organizing the data representation to reduce redundancy and make the data to retrieve efficiently.
Q-3 Explain levels / view of data / Data Abstraction in dbms with appropriate example.
Ans:
A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored and maintained. This is called data abstraction. There are three levels of data abstraction:
I. Physical Level: It is the lowest level of abstraction that describes how the data are actually stored. The physical level describes complex low level data structures in detail.
II. Logical Level: it is the next higher level of abstraction that describes what data are stored in the database and what relationships exist among these data.
III. View Level: It is the highest level of abstraction that describes only part of the entire database. Shows the relationships among the three.
For Example, Consider a banking example with records.:
Account, with fields accno, balance
Employee, with fields employeename and salary
Customer, with fields customername and customerid, address.
At the physical level, a customer, account, or employee record can be described as a block of consecutives storage locations(for example, words or bytes). The language compiler hides this level of deatail from programmers.
Q-4 Explain Data Security and Integrity.
Ans.
1.Data Security :
Data security refers to the prevention of data from unauthorized users. It is only allowed to access the data to the authorized users. In database, the DBA or head of department can access all the data. Some users are only allowed to retrieve data, whereas others are allowed to retrieve as well as to modify the data.
2.Data Integrity :
Data integrity is defined as the data contained in the database is both correct and consistent. For this purpose, the data stored in the database must satisfy certain types of procedures (rules). The data in a database must be correct and consistent. DBMS provides different ways to implement such types of constraints (rules). It can be implemented by rules i.e., Primary Key, Secondary Key, Foreign key. This improves data integrity in a database.
Q-5 Explain Types of database system architecture with diagram in detail.
Ans.
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:
o In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
o 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:
o 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.
o The user interfaces and application programs are run on the client-side.
o The server side is responsible to provide the functionalities like: query processing and transaction management.
o To communicate with the DBMS, client-side application establishes a connection with the server side.
Fig: 2-tier Architecture
3-Tier Architecture:
o The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.
o The application on the client-end interacts with an application server which further communicates with the database system.
o 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.
o The 3-Tier architecture is used in case of large web application.
Unit-2 Data Models
Q-6 Explain the network Model. Give advantages and disadvantages of it.
Ans:
The network model was created to represent complex data relationships more effectively when compared to hierarchical models, to improve database performance and standards. It has entities which are organized in a graphical representation and some entities are accessed through several paths. A User perceives the network model as a collection of records in 1:M relationships.
Advantages:
The advantages of network model are as follows:
Network models represent complex data relationships better than the hierarchical models.
It handles so many relationship types.
Data access is more flexible than hierarchical models.
Improved database performance.
It includes Data Definition Language (DDL) and Data Manipulation Language (DML) commands.
Disadvantages:
The disadvantages of network model are as follows:
Database contains a complex array of pointers.
System complexity limits efficiency.
Structural changes require changes in all application programs.
Navigation systems yield complex implementation and management.
Keep heavy pressure on programmers due to the complex structure.
Any change like updating, deletion, insertion is very complex.
Q-7 Explain basic concepts of referential integrity.
Ans.
Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key.
Foreign keys join tables and establish dependencies between tables. tables can form a hierarchy of dependencies in such a way that if you change or delete a row in one table, you destroy the meaning of rows in other tables. For example, the following figure shows that the customer_num column of the customer table is a primary key for that table and a foreign key in the orders and cust_call tables. Customer number 106, George Watson™, is referenced in both the orders and cust_calls tables. If customer 106 is deleted from the customer table, the link between the three tables and this particular customer is destroyed.
Figure 1. Referential integrity in the demonstration database
When you delete a row that contains a primary key or update it with a different primary key, you destroy the meaning of any rows that contain that value as a foreign key. Referential integrity is the logical dependency of a foreign key on a primary key. The integrity of a row that contains a foreign key depends on the integrity of the row that it references—the row that contains the matching primary key.
By default, the database server does not allow you to violate referential integrity and gives you an error message if you attempt to delete rows from the parent table before you delete rows from the child table. You can, however, use the ON DELETE CASCADE option to cause deletes from a parent table to trip deletes on child tables. See To define primary and foreign keys, and the relationship between them, use the CREATE TABLE and ALTER TABLE statements. For more information on these statements, see the IBM® Informix® Guide to SQL: Syntax. For information about how to build a data model with primary and foreign keys, see the IBM Informix Database Design and Implementation Guide.
Q-8 Construct E –R diagram of the bank. It provides different kinds of bank accounts and loan. it operates number of branches .
Ans.
Unit-3 Relational Query Languages
Que 9: Explain Selection and Projection relational algebra in detail.
Ans:
1. Selection : This operation chooses the subset of tuples from the relation that satisfies the given condition mentioned in the syntax of selection.
Notation – σ p (R)
Here, ‘p’ is selection condition and ‘σ (sigma)’ is used to denote Select Operator.
Example: σ Branch=”BCA” (Student)
2. Projection : This operation selects certain required attributes, while discarding other attributes.
Notation – πA (R)
where ‘A’ is the attribute list, it is the desired set of attributes from the attributes of relation(R), symbol ‘π(pi)’ is used to denote the project operator,
R is generally a relational algebra expression, which results in a relation.
Example: π Branch (Student)
Unit-4 Relational Database Design
Que 10: What is normalization? List out types of normalization. Explain the advantages and disadvantages of normalization.
Ans: Normalization:
Normalization is the
Normalization is used minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
Types of Normalization:
1NF (First Normal
2NF (Second Normal
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)
4NF (Fourth Normal Form)
5NF (Fifth Normal Form)
6NF (Sixth Normal Form)
Advantages of Normalization:
Normalization helps to minimize data redundancy
Greater overall database organization
Data consistency within the database
Much more flexible database design.
Enforces the concept of relational integrity
Disadvantages of Normalization:
You cannot start building the database before knowing what the user needs.
The performance degrades when normalizing the relations to higher normal forms , i.e., 4NF, 5NF.
It is very time-consuming and difficult to normalize relations of a higher degree.
Careless decomposition may lead to a bad database design, leading to serious problems.
Que 11: Explain 1 NF and 2 NF in detail with example.
Ans: First Normal Form (1NF):
For a table to be in the First
Below table is not in 1 NF because the subject column contain multiple values for single roll_no. So, 2nd table is in 1 NF.
roll_no | name | subject |
101 | Akon | OS,CN |
103 | Ckon | Java |
102 | Bkon | C,C++ |
roll_no | name | subject |
101 | Akon | OS |
101 | Akon | CN |
103 | Ckon | Java |
102 | Bkon | C |
102 | Bkon | C++ |
Second Normal Form (2NF)
For a table to be in the Second Normal Form
It should be in the First Normal Form
And, it should not have Partial Dependency
Here 1st table is not in 2 NF because it contains partial dependency and 2nd table is in 2 NF.
score_id | student_id | subject_id | marks | teacher |
1 | 10 | 1 | 70 | Java teacher |
2 | 10 | 2 | 75 | C++ teacher |
3 | 11 | 1 | 80 | Java teacher |
subject_id | subject_name | teacher |
1 | Java | Java teacher |
2 | C++ | C++ teacher |
3 | Php | Php teacher |
score_id | student_id | subject_id | marks |
1 | 10 | 1 | 70 |
2 | 10 | 2 | 75 |
3 | 11 | 1 | 80 |
Unit- 5 Query Processing and Optimization
Que 12: Explain steps of query Processing in detail.
Ans: Query Processing is a translation of high-level queries into low-evel
expression.
given SQL query is translated by the query processor into a low level program called an execution plan
An execution plan is a program in a functional language
The physical relational algebra with primitives to search through the internal storage structure of DBMS.
Basic Steps in Query Processing:
1. Parsing and translation
2. Optimization
3. Evaluation
1. Parsing and translation:
Translate the query its internal form. This is then transleted into relational Algebra.
Parser checks syntax, verifies relation.
2. Optimization:
SQL is a very high level language.
The users specify what to search for not how the search is actually done
The algorithms are chosen automatically by the DBMS.
For a given SQL query there may be many possible execution plans.
Amongst all equivalent plans choose the one with lowest cost.
Cost is estimated using statistical information from the database catalog.
3. Evaluation:
The query evaluation engine takes a query evalution plan, executes that plan and returns the answer to that query.
Que 13: Explain the conditions for 1 NF, 2 NF, 3 NF, 4 NF, 5 NF and BCNF.
Ans:
Normal Form | Description |
1NF | A relation is in 1NF if it contains an atomic value. |
2NF | A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key. |
3NF | A relation will be in 3NF if it is in 2NF and no transition dependency exists. |
BCNF | A stronger definition of 3 NF is known as Boy's Codd's normal form. |
4NF | A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued dependency. |
5NF | A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossless. |
Unit-6 Storage Strategies
Que-14: Explain the structure of Index in database. Explain different attributes of Indexing.
Ans: Indexing is used to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed.
The index is a type of data structure. It is used to locate and access the data in database table quickly.
Indexes are created using a few database columns.
The first column of the database is the search key that contains a copy of the primary key or candidate key of the table. The values of the primary key are stored in sorted order so that the corresponding data can be accessed easily.
The second column of the database is the data reference. It contains a set of pointers holding the address of the disk block where the value of the particular key can be found.
The indexing has various attributes:
Access Types: This refers to the type of access such as value based search, range access, etc.
Access Time: It refers to the time needed to find particular data element or set of elements.
Insertion Time: It refers to the time taken to find the appropriate space and insert a new data.
Deletion Time: Time taken to find an item and delete it as well as update the index structure.
Space Overhead: It refers to the additional space required by the index.
Que-15: What is Hashing? Explain dynamic hashing.
Ans-:
In this technique, data is stored at the data blocks whose address is generated by using the hashing function. The memory location where these records are stored is known as data bucket or data blocks.
In this, a hash function can choose any of the column value to generate the address. Most of the time, the hash function uses the primary key to generate the address of the data block. A hash function is a simple mathematical function to any complex mathematical function. We can even consider the primary key itself as the address of the data block. That means each row whose address will be the same as a primary key stored in the data block.
There are two types of hashing:
Dynamic hashing:-
The dynamic hashing method is used to overcome the problems of static hashing like bucket overflow.
In this method, data buckets grow or shrink as the records increases or decreases. This
method is also known as extendable hashing method.
This method makes hashing dynamic, i.e., it allows insertion or deletion without resulting in poor performance.
How to search a key:
First, calculate the hash address of the key.
Check how many bits are used in the directory, and these bits are called as i.
Take the least significant i bits of the hash address. This gives an index of the directory.
Now using the index, go to the directory and find bucket address where the record might
be.
How to insert a new record :
Firstly, you have to follow the same procedure for retrieval, ending up in some bucket.
If there is still space in that bucket, then place the record in it.
If the bucket is full, then we will split the bucket and redistribute the records.
E.X.:-
Consider the following grouping of keys into buckets, depending on the prefix of their hash address:
The last two bits of 2 and 4 are 00. So it will go into bucket B0. The last two bits of 5 and 6 are 01, so it will go into bucket B1. The last two bits of 1 and 3 are 10, so it will go into bucket B2. The last two bits of 7 are 11, so it will go into B3.
Advantages of dynamic hashing :
In this method, the performance does not decrease as the data grows in the system. It simply increases the size of memory to accommodate the data.
In this method, memory is well utilized as it grows and shrinks with the data. There will not be any unused memory lying.
This method is good for the dynamic database where data grows and shrinks
frequently.
Unit- 7 Transaction Processing
Que-16:- what is transaction? Explain properties of transaction.
Ans-:
A transaction is a sequence of operations performed as a single logical unit of work.
A transaction is a logical unit of work that contains one or more SQL statements.
Example of transaction:-
Want to transfer Rs. 50 from Account-A to Account-B
The transaction has the four properties. These are used to maintain consistency in a database, before and after the transaction.
Property of Transaction:
1. Atomicity
2. Consistency
3. Isolation
4. Durability
(i) Atomicity :
o It states that all operations of the transaction take place at once if not, the transaction is aborted.
o There is no midway, i.e., the transaction cannot occur partially. Each transaction is treated as one unit and either run to completion or is not executed at all.
Atomicity involves the following two operations:
Abort: If a transaction aborts then all the changes made are not visible.
Commit: If a transaction commits then all the changes made are visible.
Example: Let's assume that following transaction T consisting of T1 and T2. A consists of Rs 600 and B consists of Rs 300. Transfer Rs 100 from account A to account B.
T1 | T2 |
Read(A) A:=A-100 | Read(B) Y:=Y+100 |
Write(A) | Write(B) |
After completion of the transaction, A consists of Rs 500 and B consists of Rs 400.
If the transaction T fails after the completion of transaction T1 but before completion of transaction T2, then the amount will be deducted from A but not added to B. This shows the inconsistent database state. In order to ensure correctness of database state, the transaction must be executed in entirety.
Consistency :
o The integrity constraints are maintained so that the database is consistent before and after the transaction.
o The execution of a transaction will leave a database in either its prior stable state or a new stable state.
o The consistent property of database states that every transaction sees a consistent database instance.
o The transaction is used to transform the database from one consistent state to another consistent state.
For example: The total amount must be maintained before or after the transaction.
1. Total before T occurs = 600+300=900
2. Total after T occurs= 500+400=900
Therefore, the database is consistent. In the case when T1 is completed but T2 fails, then inconsistency will occur.
Isolation:
o It shows that the data which is used at the time of execution of a transaction cannot be used by the second transaction until the first one is completed.
o In isolation, if the transaction T1 is being executed and using the data item X, then that data item can't be accessed by any other transaction T2 until the transaction T1 ends.
o The concurrency control subsystem of the DBMS enforced the isolation property.
Durability:
o The durability property is used to indicate the performance of the database's consistent state. It states that the transaction made the permanent changes.
o They cannot be lost by the erroneous operation of a faulty transaction or by the system failure. When a transaction is completed, then the database reaches a state known as the consistent state. That consistent state cannot be lost, even in the event of a system's failure.
o The recovery subsystem of the DBMS has the responsibility of Durability property.
Que-17:- Explain Log-Based Recovery
Ans:-
o The log is a sequence of records. Log of each transaction is maintained in some stable storage so that if any failure occurs, then it can be recovered from there.
o If any operation is performed on the database, then it will be recorded in the log.
o But the process of storing the logs should be done before the actual transaction is applied in the database.
Let's assume there is a transaction to modify the City of a student. The following logs are written for this transaction.
When the transaction is initiated, then it writes 'start' log.
<Tn, Start>
When the transaction modifies the City from 'Noida' to 'Bangalore', then another log is written to the file.
<Tn, City, 'Noida', 'Bangalore' >
When the transaction is finished, then it writes another log to indicate the end of the transaction.
<Tn, Commit>
There are two approaches to modify the database:
1. Deferred database modification:
o The deferred modification technique occurs if the transaction does not modify the
database until it has committed.
o In this method, all the logs are created and stored in the stable storage, and the database is
updated when a transaction commits.
2. Immediate database modification:
o The Immediate modification technique occurs if database modification occurs while the transaction is still active.
o In this technique, the database is modified immediately after every operation. It follows an actual database modification.
Recovery using Log records
When the system is crashed, then the system consults the log to find which transactions need to be undone and which need to be redone.
1. If the log contains the record <Ti, Start> and <Ti, Commit> or <Ti, Commit>, then the Transaction Ti needs to be redone.
2. If log contains record<Tn, Start> but does not contain the record either <Ti, commit> or <Ti, abort>, then the Transaction Ti needs to be undone.
Que-18: Explain Deadlock with suitable example.
Ans-:
A deadlock is a condition where two or more transactions are waiting indefinitely for one another to give up locks. Deadlock is said to be one of the most feared complications in DBMS as no task ever gets finished and is in waiting state forever.
For example: In the student table, transaction T1 holds a lock on some rows and needs to update some rows in the grade table. Simultaneously, transaction T2 holds locks on some rows in the grade table and needs to update the rows in the Student table held by Transaction T1.
Now, the main problem arises. Now Transaction T1 is waiting for T2 to release its lock and similarly, transaction T2 is waiting for T1 to release its lock. All activities come to a halt state and remain at a standstill. It will remain in a standstill until the DBMS detects the deadlock and aborts one of the transactions.
E.X.-2:
Unit- 8 Database Security
Q-19 . How Many types of Access control. DAC And MAC in details.
Ans :Obsolete access models include Discretionary Access Control (DAC) and Mandatory Access Control (MAC). Role Based Access Control (RBAC) is the most common method today, and the most recent model is Attribute Based Access Control (ABAC).
Discretionary Access Control (DAC)
With DAC models, the data owner allows access. DAC is a means of assigning access rights based on user-specified rules.
Mandatory Access Control (MAC)
MAC was developed using a nondiscretionary model, in which people are granted access based on an information clearance. MAC is a policy in which access rights are assigned based on central authority regulations.
Role Based Access Control (RBAC)
RBAC grants access based on a user’s role and implements key security principles such as “least privilege” and “separation of privilege.” Thus, someone attempting to access information can only access data necessary for their role.
Attribute Based Access Control (ABAC)
In ABAC, each resource and user are assigned a series of attributes. In this dynamic method, a comparative assessment of the user’s attributes, including time of day, position and location, are used to make a decision on access to a resource.
1.DAC :
DAC is identity-based access control. DAC mechanisms will be controlled by user
identification such as username and password. DAC is discretionary because the owners can transfer objects or any authenticated information to other users. In simple words, the owner can determine the access privileges.
Attributes of DAC –
1. Users can transfer their object ownership to another user.
2. The access type of other users can be determined by the user.
3. Authorization failure can restrict the user access after several failed attempts.
4. Unauthorized users will be blind to object characteristics called file size, directory path,
and file name.
Examples- Permitting the Linux file operating system is an example of DAC.
2.MAC :
The operating system in MAC will provide access to the user based on their identities and data. For gaining access, the user has to submit their personal information. It is very secure because the rules and restrictions are imposed by the admin and will be strictly followed. MAC settings and policy management will be established in a secure network and are limited to system administrators.
Attributes of MAC –
1. MAC policies can help to reduce system errors.
2. It has tighter security because only the administrator can access or alter controls.
3. MAC has an enforced operating system that can label and delineate incoming application data.
4. Maintenance will be difficult because only the administrator can have access to the database.
Examples- Access level of windows for ordinary users, admins, and guests are some of the examples of MAC.
Q-20 Different between DAC and MAC.
Ans-:
DAC | MAC |
DAC stands for Discretionary Access Control. | MAC stands for Mandatory Access Control. |
DAC is easier to implement. | MAC is more secure to use. |
In DAC, the owner can determine the access and privileges and can restrict the resources based on the identity of the users. | In MAC, the system only determines the access and the resources will be restricted based on the clearance of the subjects. |
DAC has extra labor-intensive properties. | MAC has no labor-intensive property. |
Users will be provided access based on their identity and not using levels. | Users will be restricted based on their power and level of hierarchy. |
DAC has high flexibility with no rules and regulations. | MAC is not flexible as it contains lots of strict rules and regulations. |
DAC has complete trust in users. | MAC has trust only in administrators. |
Decisions will be based only on user ID and ownership. | Decisions will be based on objects and tasks, and they can have their own ids. |
Information flow is impossible to control. | Information flow can be easily controlled. |
DAC is supported by commercial DBMS. | MAC is not supported by commercial DBMSs. |
DAC can be applied in all domains. | MAC can be applied in the military, government, and intelligence. |
DAC is vulnerable to trojan horses. | MAC prevents virus flow from a higher level to a lower level. |
Q-21 What is the IDS(Intrusion Detection System)? Explain Classification of IDS.
Ans-:
An Intrusion Detection System (IDS) is a system that monitors network traffic for suspicious activity and issues alerts when such activity is discovered. It is a software application that scans a network or a system for the harmful activity or policy breaching. Any malicious venture or violation is normally reported either to an administrator or collected centrally using a security information and event management (SIEM) system. A SIEM system integrates outputs from multiple sources and uses alarm filtering techniques to differentiate malicious activity from false alarms.
Although intrusion detection systems monitor networks for potentially malicious activity, they are also disposed to false alarms. Hence, organizations need to fine-tune their IDS products when they first install them. It means properly setting up the intrusion detection systems to recognize what normal traffic on the network looks like as compared to malicious activity.
Intrusion prevention systems also monitor network packets inbound the system to check the malicious activities involved in it and at once send the warning notifications.
Classification of Intrusion Detection System:
IDS are classified into 5 types:
1. Network Intrusion Detection System (NIDS):
Network intrusion detection systems (NIDS) are set up at a planned point within the network to examine traffic from all devices on the network. It performs an observation of passing traffic on the entire subnet and matches the traffic that is passed on the subnets to the collection of known attacks. Once an attack is identified or abnormal behavior is observed, the alert can be sent to the administrator. An example of a NIDS is installing it on the subnet where firewalls are located in order to see if someone is trying to crack the firewall.
2. Host Intrusion Detection System (HIDS):
Host intrusion detection systems (HIDS) run on independent hosts or devices on the network. A HIDS monitors the incoming and outgoing packets from the device only and will alert the administrator if suspicious or malicious activity is detected. It takes a snapshot of existing system files and compares it with the previous snapshot. If the analytical system files were edited or deleted, an alert is sent to the administrator to investigate. An example of HIDS usage can be seen on mission-critical machines, which are not expected to change their layout.
3. Protocol-based Intrusion Detection System (PIDS):
Protocol-based intrusion detection system (PIDS) comprises a system or agent that would consistently resides at the front end of a server, controlling and interpreting the protocol between a user/device and the server. It is trying to secure the web server by regularly monitoring the HTTPS protocol stream and accept the related HTTP protocol. As HTTPS is un-encrypted and before instantly entering its web presentation layer then this system would need to reside in this interface, between to use the HTTPS.
4. Application Protocol-based Intrusion Detection System (APIDS):
Application Protocol-based Intrusion Detection System (APIDS) is a system or agent that generally resides within a group of servers. It identifies the intrusions by monitoring and interpreting the communication on application-specific protocols. For example, this would monitor the SQL protocol explicit to the middleware as it transacts with the database in the web server.
5. Hybrid Intrusion Detection System :
Hybrid intrusion detection system is made by the combination of two or more approaches of the intrusion detection system. In the hybrid intrusion detection system, host agent or system data is combined with network information to develop a complete view of the network system. Hybrid intrusion detection system is more effective in comparison to the other intrusion detection system. Prelude is an example of Hybrid IDS.
Q-22 Explain SQL Injection with example and types of SQL Injection attacks.
Ans-:
The SQL Injection is a code penetration technique that might cause loss to our database. It is one of the most practiced web hacking techniques to place malicious code in SQL statements, via webpage input. SQL injection can be used to manipulate the application's web server by malicious users.
SQL injection generally occurs when we ask a user to input their username/userID. Instead of a name or ID, the user gives us an SQL statement that we will unknowingly run on our database. For Example - we create a SELECT statement by adding a variable "demoUserID" to select a string. The variable will be fetched from user input (getRequestString).
1. demoUserI = getrequestString("UserId");
2. demoSQL = "SELECT * FROM users WHERE UserId =" +demoUserId;
Types of SQL injection attacks
SQL injections can do more harm other than passing the login algorithms. Some of the SQL injection attacks include:
o Updating, deleting, and inserting the data: An attack can modify the cookies to poison a web application's database query.
o It is executing commands on the server that can download and install malicious programs such as Trojans.
o We are exporting valuable data such as credit card details, email, and passwords to the attacker's remote server.
o Getting user login details: It is the simplest form of SQL injection. Web application typically accepts user input through a form, and the front end passes the user input to the back end database for processing.
Example of SQL Injection
We have an application based on employee records. Any employee can view only their own records by entering a unique and private employee ID. We have a field like an Employee ID. And the employee enters the following in the input field:
236893238 or 1=1
It will translate to:
1. SELECT * from EMPLOYEE where EMPLOYEE_ID == 236893238 or 1=1
The SQL code above is valid and will return EMPLOYEE_ID row from the EMPLOYEE table.
The 1=1 will return all records for which this holds true. All the employee data is compromised;
now, the malicious user can also similarly delete the employee records.
Example:
1. SELECT * from Employee where (Username == "" or 1=1) AND (Password="" or 1=1).
Now the malicious user can use the '=' operator sensibly to retrieve private and secure user
information. So instead of the query mentioned above, the following query, when exhausted,
retrieve protected data, not intended to be shown to users.
1. SELECT * from EMPLOYEE where (Employee_name =" " or 1=1) AND
(Password=" " or 1 =1)
Unit-9 SQL Concepts
Q-23 Explain DDL and DML with Syntax.
Ans-:
DDL - DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.
CREATE - to create a database and its objects like (table, index, views, store
procedure, function, and triggers)
ALTER - alters the structure of the existing database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for
the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML - DML is short name of Data Manipulation Language which deals with data
manipulation and includes most common SQL statements such SELECT, INSERT,
UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.
SELECT - retrieve data from a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - Delete all records from a database table
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - interpretation of the data access path
LOCK TABLE - concurrency Control
Q-24 Explain following Term.
(1) PRIMARY KEY - A primary key is a column -- or a group of columns -- in a table that uniquely identifies the rows in that table. For example, in the table below, Customer No, which displays the ID number assigned to different customers, is the primary key.
(2) CANDIDATE KEY- Candidate key is a single key or a group of multiple keys that uniquely identify rows in a table. A Candidate key is a subset of Super keys and is devoid of any unnecessary attributes that are not important for uniquely identifying tuples.
(3) SUPER KEY - The set of attributes that can uniquely identify a tuple is known as Super Key. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc. Adding zero or more attributes to the candidate key generates the super key. A candidate key is a super key but vice versa is not true.
(4) ON DELETE CASCADE - ON DELETE CASCADE clause in MySQL is used to
automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key.
(5) ON DELETE SET NULL - On Delete Set Null clause sets all the records of the column which is defined as a foreign key in the child table to Null if the corresponding record in the parent table is deleted.
(6) NOT NULL - The NOT NULL constraint is used to ensure that a given column of a table is never assigned the null value. Once a NOT NULL constraint has been defined for a particular column, any insert or update operation that attempts to place a null value in that column will fail.
(7) UNIQUE - A unique key in DBMS is a key that is able to identify all the records of a table uniquely.
(8) FORIGN KEY - A foreign key is the one that is used to link two tables together via the primary key. It means the columns of one table points to the primary key attribute of the other table.
(9) CHECK - CHECK constraint is used to restrict the value of a column between a ranges. It performs check on the values, before storing them into the database. It’s like condition checking before saving data into a column.
(10) Instance and Schema –
Instance- An instance is defined as the data or collection of information stored in a database at a particular moment of time.
Schema - Schema is defined as the overall description of the database, or simply it is the design of a database.
Q-25 Explain Aggregate functions with example.
Ans:
Aggregate functions are count, min, max,sum,avg
They are used tocompute a single value from a set of attribute values of a column:
Count()
Returns the number of rows that matches a specified condition. Default
it returns total number of records.
Example: How many tuples are stored in the relation EMP?
select count(*) from EMP;
COUNT(*)
----------
14
Example: How many different job titles are stored in the relation EMP?
select count(JOB) from EMP;
COUNT(JOB)
------------------
5
Max()
Returns the largest value of the selected column.
Example: Display maximum salary.
select max(salary) from EMP;
MAX(salary)
----------
80000
Min ()
Returns the smallest value of the selected column.
Example: Display minimum salary.
select minimum(salary) from EMP;
minimum(salary)
----------
5000
Example: Compute the difference between the minimum and maximum salary.
SQL> select max(salary) - min(salary) from EMP;
MAX(SAL)-MIN(SAL)
-----------------
4200
Sum()
Returns the total sum of a numeric column.
Example: Sum of all salaries of employees working in the department 30.
SQL> select sum(salary) from EMP
SUM(SALary)
----------
540000
SQL> select sum(salary) from EMPwhere DEPTNO =
SUM(salary)
----------
94000
Avg()
Returns the average value of a numeric column.
SQL> select avg(salary) from EMP;
AVG(SAL)
----------
2073.21429
Q-26 Explain Types of SQL Join.
Ans:
A SQL Join statement is used to combine data or rows from two or more tables
based on a common field between them. Different types of Joins are :
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
Consider the two tables below:
Student
StudentCourse
The simplest Join is INNER JOIN.
1. INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
1. Note: We can also write JOIN instead of INNER JOIN. JOIN is same as
INNER JOIN.
1.
Example Queries(INNER JOIN)
1.
This query will show the names and age of students enrolled in different
courses.
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM
Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output:
1. LEFT JOIN: This join returns all the rows of the table on the left side of the
join and matching rows for the table on the right side of join. The rows for
which there is no matching row on right side, the result-set will contain null.
LEFT JOIN is also known as LEFT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
1. Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are
same.
Example Queries(LEFT JOIN):
1.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
1. Output:
1. RIGHT JOIN: RIGHT JOIN is similar to LEFT JOIN. This join returns all the
rows of the table on the right side of the join and matching rows for the table
on the left side of join. The rows for which there is no matching row on left
side, the result-set will contain null. RIGHT JOIN is also known as RIGHT
OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
1. Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both
are same.
1.
Example Queries(RIGHT JOIN):
1.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
1. Output:
1. FULL JOIN: FULL JOIN creates the result-set by combining result of both
LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from
both the tables. The rows for which there is no matching, the result-set will
contain NULL values.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
1.
Example Queries(FULL JOIN):
1.
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
1. Output:
NAME | COURSE_ID |
HARSH | 1 |
PRATIK | 2 |
RIYANKA | 2 |
DEEP | 3 |
SAPTARTHI | 1 |
DHANRAJ | NULL |
NAME | COURSE_ID |
ROHIT | NULL |
NIRAJ | NULL |
NULL | 4 |
NULL | 5 |
NULL | 4 |
Q-27 Write a query to find the names of employees that begin with ‘S’
Ans:
SELECT * FROM EmployeeInfo WHERE EmpFname LIKE 'S%';
Q-28 Write a query to fetch the department-wise count of employees sorted by department’s count in ascending order.
Ans:
Select Department ,count(EmpId) AS EmpDeptCount
From EmployeeInfo GroupBy Department
ORDER BY EmpDeptCount ASC;
Q-29 Write a query to retrieve the list of employees working in the same department.
Ans:
select DISTINCT E.Empid , E. Empname, E.Department
FROM EmployeeInfo E, Employee E1
Where E. Department=E1. Department AND E.EmpID !=E1. EmpID;
Q-30 Write a query to fetch all the records from the EmployeeInfo table ordered by EmpLname in descending order and Department in the ascending order.
Ans:
SELECT * FROM EmployeeInfo ORDER BY EmpFname desc, Department asc;
😊 Good Luck On Your Exams 😊
Comentarios