Q.1 What is second normal form?
Explain with the help of an
example.
ANSWER: - A database is in second normal form if it satisfies the
following conditions:
It is in first normal form
All non-key attributes are fully functional dependent on the
primary key
In a table, if attribute B is functionally dependent on A, but is not
functionally dependent on a proper subset of A, then B is considered
fully functional dependent on A. Hence, in a 2NF table, all non-key
attributes cannot be dependent on a subset of the primary key. Note that
if the primary key is not a composite key, all non-key attributes are
always fully functional dependent on the primary key. A table that is in
1st normal form and contains only a single key as the primary key is
automatically in 2nd normal form.
The primary key provides a means to uniquely identify each row in a
table. When we talk about columns depending on the primary key, we
mean, that in order to find a particular value, such as what color is Kris’
hair, you would first have to know the primary key, such as an
Employee ID, to look up the answer.
When all the columns relate to the primary key, they naturally share a
common purpose, such as describing an employee. We can say that
when a table is in second normal form, it has a single purpose, such as
storing employee information.
2nd Normal Form Example
Consider the following example:
This table has a composite primary key [Customer ID, Store ID]. The
non-key attribute is [Purchase Location]. In this case, [Purchase
Location] only depends on [Store ID], which is only part of the primary
key. Therefore, this table does not satisfy second normal form.
To bring this table to second normal form, we break the table into two
tables, and now we have the following:
What we have done is to remove the partial functional dependency that
we initially had. Now, in the table [TABLE_STORE], the column
[Purchase Location] is fully dependent on the primary key of that table,
which is [Store ID].
Q.2 Write a short note on:
a) Traditional file keeping system
b) DBMS
ANSWER: -
a) Traditional File System
Before the use of a computer, a manual file system was used to maintain
the records and files. Data were stored and processed using a traditional
file system and it makes it easy to find any information. In this
traditional file system, each file is independent of other file and data in
the different file can be integrated only by writing an individual program
for each application. The data and application program that uses the data
are arranged that any change to data requires modification of all the
programs that use the data. Sometimes, it is not possible to identify all
the programs using data and identified on trial and error basis. All
functional areas in the organization create, processes its own files.
The files such as inventory and payroll generate separate files and do not
communicate with each other. The organization was simple to generate
and had better local control but the data of an organization is dispersed
throughout the functional subsystem.
Components of Traditional File System
Hardware, Software, data manager, DB Engine, User, Data, Database,
Database access language, Server, Run Time Manager.
Applications of Traditional File System
The application is developed for a specific purpose and they will access
the database only.
Uses of Traditional File System
Language-specific run-time libraries
User programs use file system APIs to make requests of the file
system
Data transfer
Positioning.
Updating metadata
Managing directories.
Managing access specifications.
Removal
Advantages of Traditional File System
Simple to use.
Less complex.
Minimal investment (Not make the investment in software because
it allows us to transport files from one to another computer).
No requirement of the specialist.
Disadvantages of Traditional File System
Data redundancy (Each application has its own data file so; same
data may have to be recorded and stored in many times).
Data inconsistency (Due to the same data items that appear in more
than one file do not get updated simultaneously in each file).
Data dependence (Program and application in the file processing
system are data dependent but, the problem is incompatible with
file format).
Limited data sharing.
The problem with security.
Retrieval (retrieval is not easy).
Time-consuming.
Inefficient to maintain the record of the big firm having many
items.
Required Lots of labor work to do.
Functions of Traditional File System
Store and arrange the computer files.
Stored files into database, manipulation, and retrieval by the
computer's operating system.
Perform services for the end-users, such as updating, insertion,
deletion adding new files to database etc.
Each program defines and manages its data.
Features of Traditional File System
It stores data in a group of files.
Files data are dependent on each other.
C/C++ and COBOL languages were used to design the files.
It is very difficult to maintain the traditional file processing
system.
The traditional file system is also called a flat file system.
b) DBMS (Database Management System)
We must know about what is data, database, and management first for
knowing more about DBMS. So, Data is a collection of facts and figure
that can be recorded; it can be in text, number, speech, video, image.
Database means a huge amount of inter-related data is stored, retrieved
and collect at one place in the database; In short, it is a collection of
inter-related data stored in the database. Management is a collection of
the program for security manage, retrieved and stored the data.
After knowing about data, database and management you can assume
that what is DBMS. So, Basically DBMS is a collection of interrelated
data stored in a database server; these data's will be stored in the form of
tables. The aim of the database is to provide a way to store and retrieve
database information in a fast and efficient manner. database system not
only contains data's but it contains a complete definition or description
of the database structure and constraints.
The first DBMS introduced by IBM in 1960's named Apollo (mission
for moon). DB2 is the famous open source code (in which we can
modify the code) and free source code (where code cannot be changed
but, we can use it with its source code) database. EF-CODD (Father of
RDBMS) was the first who describe database function and create a first
latest version of the database.
Components of DBMS
Hardware, Software, data manager (Command Checker), DB Engine,
User, Data Dictionary (Structure), Data, Database, Database access
language, Query Processor (Find and solve queries), Server, Run Time
Manager.
Applications of DBMS
Airlines, Universities, Banking, Wave, Human Resources,
Manufacturing, and selling etc.
Uses of DBMS
Effective and efficient management of data.
Query processing and management.
Security and integrity of data.
Data sharing and storage.
Advantages of DBMS
Less space consumes.
Reduction of redundancy.
Data integrity, security, and continuity.
Backup and recovery process.
The data model can be developed.
Concurrency control.
Data independence.
Performance is good.
Disadvantages of DBMS
Required large size of memory.
Time-consuming.
Required a processor with the high speed of data processing.
Cost of data conversion.
Database failure (DB corrupted due to power failure or whole
system stops).
Expensive.
Complex.
Higher impact of a failure (if any component can bring the
operation to a halt).
Cost of DBMS (depend on the environment, function or recurrent
annual maintenance cost.
Functions of DBMS
Data dictionary management.
Modify, delete, insert the data.
Security.
Control multi-user access management.
Achieved data integrity.
Transaction management.
Data transformation and presentation.
Features of DBMS
Persistence – permanent data stored.
Validity – validation of field.
Consistency – the value of data is the same at all place.
Security.
Non-redundancy – no multiple copies.
Independence.
Concurrency – multiple users can share a file at same time
