?
?
.
5. ?
6. IRUN?
7. Migrate Arrow Data?
8. ?
9. ?
10. ?
5
MS ACSESS 2000
: MS ACSESS 2000 Erwin 4.1.
ERwin .
Physical (), (.1).
1. .
Database Choose Database (.2).
2. Database.
Target Desktop DBMS Access, Access Version 2000 (. 3). .
3. Target Server.
Access (// Microsoft Office/ Microsoft Access 2000). .
Tools Forward Engineer/Schema Generation (.4).
4. Tools.
Access Schema Generation Generate.
5. Access Schema Generation.
Access Connection User Name admin. Browse Database Access. Connect (. 6).
6. Access Connection.
Generate Database Schema, . . Continue. , Stop If Failure (. 7).
. Access .
7. Generate Database Schema.
Access.
1.
|
| ||||||
| 100001 | 10 | 19.09.1946 | ||||
| 100002 | 10 | 21.02.1953 | . | |||
| 110003 | 11 | 05.06.1950 | . | |||
| 110004 | 11 | 19.11.1978 | ||||
| 110005 | 11 | 25.10.1965 | 1 | |||
| 110006 | 11 | 22.10.1983 | 2 | |||
| 110007 | 11 | 19.01.1984 | ||||
| 120008 | 12 | 13.03.1958 | . . | |||
| 120009 | 12 | 15.05.1974 | ||||
| 120010 | 12 | 17.10.1954 | ||||
| 130011 | 13 | 10.04.1960 | . . | |||
| 130012 | 13 | 04.04.1984 | ||||
| 130013 | 13 | 03.08.1962 | ||||
| 140014 | 14 | 12.09.1964 | . . | |||
| 140015 | 14 | 23.07.1968 | ||||
| 140016 | 14 | 06.03.1978 | 1 | |||
| 140017 | 14 | 16.12.1982 | ||||
|
|
|
.
.
: , - . , , , ,
9.
. , 2, , . , .
.
. .
1. , .
2. , .
3. . . . (. 10).
9
10
4. , . (.11)
11
(. 12). .
12
:
. , .
. .
.
.
8 . :
, , , , , ;
, , ;
- 1, 2, 3, .
, .
|
|
|
: . , .
2 ( , ), (, , ), (, ).
ࠠ
12.12.2007 ..
:
1. ?
2. , ?
3. MS ACCESS?
4. ?
5. ?
6. ?
7. MS ACCESS?
8. MS ACCESS?
9. MS ACCESS?
10. , ?
6
MS SQL SERVER.
: MS SQL SERVER Erwin 4.1.
Erwin .
Physical (), (.1).
1. .
Database Choose Database (.2).
2. Database.
Target SQL DBMS SQL Server, SQL Server Version 2000 (. 3). . Would you like to convert databases type from Access 2000 to SQL Server 2000? This is not undoable Yes Convert Domain Datatypes.
3. Target Server.
.
Tools Forward Engineer/Schema Generation (.4).
4. Tools.
SQL Server Schema Generation Generate (. 5). ( , Connect, Report. Erwin , , .)
5. SQL Server Schema Generation.
SQL Server Connection :
User Name ;
Password ;
Database ;
Server Name .
Connect (. 6).
6. SQL Server Connection.
Generate Database Schema, . . Continue. , Stop If Failure (. 7). .
7. Generate Database Schema.
:
1. ?
2. ?
3. ?
4. ?
5. ?
6. ?
7. ?
8. ?
9. ?
10.
7
OBDC.
|
|
|
SQL Server . ODBC . , Access, . . ODBC.
ODBC :
User DSN. , .
SYSTEM DSN - .
FILE DSN , .
User DSN
1. (ODBC).
ODBC ODBC. , ODBC SQL Server.
8. ODBC
2. DSN. SQL Server. . SQL Server, , .
10.
3. .
ü :
- , Istok, ;
, , .
A 518-1, .
11. .
ü SQL Server . SQL Server , , Windows NT , . SQL Server. , , SQL Server. , .
12. .
ü , . .
ü .
4. , . .
5. ODBC DSN Istok.
SQL Server
SQL Server . , , Access. , , Access. , .
|
|
|
Access . ODBC, SQL Server, , ODBC Databases ().
13.
ODBC Istok, (. 14).
.
14.
, . . , . , , , , . , .
, . . .
:
1. ODBC?
2. DNS?
3. DNS?
4. DNS?
5. ?
6. ?
7. ?
8. , ?
9. ?
10. Access?
8
: .
, . :
CREATE PROCEDURE] procedure_name [;number]
[ {^parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n]
[WITH { RECOMPILE
| ENCRYPTION
| RECOMPILE. ENCRYPTION } ]
[FOR REPLICATION]
AS sql_statement [...n]
:
procedure_name . sp_, # ##, . , , , CREATE PROCEDURE . USE, ;
number , ;
(^parameter , . , , , ;
data_type , . ;
OUTPUT ;
VARYING , , cursor. , ;
default , , .
RECOMPILE , ;
FOR REPLICATION , , . WITH RECOMPILE;
|
|
|
ENCRYPTON , syscomments . , ;
AS , . Transact-SQL, (sql statement). , , , CREATE.
1. .
CREATE PROCEDURE CpicokVidach
AS
SELECT /* , */
_2._, COUNT(_2._)
FROM /* */
_2
WHERE /* */
_2._=(SELECT GETDATE())
GROUP BY /* */
_
/*SELECT GETDATE() (, , )
COUNT (<>) - */
2. - .
CREATE PROCEDURE KolExzemplarov
/* */
@ISBN varchar(20)
AS
/* , ISBN */
IF not EXISTS (SELECT * FROM WHERE ISBN = @ISBN)
RETURN 0 /* KolExzemplarov */
SELECT .ISBN
INTO TEMP1 /* Temp 1*/
FROM
WHERE ISBN = @ISBN
SELECT COUNT(ISBN) /* Count ISBN */
FROM TEMP1
3. , -
CREATE PROCEDURE CpicokKnigCtudenta
@Chit_nom int /* */
AS SELECT ., ., _2., ., .
FROM /* , */
, ,, _2
WHERE (._ = _2._) AND (. = _2.) AND (.ISBN =.ISBN)
/* AND WHERE , */
4. . , 2 .
CREATE PROCEDURE DeleteStudent
@Chit_nom int /* */
AS /*, , , .*/
IF not EXISTS (SELECT * FROM _2 WHERE _=@Chit_nom)
DELETE /* */
FROM /* , */
WHERE /* , _ */
_=@Chit_nom
5. . , , .
PROCEDURE NewZakaz
@Kolvo int, /* */
@data_zakaza datetime,
@Chit_nomer int,
@ISBN varchar(20)
AS /*, , */
IF EXISTS (SELECT * FROM WHERE ISBN = @ISBN AND _=@Chit_nomer)
RETURN 0 /* , */
IF EXISTS (SELECT * FROM WHERE _ = @Chit_nomer)
/*, */
IF EXISTS (SELECT * FROM WHERE ISBN = @ISBN)
/*, */
INSERT INTO /* , */
VALUES (@Kolvo,@data_zakaza,@Chit_nomer, @ISBN) /* */
6. . .
CREATE PROCEDURE UpdateStudent
@Chit_nom int, /* */
@Fam varchar(20)
AS
IF EXISTS (SELECT * FROM /* , ,*/
WHERE _ = @Chit_nom) /* */
UPDATE /*
SET =@Fam /* */
WHERE _ = @Chit_nom /* */
7. 2. , , _ .
ALTER PROCEDURE NewPolzovanieStydentov
@Chit_nomer int, /* */
@data_vidachi datetime,
@data_priema datetime,
@tab_nomer int,
@Shifr varchar(20)
AS
IF EXISTS (SELECT * FROM _2 /*, */
WHERE = @Shifr AND /* */
_=@Chit_nomer)
RETURN 0 /* , */
IF EXISTS (SELECT * FROM /* */
WHERE _ = @Chit_nomer) /* */
IF EXISTS (SELECT * FROM 𠠠 /* */
WHERE = @Shifr) /* */
IF EXISTS (SELECT * FROM _ /* */
WHERE _ = @tab_nomer) /* _*/
INSERT /* , */
INTO _2 /* */
VALUES (@Chit_nomer,@data_vidachi,@data_priema,@tab_nomer,@Shifr)
8. _. , - .
CREATE PROCEDURE NewSotrudnik
@Tab_nom int, /* */
@Fam varchar(20),
@Name varchar(20),
@Sec_name varchar(20),
@data_rogd datetime,
@Dolgn varchar(20),
@Nom_otd int
AS
IF EXISTS (SELECT * FROM _蠠 /*, */
WHERE _ = @Tab_nom) /* */
RETURN 0 /* , */
IF EXISTS (SELECT * FROM /*, */
WHERE _ = @Nom_otd) /* _*/
INSERT /* , */
INTO _ /* */
VALUES (@Tab_nom,@Fam, @Name, @Sec_name, @data_rogd, @Dolgn, @Nom_otd)
9. .
CREATE PROCEDURE KolDolgn
AS SELECT COUNT (DISTINCT _.)
FROM _ /* COUNT (DISTINCT <>) - */
10. . .
ALTER PROCEDURE UpdateOtdel
@Nom_otdela int,
@Fam_New_Nach_otd varchar(20),
@Tab_Nom_New_Nach_otd int
AS
IF not EXISTS (SELECT * FROM _ /* , */
WHERE _ = @Tab_Nom_New_Nach_otd /* */
AND =@Fam_New_Nach_otd) /* */
RETURN 0 /* , */
IF EXISTS (SELECT * FROM /* , */
WHERE _ = @Nom_otdela) /* */
UPDATE
SET __=@Fam_New_Nach_otd /* */
UPDATE
SET __=@Tab_Nom_New_Nach_otd /* */
WHERE _ = @Nom_otdela /* , _ */
:
1. ?
2. ?
3. ?
4. ?
5. ?
6. .
7. COUNT?
8. , ?
9. ?
10. AS?
9
: .
, . .
, :
1) .
2) .
3) .
.
Transact SQL:
CREATE TRIGGER trigger_name
ON TABLE
[WITH ENCRYPTION]
{FOR {[DELETE] [,] [INSERT] [,] [UPDATE]}
[WITH APPEND]
[NOT FOR REPLICATION]
AS
sql_statement [...n]
}
Trigger_name , Transact SQL. .
TABLE , .
WITH ENCRYPTION .
[DELETE] [,] [INSERT] [,] [UPDATE] , . , , .
WITH APPEND SQL-.
NOT FOR REPLICATION .
sql_statement , .
:
CREATE TRIGGER trigger_name ON TABLE
[WITH ENCRYPTION]
{FOR { [[INSERT] [,] [UPDATE]}
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (column)
[ { AND/OR }
UPDATE(column) ]
[..n]}
sql_statement [...n]}}
FOR { [[INSERT] [,] [UPDATE] , .
IF UPDATE (column) .
AND/OR UPDATE(column) , . column , . AND , , . OR . AND/OR UPDATE(column).
ALTER TRIGGER.
1. , , - .
CREATE TRIGGER udalenie /* */
ON _2 /* , */
FOR DELETE /* , . ( )*/
AS
IF (SELECT count(*) /* */
from _2 / * */
where _2._ is not null)>0 /* . count (, ) IF */
AND (CURRENT_USER <> 'dbo') /* , */
BEGIN
PRINT ' ' /* */
ROLLBACK TRANSACTION /* () */
END
2. . 5 , 10 14 .
CREATE TRIGGER dobavlenie
ON
FOR INSERT
AS
DECLARE @@f int /* */
Set @@f=10 /* */
IF NOT EXISTS (SELECT * FROM , inserted
WHERE ._ = inserted._)
Set @@f=0
IF EXISTS (SELECT * FROM , inserted
WHERE inserted._>14 OR inserted._<10) /* >14 <10, */
Set @@f=0 /* */
If @@f=0 /* f =0, */
BEGIN
PRINT ' ' /* */
ROLLBACK TRANSACTION /* () */
END
/* inserted , */
3. , , ,
DeletedItems, :
CREATE TABLE DeletedItem (
[_] [int] NOT NULL, /* */
[] [varchar] (25) NULL,
[] [varchar] (75) NULL,
[] [varchar] (25) NULL,
[] [varchar] (35) NULL,
[_] [varchar] (50) NULL,
[_] [datetime] NULL
) ON [PRIMARY]
;
CREATE TRIGGER deletedby
ON /* */
FOR DELETE
AS
INSERT INTO DeletedItem
(_,,, /*, */
,,_,_)
SELECT _,,,, ,
SYSTEM_USER, /* */
getdate() /* */
FROM deleted /* deleted , */
4. , _ , .
InsertedItemSotrydniki, :
CREATE TABLE InsertedItemSotrydniki
(
[_] [int] NOT NULL, /* */
[] [varchar] (25) NULL,
[] [varchar] (25) NULL,
[] [varchar] (75) NULL,
[_] [datetime] NULL,
[] [varchar] (35) NULL,
[_] [varchar] (50) NULL,
[_] [datetime] NULL
) ON [PRIMARY]
;
CREATE TRIGGER infoInsertSotrud
ON _蠠 /* _ */
FOR INSERT
AS
INSERT INTO InsertedItemSotrydniki (_, /*, */
,,,_, ,_,_)
SELECT _,,,,_,,
SYSTEM_USER, /* */
getdate() /* */
FROM inserted /* inserted , */
5. , , .
UpdatedStudents, :
CREATE TABLE UpdatedStudents
(
[_] [int] NOT NULL,
[] [varchar] (25) NULL,
[_] [varchar] (50) NULL,
[_] [datetime] NULL
) ON [PRIMARY]
:
CREATE TRIGGER infoUpdateStudent
ON /* */
FOR UPDATE
AS
INSERT INTO UpdatedStudents (_, /*, */
,_,_)
SELECT _,, /* */
SYSTEM_USER, /* */
getdate() /* */
FROM updated /*updated , */
6. _2 _ .
ALTER TRIGGER proverka ON _2
FOR UPDATE
AS
IF UPDATE(_) /*, */
AND datename(dw,GETDATE()) in ('Sunday') /*, */
BEGIN
PRINT ' '
ROLLBACK TRANSACTION
END
IF UPDATE(_) /*, */
AND datename(dw,GETDATE()) in ('Sunday') /*, */
BEGIN
PRINT ' '
ROLLBACK TRANSACTION
END
/* datename (dw, GETDATE ()) GETDATE () , datename (dw,<>) */
7. , _ , ,
DeletedSotrudLibrary, :
CREATE TABLE DeletedSotrudLibrary (
[_] [int] NOT NULL, /* */
[] [varchar] (25) NULL,
[] [varchar] (25) NULL,
[] [varchar] (75) NULL,
[_] [datetime] NULL,
[] [varchar] (35) NULL,
[_] [int] NOT NULL,
[_] [varchar] (50) NULL,
[_] [datetime] NULL
) ON [PRIMARY]
:
CREATE TRIGGER deletesotrud
ON _蠠 /* */
FOR DELETE
AS
INSERT INTO DeletedSotrudLibrary /* , */
(_,,,,_, /* */
,_, _,_)
SELECT /* */
_, , , , _, , _,
SYSTEM_USER, getdate()
FROM deleted /*deleted , */
8. , , .
I nsertedItemPrepdavateli,
CREATE TABLE InsertedItemPrepdavateli
(
[_] [int] NOT NULL, /* */
[] [varchar] (25) NULL,
[] [varchar] (75) NULL,
[] [varchar] (25) NULL,
[] [varchar] (35) NULL,
[_] [varchar] (50) NULL,
[_] [datetime] NULL
) ON [PRIMARY]
:
CREATE TRIGGER infoInsertPred
ON
FOR INSERT
AS
INSERT INTO InsertedItemPrepdavateli
(_, , , ,, _, _)
SELECT _, , , , ,
SYSTEM_USER, getdate()
FROM inserted
9. , _, .
CREATE TRIGGER proverka_Studentov ON FOR INSERT
AS
DECLARE @@t int /* */
Set @@t=5 /* */
IF NOT EXISTS (SELECT * FROM , inserted
WHERE ._ = inserted._)
Set @@t=0
IF EXISTS (SELECT * FROM , inserted
WHERE inserted._>YEAR(GETDATE())) /*, , */
Set @@t=0 /* , */
If @@t=0 /* f=0, */
BEGIN
PRINT ' ' /* */
ROLLBACK TRANSACTION /* () */
END
10. , _, .
CREATE TRIGGER proverka_otchislenia_Studentov
ON
FOR UPDATE
AS
DECLARE @@t int /* */
Set @@t=5 /* */
IF NOT EXISTS (SELECT * FROM , inserted
WHERE ._ = inserted._)
Set @@t=0
IF EXISTS (SELECT * FROM , inserted
WHERE inserted._>YEAR(GETDATE())) /*, , */
Set @@t=0 /* */
If @@t=0 /* f=0, */
BEGIN
PRINT ' ' /* */
ROLLBACK TRANSACTION /* () */
END
:
1. ?
2. ?
3. ?
4. ?
5. ?
6. .
7. ROLLBACK TRANSACTION?
8. , ?
9. ?
10. FOR INSERT?
10
: .
(View) , , , . SQL-, .
, , , . 32. .
. , , , . , , , .
Transact - SQL
CREATE VIEW view_name [(column [,...n])]
[WITH ENCRYPTION]
AS
select_statement
[WITH CHECK OPTION]
view_name . , .
column , . 128 . . , . . SELECT, .
WITH ENCRYPTION - SQL-. , . , , .
select_statement SELECT, , . SELECT :
1) , , INTO.2) , , # ##.
, ORDER BY, COMPUTE COMPUTE BY.
Transact SQL DROP VIEW{view [n]}. .
1. , .
CREATE VIEW infoStudent /* */
AS
SELECT /*, */
., ., ., .
FROM /* */
WHERE . LIKE []
2. ++.
CREATE VIEW infoZamena
AS
SELECT /*, , */
., ., ., _.__
FROM /* , INNER JOIN , . ON , */
( INNER JOIN ON .ISBN =.ISBN)
INNER JOIN _ ON . = _.
WHERE . LIKE '%C++' /* , C ++ */
3. Delphi.
CREATE VIEW infoCpicanie
AS
SELECT /*, , */
., ., ., _._, _.__, _.__
FROM /* , , .*/
INNER JOIN ( INNER JOIN _ ON . = _.) ON .ISBN = .ISBN
WHERE . LIKE '%Delphi' /* , Delphi */
4. , 10 20 25 30.
CREATE VIEW infoZakazi /* */
AS
SELECT /* , */
.,., ., ., .,.
FROM /* , , .*/
INNER JOIN ( INNER JOIN ON ._ = ._)
ON .ISBN = .ISBN
WHERE (. BETWEEN 10 AND 20) OR (. BETWEEN 25 AND 30) /* 10< <20 25< <30 */
5. , , . 11.
CREATE VIEW infoOPolzovaniiStudentov
AS
SELECT /* , */
., ., ., ., _._
FROM /* , , .*/
((( INNER JOIN ON .ISBN = .ISBN) INNER JOIN
_2 ON . = _2.) INNER JOIN
ON _2._ = ._) INNER JOIN
_ ON _2._ = _._
WHERE _._ LIKE '10[]' /* 11*/
6. , . 11.
CREATE VIEW infoOPolzovaniiPrepodovatelej
AS
SELECT /* , */
., ., ., .,_._
FROM /* , , .*/
((( INNER JOIN ON .ISBN = .ISBN) INNER JOIN
_3 ON . = _3.) INNER JOIN
ON _3._ = ._) INNER JOIN
_ ON _3._ = _._
WHERE _._ LIKE '10[]' /* 11*/
7. 11 12 .
CREATE VIEW Otdeli
AS
SELECT /* */
._, ._, _.,
_.,_.
FROM /* , , .*/
INNER JOIN _ ON ._ = _._
GROUP BY . /* */
WHERE ._ BETWEEN 10 AND 13 /* , 10 13*/
8. , .. , ݻ, .
CREATE VIEW INFOPeriodika
AS
SELECT /* */
_., _._, ._, ._
FROM /* , , .*/
INNER JOIN _ ON ._ = _._
WHERE _. LIKE '[^] %%'
/*[^] ݻ, % % , */
9. , 2000
CREATE VIEW FullInfoEkzemplar
AS
SELECT /* */
.,.,.,._,.,._, ._, .__, .__
FROM /* , , .*/
INNER JOIN ON .ISBN = .ISBN
WHERE ._ BETWEEN 2000 AND YEAR (GETDATE())
/* GETDATE () , YEAR (<>) <>*/
10.
CREATE VIEW InfoAboutBook
AS
SELECT /* */
.ISBN, ., ._,
_2._,_2._
FROM /* ,
