If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
I am taking a database class. It is an online class and the teacher and I are having a conflict. Basically she doesn't want to help me! I know it is an online class and it is up to us to learn, but there comes a point in time when if you don't get something the teacher should be leading you in some direction. This is the last class I need to graduate! Databases are not my thing but here is the situation:
We have to pretend we are working in a group to develop a database. The database that I came up with was very simple. Basically it would hold birthday, anniversary, or other event information about people that I know. It would remind me of the birthdates a week in advanced. I would also be able to pull up who had an event for a particular month.
We had to start with user requirements. She sent my user requirements back to me four times and on the fourth time finally took them but told me they were still wrong. Here are my user requirements:
Requirements
The database will provide a simple user interface to allow for the entry of the data listed below:
Name
Home Address
Home Phone
Cell Phone
Work Place (Name of Company or Business)
Work Address
Work Phone
Birthday Month
Anniversary Month
Other Event1 Month (i.e. Graduation, Promotion)
Other Event2 Month
The database will have a calendar feature.
The calendar feature will provide a reminder feature.
The reminder feature will prompt the user with a message when the significant event is within one week.
The database will have a simple reporting function to allow the user to query by:
Name
Birthday Month
Anniversary Month
Other Event1 Month
Other Event2 Month
The query or report will return all the data associated with the Name, but only the fields listed above will need to be entered.
The database will have a query feature that will allow labels to be printed by associating a name with the address of the recipient.
Assumptions
1. This will be for only one user and possibly one other to include my husband.
2. The budget will be very small since I will be making the program for myself, < 100.00
3. This database will allow the opportunity to purchase cards for birthdays, anniversaries and other events to ensure contacts will receive a card on or before the event.
We had to do an ERD, here is mine:
(there is a graphic here that shows relationship, but I will write it out)
Table 1
PersonID - Primary Key
Name
HmAddress1
HmAddress2
HmAddress3
HmCity
HmState
HmZip
HmPhone
CellPhone
WkPlace
WkAddress1
WkAddress2
WkAddress3
WkCity
WkState
WkZip
WkPhone
There is a dotted line that links table one to table two as a one to many relationship with the word celebrates connecting them.
Assumptions:
1. Each person will be entered separately. There will be no couples entered in to the same entry.
2. Name, HmAddress1, HmCity, HmState, HmZip, & HmPhone will be mandatory in Table 1.
3. BirthdayMonth, BirthdayDay, & BirthdayYear will be mandatory in Table 2.
4. Birthdays and Anniversaries will automatically repeat yearly
5. Events that are entered will have an option as to whether it should repeat yearly or not.
I am supposed to have a Logical Design, Metadata, and Physical Model as well. But I am really not sure about the first two. I just really need someone to help me get through this. I can email what the teacher has as examples in order to help anyone willing to help me understand what she is looking for because I obviously have no idea!
Your schema is not normalized, which would cost you mega-points if I was the instructor.
A person presumably has only one favorite color, so it belongs in table1. While they might have several hobbies, you can probably get away with putting Hobby1 and Hobby2 in Table1 as well, though strict rules of normalization would require a separate table to handle multiple hobbies. Either way, Hobby1 and Hobby2 certainly do not belong in Table2.
Table2's schema should just be this:
Table 2 (PersonID - primary key
EventName
EventMonth
EventDay
EventYear
Comments)
...which can handle any number of events and use the same code for each event. And personally I would store a single datetime value for the Event rather than splitting day, year, and month out into different columns.
Now to really burst your bubble:
Quote:
Originally Posted by donnarae79
The database that I came up with was very simple. Basically it would hold birthday, anniversary, or other event information about people that I know. It would remind me of the birthdates a week in advanced. I would also be able to pull up who had an event for a particular month.
Bzzzzzzz! Wrong-o!
This is NOT a simple task. As a matter of fact, if I was interviewing a candidate and wanted to stump him with a question that appears simple but is in actuality very complex, this is what I would ask him to do.
To get an idea of what you are getting into, and some possible solutions, check out this thread: List up-coming birthdays
I hope this helps you, and good luck with your assignment.
__________________
If it's not practically useful, then it's practically useless.
I did just say to someone that I should have done something that required more tables because this one was complicated because it was so simple.
I understand what you are saying about the tables and that does make sense! For the first time ever! We don't actually have to make this database work, we just have to include a project overview, Entity Relationship Model, logical & physical models, database schema, & sql examples. We are basically turning in a paper with all of the above. Your comments have certainly helped me with the user requirements that we had to turn in. I just don't understand what the logical design is for. In hers she has conversion rules. The problem is I don't know what that is and it does not discuss them in our book. The book and our project are totally unrelated, which is why I am having so many problems!
Your schema is not normalized, which would cost you mega-points if I was the instructor.
A person presumably has only one favorite color, so it belongs in table1. While they might have several hobbies, you can probably get away with putting Hobby1 and Hobby2 in Table1 as well, though strict rules of normalization would require a separate table to handle multiple hobbies. Either way, Hobby1 and Hobby2 certainly do not belong in Table2.
Table2's schema should just be this:
Table 2 (PersonID - primary key
EventName
EventMonth
EventDay
EventYear
Comments)
...which can handle any number of events and use the same code for each event. And personally I would store a single datetime value for the Event rather than splitting day, year, and month out into different columns.
1. Each person will be entered separately. There will be no couples entered in to the same entry.
2. PersonID, Name, HmAddress1, HmCity, HmState, HmZip, & HmPhone will be mandatory in Table 1.
3. PersonID, EventName, EventMonth, EventDay, and EventYear will be mandatory in Table 2.
4. Events that are entered will have an option as to whether it should repeat yearly or not.
Normalization:
1. There is a several to one (M-N) Relationship between table 1 and table 2. In the previous ERD I had FavoriteColor, Hobby1, and Hobby2 listed under table 2. I have changed them to table 1 since most entries will not have more then one of these.
2. Table 2 previously had BirthdayMonth, BirthdayDay, BirthdayYear, AnniversaryMonth, AnniversaryDay, AnniversaryYear, Event1, Event1Month, Event1Day, Event1Year, Event2, Event2Month, Event2Day, and Event2Year which I removed because they were not required. I created EventName, EventMonth, EventDay, and EventYear to reduce redundancy.
Will this work?? I know the definition of normalization, but I really have no idea what it means in the real world. I know it is to reduce redundancy, but that means nothing to me really.
Presumably one of your events would be birthday. By definition a person can only have one birthday. You stated earlier that birthday is mandatory. I think birthday belongs in table1 which, I think, changes assumption 3
BTW - I would also be a little nervous about what you mean by "repeat yearly" - it may be unfounded... it may not be.
What I meant by that was, for example:
A friend of mine recently graduated from optometry school, her graduation is something that will happen only one time. But the anniversary of her wedding will happen every year. So That's why I wanted an option as to whether it should repeat or not.
How do you intend to indicate that an event is repeating?
That is a good question, I was just thinking about that! ummm. Do I have to include that in the write up or can it just be part of the assumptions?
I was thinking of something like, Repeat event yearly? and then a yes or no radio button, would that be considered a boolean?
Here is what I have for my list of tables and primary keys for the Physical model : (I guess she also wants us to do a data schema, which is below the physical model.)
CREATE TABLE Table1
( PersonID CHAR(5) NOT NULL,
Name VARCHAR(50) NOT NULL,
HmAddress1 VARCHAR(50) NOT NULL,
HmAddress2 VARCHAR(50),
HmAddress3 VARCHAR(50),
HmCity VARCHAR(50) NOT NULL,
HmState CHAR(5) NOT NULL,
HmZip CHAR(10) NOT NULL,
HmPhone CHAR(10) NOT NULL,
CellPhone CHAR(10),
WkPlace VARCHAR(50),
WkAddress1 VARCHAR(50),
WkAddress2 VARCHAR(50),
WkAddress3 VARCHAR(50),
WkCity VARCHAR(50),
WkState CHAR(5),
WkZip CHAR(10),
WkPhone CHAR910),
Hobby1 VARCHAR(50),
Hobby2 VARCHAR(50),
FavoriteColor VARCHAR(50),
Comments VARCHAR(100) )
CREATE TABLE Table2
( Person ID CHAR(5) NOT NULL,
EventName VARCHAR(50) NOT NULL,
EventMonth CHAR(2) NOT NULL,
EventDay CHAR(2) NOT NULL,
EventYear CHAR(4) NOT NULL, )
DO I need to have a CONSTRAINT statement? I'm not sure if I understand what it is! And would I need to add something for the yes or no yearly question?
Just a quick comment on normalization. While normalization is the correct way to develop a database, it's not always the way it's done and you'll find some companies have different ways they design which you have to work with. For example, the attached picture shows a diagram of relationships develeoped by a company which developed this application years ago and continues it's support of it with upgrades (yet the structure remains the same). It's a Progress backend and the rules of normalization don't seem to apply. Take a look at the picture and see if this is something you'd like to work with developed by a rather "Big" company in business since the 1980's. Something you can look forward to in the real world of databases (NOTE: This is only PART of the big picture - there are actually 600 tables in the entire application!)
The point being - while you are being taught the correct way of developing databases, don't think EVERY company is going to design things in a normalized sense of manner! If you're going to work with databases and structures, you'll sometimes have to work with some "different" designs (this is not meant to scare you - just give you a reality picture).
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
It's not too bad once you figure out what's going on with it (smile). The below attachment of this other db is the one which really through me for a loop because it's supposed to be one of the easiest code management programs/databases there is currently on the market (never say a dba's job is easy.)
Anyone want to take a stab at figuring out the "normalization" rules with this one? ...I think I'll go back to the MSAccess forum for a bit now to make my head clearer.
__________________
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
Yes, it is true that just because a company is big doesn't mean their databases are well designed....
This "pile-o-crap" database is for school and is not one I have to make work. In fact it's only purpose is to help me pass the class! I chose something that I thought was simple. Trust me, I have no intentions of ever messing with a database ever again in my life! And the only reason I took the class was because it was required. But thank you for your help!
dBforums
>
I Need Help!!
09-13-06, 09:53
Linear Mode
