Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: I Need Help!!

  1. #1
    Join Date
    Sep 2006
    Posts
    18

    I Need Help!!

    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

    Table 2
    PersonID - primary key
    BirthdayMonth
    BirthdayDay
    BirthdayYear
    AnniversaryMonth
    AnniversaryDay
    AnniversaryYear
    OtherEvent1
    OtherEvent1Month
    OtherEvent1Day
    OtherEvent1Year
    OtherEvent2
    OtherEvent2Month
    OtherEvent2Day
    OtherEvent2Year
    FavoriteColor
    Hobby1
    Hobby2
    Comments

    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!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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:
    http://www.dbforums.com/showthread.php?threadid=985381
    I hope this helps you, and good luck with your assignment.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Itz ya birf-day!
    Lol.

    Nice solution - bookmarked.

    I'm sure I saw the SQL Teamers floundering around with this problem a few weeks ago.... I'm not sure if they cracked it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2006
    Posts
    18

    That makes sense

    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!

  5. #5
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by blindman
    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.

    Here is what I have for my ERD Now:

    Table 1
    PersonID - Primary Key
    Name
    HmAddress1
    HmAddress2
    HmAddress3
    HmCity
    HmState
    HmZip
    HmPhone
    CellPhone
    WkPlace
    WkAddress1
    WkAddress2
    WkAddress3
    WkCity
    WkState
    WkZip
    WkPhone
    Hobby1
    Hobby2
    FavoriteColor
    Comments

    Table 2
    PersonID - Primary Key
    EventName
    EventMonth
    EventDay
    EventYear

    Assumptions:

    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assumption 3 can be tricky to enforce.

    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I would also be a little nervous about what you mean by "repeat yearly" - it may be unfounded... it may not be.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by pootle flump
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How do you intend to indicate that an event is repeating?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by pootle flump
    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.)

    Table 1
    PersonID - Primary Key
    Name
    HmAddress1
    HmAddress2
    HmAddress3
    HmCity
    HmState
    HmZip
    HmPhone
    CellPhone
    WkPlace
    WkAddress1
    WkAddress2
    WkAddress3
    WkCity
    WkState
    WkZip
    WkPhone
    Hobby1
    Hobby2
    FavoriteColor
    Comments

    Table 2
    PersonID - Primary Key
    EventName
    EventMonth
    EventDay
    EventYear


    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?

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Normalization

    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).
    Attached Thumbnails Attached Thumbnails DSTestDatabaseRelationships.JPG  
    Attached Files Attached Files
    Last edited by pkstormy; 09-14-06 at 11:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I think the table "Dsorder" sums up that ERD very well, Paul.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Wow. That is one pile-o-crap database.

    Yes, it is true that just because a company is big doesn't mean their databases are well designed....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Diagram

    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.
    Attached Files Attached Files
    Last edited by pkstormy; 09-14-06 at 15:53.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Sep 2006
    Posts
    18
    Quote Originally Posted by blindman
    Wow. That is one pile-o-crap database.

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •