Re: Normalization Question

From: Neo <neo55592_at_hotmail.com>
Date: 21 Jan 2005 13:13:08 -0800
Message-ID: <1106341988.449090.171880_at_c13g2000cwb.googlegroups.com>

An experimental db that handles some changes "without changing the logical model" was described most recently in thread titled "Demo: Modelling Cost of Travel Paths Between Towns". In specific, see messages exchanged between Neo and Alan/Ed about creating an initial schema/query that were resilient to changes.

Below script models the original poster's schema in a fairly normalized manner. It then adds a single person named John who lives on 123 Main St, Dallas, TX; has home, work and cell#s 111-1111; an additional cell# 222-2222; was added, updated, promoted and deleted on 1/1/2005.

// Create items in directory to classify things.
(CREATE *person.item ~in = dir)
(CREATE *address.item ~in = dir)
(CREATE *street.item ~in = dir)
(CREATE *city.item ~in = dir)
(CREATE *state.item ~in = dir)
(CREATE *phone#.item ~in = dir)
(CREATE *home.item ~in = dir)
(CREATE *work.item ~in = dir)
(CREATE *cell.item ~in = dir)
(CREATE *date.item ~in = dir)

// Create necessary adverbs
// Note: db already knows first and last
(CREATE *add.cls = adverb)
(CREATE *promote.cls = adverb)
(CREATE *update.cls = adverb)
(CREATE *delete.cls = adverb)

// Create name John
(CREATE *john.cls = name)

// Create state Texas
(CREATE *texas.cls = state)

// Create city Dallas
(CREATE *dallas.cls = city)
(CREATE it.state = texas)

// Create steet 123 Main St
(CREATE *123 main st.cls = street)

// Create address: 123 Main St, Dallas, TX
(CREATE *.cls = address)
(CREATE it.street = 123 main st)
(CREATE it.city = dallas)

// Create two phone#s
(CREATE *111-1111.cls = phone#)
(CREATE *222-2222.cls = phone#)

// Create a date
(CREATE *1/1/2005.cls = date)

// Create a person named John John.
// Note: His first and last names are the same.
(CREATE *.cls = person)
(CREATE it.name,first = john)
(CREATE it.name,last = john)
(CREATE it.address = (SELECT %.street=123 main st))

(CREATE it.phone#,home = 111-1111)
(CREATE it.phone#,work = 111-1111)
(CREATE it.phone#,cell = 111-1111)
(CREATE it.phone#,cell = 222-2222)

(CREATE it.date,add = 1/1/2005)
(CREATE it.date,update = 1/1/2005)
(CREATE it.date,promote = 1/1/2005)
(CREATE it.date,delete = 1/1/2005)

// Find a person whose last name is John
// and was updated on 1/1/2005.
// Finds person named John John.

(SELECT %.cls = person & %.name,last=john & %.date,update=1/1/2005)
Received on Fri Jan 21 2005 - 22:13:08 CET