Electric Imp 1st Cut

31910_originalI have the 1st-Cut:  a database that via the Python script randomly generates thousands upon thousands of rows very quickly. On the surface all is good, but when I dived in a little deeper I realised the data is non-sensical. The good things so far:

  • I have tuples (rows) for each relation
  • Attributes are of the expected datatype and length
  • Relations are related via foreign keys

This is great, I have relations with many entries, and they are suitably related through foreign keys. However, the data does not necessarily make sense. For example,:

CommissionedImp {deviceID, networkID, impID}

A commissioned imp represents an imp that is connected to some network. Logically, an imp may only be connected to a single network at anyone time, therefore the foreign key, `impID`, should be unique in the relation CommissionedImp (should also be a referential integrity constraint whereby `impID` is in the set of ids in the `Imp` relation). At this point in time, I have not included the necessary logic in my Python script. As a result, there are multiple rows that contain any given impID. I need to account for these kind of integrity constraints (similar for the Link table, where both FKs must be in the set of Node IDs).

draftFor the 2nd-Cut I will build the Electric Imp db within Sql Server. My task will be to include the logical constraints that makes the data sensible. At this stage I think I will not emphasise non-critical data, for example:

Developer {devID, username, password, email, mobile, address, city, ZIP}

DevID is the primary key, just like in the Python script, this will be set to automatically increment from 1 upwards. A Developer’s username is critical, and should be unique (ideally). I need an integrity constraint to ensure this. Beyond the username, the rest of the attributes do not hold any real information, for the purposes of our assignment. Of course, they need to be present, but for all intents and purposes they are placeholders for the sake of our assignment. Regardless of their value, we will still be able to demonstrate a working db with CRUD operations. I think the important idea here, is that any update to non-key attributes will have no further cascading effects upon the db, therefore they are not mission critical. As such (and to save my imagination and typing skills) I will set some of these as derived or computed attributes, or even NULLS SIMPLY FOR THE PURPOSES OF THE ASSIGNMENT!!!!!! Clearly in real life, they would offer real information about the Developer.

Looking ahead to the 3rd-Cut, I will also begin to think about doing a Transactional CRUD Analysis, and identify central relations / transactions within the model.


2 thoughts on “Electric Imp 1st Cut

  1. I also feel the same about my model like I have made tables which are not necessarily required because the same information can be fetched by simply writing the query..


    • Know what you mean! I axed all of those tables in the logical, but felt that it stripped it out quite a lot. But then, we might as well just have a hash-table if we aren’t going to take advantage of joins and what not 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s