Basics

Question 1

DataBase

Answer 1

The database is a collection of inter-related data which helps in efficient retrieval, insertion, and deletion of data from the database and organizes the data in the form of tables, views, schemas, reports, etc.

Question 2

DDL

Answer 2

DDL is the short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

Question 3

DDL commands

Answer 3

Data definition language
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

It helps in the creation, modification, and removal of definitions that define the organization of data in the database.

Question 4

DML

Answer 4

DML is the 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.

Question 5

DBMS

Answer 5

Database Management System: The software which is used to manage the database is called Database Management System (DBMS). For Example, MySQL, Oracle, etc. are popular commercial DBMS used in different applications

Question 6

Why not File System?

Answer 6

Redundancy of data
Inconsistency of Data
Difficult Data Access
Unauthorized Access
No Concurrent Access( access of same data by multiple users at the same time is known as concurrency)
No Backup and Recovery

Question 7

Data Independence

Answer 7

Data independence means a change of data at one level should not affect another level.

Question 8

Physical Data independence

Answer 8

Physical Data Independence: Any change in the physical location of tables and indexes should not affect the conceptual level or external view of data. This data independence is easy to achieve and implemented by most of the DBMS.

The physical level of a database describes how the data is being stored in secondary storage devices like disks and tapes

Question 9

Conceptual data independence

Answer 9

Conceptual Data Independence: The data at conceptual level schema and external level schema must be independent.
This means a change in conceptual schema should not affect external schema.
e.g.; Adding or deleting attributes of a table should not affect the user’s view of the table. But this type of independence is difficult to achieve as compared to physical data independence because the changes in conceptual schema are reflected in the user’s view.

Question 10

Entity and Entity set

Answer 10

An Entity is an object of Entity Type and a set of all entities is called an entity set. e.g.; E1 is an entity having Entity Type Student and the set of all students is called Entity Set.
Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age, Address, Mobile_No are the attributes that define entity type Student.

Question 11

Composite Attribute

Answer 11

An attribute composed of many other attributes is called a composite attribute. For example, the Address attribute of student Entity type consists of Street, City, State, and Country.

Question 12

Relationship between entities

Answer 12

A relationship type represents the association between entity types. For example, ‘Enrolled in’ is a relationship type that exists between entity type Student and Course.

Question 13

Cardinality

Answer 13

The number of times an entity of an entity set participates in a relationship set is known as cardinality.
types:
One to one
many to one
many to many

Question 14

Participation Constraint:

Answer 14

Total Participation – Each entity in the entity set must participate in the relationship. If each student must enroll in a course, the participation of the student will be total.
Partial Participation – The entity in the entity set may or may NOT participate in the relationship. If some courses are not enrolled by any of the students, the participation of the course will be partial.

Question 15

Weak Entity

Answer 15

an entity type has a key attribute that uniquely identifies each entity in the entity set. But there exists some entity type for which key attributes can’t be defined. These are called the Weak Entity types

For example, A company may store the information of dependants of an Employee. But the dependents don’t have existed without the employee. So Dependent will be weak entity type and Employee will be Identifying Entity type for Dependant.

The participation of the weak entity type is always total.

Question 16

Participation of weak entity is ——

Answer 16

Total

Question 17

ER diagram

Answer 17

Entity-Relationship (ER) Diagram is a diagrammatic representation of data in databases, it shows how data is related.

Question 18

Generalization

Answer 18

Generalization is the process of extracting common properties from a set of entities and create a generalized entity from it.

It is a bottom-up approach in which two or more entities can be generalized to a higher-level entity if they have some attributes in common.

Question 19

Specialization

Answer 19

In specialization, an entity is divided into sub-entities based on its characteristics.
It is a top-down approach where the higher-level entity is specialized into two or more lower-level entities.

Question 20

The relational model represents data in the form of ____

Answer 20

Relations or Tables

Question 21

Relational Instance:

Answer 21

The set of values present in a relationship at a particular instance of time is known as relational instance

Question 22

In a row of a relational table, how many values can an attribute have?

Answer 22

In a row of a relational table, an attribute can have exactly one value or a NULL value

Question 23

Relational model

Answer 23

The relational model was proposed by E.F. Codd to model data in the form of relations or tables.

Question 24

Relation schema

Answer 24

STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is relation schema for STUDENT.
A relation schema represents the name of the relation with its attributes.

Question 25

Degree of relation

Answer 25

The number of attributes in the relation is known as the degree of the relation.

Question 26

Constraints in Relational Model

Answer 26

While designing the Relational Model, we define some conditions which must hold for data present in the database are called Constraints. These constraints are checked before performing any operation (insertion, deletion, and updation) in the database. If there is a violation of any of the constraints, the operation will fail.

Question 27

Types of constraints

Answer 27

Domain Constraints
Key Integrity:It should be unique for all tuples and cannot be NULL
Referential Integrity:

Question 28

Anomalies in referencing relation

Answer 28

Insertion Anomaly in Referencing Relation:
We can’t insert a row in REFERENCING RELATION if referencing attribute’s value is not present in the referenced attribute value.
Deletion/ Updation Anomaly in Referenced Relation: We can’t delete or update a row from REFERENCED RELATION if the value of REFERENCED ATTRIBUTE is used in the value of REFERENCING ATTRIBUTE.

Question 29

Keys

Answer 29

Any set of attributes that allows us to identify unique rows (tuples) in a given relationship are known as super keys.
Out of these super keys, we can always choose a proper subset among these which can be used as a primary key. Such keys are known as Candidate keys. I
f there is a combination of two or more attributes that is being used as the primary key then we call it a Composite key.

Question 30

Difference between super key and primary key

Answer 30

The super key is different from the primary and candidate keys in the sense that only the minimal superkeys are the candidate/primary keys.

Question 31

Why data normalization?

Answer 31

To reduce data redundancy

Question 32

First Normal Form

Answer 32

a relation is in first normal form if it does not contain any composite or multi-valued attribute.

Question 33

Second normal form

Answer 33

A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

Partial Dependency – If the proper subset of candidate keys determines non-prime attributes, it is called partial dependency.

Question 34

Third normal form

Answer 34

A relation is in the third normal form if there is no transitive dependency for non-prime attributes as well as it is in the second normal form.

A relation is in 3NF if at least one of the following conditions holds in every non-trivial function dependency X –> Y
X is a super key.
Y is a prime attribute (each element of Y is part of some candidate key).

Question 35

BCNF

Answer 35

A relation is in BCNF iff in every non-trivial functional dependency X –> Y, X is a super key.

Question 36

DCL

Answer 36

Data Control Language which acts as an access specifier to the database.(basically to grant and revoke permissions to users in the database

Question 37

ER models which view

Answer 37

ER Model is used to model the logical view of the system from data perspective which consists of these components:

Entity, Entity Type, Entity Set