Скрипти для створення бази даних

Скрипти для створення бази даних наведені нижче:

-- Generated for Oracle 8 on Tue Dec 06 14:28:00 2011 by Server Generator 6.0.3.3.0

 

PROMPT Creating Table 'GOODS_INFOS'

CREATE TABLE GOODS_INFOS

(GOOD_ID NUMBER(38) NOT NULL

,WARANTY_ID NUMBER(38) NOT NULL

,GOOD_TYPE VARCHAR2(20) NOT NULL

,PRODUCER VARCHAR2(25) NOT NULL

,MODEL VARCHAR2(15) NOT NULL

,PRICE NUMBER(38) NOT NULL

,WARANTY NUMBER(38) NOT NULL

,EXISTING CHAR(4) NOT NULL

,SH_CART_USER_ID NUMBER(38) NOT NULL

,SH_CART_GOOD_ID NUMBER(38) NOT NULL

,SH_CART_WARANTY_ID NUMBER(38) NOT NULL

)

/

 

PROMPT Creating Table 'SHOP_CARTS'

CREATE TABLE SHOP_CARTS

(USER_ID NUMBER(38) NOT NULL

,GOOD_ID NUMBER(38) NOT NULL

,WARANTY_ID NUMBER(38) NOT NULL

)

/

 

 

PROMPT Creating Table 'CUSTOMER_INFOS'

CREATE TABLE CUSTOMER_INFOS

(USER_ID NUMBER(38) NOT NULL

,NAME VARCHAR2(20) NOT NULL

,SURNAME VARCHAR2(20) NOT NULL

,PHONE NUMBER(38) NOT NULL

,E_MAIL VARCHAR2(35) NOT NULL

,ADRESS VARCHAR2(40) NOT NULL

,PASSWORD CHAR(15) NOT NULL

,SH_CART_USER_ID NUMBER(38) NOT NULL

,SH_CART_GOOD_ID NUMBER(38) NOT NULL

,SH_CART_WARANTY_ID NUMBER(38) NOT NULL

)

/

 

PROMPT Creating Table 'WARANTIES'

CREATE TABLE WARANTIES

(WARANTY_ID NUMBER(38) NOT NULL

,GOOD_ID NUMBER(38) NOT NULL

,SH_CART_USER_ID NUMBER(38) NOT NULL

,SH_CART_GOOD_ID NUMBER(38) NOT NULL

,SH_CART_WARANTY_ID NUMBER(38) NOT NULL

,TIME NUMBER(38) NOT NULL

,BUY_DATE DATE NOT NULL

)

 

 

Скрипти для створення первинних ключів в базі даних

Скрипти для створення первинних ключів в базі даних наведені нижче:

--

-- Generated for Oracle 8 on Tue Dec 06 14:28:00 2011 by Server Generator 6.0.3.3.0

 

PROMPT Creating Primary Key on 'GOODS_INFOS'

ALTER TABLE GOODS_INFOS

ADD CONSTRAINT G_INF_PK PRIMARY KEY

(GOOD_ID)

/

 

PROMPT Creating Primary Key on 'SHOP_CARTS'

ALTER TABLE SHOP_CARTS

ADD CONSTRAINT SH_CART_PK PRIMARY KEY

(USER_ID

,GOOD_ID

,WARANTY_ID)

/

 

 

PROMPT Creating Primary Key on 'CUSTOMER_INFOS'

ALTER TABLE CUSTOMER_INFOS

ADD CONSTRAINT CUS_INF_PK PRIMARY KEY

(USER_ID)

/

 

PROMPT Creating Primary Key on 'WARANTIES'

ALTER TABLE WARANTIES

ADD CONSTRAINT WARANTY_PK PRIMARY KEY

(WARANTY_ID)

/

 

PROMPT Creating Foreign Keys on 'GOODS_INFOS'

ALTER TABLE GOODS_INFOS ADD CONSTRAINT

G_INF_SH_CART_FK FOREIGN KEY

(SH_CART_GOOD_ID

,SH_CART_WARANTY_ID

,SH_CART_USER_ID) REFERENCES SHOP_CARTS

(GOOD_ID

,WARANTY_ID

,USER_ID)

/

 

PROMPT Creating Foreign Keys on 'CUSTOMER_INFOS'

ALTER TABLE CUSTOMER_INFOS ADD CONSTRAINT

CUS_INF_SH_CART_FK FOREIGN KEY

(SH_CART_USER_ID

,SH_CART_GOOD_ID

,SH_CART_WARANTY_ID) REFERENCES SHOP_CARTS

(USER_ID

,GOOD_ID

,WARANTY_ID)

/

 

PROMPT Creating Foreign Keys on 'WARANTIES'

ALTER TABLE WARANTIES ADD CONSTRAINT

WARANTY_SH_CART_FK FOREIGN KEY

(SH_CART_USER_ID

,SH_CART_GOOD_ID

,SH_CART_WARANTY_ID) REFERENCES SHOP_CARTS

(USER_ID

,GOOD_ID

,WARANTY_ID)

/

 

Скрипти для створення індексів в базі даних

Скрипти для створення індексів в базі даних наведені нижче:

--

-- Generated for Oracle 8 on Tue Dec 06 14:28:00 2011 by Server Generator 6.0.3.3.0

 

 

PROMPT Creating Index 'G_INF_SH_CART_FK_I'

CREATE INDEX G_INF_SH_CART_FK_I ON GOODS_INFOS

(SH_CART_USER_ID

,SH_CART_GOOD_ID

,SH_CART_WARANTY_ID)

/

 

PROMPT Creating Index 'SHOP_CUSTOM_FK_I'

CREATE INDEX SHOP_CUSTOM_FK_I ON ENTER_SHOPS

(CUSTOM_CUSTOM_ID)

/

 

 

PROMPT Creating Index 'CUS_INF_SH_CART_FK_I'

CREATE INDEX CUS_INF_SH_CART_FK_I ON CUSTOMER_INFOS

(SH_CART_USER_ID

,SH_CART_GOOD_ID

,SH_CART_WARANTY_ID)

/

 

PROMPT Creating Index 'WARANTY_SH_CART_FK_I'

CREATE INDEX WARANTY_SH_CART_FK_I ON WARANTIES

(SH_CART_USER_ID

,SH_CART_GOOD_ID

,SH_CART_WARANTY_ID)

/

Проектування программного забезпечення

Модель ієрархії функцій

Модель ієрархії функцій, має такий вигляд. Зображено на рис. 7

Рис.7 Модель ієрархії функцій

 

 

Вихідні тексти системи

VERSION 5.00

Begin VB.Form VVOD_DANYH_USERS

Appearance = 0 'Flat

BorderStyle = 3 'Fixed Dialog

Caption = "Query Goods"

ClientHeight = 3285

ClientLeft = 1350

ClientTop = 2205

ClientWidth = 4860

ForeColor = &H80000008&

LinkTopic = "Form3"

MaxButton = 0 'False

MinButton = 0 'False

PaletteMode = 1 'UseZOrder

ScaleHeight = 3285

ScaleWidth = 4860

Tag = "405470"

 

Begin VB.TextBox NOMER_ZHOP_CART

BackColor = &H00FFFFFF&

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 285

Left = 1405

TabIndex = 1

Tag = "405479"

Top = 120

Width = 1885

End

Begin VB.CommandButton cmdNew

Appearance = 0 'Flat

BackColor = &H80000005&

Caption = "&New"

Height = 345

Left = 3515

TabIndex = 17

Top = 1355

Width = 1225

End

Begin VB.CommandButton cmdOK

Appearance = 0 'Flat

BackColor = &H80000005&

Caption = "OK"

Default = -1 'True

Height = 345

Left = 3515

TabIndex = 14

Top = 120

Width = 1225

End

Begin VB.Label lblPZHOP_CART

Appearance = 0 'Flat

BackStyle = 0 'Transparent

Caption = "to:"

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 204

Left = 296

TabIndex = 5

Top = 645

Width = 320

End

End

Begin VB.CommandButton cmdCancel

Appearance = 0 'Flat

BackColor = &H80000005&

Cancel = -1 'True

Caption = "Cancel"

Height = 345

Left = 3515

TabIndex = 15

Top = 515

Width = 1225

End

Begin VB.CommandButton cmdClear

Appearance = 0 'Flat

BackColor = &H80000005&

Caption = "&Clear"

Height = 345

Left = 3515

TabIndex = 16

Top = 960

Width = 1225

End

 

Begin VB.Frame fraGroup

Caption = "Warranty"

Height = 1080

Left = 120

TabIndex = 2

Top = 525

Width = 1364

Begin VB.TextBox Warranty

BackColor = &H00FFFFFF&

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 285

Left = 616

TabIndex = 4

Tag = "405480"

Top = 360

Width = 583

End

Begin VB.TextBox Warranty

BackColor = &H00FFFFFF&

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 285

Left = 616

TabIndex = 6

Tag = "405480"

Top = 645

Width = 583

End

Begin VB.Label lblWarranty

Appearance = 0 'Flat

BackStyle = 0 'Transparent

Caption = "from:"

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 204

Left = 120

TabIndex = 3

Top = 360

Width = 496

End

 

Begin VB.Frame fraGroup2

Caption = "&Data"

Height = 1080

Left = 120

TabIndex = 7

Top = 1725

Width = 1885

Begin VB.TextBox txtDATA

BackColor = &H00FFFFFF&

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 285

Left = 616

TabIndex = 9

Tag = "405481"

Top = 360

Width = 1104

End

 

Begin VB.Label lblPDATA

Appearance = 0 'Flat

BackStyle = 0 'Transparent

Caption = "from:"

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 204

Left = 120

TabIndex = 8

Top = 360

Width = 496

End

Begin VB.Label lblPDATA2

Appearance = 0 'Flat

BackStyle = 0 'Transparent

Caption = "to:"

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 204

Left = 296

TabIndex = 10

Top = 645

Width = 320

End

End

Begin VB.TextBox txtZAKAZTOVAR_NOMER_ZAKAZA

BackColor = &H00FFFFFF&

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 285

Left = 1405

TabIndex = 13

Tag = "405486"

Top = 2925

Width = 844

End

Begin VB.TextBox txtHintText

Appearance = 0 'Flat

BackColor = &H80000018&

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H80000017&

Height = 285

Left = 3240

TabIndex = 18

TabStop = 0 'False

Top = 3000

Visible = 0 'False

Width = 1455

End

 

Begin VB.Label lblPZAKAZTOVAR_NOMER_ZAKAZA

Appearance = 0 'Flat

BackStyle = 0 'Transparent

Caption = "N&omer Zakaza:"

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 204

Left = 120

TabIndex = 12

Top = 2925

Width = 1285

End

 

 

Begin VB.Label lblPNOMER_ZAKAZA

Appearance = 0 'Flat

BackStyle = 0 'Transparent

Caption = "Nomer &Zakaza:"

BeginProperty Font

Name = "MS Sans Serif"

Size = 8.25

Charset = 204

Weight = 400

Underline = 0 'False

Italic = 0 'False

Strikethrough = 0 'False

EndProperty

ForeColor = &H00000000&

Height = 204

Left = 120

TabIndex = 0

Top = 120

Width = 1285

End

End

 

Attribute VB_Name = "VVOD_DANYH_USERSZAKAZQD"

Attribute VB_GlobalNameSpace = False

Attribute VB_Creatable = False

Attribute VB_PredeclaredId = True

Attribute VB_Exposed = False

Option Explicit

 

 

' CG$QD0009

 

' Global flag to control QD reset

Dim gbResetOnActivate As Integer

Private mstrCmdAction As String

Private mstrWhere As String

' CG$QD0009

Sub Form_Load()

 

On Error GoTo Form_Load_Err

' TEMPLATE DERIVED CODE

 

 

' Centralize this form

Call lgCentralizeForm(Me)

 

' TEMPLATE DERIVED CODE

 

 

' CG$QD0008.405470

 

 

' Activate the clear button to ensure all

' Query controls have initial value of Null

Me!cmdClear = True

gbResetOnActivate = True

 

 

' CG$QD0008.405470

 

Exit Sub

 

Form_Load_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "Form_Load", Err)

Exit Sub

End Sub

Sub Form_Deactivate()

 

On Error GoTo Form_Deactivate_Err

 

' CG$HT0002

 

 

' Clear the hint text

Me!txtHintText.Visible = False

' CG$HT0002

 

Exit Sub

 

Form_Deactivate_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "Form_Deactivate", Err)

Exit Sub

End Sub

 

 

Sub BuildWhere()

' NR$QD0001.405470

 

' Call library functions to derive WHERE clause predicates

' for each control in the Query Dialog

 

Dim strWhere As String

 

 

' Add straight forward equality/LIKE predicates. Need to perform

' some conversion from control values to database values for

' controls whose 'value' property does not hold the database value

' (eg Radio Group)

Call lgQDAddPredEqual("ZAKAZ.NOMER_ZAKAZA", NOMER_ZHOP_CART, "N", strWhere)

Call lgQDAddPredEqual("ZAKAZ.ZAKAZTOVAR_NOMER_ZAKAZA", txtZAKAZTOVAR_NOMER_ZAKAZA, "N", strWhere)

 

' Do the predicates defined by ranges

Call lgQDAddPredRange("ZAKAZ.CENA_ZAKAZA", txtCENA_ZAKAZA, Warranty, "N", strWhere)

Call lgQDAddPredRange("ZAKAZ.DATA", txtDATA, txtDATA2, "D", strWhere)

' Assign the string to member variable

mstrWhere = strWhere

 

End Sub

 

Public Sub ZAKAZ_DlgQuery(pstrAction As String, pstrSQLWhere As String)

' NR$QD0010.405470

 

' This set up the query dialog for display and return action and

' SQL where using the parameters

mstrCmdAction = ""

mstrWhere = ""

Me.Show vbModal

pstrAction = mstrCmdAction

pstrSQLWhere = mstrWhere

 

End Sub

Sub Form_Activate()

 

On Error GoTo Form_Activate_Err

 

' CG$QD0002.405470

 

 

If gbResetOnActivate Then

' Clear action variable

mstrCmdAction = ""

 

' Higlight last criteria

Call lgQDHighlightTextBox(Me)

gbResetOnActivate = False

End If

 

' CG$QD0002.405470

 

Exit Sub

 

Form_Activate_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "Form_Activate", Err)

Exit Sub

End Sub

Sub ZAKAZ_Validate(Action As Integer, Cancel As Boolean)

' PER$Validate.405470

 

' CG$GN0004

 

 

' This routine validates the data in the controls.

 

' CG$GN0004

 

End Sub

Sub cmdOK_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

 

On Error GoTo cmdOK_MouseMove_Err

 

' CG$HT0001

 

 

' Call library routine to display the hint text

Call lgHintText(Me!txtHintText, Me!cmdOK, "Query Goods based on criteria defined")

' CG$HT0001

 

Exit Sub

 

cmdOK_MouseMove_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdOK_MouseMove", Err)

Exit Sub

End Sub

Sub cmdOK_Click()

 

On Error GoTo cmdOK_Click_Err

 

' CG$QD0004

 

 

' Validate the zone before building the where clause

Dim bQryCancel As Boolean

bQryCancel = False

Call ZAKAZ_Validate(gACTION_QUERY, bQryCancel)

If bQryCancel Then

Exit Sub

End If

 

' Build the WHERE clause and hide the form

' indicating that user has chosen OK

Call BuildWhere

mstrCmdAction = "OK"

gbResetOnActivate = True

Me.Hide

 

' CG$QD0004

 

Exit Sub

 

cmdOK_Click_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdOK_Click", Err)

Exit Sub

End Sub

Sub cmdCancel_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

 

On Error GoTo cmdCancel_MouseMove_Err

 

' CG$HT0001

 

 

' Call library routine to display the hint text

Call lgHintText(Me!txtHintText, Me!cmdCancel, "Do not Query Goods")

' CG$HT0001

 

Exit Sub

 

 

cmdCancel_Click_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdCancel_Click", Err)

Exit Sub

End Sub

Sub cmdClear_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

 

On Error GoTo cmdClear_MouseMove_Err

 

' CG$HT0001

 

 

' Call library routine to display the hint text

Call lgHintText(Me!txtHintText, Me!cmdClear, "Reset query criteria")

' CG$HT0001

 

Exit Sub

 

cmdClear_MouseMove_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdClear_MouseMove", Err)

Exit Sub

End Sub

Sub cmdClear_Click()

 

On Error GoTo cmdClear_Click_Err

 

' CG$QD0005

 

 

' Clear all controls in the Query Dialog

mstrCmdAction = "CLEAR"

NOMER_ZHOP_CART = ""

mstrCmdAction = "CLEAR"

txtCENA_ZAKAZA = ""

mstrCmdAction = "CLEAR"

Warranty = ""

mstrCmdAction = "CLEAR"

txtDATA = ""

mstrCmdAction = "CLEAR"

txtDATA2 = ""

mstrCmdAction = "CLEAR"

txtZAKAZTOVAR_NOMER_ZAKAZA = ""

 

' CG$QD0005

 

Exit Sub

 

cmdClear_Click_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdClear_Click", Err)

Exit Sub

End Sub

Sub cmdNew_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

 

On Error GoTo cmdNew_MouseMove_Err

 

' CG$HT0001

 

 

' Call library routine to display the hint text

Call lgHintText(Me!txtHintText, Me!cmdNew, "Terminate query, enter new Zakaz record")

' CG$HT0001

 

Exit Sub

 

cmdNew_MouseMove_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdNew_MouseMove", Err)

Exit Sub

End Sub

Sub cmdNew_Click()

 

cmdNew_Click_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdNew_Click", Err)

Exit Sub

End Sub

On Error GoTo cmdNew_Click_Err

 

' CG$QD0007

 

 

' Dismiss the dialog & indicate that the

' New button has been pressed.

mstrCmdAction = "NEW"

gbResetOnActivate = True

Me.Hide

 

' CG$QD0007

 

Exit Sub

 

cmdNew_MouseMove_Err:

Call lgMsgVBError("VVOD_DANYH_USERS02.FRM", "cmdNew_MouseMove", Err)

Exit Sub

End Sub

Висновки

Головним результатом курсової роботи є створення автоматизованої системи для бізнес процесу «Інтернет — магазину». В результаті виконання курсової роботи: Може бути використаний для створення систем згідно технології CADM ORACLE.

Результатами роботи є розроблені модель ділового процесу, ER-модель, Створена логічна модель бази даних, Створені скрипти для створення фізичної бази даних, Розроблена модель ієрархії функцій та згенеровані декілька модулів розроблюваної інформаційної системи.

 

Перелік посилань

1. Oracle Designer Documentation http://www.oracle.com/technetwork/developer-tools/designer/documentation/documentation-093936.html

2. В.П.Шилін «Проектування інформаційного забезпечення, конспект лекцій», - Київ, МНТУ

3. В.П.Шилін «CASE – технології, конспект лекцій», – Київ, МНТУ