Database Management System (12)
Database Management System
Data
A raw fact about anything which does not give any
complete meaning is data. Data is a set of values of subjects with respect to
qualitative or quantitative variables.
example: Hari, 34, 40, Kathmandu
Information
The processed data which gives some or complete
meaning is called information. Information is organized or classified data,
which has some meaningful values for the receiver.
Information must qualify for the following
characteristics:
Timely − Information should be available when required.
Accuracy − Information should be accurate.
Completeness − Information should be complete.
Database
A database is an organized collection of data,
generally stored and accessed electronically from a computer system.
What is data processing? Write with steps.
1. Data Processing Cycle
Data processing is the re-structuring or re-ordering
of data by people or machine to increase their usefulness and add values for a
particular purpose.
Data processing consists of the basic steps - input,
processing, and output.
2. Collection
The collection of raw data is the first step of the data
processing cycle. The type of raw data collected has a huge impact on the
output produced.
3.
Preparation
Data
preparation or data cleaning is the
process of sorting and filtering the raw data to remove unnecessary and
inaccurate data.
4.
Input
In
this step, the raw data is converted into machine readable form and fed into
the processing unit. This can be in the form of data entry through a keyboard,
scanner or any other input source.
5.
Output
The
data is finally transmitted and displayed to the user in a readable form like
graphs, tables, vector files, audio, video, documents, etc.
6.
Storage
The
last step of the data processing cycle is storage, where data are stored for
further use.
Database Management System (DBMS)
A database management system is software used to
perform different operations, like addition, access, updating, and deletion of
the data, like adding your name in the database for an online retail store as a
customer. A database management system acts as the backbone of a database and
makes using a database a cakewalk as it makes access and management of data a
lot easier.
The software which is used to manage database is
called Database Management System (DBMS). For Example: SQL, Mariadb, MySQL,
Oracle etc. are popular commercial DBMS used in different applications.
Field
A field is a piece of information about an element;
element may be a person, student or employ etc. in table top row like SN, NAME
and PHONE NO are fields.
Record
A collection of related field is called record. A
single row containing of one college data is a record. A database record is
collection of fields about the same person, item, or object in a database. A
single row with one particular information refers as a record.
Primary Key
A primary is a single column value used to identify a
database record uniquely.
It has following attributes
A primary key cannot be NULL
A primary key value must be unique
The primary key values should rarely be changed
The primary key must be given a value when a new record is inserted
Alternate Key
Alternate Key is a column or group of columns in a
table that uniquely identify every row in that table. A table can have multiple
choices for a primary key but only one can be set as the primary key. All the
keys which are not primary key are called an Alternate Key.
Candidate Key
Candidate 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.
Properties of Candidate key:
It must contain unique values
Candidate key may have multiple attributes
Must not contain null values
It should contain minimum fields to ensure uniqueness
Uniquely identify each record in a table
Foreign Key
A foreign key is the one that is used to link
two tables together via the primary key. It means the columns of one table
points to the primary key attribute of the other table. It further means that
if any attribute is set as a primary key attribute will work in another table
as a foreign key attribute.
Database Management System
Database Management Systems (DBMS) are
software systems used to store, retrieve, and run queries on data. A DBMS
serves as an interface between an end-user and a database, allowing users to
create, read, update, and delete data in the database. DBMS manage the data,
the database engine, and the database schema, allowing for data to be
manipulated or extracted by users and other programs. This helps provide data
security, data integrity, concurrency, and uniform data administration
procedures.
Advantages of Database Management System
1. Improved data sharing
2. Improved data security
3. Better data integration
4. Minimized data inconsistency
5. Improved data access
6. Improved decision making
7. Increased end-user productivity
Disadvantages of database management system
1. Increased cost
2. Management complexity
3. Maintaining currency
4. Frequent upgrade/replacement cycles
SQL
SQL
stands for Structured Query Language. SQL is used to communicate with a database.
It is the standard language for relational database management systems. SQL
statements are used to perform tasks such as update data on a database, or
retrieve data from a database. Some common relational database management
systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres,
etc.
Types
of SQL Command
Data
Definition Language (DDL)
DDL changes
the structure of the table like creating a table, deleting a table, altering a
table, etc. All the command of DDL are auto-committed that means it permanently
save all the changes in the database. For example CREATE, ALTER, DROP, TRUNCATE
Data
Manipulation Language
DML commands
are used to modify the database. It is responsible for all form of changes in
the database. The command of DML is not auto-committed that means it can't
permanently save all the changes in the database. They can be rollback. Example
: INSERT, UPDATE, DELETE
Data Control
Language
DCL commands
are used to grant and take back authority from any database user.
Example:
Grant, Revoke
Transaction
Control Language
TCL commands
can only use with DML commands like INSERT, DELETE and UPDATE only. These
operations are automatically committed in the database that's why they cannot
be used while creating tables or dropping them. Example : COMMIT, ROLLBACK, SAVEPOINT
Data Query
Language
DQL is used
to fetch the data from the database. It uses only one command that is SELECT
Database
Model
A database model
shows the logical structure of a database, including the relationships and
constraints that determine how data can be stored and accessed. Database Model gives us an idea that how the final system will look like
after its complete implementation. It defines the data elements and the
relationships between the data elements.
Types of
Database Models
1. Hierarchical Model
The
hierarchical model organizes data into a tree-like structure, where each record
has a single parent or root. Sibling records are sorted in a particular order.
That order is used as the physical order for storing the database. This model
is good for describing many real-world relationships.
Advantages:
It is very simple and fast to
traverse through a tree-like structure.
Any change in the parent node
is automatically reflected in the child node so, the integrity of data is
maintained.
Disadvantages:
Complex relationships are not
supported.
As it does not support more
than one parent of the child node so if we have some complex relationship where
a child node needs to have two parent node then that can't be represented using
this model.
If a parent node is deleted, then the child node is automatically
deleted.
2. Network
Model
The network model builds on the hierarchical model by allowing
many-to-many relationships between linked records, implying multiple parent
records. Based on mathematical set theory, the model is constructed with sets
of related records. Each set consists of one owner or parent record and one or
more member or child records. A record can be a member or child in multiple
sets, allowing this model to convey complex relationships.
Advantages:
This model is more flexible.
It reduces redundancy.
Searching is faster.
Disadvantages:
It is very complex type of
database model.
It needs long programs to
handle the relationship.
Pointers needed in the
database model increases overhead of storage.
Less security in comparison to hierarchical model because it is open to
all.
3. Relational Model
Relational
Model is the most widely used model. In this model, the data is maintained in
the form of a two-dimensional table. All the information is stored in the form
of row and columns. The basic structure of a relational model is tables. So,
the tables are also called relations in the relational model.
Advantages:
The rules are common in each
table and easy to link one table to another.
Normalization of database is
possible.
Quick database processing is
possible.
It has very less redundancy.
It enables a computer system
to accommodate a variety of file inquires in an efficient manner.
It also helps to add indexes
for table.
Disadvantages:
It is complex than other
models.
It is confusing as many rules
being applied and becomes non-user friendly.
The index portion of the file
must be created and maintained along with the file records.
In some cases, the index
portion of the file may be larger than the file with the file records.
The file index must be
searched sequentially before the actual file records are obtained, resulting in
wastage of time.
What is normalization?
Normalization is a database design technique that reduces data
redundancy and eliminates undesirable characteristics like Insertion, Update
and Deletion Anomalies. Normalization rules divide larger tables into smaller
tables and links them using relationships. The purpose of Normalization in SQL
is to eliminate redundant (repetitive) data and ensure data is stored
logically.
First
Normal Form
Second
Normal Form
Third
Normal Form
First
Normal Form,
it should follow the following 4 rules:
- It
should only have single(atomic) valued attributes/columns.
- Values
stored in a column should be of the same domain
- All the
columns in a table should have unique names.
- And the
order in which data is stored, does not matter.
Example : We have a table
|
Roll No |
Name |
Subject |
|
1 |
Kamal |
OS, PL |
|
2 |
Lalit |
Java |
|
3 |
Prem |
C, C++ |
After first
normal Form :
|
Roll No |
Name |
Subject |
|
1 |
Kamal |
OS |
|
1 |
Kamal |
PL |
|
2 |
Lalit |
Java |
|
3 |
Prem |
C |
|
3 |
Prem |
C++ |
For
a table to be in the Second Normal Form,
- It
should be in the First Normal form.
- And, it
should not have Partial Dependency.
One
first normalized table example
|
Score_id |
student_id |
subject_id |
marks |
teacher |
|
1 |
10 |
1 |
70 |
Java Teacher |
|
2 |
10 |
2 |
75 |
C++ Teacher |
|
3 |
11 |
1 |
80 |
Java Teacher |
After
Second Normal form (Removing Partial Dependency)( Partial Dependency,
where an attribute in a table depends on only a part of the primary key and not
on the whole, so will separate the fields)
|
Subject_id |
Subject_name |
teacher |
|
1 |
Java |
Java Teacher |
|
2 |
C++ |
C++ Teacher |
|
3 |
PHP |
PHP Teacher |
|
score_id |
student_id |
subject_id |
marks |
|
1 |
10 |
1 |
70 |
|
2 |
10 |
2 |
75 |
|
3 |
11 |
1 |
80 |
To
be in the Third Normal Form when,
- It is
in the Second Normal form.
- And, it
doesn't have Transitive Dependency.
Our
table
|
score_id |
student_id |
subject_id |
marks |
|
1 |
10 |
1 |
70 |
|
2 |
10 |
2 |
75 |
|
3 |
11 |
1 |
80 |
In the above table, we
need to store some more information, which is the exam name and total marks, so
let's add 2 more columns to the Score table.
|
score_id |
student_id |
subject_id |
marks |
exam_name |
total_marks |
|
1 |
10 |
1 |
70 |
|
|
|
2 |
10 |
2 |
75 |
|
|
|
3 |
11 |
1 |
80 |
|
|
after
removing transitive dependency
|
score_id |
student_id |
subject_id |
marks |
exam_id |
|
1 |
10 |
1 |
70 |
1 |
|
2 |
10 |
2 |
75 |
2 |
|
3 |
11 |
1 |
80 |
3 |
|
exam_id |
exam_name |
total_marks |
|
1 |
workshop |
200 |
|
2 |
mains |
70 |
|
3 |
practicals |
30 |
Centralized database management system.
Centralized database management system is the system in which
all the data is stored and managed in a single unit. This is also known as
central computer database system. This system is mostly used in an
organization, in any Business Company or in institution to centralize the
tasks. Data can be accessed through a network Local Area Network (LAN) or Wide
Area Network (WAN).
Advantage
of centralized database system:
Data
integrity
Data is more unified as it stored in single computer system and
managed. It is easier to communicate and coordinate to get more reliable and
meaningful data.
Data
redundancy
Data is centralized and stored in one location only. There is no
duplication of data and irrelevancy in data.
Data
security
Due to storage of data in centralized computer system, the
security of data needs to be stronger. Centralized database management system
is more secure and more efficient.
Scalability
and localization
New computer systems can be added or removed in centralized
database management system more easily.
Data
portability
Data can be easily transferred from one computer to another
computer because it is stored in centralized database management system.
Lesser
cost and maintenance
The centralized database system is cheaper in installation and
maintenance than other database management systems and it required single
storage system and data can be accessed by all the connected computers.
Disadvantage
of centralized database system
Slow
processing
In centralized database management system, data is stored in one
location and it access and processing speed is lesser than other management
system. It requires more time to access the data from one location.
Less
efficiency
If the multiple users try to access and process query toward
server simultaneously then it creates problem. The processing speed of the
central computer turns into low. These problems may reduce the efficiency.
Loss
of data
In centralized database management system, if any system failure
occurs or any data is lost, then it is not recovered.
Distributed Database Management System
Distributed database system is database which is integrated
with collection of databases which are physically connected to a computer network.
It is not limited to a single system but it is spread over different computer
networks or multiple databases.
Advantages of
Distributed Database Management System
Reliable
In
distributed database management system, if any connected system fails to do
work then there is no effect on the performance of the system. It continues
functioning and it is more reliable than other simple database management
system.
Low
communication cost
Data
and information is stored locally in distributed database management system.
Its communication cost and data manipulation become easy and less costly.
Modular
development
Modulation
in distributed database management system is so easy. More systems can be
manipulated and installed by just installing and connecting with the
distributed database system with no interruption and failure.
Better
response
All
the computer system are installed centrally and can process any query with in
shortest possible time. It provides faster response due to centralized
processing of database management systems.
Data
recovery
Data
can be easily recovered in distributed database management systems.
Disadvantages
of Distributed database system
Data
integrity
Data
is updated on multiple sites can cause problems. Data integrity is more complex
and very hard to handle.
Duplication
of data
Same
type of data is stored in different systems make duplication of data. It takes
much space to store the same data in different computer systems in distributed
database management systems.
Improper
data distribution
Improper
data distribution can lead to slow response in processing of query. Same data
is stored in different computers can create more problems in distributed database
management systems.
Less
processing speed
Much
communication is needed to a simple query to perform. In this reason ample time
period is required to solve a specific problem.
Data integrity:
Data
integrity refers to validity of data contained in database. Database integrity
can be reduced in many ways including input typing errors, hardware
malfunctions and data transmission errors. To avoid data integrity errors,
database programs should use data validation process, which define acceptable
ranges for each field in record.
Domain Integrity
Domain
refers to the range of acceptable values. It refers to the range of values that
we are going to accept and store in a particular column within a database. The
data types available are mainly integer, text, date etc. Any entry which we
make for a column should be available in the domain of the data type.
Referential Integrity
Referential
Integrity is used to maintain the data consistency between two tables. Rules
are made in the database structure about how foreign keys should be used to
ensure that changes, addition and deletion in the database maintain the data
integrity. The referential integrity constraints state that if a foreign key in
the first table refers to the primary key of the second table then every value
of foreign key in the first table should either be null or present in the
second table.
Entity Integrity
Each row
for an entity in a table should be uniquely identified i.e. if some record is
saved in the database then that record should be uniquely identified from
others. This is done with the help of primary keys. The entity constraint says
that the value of the primary key should not be NULL.
DBA (Database Administrator)
DBA is the most responsible person in an organization
with sound knowledge of DBMS. He/she is the overall administrator of the program. He/she has the maximum
amount of privileges for accessing database and defining the role of the
employee which use the system. The main goal of DBA is to keep the database
server up to date, secure and provide information to the user on demand.
Qualities of good DBA
1. He/she should have sound and complete knowledge about DBMS and its
operation.
2. He/she should be familiar with several DBMS packages such as MS Access,
MY SQL, Oracle etc
3. He/she should have depth knowledge about the OS in which database server
is running.
4. He/she should have good understanding of network architecture.
5. He/she should hove good database designing skill.
Responsibilities of DBA
1. Installing and Upgrading an Database Server
2. Monitoring the Database Server’s Health and Tuning Accordingly
3. Performing Backup and Recovery Duties
4. Managing Database Users and Security
5. Working with Developers
6. Transferring Data
7. Data Warehousing
8. Scheduling Events
9. Providing 24-Hour Access
10. Learning Constantly
Database Relationship
The
main feature of relational
databases is relationships. Different relationship types
control how the data and tables relate to one another. Establishing links
between tables through relationships makes this database type unique,
and knowing how relationships work helps broaden database modeling
capabilities.
The two following elements define how a database relationship is
established:
Primary key - A column whose value uniquely identifies a
table record.
Foreign key - A column whose values reference the primary
key of another table.
A connection between a parent table and the child table exists by
referencing the parent's primary key. The reference's behavior defines the
relationship type between two database tables.
One
to One Relationship (1:1): It is used to create a relationship
between two tables in which a single row of the first table can only be related
to one and only one records of a second table. Similarly, the row of a second
table can also be related to anyone row of the first table.
One
to Many Relationship: It is used to create a relationship
between two tables. Any single rows of the first table can be related to one or
more rows of the second tables, but the rows of second tables can only relate
to the only row in the first table. It is also known as a many to
one relationship.
Many to Many Relationship: It is many to many relationships that create a relationship between two tables. Each record of the first table can relate to any records (or no records) in the second table. Similarly, each record of the second table can also relate to more than one record of the first table. It is also represented an N:N relationship.
ER-Diagram
Now, let’s explain how we create the diagram.
Srep1: Define
the process.
As it is
a context data flow diagram, the process is only one. In our case, it
is Clothes Ordering System. Draw a rectangle for the process.
Step
2: Create the list of all external entities.
In our
example, the external entities are: Customer, Clothes Store, Clothes
Supplier, and the Sales Manager. These are all entities who are involved
with our system. Also, now you can draw a rectangle for each of the
entities.
Step 3: Create
a list of the data flows.
In
between our process and the external entities, there are data flows that show a
brief description of the type of information exchanged between the entities and
the system.
In our
example, the list of data flows includes: Customer Order, Receipt, Clothes
Order, Receipt, Clothes Order, and Management Report.
Now,
connect the rectangles with arrows signifying the data flows.
If data flows both ways between any two rectangles, create two individual arrows.
Comments
Post a Comment