1. .
2. .
3. .
, .
1. - , , . . .: . .: , 2003. 1088 .
2. . . , 8- : . . .: , 2005. 1328 .
3. .. : ; ; ; .: , 2002. 800 .
2
CASE- Oracle Designer CASE Studio.
Oracle Designer , .
Oracle Designer:
Entity Relationship Diagrammer ER-;
Database Desing Transformer ER- (Server Model)
Design Editor (Server Model)
Generate Database from Server Model SQL
CASE Studio ER- t IDEF1X. CASE Studio , , .. : ( ), ( ), , , (), . ( Database Convertion) . Generate script.
1. ER- Oracle Designer
() (, ) | |
(1,0):(N,1) | |
(1,0):(N,0) | |
(1,1):(N,1) | |
(1,1):(1,1) | |
(N,0):(N,0) | |
# | |
* | (NOT NULL) |
O | (NULL) |
2. ER- CASE Studio
(0,N):(1,1) | |
(1,N):(1,1) | |
(1,1):(1,1) | |
(0,1):(0,1) | |
(N:N) | |
(1,1):(1,N) | |
(1,1):(0,N) | |
(PK) | |
(FK) |
|
|
1. .. : ; ; ; .: , 2002. 800 .
2. . , 9- : . . .: , 2005. 864 .
3. , - . Oracle Desiner. , 2- .: Ȼ, 1999. 592 .
4. .., .., .. : , 5- .: -; .: , 2006. 736 .
5. http://www.idefinfo.ru ( )
6. http://www.intuit.ru/department/database/basedbw/3/3.html ( CASEStudio).
7. http://www.interface.ru/oracle/des2000x.html ( Oracle Designer).
3
3. SQL
Data Definition Language (DDL) | CREATE TABLE | |
DROP TABLE | ||
ALTER TABLE | ||
CREATE INDEX | ||
DROP INDEX | ||
CREATE VIEW | ||
DROP VIEW | ||
Data Manipulation Language (DML) | SELECT | |
UPDATE | ||
INSERT | ||
DELETE |
4.
SUM([DISTINCT] ) | [] | , NULL |
AVG([DISTINCT] ) | [] | , NULL |
COUNT([DISTINCT] ) | [] | , NULL |
COUNT(*) | NULL | |
MAX() | , NULL | |
MIN() | , NULL |
SQL .
1.
, CREATE TABLE.
CREATE TABLE films (
film_id INTEGER NOT NULL,
film_name VARCHAR(100) NOT NULL,
film_time time,
film_director VARCHAR(50) NOT NULL,
film_actors VARCHAR(255),
film_year INTEGER NOT NULL,
PRIMARY KEY (film_id))
FILMS , .
2.
ALTER TABLE.
ALTER TABLE film_distributions ADD FOREIGN KEY (film_id) REFERENCES films(film_id) ON DELETE CASCADE
3.
DROP TABLE
DROP TABLE films
4.
SQL- SELECT.
|
|
SELECT * FROM films
WHERE films.film_year=1999
1999 :
FILM_ID | FILM_NAME | FILM_TIME | FILM_DIRECTOR | FILM_ACTORS | FILM_YEAR |
Ghost Dog: The Way of the Samurai | Jim Jarmusch | Forest Whitaker,John Tormey,Cliff Gorman,Henry Silva,Isaach de Bankole,Frank Minucci | |||
Man on the moon | Milos Forman | Jim Carrey,Danny DeVito,Courtney Love,Paul Giamatti,Vincent Schiavelli |
:
1. WHERE;
2. LEFT (RIGHT, FULL) OUTER JOIN.
.
5.
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id,
clients
WHERE clients.client_id=rented_films.client_id
CLIENT_FIO | RENT_START_DATE | RENT_END_DATE |
28.06.2006 | 29.08.2006 | |
12.09.2006 | 13.09.2006 | |
11.10.2005 | 13.10.2005 | |
01.05.2006 | 02.05.2006 | |
05.06.2006 | 06.06.2006 | |
13.09.2006 | NULL |
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films,
returned_rented_films,
clients
WHERE clients.client_id=rented_films.client_id AND
returned_rented_films.rent_id=rented_films.rent_id
CLIENT_FIO | RENT_START_DATE | RENT_END_DATE |
12.09.2006 | 13.09.2006 | |
28.06.2006 | 29.08.2006 | |
28.06.2006 | 29.08.2006 | |
01.05.2006 | 02.05.2006 | |
05.06.2006 | 06.06.2006 | |
11.10.2005 | 13.10.2005 |
. WHERE , . OUTER JOIN JOIN. , , , , NULL-.
6.
, - . , . CREATE VIEW.
client_list .
CREATE VIEW client_list AS
SELECT clients.client_fio, rented_films.rent_start_date, returned_rented_films.rent_end_date
FROM rented_films LEFT OUTER JOIN returned_rented_films ON returned_rented_films.rent_id=rented_films.rent_id, clients
WHERE clients.client_id=rented_films.client_id
7.
( ).
SELECT film_name,cnt_clients
FROM films,
(SELECT film_id, COUNT(DISTINCT client_id) AS cnt_clients
FROM rented_films GROUP BY film_id) cnt
WHERE films.film_id=cnt.film_id
ORDER BY film_name
, . , 1 .
8.
SQL INSERT.
INSERT INTO medium_type_directory (medium_type) VALUES ('dvd')
|
|
medium_type_directory .
9.
DELETE - .
DELETE FROM clients WHERE client_id=15
client_id 15.
9.
, UPDATE.
UPDATE clients
SET client_phone_number=795-55-78-48
WHERE client_fio=John N. Doe
John N. Doe .
1. ., , . SQL, 4- : . . .: , 2001. 352 .
2. ., . SQL. 3- : . . .: , 2003. 896 .
3. . , 9- : . . .: , 2005. 864 .
4. http://www.firststeps.ru/sql/oracle/oracle3.html (SQL, Oracle)
5. http://www.sql-ex.ru (SQL, Oracle)
6. http://megalib.com/items.php?idsubject=6 ( SQL)