Лабораторная работа: Проектирование РБД "Санатория" с помощью инструментария AllFusion ERwin Data Modeler
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)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("Insurance policy", DB_TEXT, 18)
ERwinTableDef.Fields.Append ERwinField
Set ERwinField = ERwinTableDef.CreateField("Delivery date of work", DB_TEXT, 18)
ERwinTableDef.Fields.Append ERwinField
ERwinDatabase.TableDefs.Append ERwinTableDef
' CREATE INDEX "PrimaryKey"
Set ERwinTableDef = ERwinDatabase.TableDefs("Worker")
Set ERwinIndex = ERwinTableDef.CreateIndex("PrimaryKey")
Set ERwinField = ERwinIndex.CreateField("ID of worker")
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 RELATIONSHIP "Send"
Set ERwinRelation = ERwinDatabase.CreateRelation("Send", "Enterprise-sender", "Client")
Set ERwinField = ERwinRelation.CreateField("ID enterprise")
ERwinField.ForeignName = "ID enterprise"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Belong to"
Set ERwinRelation = ERwinDatabase.CreateRelation("Belong to", "Housing estate", "Client")
Set ERwinField = ERwinRelation.CreateField("Number of flat")
ERwinField.ForeignName = "Number of flat"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "R/12"
Set ERwinRelation = ERwinDatabase.CreateRelation("R/12", "Client", "Department_Client")
Set ERwinField = ERwinRelation.CreateField("ID client")
ERwinField.ForeignName = "ID client"
ERwinRelation.Fields.Append ERwinField
Set ERwinField = ERwinRelation.CreateField("ID enterprise")
ERwinField.ForeignName = "ID enterprise"
ERwinRelation.Fields.Append ERwinField
Set ERwinField = ERwinRelation.CreateField("Number of flat")
ERwinField.ForeignName = "Number of flat"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "R/11"
Set ERwinRelation = ERwinDatabase.CreateRelation("R/11", "Department", "Department_Client")
Set ERwinField = ERwinRelation.CreateField("ID department")
ERwinField.ForeignName = "ID department"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "perform"
Set ERwinRelation = ERwinDatabase.CreateRelation("perform", "Work place", "Function")
Set ERwinField = ERwinRelation.CreateField("ID work place")
ERwinField.ForeignName = "ID work place"
ERwinRelation.Fields.Append ERwinField
Set ERwinField = ERwinRelation.CreateField("ID sector")
ERwinField.ForeignName = "ID sector"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Provide"
Set ERwinRelation = ERwinDatabase.CreateRelation("Provide", "Department", "Motor transport")
Set ERwinField = ERwinRelation.CreateField("ID department")
ERwinField.ForeignName = "ID department"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Have"
Set ERwinRelation = ERwinDatabase.CreateRelation("Have", "Client", "Permit")
Set ERwinField = ERwinRelation.CreateField("ID client")
ERwinField.ForeignName = "ID client"
ERwinRelation.Fields.Append ERwinField
Set ERwinField = ERwinRelation.CreateField("ID enterprise")
ERwinField.ForeignName = "ID enterprise"
ERwinRelation.Fields.Append ERwinField
Set ERwinField = ERwinRelation.CreateField("Number of flat")
ERwinField.ForeignName = "Number of flat"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Give to"
Set ERwinRelation = ERwinDatabase.CreateRelation("Give to", "Enterprise-sender", "Permit")
Set ERwinField = ERwinRelation.CreateField("ID enterprise")
ERwinField.ForeignName = "ID enterprise"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Consist of"
Set ERwinRelation = ERwinDatabase.CreateRelation("Consist of", "Department", "Sector")
Set ERwinField = ERwinRelation.CreateField("ID department")
ERwinField.ForeignName = "ID department"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Consist of"
Set ERwinRelation = ERwinDatabase.CreateRelation("Consist of", "Sector", "Work place")
Set ERwinField = ERwinRelation.CreateField("ID sector")
ERwinField.ForeignName = "ID sector"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
' CREATE RELATIONSHIP "Give to"
Set ERwinRelation = ERwinDatabase.CreateRelation("Give to", "Work place", "Worker")
Set ERwinField = ERwinRelation.CreateField("ID work place")
ERwinField.ForeignName = "ID work place"
ERwinRelation.Fields.Append ERwinField
Set ERwinField = ERwinRelation.CreateField("ID sector")
ERwinField.ForeignName = "ID sector"
ERwinRelation.Fields.Append ERwinField
ERwinDatabase.Relations.Append ERwinRelation
ERwinDatabase.Close
ERwinWorkspace.Close
' Terminating Access Basic DAO Session...
Вывод:
В процессе выполнения данной лабораторной работы были изучены основы работы в AllFusion ERwin Data Modeler, а также определена предметная область, основные сущности БД, информация о которых должна содержаться в базе данных, первичные ключи сущностей, их атрибуты и связи между ними.
AllFusion ERwin Data Modeler позволяет проектировать, документировать и сопровождать базы данных, хранилища данных и витрины данных (data marts). Создав наглядную модель базы данных, можно оптимизировать структуру БД и добиться её полного соответствия требованиям и задачам организации. Визуальное моделирование повышает качество создаваемой базы данных, продуктивность и скорость её разработки
Литература:
1. Маклаков С.В. CASE-средства разработки информационных систем. BPwin и Erwin –М.: ДиалогМифи, 2001.
2. Постановление Правительства РФ «О мерах по организаии индивидуального (персонифицированного) учета для целей государственного пенсионного страхования».
3. Федеральный закон «Об индивидуальном (персонифицированном) учете в системе государственного пенсионного страхования».
4. Инструкция о порядке ведения индивидуального (персонифицированного) учета сведений о застрахованных лицах для целей государственного пенсионного страхования.
5. «О формах документов индивидуального (персонифицированного) учета в системе государственного пенсионного страхования и инструкции по их заполнению»
