Database and DBMS
Data
A data can be anything which we give to computer system to get some useful meaning as an output. For example, if we want to do 2+2 then we enter 2 and 2 (may be for variables like A and B) . Here 2 and 2 both are data. A data can be text or a character or anything else.
Information
Knowledge derived from study, experience (by the senses), or instruction after entering data into the system. It gives us useful meaning or result.
"Information is interpreted data" .
Like in previous example,
data------------>2 and 2
Information --->4
Database: - Database is a collection of data for one or more multiple uses in very well organized format.
Or
Simply well organized collection of data is database.
One way of classifying databases involves the type of content, for example: bibliographic, full-text, numeric, and image. Databases consist of software-based "containers" that are structured to collect and store information so users can retrieve, add, update or remove such information in an automatic fashion. Database programs are designed for users so that they can add or delete any information needed. The structure of a database is tabular, consisting of rows and columns of information.
Advantages
Reduced data redundancy
Improved data security
Reduced data entry, storage, and retrieval costs
Facilitated development of new applications program
improved data integrity (integrity:data should be accurate & accurate; it is done by providing some checks
multiple users.
Disadvantages
Database systems are complex, difficult, and time-consuming to design
Substantial hardware and software start-up costs
Initial training required for all programmers and users
Database Management System (DBMS):-It’s a set of computer programs that controls the creation, maintenance, and the use of the database with computer as a platform or of an organization and its end users.
A DBMS is a system software package that helps the use of integrated collection of data records and files known as databases. It allows different user application programs to easily access the same database. DBMSs may use any of a variety of database models, such as the network model or relational model. In large systems, a DBMS allows users and other software to store and retrieve data in a structured way. Instead of having to write computer programs to extract information, user can ask simple questions in a query language.
Its roles can be listed as given below.
1. provides an interface to the user as well as to application
2. lets user to create,maintain databases.
3. provides high integrity and security to its data.
4. provides sharing facility to many users.
5. provides relationships between tables.
6. provides access level to different users.
7. maintains integrity,consistency of data etc
Disadvantages:
1.initial setup is expensive
2. training to staffs has to be provided
3.database failures.
4.data conversion( computer file into database file which takes more time)
Flat file system (old one approach):- Strictly, a flat file database should consist of nothing but data and, if records vary in length, delimiters. More broadly, the term refers to any database which exists in a single file in the form of rows and columns, with no relationships or links between records and fields except the table structure.
In this model, data is stored in a formatted text file formatted as either a "fixed length" or "character delimited" text file.
In such system, the single fields can be separated by delimiters, e.g. commas, or have a fixed length. In the latter case, padding may be needed to achieve this length. Extra formatting may be needed to avoid delimiter collision. There are no structural relationships between the records.
or
It’s old one approach called manual where a manual file can be setup to hold all related matters like, employee, tasks,clients. In an organization there could be many such files which may be labeled and stored and same can be done at home. It works if items to be stored is small but crashes mostly when it is processed. Like, a university may have separate files for each student who have different choices of cour ses with department heads/members,fees etc.Can we give answer of following questions without effort? Let’s try.
q1)annual fees paid by students.
q2)students using transport facility.
q3)total turnover annually etc
It takes more time,am I right?
But if we do same with latest concept then it would be quite easier to carry out these tasks
It used to have following disadvantage:-
1) Data redundancy
2) Data integrity
3) Data consistency
4) Security, sharings
5) Isolation of data.
etc.
To overcome these all we have to use latest database system having table, relationship etc.
Database schema:-
The schema of a database system is its structure described in a formal language supported by the database management system (DBMS).In a relational database, the schema defines the tables, the fields, relationships, views, indexes, packages, procedures, functions, queues etc and other elements. Schemas are generally stored in a data dictionary. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure. In other words, schema is the structure of the database that defines the objects in the database.
Schema has mainly two ways to structured.
1) Logical schema:-It says about database design or structure mainly how data is managed or structured, what is used level etc.Logical Schema is a data model of a specific problem domain expressed in terms of a particular data management technology. Without being specific to a particular database management product, it is in terms of relational tables and columns, object-oriented classes, or XML tags.
2) Physical schema:- The underlying structure of storing device is physical schema. It is for most part determined by DBMS. It simply says about disk representation of data that accounts for layout, partitioning, index, space management, etc. The beauty of data arrangement is that database designers and users do not need to be concerned about physical storage simply simplifying access to database and making much easier to make changes to both logical and physical
Keys:
Primary key: It is the key which is used to indetify a record uniquely.
|
Student id |
Student name |
Phone number |
Grade |
|
1 |
Raman |
87987987 |
11 |
|
2 |
Raman |
87987987 |
11 |
|
3 |
Rimran |
63435332 |
11 |
Here student id is a primary key.
note:It can not be null.
In a table, there can be one primary key though there exists multiple fields having unique values.
Candidate key: The minimal number of fields used to identify unique record is called candidate key.
It can be an attribute or collection of attributes(composite).
It can not be null.
e.g.
|
Student id |
Student name |
Phone number |
Grade |
|
1 |
Raman |
87987987 |
11 |
|
2 |
Saman |
12098889 |
11 |
|
3 |
Simran |
63435332 |
11 |
|
teacher id |
Teacher name |
Subject code |
|
1 |
Ram Kumar Shrestha |
CS01 |
|
2 |
Samarpan Malla |
CS02 |
|
1 |
Ram Kumar shrestha |
CS03 |
Here, teacher id and subject code act as composite key.
Alternate key:-
It is possible to have many fields with unique records.Among many we make one key as primary key and remaining key act as alternate key.
e.g.
|
Student id |
Student name |
Phone number |
Grade |
|
1 |
Raman |
87987987 |
11 |
|
2 |
Saman |
12098889 |
11 |
|
3 |
Simran |
63435332 |
11 |
In this table, we take student id as primary key and phone number as alternate key.
Foreign key:-
If we want to link two more tables then there must be some fields common in both.One field that is primary in one table and can hold some data in another table.That field is called foreign key refernced to first table.
e.g.
Customer details
|
customer
id |
customer
name |
Customer
address |
|
1 |
Raman |
KTM |
|
2 |
Saman |
PKH |
|
3 |
Simran |
ktm |
Product details
|
customer
id |
product
name |
Product
price |
|
1 |
Laptop |
45000 |
|
2 |
Mobile
phone |
55000 |
|
1 |
Charging
adaptor |
4000 |
Note:It can be null.
IT is not necessary that it must be unique.
Data model:-In software engineering,it is an abstract model that describes how data is represented and accessed. Data models formally define data elements and relationships among data elements for a domain of interest.
Or
A data model explicitly determines the meaning of data, which in this case is known as structured data (as opposed to unstructured data, for example an image, a binary file or a natural language text, where the meaning has to be elaborated). Typical applications of data models include database models, design of information systems, and enabling exchange of data.
Database model:-A database model or database schema is the structure or format of a database, described in a formal language supported by the database management system.
Hierarchical model:-
Hierarchical database is a model in which data is organized into a tree-like structure. In this,
Data is organized like a family tree or organization chart
The parent record can have multiple child records – child records can only have one parent
Pointers link each parent record with each child record
Complex and difficult to maintain
This structure allows one 1:N relationship between two types of data. This structure is very efficient to describe many relationships in the real world; recipes, table of contents, ordering of paragraphs/verses,
Example:
We can see here a parent node with many branches called children.Each child has their own record with furhher informations.In given diagram, the first record has name “Ram” with street “Baneshwor” and city named kathmandu. It is further linked to its balance id and amount. In same fashion all others records have, they all are related or linked to their id and amount. We can see records’ arrangement in tree like format.
Advantages:- 1) easiest model of database.
2) Searching is fast if parent is known.
3) supports one to many relationship.
Disadvantages: 1) old and outdated one.
2) Can not handle many to many relationships.
3) More redundancy.
Network model:-The network model organizes data using two fundamental constructs, called records and sets. The Network Model
Data are represented by collections of records.
Relationships among data are represented by links.
Organization is that of an arbitrary graph.
Figure shows a sample network database that is the equivalent of the relational database of Figure ??
eg.
We have taken same example as mentioned above.but the records are arranged in graph like structure. In this we do not use concept of child /parent.In given diagram the records are linked to each other with a solid line as it is in hierarchical. Two records namely Ramesh and Hary are linked to two balance id and amounts.
Advantages:- 1) more flexible due to many to many relationship.
2) Reduction of redundancy.
3) Searching is very fast.
Disadvantages:- 1) very complex.
2) Needs long and complex programs.
3) Less security
Relational model: - Three key terms are used extensively in relational database models: relations, attributes, and domains. A relation is a table with columns and rows. The named columns of the relation are called attributes, and the domain is the set of values the attributes are allowed to take.
The basic data structure of the relational model is the table, where information about a particular entity is represented in columns and rows. Thus, the "relation" in "relational database" refers to the various tables in the database; a relation is a set of tuples.So,
Relational databases
Data is organized into two-dimensional tables, or relations
Each row is a tuple (record) and each column is an attribute (field)
A common field appears in more than one table and is used to establish relationships
Because of its power and flexibility, the relational database model is the predominant design approach
In this table we have used different concept like field (each column),record (each row). Each row gives us a complete information. If we have many tables then we relate them for data extractions,this is called relationship between tables.Apart from these, we also use other concept like,primary key,foreign key,Entity etc.
Data integrity:
Integrity is consistency of actions, values, methods, measures, principles, expectations and outcome.
Data integrity is a term used in computer science and telecommunications that can mean ensuring data is "whole" or complete, the condition in which data are identically maintained during any operation (such as transfer, storage or retrieval), the preservation of data for their intended use, or, relative to specified operations, the a priori expectation of data quality.
Or
Database integrity means the correctness and consistency of data. It is another form of database protection.
Put simply, data integrity is the assurance that data is consistent and correct.
Data integrity is normally enforced in a database system by a series of integrity constraints or rules.
Three types of integrity constraints are an inherent part of the relational data model: entity integrity, referential integrity
Entity integrity concerns the concept of a primary key. Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null. It means the primary key’s data must not be missing in table/s.
or
The entity integrity is a constraint on primary key value. It states that any attribute of a primary key cannot contain null value. If primary key contains null value, it is not possible to uniquely identify a record in a relation. Entity integrity ensures that it should be easy to identify each entity in the database.
Referential integrity concerns the concept of a foreign key. The referential integrity rule states that any foreign key value can only be in one of two states. The usual state of affairs is that the foreign key value refers to a primary key value of some table in the database. Occasionally, and this will depend on the rules of the business, a foreign key value can be null. In this case we are explicitly saying that either there is no relationship between the objects represented in the database or that this relationship is unknown.
Or
Referential Integrity
Referential integrity ensures that the relationship between the primary key (in a referenced table) and the foreign key (in each of the referencing tables) is always maintained. The maintenance of this relationship means that:
A row in a referenced table cannot be deleted, nor can the primary key be changed, if a foreign key refers to the row. For example, you cannot delete a customer that has placed one or more orders.
A row cannot be added to a referencing table if the foreign key does not match the primary key of an existing row in the referenced table. For example, you cannot create an order for a customer that does not exist.
Domain Integrity
Domain (or column) integrity specifies the set of data values that are valid for a column and determines whether null values are allowed. Domain integrity is enforced by validity checking and by restricting the data type, format, or range of possible values allowed in a column.
E-R model(E-R diagram):
The Entity-Relationship (E-R) data model is based on a perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects. An entity is a “thing” or “object” in the real world that is distinguishable from other objects. For example, each person is an entity, and bank accounts can be considered as entities.
Entities are described in a database by a set of attributes. For example, the attributes account-number and balance may describe one particular account in a bank, and they form attributes of the account entity set. Similarly, attributes customer-name, customer-street address and customer-city may describe a customer entity.
A relationship is an association among several entities. For example, a depositor relationship associates a customer with each account that she has. The set of all entities of the same type and the set of all relationships of the same type are termed an entity set and relationship set, respectively.
The overall logical structure (schema) of a database can be expressed graphically by an E-R diagram, which is built up from the following components:
Rectangles, which represent entity sets
Ellipses, which represent attributes.
Diamonds, which represent relationships among entity sets.
Lines, which link attributes to entity sets and entity sets to relationships.
for example,
1)
2.
3.
Relationship and its types:-
A relationship is simply an association between two or more entities.It shows type of relationship existing between them. It is helpful when we want to extract information from different tables(entities).
Types:-
one-to-one relationship:-
A relationship is said to be one-one if there is link of one table(entity)/attribute with only one another table or its attribute. For example,
a Relationship between an employee and their workstation as shown below.
we can see here that an employee can have only one workstation to work so it is one-one relationship.
or
Another example can be taken as,
In this E-R diagram we can see a student can appear only in one exam at one time so it can said as one-one relationship.
one-to-many relationship:-
In a one-to-many relationship, each row in the related to table can be related to many rows in the relational table.
OR
A one-to-many relationship occurs when one entity has a multivalued relationship with another entity.
In the following figure, you see that a one-to-many relationship exists between two entities—employee and department.
It can be shown in the form of 1-∞ while linking the tables.
many to many relationship:-
The many-to-many relationship is a relationship where multiple rows from table A can correspond to multiple rows in table B. An example of such a relationship is a school where teachers teach students. In most schools each teacher teaches multiple students and each student can be taught by multiple teachers.
or
In database design, a many-to-many relationship is a relationship that is multivalued in both directions.
The following figure illustrates this kind of relationship. An employee can work on more than one project, and a project can have more than one employee assigned.
Figure 1. Assigning many-to-many facts to an entity
Normalization :In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies that could lead to a loss of integrity.
Or
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.
Following rules are applied when we normalize relations.
1.Eliminating repeating groups
2.Eliminating repeating data
3.Eliminating columns not depending on key.
4.Isolate independent multiple relationship
5.Isolate semantically related multiple relationships
Types
1N (first normalization):- A table is said to be in 1N if there is no repeating groups (fields or tuple for all records) in individual tables. For example
Here actually there is one group named class which is repeated and which should not be in table of two dimensions so let’s make this table in 1N and that is like,
Now this table is in 1N.
2N (second normalization):- A table is said to be in 2N if it is in 1N and no records are functionally dependant other than primary keys. Let’s take an example,
In above table, the field class is not functionally dependant on primary key(student #) so it is not in 2N. To bring this in 2N let’s break this into two tables given below.
Students
Registration
Now we can see the above tables are in 2N completely.
3N (3rd normalization):- A table is said to be in 3N if it is in 2N and if there is no field which does not depend on key. For example,
Students
In the above example, Adv-Room (the advisor's office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below: students
Faculty
Now we can see the tables (broken ones) are in 3N.
another example can be like,
1 NF:
A TABLE IS SAID TO BE IN 1n IF there is not repeating groups or information in a table..Here, repeating group means a set of columns that stores similar information that repeats in the same table.
Let’s consider following SQL commands.
Create table contacts
(
Contact Id Integer not null,
L_name varchar(20) not null,
F_name varchar(20)
Contact_date1 date,
Contact_desc1 varchar(50),
Contact_date2 date,
Contact_desc2 varchar(50),
);
We can see here in this table that there is a repeating group of date and description.
Now to convert into 1 N, we have to make a new table and shift that repeating group into new table.
Like,
Create table contacts
(
Contact_ID integer not null,
L_name varchar(20) not null,
F_name varchar(20)
);
Create table conversation
(
Contact_id integer not null,
Contact_date date,
Contact_desc varchar(50)
);
Now we have eliminated the repeating groups and established relationship between them by using primary key and hence table is in 1N.
2N:-
A table is said to be in 2 N if it is in 1N and there is no redundant data in table i.e. if a value of column is dependent on one column(primary key only) but not another.
For example:
Create table employee
(
Emp_no integer not null,
L-name varchar(20) not null,
F_name varchar(20),
Dept_code integer,
Description varchar(50),
);
This table contains redundant data i.e. the value of column(field) “description” depends on dept_code but does not depend on primary key “emp_no”.So let’s make a new table and shift them into that.
Create table employee
(
Emp_no integer not null,
L_name varchar(20) not null,
F_name varchar(20),
Dept_code integer,
);
Create table department
(
Dept_code integer not null,
Description varchar(50) not null,
);
We got two tables named employee and department with fields. Both the tables are related by primary key called dept_code. Now there is no redundancy and table is in 2N.
3N:
An entity is said to be in the third normal form when,
1) It satisfies the criteria to be in the second normal form.
2) There exists no transitive functional dependency. (Transitive functional dependency can be best explained with the relationship link between three tables. If table A is functionally dependent on B, and B is functionally dependent on C then C is transitively dependent on A and B).
Example:
Consider a table that shows the database of a bookstore. The table consists of details on Book id, Genre id, Book Genre and Price. The database is maintained to keep a record of all the books that are available or will be available in the bookstore. The table of data is given below.
|
Book id |
Genre id |
Book genre |
price |
|
121 |
2 |
fiction |
150 |
|
233 |
3 |
travel |
100 |
|
432 |
4 |
sports |
120 |
|
123 |
2 |
fiction |
185 |
|
424 |
3 |
travel |
140 |
The data in the table provides us with an idea of the books offered in the store. Also, we can deduce that BOOK id determines the GENRE id and the GENRE id determines the BOOK GENRE. Hence we can see that a transitive functional dependency has developed which makes certain that the table does not satisfy the third normal form.
|
Book id |
Genre id |
price |
|
121 |
2 |
150 |
|
233 |
3 |
100 |
|
432 |
4 |
120 |
|
123 |
2 |
185 |
|
424 |
3 |
140 |
table book
|
Genre id |
Book genre |
|
2 |
fiction |
|
3 |
travel |
|
4 |
sports |
table genre
After splitting the tables and regrouping the redundant content, we obtain two tables where all non-key attributes are fully functional dependent only on the primary key.
When all the column in a table describe and depend upon the primary key,the table is 3N.
----------------------------------------------------------------------------------------------------------------------------------------
simple example to understand normalization:-
1N:
1.It contains only automic values.Automic values:- The Single cell have only single value
2.Each Record needs to be unique and there are no repeating groups.
Repeating Groups:- Repeating group means a table contains 2 or more values of columns that are closely related.
example:
Consider following
table which is not normalized:
Employee Table:
|
Employee No |
Employee Name |
Department |
|
1 |
Amir |
IT, Security |
|
2 |
Divyani |
HR |
|
3 |
Raman |
Accountant |
To bring it in to
first normal form We need to split table into 2 tables.
First table:Employee
Table
|
Employee No |
Employee Name |
|
1 |
Amir |
|
2 |
Divyani |
|
3 |
Raman |
Second Table:
Department table
|
Employee No |
Department |
|
1 |
IT |
|
1 |
Security |
|
2 |
HR |
|
3 |
Accountant |
We have divided the
table into two different tables and the column of each table is holding the atomic
values and no duplicates
The data is said to be
in second normalized form If,
1.It is in First
normal form
2.There should not be
any partial dependency of any column on primary key. Means the table have concatenated
primary key and each attribute in table depends on that concatanated primary
key.
3.All Non-key
attributes are fully functionally dependent on primary key.If primary is is not
composite key then all non key attributes are fully functionally dependent on
primary key.
Example:
Let us consider
following table which is in first normal form:
|
Employee No |
Department No |
Employee Name |
Department |
|
1 |
101 |
Amit |
OBIEE |
|
2 |
102 |
Divya |
COGNOS |
|
3 |
101 |
Rama |
OBIEE |
In above example we
can see that department .Here We will see that there is composit key as{
Employee No,Department No}.Employee No is dependent on Employee Name and
Department is dependent on Department No.We can split the above table into 2
different tables:
Table 1:Employee_NO
table
|
Employee No |
Department No |
Employee Name |
|
1 |
101 |
Amit |
|
2 |
102 |
Divya |
|
3 |
101 |
Rama |
Table 2:Department
table
|
Department No |
Department |
|
101 |
OBIEE |
|
102 |
COGNOS |
Now we have simplified
the table in to second normal form where each entity of table is functionally
dependent on primary key.
Third Normal Form/3rd Normal Form:
The database is in Third normal form if it satisfies following conditions:
1.It is in Second normal form
2.There is no transitive functional dependency
Transitive Dependency:
When table 1 is Functionally dependent on table 2. and table 2 is functionally dependent on table 3 then.table 3 is transitively dependent on table 1 via table 2.
Example:
Consider following table:
| Employee No | Salary Slip No | Employee Name | Salary |
| 1 | 0001 | Amit | 50000 |
| 2 | 0002 | Divya | 40000 |
| 3 | 0003 | Rama | 57000 |
In above table Employee No determines the Salary Slip No.And Salary Slip no Determines Employee name.Therefore Employee No determines Employee Name.We have transitive functional dependency so that this structure not satisfying Third Normal Form.
For That we will Split tables into following 2 tables:
Employee table:
| Employee No | Salary Slip No | Salary |
| 1 | 0001 | 50000 |
| 2 | 0002 | 40000 |
| 3 | 0003 | 57000 |
Salary Table:
| Salary Slip No | employee name |
| 0001 | Amit |
| 0002 | Divya |
| 0003 | Rama |
Following are 2 Advantages of 3rd normal form:
1.Amount of data duplication is removed because transitive dependency is removed in third normal form.
2.Achieved Data integrity
Functional dependency:- To understand about this, let a relation R with two attributes A and B. the attribute B of relation is said to be functionally dependent on attribute A if and only if for each value of A,no more than one value of B is associated. It can be written like R.A----------------->R.B.
Data definition language (DDL):-This is the means by which the content & format data to be stored is described & structure of database is defined, including relationship between records & indexing strategies. This definition of database is known as schema.
DDL is essentially link between logical & physical views of database. Here logical refers to the way the users view the data, physical refers to the way the data are physically stored. The logical structure of database sometimes is known as schema.
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the 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
Data Manipulation Language (DML) is a family of computer languages used by computer programs database users to retrieve, insert, delete and update data in a database.
Currently the most popular data manipulation language is that of SQL, which is used to retrieve and manipulate data in a Relational database. Data Manipulation Languages have their functional capability organized by the initial word in a statement, which is almost always a verb. In the case of SQL, these verbs are:
Select
Insert
Update
Delete
or
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
Each SQL statement is a declarative command. The individual SQL statements are declarative, as opposed to imperative, in that they describe what the program should accomplish, rather than describing how to go about accomplishing it.
Data dictionary Database users and application developers can benefit from an authoritative data dictionary document that catalogs the organization, contents, and conventions of one or more databases. The term "data dictionary" is used by many, including myself, to denote a separate set of tables that describes the application tables. The Data Dictionary contains such information as column names, types, and sizes, but also descriptive information such as titles, captions, primary keys, foreign keys, and hints to the user interface about how to display the field.
A super-simple beginning Data Dictionary might look like this:
Among other items of information, it records (1) what data is stored, (2) name, description, and characteristics of each data element, (3) types of relationships between data elements, (4) access rights and frequency of access. Also called system dictionary when used in the context of a system design.
A data dictionary document also may include further information describing how data elements are encoded. One of the advantages of well-designed data dictionary documentation is that it helps to establish consistency throughout a complex database, or across a large collection of federated databases
“SQL”:- “SQL” stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), 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. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database. It can have/be with,
1. Data definition language
2. interactive data manipulation language
3. embedded DML
4. View definition
5. Integrity etc.
e.g.
CREATE DATABASE MyDatabase; comments creates a database
USE mydatabase;
CREATE TABLE orders
(id INT IDENTITY(1,1) PRIMARY KEY,
customer VARCHAR(50),
day_of_order DATETIME,
product VARCHAR(50),
quantity INT);
etc.
DBA: - A database administrator (DBA) is a person/s who is/are responsible for the environmental aspects/activities related to database. The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases.
Duties:
1. Installation of new software.
2. Configuration of hardware and software with the system administrator
3. Security administration
4. Data analysis
5. Database design (preliminary
6. Data modeling and optimization
7. Responsible for the administration of existing enterprise databases and the analysis, design, and creation of new databases
Distributed database system: A database system in which set of databases are stored on multiple computers that appear a single database for whole organization. As a result an application can simultaneously access and modify data in several databases in a network. Each database in the system local is controlled by its local server but cooperates to maintain consistency of the global distributed database. The computers in distributed system communicate through communication media like high speed buses or telephone lines. The diagram can be shown like
Diagram...............
This system consists many sites which may participate during execution process, which accesses the data from one site or several sites and sends to any other workstation. This system has many advantages like
1. Data sharing and distributed control
2. Reflects organizational structure.
3. More reliability.
4. Improved performance
5. Speed up query processing
6. Modular growth (new site can be easily added) etc.
Centralized database system:- The concept behind centralized database system is concurrent. Cooperative processing; It involves processing on multiple interconnected machines. We have to remember here that client-server database is distribution of activities into clients and server. It may have centralized or distributed database at server backend.
The client-server systems are connected through the network. This network need not be LAN. It can be WAN connecting many cities. The client server machines communicate through standard application called “application program interface (API) and remote procedure calls. They use language called “SQL”. The central system can shown be in following form where we can see many clients which request to server for service and then server immediately provides service to clients with the help of powerful application.
diagram....
This centralized system involves running application on multiple machines in which each machine with its component software handles only part of a job. Client machine is a basic pc or workstation that provides presentation services and appropriate computing, connectivity and interfaces while server provides database services with computing facility to all clients. As the number of users grow, clients can be easily added to network while as database load increases, more servers are added and connected to network. The server machines are more or extremely powerful than all clients. Its advantages can be;
1. Low cost user-friendly environment.
2. Good expandability with increase load.
3. Allows real time connectivity.
4. Security, backup.
5. Many users can access same database at same time.
comparison of distributed and centralized system:
Data security:-
It simply says protection of data /information contained in database against unauthorized access, modification or destruction. The main condition for database security is to have “database integrity’ which says about mechanism to keep data in consistent form. Besides these all, we can apply different level of securities like:
Physical: - says about sites where data is stored must be physically secured.
Human: - an authorization is given to user to reduce chance of any information leakage or manipulation.
Operating system: must be taken a foolproof operating system regarding security such that no weakness in o.s.
Network: - since database is shared in network so the software level security for database must be maintained.
Database system: - the data in database needs final level of access control i.e. a user only should be allowed to read and issue queries but not should be allowed to modify data.
Etc.
Thanks For This Blog, Keep Sharing Your Thoughts Like This...
ReplyDeleteC C++ Training in Chennai
C++ Online Course
ReplyDeleteThe article is so informative. This is more helpful. Thanks for sharing
Java Training Institute in Chennai