OAD Consulting, Inc.

Oxford Architectural Design & Consulting

(EA)2 User's Guide

DB Schema / EDB Schema

DB Schemas provide the tables, relationships, views, functions, and stored procedures, for a particular Business System or an entire enterprise domain. The term DB Schema is used when providing for a Business System, whereas the term EDB Schema (enterprise DB Schema) is used when providing for an Enterprise Domain. Enterprise architecture is not concerned with DB Schemas that are used only by their owning Business Systems and application(s), since all communication with them is done via their applications. However, enterprise architecture should track whenever an application communicates directly with the DB Schema of another application because this establishes an indirect relationship with the application.

As components of a Business System, DB Schemas must have a <<EA2 System Compnent>> connection to their Business System.Since EDB Schemas are not owned by any given Business System, they are considered true enterprise level assets on par with the Business Systems, Business Apps, etc. All communication with them should be tracked. As such, there are quicklinks available with EDB Schemas that are not available to DB Schemas. Other than this one distinction, the documentation for DB Schemas and EDB Schemas are the same. Where ever a DB Schema is documented, you should consider it documentation for the EDB Schema as well.

Schema Interfaces

DB_Schema

To enable interactions with a DB Schemas (this discussion pertains to EDB Schemas as well) on application structure diagrams, you create instances of DB Interfaces on the DB Schema, as show above. Refer to Exposed Interfaces for more information. ProvidedInterfaces are used on the DB Schemas while RequiredInterfaces are used on the applications. You can see two exposed interfaces. One is a read/write SQL interface that is used by applications and the other is used by the Member to ProgDev ETL Job.

The following diagram shows the DB Schema in an Application Component Diagram.

AppComponentDiagram

Logical Schemas

There are two packages associated with the DB Schema, Logical Schema and Physical Schema. Both of these should use Enterprise Architect's Data Modeling extended diagram type. The logical schema is modeled without regard to primary and foreign keys, just the business attributes as shown below.

LogicalSchema

Physical Schemas

Enterprise Architect can transform the logical schema into a physical schema as shown below.

PhysicalSchema

Note how Enterprise Architect has added primary and foreign keys and constraints on the columns.

DDL

Enterprise Architect can also generate the DDL from the physical schema, as shown below.

USE SCMS
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Venue Week Day Availability_Venue') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE Venue Week Day Availability DROP CONSTRAINT FK_Venue Week Day Availability_Venue
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_VenueTimePeriod_TimePeriod') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE VenueTimePeriod DROP CONSTRAINT FK_VenueTimePeriod_TimePeriod
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_VenueTimePeriod_Venue') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1)
ALTER TABLE VenueTimePeriod DROP CONSTRAINT FK_VenueTimePeriod_Venue
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('TimePeriod') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE TimePeriod
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Venue') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Venue
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('Venue Week Day Availability') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE Venue Week Day Availability
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('VenueTimePeriod') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE VenueTimePeriod
GO

CREATE TABLE TimePeriod (
PeriodName nvarchar(50),
FirstDate smalldatetime,
PeriodStatus nvarchar(50),
LastDate smalldatetime,
StartTime smalldatetime,
EndTime smalldatetime,
timePeriodID int NOT NULL
)
GO

CREATE TABLE Venue (
Name nvarchar(50),
VenueSize nvarchar(50),
venueID int NOT NULL
)
GO

CREATE TABLE Venue Week Day Availability (
DayOfWeek int,
OpeningTime smalldatetime,
ClosingTime smalldatetime,
venue Week Day AvailabilityID int NOT NULL,
venueID int NOT NULL
)
GO

CREATE TABLE VenueTimePeriod (
venueTimePeriodID int NOT NULL,
timePeriodID int NOT NULL,
venueID int NOT NULL
)
GO

ALTER TABLE TimePeriod ADD CONSTRAINT PK_TimePeriod
PRIMARY KEY CLUSTERED (timePeriodID)
GO

ALTER TABLE Venue ADD CONSTRAINT PK_Venue
PRIMARY KEY CLUSTERED (venueID)
GO

ALTER TABLE Venue Week Day Availability ADD CONSTRAINT PK_Venue Week Day Availability
PRIMARY KEY CLUSTERED (venue Week Day AvailabilityID)
GO

ALTER TABLE VenueTimePeriod ADD CONSTRAINT PK_VenueTimePeriod
PRIMARY KEY CLUSTERED (venueTimePeriodID)
GO

ALTER TABLE Venue Week Day Availability ADD CONSTRAINT FK_Venue Week Day Availability_Venue
FOREIGN KEY (venueID) REFERENCES Venue (venueID)
GO

ALTER TABLE VenueTimePeriod ADD CONSTRAINT FK_VenueTimePeriod_TimePeriod
FOREIGN KEY (timePeriodID) REFERENCES TimePeriod (timePeriodID)
GO

ALTER TABLE VenueTimePeriod ADD CONSTRAINT FK_VenueTimePeriod_Venue
FOREIGN KEY (venueID) REFERENCES Venue (venueID)
GO

EXEC sp_addextendedproperty 'MS_Description', 'This is a generic time period that can be used by numerous specific time periods, like the time period a venue is available.
', 'Schema', dbo, 'table', TimePeriod
GO