DBMS Assignment

Q1. What are the advantages of DBMS? What are the disadvantages in File Processing System?

Advantages of DBMS (Database Management System)

 Enhanced data integrity and security

 Efficient data management and retrieval

 Reduced data redundancy and inconsistency

 Support for complex transactions and queries

 Facilitates data sharing and multi-user environment

Disadvantages of File Processing System

A file processing system suffers from data redundancy, lack of data integrity, and inconsistency issues.
It provides limited data sharing and has poor security measures. Additionally, it lacks support for
complex queries and efficient data retrieval, making data management cumbersome and less
reliable.

Q2. What are the three levels of data abstraction?

The three levels of data abstraction in a database are:

Physical Level: This is the lowest level of abstraction, which describes how the data is physically
stored on storage devices. It involves the complex details of data storage such as file structures and
access methods.

Logical Level: This middle level of abstraction defines what data is stored in the database and the
relationships among those data. It provides a more abstract view compared to the physical level,
focusing on the logical structure of the database without worrying about how they are physically
stored.

View Level: This is the highest level of abstraction and is concerned with how data is viewed by
individual users or specific applications. At this level, only a portion of the entire database is
presented to the user, hiding the complexity of the underlying data structures.

Q3. What is the difference between CHAR and VARCHAR?

CHAR

CHAR is a fixed-length data type. When you define a CHAR column, you set a fixed size, and every
value will occupy that exact amount of space, regardless of its actual length. For example, a CHAR(10)
column storing a 5-character word will fill the remaining spaces with blanks. This can lead to wasted
storage but ensures consistent size and potentially faster access for fixed-size data.

VARCHAR

VARCHAR is a variable-length data type. When you define a VARCHAR column, you set a maximum
size, but each value uses only the necessary space plus a small overhead for length storage. For
example, a VARCHAR(10) column storing a 5-character word will use space for those 5 characters
plus a little extra.

Q4. There is a table which contains two columns Student and Marks, you need to find all the

students whose marks are greater than average marks i.e list of above average students.

Q5. Define the types of Relational Integrity Constraint.

Types of Relational Integrity Constraints

1. Entity Integrity: Ensures that each table has a unique primary key and that the primary key
cannot be null. This guarantees that every record can be uniquely identified.

2. Referential Integrity: Ensures that foreign keys correctly reference primary keys in other
tables. This maintains the accuracy and consistency of relationships between tables.

3. Domain Integrity: Ensures that all entries in a column are of the same type and within a valid
range. This enforces valid data entries based on defined rules for each column.

Q6. What is Data Independence? Describe the categories of Data Independence.

Data Independence is the ability to change the schema (structure) at one level of a database
system without affecting the schema at the next higher level. This ensures that changes in data
storage or structure do not impact the application programs that access the data.

Categories of Data Independence

1. Logical Data Independence: The ability to change the logical schema (the design and
organization of the data, like adding new fields or tables) without altering the existing
applications or user views. This means you can modify the data structure without changing
how the data is accessed by users.

2. Physical Data Independence: The ability to change the physical schema (how and where
data is stored, like using different storage devices or file structures) without affecting the
logical schema. This ensures that changes in storage methods or hardware do not impact
how the data is logically organized and accessed by applications.

Q7. What is a Data Model? Explain with diagram the Three-Tier Client-Server Architecture.

What is a Data Model?

A data model is a conceptual framework that outlines how data is structured, stored, and
managed in a database system. It defines the logical relationships, constraints, and rules
governing the data. Common data models include hierarchical, network, relational, and object-
oriented models.

Three-Tier Client-Server Architecture

The Three-Tier Client-Server Architecture divides an application into three layers or tiers:

1. Presentation Tier: This is the topmost layer, where users interact with the application. It
includes the user interface (UI) components like web pages or mobile app interfaces.

2. Application Tier: This middle layer, also known as the business logic layer, processes the
user's requests. It contains the application’s logic and communicates between the
presentation and data tiers.

3. Data Tier: This bottom layer is responsible for data storage and management. It includes the
database servers where data is stored and retrieved.

Q8. Describe the meaning of the following simple SQL query:

SELECT FNAME, LNAME, ADDRESS

FROM EMPLOYEE, DEPARTMENT

WHERE DNAME=’Research’ AND DNUMBER=DNO;

This SQL query retrieves specific information from a database by combining data from two
tables, EMPLOYEE and DEPARTMENT.

 SELECT FNAME, LNAME, ADDRESS: This specifies that the query should return the first name
(FNAME), last name (LNAME), and address (ADDRESS) of employees.

 FROM EMPLOYEE, DEPARTMENT: This indicates that the data will be fetched from both the
EMPLOYEE and DEPARTMENT tables.

 WHERE DNAME='Research' AND DNUMBER=DNO: This filters the results to include only
employees who work in the department named 'Research'. It also ensures that the
department number (DNUMBER) in the DEPARTMENT table matches the department
number (DNO) in the EMPLOYEE table.

Q9. What is the difference between ORDERBY and GROUPBY?

ORDER BY and GROUP BY are both used to organize data, but they serve different purposes:

 ORDER BY: This is used to sort the results of a query in a specific order. You can sort the data
in ascending (default) or descending order based on one or more columns. For example, if
you want to sort employees by their last names, you would use ORDER BY LNAME.

 GROUP BY: This is used to group rows that have the same values in specified columns into
summary rows. It's often used with aggregate functions like COUNT, SUM, or AVG to perform
calculations on each group. For example, if you want to group employees by their
department and count how many employees are in each department, you would use GROUP
BY DNO.

Q10. What are Super key, Candidate Key, Primary Key and Foreign Key?

Superkey: A set of columns that uniquely identifies each row in a table, but may include extra
columns. For example, ID and Email together can be a superkey.

Candidate Key: A minimal set of columns that can uniquely identify each row. There can be
multiple candidate keys, such as ID or Email, if either alone can uniquely identify rows.

Primary Key: A selected candidate key used to uniquely identify each row. It must be unique and
not null, like ID.

Foreign Key: A column in one table that refers to the primary key in another table, creating a link
between the two tables. For example, an Employee table may use DNO to link to the Department
table’s primary key.

Q11. What are the differences between DROP, TRUNCATE and DELETE commands?

DROP

The DROP command completely removes a table or database, including all data, structure, and
indexes. This action is irreversible and cannot be undone.

TRUNCATE

The TRUNCATE command deletes all rows from a table but keeps the table structure. It’s faster
than DELETE and doesn’t log individual deletions, but it is also irreversible.

DELETE

The DELETE command removes specific rows based on a condition and logs each deletion. It
preserves the table’s structure and indexes, allowing for selective data removal.