For your ER Diagram, I have a few comments:
* Valves and pumps each will have a state (i.e. Open/Closed, On/Off,
etc) and an alarm value. The diagram should to show an Alarm element
for the valve.
* We have two types of pumps; heat pumps and water pumps. In
many ways, they behave the same way - for now. In the long term,
however, they may change.
Thus they should be represented as separate objects.
* Sensors all behave the same way - they return raw float values. Some
sensors return more than one value, but it is still just a float. With
this in mind, it may be
better to organize all sensor information into a
single entity with the ability to discern they type of reading via an index
value of some kind. This would end up
merging your Flow Meter, Meter and Sensor entities
into one entity with three elements - a device identifier, the index to
discern the type of reading and the reading itself.
* I don't understand the Ship State or the In Storage elements for
the Device entity.
* I don't think the System entity describes what we're doing.
I think the breakdown that I sent out earlier this morning will clarify
our intentions here to a large degree.
Not going into detailed design will be tough for me, so please bear with. I'm used to solving the problem, not phrasing the problem so that it can be solved.
A quick rundown of the overall needs we have for this semester - Ability to record object information, object behavior, object state information, system and subsystem state information, and a method of associating objects with systems and subsystems. We will need stored procedures to do lookups of the state of any object, system or subsystem and to perform updates of the state information for a given object, system or subsystem. Utility procedures would include the ability to assign objects to systems and subsystems, create objects, create ehaviors/sets of behaviors, create systems and subsystems, etc. Also useful would be generic informational lookups such as all devices in a subsystem within a system and their current states, behavioral descriptions, all objects in alarm, etc.
A bit more detail -
The database needs to be able to provide a means of tracking data of
various types for components. This entails modeling behaviors based
off of the data being entered. That requires abstraction of the database
structures so that the data can be self defining in nature. For example,
a valve can be open or closed. It can also have an alarm status (for
now, that means it's either in alarm or it's not, but don't make this a
boolean since future alarm states may include diagnostic information).
These are two discrete behaviors that can be attributed to a valve.
The alarm status behavior is no different than the alarm status behavior
for a heat pump. Therefore, that single behavior can be attributed
to both types of objects.
Additionally, a heat pump can be off, cooling or heating. These states are similar to the valve being open or closed, so they can be described in a similar fashion, but constraints need to be used to keep the correct behaviors associated with the correct object types. One method of accomplishing this is to define generalized behavior characteristics, associated with predefined values (-1=In alarm, 0=Not in alarm, 1=Cooling, 2=Heating, 3=Open 4=Closed, etc), then define behaviors that can use only certain aspects of those characteristics (valve state behavior would be allowed to use 3 and 4, heat pump state behavior would be allowed to use 1 and 2, alarm behavior would be allowed to use -1 and 0). Once the characteristics of all needed behaviors are defined, then behaviors can be grouped together into sets of behaviors (valves would use the valve state behavior and alarm behavior while heat pumps would use heat pump state behavior and alarm behavior). With a set of behaviors defined, object types can be mapped to a behavior set and thus allow for specific objects to be mapped to specific characteristics.
When behaviors have been defined, objects can be created. The objects themselves will need at least three pieces of information - a unique identifier, a useful description and an object type which will end up defining how it behaves. We also want to include other information that is specific to the individual object - most recent firmware update, physical location of the object, notes, name of the person responsible for the latest maintenance, etc.
After objects are defined, they can be grouped together. The grouping method we're using is via system (we also tend to call this "Zone", as in "Zone 5") and subsystem (we tend to call this a "System", as in "Environmental System"). So, a device can be assigned to any subsystem within any system (i.e. a valve can be assigned to belong to the Fuel system of Zone 7). Any one device could be assigned to multiple zones and/or systems.
Subsystems and systems also have statuses that must be maintained. Every subsystem belongs to a system, however it is not exclusive (i.e. the same Environmental system may span both Zone 4 and Zone 5), not all inclusive (some zones have an Environmental system, but no Fuel system) and their status must each be maintained discretely (Zone 4's Environmental system may be red while it's Fuel system may be green). Additionally, broad scale system and subsystem statuses must be maintained (i.e. the Environmental System of the entire ship may be yellow while Zone 4's Environmental System may be red).
Once we have a bunch of fully described objects (their individual information and how they fit into the ship structure via the system and subsystem format) and we know how they behave, we can create state tables that will contain the information that the defined behaviors allow. A valve state table would include fields for valve state (open or closed) and alarm status (in alarm or not in alarm). The structure of a heat pump state table would be identical, but the constraints imposed by the above behavior definitions would make it hold states of Cooling, Heating, Off, etc in the state field instead of the open or closed values that apply to valves. The alarm status would behave identically to the valve alarm status since the same behavior was assigned to each behavior set.
Hello Brett,
I do not have any suggestions at this time. I am the lead
electrical
engineer on the project, so I don't know the details concerning the
software
aspect of the project. Dave Morgan would be a better person to speak
to
about this. He is the lead computer programmer on the project and spend
most
of time working with the team from last semster.
Shane
Shane P. Mueller
Weapon Control System
Lockheed Martin NESS
Moorestown, New Jersey
Mailstop: 137-230
Office #: (856) 722-2957
Lab #: (856) 722-2818
Fax #: (856) 273-5317
Email: shane.p.mueller@lmco.com
> From: Brett Keenan[SMTP:brettkeenan@yahoo.com]
> Reply To: gte402f@prism.gatech.edu
> Sent: Friday, September 01, 2000 1:33 PM
> To: david.c.morgan@lmco.com
> Cc: shane.p.mueller@lmco.com
> Subject: Real World Lab Project Notebook
>
> David and Shane,
>
> Hello, my name is Brett Keenan. I have been ellected
> to be the manager for this semester's RWL Lockheed
> project team. I just wanted to take a moment and let
> you know our goals for this semester and get any
> feedback you may have. This semester, our group would
> like to do the following:
>
> 1. Complete database design and implementation
> 2. Establish Use Cases for the Product
> 3. Install all the necessary software on the Notebook
> (we will need to speak with one of you to
find out
> what type of database we should create, if you would
> lke the web interface to be in JSP, ASP, or whatnot,
> etc.-this will allow for future groups to begin
> programming in the first week of the semester)
> 4. Create & Prototype User Interface for Product
> 5. Produce necessary documentation
>
> Please let me know if you have any suggestions. We
> look forward to working with you this semester.
>
> Thank you,
> Brett Keenan
Some immediate thoughts on your presentation.
1. Being up first especially with Scott present was a tough environment
-
you coped with it very well.
2. You covered all of the topics I asked for except the picture of
the
long-term project - we talked about that; no action - just have
it there
next time. I just realized that talking to that long-term plan
is the
opportunity for your documentation specialist to discuss the configuration
status of the documents. Without that thought, Tamrah was left without
a
good place to present her specialty - sorry.
3. Levi's discussion of the database was where the wheels didn't come
off
exactly, but really started to wobble. This is a tough slide
to talk to.
When shown a lot of detail (this is good), the audience is going
to read
all the info on the screen it cares to read much faster than you can
tell
us. What we want to know is not the names in all of the slots,
but why the
structure is the way it is. What you have to do is mentally carry
over the
main message from the previous slide - perhaps, the need for abstraction
-
and show how that constraint shaped the design. Then, perhaps,
verbally
"connect the dots" by tracing how a user might start at a system, and
dig
down to the characteristics of a particular valve...
4. Eric's risk discussion was OK, missing only the probability and
impact
assessments of each risk.
5. Although you avoided talking about the GUI because it is just a
prototype, it is your test environment, and I think I would have liked
to
see it.
Overall, an excellent show - keep it up.
Comments are inline:
Dave,
Please give me your feedback on the following database
design decisions I have come up with.
DEVICE-SYSTEM-ZONE RELATIONSHIPS
* Devices are linked to Systems through a field in the DEVICE_T
table.
- Do you mean the DEVICE_T table will store a SystemID or something
like that? If so, how would you show the same device as belonging
to two different systems? On the ship, sometimes systems can be cross
connected: for emergencies, water may be siphoned off of the potable water
system to give to the fire main system so that a fire can be fought.
This is done via a single valve that connects the two systems, but is usually
closed. When needed, this valve can be opened to allow water through.
In this instance, we may wish to show that same valve as part of both the
potable water and fire main systems.
- If you mean that you'll use the device ID and link it to a system
somewhere, then I'd agree that's a good solution (you'll see later that
I did it this way).
Each zone has it's own talbe containing rows for each system in that
zone.
* We Have a SYSTEMS_T which has System_ID, Status, Alarm, Parent_System.
We also have ZONES_T table, containing the fields: Zone_ID, name, status,
alarm. Every row in this ZONES_T table maps to a table for each ZONE (you
can still add new zones dynamically even though they have their own table),
which has fields for System_ID, Status, & alarm. Now one can
check the status of an overall system, overall zone, or a system as it
is for one particular zone.
- Not my approach, but it is a plausible solution. Using triggers
would allow for the creation of new Zones on the fly (i.e. use the addrecord
trigger for the ZONE_T table to create a new table and ppropriate
data and constraints could be back filled as part of that trigger to direct
stored procedures to the correct tables). One slightly messy part
of this would be maintaining relationships and constraints. It is
certainly doable, though.
- Again, one thing to bear in mind is: how would you show that a single
device belongs to multiple systems?
* Here was my approach:
Needing to maintain discrete information for both zones and systems,
I figured that keeping those two groups of information separate was useful.
1) Create a Systems table that lists each system, give it a
unique SystemID (an integer) and a Status field (for the overall system
status).
2) Create a Zones table that lists each zone, give it a unique
ZoneID (an integer) and a Status field (for the overall zone status).
Needing to be able to mix and match zone and system combinations while
maintaining an individual status for each combination created made me decide
to cross the Systems and Zones tables into a third table (which would be
easy to maintain by simply adding new combinations).
3) Create a third table that melds the two into zonal systems
(systems within zones). It has a unique ZonalSystemID, a ZoneID,
a SystemID and a Status field. Have a dual primary key based on ZoneID
and SystemID (ensuring that the same system is never created in the same
zone twice). With Zone-System combinations now defined and uniquely identified,
I can map devices to them. Since a device can belong to multiple
systems and/or multiple zones, I needed to create a separate table that
would allow for multiple ZonalSystem assignments to a single device.
4) Create a forth table where devices are mapped to ZonalSystems.
The primary key is a combination of the DeviceID and the ZonalSystemID
so that any one device will not be shown to be in the same zonal-system
twice, but can still exist in multiple systems and/or zones.
The above yields the ability to create any combination of systems within any zone, tracks the status for each zone and each system independently while still maintaining an individual status for a system within a zone and yields a quick and easy way to map any device to any combination of zones and systems. It also accomplishes the above purely through data manipulation and keeps metadata static (which makes long term administration a bit easier, IMHO).
* Either method can be made to work. They're just different approaches.
DEVICE-BEHAVIOR RELATIONSHIP
* In the DEVICE_T table every Deveice has a Behavior ID. The
Behavior_ID is the primary key of a BEHAVIORS_T table. Every Behavior_ID
has an associated behavior set. Every row of data maps directy to
a table in the database, where there is a collum for each behavior in the
set. So to add a new type of device, we add a new row to the BEHAVIORS_T,
which will trigger the creation of a new Table where all of devices of
this new type will go. This is a viable solution. It will blend a
relational and object oriented format nicely. I took a slightly different
approach, but this one could easily turn out to be better.
* Here's what I did:
Needing to define behaviors that varied right down to field type, I
decided that I would group information based on field types. I separated
Sensors from the rest of the data (since sensors will return floats and
everything else can be based off of integers). This gives a general
picture of the values being used for state data - indexed value (on, off,
open, closed, heating, cooling, etc), a raw value (a float for sensor data)
and some method of dealing with alarm data (I simply used negative integers).
Ultimately, this results in a description of all possible behavior values
(with raw values covering all of the floats from the sensors).
1) Create a BehaviorCharacteristics table with a unique BehaviorValue
(which describes the way to interpret the state data) and a textual description
of the behavior (more for readability than anything else).
Basically, this is a constraints table that will be used to limit the
available options for which a given device type can assert its state as
being. For now, I use -1 to indicate In Alarm and 0 to indicate Nominal
(in
the future, the actual value may vary for the negative number to help
show diagnostic information). The remaining states are indexed 1
to 10 for On, Off, Stand By, Operating, Open, Closed, Raw Number (for sensors),
Heating, Cooling, Inoperable. Not all of these states are currently
used. With all potential state data defined, I can begin assigning
allowable state assignments (behavior characteristics) to a particular
behavior.
2) Create a Behaviors table that describes which behaviors can
assume which states. Alarm behavior can take on Nominal (0) or In
Alarm (-1), so that's two rows (one that maps Alarm behavior to -1 and
one that maps Alarm behavior to 0). A valve's state can be Open (5)
or Closed (6), so that's two rows for the valve state behavior. A
heat pump can be Cooling (9), Heating (8) or Off (2). Sensors provide
raw data and therefore are only capable of the Raw Number behavior characteristic
(7). With specific behaviors defined, I can begin mixing and matching
my behaviors to describe my devices.
3) Create a BehaviorSet table that groups multiple behaviors
into a single behavior set. This requires a BehaviorSetID (not unique,
I'll explain why in a moment), a BehaviorSetName (more for readability
than anything else) and a TableName. The BehaviorSetID cannot be
unique since it will have multiple behaviors mapped to the same BehaviorSet
(a combination of BehaviorSetID and Behavior will work). The TableName
provides a means of linking a specific behavior for a specific BehaviorSet
to a specific table. So, here I could define a generic Heat Pump behavior
set as having the heat pump state behavior (Cooling, Heating or Off) and
the Alarm behavior (In Alarm or Nominal). I could also define a generic
Valve behavior set as having the valve state behavior (Open or Closed)
and the Alarm behavior.
With future expansion of the types of devices (and hence, behavior
sets) I can reuse my existing definitions for behaviors and just define
a new behavior set. For example - we are currently integrating a
new type of valve that also has sensors imbedded in it. This is easy
to represent in the database because all I need to do is define a new behavior
set "Valve
with Sensors" and assign it the behaviors of valve state (Open/Closed),
Alarm (In Alarm/Nominal) and Sensor (Raw Number). The table names
that are associated with each behavior for each behavior set makes it easy
to discern where I need to go to get the data. The next step is to
bridge the gap between my defined behavior sets and my actual devices.
Instead of assigning a behavior set directly to a device, I decided to
use a little abstraction to allow for a bit more information to be discerned
by end users. I created another table that maps device types to behavior
sets. At first, this looks like an unnecessary step, but having valves
from ten different manufacturers with all of them behaving the same makes
it a little more difficult to track down information about any one type
of valve. So, creating the mapping table allows for me to list specifics
about a type of device (size, manufacturer, etc) that differentiate it
from all other devices while still allowing it to have the exact same behavior.
4) Create a device type to behavior set map and add extra fields
for specific information on the device types. Link the DeviceTypeID
(unique) to the devices table and the behavior set to the BehaviorSets
table.
The above structure allows me to easily define new behaviors, mix and match behaviors among device types, maintain individual information on a specific type of device even though it behaves just like another type of device, add/remove behavior characteristics from devices or behaviors themselves and gives me the ability to find out what tables I need to use (and the type of information they hold) to find out the state of any given type of device.
* I'd have to think about it a bit to figure out which approach is better. Mine is more normalized and relational, yours more object oriented. They both have their merits.
STORING HISTORY
* To store history, we make copies of every table, adding a TimeStamp
field. This is the primary key for these tables. All History
is stored in these tables. It would be bad for the database to store history
in
the same places that we store current information becasue it would
increase querry times.
- Yes. Simple and effective.
That'd be fine with me. If you have any questions at all, just
let me know.
I can't clarify issues that I don't know are unclear.
-----Original Message-----
From: Brett Keenan [mailto:brettkeenan@yahoo.com]
Sent: Monday, October 02, 2000 11:13 AM
To: Morgan, David C
Subject: RE: Meeting today, Friday 9/29/2000
Dave,
Thank you for your suggestions. I know you have a
meeting today, so if it is alright with you, our group
will meet and change the database to reflect your
input and we can send you the updated links in a few
days.
Brett
--- "Morgan, David C" <david.c.morgan@lmco.com> wrote:
> Comments on GT design documentation:
>
> Where is Subsystem status taken into account?
> Where are Subsystems tracked in terms of what Subsystems exist within
what Systems (i.e. where can I find out if Zone 4 has an Environmental
system defined)?
> How do I keep track of what devices belong to what Subsystems?
>
> Wouldn't the connection between the base Devices_T table and the
subsidiary tables be tied to the ID of the device, which is the primary
key in the Devices_T table? This being the case,
> wouldn't any one device only have one alarm state (in alarm or not
in alarm) and one device state (on/off, open/closed)? If that's true,
wouldn't it be a one to one
> relationship between the device table and the effectors table?
>
> Your description of the sensors has me a bit confused. I see
that section as being interpreted in two possible ways, each of which I
have questions on:
> 1) Sensors_T table records the actual values for the sensor
readings.
> Why is "Value" in the Sensors_T table the primary key? For
sensors, if the primary key is the value, then when I try to update the
database, I cannot have two sensors with the same reading.
> Similarly, in the effectors table, it is not possible for me to have
more than two devices (one in alarm and one not in alarm) without violating
the primary key, which is "Alarm".
> Another problem would be how do I discern the reading types (i.e.
Flow or Pressure) from each other in the Sensors_T table?
> 2) Sensors_T and Effectors_T tables hold indicies which map
the Devices_T table to state tables (I think this is what you're trying
to represent).
> If so, then you still need to represent the link between
the tables adequately - I see no connection from which the index represented
by "Value" can be bounced off of the subsidiary tables to find
> the specific sensor readings for a given device id. In fact, I only
see one field in the Air_Temperature_Sensors_T table. In relational
databases, one field tables limit their usefulness to being
> merely constraint tables that limit allowed inputs into the fields
of other tables.
>
> Your description of effectors has an equivalent situation to the
one for sensors (i.e. linking of state tables back to the base devices
table via an ID, the heat pump and water pump tables are one
> field tables). I also don't quite see why you're attempting
to merge heat pumps and water pumps into one pumps table. They are
discrete objects that behave differently (a water pump cannot turn on/off
its > fan, but a heat pump can). They are separate behaviors.
>
> How would I implement a new behavior into this design? Say
I wanted to represent a new type of valve that has both the ability
to open and close AND the ability to return flow, pressure and
> temperature data. How do I model that in this design considering
that the behaviors themselves are not specialized in any way (its a valve
that also has the same attributes as a flow meter), it's the grouping >
of those behaviors that is unique?
>
> -----Original Message-----
> From: Brett Keenan [mailto:brettkeenan@yahoo.com]
> Sent: Friday, September 29, 2000 12:21 PM
> Subject: Meeting today, Friday 9/29/2000
>
> Hey guys,
>
> Tamrah, Mike and I spoke briefly with the people up at Lockheed,
but we didn't have a great deal to talk with them about, which I feel made
us look bad. We're having a difficult time getting everyone > together
for these meetings it seems. I need your input here-is meeting here
at our work too difficult? If it is, we can start meeting somewhere
on campus or elsewhere. This class gives us the opportunity to > work at
our own pace on a project and not have a teacher looking over our shoulders
each week. With this comes a good deal of responsibility. Not
only do we need to keep in touch with the people up > at Lockheed, but
as well, with each other about what's going on. I think that if we
talk on Wednesdays in class (sorry mike and I weren't there this past week),
and have a weekly meeting every friday at > 11:00 with the Lockheed people.
I will send e-mails when we have other scheduled meetings, and Tamrah posts
them on the webpage as well. We also need to keep each other informed
through > e-mails-let's go ahead and send e-mails out to the whole group
so everyone knows what's going on.
>
> This is important-everyone check out Levi's designs for the Data
Base-they're up on the web at:
>
> http://www.cc.gatech.edu/classes/RWL/Projects/lockheed/webdocs/object_diagram.jpg
> http://www.cc.gatech.edu/classes/RWL/Projects/lockheed/webdocs/database_diagram.jpg
>
> And here is Tamrah's ideas on our presentation, which is on the 9th
of
October:
>
> http://www.cc.gatech.edu/classes/RWL/Projects/lockheed/webdocs/presentation.html
>
> Now, here's what I see going on right now- Jeff is coming by today
at around 3:00 to the office and we are going to get him back up to speed
on everything that has been going on.
> On Monday, we are planning to meet with Dave, the DB design person
to discuss what else we need to do before we begin implementing it on the
server.
> Levi and Eric, you are the main DB guys, and Jeff, Mike, Tamrah and
I will be happy to do whatever, you guys just need to tell us what you
need. We had that meeting last week with Scott and got a lot of
> questions answered, but don't hesitate to send off e-mails
to Dave or whomever up at Lockheed (and CC everyone else up there too-they
may have answers too).
> Jeff, could you write up some Scenario Cases that we can turn into
test cases later in the semester? Once the back end stuff is complete,
we want to be able to add, edit, and monitor/view whatever devices > we
have in the system, and later groups can work on the user interface and
such.
> The presentation isn't that big of a deal-it's only 15 minutes long,
but it needs to be solid-our professor seems big on that. I'll work
on putting together some slides this weekend. Mike and Tamrah are
> going to help me with that, and remember, all three of us are available
to help out with any DB stuff or scenario cases or whatnot, we just need
to know what we can do.
>
> I just spoke with Eric, and suggest that we get together this Sunday
at 11:00 am to work on the Presentation as well as creating some of the
DB on the server machine, which may change, but that's the way
Jeff,
I figured out how to put the constraints on the Database. It is
actually
pretty cool.
First, I made some changes to the database. I put all three "constants"
tables into one "BEHAVIOR_CHARACTERISTICS_T" table. I then took
"type" out
of Behavior_T and stuck it in that new table.
To put behavior constraints on each device, I needed to create a table
containing each device id and all behaviors (behavior characteristics)
that
this device has. I made this table by way of a view. This
view is created
using recursive SQL calls. The SQL to create the view is::
-------------------------------------------------------------
CREATE VIEW BEHAVIOR_CONSTRAINTS_V AS
SELECT D.DEVICE_ID, C.CHARACTERISTIC_ID
FROM DEVICE_T D, BEHAVIOR_CHARACTERISTICS_T C
WHERE CHARACTERISTIC_ID =
(SELECT CHARACTERISTIC_ID
FROM BEHAVIOR_T
WHERE BEHAVIOR_ID =
(SELECT BEHAVIOR_ID
FROM BEHAVIOR_SET_T
WHERE BEHAVIOR_SET_ID =
(SELECT BEHAVIOR_SET_ID
FROM DEVICE_T
WHERE DEVICE_ID = D.DEVICE_ID)))
-------------------------------------------------------------
Please take a moment to see what is happening here. We will end
up with a
table containing Device_ID's and all thier possible behavior
characteristics.
somethign like this::
DEVICE_ID CHARACTERISTIC_ID
0 3
0 6
0 8
1 2
1 3
1 8
etc.....
so a behavior may be mapped to serveral characteristics. See????
now we just have to add code to put constraints on the SENSOR, STATUS,
and
ALARM tables based of this view and all is well.
to add constraints to the three tables you do something like this::
-------------------------------------------------------------------------
ALTER TABLE table_name
ADD CONSTRAINT DEVICE_CHARACTERISTIC_FK FOREIGN KEY(DEVICE_ID,
CHARACTERISTIC_ID)
REFERENCES BEHAVIOR_CONSTRAINTS_V (DEVICE_ID, CHARACTERISTIC_ID)
-------------------------------------------------------------------------
this will ensure that your DEVICE_ID, CHARACTERISTIC_ID pair matches
a pair
in the BEHAVIOR_CONSTRAINTS_V
As a side note:
Now you probably understants why we didn't want to drop all those
"_T"s at
the end of all the tables. It just makes understanding the database
easier.