RDBMS Answer key
Most simple and easy to understand
Unit - 1
1Q: Define Data, Information, Database, DBMS.
Ans :-
Introduction :-
Data is an individual unit that consists of raw materials that have no specific meaning. A database is a structured collection of relational data.Information is a collection of data that has a logical meaning when taken as a whole. Data is not dependent on information. Data is essential for information. For decision making, raw data alone is insufficient.
DBMS :-
A database management system (DBMS), also known as a database system, is a piece of software that allows you to create and manage databases. Users or application programmes can use DBMS to create a database, store, manage, update/modify, and retrieve data from that database.
Applications of DBMS :-
1) Reservation Applications :-
IRCTC, redbus, AirAsia
2) Banking :- customer transaction, scholarship records, loans ,etc.
3) Education :- student details, student scholarship, staff details, college academics.
Examples of DBMS :-
Popular database management systems (DBMSs) include MySQL, Microsoft Access, Oracle Database, and Microsoft SQL.
2Q: What is DBMS? Explain advantages of DBMS over FPS (Database Approach)
Ans :-
Introduction :-
The file system does not allow data sharing, or sharing is too complicated. Data in DBMS, on the other hand, can be easily shared due to a centralised system. Concurrent access to data means that more than one user is accessing the same data at the same time.
Advantages of DBMS over FPS :-
1) File processing system :-
A File Management System (FMS) is a DBMS that allows access to a single file or table at a time. Data is directly stored in a set of files in a File System. It contains flat files with no relationship to other files (when only one table is stored in single file, then this file is known as flat file).
2) Data sharing :-
The file system does not permit data sharing, or sharing is overly complex. Data in DBMS, on the other hand, can be easily shared due to a centralised system.
3) Data Concurrency :-
Concurrent access to data means that more than one user is accessing the same data at the same time. Errors occur when changes made by one user are overwritten by changes made by another. There is no way to stop errors in the file system. DBMS, on the other hand, includes a locking system to prevent errors from occurring.
4) Data searching :-
A separate application programme must be written for each search operation performed on the file system. While DBMS has built-in searching capabilities. To retrieve data from the database, the user only needs to write a small query.
5) Data integrity :-
Before inserting data into a database, some constraints may need to be applied to it. The file system does not include any procedure for automatically checking these constraints. DBMS, on the other hand, maintains data integrity by enforcing user-defined constraints on data.
6) Data security :-
A file system has a password mechanism in place to protect the database, but how long can the password be protected? Nobody can guarantee it. In the case of DBMS, this does not occur. DBMS has specialised features that help to protect its data.
7) Interfaces :-
It offers a variety of user interfaces, including graphical user interfaces and application programme interfaces.
3Q: Architecture of DBMS-need of three level architecture.
Ans :-
Three Level Architecture of Database :-
Most modern databases are built on the ANSI-SPARC database architecture.
This architecture has three levels: the Physical level, the Conceptual level, and the External level.
The following are the specifics of these
levels :-
1) Physical Level :-
This is the lowest level in the three level architecture. It is also known as the internal level. The physical level describes how data is actually stored in the database. In the lowest level, this data is stored in the external hard drives in the form of bits and at a little high level, it can be said that the data is stored in files and folders. The physical level also discusses compression and encryption techniques.
2) Conceptual Level :-
The conceptual level is greater than the physical level. It is also referred to as the logical level. It describes how the database appears conceptually to users, as well as the relationships between various data tables. The conceptual level is unconcerned about how the data in the database is stored.
3) External Level :-
This is the highest level of the three-level architecture and the one closest to the user. It is also known as the view level. The external level only displays relevant database content to users in the form of views and hides the rest of the data. As a result, different users can view the database in different ways depending on their specific needs.
4Q: Functions of DBMS.
Ans :-
Introduction :-
Organizations make extensive use of data. A database management system (DBMS) is a piece of software that allows you to organise data in a database.
A database management system's ultimate goal is to store and transform data into information that can be used to make decisions.
Functions of DBMS :-
1. Integrity :-
There are several rules for DBMS, and the database structure is designed in such a way that data can be managed with proper rules and security. This feature allows DBMS to adhere to all database protocols and effectively manage data. There are no redundant files in the database because the rules are set up to always keep the most recent data in DBMS. Furthermore, there are no inconsistencies in the database. The use of a data dictionary can help to promote data integrity in database management systems.
2. Concurrency :-
When multiple users log into the same database at the same time, the database does not lag while running queries and returns results at the same rate. If the database is large, this concurrency feature allows multiple users to work and get results. As a result, the work is completed more quickly, and many users benefit. Several algorithms are in place to allow multiple users to access the same database.
3. Data Description :-
The database provides a data dictionary to which all database descriptions are added, allowing users to figure out newer terms and add specific terms to the query and database. The data dictionary stores all information about data elements and relationships, allowing users to check the relevant information.
4. Security :-
DBMS is managed by administrators who are committed to safeguarding the database against all threats in the database environment. They only grant users the necessary access, so some will only have read access while others will have both read and write access to the database. Some users can also edit the database, and all of these accesses are granted based on the users' roles.
5. Recovery and Backup of Data :-
DBMS is configured in such a way that the database automatically processes backups and recovers data in the event of data loss. Additionally, logs are present in the database to know the operations in DBMS so that it is easy to know what changes were made to the database and who made the changes. DBMS has automated methods for creating and manipulating data. Once the initial setup is complete, data insertion and backup are simple.
6. Access Languages and APIs :-
To access the data in the database, query language is used. Writing queries is simple because users do not need to specify how they should be done because the DBMS will handle it. SQL, or Structured Query Language, is the most commonly used language in databases because it is supported by all vendors.
5Q: Explain about logical database
Logical Database :-
A logical database is a type of ABAP (Advanced Business Application and Programming) that is used to retrieve data from multiple tables that are all interconnected. A logical database also offers a read-only view of Data.
Structure Of Logical Database :-
A logical database only uses a hierarchical structure of tables, which means that data is organised in a Tree-like structure and stored as records that are connected to each other via edges (Links). Open SQL statements are used to read data from the database in Logical Database. The logical database reads the programme, stores it if necessary, and then passes it line by line to the application programme.
Features of Logical Database :-
1) We can only select the type of data that we require.
2) Data authentication is performed to ensure security.
3) Logical databases employ a hierarchical structure to ensure data integrity.
6Q: who is DBA? Functions and roles of DBA.
Ans :-
DBA :-
A Database Administrator (DBA) is the person or entity in charge of controlling, maintaining, coordinating, and operating a database management system. The primary responsibility is to manage, secure, and maintain the database system.
They are in charge of authorising database access, coordinating, capacity, planning, installation, and monitoring uses, as well as acquiring and gathering software and hardware resources as needed.
Role and Duties of Database Administrator (DBA) :-
1. Decides Hardware :-
They choose economical hardware based on cost, performance, and efficiency of hardware, as well as what best suits the organisation. It is the hardware that serves as the interface between end users and databases.
2. Manages data integrity and security :-
Data integrity must be checked and managed correctly because it protects and restricts unauthorised use of data. To maintain data integrity, DBAs focus on relationships within data.
3. Database design :-
DBAs are held responsible and accountable for logical, physical, and external model design, as well as integrity and security control.
4. Database Implementation :-
DBAs implement DBMS and test database loading during implementation.
5. Query processing performance :-
DBA improves query processing by increasing speed, performance, and accuracy.
6. Enhancing database performance :-
If a user is unable to obtain data quickly and accurately, the organization's business may suffer. DBAs can improve database performance by tuning SQL commands.
Types of Database Administrators :-
Administrative DBA , Data Warehouse DBA ,
Development DBA , Application DBA , Architect, OLAP DBA , etc .
7Q: Explain different data models in DBMS .
Ans :-
Data Models :-
The modelling of the data description, data semantics, and consistency constraints is known as a data model. It provides conceptual tools for describing database design at each level of data abstraction. As a result, the following four data models are used to understand the structure of the database:
1. Relational Data Model :- This model organises data into rows and columns within a table. As a result, a relational model employs tables to represent data and in-between relationships. Tables are also known as relations. Edgar F. Codd first described this model in 1969. The relational data model is the most widely used model, with commercial data processing applications primarily using it.
2. Entity-Relationship Data Model (ER Model) :- An ER model is a logical representation of data as objects and their relationships. These objects are referred to as entities, and a relationship is a connection between them. Peter Chen created this model, which was published in 1976 papers. It was commonly used in database design. The entities are described by a set of attributes. For example, the'student' entity is described by student name and student id. An entity set is a collection of the same type of entities, and a relationship set is a collection of the same type of relationships.
3. Object-based Data Model :- An extension of the ER model that includes concepts such as functions, encapsulation, and object identity. This model includes a rich type system with structured and collection types. As a result, various database systems based on the object-oriented approach were developed in the 1980s. Objects in this context are nothing more than data with properties.
4. Semistructured Data Model :- This data model differs from the other three types (explained above). The semistructured data model supports data specifications at locations where individual data items of the same type may have different attribute sets. The Extensible Markup Language (XML), also known as markup language, is widely used to represent semistructured data.
8Q : Explain E.R model.
Ans :-
ER model :-
1. The ER model is an Entity-Relationship model. It is an abstract data model. This model defines the data elements and relationships for a given system.
2. It creates a conceptual design for the database. It also creates a very simple and easy to design data view.
3. The database structure is depicted as an entity-relationship diagram in ER modelling.
For example :-
Suppose we design a school database. In this database, the student will be an entity with attributes like address, name, id, age, etc. The address can be another entity with attributes like city, street name, pin code, etc and there will be a relationship between them.
Components of ER model :-
9Q: Draw E.Rmodel on student and college database.
Ans :-
Follow the steps given below to draw an Entity Relationship (ER) diagram for a University database application :-
Step 1 − Identifying the entity sets.
The entity set has multiple instances in a given business scenario.
As per the given constraints the entity sets are as follows :-
• Department
• Course
• Student
• Instructor
Head of the Department (HOD) is not an entity set. It is a relationship between the instructor and department entities.
Step 2 :- Identifying the attributes for the given entities .
• Department − the relevant attributes are department Name and location.
• Course − The relevant attributes are courseNo, course Name, Duration, and prerequisite.
• Instructor − The relevant attributes are Instructor Name, Room No, and telephone number.
• Student − The relevant attributes are Student No, Student Name, and date of birth.
Step 3 :- Identifying the Key attributes
• Department Name is the key attribute for Department.
• CourseNo is the key attribute for Course entity.
• Instructor Name is the key attribute for the Instructor entity.
• StudentNo is the key attribute for Student entities.
Step 4 :- Identifying the relationship between entity sets.
• The department offers multiple courses and each course belongs to only one department, hence cardinality between department and course if one to many.
• One course is enrolled by multiple students and one student for multiple courses. Hence, relationships are many to many.
• One department has multiple instructors and one instructor belongs to one and only one department, hence the relationship is one to many.
• Each department has one “HOD” and one instructor is “HOD” for only one department, hence the relationship is one to one. Here, HOD refers to the head of the department.
• One course is taught by only one instructor but one instructor teaches many courses hence the relationship between course and instructor is many to one.
• The relationship between instructor and student is not defined because of the following reasons :-
There is no significance in the relationship.
We can always derive this relationship indirectly through course and instructors, and course and student.
Step 5 :- Complete ER model.
The complete ER Model is as follows :-
10Q: Explain Database Approach.
Ans :-
A database approach is nothing but a method in which data is stored electronically within a computer system.
It’s arranged into different charts that can be easily accessed by a variety of computer applications from various locations. Databases are composed of a diversity of information that is relevant to the firm that is utilizing the database.
Databases are composed of two diverse pieces of information including the data and the schema that are related to each other but not the same thing.
Some of the common benefits of database management systems include:
• It reduces data redundancy
• It allows easy sharing of data
• It provides better data security and data integrity
• DBMS automatically takes care of data backup and data recovery.
As you know, a DBMS is a complex software application. All DBMSs may not offer all the above features, but these are some of the common features of current database management systems. One needs to have some basic skills and expertise about the particular DBMS being used.
Some of the most commonly used DBMSs today are Oracle, Microsoft SQL Server, Microsoft Access, MySQL, PostgreSQL, and IBM's DB2. Amongst these database management systems, SQL is the basics for all. Hence, gaining knowledge of SQL will help you become a database developer, database administrator, or database analyst.
No comments:
Post a Comment