This lens is currently under construction. Please go to SQLite The Hammer for more information on SQLite.
For more information on SQLite, go to SQLite The Hammer. For more information on programming with SQLite, go to SQLite Programming. For an introductory tutorial SQL or SQLite go to SQLite Tutorial.
Contents at a Glance
- Good Book on Database Design
- How To Design a Simple Phone Book with SQLite
- Simple Phone Book: Unique Attributes
Good Book on Database Design
A wise old computer scientist once said, "Make your data structures smart and your code dumb". That is so true! When you have smart data structures, you just don't have to write as much code to build your application. SQLite makes it really easy to design smart data structures, but If you haven't done a lot of data design, it's hard. Get "Beginning Database Design: From Novice to Professional" if you need to jump-start your data design.
Beginning Database Design: From Novice to Professional
Read a snippet at Google Book Search
Avg. Customer Rating: 
Amazon Price: $28.62 (as of 11/29/2009) 
List Price: $34.99
How To Design a Simple Phone Book with SQLite
...a good example to show how to get going with SQLite
Start by firing up SQLite.
C:\Users\Jay> sqlite3 phonebook.db
SQLite version 3.5.6
Enter ".help" for instructions
Now create the table. (The .schema command above shows you which tables are in this database.)
sqlite> create table phonebook (name string, phone string);
sqlite> .schema
CREATE TABLE phonebook (name string, phone string);
sqlite> insert into phonebook values ("Jay", "555-1234");
sqlite> insert into phonebook values ("Jay", "222-1234");
sqlite> insert into phonebook values ("Kay", "555-3333");
sqlite> insert into phonebook values ("Ray", "222-1111");
sqlite> insert into phonebook values ("May", "222-1111");
sqlite> insert into phonebook values ("Fay", "333-1111");
sqlite> insert into phonebook values ("Balasubrahmaniam", "333-1111");
sqlite>
Now show the table. (Note that "|" separates the columns of the rows).
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Kay|555-3333
Ray|222-1111
May|222-1111
Fay|333-1111
Balasubrahmaniam|333-1111
sqlite>
Now change a phone number (with update) and see the effect on the phone book compared to what was above.
sqlite> update phonebook set phone="666-1234" where name="Ray";
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Kay|555-3333
Ray|666-1234
May|222-1111
Fay|333-1111
Balasubrahmaniam|333-1111
Now change a name for a given phone number.
sqlite> update phonebook set name="Fay Jones" where phone="333-1111";
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Kay|555-3333
Ray|666-1234
May|222-1111
Fay Jones|333-1111
Fay Jones|333-1111
Notice that there were two names changed because they both had the same phone number.
Now delete a user from the phone book and check for updates.
sqlite> delete from phonebook where name="Kay"
...> ;
sqlite> select * from phonebook;
Jay|555-1234
Jay|222-1234
Ray|666-1234
May|222-1111
Fay Jones|333-1111
Fay Jones|333-1111
Now find phone numbers with "333" in it.
sqlite> select * from phonebook where phone like "333";
sqlite> select * from phonebook where phone like "%333%";
Fay Jones|333-1111
Fay Jones|333-1111
Now find phone numbers with "222" in it.
sqlite> select * from phonebook where phone like "%222%";
Jay|222-1234
May|222-1111
Now put another number into the book, and look for the numbers with "222" in it.
sqlite> insert into phonebook values ("Balasubrahmaniam", "333-2221");
sqlite> select * from phonebook where phone like "%222%";
Jay|222-1234
May|222-1111
Balasubrahmaniam|333-2221
Now delete all entries from the phone book and check that it worked.
sqlite> delete from phonebook;
sqlite> select * from phonebook;
sqlite>
Phonebook
This shows how to use SQLite to build a simple phone book in a SQLite table. It shows also how to add entries, delete entries, update entries, and search for phone numbers and names. For more information on SQLite, see http://www.squidoo.com/sqlitehammer http://www.squidoo.com/sqliteprogramming http://www.squidoo.com/sqlitetutorial http://www.squidoo.com/sqlitedatabasedesign
Runtime: 528
192 views
0 Comments:
curated content from YouTube
Simple Phone Book: Unique Attributes
...one way to write less code and avoid errors...
The way to solve this is to use unique columns. When you define a table, define a column as UNIQUE, and the database itself will disallow entering duplicates.
From the example above do the following in SQLite.
sqlite> drop table phonebook;
sqlite> create table phonebook (name string unique, phone string);
The last command tells you that the names in the table must be unique. SQLite will reject attempts to insert or modify data that causes non-unique names.
Then insert some data.
sqlite> insert into phonebook values ("Jay", "555-1234");
So far, so good.
sqlite> insert into phonebook values ("Jay", "222-1234");
SQL error: column name is not unique
sqlite> select * from phonebook;
Jay|555-1234
Notice that SQLite rejected the same insertion attempt for "Jay", and the select statement proves it.
sqlite> insert into phonebook values ("Ray", "222-1111");
sqlite> select * from phonebook;
Jay|555-1234
Ray|222-1111
sqlite>
SQLite Unique Attributes
This video shows how to specify tables in SQL with unique attributes. This video is a part of my Squidoo Lens at http://www.squidoo.com/sqlitedatabasedesign#module46059292. For more information on SQLite, see http://www.squidoo.com/sqlitehammer http://www.squidoo.com/sqliteprogramming http://www.squidoo.com/sqlitetutorial http://www.squidoo.com/sqlitedatabasedesign
Runtime: 223
105 views
0 Comments:
curated content from YouTube
How to Use SQL Join Tables to Extract Data From Many Tables
Many times when designing normalized data structures, you are asked to split information across multiple tables. It seems wasteful, but you can sort of understand why. For example, consider this example where you have many users and phones. Each phone is associated with a user with its "user_id" field.C:\Users\Jay>sqlite3 joinexample.db
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> create table users (id integer, name string);
sqlite> insert into users values (1, "Jay");
sqlite> insert into users values (2, "Ray");
sqlite> insert into users values (3, "Kay");
sqlite> insert into users values (4, "May");
sqlite> create table phones (user_id integer, number string);
sqlite> insert into phones values (1, "221-7182");
sqlite> insert into phones values (1, "492-9999");
sqlite> insert into phones values (2, "836-4252");
sqlite> insert into phones values (2, "271-4002");
sqlite> insert into phones values (3, "737-1111");
sqlite> insert into phones values (4, "969-4322");
sqlite> select * from users;
1|Jay
2|Ray
3|Kay
4|May
sqlite> select * from phones;
1|221-7182
1|492-9999
2|836-4252
2|271-4002
3|737-1111
4|969-4322
So far, so good. Now if somebody asked you to figure out which phone numbers belong to Ray, you would do the following:
1) You would look into the table and realize that Ray has an "id" of "2".
2) Then you go the phones table and realize that there are 2 phone numbers belonging to "user_id" of "2", namely "836-4252", and "271-4002".
Although it seems easy to do this by hand, doing it in software is harder unless you understand the concept of joining tables in SQL. Consider the following statement.
sqlite> select * from users, phones;
1|Jay|1|221-7182
1|Jay|1|492-9999
1|Jay|2|836-4252
1|Jay|2|271-4002
1|Jay|3|737-1111
1|Jay|4|969-4322
2|Ray|1|221-7182
2|Ray|1|492-9999
2|Ray|2|836-4252
2|Ray|2|271-4002
2|Ray|3|737-1111
2|Ray|4|969-4322
3|Kay|1|221-7182
3|Kay|1|492-9999
3|Kay|2|836-4252
3|Kay|2|271-4002
3|Kay|3|737-1111
3|Kay|4|969-4322
4|May|1|221-7182
4|May|1|492-9999
4|May|2|836-4252
4|May|2|271-4002
4|May|3|737-1111
4|May|4|969-4322
sqlite>
You'll notice that each row of the result is from combining each row of the "users" table with each row of the "phones" table. Thus you would expect 4 users X 6 phones for a total of 24 rows. This is just a set join operation in relational algebra. (See Wikipedia on Relational Algebra).
How is this useful?
In SQL, you can also filter the results. For example suppose you want to join the tables, but only pick the tables where the "id" of the "users" table matches the "user_id" of the "phones" table. Then you write:
sqlite> select * from users, phones where users.id = phones.user_id;
1|Jay|1|221-7182
1|Jay|1|492-9999
2|Ray|2|836-4252
2|Ray|2|271-4002
3|Kay|3|737-1111
4|May|4|969-4322
Conceptually, this causes the SQL engine to build the 24-row result above, and then filters out the rows where the "users.id" and "phones.user_id" fields don't match.
The following example is a bit more efficient in most database engines.
sqlite> select * from users, phones on users.id = phones.user_id;
1|Jay|1|221-7182
1|Jay|1|492-9999
2|Ray|2|836-4252
2|Ray|2|271-4002
3|Kay|3|737-1111
4|May|4|969-4322
sqlite>
Conceptually it causes only the rows where "users.id" match "phones.user_id" to be put into the result row. (There is no difference in performance with SQLite, but there may be with other SQL database engines).
If you want to use this to present in a nice user interface, you probably don't want the "id" interface in the output. In that case you just do:
sqlite> select users.name, phones.number from users, phones on users.id=phones.user_id;
Jay|221-7182
Jay|492-9999
Ray|836-4252
Ray|271-4002
Kay|737-1111
May|969-4322
sqlite>
In this case, you had to select which field you wanted, and from which table. In the example above, we took the "name" from the "users" table and the "number" from the "phones" table. Under the covers, we are still only picking results where "users.id=phones.user_id".
Table joins can also be extended to multiple tables, and with multiple filtering conditions. When you know you can use joins to get the information you need, you'll feel much safer normalizing your data and storing a piece of information only once.
Video: How to Use SQL Join Tables to Extract Data From Many Tables
...see joins live.
SQLite Join Tables
This tutorial shows how to create tables in SQLite, and then how to extract data from them using the SQL joins and filters. This is part of my Squidoo Lens at http://www.squidoo.com/sqlitedatabasedesign#module46058772 For more information on SQLite, see http://www.squidoo.com/sqlitehammer http://www.squidoo.com/sqliteprogramming http://www.squidoo.com/sqlitetutorial http://www.squidoo.com/sqlitedatabasedesign
Runtime: 430
371 views
4 Comments:
curated content from YouTube
How To Generate Data-driven Strings Using SQLite
Powerful data-driven string construction in SQLite
sqlite> select "a" || 2 || "b" ;
a2b
sqlite>
you'll notice that you can concatenate multiple elements of varying types. SQLite handles it for you. This is fairly easy to do in Ruby or Perl, but in C you have to worry about allocating and de-allocating memory buffers to hold strings, and to convert from char to int (atoi) or vice versa (itoa). SQLite provides a simple way to build the strings and all of the memory allocation and type conversions are handled by the SQLite library.
The example below shows how to build a list of function names based on a list of things and a list of common operations. Concatenating integers (rowid) and literal strings are thrown into the mix for completeness. Run the commands and study the results as it relates to the query.
sqlite> .dump
BEGIN TRANSACTION;
CREATE TABLE things (thingName string);
INSERT INTO "things" VALUES('Button');
INSERT INTO "things" VALUES('Window');
INSERT INTO "things" VALUES('Pane');
INSERT INTO "things" VALUES('Label');
CREATE TABLE operations(opName string);
INSERT INTO "operations" VALUES('Create');
INSERT INTO "operations" VALUES('Read');
INSERT INTO "operations" VALUES('Update');
INSERT INTO "operations" VALUES('Delete');
INSERT INTO "operations" VALUES('Ioctl');
COMMIT;
sqlite> select thingName || "_" || opName || "___" || things.rowid || "__" || operations.rowid from things, operations;
Button_Create___1__1
Button_Read___1__2
Button_Update___1__3
Button_Delete___1__4
Button_Ioctl___1__5
Window_Create___2__1
Window_Read___2__2
Window_Update___2__3
Window_Delete___2__4
Window_Ioctl___2__5
Pane_Create___3__1
Pane_Read___3__2
Pane_Update___3__3
Pane_Delete___3__4
Pane_Ioctl___3__5
Label_Create___4__1
Label_Read___4__2
Label_Update___4__3
Label_Delete___4__4
Label_Ioctl___4__5
sqlite>
If you need data-driven string construction, SQLite provides a convenient native utility, and it is especially useful for C programs that use SQLite.
SQL Books
SQL Cookbook (Cookbooks (O'Reilly))
Read a snippet at Google Book Search .
Amazon Price: $26.37 (as of 11/29/2009)
Joe Celko's SQL for Smarties: Advanced SQL Programming Third Edition (The Morgan Kaufmann Series in Data Management Systems)
Read a snippet of this book at Google Book Search.
Amazon Price: $38.40 (as of 11/29/2009)
How To Use SQLite For a SNMP Agent Implementation
Use SQLite for SNMP data storage.
Here is a simple example:
We havea 4-port DSL home router, with 4 10-100 ethernet ports, and one DSL port. There are no removable modules. Provide the data for a SNMP agent to "walk" the Entity MIB.
The entity MIB tells what is actually there. This is a pretty trivial example because this router has exactly one module.
According to RFC 2737 ,the Entity MIB data structure looks like the structure below. (My comments are to the right of each variable).
EntPhysicalEntry ::= SEQUENCE {
entPhysicalIndex Index of table, starting at 1
entPhysicalDescr Manufacturer's name, product code
entPhysicalVendorType Use NULL here
entPhysicalContainedIn index of entity contained in. Use 0 for the
entPhysicalClass ports=10, chassis=3
entPhysicalParentRelPos port number (1..4) for ethernet, 1 for DSL, 0 for chassis
entPhysicalName "home router" for the chassis, "DSL uplink" for the DSL uplink, and "port XX" for the ethernet port.
entPhysicalHardwareRev "" for the ports, "hrx213" for the chassis.
entPhysicalFirmwareRev "" for ports, "hrx213fw" for the chassis.
entPhysicalSoftwareRev "" for the ports, "hrx213sw-build101" for the chassis
entPhysicalSerialNum "" for the ports, "MFGXX12748398" for the chassis
entPhysicalMfgName "XX Networks" for all.
entPhysicalModelName "" for ports "DSL Home Router 3x17" for chassis
entPhysicalAlias "" for all
entPhysicalAssetID ""
entPhysicalIsFRU true=1 for chassis, false=2 for ports.
}
There will be one row for the router, one entry for the DSL port, and 4 entries for the ethernet ports. Read the RFC 2737 for details.
Here is what the table will look like on its side.
entPhysicalIndex 1 2 3 4 5 6
entPhysicalDescr "XX Networks DSL Home Router 3x17" "" "" "" "" ""
entPhysicalVendorType "" "" "" "" ""
entPhysicalContainedIn 0 1 1 1 1 1
entPhysicalClass 3 10 10 10 10 10
entPhysicalParentRelPos 0 1 1 2 3 4
entPhysicalName "home router" "DSL uplink" "ethernet port 1" "ethernet port 2" "ethernet port 3" "ethernet port 4"
entPhysicalHardwareRev "hrx213" "" "" "" "" ""
entPhysicalFirmwareRev "hrx213fw" "" "" "" "" ""
entPhysicalSoftwareRev "hrx213sw-build101" "" "" "" "" ""
entPhysicalSerialNum "MFGXX12748398" "" "" "" "" ""
entPhysicalMfgName "XX Networks" "" "" "" "" ""
entPhysicalModelName "DSL Home Router 3x17" "" "" "" "" ""
entPhysicalAlias "" "" "" "" "" ""
entPhysicalAssetID "" "" "" "" "" ""
entPhysicalIsFRU 1 2 2 2 2 2
To implement the table in SQLite, first create the table:
CREATE TABLE entPhysicalTable (entPhysicalIndex integer, entPhysicalDescr string, entPhysicalVendorType string, entPhysicalContainedIn integer, entPhysicalClass integer, entPhysicalParentRelPos integer, entPhysicalName string, entPhysicalHardwareRev string, entPhysicalFirmwareRev string, entPhysicalSoftwareRev string, entPhysicalSerialNum string, entPhysicalMfgName string, entPhysicalModelName string, entPhysicalAlias string, entPhysicalAssetID string, entPhysicalIsFRU integer);
The statements to initialize the tables is as follows:
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (1,0,"home router");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (2,1,"DSL uplink");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (3,1,"ethernet port 1");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (4,2,"ethernet port 2");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (5,3,"ethernet port 3");
insert into entPhysicalTable (entPhysicalIndex, entPhysicalParentRelPos, entPhysicalName) values (6,4,"ethernet port 4");
update entPhysicalTable set entPhysicalDescr='', entPhysicalVendorType="", entPhysicalContainedIn=1, entPhysicalClass=10, entPhysicalHardwareRev="", entPhysicalFirmWareRev="", entPhysicalSoftwareRev="", entPhysicalSerialNum="", entPhysicalMfgName="", entPhysicalModelName="", entPhysicalAlias="", entPhysicalAssetID="" ,entPhysicalIsFRU=2;
update entPhysicalTable set entPhysicalDescr="XX Networks DSL Home Router 3x17", entPhysicalModelName="DSL Home Router 3x17", entPhysicalIsFRU=1 where entPhysicalIndex=1;
These can be done once when the load is built, or at initialization time.
There will be a software routine that reads the hardware, firmware, and software revisions at initialization time from well-known APIs.
Once done, do the following at system initialization time right after reading the values:
update entPhysicalTable set entPhysicalHardwareRev='hrx213', entPhysicalFirmwareRev='hrx213fw', entPhysicalSoftwareRev='hrx213sw-build101' where entPhysicalIndex=1;
The actual values will be built into the update query string in the language of your choice.
If hard disk storage is not available, an in-memory SQLite database can be used instead.
The SNMP manager will issue SNMP-GET requests to the SNMP agent. The following query satisfies the request for the index @X:
select * from entPhysicalTable where entPhysicalIndex = @X;
If the query yields no results, then the MIB objects do not exist.
The SNMP manager will issue SNMP-GETNEXT requests to the SNMP agent. The following query satisfies the request for the index @X:
select * from entPhyscialTable where entPhysicalIndex >= @X order by entPhysicalIndex limit 1;
If the query yields no results, then the table is empty or the table has been walked completely.
This example shows that you can use a very simple SQLite table and simple SQLite queries to implement the data storage for the SNMP Entity MIB.
P.S.
There is a great wiki at http://net-snmp.sourceforge.net/wiki/index.php/Tutorials which shows how to hook up NetSNMP to a c++ extension.
SQLite For A SNMP Agent For A Flexible Modular Device
Take the following example:
1) Ethernet switch chassis, with a built-in main controller, and three plug-in slots, and a 1000-base T ethernet interface...northbound
2) Optional cards can be plugged into any slot in any combination. One card type is a 2-port PoE card. The is a 4-port Ethernet card.
A number of issues come up:
1) Slots and modules are uniquely identified by slot number (1, 2, or 3).
2) Ports could be identified by a combination of slot number and port number, or just by the fact that it is a northbound port.
3) Depending on which kind of cards are plugged in, the configuration could have anywhere from 3 to 13 ports. Allocating entPhysicalIndex could be troublesome, because there will either be an inconsistent numbering scheme for a given slot/port combination, or the allocation of entPhysicalIndex could be sparse.
The solution to this problem is to realize that entPhysicalTable is only a view of data. You can and should store data in tables that are keyed by the appropriate quantities.
Also, to make this work, we need to ensure that entPhysicalIndex is consistently allocated. A convenient way to do this is to pre-allocate them up front. For example:
1) entPhysicalIndex=1 goes for the chassis.
2) 2 goes for the 1000-base-T port
3) 3, 4, and 5 go for the slots.
4) 6, 7, and 8 go for the modules.
5) Since we know that a module can have up to 4 ports, indices 9-12 go to the possible ports in slot 1. 13-16 go to the ports in slot 2. 17-20 go to the ports in slot 3.
6) We know that modules 6,7,and 8 are contained in slots 3.4,and 5 respectively (entPhysicalContainedIn). We know that the slots 3,4,and 5 as well as the port at 2 are contained in the chassis (1), that the chassis is contained in 0.
7) We know that the slots 3,4,and 5 are positioned relatively in the module as 1,2,and 3 respectively (entPhysicalParentRelPos). We also know that the port number of a port within a module is the entPhysicalParentRelPos as well. Any other contained entity (including the modules) have 1 as the entPhysicalParentRelPos.
8) We know that only modules and the chassis are field-replaceable units (entPhysicalIsFRU=1), and ports and slots/containers are not field-replaceable.
We will only focus on the following fields of the entPhysicalTable, as they are the only ones relevant to using entPhysicalTable as a view on physical entity data.
EntPhysicalEntry ::= SEQUENCE {
entPhysicalIndex Index of table, starting at 1
entPhysicalContainedIn index of entity contained in. Use 0 for the chassis
entPhysicalClass ports=10, chassis=3, container=5, module=9
entPhysicalParentRelPos port number for ethernet, 1 for 1000-Base-T, 0 for chassis, slot number for slots, and 1 for modules
entPhysicalName "FlexiRouter" for the chassis,
entPhysicalHardwareRev
entPhysicalIsFRU true=1 for chassis, false=2 for ports.
}
In this example, we need separate tables for ports, slots and modules, and input default data for the maximum configuration. For modules it is:
sqlite>CREATE TABLE Modules (slotNumber integer, hardwareRev string, moduleType string, isPresent integer, entPhysicalIndex integer);
sqlite>INSERT INTO "Modules" VALUES(1,'','',0,6);
sqlite>INSERT INTO "Modules" VALUES(2,'','',0,7);
sqlite>INSERT INTO "Modules" VALUES(3,'','',0,8);
Notice how the values for entPhysicalIndex are 6, 7, 8. Also note that isPresent=0 because nothing is present yet.
For the ports, it is a bit more tedious. Again, the port indices start at 9, and go up as specified above, and isPresent is 0 for all.
sqlite>CREATE TABLE AccessPorts (slotNumber integer, portNumber integer, portType string, isPresent integer, entPhysicalIndex integer, entPhysicalContainedIn );
sqlite>INSERT INTO "AccessPorts" VALUES(1,1,'',0,9,6);
sqlite>INSERT INTO "AccessPorts" VALUES(1,2,'',0,10,6);
sqlite>INSERT INTO "AccessPorts" VALUES(1,3,'',0,11,6);
sqlite>INSERT INTO "AccessPorts" VALUES(1,4,'',0,12,6);
sqlite>INSERT INTO "AccessPorts" VALUES(2,1,'',0,13,7);
sqlite>INSERT INTO "AccessPorts" VALUES(2,2,'',0,14,7);
sqlite>INSERT INTO "AccessPorts" VALUES(2,3,'',0,15,7);
sqlite>INSERT INTO "AccessPorts" VALUES(2,4,'',0,16,7);
sqlite>INSERT INTO "AccessPorts" VALUES(3,1,'',0,17,8);
sqlite>INSERT INTO "AccessPorts" VALUES(3,2,'',0,18,8);
sqlite>INSERT INTO "AccessPorts" VALUES(3,3,'',0,19,8);
sqlite>INSERT INTO "AccessPorts" VALUES(3,4,'',0,20,8);
Now let's suppose that we insert a "2-PoE-Port" module in slot 1, and a "4-Port-Ethernet" module in slot 3, and the plug&play software detects hardware revision "A001" for the 2 port PoE card and "B002" for the 4 port card. We'll need to update the module table as follows:
sqlite>update Modules set isPresent=1, hardwareRev="A001", moduleType="2-PoE-Port" where slotNumber=1;
sqlite>update Modules set isPresent=1, hardwareRev="B002", moduleType="4-Port-Ethernet" where slotNumber=3;
We'll need to update the port table as follows:
sqlite>update AccessPorts set isPresent=1, portType="PoE" where slotNumber=1 and portNumber>0 and portNumber <3;
sqlite>update AccessPorts set isPresent=1, portType="Ethernet" where slotNumber=3;
For simplicity we'll have a table for the slots:
sqlite> create table Slots (slotNumber integer, entPhysicalIndex integer);
sqlite> insert into Slots values (1,3);
sqlite> insert into Slots values (2,4);
sqlite> insert into Slots values (3,5);
We don't need tables for the chassis, or the 1000-base T port on the chassis, because they are always there, but we will need to represent them in a view for entPhysicalTable.
The view of entPhysicalTable is just a union of the views for the chassis, uplink, slots, modules and ports, and ordered by entPhysicalIndex. The SQL view below encodes this idea.
create view entPhysicalTable as
-- This part represents the whole chassis
select 1 as entPhysicalIndex, 0 as entPhysicalContainedIn, 3 as
entPhysicalClass, 0 as entPhysicalParentRelPos, "FlexiRouter" as
entPhysicalName, "AAXX01" as entPhysicalHardwareRev, 1 as
entPhysicalIsFRU
UNION
-- This part represents the 1000 Base T uplink
select 2 as entPhysicalIndex, 1 as entPhysicalContainedIn, 10 as
entPhysicalClass, 1 as entPhysicalParentRelPos, "1000-Base-T Uplink" as
entPhysicalName, "" as entPhysicalHardwareRev, 2 as entPhysicalIsFRU
UNION
-- This part represents the slots
select entPhysicalIndex, 1 as entPhysicalContainedIn, 5 as
entPhysicalClass, slotNumber as entPhysicalParentRelPos, "Slot " ||
slotNumber as entPhysicalName, "" as entPhysicalHardwareRev, 2 as
entPhysicalIsFRU from Slots
UNION
-- This part represents the modules
select entPhysicalIndex, entPhysicalIndex-3 as entPhysicalContainedIn,
9 as entPhysicalClass, slotNumber as entPhysicalParentRelPos,
moduleType || ' module/slot ' || slotNumber as entPhysicalName,
hardwareRev as entPhysicalHardwareRev, 1 as entPhysicalFRU from Modules
where isPresent=1
UNION
-- This part represents the ports
select entPhysicalIndex, entPhysicalContainedIn, 10 as
entPhysicalClass, portType || "/slot=" || slotNumber || "/port=" ||
portNumber as entPhysicalName, "" as entPhysicalHardwareRev, 2 as
entPhysicalIsFRU from AccessPorts where isPresent=1
ORDER BY entPhysicalIndex;
Because the structure of the entPhysicalTable view is the same as with the previous example, the queries for SNMP requests are the same as with the previous example.
The SNMP manager will issue SNMP-GET requests to the SNMP agent. The following query satisfies the request for the index @X:
sqlite>select * from entPhysicalTable where entPhysicalIndex = @X;
If the query yields no results, then the MIB objects do not exist.
The SNMP manager will issue SNMP-GETNEXT requests to the SNMP agent. The following query satisfies the request for the index @X:
sqlite>select * from entPhyscialTable where entPhysicalIndex >= @X;
Wikipedia on Data Normalization
This article is great for learning why normalization is important. Remember that highly normalized data means that you have not duplicated data anywhere, and that usually results in much less application code.
In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics?insertion, update, and deletion anomalies?that could lead to a loss of data integrity.Codd, E.F. The Relational Model for Database Management: Version 2. Addison-Wesley (1990), p. 271 E.F. Codd, the inventor of the relational model, introduced the concept of normalization and what we now know as the First Normal Form (1NF) in 1970. Codd went on to define the Second Normal Form (2NF) and Third Normal Form (3NF) in 1971,Codd, E.F. "Further Normalization of the Data Base Relational Model." (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems," New York City, May 24th-25th, 1971.) IBM Research Report RJ909 (August 31st, 1971). Republished in Randall J. Rustin (ed.), Data Base Systems: Courant Computer Science Symposia Series 6. Prentice-Hall, 1972. and Codd and Raymond F. Boyce defined the Boyce-Codd Normal Form in 1974.Codd, E. F. "Recent Investigations into Relational Data Base Systems." IBM Research Report RJ1385 (April 23rd, 1974). Republished in Proc. 1974 Congress (Stockholm, Sweden, 1974). New York, N.Y.: North-Holland (1974). Higher normal forms were defined by other theorists in subsequent years, the most recent being the Sixth Normal Form (6NF) introduced by Chris Date, Hugh Darwen, and Nikos Lorentzos in 2002.C.J. Date, Hugh Darwen, Nikos Lorentzos. Temporal Data and the Relational Model. Morgan Kaufmann (2002), p. 176
Informally, a relational database table (the computerized representation of a relation) is often described as "normalized" if it is in the Third Normal Form.C.J. Date. An Introduction to Database Systems. Addison-Wesley (1999), p. 290 Most 3NF tables are free of insertion, update, and deletion anomalies, i.e. in most cases 3NF tables adhere to BCNF, 4NF, and 5NF (but typically not 6NF).
A standard piece of database design guidance is that the designer should create a fully normalized design; selective denormalization can subsequently be performed for performance reasons.Chris Date, for example, writes: "I believe firmly that anything less than a fully normalized design is strongly contraindicated ... You should "denormalize" only as a last resort. That is, you should back off from a fully normalized design only if all other strategies for improving performance have somehow failed to meet requirements." Date, C.J. Database in Depth: Relational Theory for Practitioners. O'Reilly (2005), p. 152. However, some modeling disciplines, such as the dimensional modeling approach to data warehouse design, explicitly recommend non-normalized designs, i.e. designs that in large part do not adhere to 3NF.Ralph Kimball, for example, writes: "The use of normalized modeling in the data warehouse presentation area defeats the whole purpose of data warehousing, namely, intuitive and high-performance retrieval of data." Kimball, Ralph. The Data Warehouse Toolkit, 2nd Ed.. Wiley Computer Publishing (2002), p. 11.
How To Design Data To First Normal Form
First normal form (1NF or Minimal Form) is a normal form used in database normalization. A relational database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a faithful representation of a relation"The overriding requirement, to the effect that the table must directly and faithfully represent a relation, follows from the fact that 1NF was originally defined as a property of relations, not tables." Date, C. J. "What First Normal Form Really Means" in Date on Database: Writings 2000-2006 (Springer-Verlag, 2006), p. 128. and that it is free of repeating groups."First normal form excludes variable repeating fields and groups." Kent, William. "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM 26 (2), Feb. 1983, pp. 120-125.
The concept of a "repeating group" is, however, understood in different ways by different theorists. As a consequence, there is no universal agreement as to which features would disqualify a table from being in 1NF. Most notably, 1NF as defined by some authors (for example, Ramez Elmasri and Shamkant B. Navathe,Elmasri, Ramez and Navathe, Shamkant B. Fundamentals of Database Systems, Fourth Edition (Addison-Wesley, 2003), p. 315. following the precedent established by Edgar F. Codd) excludes relation-valued attributes (tables within tables); whereas 1NF as defined by other authors (for example, Chris Date) permits them.
How To Design Data To Second Normal Form
Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. CoddCodd, E.F. "Further Normalization of the Data Base Relational Model." (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems," New York City, May 24th-25th, 1971.) IBM Research Report RJ909 (August 31st, 1971). Republished in Randall J. Rustin (ed.), Data Base Systems: Courant Computer Science Symposia Series 6. Prentice-Hall, 1972. in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.
In slightly more formal terms: a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is one that does not belong to any candidate key.)
Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.
How To Design Data To Third Normal Form
The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. CoddCodd, E.F. "Further Normalization of the Data Base Relational Model." (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems," New York City, May 24th-25th, 1971.) IBM Research Report RJ909 (August 31st, 1971). Republished in Randall J. Rustin (ed.), Data Base Systems: Courant Computer Science Symposia Series 6. Prentice-Hall, 1972. in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:
* The relation R (table) is in second normal form (2NF)
* Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.
A non-prime attribute of R is an attribute that does not belong to any candidate key of R.Codd, 43. A transitive dependency is a functional dependency in which X ? Z (X determines Z) indirectly, by virtue of X ? Y and Y ? Z (where it is not the case that Y ? X).Codd, 45-46.
A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X ? A, at least one of the following conditions holds:
* X contains A (that is, X ? A is trivial functional dependency), or
* X is a superkey, or
* A is a prime attribute (i.e., A is contained within a candidate key)Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." ACM Transactions on Database Systems 7(3), September 1982.
Zaniolo's definition gives a clear sense of the difference between 3NF and the more stringent Boyce-Codd normal form (BCNF). BCNF simply eliminates the third alternative ("A is a prime attribute").
How To Design Data To Boyce-Codd Normal Form
Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X ? Y, X is a superkey?that is, X is either a candidate key or a superset thereof.
BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by 3NF as originally defined.Codd, E. F. "Recent Investigations into Relational Data Base Systems." IBM Research Report RJ1385 (April 23rd, 1974). Republished in Proc. 1974 Congress (Stockholm, Sweden, 1974). New York, N.Y.: North-Holland (1974).
Chris Date has pointed out that a definition of what we now know as BCNF appeared in a paper by Ian Heath in 1971.Heath, I. "Unacceptable File Operations in a Relational Database." Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access, and Control, San Diego, Calif. (November 11th-12th, 1971). Date writes: "Since that definition predated Boyce and Codd's own definition by some three years, it seems to me that BCNF ought by rights to be called Heath normal form. But it isn't."Date, C.J. Database in Depth: Relational Theory for Practitioners. O'Reilly (2005), p. 142.
An example of a 3NF table that does not meet BCNF is:
*Each row in the table represents a court booking at a tennis club that has one hard court (Court 1) and one grass court (Court 2)
*A booking is defined by its Court and the period for which the Court is reserved
*Additionally, each booking has a Rate Type associated with it. There are four distinct rate types:
:*SAVER, for Court 1 bookings made by members
:*STANDARD, for Court 1 bookings made by non-members
:*PREMIUM-A, for Court 2 bookings made by members
:*PREMIUM-B, for Court 2 bookings made by non-members
The table's candidate keys are:
*{Court, Start Time}
*{Court, End Time}
*{Rate Type, Start Time}
*{Rate Type, End Time}
Recall that 2NF prohibits partial functional dependencies of non-prime attributes on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. In the Today's Court Bookings table, there are no non-prime attributes: that is, all attributes belong to candidate keys. Therefore the table adheres to both 2NF and 3NF.
The table does not adhere to BCNF. This is because of the dependency Rate Type ? Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key.
Any table that falls short of BCNF will be vulnerable to logical inconsistencies. In this example, enforcing the candidate keys will not ensure that the dependency Rate Type ? Court is respected. There is, for instance, nothing to stop us from assigning a PREMIUM A Rate Type to a Court 1 booking as well as a Court 2 booking?a clear contradiction, as a Rate Type should only ever apply to a single Court.
The design can be amended so that it meets BCNF:
The candidate keys for the Rate Types table are {Rate Type} and {Court, Member Flag}; the candidate keys for the Today's Bookings table are {Court, Start Time} and {Court, End Time}. Both tables are in BCNF. Having one Rate Type associated with two different Courts is now impossible, so the anomaly affecting the original table has been eliminated.
Consider the following non-BCNF table whose functional dependencies follow the {AB ? C, C ? B} pattern:
For each Person / Shop Type combination, the table tells us which shop of this type is geographically nearest to the person's home. We assume for simplicity that a single shop cannot be of more than one type.
The candidate keys of the table are:
* {Person, Shop Type}
* {Person, Nearest Shop}
Because all three attributes are prime attributes (i.e. belong to candidate keys), the table is in 3NF. The table is not in BCNF, however, as the Shop Type attribute is functionally dependent on a non-superkey: Nearest Shop.
The violation of BCNF means that the table is subject to anomalies. For example, Eagle Eye might have its Shop Type changed to "Optometrist" on its "Fuller" record while retaining the Shop Type "Optician" on its "Davidson" record. This would imply contradictory answers to the question: "What is Eagle Eye's Shop Type?" Holding each shop's Shop Type only once would seem preferable, as doing so would prevent such anomalies from occurring:
In this revised design , the "Shop Near Person" table has a candidate key of {Person, Shop}, and the "Shop" table has a candidate key of {Shop}. Unfortunately, although this design adheres to BCNF, it is unacceptable on different grounds: it allows us to record multiple shops of the same type against the same person. In other words, its candidate keys do not guarantee that the functional dependency {Person, Shop Type} ? {Shop} will be respected.
A design that eliminates all of these anomalies (but does not conform to BCNF) is possible.Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." ACM Transactions on Database Systems 7(3), September 1982, pp. 493. This design consists of the original "Nearest Shops" table supplemented by the "Shop" table described above.
If a referential integrity constraint is defined to the effect that {Shop Type, Nearest Shop} from the first table must refer to a {Shop Type, Shop} from the second table, then the data anomalies described previously are prevented.
How To Design Data To Fourth Normal Form
Fourth normal form (4NF) is a normal form used in database normalization. Introduced by Ronald Fagin in 1977, 4NF is the next level of normalization after Boyce-Codd normal form (BCNF). Whereas the second, third, and Boyce-Codd normal forms are concerned with functional dependencies, 4NF is concerned with a more general type of dependency known as a multivalued dependency. A table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X ?? Y, X is a superkey?that is, X is either a candidate key or a superset thereof."A relation schema R* is in fourth normal form (4NF) if, whenever a nontrivial multivalued dependency X ?? Y holds for R*, then so does the functional dependency X ? A for every column name A of R*. Intuitively all dependencies are the result of keys."
How To Design Data To Fifth Normal Form
Fifth normal form (5NF), also known as Project-join normal form (PJ/NF) is a level of database normalization, designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships. A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys.
A join dependency *{A, B, ? Z} on R is implied by the candidate key(s) of R if and only if each of A, B, ?, Z is a superkey for R.Analysis of normal forms for anchor-tables
How To Design Data To Domain-Key Normal Form
Domain/key normal form (DKNF) is a normal form used in database normalization which requires that the database contains no constraints other than domain constraints and key constraints.
A domain constraint specifies the permissible values for a given attribute, while a key constraint specifies the attributes that uniquely identify a row in a given table.
The domain/key normal form is achieved when every constraint on the relation is a logical consequence of the definition of keys and domains, and enforcing key and domain restraints and conditions causes all constraints to be met. Thus, it avoids all non-temporal anomalies.
The reason to use domain/key normal form is to avoid having general constraints in the database that are not clear domain or key constraints. Most databases can easily test domain and key constraints on attributes. General constraints however would normally require special database programming in the form of stored procedures that are expensive to maintain and expensive for the database to execute. Therefore general constraints are split into domain and key constraints.
It's much easier to build a database in domain/key normal form than it is to convert lesser databases which may contain numerous anomalies. However, successfully building a domain/key normal form database remains a difficult task, even for experienced database programmers. Thus, while the domain/key normal form eliminates the problems found in most databases, it tends to be the most costly normal form to achieve. However, failing to achieve the domain/key normal form may carry long-term, hidden costs due to anomalies which appear in databases adhering only to lower normal forms over time.
The Boyce-Codd normal form, Third normal form, Fourth normal form and Fifth normal form are special cases of the domain/key normal form. All have either functional, multi-valued or join dependencies that can be converted into (super)keys. The domains on those normal forms were unconstrained so all domain constraints are satisfied. However, transforming a higher normal form into domain/key normal form is not always a dependency-preserving transformation and therefore not always possible.
How To Design Data To Sixth Normal Form
Sixth normal form (6NF) is a term in relational database theory, used in two different ways.
Logic Data Modeling
...videos on data design....
Explore related pages
SQLite Tutorial- Use this lens to learn about SQLite and as a tutorial SQL. It will show you how...
Everything Mac- I'm a computer engineer with over 20 years of experience working with Unix, Wind...
SQLite: The Hammer For Software Problems- Use SQLite to solve software problems quickly for embedded systems, mobile appli...
SQLite Programming- Use this lens to learn how to write software using SQLite. It starts with a desc...
