Проектирование РБД “Санатория” с помощью инструментария AllFusion ERwin Data Modeler

Рис. 2. Разрешение связи "многие-ко-многим"

 

На логическом уровне проектирования БД существовала связь «многие-ко-многим»  между сущностями Отделение и  Клиент. В результате перехода на физический уровень образовалась дополнительная сущность с соответствующей областью первичных ключей:

 

Рис.3 Переход на физический уровень

 

8. Скрипт для создания БД в Access:

 

'  Starting Access Basic DAO Session...

 

Dim ERwinWorkspace As Workspace

Dim ERwinDatabase As Database

Dim ERwinTableDef As TableDef

Dim ERwinQueryDef As QueryDef

Dim ERwinIndex As Index

Dim ERwinField As Field

Dim ERwinRelation As Relation

 

Set ERwinWorkspace = DBEngine.WorkSpaces(0)

 

Set ERwinDatabase = ERwinWorkspace.OpenDatabase(sERwinDatabase)

 

'  CREATE TABLE "Client"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Client")

Set ERwinField = ERwinTableDef.CreateField("ID client", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID enterprise", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Number of flat", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Surname", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Name", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Lastname", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Address", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Insurance policy", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Birthday", DB_DATETIME)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Client")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID client")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID enterprise")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("Number of flat")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Department"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Department")

Set ERwinField = ERwinTableDef.CreateField("ID department", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Department name", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Legal address", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Department")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID department")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Department_Client"

Set ERwinTableDef = ERwinDat base.CreateTableDef("Department_Client")

Set ERwinField = ERwinTableDef.CreateField("ID department", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID client", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID enterprise", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Number of flat", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Department_Client")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID department")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID client")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID enterprise")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("Number of flat")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Enterprise-sender"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Enterprise-sender")

Set ERwinField = ERwinTableDef.CreateField("ID enterprise", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Name", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Legal address", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Number contract", DB_INTEGER)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Enterprise-sender")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID enterprise")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Function"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Function")

Set ERwinField = ERwinTableDef.CreateField("ID function", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID work place", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID sector", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Function's name", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Function")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID function")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID work place")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID sector")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Housing estate"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Housing estate")

Set ERwinField = ERwinTableDef.CreateField("Number of flat", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Furniture", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Area", DB_INTEGER)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Quantity of places", DB_INTEGER)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Condition", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Housing estate")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("Number of flat")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Motor transport"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Motor transport")

Set ERwinField = ERwinTableDef.CreateField("State number", DB_TEXT, 18)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID department", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Car's brand", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Explotation time", DB_DATETIME)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Date maintenance", DB_DATETIME)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Motor transport")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("State number")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Permit"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Permit")

Set ERwinField = ERwinTableDef.CreateField("ID permit", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID client", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID enterprise", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Number of flat", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Period", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Cost", DB_INTEGER)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("People quantity", DB_INTEGER)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Permit")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID permit")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID client")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID enterprise")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("Number of flat")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Sector"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Sector")

Set ERwinField = ERwinTableDef.CreateField("ID sector", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID department", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Sector name", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Sector")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID sector")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Work place"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Work place")

Set ERwinField = ERwinTableDef.CreateField("ID work place", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID sector", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Name work place", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

ERwinDatabase.TableDefs.Append ERwinTableDef

 

'  CREATE INDEX "PrimaryKey"

 

Set ERwinTableDef = ERwinDatabase.TableDefs("Work place")

Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")

Set ERwinField = ERwinIndex.CreateField("ID work place")

ERwinIndex.Fields.Append ERwinField

Set ERwinField = ERwinIndex.CreateField("ID sector")

ERwinIndex.Fields.Append ERwinField

ERwinIndex.Primary = True

ERwinTableDef.Indexes.Append ERwinIndex

 

'  CREATE TABLE "Worker"

Set ERwinTableDef = ERwinDatabase.CreateTableDef("Worker")

Set ERwinField = ERwinTableDef.CreateField("ID of worker", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID work place", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("ID sector", DB_INTEGER)

ERwinField.Required = True

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Surname", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Name", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Lastname", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Address", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Pay", DB_TEXT, 18)

ERwinTableDef.Fields.Append ERwinField

Set ERwinField = ERwinTableDef.CreateField("Birthday", DB_DATETIME)