TABLE SETUP:


ALARM_T	DEVICE_ID	ALARM_ID
BEHAVIOR_CHARACTERISTICS_T	CHARACTERISTIC_ID	CHARACTERISTIC_NAME	CHARACTERISTIC_TYPE
BEHAVIOR_NAME_T	BEHAVIOR_ID	BEHAVIOR_NAME
BEHAVIOR_SET_T	BEHAVIOR_SET_ID	BEHAVIOR_TYPE_ID	BEHAVIOR_SET_NAME
BEHAVIOR_T	BEHAVIOR_TYPE_ID	BEHAVIOR_CHARACTERISTIC	BEHAVIOR_ID
BEHAVIOR_SET_NAME_T	BEHAVIOR_SET_ID	SEHAVIOR_SET_NAME
DEVICE_SYSTEM_T	DEVICE_ID	SYSTEM_ID
DEVICE_T	DEVICE_ID	DEVICE_NAME	LOCATION	BEHAVIOR_SET_ID	DESCRIPTION
DEVICE_ZONE_T	DEVICE_ID	ZONE_ID
SENSOR_READING_T	DEVICE_ID	SENSOR_ID	READING
STATUS_T	DEVICE_ID	STATUS_ID
SYSTEM_T	SYSTEM_ID	PARENT_ID	NAME
USERS_T	FIRST_NAME	LAST_NAME	USER_TYPE	PASSWORD	USER_ID
ZONE_SYSTEM_T	ZONE_ID	SYSTEM_ID
ZONE_T	ZONE_ID	ZONE_NAME

VIEW SETUP:


BEHAVIOR_CHARACTERISTICS_V	BEHVIOR_ID	CHARACTERISTIC_ID	CHARACTERISTIC_NAME	BEHAVIOR_NAME	CHARACTERISTIC_TYPE
BEHAVIOR_V	BEHAVIOR_SET_ID	BEHAVIOR_SET_NAME	BEHAVIOR_ID	BEHAVIOR_NAME
DEVICE_SYSTEM_V	DEVICE_ID	SYSTEM_ID	DEVICE_NAME
DEVICE_ZONE_V	DEVICE_ID	ZONE_ID	DEVICE_NAME
ZONE_SYSTEM_V	ZONE_ID	SYSTEM_ID	SYSTEM_NAME
BEHAVIOR_CONSTRAINTS_V	DEVICE_ID	CHARACTERISTIC_ID

ALARM_T:


DEVICE_ID	INTEGER	Foreign Key (DEVICE_T)
ALARM_ID	INTEGER	Foreign Key (BEHAVIOR_CHARACTERISTICS_T)

BEHAVIOR_CHARACTERISTICS_T:


CHARACTERISTIC_ID	INTEGER	Foreign Key (DEVICE_T)
CHARACTERISTIC_NAME	VARCHAR (16)
CHARACTERISTIC_TYPE	INTEGER

BEHAVIOR_NAME_T:


BEHAVIOR_ID	INTEGER	Primary Key
BEHAVIOR_NAME	VARCHAR (16)

BEHAVIOR_SET_T:


BEHAVIOR_SET_ID	INTEGER	Primary Key
BEHAVIOR_TYPE_ID	INTEGER	Foreign Key (BEHAVIOR_T)

BEHAVIOR_T:


BEHAVIOR_TYPE_ID	INTEGER	Primary Key
BEHAVIOR_CHARACTERISTIC	INTEGER
BEHAVIOR_ID	INTEGER	Foreign Key (BEHAVIOR_NAME_T)

BEHAVIOR_SET_NAME_T:


BEHAVIOR_SET_ID	INTEGER	Foreign Key (BEHAVIOR_SET_T)
BEHAVIOR_SET_NAME	VARCHAR (50)

DEVICE_SYSTEM_T:


DEVICE_ID	INTEGER	Foreign Key (DEVICE_T)
SYSTEM_ID	INTEGER	Foreign Key (SYSTEM_T)

DEVICE_T:


DEVICE_ID	INTEGER	Primary Key
DEVICE_NAME	VARCHAR (50)
LOCATION	VARCHAR (50)
BEHAVIOR_SET_ID	INTEGER	Foreign Key (BEHAVIOR_SET_T)

DEVICE_ZONE_T:


DEVICE_ID	INTEGER	Foreign Key (DEVICE_T)
ZONE_ID	INTEGER	Foreign Key (ZONE_T)

SENSOR_READING_T:


DEVICE_ID	INTEGER	Foreign Key (DEVICE_T)
SENSOR_ID	INTEGER	Foreign Key (SENSOR_READING_CONSTANTS_T)
READING	FLOAT

STATUS_T:


DEVICE_ID	INTEGER	Foreign Key (DEVICE_T)
STATUS_ID	INTEGER	Foreign Key (BEHAVIOR_CHARACTERISTICS_T)

SYSTEM_ID	INTEGER	Primary Key
PARENT	INTEGER
NAME	VARCHAR (50)

USERS_T:


FIRST_NAME	VARCHAR (50)
LAST_NAME	VARCHAR (50)
USER_ID	VARCHAR (10)	Primary Key
USER_TYPE	INTEGER
PASSWORD	VARCHAR (10)

ZONE_SYSTEM_T:


ZONE_ID	INTEGER	Foreign Key (ZONE_T)
SYSTEM_ID	INTEGER	Foreign Key (SYSTEM_T)

ZONE_ID	INTEGER	Primary Key
ZONE_NAME	VARCHAR (50)

This database is for keeping track of smart devices on a warship. This database was designed with a primary goal of flexibility. Devices on a Lockheed warship needed to be very flexible in terms of the tasks they are responsible for performing. While some devices are relatively simple, and only perform a single task (such as a valve which is either “on” or “off”), other devices may perform many tasks.A device can take on several behaviors.Some devices may each share one behavior while also each having others.A device type is determined by the behaviors that that device has.Device type is determined by the devices set of behaviors (BEHAVIOR_SET). The creation of a flexible Data Base that enabled the addition of new devices and behaviors, as well as linking the two, was a primary concern of our clients. Lockheed wishes this system to be flexible enough to be easily integrated into future warships.

After multiple versions of our database and meetings with our clients, the current database was created and approved. It allows for great flexibility in devices and the way that they behave, and enables any additional behaviors to be shared by all devices. To ensure maximum flexibility in adding new elements to our system, a hierarchal structure was made to encapsulate the various ways that a device can behave. In our database, every device has a Behavior Set. A Behavior Set contains a group of behaviors. Behaviors in turn hold Behavior Characteristics. There are three types of Characteristics: Sensor, Alarm, and Status. To aid in the flexibility, there are many reusable components in the database. Behavior Characteristics can be reused in several Behaviors, Behaviors can be reused in multiple Behavior Sets, and single Behavior Sets can be used by multiple devices. Devices currently can have multiple alarms, status, and/or sensors, or any combination of the three. Our current system allows for a large amount of flexibility when determining a device’s exact functions on a ship.

Behavior Characteristics are broken down into three categories: Sensor (type 0), Alarm (type 1), and Status (type 2). All of these characteristics have a characteristic ID, name, and type. In the Behavior Characteristics Table, an example sensor would be [ 1 , "Air Temperature" , 0 ]. An example alarm would be [ 2 , "Low Power" , 1 ]. Of course, an example status would be [ 3 , "On" , 2 ]. Each type of characteristic is stored in it's own table. Storing the three different characteristic types in three different tables makes look up more efficient, which is important. For example, it makes attaining all devices currently in alarm state very fast (select * from ALARM_T).

The alarm table and status table are the same in that a device is in a given status/alarm or it isn't. Sensors are a bit different, because you have to know both what is being sensed (air temp) and what the value is ( 73 degrees). In breaking down devices behaviors into these components, advanced devices can be easily added in the future.

Devices are stored in a Device Table, each containing a unique Device ID. Further, each device has a name field, a location field, and a map to a Behavior Set ID, defining the devices various behaviors. Devices can be members of Systems and Zones. If a device is a member of a system, its device ID is stored in the Device System Table. Similairly, if a device is a member of a zone, its device ID is stored with the corresponding Zone ID in the Device Zone Table. Devices can also be "Standalone" devices. Systems are groups of devices which work together throughout the ship. Systems are stored in the System Table, each having a unique System ID, as well as a Parent ID, which, if the system is standalone, has a value of "0". An example of a system is the Air Conditioning System. Systems can be members of other systems, or they can be members of zones. Zones differ from systems in that zones are physical sections of a ship. Systems may be members of multiple zones, or multiple other systems. The systems in each zone are kept in a Zone System Table, which holds the zone ID and the corresponding system ID for the system belonging to the zone.

Finally, a User Table is kept which maintains information about registered users in the system, such as their name, their unique User ID, what type of user they are, used to determine the level of security access they have, and the user's login password. A security model may be implemented by future groups on this project.

We created several Views as well in SQL which enabled us to use the data in existing tables to create "virtual tables" with special data groupings.

Database Weaknesses

We found it very difficult to develop particular database constraints.This is probably due to the fact that none of us have had any experience doing database design.We were forced to put some constraint checking into our business logic.I will describe these things here.

You should not be able to delete a behavior characteristic unless no behaviors reference it.Likewise, no behavior should be removed unless no behavior set references it, and no behavior set should be removed unless no devices reference it.We did this in our business logic, but it would be better to have it done within the database.The same type of rules should apply to systems and zones.

A behavior may contain characteristics “on” and “off”.A device that can assume this behavior should be able to be “on” or “off” but not both at the same time.We were going to go about this by making views and putting constraints on the tables SENSOR_T, STATUS_T, and ALARM_T based of this view.You could make a view that had the device ID and its behavior Ids and characteristic ids.That would have worked out very nice, but we couldn’t figure out how to but a foreign key constraint on a table that mapped to a view.

Also we were never clear on how these devices will be putting information into this database.Will it be by the Device ID that our database assigns them, or by some special ID that each device has?We assumed it was by the device ID we assigned them.Alternatively, another field in the Device_T (serial #?) could be used to store device readings/statuses/alarms.