Unit - 1

Introduction to RDBMS

Q1) Define database management system?

A1) Database management system (DBMS)

A DBMS is a programme that enables database creation, specification and manipulation, allowing users to easily store, process and analyse information.

DBMS provides one with an interface or a tool to conduct different operations, such as building databases, storing data in them, updating data, creating database tables, and much more.

The DBMS also provides databases with privacy and security. In the case of multiple users, it also ensures data consistency.

Some Example of DBMS:

• MySql
• Oracle
• SQL Server
• IBM DB2

Characteristics of DBMS

●      Data stored into tables

●      Reduced redundancy

●      Query language

●      Data consistency

●      Security

●      DBMS support transactions

●      Support multiple user and concurrent access

Q2) Write the advantages and disadvantages of DBMS?

A2) Advantages of DBMS

Disadvantages of DBMS

Q3) What is RDBMS?

A3) RDBMS

Relational Database Management System stands for Relational Database Management System.

RDBMS underpins all modern database management systems, including SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access.

It's termed Relational Database Management System (RDBMS) since it's based on E.F. Codd's relational paradigm.

The fact that the values of each table are related to each other is the cornerstone of relational DBMS. It is capable of handling enormous amounts of data and easily simulating queries.

Working of RDBMS

In RDBMS, data is represented as tuples (rows).

The most prevalent database is a relational database. It has many tables, each with its own primary key.

Data in RDBMS may be accessible quickly thanks to a collection of well-organized tables.

Q4) Write the characteristics of RDBMS?

A4) Characteristics

●      In a database file, data must be saved in tabular form, that is, in the form of rows and columns.

●      Each table row is referred to as a record/tuple. The cardinality of the table is the collection of such records.

●      Each table column is referred to as an attribute/field. The arity of the table is a collection of similar columns.

●      The DB table cannot have duplicate records. By employing a candidate key, data duplication is eliminated. The Candidate Key is a set of properties that must be present for each record to be uniquely identified.

●      Foreign keys are used to link tables together.

●      Database tables also support NULL values, which means that if the values of any of the table's elements are not filled or missing, the value becomes a NULL value, which is not the same as zero.

Q5) What are the advantages and disadvantages of RDBMS?

A5) Advantages

●      Simple to manage: Each table can be manipulated independently of the others.

●      It is more secure since it has numerous levels of security. Access to shared data might be restricted.

●      Flexible: Data may be updated in one place rather than having to change many files. Databases may be simply expanded to accommodate additional records, increasing scalability. It also makes it easier to use SQL queries.

●      Users: RDBMS can store many users in a client-side architecture.

●      Large amounts of data can be stored and retrieved more easily.

●      Fault Tolerance: Database replication allows for simultaneous access and aids in system recovery in the event of calamities such as power outages or unexpected shutdowns.

Disadvantages

●      High Cost and Extensive Hardware and Software Support: These systems demand significant investment and setup.

●      Scalability: As more data is added, more servers, as well as more power and memory, are necessary.

●      Complexity: Large amounts of data complicate understanding of relationships and may reduce performance.

●      Structured Limits: A relational database system's fields or columns are encased inside various limits, which might lead to data loss.

Q6) Write the difference between dbms and rdbms?

A6) Difference between DBMS and RDBMS

Although both DBMS and RDBMS are used to store information in physical databases, they have some significant differences.

The following are the primary distinctions between DBMS and RDBMS:

Q7) What do you mean by Object Relational Model?

A7) Object model

The object-oriented database derivation is the integrity of object-oriented

Programming language systems and consistent systems. The power of 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 1: 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.

Q8) Write about Network Model?

A8) Network model

●      Network model uses two different data structures:

a)     A record type is used to represent an 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 2: Network model

Q9) Write short notes on Hierarchical Model?

A9) Hierarchical Model

●      This model of the database organizes information into a tree-like structure with a single root to which all the other information is connected. The hierarchy begins from the root data and extends to the parent nodes like a tree, adding child nodes.

●      A child node can only have a single parent node in this model.

●      A Hierarchical model uses tree structure to represent relationships among entities.

●      This model represents many real-world relationships effectively, such as a book index, recipes, etc.

●      Data is structured into a tree-like structure in the hierarchical model with a one-to-many relationship between two different data forms.

For instance, one department can have many classes, many professors and many students of course.

Fig 3: Hierarchical model

Q10) Explain Relational Model?

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

1. Teacher Table

1. Department Table

1. Teaches in table

Q11) Describe Physical data model?

A11) Physical Data Model

A Physical Data Model specifies how the data model is implemented in a database. It provides database abstraction and assists in schema generation. This is due to the abundance of meta-data that a Physical Data Model provides. By recreating database column keys, constraints, indexes, triggers, and other RDBMS characteristics, the physical data model aids in visualizing database structure.

Fig 4: Physical data model

Advantages

●      The primary purpose of a developing data model is to ensure that the functional team's data items are appropriately represented.

●      The data model should be sufficiently detailed to be employed in the construction of the physical database.

●      Table relationships, primary and foreign keys, and stored procedures can all be defined using the data model's metadata.

●      Data modelling aids internal and cross-organizational communication.

●      In the ETL process, a data model is useful for documenting data mappings.

●      Assist in identifying appropriate data sources for the model.

Disadvantages

●      To create a data model, one must first understand the physical features of the data.

●      This navigational system results in the construction and management of complicated applications. As a result, it necessitates biographical knowledge.

●      Even minor changes in structure necessitate changes to the entire application.

●      In DBMS, there is no standard data manipulation language.

Q12) Write the characteristics of a physical model?

A12) Characteristics

●      The physical data model covers the data requirements for a specific project or application, albeit it may be combined with different physical data models depending on the scope of the project.

●      The Data Model contains relationships between tables that address relationship cardinality and nullability.

●      Designed for a specific version of a database management system, project location, data storage, or technology.

●      Columns should be assigned precise datatypes, lengths, and default values.

●      Views, indexes, access profiles, and authorizations, among other things, are defined.

Q13) Write short notes on DBA?

A13) DBA

A Database Administrator (DBA) is the person or entity in charge of the database management system's control, maintenance, coordination, and operation. The primary role is to manage, secure, and maintain the database system.

They are in charge of approving database access, managing database capacity, planning, installation, and monitoring uses, as well as procuring and gathering software and hardware resources as needed. Configuration, database design, migration, security, troubleshooting, backup, and data recovery are all roles they play. Database administration is a critical job for any company or organisation that uses one or more databases. They are the database system's overall commander.

The primary role of Database administrator is as follows −

●      Database design

●      Performance issues

●      Database accessibility

●      Capacity issues

●      Data replication

●      Table Maintenance

Q14) What is the importance of DBS and also write skills required?

A14) Importance of Database Administrator (DBA):

●      Database Administrators administer and control three levels of databases: internal, conceptual, and external levels of database management system architecture, and define the database's world view in consultation with a large user community. The exterior perspective of different users and programmes is then provided.

●      Database administrators guarantee that the database's integrity and security are protected from unauthorised users. It gives database users authorization and keeps track of each and every user's profile.

●      The database administrator is also responsible for ensuring that the database is safe and secure, with no risk of data loss.

Skills required for DBA

The following are the skills needed to be a successful DBA:

●      Creating a database.

●      Understanding of the Structured Query Language (SQL).

●      Understand the concept of distributed architecture.

●      Understanding of several operating servers.

●      Relational Database Management System Concept (RDBMS).

●      Prepared to meet obstacles and solve problems promptly.

Q15) Describe relational model?

A15) Relational model

The primary data model is the Relational Data Model, which is commonly used for data storage and processing around the world. This model is simple and has all the features and functionality needed to process data with efficiency in storage.

The relational model can be interpreted as a table with rows and columns. Each row is called a tuple. There's a name or attribute for each table in the column.

Structure

A relational database is made up of several tables.

●      Each table is given its own name.

●      There are several rows in each table.

●      Each row is a collection of data that are, by definition, related to one another in some way; these values correspond to the table's attributes or columns.

●      Each table attribute has a set of authorized values for that attribute; this set of permitted values is the attribute's domain.

Basic concept:

●      Table: Relationships are saved in the format of tables in a relational data model. The relationship between entities is stored in this format. A table includes rows and columns, where rows represent information, and attributes are represented by columns.

●      Tuple: A tuple is called a single row of a table, which contains a single record for that relationship.

Attributes and Domains

• Domain: It includes a set of atomic values that can be adopted by an attribute.
• Attribute: In a specific table, it includes the name of a column. Every Ai attribute must have a domain, a domain (Ai)

Relations

• Relational instance: The relational example is represented in the relational database structure by a finite set of tuples. There are no duplicate tuples for relation instances.
• Relational schema: The name of the relationship and the name of all columns or attributes are used in a relational schema.
• Relational key: Each row has one or more attributes in the relational key. It can uniquely identify the row in the association.

Example: STUDENT Relation

• In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the attributes.
• The instance of schema STUDENT has 5 tuples.
• t3 = <Laxman, 33289, 8583287182, Gurugram, 20>

Q16) Explain relational algebra?

A16) Relational Algebra

Relational Algebra is procedural query language. It takes Relation as input and generates relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.

Basic Operations which can be performed using relational algebra are:

1. Projection

2. Selection

3. Join

4. Union

5. Set Difference

6. Intersection

7. Cartesian product

8. Rename

Consider following relation R (A, B, C)

1.Projection (π):

This operation is used to select particular columns from the relation.

Π(AB) R:

It will select A and B column from the relation R. It produces the output like:

Project operation automatically removes duplicates from the resultant set.

2. Selection (σ):

This operation is used to select particular tuples from the relation.

 σ(C<4) R:

It will select the tuples which have a value of c less than 4.

But select operation is used to only select the required tuples from the relation. To display those tuples on screen, select operation must be combined with project operation.

Π (σ (C<4) R) will produce the result like:

3. Join 

A Cartesian product followed by a selection criterion is basically a joint process.

Operation of join, denoted by ⋈

The JOIN operation often allows tuples from different relationships to join different tuples

Types of JOIN:

Various forms of join operation are:

Inner Joins:

• Theta join
• EQUI join
• Natural join

Outer join:

• Left Outer Join
• Right Outer Join
• Full Outer Join

4. Union

Union operation in relational algebra is the same as union operation in set theory, only the restriction is that both relations must have the same set of attributes for a union of two relationships.

Syntax:  table_name1 ∪ table_name2

For instance, if we have two tables with RegularClass and ExtraClass, both have a student column to save the student name, then,

∏Student(RegularClass) ∪ ∏Student(ExtraClass)

The above operation will send us the names of students who attend both normal and extra classes, reducing repetition.

5. Set difference

Set Difference in relational algebra is the same operation of set difference as in set theory, with the limitation that the same set of attributes can have both relationships.

Syntax: A - B

Where the A and B relationships.

For instance, if we want to find the names of students who attend the regular class, but not the extra class, then we can use the following procedure:

∏Student(RegularClass) - ∏Student(ExtraClass)

6. Intersection

The symbol ∩ is the definition of an intersection.

A ∩ B

Defines a relationship consisting of a set of all the tuples in both A and B. A and B must be union-compatible, however.

7.Cartesian product

This is used to merge information into one from two separate relationships(tables) and to fetch information from the merged relationship.

Syntax: A X B

For example, if we want to find the morning Regular Class and Extra Class data, then we can use the following operation:

σtime = 'morning' (RegularClass X ExtraClass)

Both RegularClass and ExtraClass should have the attribute time for the above query to operate.

8. Rename

Rename is a unified operation that is used to rename relationship attributes.

ρ (a/b)R renames the 'b' component of the partnership to 'a'.

Syntax: ρ(RelationNew, RelationOld)

Q17) Write the responsibilities of DBA?

A17) The following are DBA's responsibilities:

• Makes the final decision on the database's content.
• The storage structure and access strategy are planned.
• Provides assistance to users.
• The security and integrity checks are defined here.
• Backup and recovery solutions for interpreters.
• Monitoring and reacting to changes in the needs.

Q18) Explain the types of DBA?

A18) Types of Database Administrator (DBA):

• Administrative DBA – Their role is to keep the server up and running. They're worried about data backups, security, troubleshooting, replication, and migration, among other things.
• Data Warehouse DBA – Previously assigned roles, but now responsible for combining data from multiple sources into a data warehouse. Prior to loading, they also construct warehouses with cleaning and cleanses data.
• Development DBA – They create and construct queries, store procedures, and other tools to satisfy the demands of a company or organisation. At programmer, they are on par.
• OLAP DBA – They create multi-dimensional cubes for use in decision support or OLAP systems.
• Architect – They are in charge of defining schemas and creating tables. They work to create a structure that fulfils the needs of the organization. Developers and development DBAs use the design to create and implement real-world applications.
• Application DBA – They are responsible for all application component requirements that interface with databases, as well as operations such as application installation and coordination, application updates, database cloning, data load process management, and so on.