Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    couple questions

    Im new to these parts. First post in fact. That said the nerd in me actually enjoys the database class im taking and If I think I can help I will.
    I've been kicking around a design concept for a few weeks now. Ive given it quite some thought. I normalized it and then started creating tables in SQL.
    Heres the dillema.
    I had to create my tables in a very illogical order. In order for my foreign key dependancies to work I needed to create certain tables first and more critical ones afterwards. I suppose my first question would be.... is the order by which tables are created a big deal?
    Secondly... Im designing a county court system. I need 6 entities. At first i thought. No prob. There are more than a couple functions that will require their own entity.
    I had 8 to begin with. The more I work my concept tho the slimmer it has been getting.
    i was working with
    Defendant
    Court
    Judge
    Supervison Agent
    Jail
    I had a bridge entity between defendant and court as there are many defendants, many courts and each defendant has several court dates.
    the bridge was Appearance
    I also thought about creating Defendant as a supertype with Probationer and Inmate as subtypes.
    Basically im uncomfortable with a few things. My defendant is the 1 side to quite a few manys. It makes sense. I want foreign keys that will bring up information about the judge, court, probation officer, jail that pertain to a specific defendant. However, by looking at examples in my book I'm not finding anything that resembles this.

    Another thing... As the court system is as much a business as anything I thought it would be appropriate to include a financial division.
    I have worked through some relational algebra equations that will calculate debt owed at any given time. However... all of these attributes seem like they should be located within my defendant entity. From Current_Date, Supervision_Startday, Supervision_End and origional fine. I am able to compute and report enough info to suit the purposes of my assignment.
    Since the attributes i need to make the computations reside in the Defendant Table I dont kno if i can even make a Financial Division entity... even though it seems appropriate.
    The only values i would have to insert would be... phone, address, a couple foreign keys but most of what i would want in there already belongs in Defendant.
    Well... thats that. I appreciate any fresh insite or suggestions. Im not looking for anybody to do my homework. In reality I would way rather fail the class and have a clear functioning understanding than pass and not understand at all.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by abcs View Post
    is the order by which tables are created a big deal?
    only if you want to define your FKs in the CREATE TABLE statements

    many people put off that task and do it after all the tables have been created, using ALTER TABLE statements to add the FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    i suppose im getting kinda frustrated by my database.
    There are classes after this that get more involved and technical. I want
    to take them but this project is making me question my abilities. I mean
    i'm doing well so far in the class im taking. Giving it proper attention and all. I've taken tests and done really well in creating ERDs for the questions given but now that im trying to design my own hookup I dont feel so brilliant.
    In my database there are a few entities that i left out that i should probably include like Prosecutor... however....
    Even though there are many Defendants and more than 1 prosecutor each case is kinda self contained. Its an affair between 1 defendant and 1 prosecutor. Defendants aren't bunched together in real life. Even though in the big picture many defendants see many prosecutors it almost seems like since the court works on a case to case basis it should be 1 defendant to 1 prosecutor.
    I think im over thinking all of this

  4. #4
    Join Date
    Nov 2009
    Posts
    8
    Quote Originally Posted by r937 View Post
    only if you want to define your FKs in the CREATE TABLE statements

    many people put off that task and do it after all the tables have been created, using ALTER TABLE statements to add the FKs
    right... i could totally do that too. I just look at my ERD and then look back at my SQL and as i have been putting my FKs in the create table statement I feel uncomfortable with the order i was having to create tables. It seems like the SQL create table progression should mirror my ERD but i suppose what you say makes perfect sense.
    THX

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It might be well worth documenting the difficult decisions you had to make and what your choices were. That should make interesting reading for the lecturer and shows your thinking process. It's also worth considering using a single user table and using a user_type field - not sure whether it would help or not in this case. Do judges etc remain constant for all the court appearances of a particular case or can they change?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by abcs View Post
    Its an affair between 1 defendant and 1 prosecutor.
    Wouldn't that make an interesting Law And Order episode...
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42
    Hi
    It would probably make your life easier if you defined your requirements first, then designed a Data Model and finally generated the SQL from the Data Model.

    In the meantime, here's a Case Management Data Model that might help you thinking thru the problem :-
    Case Management Data Model

    and here's another one that looks at Judges and Courts :-
    Judges and Courts Data Model

    HTH

    Barry

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Barry - Generally it's frowned upon to just supply answers on a plate to students. This particular chap seems more than bright enough to produce his own design and, through building that design, he'll learn a lot about the subject. There's also no guarantee that a db you take from the web will match the students requirements.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mike_bike_kite View Post
    There's also no guarantee that a db you take from the web will match the students requirements.
    mike, he didn't just "take" them from the web, he wrote them

    and he did say they were intended to provoke thought

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Sorry - that bit was meant to be for abcs but I didn't make it very clear. Abcs sounds intelligent enough to not plagiarise someone else's design but I certainly wouldn't say the same for most of the students who post their coursework here and just hope for an easy answer. If that wasn't the case then your NZDF solutions would be redundant.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're confusing me with patp

    i may have coined the term (nzdf), but they're his solutions

    i hardly ever indulge
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937 View Post
    you're confusing me with patp
    Wouldn't that take VAST amounts of tequila and hallucinogenic's?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2009
    Posts
    8

    2nd run

    much thanks to all. I have taken care to document my logic and naturally it is a work in progress.
    I am beginning to see a much clearer picture of how this will all come together. I redesigned my ERD. I am fairly new to all of this and i appreciate the insite.

  14. #14
    Join Date
    Nov 2009
    Posts
    8
    A couple things. For all intents and purposes I think im getting close. My first normalization attempt is still functionally in tact. I still have a composite PK composed of defendant and court. From court i will be able to establish everything that is not defendant related information. I have decided to include a financial dimmension. Seemed more than appropriate. Herein lies my last real problems. They are basically relationship related and i suppose there is more than 1 way to interpret this. I am trying to mirror the court model as closely as possible. To recreate how it is actually done. Circuit court probation officers do not supervise defendants who are processed in the District court. If you are prosecuted and found guilty in circuit court you end up reporting to circuit court probation. Pretty sure same rings true for circuit court prosecutors.
    So... My county has many courts; district,circuit,family ect...There are many prosecutors who work cases but they do not jump from court to court. A district court prosecuting attorney does not try cases in the circuit court. So there are many of both but there is a condition that dictates that while there exist many they only work at 1.

    Would i be correct in my assessment that this is a 1 to many relationship? I feel like I kno that it is however i keep second guessing myself. If someone could review my logic and reaffirm it or disqualify it I would be eternally greatful. Otherwise it will involve an un-necessary bridge entity.
    Either way im almost there. Once i hash these relationship issues out im pretty confident that i will have this one beat.

Posting Permissions

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