Really no other reason. I just thought everyone would love to see the gooey wonderfulness from my Informatics "Final Project". What's even scarier is that because I'm so used to thinking in databases, creating both of those documents took less than an hour (and a lot of that was making them look pretty . . . ).
Given I don't think its going to take more than five minutes to grade for the AIs, and I didn't want it to be so hardly seen after I spent all that effort to make it look nice, I thought I might inflict it on all of you .
Posted by Vána (Member # 6593) on :
They're very pretty. *patpat*
Posted by fugu13 (Member # 2859) on :
Thank you.
Posted by Mike (Member # 55) on :
Lemme guess, OmniGraffle?
Posted by Dagonee (Member # 5818) on :
This is how I think in my head. Big help in law school, actually. What program did you use?
The only things I'd mark you down on are using SSN for a primary key , having separate tables for managers and servers, and using Name for a primary key on OrderItem.
I'd probably also use a generated key for Order, but I wouldn't mark you down for it.
Dagonee
[ December 10, 2004, 12:39 PM: Message edited by: Dagonee ]
Posted by Altáriël of Dorthonion (Member # 6473) on :
You evil person you!
Posted by fugu13 (Member # 2859) on :
Heh, I actually tend to agree on those things, but this was a group project, and I have to do two things: make it understandable to my group members and fulfill the requirements, which were badly written.
I don't feel bad about using the SSN as a primary key, though, because this is a small "Mom N Pop" restaurant (the name in the problem is "Mom N Pop's Restaurant").
Yes, it was created with Omnigraffle, which is excellent for these sorts of things.
Name has to be part of the primary key on orderitem because its the primary key on item, so to uniquely determine an orderitem (in a mathematically meaningful way ) you need it. The reason its a primary key on item is that due to how people need to order stuff, any name must be unique. You can't order the "Strawberry Tart with ItemID #52" instead of the "Strawberry Tart with ItemID #236" -- there's only the Strawberry Tart.
The separate tables for managers and servers is just a group understanding thing, though it is imaginable the information in the real world for managers and servers would be orthogonal enough to justify separate tables. However, what I'd normally do is make them both IsA employee on the ER model, then have an employee table with a manager? field.
Posted by Altáriël of Dorthonion (Member # 6473) on :
bad squishy! bad!
Posted by Scott R (Member # 567) on :
My hatred for you is surpassed only by my apathy.
Posted by Dagonee (Member # 5818) on :
Oops, I meant Item, not OrderItem when referring to name as the primary key. The big problem there is if the name changes (that's only one of the problems w/ SSN - yes, it does happen), then old records either have to be updated or orphaned.
SSN has privacy issues in addition to the potential for changing. Having it in one field allows that field to be restricted. In DBMSs without field-level access control, we've gone so far as to move SSN to separate tables. But then, we worked for the government, with much stricter rules on privacy issues.
And badly written requirements are the bane of all designers.
Dagonee
Posted by fugu13 (Member # 2859) on :
(((((Scott)))))
In this case, if the name changes, its a new item.
*nods* yeah, I can see that on SSN, in this case we're sort of like blockbuster, where any employee can see the full credit card numbers of any credit card used to sign up for an account that has been used at that store . In fact, I may change that, if I feel like it.
Posted by Mike (Member # 55) on :
Huh. Databases are completely foreign to me, and I majored in computer science (and math). One of these days I'll learn how to use them.
Posted by Dagonee (Member # 5818) on :
Damn, remind me to cancel my Blockbuster accounts.
Posted by Sara Sasse (Member # 6804) on :
fugu, would you lose all respect for me if I said I'm afraid to look?
I don't understand many things, and I have a tendency to screw up my computer in weird ways, so anxiety is preventing me from clicking. (I've tried thrice.)
But I'm very impressed from the description. Posted by fugu13 (Member # 2859) on :
Oh, you can do several things to avoid it.
1) sign up without a credit card.
2) ask them to remove the credit card information from your account.
3) sign up with a credit card, but request they not write down the number, you'll just rent less for the first month.
Oh, and if ask them to cancel it, make sure they do it. They might do nothing (because from their perspective, if you don't come in the account doesn't get used, so what's the point of canceling it?)
Posted by Corwin (Member # 5705) on :
Sara, don't worry, they're just some innocent looking diagrams! The names sound more complicated than the actual things! Of course, it might have something to do with the fact that I too study Computer Science... Hmm...
Posted by fugu13 (Member # 2859) on :
All those are is PDF's, and small ones at that, they can't screw up your computer. However, if nothing's happening, you may not have a PDF reader installed or correctly configured (most likely the former).
Posted by fugu13 (Member # 2859) on :
Mike: Databases are just set theory, pretty much.
Posted by Sara Sasse (Member # 6804) on :
Oh, fugu, they are very pretty! Posted by Corwin (Member # 5705) on :
I think this is the first time I hear someone say diagrams are pretty!
Posted by Mike (Member # 55) on :
fugu: does the axiom of choice matter? Posted by fugu13 (Member # 2859) on :
You know, I'm not sure . . . I'd guess yes, but that's just intuition.
Posted by Dagonee (Member # 5818) on :
A good ERD is a thing of beauty. Posted by MrSquicky (Member # 1802) on :
I'd agree with Dags here. I'd combine the server and manager table into one employee table with either a flag or foreign keyed type field, which would also let you consolidate the different shift tables. If you can only have one item with a name, just drop an unique index on that field and use an autogen'd id. That'll give you the ability to make item name alterations based on things like American foreign policy (eg. French fries to Freedom fries) and relieve you of the admittedly slight performance/storage hit you take for using a non integer id.
Hey, if we're being evil, I need to get my piece too.
edit: [Kung-Fu announcer voice]Enter the Fifth Normal Form[/Kung-Fu announcer voice]
Idle question, does anyone really trifle with the 4th or 5th normal forms? My db-fu is strong, but they frighten me (or rather seem largely unnecessary).
[ December 10, 2004, 04:09 PM: Message edited by: MrSquicky ]
Posted by fugu13 (Member # 2859) on :
You mean the joining tables? Those're just implementation details, and taken care of by any good OR mapping software automatically.
And anyways, as I said that's likely what I'd do myself absent group members .
As this class largely focused on (very primitive) database theory, the name as primary key thing is definitely staying, as its more "theorylicious". Primary keys independent of the data are considered icky .
As for fourth and fifth normal form, I think you'll find them used mainly in data warehousing and KDD processes, as normalizations are particularly important there.
Posted by Dagonee (Member # 5818) on :
quote: Primary keys independent of the data are considered icky
Which has always proven to me that theorists don't design enough systems and maintain them in the real world.
I never paid much attention to 4NF or 5NF. THey usually happened anyway, and really amount to simply thinking out what you want to be able to represent.
Dagonee
Posted by fugu13 (Member # 2859) on :
Dagonee, think of it as naive implementation for the sake of learning, sort of like how physics students calculate all those trajectories absent air resistance, or assume instantaneous turnarounds, or assume a point cow. So many people are taught to just use a separate, automatically incrementing field as a primary key that they do it everywhere, even with (for instance), the shifts table here, where its preferable to use start and duration (since whenever those are the same its the same shift, and whenever those are different its a different shift).
Many people are taught the autonum way, and then never bother with anything different because that's "easy". This is the first time most of the students in this class are seeing databases, and emphasizing the importance of meaningful PKs will result in better database design in the long run, I feel.
Posted by Raia (Member # 4700) on :
Because *I* am an evil person...
I just won't look at them. Posted by TMedina (Member # 6649) on :
I'll second the "joint employee table" idea - you can just assign an "employee_ID" which is shorter and easier to use than a SSN which, while fulfilling the requirements of a Primary Key, is just a pain to type in.
Reducing your table count is always a good thing.
I'm not sure why you have so many primary keys in the Server_Shift table - in fact, the design seems to be pretty PK happy.
All of which reminds me, I need to brush up on my ERDs.
-Trevor
Posted by fugu13 (Member # 2859) on :
There is only one PK on any given table. That PK can, however, be over multiple columns.
The SSN is going to be in the database structure somewhere -- that's important information if nothing else for tax purposes. As for it being a pain to type in . . . this is a project intended to simulate a small restaurant. Typing in a nine digit number for maybe a couple dozen employees is not a big deal.
Also, reducing your table count is not always a good thing, it is often better to factor something out than to make a huge overly complex table.
Any design should be "PK happy". In fact, there should be indexes and constraints and such all over the freakin' place in most well-designed databases. This doesn't mean index everything -- but if you're going to be doing any sort of search or join on a column or group of columns, an index will speed things up by orders of magnitude.
Posted by Dagonee (Member # 5818) on :
quote:Dagonee, think of it as naive implementation for the sake of learning, sort of like how physics students calculate all those trajectories absent air resistance, or assume instantaneous turnarounds, or assume a point cow.
Absolutely. Point cows are wildly impractical - low meat yield.
quote:Reducing your table count is always a good thing.
It's no so much reducing table count - normalization usually increases table count by a lot. It's more about treating like entities alike.
![[Wink]](wink.gif)
![[Razz]](tongue.gif)
.
![[No No]](graemlins/nono.gif)
, having separate tables for managers and servers, and using Name for a primary key on OrderItem. ![[Smile]](smile.gif)
![[ROFL]](graemlins/rofl.gif)
![[Big Grin]](biggrin.gif)