Programs

Programs

 

The Relational Data Model

Table is an essential element of the relational data model:

  • The number of columns is fixed.
  • With each column is linked to the name and the domain, specifying a set of values that can occur in the column.
  • At the intersection of row and column is a single value which belongs to the field column.
  • Row represents a single record of information
  • In the relational model we disregard the order of rows (records) and columns (fields in the record).

Example

EMP

EMPNO ENAME     JOB       MGR HIREDATE   SAL COMM DEPTNO

—– ——- ——— —- ——— —– —- ——

7839 KING    PRESIDENT NULL 17-NOV-81  5000 NULL     10

7698 BLAKE   MANAGER   7839 01-MAY-81  2850 NULL     30

7782 CLARK   MANAGER   7839 09-JUN-81  2450 NULL     10

7566 JONES   MANAGER   7839 02-APR-81  2975 NULL     20

7654 MARTIN  SALESMAN  7698 28-SEP-81  1250 1400     30

7499 ALLEN   SALESMAN  7698 20-FEB-81  1600  300     30

7844 TURNER  SALESMAN  7698 08-SEP-81  1500    0     30

7900 JAMES   CLERK     7698 03-DEC-81   950 NULL     30

7521 WARD    SALESMAN  7698 22-FEB-81  1250  500     30

7902 FORD    ANALYST   7566 03-DEC-81  3000 NULL     20

7369 SMITH   CLERK     7902 17-DEC-80   800 NULL     20

7788 SCOTT   ANALYST   7566 09-DEC-82  3000 NULL     20

7876 ADAMS   CLERK     7788 12-JAN-83  1100 NULL     20

7934 MILLER  CLERK     7782 23-JAN-82  1300 NULL     10

The table DEPT save the information on the sectors of the sample companies.

DEPT

DEPTNO DNAME       LOC—— ———– ——–    10 ACCOUNTING  NEW YORK    20 RESEARCH    DALLAS    30 SALES       CHICAGO    40 OPERATIONS  BOSTON

The table SALGRADE save information about classes grade wage workers hypothetical company.

SALGRADE

GRADE LOSAL HISAL—– —– —–    1   700  1300    2  1201  1400    3  1401  2000    4  2001  3000    5  3001  8000   Deptno  column Represents the relationship of the employee with the department, for which the information is in a different table. using the ID we can recognize in another table row the appropriate department and read the information on it.It is important, therefore, that this identifier uniquely defined the department. In the relational model, there is no other possibility of identifying a row only by the values of columns that uniquely identify a row.

Primary key and unambiguous

For each table must be specified unique identifier called a primary key – set composed of one or more columns in which values uniquely identify the row.

Unique key has the same property as the primary key. A primary key is the only one key clear in the table can be more than one.

The Emp table the primary key is a unique number EmpNo employee. Ename does not have to be the key!

In the table Dept primary key is the unique number of the department deptno.

The table Salgrade primary key is a unique number grade Grade.

Foreign key

A foreign key is a set composed of one or more columns whose values are determined as a value or a unique primary key in the same or a different table and are interpreted as indicators for the other rows in the table.

The table Emp foreign key number is DEPTNO whose values are derived from the DEPTNO column in the table Dept.

The table Emp foreign key is also the number Mgr whose values are derived from the EMPNO column in the same table Emp.

Data types

  1. Types of String
  • CHARACTER(N), CHAR(N)character string constant length N
  • NATIONAL CHARACTER (N), NATIONAL CHAR (N) – the inscription fixed-length character N written in the alphabet of the national (the Oracle NCHAR (N)).
  • CHARACTER VARYING (N) VARCHAR (N) – variable-length character string (Oracle VARCHAR (N) and recommended VARCHAR2 (N)).
  • NATIONAL CHARACTER VARYING (N), NATIONAL CHAR VARYING (N), NCHAR VARYING (N) – variable length character string stored in the national alphabet (in Oracle NVARCHAR2 (N)).
  • BIT (N) – a sequence of bits of length N. bit strings are mainly used to represent the image and sound data.
  • BIT VARYING (N) – a sequence of bits of variable length (in Oracle RAW (n) and LONG RAW).
  1. The numeric types:
  • NUMERIC, DECIMAL, NUMERIC (p, s) – decimal type (in Oracle NUMBER (p, s)).
  • FLOAT, REAL – floating point type.
  • INTEGER – integer type.
  1. Types dates:
  • DATE (date), TIME (hour), TIMESTAMP (date and time) – the type of extension data contained in the data type DATE (In Oracle does not have the type TIME).
  1. Types interval – starting with the word INTERVAL.
  2. W Oraclesą jeszcze typy:
  • LONG – dokumenty tekstowe (w jednej tabeli może być tylko jedna kolumna tego typu).
  • BLOB – duże obiekty binarne.
  • BFILE – duże obiekty binarne zapisywane w plikach.
  • CLOB – duże obiekty tekstowe.
  • NCLOB – duże obiekty tekstowe zapisane w alfabecie narodowym.
  • ROWID – identyfikatory (adresy) wierszy tabel.
  1. Nowa wersja standardu SQL’1999wprowadza cztery nowe typy danych:
  • LOB – duże obiekty albo binarne, albo tekstowe.
  • BLOB – duże obiekty binarne – podtyp typu LOB.
  • CLOB – duże obiekty tekstowe – podtyp typu LOB.
  • BOOLEAN – wartości logiczne true, false i unknown.

Create table

The table is created with the following statement:

CREATE TABLE table_name (

column_name datatype , bonds of cohesion…);

basic bonds of cohesion:

NOT NULL – pseudo-Null is not allowed in a column,

PRIMARY KEY – this column is the primary key,

REFERENCES table_name – this column is a foreign key references the primary key of the specified table.

CREATE TABLE Dept(
Deptno NUMBER(2) PRIMARY KEY,
Dname VARCHAR2(14),
Loc VARCHAR2(13)
);

CREATE TABLE Emp(
Empno NUMBER(4) PRIMARY KEY,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4) REFERENCES Emp,
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL REFERENCES Dept
);

CREATE TABLE Salgrade(
Grade NUMBER(2) PRIMARY KEY,
Losal NUMBER(7,2) NOT NULL,
Hisal NUMBER(7,2) NOT NULL,
);

View the contents of the table Dept is done using the instructions:

SELECT * FROM Dept;

 

Deleting a table

DROP TABLE table_name;

When other tables have foreign keys that relate to the table, the removal fails.

Only use additional clause CASCADE CONSTRAINTS Oracle will remove the table Dept, of course, together with constraints referential integrity.

DROP TABLE Dept CASCADE CONSTRAINTS;

Changing table schema

Diagram of an existing table can be changed using the ALTER TABLE statement.

ALTER TABLE table_name clause schema changes;

It could be:

-adding a new column (using clause ADD),

-extension of the data type of an existing column (using the ALTER COLUMN clause, and Oracle using the MODIFY clause)

-remove a column (by clause DROP)

-as well as adding new bonds of cohesion.

ALTER TABLE Emp DROP ……;

Inserting data

The table in the database, the individual lines are inserted using the INSERT statement.

INSERT INTO table_name

VALUES (value, …);

INSERT INTO Emp VALUES (9901, ‚Smith’, ‚SALESMAN’, 9345, ’23-JAN-90′,300,100,12);

Modifying data

 

UPDATE table_name

SET column_name = expression, … [WHERE condition];

Example

Pick up all the vendors (SALESMAN) Sal earnings by 10%.

UPDATE Emp SET Sal = Sal*1.1
WHERE Job = ‚SALESMAN’;

Deleting data

 

DELETE FROM table_name

[WHERE condition];

Delete all employees who do not have a specific position (Job IS NULL).

DELETE FROM Emp
WHERE Job IS NULL;

Approval of changes to the database

INSERT, DELETE and UPDATE do not make themselves permanent changes to the database. To make the changes introduced by not fix, follow the instructions:

COMMIT [WORK];

You can also opt out of changes to the database, backing them with instructions:

ROLLBACK [WORK];

Simple question

 

SELECT statement, which defines the tables in the database can be imported data.

SELECT [DISTINCT] column_name, …

FROM table_name

[WHERE condition];

Both clauses SELECT and FROM are required for each request – while the DISTINCT clause and WHERE are optional.

 

Example

List the data of the names, salaries and positions of employees.

SELECT Ename, Sal, Job
FROM Emp;

SELECT with * indicates print data from any table columns

SELECT *

FROM Emp;

List the data of the names, salaries and positions of employees who work in department number 10.

In this embodiment, in terms of reduction or discharged results of their selection on a certain condition. This condition is written in the WHERE clause:

SELECT Emp.Ename, Emp.Sal, Emp.Job

FROM Emp

WHERE Emp.Deptno = 10;

May include comparison of the two different columns, eg. Employee’s earnings from the commission:

SELECT Emp.Ename, Emp.Sal, Emp.Comm

FROM Emp

WHERE Emp.Sal <= Emp.Comm;

The WHERE clause can also be a conjunction (AND), alternative (OR) or negation (NOT) other logical conditions.

Here is an example conjunction of two conditions – in-written poems both earnings to be greater than 1100, and the position has to be equal to ‚CLERK’:

SELECT Emp.Empno, Emp.Ename, Emp.Job, Emp.Sal
FROM Emp
WHERE Emp.Sal >= 1100 AND Emp.Job=’CLERK’;

Keep in mind that when calculating the value of expressions valid set precedence for evaluation of operators. In the case of logical operators, the following sequence:

  1. NOT
  2. AND
  3. OR

This order can be changed using parentheses.

Repeated rows are not automatically eliminated from the query results. The word DISTINCT means eliminating redundant lines.

Example

List the IDs of people who have subordinates, ie. Which are managers of some employees.

If we had not posted a DISTINCT, each employee was discharged identifier his head, and so many ID would recurred.

Query results can be sorted in ascending order ASC (default) or descending order – then use the keyword DESC.

SELECT [DISTINCT] expression [[AS] alias] …

FROM table_name

[WHERE condition]

[ORDER BY expression [ASC | DESC], …];

In order to calculate the employee’s period of employment from the date of employment to this day – in full years:

SELECT Emp.Empno, Emp.Ename,
Trunc(Months_Between(Sysdate,Emp.Hiredate)/12)  employment
FROM Emp
ORDER BY employment DESC;