Maths Presentation By…

DBMS. Agenda Introduction to DBMS Database Models SQL.

Data may be defined as a known Raw fact that can be recorded and that have no meaning..

It is a collection of meaningful data or processed data.

DATA STORE: It is a place where we can store data or information. 1) Books & Papers 2) Flat files 3) Database FLAT FILES: This is a traditional mechanism which is used to store data or information in individual unrelated files. These files are also called as Flat Files. Drawbacks of Flat files: 1) Data Retrieval 2) Data Redundancy 3) Data Integrity 4) Data Security 5) Data Indexing * In many cases, all records in a file are of the same record type, i.e., all the records are having an identical format..

Database. A database is defined as a collection of logically related data stored together that is designed to meet the information needs of an organization. It is basically an electronic filing cabinet, which contain computerized data files. It can contain one data file or large number of data files depending on organizational needs. A database is organized in such a way that a computer program can quickly select desired pieces of data..

A database management system (DBMS) , is a generalized software system for manipulating databases. DBMS is also a collection of programs that enables users to create and maintain database . Example DBS: ORACLE, FOXPRO, DB2, TERADATA, SQLSERVER, SYBASE, MYSQL, INGRESS, INFORMIX, SQLLITE…… etc; It is basically a computerized record-keeping system, which it stores information and allows users to add, delete, retrieve and update that information on demand. It provides for simultaneous use of a database by multiple users and tool for accessing and manipulating the data in the database..

Inserting new data into existing data files. Adding new files to the database.

The ANSI proposes a Three Level Database Architecture,.

A data model provides a way to describe the design of a database at physical, logical and view level..

Data models can be broadly classified into the following three categories:.

In 1970, E.F.Codd introduced Relational Data Model..

An entity–relationship (E-R) model is a high-level conceptual data model, which has a diagrammatic representation of data for an enterprise of business establishment to produce a well-designed database..

Entity. An entity is an object of concern used to represent the things in the real world,.

Attributes. An attribute is a property used to describe the specific feature of the entity. So to describe an entity entirely, a set of attributes is used..

Relationships. A relationship can be defined as:.

Cardinality specifies the number of instances of an entity associated with another.

One-to-many: An entity in A is associated with any number of entities in B..

Many-to-one: An entity in A is associated with at most one entity in B. An.

Many-to-many: Entities in A and B are associated with any number of entities from each other..

Office Errolment Student name Student Address Enrols Course u)de Courses Coure name Teaches lmstructor Instructor/ Teacher.

SQL Structured Query Language.

Structured Query Language (SQL) is used to manage data in all relational databases like DB2,Oracle, SQL Server etc. Majorly We can divide SQL into 4 Parts DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) TCL (Transaction Control Language).

Overview Of SQL Commands. To rename the DB object CREATE Create new database objects ALTER Modify existing database objects DROP Delete existing database objects TRUNCATE Remove all rows from table GRANT Provide access rights on database REVOKE Withdraw access rights on database RENAME DDL Data Definition Language DCL Data Control Language SQL Structured Query Language DML Data Manipulation Language TCL Transaction Control Language INSERT Create new rows in tables UPDATE Modify data in tables DELETE Delete data from tables SELECT Retrieve data from tables COMMIT Save database changes and end transaction ROLLBACK Undo changes that are not committed and end transaction.

SQL Character Data Types: SQL supports two character data types for storing printable and displayable characters. They are used for storing information like name, address, description etc. CHAR(n) VARCHAR2(n) Useful for Storing characters having pre-determined length Storing characters whose length vary a lot Storage size Size for n characters size for actual no. of characters + fixed size to store length Storage Trailing spaces are applied if data to be stored has Trailing spaces are not applied. smaller length than n. Max size 2000 bytes 4000 bytes Example A CHAR(10) field will store "Hello" as 10 bytes A VARCHAR2(10) field will store "Hello" by appending 5 trailing spaces. as 7 bytes (assuming 2 bytes to store length). Alte Name CHARACTER(n) CHARACTER VARYING(n).

SQL supports SMALLINT, INTEGER and INT data types that are used for storing whole numbers. Unlike other databases Oracle does not define different size limits for them. They are all treated internally to have 38 digits of precision. SQL Non-Integral Data Types: Nonintegral data types have an integer part and a fractional part. Either NUMERIC, DECIMAL or NUMBER data types can be used to store nonintegral numbers. Ex: Number (3,1) In the Above example first parameter is total number digits in the given value Second parameter is number of digits after the Decimal point Number (3)  555 Number (3,2)  5.55 Number (3,1)  55.5 Number (3,3)  0.555.

. SQL Date & Time Data Types:. Date Data Type It is used to store date and time information The dates can be specified as literals using Gregorian Calendar The default format in ORACLE is DD-MM-YY.

DDL Creating A Table: CREATE TABLE Command Syntax: CREATE TABLE <table name>( Column_name1 data type (column width) [constraints], Column_name2 data type (column width) [constraints], Column_name3 data type (column width) [constraints], ……………………………………….. ); Guidelines for creation of table: Table name should start with an alphabet. In table name, blank spaces and single quotes are not allowed. Reserve words of that DBMS cannot be used as table name. Proper data types and size should be specified. Unique column name should be specified..

DDL Creating A Table: Example: CREATE TABLE Student ( StudentId NUMBER, FName VARCHAR2(15), LName VARCHAR2(20), Gender CHAR(1), DOB DATE);.

. SQL constraints are used to specify rules for data in a table..

NOT NULL Constraint prevents a column from accepting NULL values. NOT NULL can only be applied as a column level constraint. Constraint name is optional and it can be specified by using CONSTRAINT keyword. CREATE TABLE Student ( StudentId NUMBER CONSTRAINT Stud_SId_nn NOT NULL, FName VARCHAR2(10) NOT NULL, LName VARCHAR2(10));.

A column can be given the default value by using DEFAULT option. The data type of column and default expression must be the same. DEFAULT option can be provided for nullable as well as NOT NULL attributes. Oracle database does not consider DEFAULT as a constraint. CREATE TABLE Student ( StudentId NUMBER, FName VARCHAR2(10), DOJ DATE DEFAULT SYSDATE); In The Above Table Definitions Whenever Input Is Not Provided Into The DOJ Field, The Default Value SYSDATE Will Be Taken.

PRIMARY KEY constraint on a column ensures that the column cannot contain NULL and duplicate values. We can have only one PRIMARY KEY in a table. CREATE TABLE Student ( StudentId NUMBER CONSTRAINT stud_sid_pk PRIMARY KEY, FName VARCHAR2(10), ContactNo NUMBER(10));.

CHECK constraint is used to limit the values that can be specified for a column. CREATE TABLE Student ( StudentId NUMBER, FName VARCHAR2(10), Gender CHAR (1) CONSTRAINT Stud_gender_ck1 CHECK (Gender IN('M', 'F')));.

UNIQUE constraint on a column ensures that two rows in a table cannot have same value in that column. Unlike Primary Key, UNIQUE constraint allows NULL values. A table can have many UNIQUE constraints. CREATE TABLE Student ( StudentId NUMBER , FName VARCHAR2(10), ContactNo NUMBER(10) CONSTRAINT Stud_cno_uk UNIQUE);.

Marks Table Without Connecting To Student CREATE TABLE Marks( CourseId NUMBER , StudentId NUMBER , MarksScored DECIMAL(5,2)); Marks Table With Connecting To Student Through Foreign Key CREATE TABLE Marks( CourseId NUMBER , StudentId NUMBER CONSTRAINT marks_sid_fk REFERENCES Student(StudentId), MarksScored DECIMAL(5,2));.

Data Definition Language is used to specify the structure i.e. schema of a relational database. DDL provides commands for creation, modification and deletion of various database objects like tables, views, stored procedures, indexes, constraints etc. The output of DDL is placed in data dictionary which contains metadata i.e. data about data. The Commands IN DDL Create: Creates a new database objects like table,view,sequences,indexes Alter: Modifies the existing database object Drop: Delete exiting database object Truncate: Deletes all rows from the database object Rename : (oracle 9i) Rename the database object.

ALTER TABLE Command: This command is used for modification of existing structure of the table in the following situation: When a new column is to be added to the table structure. When the existing column definition has to be changed, i.e., changing the width of the data type or the data type itself. When integrity constraints have to be included or dropped. When a constraint has to be enabled or disabled. Syntax ALTER TABLE <table name> ADD (<column name> <data type>…); ALTER TABLE <table name> MODIFY (<column name> <data type>…); ALTER TABLE <table name> ADD CONSTRAINT <constraint name> < constraint type>(field name); ALTER TABLE <table name> DROP<constraint name>; ALTER TABLE <table name> ENABLE/DISABLE <constraint name>;.

ALTERING TABLE Example: Altering Table To Add A New Column ALTER TABLE Student ADD Address varchar2(20) ALTER TABLE Student ADD (Course VARCHAR2 (20), Marks NUMBER (10)); // Adding Two Columns Altering Table To Modify The Existing Column ALTER TABLE Student MODIFY Address varchar2 (50) Altering Table To Rename A Column Name Syntax: ALTER TABLE <table name> RENAME COLUMN <old column name> TO <new column name>; Example: ALTER TABLE Student RENAME COLUMN Address To ResidentialAddress ;.

Altering Table To Drop An Existing Column ALTER TABLE Student Drop (ResidentialAddress) ALTER TABLE Student DROP (GNDR, MobNo); // Dropping Two Columns Altering Table To Add A Constraint ALTER TABLE Student Add Constraint Studid_PK PRIMARY KEY (StudentID) Altering Table To Drop An Existing Constraint ALTER TABLE Student Drop constraint Studid_PK.

DROP TABLE Student; Get it back from recycle bin. Syntax: flashback table tablename to before drop; To drop permanently: Syntax: drop table tablename purge ; Example: (dropping a table oracle 10g Enterprise Edition) SQL> drop table first; Table dropped. Get it back the table: SQL> flashback table first to before drop;.

Truncate: Oracle 7.0 introduced truncate command , whenever we are using “truncate” command total data permanently deleted from table. Syntax: truncate table tablename ; SQL> truncate table first; Rename: It is used to rename a table and renaming a column also. Renaming a Table: Syntax: rename old_tablename to new_tablename ; Example: rename first to last; Renaming a Column: (oracle 9i) Syntax: alter table tablevname rename column old_column_name to new_column_name ; Example: SQL> alter table emp rename column empno to sno ;.

Creating a new table from another table: Syntax: create table newtablename as select * from existingtablename ; Example: SQL> create table test as select * from emp; Note: In all database systems whenever we are copying a table from another table constraints are never copied. (Primary table, Foreign key,…..); Creating a new table from existing table without copying data: Syntax: create table newtablename as select * from existingtablename where falsecondition ; Example: SQL> create table test1 as select * from emp where 1=2;.

QUESTION You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task? ALTER TABLE students ADD PRIMARY KEY student_id ; B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY ( student_id ); C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id ; D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY ( student_id ); E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY ( student_id );.

Data Manipulation Language enables users to access or manipulate data in a relational database. DML provides commands for retrieval, creation, deletion and modification of information in a database. The Commands In DML Insert Creates new rows in the table Update Modify data in the tables Delete Delete the data from the tables Select Retrieves the data from the tables merge (oracle 9i ) Merge is an DML command which is used to transfer data from “source table” into “target table” when table structure are same.

Inserting Data into Tables: - once a table is created the most natural thing to do is load this table with data to be manipulated later. Syntax: insert into < tablename > (<col1>,<col2>) values(<exp>,<exp>); Example : SQL> create table first( sno number(10), name varchar(20)); Inserting data into table: SQL> insert into first values(1, ‘ abc ’); SQL> insert into first values(2, ‘ sachin ’);.

Delete operations. a) remove all rows Syntax: delete from < tablename >; b) removal of a specified row/s Syntax: delete from < tablename > where <condition>;.

Updating the contents of a table a) updating all rows Syntax: UPDATE <TABLENAME> SET <COL>=<EXP>,<COL>=<EXP>; b) updating selected records. Syntax: UPDATE<TABLENAME> SET <COL>=<EXP>,<COL>=<EXP> WHERE <CONDITION>;.

SELECT Statement This statement is used for retrieving information from the databases. It can be coupled with many clauses. Query : It is an operation that retrieves data from one or more tables or views Operators Used in “Select” statement: 1. Arithmetic operator(+,-,*,/) 2. Relational operator(=,<,<=,>,>=,[<> or!=]not equal). 3. Logical operator (AND,OR,NOT) Operator Precedence The basic operators used in SQL are * / + - Operators of the same priority are evaluated From Left to right Parentheses are used to force prioritized evaluation..

SELECT Statement This statement is used for retrieving information from the databases. It can be coupled with many clauses. Let us discuss these clauses in more detail: Using Arithmetic operator Example: SELECT ENAME, SAL, SAL+300 FROM EMP; Operator Precedence The basic operators used in SQL are * / + - Operators of the same priority are evaluated From Left to right Parentheses are used to force prioritized evaluation. Example: SELECT ENAME, SAL, 12*SAL+100 FROM EMP;.