Database Management System - 1, MCA DBMS Material Overview

Characteristics of DBMS

• Data Integrity
• Security
• Consistency
• Roll-back
• Data Independence
• Back-up Procedures
• Concurrency Control
• Version Control

DBMS Model

• Hierarchical Model
• Network Model
• Relational Model

Components of DBMS

• Data
• Users
• Report Writing
• Query Language
• Spreadsheets
• End-user Interfaces
• Hardware
• Software
• Command
• Editing
• Queries
• Reporting
• Analysis

Types of DBMS

• Operational Databases
• Analytical Databases
• Distributed Databases
• Personal End User Databases
• Multimedia Databases
• Special Purpose Databases

Transaction Management

• Concept of Transaction
• Transaction Outcomes
• Transaction Support
• Properties of Transactions
• Atomicity
• Consistency
• Independence
• Durability

Primary, Foreign and Candidate Keys

• Identify and define the primary key attributes for each entity
• Validate primary keys and relationships
• Migrate the primary keys to establish foreign keys

·                                                         Define Primary Key attributes

·                                                         Validate Keys and Relationships

·                                                         A foreign key

·                                                         Candidate key

            Uniqueness property – No two distinct tuple of R have the same value for K

            Irreducibility property – No proper subset of K has uniqueness property

Functions of a DBMS

• Data Definition
• Data Manipulation
• Data Security & Integrity
• Data Recovery & Concurrency
• Data Dictionary Maintenance
• Performance

Role of the Database Administrator

• Defining the Schema
• Liaising with Users
• Defining Security & Integrity Checks
• Defining Backup / Recovery Procedures
• Monitoring Performance

Normalization

• Basic Concepts
• Functional Dependencies
• Simple Data
• S# - supplier identification number (this is the primary key)
• Status – status code assigned to city
• City – name of city where supplier is located
• P# - part number of part supplied
• Qty> - quantity of parts supplied to date

o   First Normal Form

• Insert
• Delete
• Update

• Second Normal Form
• S# - city, status
• City – status
• (s#,p#) – qty

The Entity – Relationship Model

• Entities
• Attributes
• Connectivity and Cardinality
• One to one (1:1)
• One to many (1:N)
• Many to many (M:M)

o   ER Notation

DBMS Architecture

o   External – individual user view

o   Conceptual – community user view

o   Internal – physical or storage view

Advantages of DBMS

• Reduction of redundancies
• Shared Data
• Integrity
• Security
• Conflict Resolution
• Data Independence

Disadvantages of DBMS

• Larger file size
• Increased complexity
• Greater impact of failure
• More difficult recovery

Properties of  DBMS

• There are no duplicate tuples
• Tuples are unordered (top to bottom)
• Attributes are unordered (left to right)
• All Attribute values are atomic

Codd’s Twelve Rules

• The information rule
• Guaranteed access rule
• Systematic treatment of null values
• Dynamic online catalog based on the relational model
• Comprehensive data sub language rule
• Data definition
• View definition
• Data manipulation (interactive and by program)
• Integrity constraints
• Authorization
• Transaction boundaries (begin, commit and rollback)

o   View updating rule

o   High – level insert, update and delete

o   Physical data independence

o   Logical data independence

o   Integrity independence

o   Distribution independence

o   Non subversion rule

DDL (Data Definition Language)

o   Create

o   Drop

o   Alter

o   Truncate

o   DBCC (Database Console Commands)

DML (Data Manipulation Language)

• Data Manipulation is:
• Retrieval of information from the database
• Insertion of new information into the database
• Deletion of information in the database
• Modification of information in the database
• A DML is a language which enables users to access and manipulate data

Two types of DML

o   Procedural

o   Nonprocedural

o   Easier for user

o   May not generate code as efficient as that produced by procedural languages

DCL (Data Control Language)

• GRANT to allow specified users to perform specified tasks
• REVOKE to cancel previously granted or denied permissions

• CONNENCT allows a user to connect to the database or schema
• SELECT allows a user to select records
• INSERT allows a user to insert records
• UPDATE allows a user to update records
• DELETE allows a user to delete records
• USAGE allows a user to use a database object such as a schema or a function

ACID (Atomicity, Consistency, Independence and Durability)

Read More

Datatypes Used in Oracle 9i

(Page 1 of 3 )

For those who use databases but are not familar to the Oracle 9i database, perhaps you should read about the datatypes used in Oracle 9i and how they can be used to support object orientation.

It is assumed that you have read my previous Oracle article based on creating an Oracle database in the latest Oracle database 9i. Before we can create a table, one should sit down and take the time to get to know the datatypes available for Oracle.
Also, upon reading articles relating to the Oracle database, you should have come across the term Abstract datatypes. This article will discuss abstract datatypes in depth so that creating a table, which is designed to include abstract datatypes, will inevitably become more understandable. These abstract datatypes, which I like to call user-defined types, are datatypes that behave like objects.

Oracle Datatypes
These Oracle datatypes are as follows:
Character Strings

• CHAR (size) – A fixed-sized field of characters. The largest this particular datatype can become is 2000 bytes. In other words, it can only hold 2000 characters. If you don’t specify the length of the CHAR datatype, the default size is a single character (i.e. 1 byte).

• NCHAR (size) – A fixed-sized field of characters, where the character set is determined by its definition. So, the maximum size is 2000 bytes per row or 2000 characters. This handles multibyte character sets.

• VARCHAR2 (size) – A variable-sized field of characters. The largest this datatype can become is 4000 characters.

• NVARCHAR2 (size) – A variable-sized field of characters, where the character set is determined by its definition. The maximum size is 4000 bytes per row or 4000 characters. This handles multibyte character sets.

Note: The VARCHAR2 datatype is the successor of VARCHAR. So it is recommended that you use VARCHAR2 as a variable-sized array of characters.

• LONG – A variable-sized field of characters. The maximum size of this field is 2GB.

Number

• NUMBER (precision, scale) – A variable-sized number, where the precision is between 1 and 38 and size is between -84 and 127. A NUMBER datatype with only one parameter is NUMBER (precision), where the parameter specifies the precision of the number. A NUMBER datatype with no parameters is set to its maximum size.

Date and Time

• DATE – A fixed-sized 7 bit field that is used to store dates. One thing to note is that the time is stored as part of the date. The default format DD-MON-YY can be overridden by NLS_DATE_FORMAT.

• TIMESTAMP (precision) – A variable-sized value ranging from 7 to 11 bytes, that is used to represent a date/time value. It includes both date and time. The precision parameter determines how many numbers are in the fractional part of SECOND field. The precision of the SECOND field within the TIMESTAMP value may have a value ranging from 0 to 9 with a default precision of 6.

• TIMESTAMP (precision) WITH TIME ZONE – A fixed-sized value of 13 bytes, which represents a date/time value along with a time zone setting. There are two ways one can set the time zone. The first is by using the UTC offset, say ‘+10:0’, or secondly by the region name, say ‘Australia/Sydney’.

• TIMESTAMP (precision) WITH LOCAL TIME – A variable value ranging from 7 to 11 bytes. This particular datatype is similar to the TIMESTAMP WITH TIME ZONE datatype. The difference is that the data is normalised to the database time zone when stored. The entry is manipulated to concur with the client’s time zone when retrieved.

Intervals

• INTERVAL DAY (day_precision) TO SECOND (second_precision) – A fixed-sized 11 byte value that represents a period of time. It includes days, hours, minutes and seconds.

• INTERVAL YEAR (year_precision) TO MONTH - A fixed-sized 5 byte value that represents a period of time. It includes years and months.

Binaries

• RAW (size) – A variable-sized field of raw binary data. The maximum size for this datatype is 2000 bytes.

• LONG RAW - A variable-sized field of raw binary data. The maximum size for this datatype is 2 GB.

• BLOB – The Binary Large Object is a field that holds unstructured binary data. The maximum size for this datatype is 4 GB.

• CLOB – The Character Large Object is a field that holds single byte character data. The maximum size for this datatype is 4 GB.

• NCLOB – The National Character Large Object is a field that holds either single byte of multibyte character data dependent on the national character set. The maximum size for this datatype is 4 GB.

• BFILE – An external binary file. The maximum size for this file is 4 GB. The size is also limited by the operating system.

Rows

• ROWID – A datatype that contains binary data that is used to identify a row.

Each ROWID is:

• 6 bytes for normal indexes on non-partitioned tables, local indexes on partitioned tables and row pointers for chained/migrated rows.
• 10 bytes for global indexes on partitioned tables.

• UROWID – The Universal ROWID is the datatype used to store both logical and physical ROWID values as well as foreign tables accessed through a gateway.

Alternatives for ANSI Standard Datatypes
Instead of using ANSI standard datatypes, you can use Oracle defined datatypes. View the table below to see the Oracle datatype alternative for ANSI standard datatypes.

ANSI Standard	Oracle Datatype
CHARACTER and CHAR	CHAR
CHARACTER VARYING and CHAR VARYING	VARCHAR2
NUMERIC, DECIMAL, DEC, INTEGER, INT and SMALLINT	NUMBER
FLOAT, REAL, DOUBLE PRECISION	FLOAT

Abstract Datatypes
In Oracle, one may create there own datatypes. Abstract datatypes allow Oracle to hold a range of datatypes. So, an abstract datatypes can have many parts to it. To do this one needs to create the datatype as an object. This object is made up of one or more datatypes.
Example of an Abstract Datatype
Let’s say that we want a datatype to split up a person’s address. The abstract datatype may be,
CREATE OR REPLACE TYPE persons_address AS OBJECT
(
          v_streetNumber                      NUMBER,
          v_streetName                         VARCHAR2(30),
          v_citySuburb                          VARCHAR2(30),
          v_state                                  VARCHAR2(4),
          v_postCode                            NUMBER
);
When we create a table that references this abstract datatype the values must be inserted as
persons_address(21, ‘Kings Street’, ‘Junkville’, TN, 12345)
You should now know what datatypes exist in Oracle 9i. You should also understand how one could use this datatypes to create abstract datatypes and hence support object orientation.
Now you understand how datatypes work in Oracle, you should be ready to create Oracle tables.

Read More

differences between sql & plsql

DIFFERENCES

Sql	Plsql
SQL stands for Structured Query Language, which does not have procedural programming capability.	PL/SQL stands for Procedural Structured Query Language. Which have advantage over SQL.
SQL is the language that enables relational database users to communicate with the database in a straightforward manner.	PL/SQL is Oracle's procedural Language it comprises the Standard language of SQL and a Wide array of commands that Enable you to control the Execution of SQL statements According to different conditions.
You can use SQL commands to query the database and modify tables within the database.	PL/SQL can also handle runtime Errors.
No Programming flexibility available with SQL.	Programmers can divide functions into logical blocks of code. Modular programming techniques support flexibility during the application development.

Implicit cursor	Explicit Cursor
If  the oracle engine for its internal processing has opened a cursor they are known as IMPLICIT CURSOR.	A cursor defined by user for processing data is known as EXPLICIT CURSOR.
In implicit cursor there is no need to open, fetch and close the cursor it is implicitly maintained by oracle engine.	While in explicit cursor we have to open, close and fetch the data of an cursor.
In implicit cursor when a attributes are used they are used with ‘sql’ keyword. Ex: -sql%isopen	While in explicit cursor a cursor name is appended to the attribute name when referencing the attribute. Ex: - c_emp%isopen.
In implicit cursor sql%isopen always evaluates to be false because oracle engine automatically maintains it.	While in explicit cursor if cursor is open it evaluates to be true otherwise false.

Functions	Procedures
A function must return a value back to the caller.	A multiple values can be passed to the caller.
In function only one type of mode is available. i.e. IN	While in procedure three types of mode are available. i.e. IN,OUT,INOUT.
In function for returning the value ‘return’ keyword is used.	While in procedure there is no need to use such keyword for returning the values.

Primary Key	Foreign Key
The field on which primary key is given allows unique values only.	While the field on which foreign key is given no such restriction is their.
The field on which primary key is given does not allow NULL values.	While foreign key allows NULL values.
Primary key constraint is independent.	Foreign is dependent on  a table on which primary key is given.
We can’t delete a record from a master table on which primary key is given if that record is available in child table.	We can delete a record from a table on which foreign key is given.
In primary key there is no need to provide references.	In foreign key references are to be provided. So it is known as referential integrity constraint.

In	Out
In mode is available in functions and procedure.	While out mode is available in procedure.
In mode is used to pass data into function or procedure from calling block.	Out mode is used to return the values from procedure to calling block.
In is the default mode.	OUT is not default.It must be specified explicitly.

TOP:4 Difference between DBMS v/s RDBMS

DBMS

RDBMS

The concept of relationships

is missing in a DBMS. If it

exists it is very less.

It is based on the concept

of relationships.

Speed of operation is very

slow.

Speed of operation is very

fast.

Hardware and software

requirements are less.

Hardware and software

requirements are high.

Facilities and Utilities

offered are limited.

Facilities and Utilities

offered are many.

Platform is used is normally

DOS

Platform used can by any

DOS, UNIX, VAX,VMS etc.

Uses concept of a file.

 Uses concept of a table.

DBMS normally use a 3GL.

RDBMS normally use a 4GL.

Examples are dBASE,

FOXBASE etc.

Examples are ORACLE,

INGERS etc.

Read More

sql query exercise and assignment

DBMS Queries

Create Table : EMP

Column Name	Data Type	Size	Attributes
Empno	Number	4	Primary key
Ename	Varchar2	7	Not null
Job	Varchar2	9
Mgr	Number	4
Hiredate	Date
Sal	Number	7,2
Comm.	Number	7,
deptno	Number	2	Foreign key

Create Table : DEPT

Column Name	Data Type	Size	Attribute
Deptno	Number	2	Primary key
Dname	Varchar2	12	Not null
location	Varchar2	10

Create Table : SalGrade

Column Name	Data Type	Size
Grade	Number	4
Losal	Number	7,2
Hisal	Number	7,2

Insert the values:

Table:  EMP

Empno	Ename	Job	Mgr	Hiredate	Sal	Comm.	Deptno
7369	Smith	Clerk	7902	17-dec-80	800	0	20
7499	Allen	Salesman	7698	20-feb-81	1600	300	30
7521	Ward	Salesman	7698	22-feb-81	1250	500	30
7566	Jones	Manager	7839	02-apr-81	2975	0	20
7654	Martin	Salesman	7698	28-sep-81	1250	1400	30
7698	Blake	Manager	7839	01-may-81	2850	0	30
7782	Clark	Manager	7839	09-jun-81	2450	0	10
7788	Scott	Analyst	7566	19-apr-87	3000	0	20
7839	King	President	0	17-nov-81	5000	0	10
7844	Turner	Salesman	7698	8-sep-81	1500	0	30
7876	Adams	Clerk	7788	23-may-87	1100	0	20
7900	James	Clerk	7698	03-dec-81	950	0	30
7902	Ford	Analyst	7566	03-dec-81	3000	0	20
7934	Miller	Clerk	7782	23-jan-82	1300	0	10

Table : DEPT

Deptno	Dname	Location
10	Accounting	Newyork
20	Research	Dallas
30	Sales	Chicago
40	Operations	Boston

Table : SalGrade

Grade	Losal	Hisal
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

Assignment #1

1. List all rows of the table emp.

2. Display the structure of table emp.

3. List all table.

4. List all employees’ number from emp.

5. List all employees name and designation from emp.

6. List all commission from emp.

7. List all mgr from emp.

8. List all departments’ number from emp.

9. List all employees number,name,jobs,mgr from emp.

10.List all employees name,mgr from emp.

11.List all job,commission from emp.

12.List all employees name,department number from emp.

13.List all department number,employee name,salary,job from emp.

14.List all department number,employee name from emp.

15.List all grades from Sal grade.

16.List all losal,hisal,grade from salgrade.

Assignment #2

1. List all employees who are managers.

2. List all employees who are presidents.

3. List all employees who are either manager or clerk.

4. List all employees who are managers of department number 10.

5. List all employees who are working in department number 10.

6. List all employees who are not belonging to department number 20,30,40 and are not manager.

7. List all employees who are either manager of department number 10 or are analyst.

8. List all employees who are not president, analyst or salesman.

9. List all employees who work in department number 10 and their job is clerk.

Assignment #3

1. Change the column heading ename into your name and display it.

2. Change the column heading sal into salaries.

3. Change the column heading ename into 'employee name 'and job into designation

4. List all 'manager' and add 100 rupees in each salary.

5. List all employee and add 1000 rupees and subtract 4500 rupees in each salary.

6. If employee name is scott then add 100 rupees and divided by 100 rupees.

7. List all employee after adding 500 rupees,subtract 300 and divided by 100 rupees.

8. List all employes and find five percent from each salary.

9. Find eight percent of salesmen salary.

10.Find the two-month salary of each manager.

11.Find the daily wages of each employee.

12.Display the column heading annsal instead of sal * 12.

13.Combine empno and ename then change the column heading as employee.

14.Join ename and job then change the column heading as comp_name.

15.List all records as follows smith is clerk and he is working in dept no 20 since 12-mar-1981.

16.Find annual salary and add comm for all employees.

17.List all distinct department number from emp table.

18.Dispaly distinct value of department number and jobs.

19.List all employees who are not eligible for receiving commission and are 'manager'.

20.List all employees who are eligible of receiving commission.

21.List all distinct jobs from the table emp.

22.How many jobs do we have?

Assignment #4

1. List hiredate in descending order.

2. List hiredate in ascending order.

3. Find all department names with their department number while numbers greter then 20.

4. Find those employees whose commission is greter then their salaries.

5. Find those employees whose commission is less then their salaries.

6. Find all employees those salaries are between 500 and 1500.

7. Find those employees whose mgr is 7902, 7566, 7788.

8. To find those employees whose mgr is between 7788 and 7092.

9. List all employees whose name starts with 's'.

10.List all employees having first name as 'scott'.

11.List of those employees having only 5 character long name and job as a manager.

12.List all employees who does not have a manager (whose manager is null).

13.Find those employees whose job do not start with 'a'.

14.List of those employees whose mgr is not null.

15.List of those employees whose jobs are 'manager or clerk' of department number 10.

16.Find all manager and salesman their salaries over 1500.

17.Find the average salaries of different jobs.

18.Find the average salaries of all jobs excluding manager.

19.Find the average salaries of each job within department.

20.Find the average salaries of each department.

21.Find average and sum of the salaries of each job excluding 'salesman','clerk' and 'manager'.

Assignment #5

1. Find the absolute value of -18.

2. Find the absolute value of 30.

3. Find the celing value of 22.5.

4. Find the celing value of 31.2.

5. Find the Floor value of 37.2.

6. Display your name in lowercase.

7. Find the square root of 6.

8. Find the first and second character of Ename.

9. Display the year only from the hiredate.

10.Find the soundex of all ename.

11.Find the soundex of those employee whose job soundex to clark.

12.Find the minimum salary from the table emp.

13.Find the maximum salary from the table emp.

14.Find the length of all ename.

15.Find the length of job.

16.Find the ascii value of all employee name.

17.Find the value of 2 through ascii value.

18.Find the position of character ‘a’ in the job.

19.Find the user id which assigned by oracle.

21.Replace the value of deptno 10 with ‘ten no dept’,20 with ’20 dept no’, 30 with ’30 dept no’ else ‘not assigned’

22.Find the sum of all salary.

23.Find the all employees whose hire date is after Jan 80.

24.Find those employees whose department location is newyork.

25.Find those employees who are working in accounting department.

26.Define the the nvl substitution in comm. Field  as zero if any null  value in this field.

27.Calculate annual salary of employee and display the result as annual.

28.List all employees who are working in 'sales' department.

29.List all employees whose location is newyork and department name is accounting.

30.List all employees whose location is chicago.

31.Find the manager of 'Scott'.

32.Find the manager of 'Blake'

33.Find the salary of those employees who earn more then jones.

34.List those department who doesnot have any employees.

35.Find the union of department number 10 and department number 30.

36.Find the intersect of department number 10 and department number 30.

37.Find the minus of department number 10 and department number 30.

38.Find the minimum salary with employee name.

39.Find all employees who have the same job as blake.

40.Find the minimum salaries in each department.

41.Find all the department, which have an average salary is greater then department number 30.

42.Find all employees in department number 10 whose jobs the same as the employees job in the sales department.

43.Find all employees who earn more than any employees in department 30.

44.Find the job which has the highest average salary.

45. Find the grade of salary for each employee.

Assignment #6

1. Display the structure of the table emp.

2. Display system date

3. Count all employees.

4. Display the sum of all employees’ salaries.

5. How many managers do we have?

6. How many departments do we have?

7. List average salary of each department.

8. List average salary of each job.

9. Find the maximum minimum sal of all employees.

10.Find the day of all employees when they were hired.

11.Find the next Friday after the '12-mar-98'

12.Find the last day of each hiredate.

13.List all employees with their joining month only.

14.List all employees who were hired in year 1987.

15.List all employees who were hired from 30-mar81 to 01-jun-82.

16.Find the income tax of all employees 5% of its annual salary.

17.Display all records in following format (for all records) on employee <ename> work in department <name> and appointed on <date>.

18.Find the duration of service of all employees.

19.Count all employees who were hire after 22-mar-81.

20.List the most experienced employee.

21.Display the department number and their given names using decode () function.

          10 accounting.

          20 researches.

          30 sales.

22.Find the first occurrence of the letter 'l' in employee name and change 'l' with 'x'.

23.Find all employees whose department is not in the dept table.

Read More