Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Is my database normalised enough till the 3rd form?

    Hi

    I am currently designing a database where i need to register and keep a record of students at a college. I have done the design and normalized it but i want to know if i can make it more 'atomic' so that there is no repetition when i enter data.

    There is the design of the db attached to this post in pdf. Please anyone can check it and let me know what you think, if it is correct by design or i should atomize it further. For eg, the address table, what if a person has more than 1 postal addresses, i would then have to repeat the other attributes as well right? so not good design? can anyone advise? thanks.

    regds
    kaleem
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    With only a list of attribute names there isn't much to go on. I think you are right to think about multiple addresses. How would you go about implementing that?

    You also need to indicate the keys in your design.

  3. #3
    Join Date
    Jul 2009
    Posts
    168

    db design modified

    hi

    thanks for your reply. I have modified the design and here is the 2nd version attached. I have broken the entities further down so that attributes are not repeated in my tables but only appear once. I have also indicated the keys in the 'related keys DB' where all the foreign keys are listed in my table.

    Only thing I am worried is whether it will be a problem to query considering the number of joins? will that be a problem? if it is, what can be done? thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Your wording is not OK either. It should be Student table not DB. A database (DB) is the collection of tables (and other database objects)

    I personally prefer a different layout when it comes to a textual description of a table, something like this
    Code:
    Table student
    
    Column            | Type            | Modifiers | Description
    ------------------+-----------------+-----------+------------------------
     id               | integer         | not null  | Primary key 
     firstname        | varachar(50)    | not null  | Student's first name
     lastname         | varachar(50)    |           | Student's last name
     accademic_year   | integer         |           | The year when the student graduates
    And you should also indicated foreign key relations between the table.

    Things like this are normally done using an ER (entity relationship) diagram.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Each table is supposed to have a clear meaning in the real world.

    For example, the Student table might mean "There is a student whose id is "id", whose first name is "First name", whose surname is "Surname", who is addressed by "Title," who will graduate in "Academic year", who is a citizen of "Nationality" with a passport id of "IDNumber" and who was born on "Date of Birth."

    It's long and tedious, but it makes it more clear what you're trying to say.

    What would an entry in "Related Keys" actually mean?

  6. #6
    Join Date
    Jul 2009
    Posts
    168

    related keys table

    sorry about that, yea that should be termed 'table' instead of db. I wanted to show the keys in my 'related keys' table so that for example if a student has a number of postal addresses say, then if i indicate the foreign key in the student table, i would have to repeat attributes entries like 'firstname', 'surname', etc right? Isn't this kind of relation called the fourth normal form as explained here:

    Page 4 - Database Normalization And Design Techniques

    so the real world meaning of say the first record of 'related keys' table would then be:
    A student has studentid of 'studentid' with postal address id 'postaladdressid' and residential address id of 'residentialaddressid' ....

    which then becomes after a search is made for that 'studentid', 'postaladdressid', 'residentialaddressid', etc in their corresponding tables:

    a student of student id 'studentid' with first name 'firstname' and surname 'surname' addressed by title 'title' .... has postal address of 'postalAddress' and postal address code of 'postaladdresscode' living at 'residentialaddressid', etc

    Is that correct guys or am i missing something very crucial there? I mean in this way there is no duplication of data except the keys in the 'related keys' table where i try to link up everything. Suppose i have 2 postal addresses for a student, then if i put the postal address id as foreign in the student table, wouldnt it be a problem in the sense that i would then have 2 records of the same student but with 2 different postal address ids? that would mean duplicating attributes such as 'firstname', 'surname' etc right?

  7. #7
    Join Date
    Jul 2009
    Posts
    168

    keys inserted

    hi guys,

    I have modified the design not wanting to risk going into the 4th normal form. I've made the studentID foreign to all tables. I think the designed is atomized in this way with no duplication possible. Is it correct?
    Attached Files Attached Files

  8. #8
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by kpeeroo
    I have modified the design not wanting to risk going into the 4th normal form.
    Why would that be a problem?

    I've made the studentID foreign to all tables. I think the designed is atomized in this way with no duplication possible. Is it correct?
    Can you generate some example data?

  9. #9
    Join Date
    Jul 2009
    Posts
    168

    my designed improved version

    I have not yet built the db in mysql. I wanted to design it properly first because it could be a large db we are dealing with. I have worked on it further and I think this is one of my final drafts.

    Now I will start with the building of the db on sql. I would post out the example data for the sake of some examples. Any idea or suggestion on my design is welcome in the meantime.
    Attached Files Attached Files

  10. #10
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I wanted to design it properly first because it could be a large db we are dealing with. I have worked on it further and I think this is one of my final drafts.
    It's impossible to tell from what you've posted, but I suspect you're not designing it properly and you're not much further than when you first posted.

    It looks like you're trying to make version 1 do everything and be perfect. I think you'd should try to get just a few tables working fully. Once that small portion can do add, update, delete and some useful queries, you'll have a better understanding of what the normalization thing is really about.

    Quote Originally Posted by kpeeroo
    I have not yet built the db in mysql.
    You don't have to have the system running to write down a few rows of example data for each table.

    You also don't have to have a working system to write out questions you're going to ask and translate those to actual SQL SELECT statements.

    For instance, how would you answer "How many students living on-campus will be graduating this year?"

    Really, a database is a question and answer machine. That's the way to really understand it and think through the problem fully, not this mystical puzzle of writing out table names and column names and trying to divine the most normalized form.

  11. #11
    Join Date
    Jul 2009
    Posts
    168

    example data

    great post scooby. that's what i was searching for, ie, suggestions on how to proceed. Yeps think u r right. i will drop in some example rows for you to check out as well so you can ask some questions to the db. but what ve been doing is creating the backbone, i know am bit maniaco on the topic

    but most tutorials ve read seem to do that first, which i followed, like the one posted earlier. anywayz, am doing the tables in mysql now n i'll show what i come up to.

  12. #12
    Join Date
    Jul 2009
    Posts
    168
    ok i have put data in my tables Students, Academics and Courses. I have been able to query things like how many students were in academics year 2 in 2008? it gave me the correct results. I also questioned the db with how many students are doing the course BBA? correct results were obtained.

    I am using visual developer 2005. I dont know how to post the tables with data and its database diagram. any idea how? but it does work..!

  13. #13
    Join Date
    Jul 2009
    Posts
    168
    can i have two foreign keys in a table A with different names but referring to the same primary key of another table B?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kpeeroo
    can i have two foreign keys in a table A with different names but referring to the same primary key of another table B?
    yes

    teams
    teamid
    teamname

    games
    gameid
    gamedate
    hometeamid FK to teams
    awayteamid FK to teams
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jul 2009
    Posts
    168

    FK and INNER JOIN

    how would you do a join select statement so that we get both hometeamid and awayteamid? I am using Visual Web Developer but when the statement is created with both FKs in the join statement, no results are returned. I have to do two separate calls:
    Code:
    SELECT     DataTypes.name, Student.firstName, Types.name AS Expr1
    FROM         DataTypes INNER JOIN
                          Types ON DataTypes.dataTypeID = Types.dataTypeID INNER JOIN
                          Student ON Types.typeID = Student.internetAccessID
    This call does not work: returns no results

    Code:
    SELECT     DataTypes.name, Student.firstName, Types.name AS Expr1
    FROM         DataTypes INNER JOIN
                          Types ON DataTypes.dataTypeID = Types.dataTypeID INNER JOIN
                          Student ON Types.typeID = Student.relationshipID AND Types.typeID = Student.internetAccessID

Posting Permissions

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