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 .
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
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.
posted
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.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
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.
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.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
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.
Posts: 1810 | Registered: Jan 1999
| IP: Logged |
posted
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. Posts: 2919 | Registered: Aug 2004
| IP: Logged |
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?)
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
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...
Posts: 4519 | Registered: Sep 2003
| IP: Logged |
posted
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).
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
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).
posted
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.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
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.
posted
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.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
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.
posted
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.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
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.
Printer-friendly view of this topic
![[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)
