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).
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.
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.
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.
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.
- 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).
- The numeric types:
- NUMERIC, DECIMAL, NUMERIC (p, s) – decimal type (in Oracle NUMBER (p, s)).
- FLOAT, REAL – floating point type.
- INTEGER – integer type.
- 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).
- Types interval – starting with the word INTERVAL.
- 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.
- 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.
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,
CREATE TABLE Emp(
Empno NUMBER(4) PRIMARY KEY,
Mgr NUMBER(4) REFERENCES Emp,
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 ……;
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);
SET column_name = expression, … [WHERE condition];
Pick up all the vendors (SALESMAN) Sal earnings by 10%.
UPDATE Emp SET Sal = Sal*1.1
WHERE Job = ‘SALESMAN’;
DELETE FROM table_name
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:
You can also opt out of changes to the database, backing them with instructions:
SELECT statement, which defines the tables in the database can be imported data.
SELECT [DISTINCT] column_name, …
Both clauses SELECT and FROM are required for each request – while the DISTINCT clause and WHERE are optional.
List the data of the names, salaries and positions of employees.
SELECT Ename, Sal, Job
SELECT with * indicates print data from any table columns
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
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
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
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:
This order can be changed using parentheses.
Repeated rows are not automatically eliminated from the query results. The word DISTINCT means eliminating redundant lines.
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] …
[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,
ORDER BY employment DESC;