text:
Shopping Basket
US Dollars
Search by Keyword
Chills and preserves opened wine up to 10 days! Just place an uncorked bottle inside, close the door, slide EuroCave's patented vacuum cylinder over the bottle neck, and set the desired temperature. No stoppers, pumps, or gases required. The EuroCave SoWine Home Wine Bar is all you need!
* Exclusive vacuum technology chills and preserves wine for up to 10 days
* 2 independent compartments* can preserve red and white wines at their respective serving temperatures for by-the-glass enjoyment.
* Self-setting controls with indicator lights confirm vacuum seal and temperature
* Can also chill unopened wine bottles**
* Contemporary design complements any kitchen or entertainment area
Size: 20-1/2"H (with cylinders up) / 18"H (with cylinders down) x 9-3/4"W x 9-3/4"D
*The compartments can fit most 750mL size bottles up to 12-1/2" high. It can not accommodate 1.5L bottles and larger, as well as 375mL Split bottles. Not recommended for use with Sparkling Wines including Champagne.
**When chilling unopened wine, do not slide vacuum cylinder over bottleneck. Just close the door(s) and set the desired serving temperature for red or white.
* Capacity: Up to 2* bottles.
* Size: 20-1/2"H (with cylinders up) / 18"H (with cylinders down) x 9-3/4"W x 9-3/4"D
* Temperatures: White Wine Setting - 46-50°F; Red Wine Setting - 61-65°F
* Time to Proper Serving Temperature: White Wine - 1-1/2 hours; Red Wine - 3 hours
* Weight: 15 lbs.
* Ambient Temperature Range: 65-77°F
* Warranty: 1 Year Limited Warranty
* Applications: Free Standing Only


text: Despite knowing that it is a bit silly, I always set goals for myself that are way too ambitious. I exhaust myself trying to meet these targets, and then feel a sense of failure when I can't get there. And all the while, I KNOW that I am doing it… yet it doesn't stop me.

subset: commoncrawl


text:
Internet Marketing in the Philippines
August 15, 2008 by Stanley Dumanig · 2 Comments
Finally, after several weeks of brainstorming with my wife (and myself), I decided to:
1. Maintain only TWO blogs – this one and Information Marketing Central.
2. Write a book on the topic 'Internet Marketing in the Philippines.'
Maintaining Blogs
It's a pain in the neck maintaining multiple blogs. Trust me. I have setup a LOT of blogs in hope that I will earn more money. I'm dead wrong. If you can't regularly post new content and give real meat to your audience then there's NO way you can monetize from your blog. Read more


text:
Today's feature contains photographs of autoworkers performing various tasks on three different assembly lines. The lead photo shows women in an International Harvester plant putting together instrument panels. It appears that as part of a dress code the female workers were required to wear old-fashioned captain's hats, probably to protect their hair while on the job.
An autoworker in the image below is using a torch on the roof of a Chrysler or De Soto Airflow unibody structure, possibly for filling an area of the surface with lead. At the same time, another worker is welding on the inside of the body on the passenger's side of the windshield opening.
You can view other assembly line photos here on The Old Motor. The images are courtesy of the Detroit Public Library.
- Workers below on a Willys-Overland six-cylinder engine assembly line about to install a crankshaft and flywheel into one of the Automakers six-cylinder engines in the mid-1920s.


text:
Kenya deploys army doctors as hospital strike deepens
Kenya deployed army doctors on Friday to the country's main teaching and referral hospital where the last remaining doctors joined a five-day strike that has crippled healthcare services around the country.
"Our doctors are already at the hospital to help needy cases," said military spokesman Paul Njuguna. "It is within our mandate and that is why we stepped in."
The strike for better pay has seen thousands of doctors, nurses and other healthcare workers walk off the job since Monday, leaving scenes of chaos as patients were stranded in hospital wards or forced to go to expensive private clinics.
As the Kenyatta National Hospital in Nairobi is a key referral hospital, some 300 doctors had remained on duty until Friday when they too joined the strike.
Patients requiring emergency care went unattended as they lay on stretchers at the hospital.
"We are looking for an ambulance to take the patient to a private hospital, he got in an accident and we rushed him here but there is no one to attend to him," said Jackson Mwangi, a relative of one of the patients.
"I am told there are doctors from the military but the queue is too long to get in and there is a lot of confusion."
Mary Mueni who had come to see her sister, a cancer patient, said that deploying the army was not a solution to the crisis.
"Let the government pay doctors for a permanent solution," she said.
Unions are demanding a 300-percent pay rise for doctors and 25- to 40-percent pay rises for nurses that they say was agreed in a 2013 collective bargaining agreement, but has yet to be implemented.
Several rounds of talks have collapsed and the main doctor's union has said that private hospitals would join the strike next Tuesday.
A judge has ordered union officials to appear in court next Tuesday, threatening them with jail for disobeying a court order to call off the strike.
Judge Hellen Wasiliwa said that if they did not show up she would order their arrest.


text:
The news might be grim when it comes to endangered species (especially mammals, an estimated 25 percent of which are headed for the extinction list) there are steps ordinary citizens can take - even if you don't happen to be a conservationist or biologist. While you might not be able to single-handedly save the animals, you can pitch in.
From the dangerous to the cuddly, mammals living on land and in water are facing the same fate. Like it or not, many of us share responsibility by not recycling those beer bottles or plastic soft-drink containers. Have you switched from plastic bags to recyclables yet? What are you waiting for?
An article by Kenneth Weiss in The Los Angeles Times details a survey compiled over five years by a whopping 1,700 researchers across 130 countries. They found that out of the 5,487 types of mammals around the world, about a quarter of them are disappearing. Topping the list of endangered species are marine mammals. Primates are next. And the extinction list goes on and on. But you might be thinking, "What can I do about that?"
Although it might seem a stretch to think your actions could have an impact on endangered species, it really isn't that far-fetched. And even though we would all like to make lofty contributions to save the animals, it's more realistic to focus on what we can do in our daily lives to help.
1) Put trash in its place: The first thing to do is to avoid using the ground as your personal trash can. Cheri Ikerd, president of OC Wildlife & Beach Tour Inc., an eco-tour company based in California, says not polluting is more important than you might realize. "Any trash on the ground here in Southern California ends up in the ocean. Other places it could be a lake, a stream, a river, etc. And, with the marine life or animal life we have here in the ocean, many animals mistake the trash for food and when they eat it, they can't properly digest it and they end up starving to death."
2) Recycle: "Plastic is terrible for our waterways and the environment and affects every living creature," Ikerd says. So be sure to bring reusable bags with you to the grocery store, Target and wherever else might be on your agenda. Ladies, buy bags that you can stow in your purses, and guys, leave bags in your car on the passenger side so you won't forget them.
3) Get mugged: For those of you working in an office, Ikerd's next suggestion is to launch a mug movement. Instead of using 10 Styrofoam cups a day, you'll keep the environment in better shape by consuming your beverage of choice in a mug, and encourage your co-workers to do the same. You can still stand around the water cooler sporting your own stylish mug.
4) Don't feed the animals: If you live in a rural or suburban area, you might be tempted to give food to wild animals that wander into your yard. Even though it might give you a warm feeling to think you're helping the animals, in fact you're doing the opposite. When animals become dependent upon humans, they can lose the ability to forage for themselves. By giving a raccoon your meatloaf leftovers, for instance, you're intruding on his habitat. Ikerd reminds people that wild animals need to be able to fend for themselves.
5) Think before you buy: Another step you can take is to educate yourself. Megan Mertsock, assistant director of education with the Dolphin Research Center in Florida says, "Being an educated consumer is another very important action that a person can take. Purchasing products that are environmentally friendly and supporting sustainable fisheries to prevent the overharvesting of fish is another great way to protect marine life." Keep in mind there's plenty you can do to help animals as they fight for their lives and you move through yours.


text:
What's a blog marathon without a rousing edition of everybody's favorite semi-fake advice column? (Rhetorical question.) That's right: it's time for a little… VIEWER MAIL!!
A reader from Carmichael, California writes:
Dear Conlan,
What's the deal with the Dewey Decimal System? In the internet age, don't you find it rather archaic and stinky?
My dear friend: I believe you may possess an unclear understanding of history, and thus are very stupid about some things. The Dewey Decimal System (DDS) ((Non-dentist)) is neither archaic nor phlembotic. In fact, it only stinks because it is in the internet age. Allow me to explain.
In 1948, Colonel John Dewey—philosopher, reformer, military strategist—suffered some blunt trauma to the head (not unlike the inventor of time travel) while pole vaulting. The prognosis was very negative, and the world waited with bated breath for news of his condition.
On November 3, 1948, doctors announced Dewey's full recovery. The Chicago Daily Tribune trumpeted the news with the headline "Dewey Defeats Truma" (read "Trauma").
President Truman celebrating Dewey's recovery. The celebration would be short-lived.
Indeed, it was this misspelling that instigated President Harry Truman's nationwide crackdown on proofreaders. The newly-emptied Japanese internment camps were soon brimming with the nation's proofreaders (and even some copyeditors!). The move was cheered by railroad barons (of which there were none left) and oil men like J.D. Power and Associates.
However, the Proofreaders' Union 452 had previously established an alliance with the United Librarians Guild. In protest of this injustice against their compatriots, the nation's librarians went on strike, fueling anti-Russian sentiment among the general public and bringing book lending to a standstill. (If you think this isn't making any sense, you must remember: people are dumb. So you might just be dumb.)
After an unsuccessful attempt to prosecute the librarians as communists, Truman finally caved to their demands. He freed the proofreaders and appointed a special committee to get the real commie bastards: Hollywood.
After all of this, Colonel John Dewey felt horrible for having caused such a fracas. As a way of paying back the community, he proposed a new library cataloging system using decimals for no apparent reason. This new system was embraced by librarians across the country, as they enjoyed nothing more than looking down their librarian noses at a library patron slamming a card catalog drawer in frustration.
And that, my friend, is why movie stars never have—and never will—go to the library.


text:
Some leftovers from Dinner: A mushroom and a piece of zucchini stuffed with risotto, tamagoyaki, homegrown rucola, radish and an edible flower. The bottle holds vinegar for the salad.
Bento #349
June 29, 2011 by wererabbits
3 Responses
You make leftovers look absolutely stunning! And I love that tamagoyaki–beautiful.
on July 31, 2011 at 9:59 am | carla
what a beautiful summer bento! I love the "Kapuzinerkresse" flower, they taste so spicy. You should try mexican mini cucumbers on your windowsill, I have them everyyear, they have beautiful little flowers and a lot of cucumbers!
Homegrown Sounds Great…but ithink its too late now…Next Year!


text:
RENOLIT ALKORPLAN TOUCH is the first reinforced membrane 3D to line swimming pools Inspired by nature.
This reinforced membrane to line swimming pools has a thickness of 2 mm. It is the strongest and most resistant on the market and has a natural finish. TOUCH membranes have exclusive prints and a 3D embossing that makes them unique, tactile, and visually exciting.
Discover this fantastic pool liner
Follow us!!
If you are looking for more information on all the advantages of the reinforced membrane as a swimming pool liner or you are looking for an installer in your area to coat your new pool or rehabilitate the one you already have, please contact us using this form.


text:
"Dis-Lodged" is the fourteenth episode of Season Two. Shawn and Gus discover there's more to a local brotherhood than funny hats and handshakes when their incoming leader is found dead after a secret ceremony at the lodge.
Plot SummaryEdit
Shawn tells Gus that Henry is sick. He calls to cancel their weekend camping trip in the mountains and Shawn couldn't be more thrilled, until he catches his dad driving through town, the picture of health. Now Shawn has to know why he was ditched, and he and Gus follow Henry to the Monarch Lodge, where they discover Henry is a member. After Shawn perfectly repeats the secret handshake, they enter the lodge to find Henry in a room full of robed lodge members, surrounding a dead body on the floor. The brothers have called Henry in for help, but he insists he's retired both from the force and the lodge and calls the police. Gus, meanwhile, realizes he's in a room full of doctors, and gingerly steps around the body to start networking. Shawn tells Henry that the deceased, Arthur Holmstrom, was murdered, possibly by poison, and no one in the room seems to be upset about his death. Henry's friend Ted Archibald, who called them in, is worried they did something wrong during Arthur's Patriarch ceremony. He had a ceremonial hood on for twenty minutes, and is worried Arthur might have suffocated. Lassiter and Juliet arrive at the scene. It turns out Lassiter has always wanted to be a member but has been blackballed by his soon to be former father-in-law, Irving Parker. Now Lassiter is determined to solve the case quickly to impress Irving and the rest of the lodge members. Meanwhile, Gus has been busy locking down an appointment with Dr. Downs, who has a large practice and is a big purchaser of pharmaceuticals.
Back at the Psych office, Shawn and Gus are researching the lodge when Lassiter shows up and asks for their help. He knows they have an in, and he wants them to share any information they get. They agree to help Lassiter, who warns them to keep a low profile. Of course the next day, Shawn is inducted as a member of the lodge. Now Shawn and Gus are able to move about freely and they discover that Holmstrom was being inducted as the Highmost Patriarch of the lodge, skipping the three other people who ranked above him for the spot, including Irving Parker, who, in the wake of Holmstrom's death, is the new Highmost Patriarch. They also notice all the plaques from various charities that the lodge has received for generous contributions over the years. Shawn also sees the hood Holmstrom was wearing when he died, displayed in a glass case. It's over 100 years old. But as he turns to comment to Gus, Gus is back schmoozing Dr. Downs and a few of his colleagues. So Shawn checks in with Lassiter who gets a call from Juliet. She tells them Holmstrom's tox screen came back showing a toxin resembling the venom of a Brazilian vine snake, but there were no marks or puncture wounds on the body, just a slight skin irritation on his face.
Shawn and Gus go to visit their top suspect, the new Highmost Patriarch, Irving Parker at his Export/Import Company. In the warehouse, Shawn notices shipping codes on crates from several Brazilian airports. Gus has appointments with a bunch of new clients, so Shawn is forced to tell Lassiter alone that all their evidence implicates Parker. Lassiter agrees to interview Parker but tells Shawn there is no way he's a killer and to keep looking. Shawn and Gus decide to take a different tack and investigate Holmstrom. They sneak into his house but as they are looking through his office, they hear someone else breaking in. They hide as the intruder rifles through Holmstrom's things and leaves. When they come out, they see the intruder uncovered a hidden safe but couldn't get in it. From the many pictures of Holmstrom's late wife throughout the office, they figure out the combination is her measurements and discover two lodge ledgers showing differing amounts donated to charity. It seems like Holmstrom was skimming funds from the lodge but Gus notices everything is written in doctor's shorthand and in doctor's handwriting. One of the doctors was stealing and cooking the books. Holmstrom found out about it and was blackmailing the culprit not only for money, but for the Highmost Patriarch position as well.
Back at the police station, Lassiter finally gets Parker to talk, telling him that he has no idea how Holmstrom got to be Highmost Patriarch but that he was okay with it because Arthur was a friend who had health problems which were getting worse. This would be his only shot at it. Plus, he hasn't been out of the country in years and so wouldn't have been able to bring back a snake from Brazil.
Shawn and Gus are visiting all the doctors' offices from the lodge to compare handwriting. They arrive at Dr. Downs' office and see pictures on the wall from the third world countries to which the doctor has ministered. As Gus distracts the receptionist, Shawn notices an article on powdered forms of medicine and sees a sample of Downs' handwriting. It's a match. They call Lassiter and Juliet to meet them at the lodge where Shawn explains that Downs killed Holmstrom with a powdered form of the snake venom that he put in the ceremonial hood, the venom being part of an anti-venom that Downs uses in his charitable work abroad. Shawn has solved the case but declares he never would have been able to without Lassiter's help, putting the detective in the good graces of Parker and the rest of the lodge members, rescinding his blackball status.[1]
- The title is a play on words, evoking the organizational structure of the Freemasons, and similar groups, who term their largely independent local units 'Lodges'. Therefore, being kicked out, or otherwise removed would be to be 'dis-Lodged'.
- The song Henry is listening to in his truck is "Bad, Bad Leroy Brown" by Jim Croce.
- Steve Franks' portrait is hanging to the right of Henry's in the lodge, amongst writers and producers of the series.
- Shawn mentions that one of the portraits in the lodge lobby looks like "Chuck from The Wonder Years." The portrait is of writer Andy Berman, who actually played Chuck on that show.
- Gus' assessment of the hourglass figure in the photo may be a nod to The A-Team, in an episode of which Face used binoculars to ascertain from a distance the vital statistics of the villain's girlfriend, and thus the combination to his safe.
- Isaac Asimov's book of essays, The Roving Mind, appears beside Shawn in the Psych office.
Shawn: I'd like to show you something.
[holds his left hand at eye level]
Shawn: See that? It's the lodge radar. And look at this.
[flaps his right hand underneath his left]
Shawn: It's me, flying underneath it. CAW CAW!
[walks his fingers across the table]
Shawn: Now look down here. It's Gus. He can't fly.
Shawn: [to Lassiter, about Lassiter's father-in-law] Look, he's the only suspect we have. He tried to hate me to death.
The gallery for Dis-Lodged can be found here.


text:
Sunday, February 21, 2010
We took our first of many trips to Disneyland and California Adventure.
One of Elliott"s favorite rides.
This is how Spencer spent most of the trip.
Posted by
No comments:
Email This
Share to Twitter
Share to Facebook
Share to Pinterest
Fight your own battles
Everyone always says they don't want to end up like their parents. And then, at some point in our parenting career, we do something that our parents did.
There is an
Oh Shit
I had that moment this morning. My mother was, and continues to be stoic with her feelings. She didn't express a lot of empathy or sympathy towards our childhood pleas. She despised whining. She always told us to fight our own battles. I understand that she was trying to encourage my younger sister and I to become independent and strong women. I get it. But, sometimes we needed the coddling. We needed the support of our role model, our Mom--we were little kids after all. We needed to be shown how to respond, how to behave, how to fight back.
So, I have found myself lately acting in the same manner towards my toddler. He is capable of doing simple tasks by himself-putting his shoes on, taking off his clothes, climbing up into his car seat. But, he refuses and gives a fake attempt and then says-Nope, I cant. Then he whines for me to help him. Knowing that he can do things on his own, I have been strongly encouraging him to do it himself. He will then cry about how he cant do it and whine more. The cycle of whining and crying will escalate the longer I insist he do it himself. I reach my wits end, and end up yelling, he cries because I have hurt his feelings and he thinks I am mad at him (rather then frustrated at the situation). We end up in a no-win situation.
I realized that I was not being empathetic to my son. I was looking at the problem and only wanting to move towards the solution; without allowing myself to feel for him and he plight. I wasn't allowing myself to see that he was really upset over me
helping him. I began to wonder if he was upset for the same reasons I was as a child? Did he feel abandoned by my refusal to help him? Am I do more harm than good by not helping him?
So, today I am slowing down. I am taking it easy on him and myself. I am lowering my expectations. I am choosing not to yell, and instead I am trying to look deeper into him and look at the reasons he is refusing. I want my boys to trust me and know that I will always be there for them. My love is unconditional. Yes, they will have to fight their own battles still, but I hope to be able to provide them with the tools they need to succeed.
Posted by
No comments:
Email This
Share to Twitter
Share to Facebook
Share to Pinterest
what was I thinking
Newer Posts
Older Posts
Subscribe to:
Posts (Atom)


text:
Create an Account - Increase your productivity, customize your experience, and engage in information you care about.
The pool at Roby George Park will reopen just in time for the first official Saturday of the summer.
If you're looking for a way to beat the heat, you can hit the water again at 10 a.m. on June 23.
The pool, located at 201 SW 11 Avenue, was closed for repairs. Homestead residents can swim for free, and anyone who lives outside of City limits will be charged $2.
Weekend hours are 10 a.m. to 6 p.m.


text:
We all know what pain is, but what is posture?
Posture is the position of the body for a purpose. This means your body alignment during activities like sitting, standing, walking or running. To understand what good posture is, it is first necessary to understand that "good posture" really should mean "efficient posture" to promote optimal function. This means your musculoskeletal alignment should help you achieve a desired result with the least amount of wasted energy.
For example, an increased or decreased curvature in the low back deactivates the core muscles needed to support the spine against gravity. The lack of core stability means a loss of adequate muscle firing for shock absorption and load distribution, making maintaining optimal postural alignment impossible. When this happens, increased forces can cause stress to be transmitted to the back, which can then lead to pain.
If you are sitting in a chair that has little or no back support, the lack of optimal alignment can lead to low back pain or cause a strain up the spine into the neck muscles and even lead to headaches. If you are sitting in a chair that is too high or too low, the stress and forces from those positions can cause excess muscle activity, fatigue and can even lead to elbow, wrist, or shoulder pain due to muscles that are overly lengthened or shortened.
In an athlete, if posture is in poor alignment, muscles are inhibited and cannot fire efficiently or sustain load without breakdown. This can quickly lead to tendonitis or painful muscle spasms. In sports with repetitive motion sports like swimming, cycling, or running, postural dysfunction can quickly reveal itself as increased fatigue or reduced speed and endurance, with injury not far behind.
So what can you do about your posture and how do you know if it is not ideal?
A Functional Manual Physical Therapist has specific training to identify and treat postural alignment and dysfunction. This can be as simple as assessing your sitting position at your desk, or as complex as analyzing your running gait to point out asymmetries and problematic traits.
By identifying postural deficits, we can assess for areas of weakness and inhibited muscles, or make you aware of ways in which your body is moving abnormally. More importantly we can manually treat soft tissue and joint restrictions to improve postural alignment and increase efficient movement.
A Functional Manual Therapist can also help you by making recommendations for chair or desk height, or teaching you how to eliminate specifics traits in your standing, running or other sports that will eventually lead to breakdown and pain. By assessing an individual's posture we can predict how the body will respond to any activity and make you aware of how to correct it. Remember, if postural alignment is not "good", meaning not "efficient", it always impacts function.
A functional evaluation and treatment by a Manual Physical Therapist can help diagnose and treat poor posture, decrease pain, reduce risk of injury, and improve performance.


text:
Secure Authentication for APIs
Game-changing API Security | Use Hybrid Cloud with Confidence
Corsha's Streaming Authentication is a new paradigm in API Security.
By 2020, 90% of enterprises will use hybrid cloud infrastructure, splitting workloads between public clouds and private networks. These workloads communicate primarily through APIs. Enterprises are exposing an increasing number of services through APIs, both internally and to vendors, partners, and customers. Experts warn that the next wave of network attacks will target APIs. In fact, compromise of API credentials is now a 'Top 10' cybersecurity concern.
Effective API security is critical and it must fit with today's modern continuous deployment models. By adopting Corsha's Streaming Authentication solution, enterprises are empowered to push workloads to the public cloud with confidence, thereby increasing ROI for hybrid cloud infrastructure.
Learn More
© 2020 · Corsha


text:
Throughout my life, I held this very deep belief that I was never good enough. This belief has held me back from a lot of things I would have liked to do or achieve. For example, I never played any team sports in fear of being not good enough. I didn't want to let down my fellow team members, so I just wouldn't play. To this day, I still don't take part in any team sports. Sadly because this belief still holds me back.
Over the past few years, I've become a firm believer in affirmations. By using affirmations, I put a positive message out to the universe and trust that what I ask, I shall receive. One affirmation that I started to use was "I am good enough".
The affirmation itself required some truth behind it for it to work. So I had to start thinking of reasons WHY I WAS good enough. The 10 reasons that I came up with are pretty relatable, so I figured, why not share….
- Being good enough at something entails practicing it. So get practicing and you'll be sure to get good, probably even awesome!
- You are stronger than you think you are. If you really want to do something, then do it.
- You are smarter than you think you are. If you really want to try something, then try it.
- Failures are lessons. The more you fail, the more you learn. The more you learn, the better you become. Failing lots = becoming good enough (or even awesome!)
- No one really knows what they are doing in the beginning, start now and you'll be great!
- Growth comes from trying (and failing – see point #4).
- Confidence comes from experience. So get out there and start now!
- Passion comes from your gut instinct. If it feels right then go for it!
- If you weren't good enough, you wouldn't have gotten this far already.
- Because everyone else is telling you that you are good enough ~ so start BELIEVING it!
Anytime I doubt myself, I say my affirmation and think of all the reasons I am good enough. This boosts my confidence and creates positive results. For example, I do a good job of something at work, I get better grades in my courses, I achieve something that I thought I couldn't do.
So start BELIEVING in yourself!


text:
|System and methods for performing multi-source searches over heterogeneous databases
|Carhart et al.
|System and method for dynamically displaying entering, and updating data from a database
|System for composing a graphical interface to a relational database which displays a network of query and source icons
|System for interactive clause window construction of SQL queries
|Li et al.
|Automatic and transparent denormalization support, wherein denormalization is achieved through appending of fields to base relations of a normalized database
|Conley et al.
|Indexing/compression scheme for supporting graphics and data selection
|View composition in a data base management system
|Shan et al.
|Interactive, cross-referenced knowledge system
|Farley et al.
|Database retrieval system having a natural language interface
|Shwartz et al.
|Computer system for defining logical operations on design data including retrieve entity-set, send, receive, signal, when, reference to entity-set, reference to entity method, connect and disconnect
|Bachman et al.
|Complied objective referential constraints in a relational database having dual chain relationship descriptors linked in data record tables
|Crus et al.
|Method and apparatus for processing data base
|Yamamoto et al.
specifying at least one logical relationship by:
selecting a pair of tables having a common field from among said plurality of relational database tables;
selecting the common field of said pair of tables as a join field; and
selecting a join operator for said join field to define a join between said pair of tables,
the logical relationship being one of One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More; and
creating a logical schema defining a multilevel hierarchy of the tables in the specified logical relationships, wherein a first table and a second table of a pair of tables in a logical relationship are at an equal hierarchy level if the logical relationship is one of One-to-One and One-to-Zero-or-One and the first table is at a higher hierarchy level than the second table if the logical relationship is one of One-to-Zero-or-More One-to-One-or-More.
displaying fields of tables in the logical schema multilevel hierarchy, the fields being arranged by hierarchy levels of the corresponding tables;
receiving input for a value of at least one of the displayed fields; and
querying said relational database tables based on the received input and the logical relationships included in the logical schema.
a memory for storing a logical schema for providing an interface for a user to access data stored in a plurality of relational database tables, each of said relational database tables having at least one field, wherein the logical schema comprises a plurality of logical schema tables, each corresponding to a relational database table and participating in at least one logical relationship, each logical relationship comprising a pair of logical schema tables, a loin field common to both of the logical schema tables in the pair, and a loin operator for joining the pair of logical schema tables based on the join operator, the logical relationship being one of One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More, the logical schema defining a multilevel hierarchy of the logical schema tables wherein a first table and a second table of a pair of tables in a logical relationship are at an equal hierarchy level if the logical relationship is one of One-to-One and One-to-Zero-or-One and the first table is at a higher hierarchy level than the second table if the logical relationship is one of the One-to-Zero-or-More and One-to-One-or-More; and
a display for displaying fields of the logical schema tables.
providing a logical schema for at least two logical schema tables, each logical schema table corresponding to a relational database table, wherein the logical schema is defined by pairwise logical relationships between the logical schema tables, each logical schema table participating in at least one logical relationship, wherein a logical relationship is defined by a join operator on a join field common to both logical tables in the pair, and a logical relationship is one of One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More; and
establishing a multilevel hierarchy among the logical schema tables, wherein a first table and a second table of the pair of tables in each logical relationship are at an equal hierarchy level if the logical relationship is one of One-to-One and One-to-Zero-or-One, and the first table is at a higher hierarchy level than the second table if the logical relationship is one of One-to-Zero-or-More and One-to-One-or-More.
receiving input for a value of at least one of the displayed fields;
querying the relational database tables for data satisfying the input and the joins defining the logical relationships in the logical schema; and
displaying data resulting from the query.
This invention relates to computerized databases, and more particularly to a system and method of retrieving data in a relational database using a graphical user interface.
A database system is a computerized system whose overall purpose is to maintain information and to make that information available on demand. Many of the database products developed today are based on what is called the relational approach. In a conventional relational system the data is perceived by the user as tables. A table is comprised of a row of column headings, and zero or more rows of data values. The rows of the table may be referred to as records, while each column represents a field of those records.
All access to the database is handled by a database management system (DBMS). Using the DBMS, users of application programs perform operations on the database such as storing, editing, updating, inserting, deleting, and retrieving data by issuing requests or commands called queries. The database language known as SQL (structured query language) is the standard language for interacting with database systems, and is utilized by most commercial database systems.
From a high level point of view, database systems have a two part structure consisting of the DBMS and the interface to the DBMS. Interfaces are various applications that enable users to control the DBMS.
Database products are used by a wide variety of entities; large companies, for instance, store large amounts of information in corporate databases. In general, there are two types of employees that may use the corporate database.
A person known as a database administrator (DBA) or system programmer is required to create, maintain and implement the database. For large entities, the DBA function may be performed by a team of individuals. However, for simplicity the DBA is considered to be a single individual in the following description.
Besides the DBA, the second type of user to interact with the database system is the end user. End users can access the database via a separate application program, or through an interface provided as part of the database system software.
For more background on relational databases and the SQL language, see Date C.J., An Introduction to Database Systems; The Systems Programming Series, vol. 1, 5th Ed., Addison-Wesley Publishing Company, 1990, which is herein incorporated by reference.
Several problems have been associated with conventional relational database management systems. In some prior approaches, end users mainly access data in the database through the use of a database query language, such as SQL. The problem with these command driven interfaces is that they require a certain amount of data processing expertise on the part of the end user.
Other types of relational databases, or applications, include a graphical user interface (GUI) that allow users to interact with items that appear on the computer screen, rather than requiring users to memorize the names of commands. Using a GUI, end users interact with the database by choosing an item from a menu, filling in information on a form, or interacting with a dialog box. A dialog box is a type of window, invoked by a command, that accepts responses needed to carry out a particular command.
Despite the improvement over command line interfaces, GUIs suffer a drawback in that end users are still required to have knowledge of the physical structure of the database. In other words, the end user must know the names of, and relations between database tables and columns before being able to access data in the database.
The way in which a database is structured, i.e., the relationships between tables and columns, is known as the physical schema of the database. The physical schema should be designed so that all user applications, which retrieve and manipulate data, may do so in a convenient manner. The convenience provided by such a database design, however, diminishes over time as the database matures, since as tables and columns are added, deleted and modified, the original physical schema may no longer provide efficient access to information. The data may still be obtained, but the user may have to retrieve the data by joining, i.e., jumping from table to table. This joining process requires additional steps due to the fact that the database schema was not initially designed to manipulate the new relations among the tables. Thus, schema navigation becomes more difficult as the system matures and more applications are added.
The physical database schema cannot be restructured from year to year in order to solve this problem. This is especially true of a real-time production databases supporting day-to-day operations, as companies are generally reluctant to halt a production database and replace it with a new application that may contain bugs. As a result, the database schema becomes more and more complicated over time, and eventually only a few system programmers in the company may have the necessary knowledge to perform database operations. End users must therefore send database queries to these programmers, and as they become busier, a backlog of requests may be accumulated.
GUIs that display a list of table and column names for the end user do not solve this problem. Most end users are not sophisticated database users and need to view data in familiar formats. For example, an end user may prefer to look at information in a format such as an invoice or purchaser order. The number of tables in a relational database and the relationships between those tables may seem arbitrary and confusing to end users. The challenge is then to provide an end user access to the data he wants without having to understand the physical database schema.
Accordingly, an object of the present invention is to provide improved access to relational databases.
A further object of the invention is to provide improved access for an end user without that user understanding the physical database schema.
Other objects, features and advantages of the present invention will become apparent from the following description when taken in conjunction with the accompanying drawings.
The present invention is a method for providing access to a relational database having data stored as physical database records in physical database tables. Pairs of tables which will comprise a logical schema are selected from the relational database and the logical relationships between the pairs of tables are defined. The tables in each pair of tables are joined by selecting a field common to both tables. Once a join operator is selected for each join field, the logical relationship between the pairs of tables is stored in a relational database thereby creating a logical schema.
The present invention provides access to a relational database where an end user queries the database without knowledge or understanding of the physical database schema. A logical schema is created and displayed to an end user as a set of fields grouped by levels that denote logical relationships between the information.
An end user selects from a display those fields for which he wishes to see information. The database tables and columns corresponding to fields selected by the end user are then queried. The relationships between the data defined in the logical schema controls the order in which data is returned and presented to the end user.
Because actual table and column names are not used in the logical schema, the present invention masks the complexity of the physical database schema from the end user.
The accompanying drawings, which are incorporated in and form a part of this specification, schematically an illustrate embodiment of the invention and, together with the general description given above and the detailed description given below, serve to explain the principles of the invention:
FIG. 1 is a block diagram of a prior art database approach.
FIG. 2 is a block diagram illustrating the present invention.
FIG. 3 provides a graphical representation of the structure of logical schema in accordance with the principles of the present invention.
FIG. 4A is a block diagram representing how a logical schema is stored.
FIG. 4B depicts the "Available Tables" list and the "Selected Tables" list for a sample customer invoice database.
FIGS. 5-10 depict logical schema catalog tables.
FIG. 11 depicts a row of information inserted into the SYSSUPERVIEWS table for the "invoice" logical schema.
FIG. 12A depicts the SYSSVWTABLES table after rows of data have been inserted.
FIG. 12B depicts the tables and columns in a sample database.
FIG. 13 shows the SYSSVWALIASES table after being updated with table information.
FIG. 14 depicts the relationship definition for an "invoice" logical schema.
FIG. 15 shows the SYSVWTABLES table after being updated with relationship information.
FIG. 16 depicts the SYSSVWJOINS table after being inserted with join information from the a sample schema.
FIG. 17 shows the SYSSVWORDER table after a sort order has been defined for an "invoice" logical schema.
FIG. 18 depicts a sample dialog box for a logical schema.
FIG. 19 is a form produced from a sample Customer logical schema.
FIG. 20 depicts a sample form after records have been retrieved from the database.
Reference will now be made in detail to a preferred embodiment of the invention. While the invention will be described in conjunction with a preferred embodiment, it will be understood that this description is not intended to limit the invention to that embodiment. On the contrary, the invention is intended to cover alternatives, modifications and equivalents, which may be included within the spirit and scope of the invention.
A conventional database is shown in FIG. 1. This database requires an end user to know the physical database schema, i.e., table and column names and relationships, before retrieving and viewing data contained in the database. An end user, denoted generally by reference numeral 8, is a person who queries the database to obtain specific information. In order to perform the query, he must first know a physical schema 14 of the database. A database management system (DBMS) 16 executes the user's query to the database (not shown) and sends the results to a report generator 18. Report generator 18 is an application program capable of building forms and reports. A form provides end user 8 with a window to the database, while a report is a document that allows end user 8 to format and print data retrieved from the database. Forms are usually sent to a computer screen 20 while reports are usually sent to a printer 22.
Each end user 8, of course, may desire to see different views of the data. The different ways an end user 8 may view the data is depicted in FIG. 1 as a set of boxes 10 between end user 8 and physical schema 14, each box 10 representing a different view.
Different end user views 10 can be illustrated by examining a hypothetical sound recording company. Assume the database administrator for the company produces a monthly report that displays sales of the company's compact discs grouped by artist. Assume further that a sales manager in the company needs to see the sales of only two artists over a six-month period. Although the sales manager's report queries the same tables, the DBA must generate a separate report to retrieve the specific information required by the sales manager. The two reports produced by the DBA represent separate end user views of the data stored in the company's database. The person who generates the reports must know the physical schema of the database to issue the query.
The operations and features of the present invention are illustrated by FIG. 2. As shown, end user 8 queries the database within the DBMs 16 through a logical schema 12. The logical schema 12 eliminates the requirement that end user 8 know the physical schema 14 before accessing data. Logical schema 12 is a set of instructions that defines the relationships between selected tables and column data in a relational database. In other words, logical schema 12 provides a set of rules to control how data is returned from the database.
The present invention is a method for providing access to a relational database which has data stored as physical database records in physical database tables. Pairs of tables comprising a logical schema are selected from the relational database and the logical relationships between the pairs of tables are defined. Each pair of tables is joined by selecting a field common to both tables. Once a join operator is selected for each join field, the logical relationship between the pairs of tables is stored in a relational database, creating a logical schema.
For example, a logical schema might model a purchase order or a sales invoice by specifying the invoice relationships between customers, orders, and sales items in the database.
Using the present invention, end user 8 can use a graphical user interface (GUI) to logical schema 12 to design custom forms and reports without knowledge of the database structure or actual table and column names. The end user 8 begins by specifying the database and logical schema 12 on which a document is to be based. End user 8 then selects the fields or columns he wants from a dialog box (not shown) that displays all the columns that are available in logical schema 12.
The dialog box groups columns according to a logical schema level, as explained in more detail below. In this approach, end user 8 does not need to know which table holds a specific item of data. He only needs to know which logical schema level contains the desired information. For example, to place customer information on a form, end user 8 simply selects displayed fields grouped under a heading named customer-- info.
Instead of dealing with rows in individual tables, an end user deals with a logical schema record. A logical schema record consists of one master-level row and a number of dependent detail rows. For instance, a logical schema record could include all the sales for one customer and all the items for each of those sales. This may encompass many rows in several tables, but is presented to end user 8 as a single and simple logical schema record.
Before end user 8 can make use of logical schema 12, the schema must first be created. A person with knowledge of the physical schema, including a person who later becomes end user 8, can create a logical schema 12. Since in most large companies the task of creating logical schema 12 would be the responsibility of the DBA, the person who creates the logical schema 12 will, for the sake of convenience and not by the way of limitation, may hereinafter be referred to as the DBA.
When designing logical schema 12, the first task is to choose the tables an end user 8 needs to access. It must then be determined how the data in one table relates to data in another table by establishing the relationships between those tables. When the end user 8 sends queries to the database, the relationships established in logical schema 12 control the master-detail order of the data returned in response to those queries. This allows logical schema 12 to portray data having different levels of detail.
Before explaining what is meant by a master-detail order, it is necessary to first describe the relationships between database tables. In a database designed to follow the relational model, each row of data in a table has a number of potential relationships to the other rows of data. In other words, in addition to the individual entities recorded in the database, there are also relationships linking those entities together.
For example, a given database might hold information as to which customers ordered what sale items from which stores. A relationship exists between customers and stores: each store has customers that placed orders, and conversely, a customer may have placed orders in more than one store. Similarly, a sales item may be stocked in several stores, and conversely, a store may stock several sales items. These relationships may be traversed in either direction.
Relationships between entities, or fields, of a database are a part of the database just as the basic data entities are part the database. A relationship can be regarded as an entity of its own. For example, the relationship "sales item X is stocked in store Y" is an entity for which one may wish to record data, i.e. the corresponding quantity.
A logical schema is a named set of these types of relationships and provides a set of rules to control how data is returned from the database. The types of relationships between tables that can be defined in a logical schema are: One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More.
A One-to-One relationship means that for every row in one table, there is only one row of related data in a second table. For example, a part may have only have one manufacturer.
A One-to-Zero-or-One relationship means that for every row of data in one table, there is no more than one row of related data in a second table or there may not be any related rows in the second table. For example, some but not all of the items in a catalog may have a corresponding picture.
A One-to-Zero-or-More relationship means that for every row of data in one table, there may or may not be multiple rows of related data in a second table. For example, some customers may have placed many orders, but there may be customers who have never placed orders.
A One-to-One-or-More relationship means that for every row of data in one table, there is one or more rows of related data in a second table. For example, every order must have at least one item on it, and an order can have many items.
The relationship between tables can vary depending on the purpose of the logical schema. For example, if a logical schema is created that looks at all customers and their orders, then the table holding the customer information has a One-to-Zero-or-More relationship to the table holding the order information. However, if a logical schema looks at all the orders a company fills and the customers that placed the orders, the order table has a One-to-One relationship to the customer table.
After data has been modeled in the database by defining logical schema 12, end user 8 can use that model to create any form, report, or query that looks at the data in the same logical way. In the sound recording company example given above, a logical schema could be created that displays the monthly sales of compact discs grouped by artist. Although the sales manager may need a specialized view of the data, the logical schema would enable the sales manager to personally choose information he needs without the help of a DBA, for instance, and his specialized knowledge of the physical database schema.
The number of logical schemas to be created for a database is determined by the requirements of the end user. For example, only one logical schema for each department in a company may suffice, but if end users within the same department require different types of information, then additional logical schemas can be created to satisfy those needs.
Logical Schema Structure
As shown in FIG. 3, a sample logical schema may include five tables denoted collectively by reference numerical 32: a customer table 132, a store table 133, a sales-- header table 136, a sale-- items table 137, and a parts table 138.
In relational databases, tables are joined by one or more columns that contain the same values. For example, customer table 132 and sales-- header table 136 can be joined by a column named cust-- no. The value of this column in both tables must match to relate the rows from the two tables.
The table joins are specified in a logical schema of the present invention when the relationships among the tables is defined. Since the joins may be previously defined, all end users 8 need to understand when building forms or reports is the logical levels 28 into which the data is organized. For example, they must know that for every customer there may be 0 or more sales, and each sale consists of 1 or more items.
The first level in a logical schema is known as the master level 29. Master level 29 contains a logical schema master table 30. All other tables 32 in the logical schema join directly or indirectly to the master table 30. In the example, the logical schema organizes all the data according to its relationships to rows in customer table 132. Therefore, customer table 132 is the logical schema master table 30.
A new logical schema level is created each time a One-to-Zero-or-More or One-to-One-or-More relationship between tables is established. Any new logical schema level under master level 29 is called a detail level. The levels are ordered according to how the tables in the level are joined to master table 30.
In the example of FIG. 3, two detail levels exists, a first detail level 34 and a second detail level 35. Each row in the customer table. 132 may have zero or more corresponding rows in sales-- header table 136. If a customer has not made any purchases then that customer would not have any rows in the sales-- header table 132. Therefore, this One-to-Zero-or-More relationship between tables 132 and 136 spawns a first detail level 34. Likewise, each row in sales-- header table 136 may have one or more corresponding rows in sales-- items table 137 (a One-to-One-or-More relationship), so it spawns a second detail level 35.
Tables joined on a One-to-One or One-to-Zero-or-One relationship appear on the same logical schema level. In FIG. 3, one row in customer table 132 relates to one row in store table 133, i.e., a One-to-One relationship exists between tables 132 and 133. Therefore, store table 133 belongs in the same level as customer table 132. Likewise, parts table 138 has a One-to-One relationship to sales items table 137 so it belongs in the same level as sales items table 137.
In a preferred embodiment, logical schema levels are automatically named by default. Each level may be given the name of the first detail table 36 assigned to that level. The exception to this is master level 29 of the logical schema which assumes the name of master table 30. In a preferred embodiment, logical schema levels 28 may be renamed to make them more meaningful to the end users. In FIG. 3, customer table 132 is the first table assigned to the logical schema master level 29, so the name of the master level of the logical schema is "customer level" 129. The sales header table 136 is the first, and only, table assigned to the first detail level 34, so the name of the first detail level 34 of the logical schema is "sales header level" 134. The Sales-- items table 137 is the first table assigned to the second detail level 35, so the name of the second detail level 35 of the logical schema is "sales items level" 135.
Logical Schema Creation
To create a logical schema, the structure and contents of the database must be known. In a preferred embodiment of this invention, each of the following tasks must be performed when building a logical schema:
1) open the database for which the logical schema is to be built,
2) name the logical schema,
3) select the tables to be included in the logical schema,
4) specify the relationships between the rows of data in the various tables that comprise the logical schema, and
5) specify the joins between the columns of the selected tables.
The following tasks are optional when creating a logical schema and may be performed in any order:
1) select columns to be included in the logical schema from the set of chosen tables,
2) arrange how the columns are to appear to end users when building forms and reports,
3) create aliases for tables or columns,
4) establish a default sort order for the data retrieved into the forms or reports based on the logical schema, and
5) determine which users will be able to use the logical schema.
In the preferred embodiment, each stage in the creation process is accomplished by entering information in, or making selections from, a series of dialog boxes displayed by a GUI.
Logical Schema Storage
Once a logical schema is created, it may be stored in either the same database from which the tables defining the logical schema were stored or in a separate database. FIG. 4A is a block diagram representing how a logical schema is stored. Usually a database includes two type of tables, user tables 38 and system catalog tables 40. User tables 38 store information sought by the end user, while system catalog tables 40 contain information concerning objects of interest to the database system itself. A logical schema is stored in logical schema tables 42, shown by the dashed rectangles, as a type of system catalog table 40.
Logical Schema Creation Stages
This section describes in greater detail the stages required to create a logical schema. As described earlier, a logical schema is created and subsequently used by an end user through a GUI. The implementation of a GUI and manipulation of a database through a software controlled DBMS are well known in the art and will not, therefore, be described in detail. However, both the logical schema creation process and the operation of a logical schema by an end user are explained in detail using the customer and sales invoice example described above and depicted in FIG. 3. The stages taken to create this logical schema are described in sequence below.
At this stage, the database for which a logical schema is to be created is opened and then the new schema is named. Logical schemas are created for a currently open database. Tables included within a logical schema can be tables in the current database or SQL views as well as synonyms to tables in remote databases. A SQL view is an image of database tables formed by a query and then saved. In a preferred embodiment, remarks or a description of the logical schema may be entered in the same dialog box in which the logical schema is created for future reference.
Next, a list called "Available Tables" is established. The list includes the names of all tables, views and synonyms (a name used to reference another database) in the current database. A table may be added to the logical schema by selecting and moving (i.e., using a mouse) the table from the Available Tables list to a list called "Selected Tables".
FIG. 4B depicts the "Available Tables" list 200 and the "Selected Tables" list 202 for the sample customer invoice database. All tables in the database are displayed in the available tables list 200 and prefixed with the login name of the person who created the table. In the example shown, all the tables have an owner named, "Ifmx". For this example, it is assumed that customer table 132, store table 133, sales-- header table 136, and sales-- item table 137 are selected from the available tables list 200. The chosen tables are displayed in selected tables list 202 and are included in the logical schema. The name of the logical schema, "invoice", has been entered in the Logical Schema Name box 139.
Once tables for the logical schema are selected, and they appear in the selected tables list 202, an alias, label, and title for each table may be specified. An alias is a secondary name that is recognized by the DBMS. If an alias for a table is not specified, the logical schema uses the original table name as the alias. The reason a table name is given an alias is to make the name more meaningful to an end user who will later create forms and reports using the logical schema.
In a preferred embodiment, aliases must be valid SQL identifiers. They must be unique in the logical schema and can only contain alphabetical characters. If a table is selected twice for a logical schema, a routine is called to automatically rename or alias the table. In a preferred embodiment, this is done by appending an underscore character to the name. For example, "employee" table is selected twice, the second occurrence of the table is renamed as "employee-- ".
The first table inserted into the selected tables list 202 is designated by default as the logical schema master table. This designation may be changed if desired. In FIGS. 3 and 4B, the customer table 132 is the master table 30.
A command, such as "Create Logical Schema", may be entered to store the above-mentioned information in logical schema tables 42 of FIG. 4A. Referring back to FIGS. 2 and 4A, logical schema tables 42 are used both by DBMS 16 when logical schema 12 is created, and when end user 8 uses logical schema 12 to create a form or report. During creation of logical schema 12, the logical schema tables 42 are used by DBMS 16 to store information which defines logical schema 12. When end user 8 invokes logical schema 12 to create a form or report, DBMS 16 accesses the logical schema tables 42 to retrieve table and relationship information. This information is then used to construct SQL statements that will fetch data.
FIGS. 5-10 depict six logical schema tables 42 of FIG. 4A. These tables are called SYSSUPERVIEWS, SYSSVWTABLES, SYSSVWJOINS, SYSSVWALIASES, SYSSVWORDER, and SYSSVWAUTH, respectively. Note, the columns headings contained in the tables in FIGS. 5-10 are listed along the left hand side of rows in the figures. Also, in this embodiment of the present invention, a logical schema is referred to as a "SuperView", hence the name SYSSUPERVIEWS for the table of FIG. 5.
Below is a brief description of each logical schema catalog table as implemented in this embodiment. The SYSSUPERVIEWS table of FIG. 5 contains information about the names, owners, and descriptions of logical schemas for a database.
The SYSSVWTABLES table of FIG. 6 defines the database tables and views used in a logical schema and the relationships between those tables. Every instance of a table, including the master table, is represented as one row. Level names are defined for tables joined in a One-to-One-or-More or One-to-Zero-or-More relationship.
The SYSSVWJOINS table of FIG. 7 contains information about how the logical schema joins columns.
The SYSSVWALIASES table of FIG. 8 contains information about column aliases defined within the logical schema.
The SSYSSVWORDER table of FIG. 9 defines the sort order of the joins specified in the logical schema.
The SSYSSVWAUTH table of FIG. 10 controls logical schema authorizations for end users.
Referring to FIGS. 5-10, each of the logical schema tables is indexed to improve table access time. Indexing of tables in a relational database is well known in the art and will not therefore be explained in detail. A brief overview of indexing is provided, however, in order to describe how the logical schema tables are indexed.
Indexing can be explained by the following analogy: when a particular item needs to be found in a book, a search of the book's index for a pointer (page number) indicates where the item is located in that book. Likewise, in a database, when a table has been indexed it means that a file has been created to point to the location where frequently accessed data is stored in that table.
Index files contain paired lists of data values and pointers. The data values are the values from a particular column in an indexed table, and the corresponding pointer identifies where in the indexed table the data value is located. In FIG. 5, for example, the SYSSUPERVIEWS table has two indexes, superview1 and superview2. Superview1 indexes the SYSSUPERVIEWS table based on the column `svwid` while superview2 indexes the SYSSUPERVIEWS table on both the columns `owner` and `SVWNAME`. When the DBMS needs to locate all logical schemas with an owner named "Smith," for example, the index superview2 for the column `owner` is searched for a data value corresponding to "Smith". When this value is found in the index file, the pointer for this value indicates where the record can be found in the SYSSUPERVIEWS table. This eliminates the need for a sequential search of the indexed table.
The two types of indexes used in the present invention are unique and duplicate indexes. A unique index does not allow duplicate values in a column, while a duplicate index does allow duplicate values. In the SYSSUPERVIEWS table, the indexes are unique meaning that no duplicate index values for `svwid` and `svwname` are permitted in the indexed files.
As stated above, after a command, such as "Create Logical Schema" has been invoked, the information corresponding to the newly-created logical schema is stored in the logical schema tables 42 of FIG. 4A. In a preferred embodiment, the logical schema tables SYSSUPERVIEWS of FIG. 5, SYSSVWTABLES of FIG. 6, and SYSSVWALIASES of FIG. 8, contain data concerning the newly created logical schema.
The SYSSUPERVIEWS table contains information about the names, owners, and descriptions of logical schemas for a database. For each logical schema created, one row of information is inserted into SYSSUPERVIEWS.
FIG. 11 depicts the row of information inserted into the SYSSUPERVIEWS table for the invoice logical schema being created in the present example. The column `svwid` is a serial data type which is assigned automatically and is unique for each logical schema. Assuming this is the first logical schema created, then `svwid` is assigned the value of one. The column `owner` contains the logical schema owner's login name. In this example, the owner's name is "Ifmx". The columns `svwname` and `remarks` are the character strings entered for the logical schema name and its description, respectively.
The second table to receive data is the SYSSVWTABLES table (FIG. 6). The SYSSVWTABLES table defines the database tables used in a logical schema and the relationships between those tables. One row of data is inserted into the SYSSVWTABLES table for each table chosen for the logical schema. In the present example, four tables were chosen, thus four rows of information are inserted into the SYSSVWTABLES table.
FIG. 12A depicts the SYSSVWTABLES table after the four rows have been inserted. The column `svwid` matches the svwid value automatically generated when the SYSSUPERVIEWS row was inserted. The column `tableseq` is undefined at this point for all four tables because column sequencing has not yet been defined. The column `owner` contains the owner's login name for each table as in the SYSSUPERVIEW table. The columns `owner` and `tabname` are determined from the character strings found in the available tables list 200 of FIG. 4B.
The columns `masteralias` and `cardinality` of the SYSSVWTABLES table are undefined at this point except for the master table, because relationships between the tables have not yet been established. The Customer Table is the master table, therefore the `cardinality` column for that row is assigned the value of `M`. The remaining columns receive NULL values. Column `usetype` defines whether the table may be updated (U) or is look-up only (L). In the preferred embodiment, the value of `usetype` is always "L".
The column `tabalias` is assigned the table alias string which appears in the selected tables list 202 of FIG. 4B. If the tables aliases have not been established, then the `tabalias` column is assigned the name of the original table. As shown, the Sales-- Header table has been given an alias of "Sales-- Info".
The column `levelname` is undefined at this point because logical schema levels have not yet been defined. Therefore, in FIG. 12A, a NULL is entered for the `levelname` column in each row. No remarks have been entered in the remarks column.
The SYSSVWALIASES table of FIG. 8 is the third table to receive values when the logical schema is created. The SYSSVWALIASES table contains the information about column aliases defined within logical schemas. One row is inserted into this table for each column contained in each table selected for the logical schema.
FIG. 13 depicts the SYSSVWALIASES table after data has been inserted for the "invoice" schema. In this example, the tables for the logical schema contain the columns as shown in FIG. 12B. These same columns are listed under the `colname` column as shown in FIG. 13 for each table.
Entries in the column `svwid` in the SYSSVWALIASES table of FIG. 13 match the svwid value automatically generated when the "invoice" logical schema was created and corresponds to the `svwid` in SYSSUPERVIEWS of FIG. 12A. Column `tabalias` contains table alias strings corresponding to the tables for which one column aliases are being stored. Entries in column "colname" are the actual column names in the original table. Column `colseq` indicates the order of how columns will be displayed and is assigned 0 at this point for all column names.
The column `colalias` is an alias which will identify a particular column uniquely in the logical schema. It is, by default, the original column name. If two tables are selected that contain a column with the same name, the second occurrence of the column is automatically aliased by appending an underscore character after the name. For example, the tables Customer and Sales-- Info contain a column named custnum, so that the column in the second table is automatically aliased to custnum--, as shown.
Entries in the columns `label` and `title` are the same as `colalias` at this stage. Entries are inserted into the column `flag` when a view, rather than a table, is used to define the logical schema. At this stage the entries in the flag column are set to zero.
At this stage in the creation process, only information concerning the selected tables is inserted into the SYSSVWALIASES table. Information concerning the column aliases is defined in later stages.
The most important task in defining a logical schema is establishing the relationships between logical schema tables. When a logical schema is first created, only the logical schema master table is defined, and no relationships among the remaining tables have been specified. Therefore, in the present example, the relationships among the four tables selected must be created for the "invoice" logical schema. The exact steps to follow to define the relationships vary according to the specific embodiment of the GUI employed.
To define a relationship, two tables from the logical schema, and the type of relationship or the cardinality of the relationship between the tables are selected. As described above, the types of relationships that can be defined in a logical schema are One-to-One, One-to-Zero-or-One, One-to-Zero-or-More, and One-to-One-or-More.
Once the relation is defined, columns from the two tables, and an operator that defines how the selected columns in the two tables are to be joined are selected. Two columns can be joined by the following join operators:
Not equal<>
Greater than>
Greater than or equal to>=
Less than<
Less than or equal to<=
The join operators define how tables are joined based on the values of the join columns. In the example where the customer table is joined with the store table through the `storenm` column, the "equal" join operator will join the two tables when the value of `storenm` is the same in both tables. For most cases, the "equal" join operator is used to join two columns because most columns are joined when they have values which are equal. The other join operators are available for occasions when the "equal" operator is not sufficient to join two columns for a particular application.
The "not equal" operator joins two columns when the value of the first column is not equal to the value of the second column. The "greater than" operator joins two columns when the value of the first column is greater than the value of the second column. The "greater than or equal to" operator joins two columns when the value of the first column is greater than or equal to the value of the second column. The "less than" operator joins two columns when the value of the first column is less than the value of the second column. The "less than or equal to" operator joins two columns when the value of the first column is less than or equal to the value of the second column.
FIG. 14 shows how a DBA could specify the relationships of logical schema "invoice" according to FIG. 3. Lines 1, 3 and 5 indicate the logical schema relationships between the entities represented by the tables, and lines 2, 4 and 6 show how tables are to be joined in the physical database. The Customer and Store tables are joined through the storenm column entries; as shown on line 2. The Customer and Sales-- header tables are joined through the custno column entries as shown on line 4; and the Sales-- header and Sales-- items entries are joined through the salesno column entries as shown on line 6.
The first table chosen in logical schema relationship is the Master or Parent table of the relationship. Each Master or parent table is joined to at least one detail table. This join between a parent table and a detail table provides a logical pair of tables. In the example, the tables on the left-hand side of each logical pair of tables of the logical schema relationship definitions of lines 1, 3 and 5 are master or parent tables while the tables on the right-hand side are the detail tables for those relationships. As stated above, every One-to-One-or-More or One-to-Zero-or-More relationship between logical pairs of tables adds a new logical schema level to the logical schema. Therefore, the relationship of the Customer table to the Sales-- Header table of line 3 creates a first detail level and the relationship of the Sales-- header table to the Sales-- items table of line 5 creates a second detail level, as shown in FIG. 3.
The names of logical schema levels may be automatically assigned by the DBMs. The first level of the logical schema takes the name of the logical schema master table. All other logical schema levels take the name of the first table chosen in each One-to-One-or-More or One-to-Zero-or-More relationship.
After the relationship definitions between tables in a logical schema are completed, the DBMS stores the information in the logical schema tables 42 of FIG. 4A. When a command is issued to apply the specified relationships, the DBMS updates the SYSSVWTABLES table and inserts new information regarding the joins into SYSSVWJOINS table.
FIG. 15 shows the SYSSVWTABLES table after being updated with the relationship information specified above. The column `tableseq` has been updated to reflect the hierarchy of tables in the logical schema. The table sequencing can be performed in a variety of ways, but in this embodiment the values in column "tableseq` are assigned as follows:
a) The logical schema master table is always assigned a value of "1". Thus, in FIG. 15 the Customer table is the master table.
b) The logical schema hierarchy is traversed beginning at the master table, and the detail tables are assigned tableseq values sequentially based on the order the relationships were defined. In the sample logical schema of the present example, the first relationship defined was between the customer table and the store table. Therefore, the `tableseq` column for the store row is assigned a "2". The second relationship defined in the sample logical schema was between the customer table and the Sales-- header table, so the `tableseq` column in the Sales-- header row is assigned the value of "3". The last relationship defined was between the Sales-- header table and the Sales-- items table, so the sales-- items table is assigned the value of "4".
Next, the column `masteralias` in the SSYSVWTABLE table is assigned the alias of the parent table. Because the design of the logical schema dictates that any table can have only one master table, the logical schema master table has no master, so it is assigned a NULL value. The Store table and the Sales-- header table are both detail tables of the Customer table, so the `masteralias` entries are assigned the value of "Customer".
The column `cardinality` in SYSSVWTABLES is assigned a code identifying the number of rows in the detail table per one row in the parent table. The logical schema master table is always assigned a value of "M", which stands for Master. The remaining tables are assigned one of the following codes: value of "1" means that there is always one row per master row; value of "0" means that there are zero or one row per master row; value of "n" means there are zero or more rows per master row; value of "N" means there is one or more rows per master row.
In this sample logical schema, the cardinality entry for the Store table row is a "1" because there is always one row of the Store table per customer in the Customer table. The cardinality entry for the Sales-- header table row is assigned an "N" because one or more rows exist in the Sales-- header table per row in the Customer table.
The `levelname` entries in the SYSSVWTABLES are set so that all tables at a given detail level share a level name. By default, the `levelname` entries are assigned the table alias of the table that initiated that level. The Customer and the Store tables are both in the master level and the `levelname` column is assigned the name of the master table, "Customer". The Sales-- header table is in its own detail level and its `levelname` column is given the alias for that table, "Sales-- Info". The Sales-- items table is in its own detail level and its `levelname` column is assigned the name of the Sales-- items table.
FIG. 7 depicts the SYSSVWJOINS table which also contains information about the logical schema relationships. One row of data is inserted into the SYSSVWJOINS table for each join defined in the logical schema. Three relationships between tables, namely the Customer Store relationship of lines 1 and 2, the Customer Sales-- Info relationship of lines 3 and 4, and the Sales-- Header Sales-- Items relationship of Lines 5 and 6 were defined in the sample logical schema shown in FIG. 14. Therefore, three rows are inserted into the SYSSVWJOINS table.
FIG. 16 depicts the SYSSVWJOINS table after being updated with the join information from the example schema of FIG. 3. The `svwid` entries match the svwid value for the "invoice" logical schema which has a value of "1". The `tabalias` entries identify the table aliases of the tables being joined.
The column `jcolseq` is assigned the order of the joins for a given relationship. Tables may be joined on more than one column. When this occurs, SYSSVWJOINS tables receives an additional row for each column in the table being joined. For example, if the store table was joined with the customer table by two columns, then another store table row would be inserted into the SYSSVWJOINS table and the column `jcolseq` for the new row would contain the value of "2". In the example logical schema of FIG. 3, since all the tables are joined by a single column (not composite joins), the entries are assigned the value "1".
The entries in the `mastercol` identify the join columns specified on the parent side of the joins. As shown in FIGS. 14 and 16, the Store table is joined with the master Customer table by the column "storenm". The Sales-- header table, alias Sales-- Info, is joined with the master Customer table by the column "custnm". The Sales-- items table, alias Sales-- Items, is joined with the Sales-- header table by the column "salesnm".
Entries in column `detailcol1` in the SYSSVJOINS table identify the join column specified on the detail side of the joins. In the example, the detail column names are the same as the column names in the parents tables, and are therefore appended with an underscore. The column `detailcol2` may be used when a "between" join operator, i e., where x is between "2" and "20", has been selected When this occurs, detailcol1 holds one value specified in the between statement and detailcol2 would hold the second value.
In the SYSSVWJOINS table, the entries in the column `joinoperator` are assigned the value of the selected join operator. As shown in FIGS. 14 and 16, all three joins were defined with the "=" join operator.
Logical Schema Options
Once the logical schema tables have been updated, a complete, valid logical schema exists. The steps, or functions, described below are available as options after the schema creation process.
When end users use the logical schema to create a form or report, they may be given a list of columns in the logical schema from which to choose. The original names of columns in databases may not be descriptive of their contents, and it may be helpful to provide more descriptive names. For example, "custnum" columns could be changed to the more meaningful alias of "Customer Number".
As stated above, column and table aliases are automatically assigned the name of the original column or table. In a preferred embodiment, the automatic aliases can be edited after the creation of the logical schema.
When a table alias is modified, the DBMS causes the following events to occur:
1) In the SYSSVWTABLES table each entry in the tabalias column corresponding to the table whose alias was changed, is updated.
2) Any table that joins to the `tabalias` column in the SYSSVWTABLES table causes the following columns in the corresponding tables to be updated: the column `tabalias` the SYSSVWJOINS table, the column `tabalias` in the SYSSVWALIASES table, and the column `masteralias` in the SYSSVWTABLES table is updated when it contains a value that is the same as the tablealias that is currently being modified.
Like table aliases, a column aliases can be modified after the creation of a logical schema. When this occurs the DBMS takes the following steps:
1) The row corresponding to the table whose column alias was changed is updated in the SYSSVWALIASES logical schema table.
2) Any logical schema table that includes columns referring logical schema column information is also updated based on the value of the previous column name. This includes the `mastercol` column in the SYSSVWJOINS table, the `detailcol1` in the SYSSVWJOINS table, and `colalias` in the SYSSVWORDER table. If column labels or titles are changed, then the corresponding row in SYSSVWALIASES is updated by changing the `label` and/or `title` fields. Labels and titles are placed on forms and reports, respectively, next to data fields.
The logical schema level names can also be modified after the creation of a logical schema. When this occurs the DBMS updates the level name entries in SYSSVWTABLES to reflect the new name.
In a preferred embodiment, the DBA has the capability to control which columns are displayed and in what order they appear, when end users access a logical schema. When end users design a form or report, they make selections from a dialog box listing all the columns in the logical schema. The columns they select are pasted onto the form or report in the order they are listed in the dialog box.
In the preferred embodiment, columns in the logical schema are listed in the dialog box shown to the end user by default in alphabetical order. Once they are pasted onto the form or report, the end user may have to rearrange the columns to a more useful order. If the DBA can anticipate how end users are likely to use the columns, the DBA can change the default arrangement and reorder the columns in the logical schema. This saves end users from having to reorganize the columns on their forms or reports.
Column ordering is controlled by entries in the colseq column of SYSSVWALIASES. The logical schema columns are simply displayed in numerical order of the colseq entries. When the column ordering is altered, the `colseq` entries for those columns are updated.
Columns in a logical schema may also be hidden from the user by assigning the colseq entry a negative value. For example, assume the colseq entries for city and state in the Customer table of FIG. 13 contained values of "4" and "5", respectively. Those columns may be hidden by changing the respective values to "-4" and "-5", thereby maintaining information regarding the prior order.
In a preferred embodiment, the DBMS may be instructed how to order the records returned in all the forms and reports. The basic order of the returned data is determined by the hierarchy of the logical schema. For example, in the "invoice" logical schema containing a customer level, a sales-- header level, and a sales-- items level, for every row of customer information, a number of rows of information about each sale may be returned. Likewise, for each row of sales information, a number of rows of information about the items that made up the sale is returned.
The default sort order in the logical schema determines the order of returned rows within each logical schema level. For example, it can be specified that all the rows of customer data be sorted in alphabetical order by last names; that all the sales information be sorted by the date of sale, beginning with the most recent date; and that all items on each sales order be sorted by item number.
When a sort order is defined for a logical schema, one row corresponding to each sort column specified is inserted into the logical schema table SYSSVWORDER of FIG. 9. The SSYSSVWORDER table defines the sort order of the joins specified in the logical schema. FIG. 17 shows the SYSSVWORDER table after a sort order has been defined based on the last name of the customer and the item name. The column `svwid` in the SYSSVWORDER table matches the svwid value for the logical schema, i.e. "1".
Entries in the column `colalias` specify the logical schema columns that are to be sorted. In the example of FIG. 17, the column aliases `1name` and `itemnm` indicate that the entries are to be sorted by last name and item numbers. Entries in column `sorttype` indicate whether the entries should be sorted in ascending (A) or descending (D) order.
The entries `seqno` indicate the order sorting operations are performed. For example, the assignment of the value "1" to the seqno entry for lastname sorting and the assignment of the value "2" to the seqno entry for item number sorting, as shown in FIG. 17, causes sorting first by last name and then by item numbers.
It may be the responsibility of a DBA to weigh the end user's desire for information against the DBA's responsibility to maintain the security and efficiency of the database resources. Therefore, in a preferred embodiment of the invention access to a logical schema can be controlled; limits can be placed on the query resources of the end user; and how records are locked, i.e., made inaccessible, when the user queries the database can be controlled.
All end users are initially granted SELECT privilege on all new logical schemas, meaning that all end users have access rights to the logical schemas. Who can use a logical schema can be limited by revoking the SELECT privilege from specific end users. An end user can design a form or report based on only the logical schemas to which the end user has SELECT privileges.
FIG. 10 depicts the SSYSSVWAUTH table which controls logical schema authorizations for end users. For each user specified as having logical schema SELECT privilege, one row is inserted into the SYSSVWAUTH table. The column `svwid` matches the svwid value for the logical schema. The column `username` is the login name of the user who has access. Column `svwauth` defines the authorization type for the user. The remaining fields in the row are assigned the resource values that are directly entered for that user. For example, the `rowlimit` field specifies how many rows of data can be read by a user. The column `readsperrowlimit` limits the number of reads the DMBS can perform per row. The column `elapsedtimelimit` specifies the maximum time for a read operation. The column `isolationmode` defines how isolated a user is from other `users` updates in the database.
Logical Schema End User Operation
As stated above, with the present invention, end users are capable of designing forms and reports through the use of a logical schema interface without knowledge of the physical database schema. The present invention allows end users to access database information through an intuitive "point and click" graphical interface with knowledge of SQL commands, keywords, or syntax being required. End users gain access to the data in a syntax independent of the physical database schema. The following describes a preferred implementation of the graphical interface for the end user logical schema.
To access the logical schema, the end user first specifies the database that will be the basis for the form or report. After the DBMS opens the database, a list of logical schemas that have been developed for that database appears in a dialog box. Only the logical schemas to which the end user has been provided access will appear in the list.
The end user then selects a logical schema and chooses a set of fields, which will form the basis of a form or report, from a list of available columns for that logical schema.
FIG. 18 depicts a sample dialog box displaying the master-detail level hierarchy of the "invoice" logical schema. Note that instead of displaying the actual tables and columns, the present invention hides the physical structure of the database by only displaying logical schema levels and the fields available in those levels.
A logical schema level may contain more than one table but is presented to the end user as a level name only. For instance, the customer level 129 consists of both customer and store tables, as shown in FIG. 3. The user need not know which tables contain the desired information. If columns in the tables have been given aliases, then the physical column names are also hidden from the end user.
When a logical schema is being created, table columns are displayed in the order they occur in the actual tables. When users invoke the logical schema to create a form or report, the columns of the logical schema are displayed in alphabetical order if the columns have not been resequenced. In FIG. 18, the displayed columns have been resequenced to appear in the order they exist in their respective tables.
Each logical schema level 28 is a collection of related fields capable of producing a specific type of record. For example, Customer level 129 is the Master level 29 and contains records for customer data as well as store data, including customer names, addresses, phone numbers, store names, sales numbers, and so on. The first detail level 34 is the Sales level 134 and contains records relating to sales information, including sales dates, salespersons, and sales numbers. The second detail level 35 is the Items level 135 and produces records relating to sales items, including sales numbers, item numbers, and so on.
The end user selects those fields 44 for which he wishes to see information. The fields 44 which the user selects are highlighted (shown by grey rectangles) and pasted onto the form or report by the DBMS in the order in which they are listed in the logical schema levels 28.
FIG. 19 is a sample form produced from the "invoice" logical schema. Using a form, an end user can customize the arrangement of data returned from the database. The selected master level data fields 46 selected by the end user from the Customer level 129 of FIG. 18, namely, 1name, fname, phone, storenm, and storeid, are pasted individually into the top portion of the form window as individual data fields 48. Each data field is preceded by text 49 describing the field created by the end user when designing the form. Detail level data fields 50 from the Sales Item level 135, namely, salesdate, salesprsn, itemnm, itemno, price, and qty, are automatically placed in a Detail level scrolling table 52. A detail level scrolling table 52 is automatically generated by the DBMS whenever two tables are joined in a One-to-Many relationship. For example, a particular customer may have made several purchases at one store by several different salespersons, and each sale may include many sales items. Therefore, a scrolling table 52 is generated for both the Sales Info level and for the Items Level information in the end user's form. Note that only the fields selected by the end user in FIG. 18 appear on the form in FIG. 19. Using this feature of the present invention, the end user can control what information will be returned from the database.
After the end user has created a form or report by selecting fields, records from the database can be retrieved and viewed in the form the end user created by entering a command for the DBMS to query the database based on the fields in the form. The DBMS determines from the logical schema tables 42 of FIGS. 4A and 5-10, the physical tables and columns on which to perform the query. The table relationships and joins that have been defined in the logical schema determine the master-detail order of the returned data.
FIG. 20 shows the sample form after records have been retrieved from the database. Recall that a record is a row of data values obtained from a table. The related master and detail level records returned from the query are combined on the user's screen in the form to produce a composite record called a logical schema record 54. FIG. 20 shows that the customer with a last name of Smith, having a store number of G8392 purchased items from a store named X Co. The master level record 56 owns, or controls, the detail level records 58 displayed in each of the detail level scrolling tables 52. As further shown in FIG. 20, Smith's purchase on May 23, 1993 consisted of items called Widgets. Each of these items has a part number, listed in the column labelled "part number". When the end user issues a command to retrieve information regarding the customers other sales, new records retrieved from the database scrolling tables 52 replace the current records in the detail. The detail level scrolling tables 52 graphically depict the One-to-Many relationship between the Customer level and the Sales level and between the Sales level and the Sales Items level. Other forms with this format can be produced by the specification of other customers and sales dates.
It has been shown that the present invention provides a way for an end user to access data in a relationship database without knowledge of the structure of the database by using a logical schema derived from the physical schema of the relational database.
The present invention has been described in terms of a preferred embodiment. The invention, however, is not limited to the embodiment depicted and described. Rather, the scope of the invention is defined by the appended claims.