Unit - 1

Short Questions:-

1. What is DBMS? Give example of DBMS.

Ans:
 Database management system is software that is used to manage the database.
 The database is a collection of inter-related data, which is used to retrieve, insert and delete
the data efficiently. It is also used to organize the data in the form of a table, schema, views,
and reports, etc.
 For example: The college Database organizes the data about the admin, staff, students and
faculty. Using the database, you can easily retrieve, insert, and delete the information.
 MySQL, Oracle, etc. are a very popular commercial database, which is used in different
applications.
2. Which is typical Operation Performed on database?
Ans:
There are multiple categories of operations that are performed on a relational database file
The categories are:
Data Definition operations - creating database, files, file groups, tables, views etc.
Data Manipulation operations - Insert, Update, Delete data from objects.
Data Control operations - GRANT, REVOKE etc.
Transaction Control Operations - ROLLBACK,COMMIT.
Database Maintenance Operations - BACKUP, RESTORE, REBUILD etc.
3. Draw the structure of DBMS.
Ans:

4. Give the uses of DBMS.

Ans: A Database Management Software or DBMS is used for storing, manipulating, and managing
data in a database environment. It enables users to design a personalized database to meet their
analytics and reporting needs. Database design also supports creating, implementing, and
maintaining an organization-wide data management system.
 Generally, most DBMS packages allow users to perform operations like database creation, storing
data, updating data through SQL queries.

5. Give the following definition.

Data:
Data are the known facts or figures that have implicit meaning. It can also be defined, as it is the
representation of facts, concepts or instructions in a formal manner, which is suitable for
understanding and processing. Data can be represented in alphabets (A-Z, a-z), digits (0-9) and
using special characters (+,-.#,$, etc) .
e.g.: 25, “ajit” etc.
Information:
Information is the processed data on which decisions and actions are based. Information can be
defined as the organized and classified data to provide meaningful values.
E.g.: “The age of Ravi is 25”.
Database:
A database is organized collection of related data of an organization stored in formatted way,
which is shared by multiple users.
The main feature of data in a database are:
1. It must be well organized
2. It is related
3. It is accessible in a logical order without any difficulty
4. It is stored only once
For example consider the roll no, name, address of a student stored in a student file. It is collection
of related data with an implicit meaning. Data in the database may be persistent, integrated and
shared.
Record:
A record is a data structure that can hold data items of different kinds. Records consist of different
fields, similar to a row of a database table.
Fields:
A database field is a set of data values, of the same data type, in a table. It is also referred to as a
column or an attribute.Most databases also allow fields to hold complex data like pictures, entire
files, and even movie clips.
Database file:
A database is a collection of data that has been organized to be accessible and manageable to
make data access quick and easy. Database Files are data files that are used to store the contents
of the database in a structured format into a file in separate tables and fields. Database files are
commonly used by dynamic websites (eg. Facebook, Twitter, etc.) to store data.
6. List advantages of DBMS.
 Better data security
 Better data transferring
 Better data integration
 Minimized Data Inconsistency
 Faster data Access
 Better decision making
 Increased end-user productivity
 Simple
 7. List disadvantages of DBMS.
 High Cost
 Huge Size
 Database Failure
 Complexity
 Increased Staff cost
 Requirement of Technical staff
8. Define: tuple, attributes and domain
Tuple:A single row of a table, which contains a single record for that relation, is called a tuple.
Attributes:In general, an attribute is a characteristic. In a database management system (DBMS),
an attribute refers to a database component, such as a table.
It also may refer to a database field. Attributes describe the instances in the column of a database.
Domain: A domain is essentially a data type with optional constraints (restrictions on the allowed
set of values). It is a pool of values from where one or more attributes can draw their actual values.
9. Define super key and candidate key.
Super key is a single key or a group of multiple keys that can uniquely identify tuples in a
table.Super Key can contain multiple attributes that might not be able to independently identify
tuples in a table, but when grouped with certain keys, they can identify tuples uniquely.
Candidate key: CANDIDATE KEY in SQL is a set of attributes that uniquely identify tuples in a table.
Candidate Key is a super key with no repeated attributes. The Primary key should be selected from
the candidate keys. Every table must have at least a single candidate key.
10. What is relationship? List type of relationship.
Ans:
A relationship between two database tables presupposes that one of them has a foreign key that
references the primary key of another table.
There are 3 main types of relationship in a database:
1. one-to-one
2. one-to-many
3. Many-to-many.

Long Questions:-

1. Explain terminology of relational data model.

Ans: The term relational database was originally defined by and is attributed to Edgar Coddat IBM
Almaden Research Center in 1970.Relational Database theory uses a set of mathematical terms, which
are equivalent toSQL database terminology.

Relation is defined as a set of tuples that have the sameattribute.

 Only applies to logical structure of the database, not the physical structure.
 A relation is usually described as a table which is organized in to rows andcolumns.
a) Attribute- Each column in a Table. Attributes are the properties which define a relation. e.g.,
Student_Rollno, NAME,etc.
b) Tables – In the Relational model the, relations are saved in the table format. It is stored along
with its entities. A table has two properties rows and columns. Rows represent records and
columns represent attributes.
c) Tuple – It is nothing but a single row of a table, which contains a single record.
d) Relation Schema: A relation schema represents the name of the relation with its attributes.
 e) Degree- The total number of attributes which in the relation is called the degree of the
relation.
f) Cardinality: Total number of rows present in the Table.
g) Column: The column represents the set of values for a specific attribute.
h) Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation
instances never have duplicate tuples.
i) Relation key – Every row has one, two or multiple attributes, which is called relation key.
j) Attribute domain – Every attribute has some pre-defined value and scope which is known as
attribute domain.
2. Explain the three – schema architecture for a Database Management system.
 Ans: The three-schema architecture describes how the data is represented or viewed by the user
in the database. This architecture is also known as three-level architecture and is sometimes called
ANSI/ SPARC architecture.
 The three schema architecture divides the database into three-level to create a separation
between the physical database and the user application. In simple words, this architecture hides
the details of physical storage from the user. The database administrator (DBA) should be able to
change the structure of database storage without affecting the user’s view.
 This architecture contains three layers or levels of the database management system:

o Internal or Physical level: This is the lowest level of database abstraction. It describes how the
data is actually stored in the database and provides methods to access data from the database.
It allows viewing the physical representation of the database on the computer system. The
interface between the conceptual schema and the internal schema identifies how an element
in the conceptual schema is stored and how it may be accessed.
o Conceptual or Logical/Global level: The conceptual level describes the structure of the whole
database. This level acts as a middle layer between the physical storage and user view. It
explains what data to be stored in the database, what relationship exists among those data,
and what the datatypes are. There is only one conceptual schema per database.
o Database administrator and the programmers work at this level. This level does not provide
any access or storage details but concentrates on the relational model of the database. The
conceptual schema also includes features that specify the checks to retain integrity and
consistency.
o External or View/User level: This is the highest level of database abstraction. External or view
level describes the actual view of data that is relevant to the particular user. This level also
provides different views of the same database for a specific user or a group of users. An
 external view provides a powerful and flexible security mechanism by hiding the parts of the
database from a particular user.
3. Write advantages and disadvantages of DBMS.
Advantages:
 Controls database redundancy: It can control data redundancy because it stores all the data in
one single database file and that recorded data is placed in the database.
 Data sharing: In DBMS, the authorized users of an organization can share the data among
multiple users.
 Easily Maintenance: It can be easily maintainable due to the centralized nature of the
database system.
 Reduce time: It reduces development time and maintenance need.
 Backup: It provides backup and recovery subsystems which create automatic backup of data
from hardware and software failures and restores the data if required.
 multiple user interface: It provides different types of user interfaces like graphical user
interfaces, application program interfaces
Disadvantages:
 Cost of Hardware and Software: It requires a high speed of data processor and large
memory size to run DBMS software.
 Size: It occupies a large space of disks and large memory to run them efficiently.
 Complexity: Database system creates additional complexity and requirements.
 Higher impact of failure: Failure is highly impacted the database because in most of the
organization, all the data stored in a single database and if the database is damaged due to
electric failure or database corruption then the data may be lost forever.
4. Explain classification of user of DBMS.
Naive user:
 Users who need not be aware of the presence of the database system or anyother system
supporting their usage are considered naïveusers.
 A user of an automatic teller machine falls in thiscategory.
 The user is instructed through each step of a transaction; he or she responds by a coded
key or entering a numeric value.
 The operations that can be performed by this class of users are very limited and affect a
precise portion of the database; in the case of the users of theautomatic teller machine,
only one or more of her or his ownaccounts.
 Other is end users of the database who work through a menu-oriented application
program where the type and range of responsorial ways indicated to the users.
 Thus, a very competent database designer could be allowed to use a particular database
system only as a naïveuser.

Online Users:
 There are users who may communicate with the database directly via an online
terminal or indirectly via a user interface and application program.
 These users are aware of the presence of the database system and may have acquired
a certain amount of expertise in the limited interaction they are permitted with the
database through the intermediary of the application program.
 Online users can also be naïve users requiring additional help, such as menus.
Application program:
 Professional programmers who are responsible for developing application programs or
user interfaces utilized by the naïve and online users fall into this category.
  The application programs would be written in a general-purpose language such as
Assembler, C, COBOL, FORTRAN, Pascal, or PL/I and include the commandsrequired to
manipulate thedatabase.
DatabaseAdministrator
 Centralized control of the database is exerted by a person or group of persons under
the supervision of a high-level administrator. This person or group is referred to as the
database administrator (DBA).
 They are the users who are most familiar with the database and are responsible for
creating, modifying, and maintaining its three levels.
 The DBA is responsible for granting permission to usersofthe database and stores the
profile of each user in the database. The user profile can be used by the database
system to verify that a particular user can perform a given operation on the database.
 The DBA is also responsible for defining procedures to recover the database from
failures due to human, natural, or hardware causes with minimal loss of data.
5. Explain relational and network data model.
Ans: Relational model:
 A relational data base matches data by using common characteristics found within the dataset.
 The resulting groups of data are organized and are much easier for many people to
understand.
 One of the main reasons for introducing this model was to increase the productivity of the
application programmer by eliminating the need to change application programs when a
change is made to the database. Users need not know the exact physical structures to use the
database and are protected from any changes made to these structures. They are, however,
still required to know how the data has been partitioned into the various relations.
 For example, a data set containing all the real estate transactions in a town can be grouped by
the year each transaction occurred, the sale price, a buyer’s last name and soon. Such a
grouping uses the relational model (a technical term for this is schema). Hence, such a
database is called a relational database. The software used to do this grouping is called a
relational database management system (RDBMS). The term “relational database” often refers
to this type of software.
 Advantages:
o Simplicity – The design is simple and more logical.
o Data Integrity – The data can always referred to the parent data for data integrity.
 Disadvantages:
o Navigation through the hierarchical database is a problem. To locate any data, one
has to start from the root and navigate down from parent to child and reach the
desired record.
o If the relationships are one-many, then manipulations are required.
 Example:

In the network model, two departments Y and Z come under Project A and Project B. That is,
the elements of this model represent many to many relationships, which is difficult to handle
in hierarchical model.
Network model:
 The popularity of the network data model coincided with the popularity of thehierarchical
datamodel.
 The network data model (NDM) represents data for an entity set by a logicalrecordtype.
 The data for an instance of the entity set is represented by a record occurrence of the record
type.
 Some data were more naturally modeled with more than one parent per child. Therefore, the
network model permitted the modeling of many-to-many relationships in data.
 In 1971, the Database Task Group of the Conference on Data SystemsLanguages (DBTG /
CODASYL) formally defined the network model. The basicdata-modeling construct in the
network model is the set construct.
 A set consists of an owner record type, a set name, and a member record type.
 A member record type can have that role in more than one set; hence, the multi parent
concept is supported.
 An owner record type can also be a member or owner in another set.
 The data model is a simple network, and link and intersection record types (called junction
records by IDMS) may exist, as well as sets between them. Thus, the complete network of
relationships is represented by several pair wise sets; in each set some (one) record type is
owner (at the tail of the network arrow) and one or more record types are members (at the
head of the relationship arrow).
 Usually, a set defines a 1:M relationship, although 1:1 is permitted. The CODASYL network
model is based on mathematical set theory.
 Advantages:
o The network database model is a great improvement over Hierarchical model. It is also
conceptually simple and easy to operate.
o It has the capability to operate one to one and many to many relationships.
o The data access is easier.
o The network model is very useful for high volume operations encountered in powerful
mainframes.
o Generally, changes in data characteristics do not require changes in application
programs, that are a kind of data independence built in.
 Disadvantages:
o The model has inherent complexities. Hence the user must be quite acquainted with
the model to accomplish a query or an update.
o The data access method is navigational.

In the network model, two departments Y and Z come under Project A and Project B. That is,
the elements of this model represent many to many relationships, which is difficult to handle
in hierarchical model.
 6. Explain hierarchical data model.

Ans: The hierarchical data model (HDM) organizes the data in a treestructure.
 The nodes of the tree are the record types representing the entity sets andare connected by
pointers or links.
 A pointer or link as in the network data model represents a relationshipbetween exactly two
records. However, in the hierarchical model thisrelationship, as in the genealogical (family)
tree, is that of a parent and child.
 To create links between these record types, the hierarchical model uses Parent Child
Relationships.
 There is a hierarchy of parent and child data segments.
 This structure implies that a record can have repeating information, generally in the child data
segments.
 Data in a series of records, which have a set of field values attached to it. It collects all the
instances of a specific record together as a record type. These record types are the equivalent
of tables in the relational model, and with the individual records being the equivalent of rows.
A parent record type can have any number of children record types. Two record types in a
hierarchical tree can have at most one relationship between them andthis relationship is that
of one-to-one or one-to-many.
 i.e. it represent a one-to-many relationship between two entities where thetwo are
respectively parent andchild
 For example, an organization might store information about an employee, such as name,
employee number, department, salary. The organization might also store information about an
employee's children,such as name and date of birth. The employee and children data forms
ahierarchy, where the employee data represents the parent segment and thechildren data
represents the childsegment.
 If an employee has three children, then there would be three child segmentsassociated with
one employee segment.
 In a hierarchical database, the parent-child relationship is one to many. This restricts a child
segment to having only one parentsegment. Hierarchical DBMSs were popular from the late
1960s, with the introduction of IBM's Information Management System (IMS) DBMS, through
the1970s.
7. Explain Hierarchical and relationship with example.
8. Explain type of relationship with example.
Ans: There are essentially three types of relationships:
 One-to-one(1: 1) For every record in the primary table, there is one and only one record in the
foreign table.
 One-to-many(1 : M) For every record in the primary table, there are one or more related records
in the foreign table.
 Many-to-many (M : M)For every record in the primary table, there are many related records in the
foreign table, and for every record in the foreign table, there are many related records in the
primary table.
 One – to one relation: In One-to-One relationship, one record of the first table will be linked to
zero or one record of another table. For example, each employee in the Employee table will have a
corresponding row in Employee Details table that stores the current passport details for that
particular employee. So, each employee will have zero or one record in the Employee Details table.
This is called zero or one-to-one relationship.
 Here, EmployeeID column is the primary key as well as foreign key column in
the EmployeeDetails table that linked to EmployeeID of the Employee table. This forms zero or
one-to-one relation.

• One-to-many relation : One-to-Many is the most commonly used relationship among tables. A
single record from one table can be linked to zero or more rows in another table.
• he Employee and Address tables are linked by the key column EmployeeID. It is a foreign key in
the Address table linking to the primary key EmployeeID in the Employee table. Thus, one record
of the Employee table can point to multiple records in the Address table. This is a One-to-Many
relationship.

 Many-to-Many relationship : Many-to-Many relationship lets you relate each row in one table
to many rows in another table and vice versa. As an example, an employee in
the Employee table can have many skills from the EmployeeSkill table and also, one skill can be
associated with one or more employees.
 The following figure demonstrates many-to-many relation
between Employee and SkillDescription table using the junction table EmployeeSkill.
 In the example, the EmployeeSkill is the junction table that
contains EmployeeID and SkillID foreign key columns to form many-to-many relation between
the Employee and SkillDescription table. Individually, the Employee and EmployeeSkill have a
 one-to-many relation and the SkillDescription and EmployeeSkill tables have one-to-many
relation. But, they form many-to-many relation by using a junction table EmployeeSkill.

9. Explain various key Constraints.

Ans:

 Constraints can be specified when the table is created with the CREATE TABLE statement, or after
the table is created with the ALTER TABLE statement.

 Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. If there is any violation between the constraint and the data action,
the action is aborted.
 Constraints can be column level or table level. Column level constraints apply to a column, and table
level constraints apply to the whole table.

 The following constraints are commonly used in SQL:

o NOT NULL - Ensures that a column cannot have a NULL value
o UNIQUE - Ensures that all values in a column are different
o PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in
a table
 o FOREIGN KEY - Prevents actions that would destroy links between tables
o CHECK - Ensures that the values in a column satisfies a specific condition
o DEFAULT - Sets a default value for a column if no value is specified
1. SQL PRIMARY KEY Constraint :
 The PRIMARY KEY constraint uniquely identifies each record in a table.
 Primary keys must contain UNIQUE values, and cannot contain NULL values.
 A table can have only ONE primary key; and in the table, this primary key can consist of single or
multiple columns (fields).
 A Single column Primary key is called a Simple key.
 A multicolumn primary key is called a Composite Primary key.
 Features Of Primary Key:
o Primary key is a column or set of column which uniquely identifies a record in a database
table.
o Primary keys must contain unique values means not contain duplicate value.
o Primary key column can not contain NULL values.
o Primary Key is not compulsory but necessary.
o Primary Key cannot be Long or Double.
o Only one Primary Key is allow at column level portable
o Unique Index created automatically if there is a Primary key.
o One table can combine up to 16 columns in a Composite Primary Key.
 Primary key constraint at column level syntax : <columnName><Datatype>(<size>) PRIMARYKEY

Example:

The following SQL creates a PRIMARYKEY on the "S_Id" column when the "Student" table is created:
CREATE TABLE Student
(
S_Id number(3) NOT NULL PRIMARY KEY,
Name varchar(20) NOT NULL,
Address varchar(25));
 Primary key constraint at table level syntax : PRIMARY KEY(<columnName>,<columnName>)

Example: The following SQL creates a PRIMARYKEY on the "S_Id" column when the "Student" table is
created:
CREATE TABLE Student
(
S_Id number(3) NOT NULL,
Name varchar(20) NOT NULL,
Address varchar(25),
PRIMARY KEY(S_Id));
Dropping constrains:
 Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP
CONSTRAINT option.
 For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following
command:
ALTER TABLE EMPLOYEES DROPCONSTRAINT EMPLOYEES_PK; OR
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
 2. Foreign key:
 The FOREIGN KEY represents relationship between tables. A foreign key is a column (or a group of
columns) whose values are derived from the primary key or unique key of same or some other
table.
 Foreign key defined table is called foreign table or Detail table.
 Primary key or unique key defined table is called Primary table or Master table. ‘References’
keyword is used to define foreign key.
 Features Of Foreign Key:
 A table can have more than one foreign key.
 Record cannot INSERT or UPDATE of a value, if a corresponding value does not exist in the primary
key table Primary key column can not contain NULL values.
 Parent must reference a PRIMARY KEY or UNIQUE column(s) in primary key table.
 Foreign key can be specified on child but not on parent.
 Master table cannot be updated if child record exists.
 Foreign key constraint at column level syntax : <columnName><Datatype>(<size>)
REFERENCES<tableName>(<ColumnName>)
 Example: The following SQL creates a FOREIGN KEY on the "S_Id" column when the "Student" table
is already created:
CREATE TABLE Marks
(
S_Id number(3) REFERENCES student(s_no),
Marks1 number(3) NOT NULL,
Marks2 number(3) NOT NULL,
Marks3 number(3) NOT NULL);
 Foreign key constraint at table level syntax :
FOREIGN KEY (<columnName> [,<columnName>])
REFERENCES <tableName>(<ColumnName>,< ColumnName>)
Example: The following SQL creates a FOREIGN KEY on the "S_Id" column when the "Student" table
is already created:
CREATE TABLE Marks
(
S_Id number(3),
Marks1 number(3) NOT NULL,
Marks2 number(3) NOT NULL,
Marks3 number(3) NOT NULL,
FOREIGN KEY (S_Id) REFERENCES student (s_no));
3. UNIQUE KEY:
 The UNIQUE KEY column constraint permits multiple entries of NULL INTO THE COLUMN.
 UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of
columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
 Features Of Unique Key:
 Unique keys will not contain duplicate value.
 Unique Index created automatically.
 Unique key can combine up to 16 columns in a Composite Unique Key.
 Unique Key cannot be Long or Long Raw datatype.
 Unique key constraint at column level syntax : <columnName><Datatype>(<size>)UNIQUE
 Example: The following SQL creates a PRIMARYKEY on the "S_Id" column when the "Student" table is
created:
CREATE TABLE Student
(
S_Id number(3) UNIQUE ,
Name varchar(20) NOT NULL,
Address varchar(25));
 Unique key constraint at table level syntax : UNIQUE(<columnName1>,<columnName2>)
 Example: The following SQL creates a PRIMARYKEY on the "S_Id" column when the "Student" table is
created:
CREATE TABLE Student
(
S_Idnumber(3),
Name varchar(20) NOT NULL,
Address varchar(25),
UNIQUE (S_Id));
4. NOT NULL Cosntraint:
Note: not null constraint can only be define at column level.
 Syntax: <columnName><Datatype>(<size>) NOTNULL
 Example: Not null constraint defined at column name of student in "Student" table is created:
CREATE TABLE Student
(
S_Id number(3) UNIQUE ,
Name varchar(20) NOT NULL,
Address varchar(25));
5. Check constraint:
 The check constraints must be specify the constraints as a logical expression that evaluate either TRUE
or FALSE (due to a null).
 The condition of a CHECK constraint can refer to any column in the table, but it cannot refer to
columns of other tables.
 Restrictions on CHECK Constraints:
 The condition must be a BOOLEAN expression that can be evaluated using the values in the row being
inserted or updated.
 The condition cannot contain sub queries or sequences.
 The condition cannot include the SYSDATE, UID, USER or USERENV SQL functions.
 Check Constraint Defined At Column Level syntax: <columnName><Datatype>(<size>) CHECK
(<Logical Expression>)
 Example: The following SQL check data inserted into column on "S_Id" must start with ‘S’.
CREATE TABLE Student
(
S_Id number(3) CHECK(s_id LIKE ‘S%’),
Name varchar(20) NOT NULL,
Address varchar(25));
 Check Constraint Defined At table Level syntax: CHECK (<Logical Expression>)
 Example: The following SQL check data inserted into column on "S_Id" must start with S.
CREATE TABLE Student
(
S_Id number(3),
Name varchar(20) NOT NULL,
Address varchar(25),
CHECK(s_id LIKE ‘S%’));