p align="left">Стандартным разрешением связи “многие-ко-многим” является механизм, показанный на рис. 3. Рис. 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
Страницы: 1, 2, 3
|