Types of Database USER

DBMS is a multi-user  software system. It allows multiple user of different category to use the DBMS software. Here we are discussing about different category of DBMS user. 

Database Administrator (DBA): The DBA is a person or a group of persons who is responsible for the management of the database. DBA is custodian of entire data of the organization.  The DBA is responsible for authorizing access to the database by grant and revoke permis­sions to the users, for coordinating and monitoring its use, managing backups and repairing damage due to hardware and/or software failures and for acquiring hardware and software resources as needed. In case of small organization the role of DBA is performed by a single person and in case of large organizations there is a group of DBA's who share responsibilities.

Database Designers: They are responsible for identifying the data to be stored in the database and for choosing appropriate structure to represent and store the data. It is the responsibility of database designers to communicate with all prospective of the database users in order to understand their requirements so that they can create a design that meets their requirements.

End Users: End Users are the people who interact with the database through applications or utilities. The various categories of end users are:

• Casual End Users - These Users occasionally access the database but may need different information each time. They use sophisticated database Query language to specify their requests. For example: High level Managers who access the data weekly or biweekly.

• Native End Users - These users frequently query and update the database using standard types of Queries. The operations that can be performed by this class of users are very limited and effect precise portion of the database.

For example: - Reservation clerks for airlines/hotels check availability for given request and make reservations. Also, persons using Automated Teller Machines (ATM's) fall under this category as he has access to limited portion of the database.

• Standalone end Users/On-line End Users - Those end Users who interact with the database directly via on-line terminal or indirectly through Menu or graphics based Interfaces.

For example: - User of a text package, library management software that store variety of library data such as issue and return of books for fine purposes.

Application Programmers

Application Programmers are responsible for writing application programs that use the data­base. These programs could be written in General Purpose Programming languages such as Visual Basic, Developer, C, FORTRAN, COBOL etc. to manipulate the database. These application programs operate on the data to perform various operations such as retaining information, creating new.

Concepts of various keys in DBMS

Definition of Various Keys in DBMS:

(I)            Candidate Key – It is also defined as possible primary key. It means a key which is candidate for becoming a primary key. It can be defined as minimal Super Key or irreducible Super Key.

 In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely.

E.g. of Candidate Key

1.    ID

2.    Name, Address.

Let us consider Employee Table (ID , Name , Address , Salary , Department_ID)

For above table we have only two Candidate Keys (i.e. Irreducible Super Key) used to identify the records from the table uniquely. ID Key can identify the record uniquely and similarly combination of Name and Address can identify the record uniquely, but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two employees with similar name or two employees from the same house.

(II)          Primary Key – A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key. A Primary Key can consist of one or more attributes of a table.

When Primary Key consists of single attribute it is called as simple primary keyelse it is called as composit primary key

E.g. for defining Primary Key - Database designer can use one of the Candidate Key as a Primary Key. In this case we have “ID” and “Name, Address” as Candidate Key, we will consider “ID” Key.

(III) Super Key – An attribute or a combination of attribute that is used to identify the records uniquely is known as Super Key. A table can have many Super Keys.

E.g. of Super Key

1.    ID

2.    ID, Name

3.    ID, Address

4.    ID, Department_ID

5.    ID, Salary

6.    Name, Address

7.    Name, Address, Department_ID

So on as any combination which can identify the records uniquely will be a Super Key.

(IV) Foreign Key – A foreign key is an attribute or combination of attributes in one base table that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data i.e. only values that are supposed to appear in the database are permitted.

E.g. of Foreign Key – Let consider we have another table i.e. Department Table with Attributes “Department_ID”, “Department_Name”, “Manager_ID”, ”Location_ID” with Department_ID as an Primary Key. Now the Department_ID attribute of Employee Table (dependent or child table) can be defined as the Foreign Key as it can reference to the Department_ID attribute of the Departments table (the referenced or parent table), a Foreign Key value must match an existing value in the parent table or be NULL.

Foreign Key and Primary Key may exist together in single table.

(V) Composite Key – If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key).

E.g. of Composite Key, if we have used “Name, Address” as a Primary Key then it will be our Composite Key.

(VI) Alternate Key – Alternate Key can be any of the Candidate Keys except for the Primary Key.

E.g. of Alternate Key is “Name, Address” as it is the only other Candidate Key which is not a Primary Key.

(VII) Secondary Key – The attributes that are not even the Super Key but can be still used for identification of records (not unique) are known as Secondary Key.

E.g. of Secondary Key can be Name, Address, Salary, Department_ID etc. as they can identify the records but they might not be unique.

Introduction To SQL. Video Link

ORACLE COMMAND

Delete: The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

Example- To delete any specific row you would use:

DELETE FROM MyTable WHERE id=5

This would delete the row with the id = 5 If no conditions are matched it would delete all rows

Drop: The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

Example- DROP TABLE MyTable

This would delete the whole table and its constraints.

ROLLBACK:
To undo the work/changes that are committed or done by COMMIT clause we use the ROLLBACK Command. It is reverse of COMMIT. It rollbacks all the changes of the current transactions. In other words ROLLBACK restore the state of the database to the last commit point.
Syntax
DELETE FROM table_name
ROLLBAC

COMMIT:
Whatever changes we have made/ done in our transaction cannot be fixed until we commit that transaction and this can be possible by using COMMIT statement. In other words it will make our changes permanent that cannot be rolled back.
Syntax

COMMIT;
_____________________________________________________________________________

Alter: Alter is a SQL command that is used to modify, delete or add a column to an existing table in a database. Alter is considered as a DDL statement. Commands that are used to define the structure of a database (database schema) are called DDL statements. Following is the typical syntax of an alter statement that is used to add a column to an existing table.

ALTER TABLE        tableName

ADD                       newColumnName dataTypeOfNewColumn

In here tableName is the name of the existing table that needs to be altered and newColumnName is the name given to the new column that is added to the table. dataTypeOfNewColumn provides the data type of the new column.

Update: Update is a SQL command that is used to update existing records in a database. Update is considered as a DML statement. Commands that are used to manage data without altering the data base schema are called DML statements. Following is the typical syntax of an update statement.

UPDATE                   tableName

SET                          column1Name=value1, column2Name=value2, …

WHERE                    columnXName=someValue

In the above example tableName should be replaced with the name of the table you want to modify the records in. The column1Name, column2Name in the SET clause are the names of the columns in the table in which the values of the record that needs to be modified. value1 and value2 are the new values that should be inserted in the record. WHERE clause specifies the set of records needs to be updated in the table. WEHRE clause could also be omitted from the UPDATE statement. Then all the records in the table would be updated with the values provided in the SET clause.

NORMALIZATION

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process:

·         Eliminating redundant data (storing the same data in more than one table)

·         Ensuring data dependencies make sense (only storing related data in a table)

Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

In relational database, SQL can be used to manipulate data in the following ways: insert new data, delete unwanted data, and update existing data. Similarly, in an un-normalized design, there are 3 problems that can occur when we work with the data. To understand these problems I take an example of Student table:

Student_ID	Student_Name	Address	Subject_Opted
401	Arun	Delhi	Maths
402	Sulekha	Pune	Biology
403	Pinky	Kolkata	Maths
404	Ramesh	Delhi	Physics

Update anomaly: The same data can be scattered on multiple rows therefore updates to the table may result in logical inconsistencies. For example: To update address of a student who occurs twice or more than twice in a table, we will have to update “Student_Address” column in all the rows, else the table is left in an inconsistent state.

Insertion anomaly: We tried to insert data in a record that does not exist at all. Suppose for a new admission, we have a Student_ID, Student_Name and Address of a student but if student has not opted for any subjects yet then we have to insert NULL there, leading to Insertion Anomaly.

Deletion anomaly: If (Student_ID) 401 has only one subject and temporarily he drops it, when we delete that row, entire student record will be deleted along with it.

Normalization is a method to remove all these anomalies and bring the database to a consistent state. There are several different levels of database normalization.

1.       1st Normal Form (1NF)

·         Eliminate repeating groups in individual tables.

·         Create a separate table for each set of related data.

·         Identify each set of related data with a primary key.

2.       2nd Normal Form (2NF)

·         Meet all the requirements of the first normal form.

·         The non key attributes should be fully dependent on composite primary key, partial dependency is not allowed.

·         Relate the tables with a foreign key.

3.       3rd Normal Form (3NF)

To achieve this normal form we have to eliminate the non-key values which are not fully functionally dependent on primary key of the table.

4.       Other Normalization Forms

Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but are rarely considered in practical design.

To explain the levels I take an example of unnormalized “Student” table:

Student_ID	Student_Name	Address	Subject_Opted
401	Rajeev	Delhi	Maths,Physics
402	Sulekha	Pune	Biology,chemistry
403	Pinky	Kolkata	Arts

After applying the rules of normalization To bring this table to first normal form, we split the table into multiple tables and now we have the resulting tables:

Table: Student

Student_ID	Student_Name	Address
401	Rajeev	Delhi
402	Sulekha	Pune
403	Pinky	Kolkata

Table: Subjects

Subject_ID	Student_ID	Subject_Opted
801	401	Maths
802	401	Physics
803	402	Biology
804	402	Chemistry
805	403	Arts

Table: Student_Subjects

Subject_ID	Student_ID
801	401
802	401
803	402
804	402
805	403

Introduction to SQL

INTRODUCTARY CONCEPT

1.1 What is SQL?

·         SQL stands for Structured Query Language

·         SQL allows you to access a database

·         SQL is an ANSI standard computer language

·         SQL can execute queries against a database

·         SQL can retrieve data from a database

·         SQL can insert new records in a database

·         SQL can delete records from a database

·         SQL can update records in a database

·         SQL is easy to learn

 

SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc

1.2 SQL Database Tables:

A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data.

Below is an example of a table called "Persons":

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger

The table above contains three records (one for each person) and four columns (LastName,  FirstName, Address, and City).

2. DATABASE LANGUAGE

2.1 SQL Data Definition Language (DDL)

The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables.

The most important DDL statements in SQL are: 

·                     CREATE TABLE - creates a new database table

·                     ALTER TABLE - alters (changes) a database table

·                     DROP TABLE - deletes a database table

Create a Table

To create a table in a database:

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... )

 

Example

This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":

 

ALTER TABLE

The ALTER TABLE statement is used to add, drop and modify columns in an existing table.

ALTER TABLE table_name ADD column_name datatype   ALTER TABLE table_name MODIFY column_name datatype   ALTER TABLE table_name DROP COLUMN column_name

 

Delete a Table or Database

To delete a table (the table structure attributes, and indexes will also be deleted):

DROP TABLE table_name

2.2 SQL Data Manipulation Language (DML)

DML language includes syntax to update, insert, and delete records. These query and update commands together form the Data Manipulation Language (DML) part of SQL:

·                     UPDATE - updates data in a database table

·                     DELETE - deletes data from a database table

·                     INSERT INTO - inserts new data into a database table

---

The INSERT INTO Statement

The INSERT INTO statement is used to insert new rows into a table.

Syntax

INSERT INTO table_name VALUES (value1, value2,....)

You can also specify the columns for which you want to insert data:

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

 

The Update Statement

The UPDATE statement is used to modify the data in a table.

Syntax

UPDATE table_name SET column_name = new_value WHERE column_name = some_value

The DELETE Statement

The DELETE statement is used to delete rows in a table.

Syntax

DELETE FROM  table_name WHERE column_name = some_value