-







































: ""

: ""

230105

__________________

(, ) (, )

- 71

. . ______________________

(, ) (, )

__________________

()

____________________

______________________

(, ) (, )

________________________

(, ) (, )

, 2010 .


. .

347011 - 71

1.

2. () 2010

3.

,

4.


1.

1.1

1.2

1.3

1.4

1.4.2

1.4.3

2

2.1

2.2

2.3

2.4

2.4.1

2.4.2

2.4.3

2.4.4

2.4.5

2.5 ER-

2.6 SQL

2.6.1 ࠠ

2.6.2 ࠠ

3

3.1

3.2

3.3

3.3.1 1


55 . , ER- , . .

:

, ER-, , , , , , .

:

.

:

.

:

ER-, , .

Summary

This termpaper contains 55 pages. It contains two enclosures, which contain ER-diagrams. Also it contains target setting.

Object of development is design of the database according to specified variant.

The aim of work is design of the database, describing Pharmacy .

It is necessary to make ER-diagram, to minimize functional dependences, to write and minimize SQL queries.

:

: , .

er-: , ERWin.

: (, ), .

: .

: , .

: .

:

:

:

:


. , .

, .

, .

, . , , .

, - , .


1         

1.1     

.

1.2     

.

1.3     

04.02.030.-2008.

1.4     

(, , , ). (, , , , , ).

1.4.1  

Microsoft Visual FoxPro .


 

1.4.2  

CD . Flash, CD-R/-RW, DVD-R/RW .

1.4.3  

:

−          Pentium V 2200 ;

−          1024 ;

−          Seagate Barracuda, 120 ;

−          SVGA;

−          ;

−          ;

: .


 

2         

 

2.1     

: , , , . , , , . , , . : , , -, . , , , , , . . , . -, , .

: ( , , ), , ..

2.2     

2.1 2.11.

2.1

serial_pas 3800
num_pas 884532
name
surname
patronymic

address

. , 7, . 2

telephone_num

89516782355

dolgnost

2.2

productid 105
artikul 2345456
naimenovanie
sostav
Cost_one 200

count

34

sroc_godn

24.06.2011

description

.

2.3

Data_zakaza 24.02.09
predoplata 800
Order_execution 02.03.09

2.4

oklad 4000
premia 2000
avans 1000
zarplata 5000

2.5

serial_pas_e 3234
number_pas_e 676767
name
surname
patronymic

address

. , 97, . 8

telephone_number

89513302324

2.6

supplierid 2
name
address . . , .8
telephone 546523

2.7

Data_supply 10.05.10
Time_supply 16.20

2.8

Data_prodagi 05.05.10
Time_prodagi 15.35
count 2

2.9

Month_d
Year_d 2009
Dohod_employee 91233

2.10

Month_r
Year_r 2009
arenda 15000
Communal_yslygi 4500
reklama 3000

Vnutr_rashodi

10000

2.11

month
year 2008

2.3     

, , .

data_supply, vremy_postavki -> supplierid.

, , , , , .

productid, data_cost, vremy_cost, clientid -> employeeid.

, , .

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi.

, , ,

month_d, year_d -> viruchka_emploee.

, .

employeeid -> month_d, year_d.

, , .

month, year -> rashodid, dohodid.

, .

emloyeeid -> oklad, premia, avans, zarplata.

, .

productid -> artikulid.

, 2 .

supplierid -> name.

2.1

data_supply, vremy_postavki -> supplierid

clientid, productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

2.1

2.4     

2.4.1  

2.2.

.


G:

data_supply, vremy_postavki -> supplierid

clientid, productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, mployee_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

g:

data_supply, vremy_postavki -> supplierid

clientid, productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, mployee_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

2.2

2.4.2  

2.3.

.

g:

data_supply, vremy_postavki -> supplierid

clientid, productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

g:

data_supply, vremy_postavki -> supplierid

data_voz, vremy_voz, productid -> clientid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

2.3 -

2.4.3  

2.4

.

g:

data_supply, vremy_postavki -> supplierid

productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

X->

g:

data_supply, vremy_postavki -> supplierid

productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

2.4- .

 

2.4.4  

2.5 2.6. .

1. Ef( data_supply, vremy_postavki ):

data_supply, vremy_postavki -> supplierid

2. Ef( clientid, productid, data_voz, vremy_voz ):

clientid, productid, data_voz, vremy_voz -> employeeid

3. Ef( month_r, year_r ):

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

4. Ef( month_d, year_d ):

month_d, year_d -> viruchka_emploee

5. Ef( employeeid ):

employeeid -> month_d, year_d

6. Ef( month, year ):

month, year -> rashodid, dohodid

7. Ef( emloyeeid ):

emloyeeid -> oklad, premia, avans, zarplata

2.5 -

8. Ef( productid ):

productid -> artikulid

9. Ef( supplierid ):

supplierid -> name

g:

data_supply, vremy_postavki -> supplierid

clientid, productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

2.6 -

2.4.5  

CF- 2.7 2.11.

( data_supply, vremy_postavki; ) -> supplierid

( productid, data_voz, vremy_voz; ) -> employeeid

( productid, data_zakaza, employeeid; clientid, data_zakaza, productid; )

( month_r, year_r; ) -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

( month_d, year_d; ) -> viruchka_emploee

( employeeid; ) -> month_d, year_d

( month, year; ) -> rashodid, dohodid

( emloyeeid; ) -> oklad, premia, avans, zarplata

( productid; ) -> artikulid

(supplierid;) -> name

f(C):

data_supply, vremy_postavki -> supplierid

productid, data_voz, vremy_voz -> employeeid

2.7 -

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

1. CF-:

( data_supply, vremy_postavki; ) -> supplierid

2. CF-:

( clientid, productid, data_voz, vremy_voz; ) -> employeeid

3. CF-:

( month_r, year_r; ) -> arenda, kommunal_yslygi, reklama,

vnutr_rashodi

4. CF-:

( month_d, year_d; ) -> viruchka_emploee

5. CF-:

( employeeid; ) -> month_d, year_d

2.8 -

6. CF-:

( month, year; ) -> rashodid, dohodid

7. CF-:

( emloyeeid; ) -> oklad, premia, avans, zarplata

8. CF-:

( productid; ) -> artikulid

9. CF-:

( supplierid; ) -> name

1. CF-:

( data_supply, vremy_postavki; ) -> supplierid

2. CF-:

( clientid, productid, data_voz, vremy_voz; ) -> employeeid

3. CF-:

( month_r, year_r; ) -> arenda, kommunal_yslygi, reklama,

vnutr_rashodi

4. CF-:

( month_d, year_d; ) -> viruchka_emploee

2.9 -


5. CF-:

( employeeid; ) -> month_d, year_d

6. CF-:

( month, year; ) -> rashodid, dohodid

7. CF-:

( emloyeeid; ) -> oklad, premia, avans, zarplata

8. CF-:

( productid; ) -> artikulid

9. CF-:

( supplierid; ) -> name

C

( data_supply, vremy_postavki; ) -> supplierid

( clientid, productid, data_voz, vremy_voz; ) -> employeeid

( month_r, year_r; ) -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

( month_d, year_d; ) -> viruchka_emploee

( employeeid; ) -> month_d, year_d

( month, year; ) -> rashodid, dohodid

( emloyeeid; ) -> oklad, premia, avans, zarplata

( productid; ) -> artikulid

( supplierid; ) -> name

2.10 -

f(C):

data_supply, vremy_postavki -> supplierid

clientid, productid, data_voz, vremy_voz -> employeeid

month_r, year_r -> arenda, kommunal_yslygi, reklama, vnutr_rashodi

month_d, year_d -> viruchka_emploee

employeeid -> month_d, year_d

month, year -> rashodid, dohodid

emloyeeid -> oklad, premia, avans, zarplata

productid -> artikulid

supplierid -> name

R0 = ( data_supply, vremy_postavki, supplierid ) K0 = { data_supply,

vremy_postavki }

R1 = ( clientid, productid, data_voz, vremy_voz, employeeid ) K1 = {

clientid, productid, data_voz, vremy_voz }

R2 = ( month_r, year_r, arenda, kommunal_yslygi, reklama, vnutr_rashodi)

K2 = { month_r, year_r }

R3 = ( month_d, year_d, viruchka_emploee ) K3 = { month_d, year_d }

R4 = ( employeeid, month_d, year_d ) K4 = { employeeid }

R5 = ( month, year, rashodid, dohodid ) K5 = { month, year }

R6 = ( emloyeeid, oklad, premia, avans, zarplata ) K6 = { emloyeeid }

R7 = ( productid, artikulid ) K7 = { productid }

R8 = ( supplierid, name ) K8 = { supplierid }

2.11 -

2.5      ER-

. SQL

2.5.1  

2.5.1.1          

, Fuzafungin.

2.5.1.2           SQL

SQL 2.20.

SELECT clients.name, clients.serial_pas, clients.number_pas, product.name_product, orders.data_zakaza, orders.orderid as Number_Order;

FROM ((orders JOIN sotrud s ON orders. clientid = clients. clientid) JOIN product ON orders.productid = product.productid);

WHERE (product.name_product = "Fuzafungin")

2.20 - SQL

2.5.1.3          

2.21

-: : F2= clients.name, clients.serial_pas, clients.number_pas, product.name_product, orders.data_zakaza, orders.orderid as Number_Order


-: : (product.name_product = "Fuzafungin")


q2


q1

product

clients orders

2.21-

2.6.1.4

.

, (2.1)

- ;

- , ;

- , ;

- .


, (2.2)

(2.3)

- ;

- ;

- O;

- clientid Clients;

- clientid Orders.

,

,

,

.

, (2.4)

, (2.5)

- , ;

- ;

- ;

- .

,

, (2.6)

- ;

- ;

,

,

.

.

.

.

, (2.7)

,

,

,

,

. (2.8)

.

,

, (2.9)

,

,

,

.


.

.

(2.10)

- .

:

, (2.11)

,

, (2.12)

.

,

,

.

, .

:

, (2.13)

.


2.1

2.2

2.3

2.3.1

2.3.1.1

2.3.1.2

2.3.1.3

2.3.1.4

2.3.1.5          

2.22

-: : F2= clients.name, clients.serial_pas, clients.number_pas, product.name_product, orders.data_zakaza, orders.orderid as Number_Order



-: : (product.name_product = " Fuzafungin")




q3

q2

product

clients orders

2.22 -


SQL

2.23

SELECT product.productid, product.name_product;

FROM product;

WHERE (product.name_product = " Fuzafungin ") INTO TABLE k

SELECT sotruds...name, sotruds..serial_pas, sotruds...number_pas, orders.data_zakaza, orders.orderid as Number_Order;

FROM ((orders JOIN clients ON orders. sotrudid = sotruds. sotrudid) JOIN k ON k.productid = orders.productid);

2.23

2.3.1.6          

.

, (2.14)

- .

:

, (2.15)

,

,

, (2.16)

.


,

,

.

, (2.17)

- ;

- , ;

- , ;

- .

, (2.18)

(2.19)

,

,

,

.

, (2.20)

, (2.21)

- , ;

- ;

- ;

- .

,

, (2.22)

- ;

- ;

,

,

.

.

.

.

, (2.23)

,

,

,

,

, (2.24)

.


,

, (2.25)

,

,

,

.

.

.

, .

:

, (2.26)

2.5.2  

2.5.2.1          

, , .

2.5.2.2           SQL

2.23

SELECT COUNT(*);

FROM product INTO ARRAY Ar

SELECT clients.clientid, clients.name;

FROM ((clients JOIN orders ON orders.clientid = clients.clientid) JOIN Ar ON orders.productid = Ar.productid);

GROUP BY clients.clientid, clients.name;

HAVING COUNT(*)=Ar;

2.23 2.5.2.3           2.24.

-: : F2= clients.clientid, clients.name



q2


q1


product clients


clients orders

2.4

2.5.2.4          

.

, (2.27)

- ;

- , ;

- , ;

- .

, (2.28)

(2.29)

- ;

- ;

- O;

- clientid Clients;

- clientid Orders.

,

,

,

.

, (2.30)

, (2.31)

- , ;

- ;

- ;

- .

,

, (2.32)

- ;

- ;

,

,

.

.

.

.

, (2.33)

,

,

,

,

. (2.34)

.

,

, (2.35)

,

,

,

.

.

.

(2.36)

- .

.

.

,

,

.

, .

:

, (2.37)

2.5.2.5          

2.25.

-: : F2= clients.clientid, clients.name



q2


q1


clients


product orders

2.25

2.5.2.6           SQL

2.26

SELECT COUNT(*);

FROM product INTO ARRAY Ar

SELECT clients.clientid, clients.name;

FROM ((orders JOIN Ar ON orders.productid = Ar.productid) JOIN clients ON orders.clientid = clients.clientid);

GROUP BY clients.clientid, clients.name;

HAVING COUNT(*)=Ar;

2.26

 

2.5.2.7          

.

. (2.38)

,

,

,

, (2.39)

.

,

, (2.40)

,

,

,

.

.

.

(2.41)

, (2.42)

,

,

.

,

, (2.43)

,

,

,

.

.

.

(2.44)

- .

.

.

,

,

.

, .

:

, (2.45)


 

3         

3.1     

3.1

: C:\Documents and Settings\Admin\ \ \234.bmp

3.1

3.2     

, 3.2.


: C:\Documents and Settings\Admin\ \ \w.bmp

3.2

3.3     

 

3.3.1   1

Excel.

: .

: .

3.4.


3.4


 

.

: ; ; (ER-) ; ; ; ; .


1.    , ., . / [] : , . - .: , 1983. 320 .

2.    , .., . / [] : , .., , .., .: , 1989. 351 .

3.    -, , , , ., , . . : . . . : , 2003. 188 . : .

4.    , ., DB2. / [] : , . .: , 1988. 320 .

5.    , ., . / [] : , ., -.: , 1991. 252 .

6.    , .., (SQL). / [] : , .., .: , 1994. 80 .

7.    , ., . / [] : , ., .: , 1984. 196 .

8.    , ., . / [] : , ., .: , 1987. 608 .

9.    , ., . / [] : , ., , ., 2 ., .: , 1985. . 1. 287 .: . 2. 320 .

10.  , ., . / [] : , ., .: , 1990. 386 .

11.  , ., . / [] : , ., .: , 1984. 294 .

12.  , ., . / [] : , ., , ., .: , 1985. 344 .

 

ER

.1 .

: C:\Documents and Settings\Admin\ \ \.bmp

- .1


.2 .

: C:\Documents and Settings\Admin\ \ \.bmp

- .2


 

Excel .1 .2.

LOCAL objExcel, lnrow

SELECT clients.name, clients.serial_pas, clients.number_pas, product.name_product, orders.data_zakaza;

FROM clients, orders, product;

INTO CURSOR cur_report;

WHERE (clients.clientid=orders.clientid) AND (orders.data_zakaza="22/01/05") AND (product.name_product="Acer")

objExcel=CreateObject("Excel.Application")

WITH objExcel

.Visible=.T.

.Workbooks.add

.Columns[1].ColumnWidth=20

.Columns[2].ColumnWidth=12

.Columns[3].ColumnWidth=10

.Columns[4].ColumnWidth=15

.Columns[5].ColumnWidth=7

- .1

.Range("A1:E1").Select

WITH .Selection

.MergeCells=.t.

ENDWITH

WITH .ActiveCell

.Value = ""

.Font.Size = 14

ENDWITH

lnrow = 2

SELECT cur_report

SCAN

lnrow = lnrow + 1

.Cells(lnrow,1).value = cur_report.name

.Cells(lnrow,2).value = cur_report.serial_pas

.Cells(lnrow,3).value = cur_report.number_pas

.Cells(lnrow,4).value = cur_report.name_product

.Cells(lnrow,5).value = cur_report.data_zakaza

ENDSCAN

ENDWITH

- .2







2009