Q1) Attempt any EIGHT of the following (Out of TEN) : [8 × 2 = 16]
1.What
is Specialization ? Give Example
- Specialization
is a top-down approach, and it is opposite to Generalization. In
specialization, one higher level entity can be broken down into two lower
level entities.
- Specialization
is used to identify the subset of an entity set that shares some
distinguishing characteristics.
- Normally,
the superclass is defined first, the subclass and its related attributes
are defined next, and relationship set are then added.
For example: In an Employee management system, EMPLOYEE entity can be
specialized as TESTER or DEVELOPER based on what role they play in the company.
2.
Define DBMS ?
1.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.
2.It provides protection and security to the database.
In the case of multiple users, it also maintains data consistency.
3.What is Aggregation ? Give Example
In aggregation, the relation between two entities is
treated as a single entity. In aggregation, relationship with its corresponding
entities is aggregated into a higher level entity.
4.What is Insertion Anomaly?
If there is a new row inserted in the table and
it creates the inconsistency in the table then it is called the insertion
anomaly. For example, if in the above table, we create a new row of a worker,
and if it is not allocated to any department then we cannot insert it in the
table so, it will create an insertion anomaly.
5. What is Generalization ?Give Example
1. Generalization is like a bottom-up
approach in which two or more entities of lower level combine to form a higher
level entity if they have some attributes in common.
2. In generalization, an entity of a
higher level can also combine with the entities of the lower level to form a
further higher level entity.
3. Generalization is more like subclass
and superclass system, but the only difference is the approach. Generalization
uses the bottom-up approach.
4.
In generalization, entities are
combined to form a more generalized entity, i.e., subclasses are combined to
make a superclass.
For example, Faculty and Student entities can be generalized and create a higher level entity Person.
6. Write two categories of Data Models.
1.
Hierarchical Model
2.
Network Model
3.
Entity-Relationship
Model (ER Model)
4.
Relational Model
5.
Object-Oriented Data model
7. Explain character data type of SQL.
CHAR(size) -A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255. Default is 1
VARCHAR(Size) -It is used to specify a variable length string that can contain numbers, letters, and special characters. Its size can be from 0 to 65535 characters.
VARCHAR2(size) - It is used to store
variable string data within the predefined length. It can be stored up to 4000
byte.
8.Explain the use of MAX ( ) with Example.
MAX function is used to find the
maximum value of a certain column. This function determines the largest value
of all selected values of a column.
SELECT MAX(RATE) from Product;
9. Explain Group by Command With Example.
The GROUP
BY statement groups rows that have the same values into summary rows, like
"find the number of customers in each country".
The GROUP BY statement
is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG())
to group the result-set by one or more columns.
SELECT column_name(s)FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
10. Define Data and Information.
Data can
be defined as a representation of facts, concepts, or instructions in a
formalized manner, which should be suitable for communication, interpretation,
or processing by human or electronic machine.
Data is represented with the
help of characters such as alphabets (A-Z, a-z), digits (0-9) or special
characters (+,-,/,*,<,>,= etc.)
Information is
organized or classified data, which has some meaningful values for the
receiver. Information is the processed data on which decisions and actions are
based.
For the decision to be
meaningful, the processed data must qualify for the following characteristics −
·
Timely −
Information should be available when required.
·
Accuracy −
Information should be accurate.
·
Completeness −
Information should be complete.
11. Define Candidate key.
A candidate key is a subset of a super key set where the key which contains no redundant attribute is none other than a Candidate Key. In order to select the candidate keys from the set of super key, we need to look at the super key set.
12.Define Tuple and Domain
A tuple is a row of a relation. A domain is a set of allowable values for one or more attributes.
13. Write two advantages of Sequential file organization.
o
It contains a fast and efficient
method for the huge amount of data.
o
In this method, files can be easily
stored in cheaper storage mechanism like magnetic tapes.
o
It is simple in design. It requires
no much effort to store the data.
o
This method is used when most of the
records have to be accessed like grade calculation of a student, generating the
salary slip, etc.
o
This method is used for report
generation or statistical calculations.
14. Explain the use of MIN( ) with Example.
The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name WHERE condition;
SELECT MIN(Price)FROM Products;
15.What are Attributes ? Enlist types of Attributes.
Attributes are the properties which
describe an entity.
Composite attribute -It can be divided into
smaller sub parts, each sub part can form an independent attribute.
Simple or Atomic attribute -Attributes that cannot be further
subdivided are called atomic attributes.
Single valued Attribute -Attributes having a
single value for a particular item is called a single valued attribute.
Multi-valued Attribute -Attribute having a
set of values for a single entity is called a multi-valued attribute.
Derived Attributes or stored Attributes -
When one attribute value is derived from the other is called a
derived attribute.
16.What is an Alternate key ?
An Alternate Keys of a table are those candidate keys that are not currently selected as the primary key of a table.
17. Define
the term Cardinality.
Cardinality
refers to the uniqueness of data values that are contained in a column.
18. Define Primary Key
The
SQL PRIMARY KEY is a column in a table which must contain a unique value which
can be used to identify each and every row of a table uniquely.
19.
Explain Physical Data Independence
- Physical data
independence can be defined as the capacity to change the internal schema
without having to change the conceptual schema.
- If we do any
changes in the storage size of the database system server, then the
Conceptual structure of the database will not be affected.
- Physical data
independence is used to separate conceptual levels from the internal
levels.
- Physical data
independence occurs at the logical interface level.
20. Explain the use
of COUNT () with example.
Count is the
function to count the number of rows in a table
Select Count(*) from Customers.
21.Define Functional Dependency
The
functional dependency is a relationship that exists between two attributes. It
typically exists between the primary key and non-key attribute within a table.
X → Y
The
left side of FD is known as a determinant, the right side of the production is
known as a dependent.
For example:
Assume we have an employee table
with attributes: Emp_Id, Emp_Name, Emp_Address.
Here
Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table
because if we know the Emp_Id, we can tell that employee name associated with
it.
Functional
dependency can be written as:
Emp_Id → Emp_Name
Emp_Name
is functionally dependent on Emp_Id.
22.What
are attributes ? enlist types of attributes
in dbms
In
a database management system (DBMS), an attribute is a piece of data that describes
an entity. For example, in a customer database, the attributes might
be name, address, and phone number.
Simple
Attribute:
A
simple attribute is atomic and cannot be divided any further. For example, the
"Age" of a person is a simple attribute.
Composite
Attribute:
A
composite attribute is composed of multiple simple attributes. For instance, an
address attribute can be composed of street, city, state, and zip code.
Derived
Attribute:
A
derived attribute is one whose value can be derived or calculated from other
attributes. For example, the "Age" attribute can be derived from the
"Date of Birth" attribute.
Multi-valued
Attribute:
A
multi-valued attribute can hold multiple values for a single entity. For
instance, the "Phone Numbers" attribute for a person may have multiple
phone numbers.
Single-valued
Attribute:
A
single-valued attribute holds only a single value for a particular entity. For
example, the "Date of Birth" attribute for a person is typically a
single-valued attribute.
Composite
Key Attribute:
In
a relational database, a composite key attribute is a combination of two or
more attributes that uniquely identifies an entity.
23.Explain
Logical Data Independence
o
Logical data
independence refers characteristic of being able to change the conceptual
schema without having to change the external schema.
o
Logical data
independence is used to separate the external level from the conceptual view.
o
If we do any
changes in the conceptual view of the data, then the user view of the data
would not be affected.
o
Logical data
independence occurs at the user interface level.
24.Explain the use of AVG() with
example.
The AVG() function in a database is used to
calculate the average value of a set of values. It is often employed in
conjunction with the SELECT statement in SQL to retrieve the average of a
specific column or expression.
Here's the
basic syntax of the AVG() function:
SELECT AVG(column_name) FROM table_name;
