Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Cool Need help for creating a SQL DataBase

    Hello everybody, my name is Andy,

    I need help for creating a database, it's an exercise for my school, we just began to learn SQL, about entities, relationships (one to one, one to many...), creating tables, but the thing is i don't know where to start, i think i need to practise but it's hard to transform a text into a database, i think i need some help from you guys and i hope you will help me.

    I give you the exercise, and what i found :


    Project wants users to be able to initiate debates, post articles and get to the achieved items. A user should be able to write as many articles as desired after having register with login and passport

    However articles should contain these references for more http links, URL, summary of the content; 1 or more books, article, doc, case studies …

    1)Identify the data objects and relationship and draft the initial ER Diagram with entities and relationships. Normalize model as far as you can

    2)write the SQL query / link (url) and corresponding summary

    About the 1)
    Entities : users --> login and password associated (one to one relationships one user = one login/password)
    Articles --> associated with the users accounts (one to many)

    I am not sure for creating the different tables, im a bit lost... :s

    About the 2) totally lost, i need the 1) to make the SQL Query (i know a bit about queries)

    I hope you will help me, i am a beginner in SQL (only one course taken) and it is our first exercise, thanks for your help all!!!

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    best advice I can give at this point is to look through your information on ER diagrams. I think that is a good starting point. You should be able to make a diagram even if it is not good

    Don't worry about the queries to much until you have the tables ready.

    Then ask specific questions about things you don't understand.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Nov 2007
    Posts
    8

    Lightbulb

    I've tried this ER diagram but i am sure its wrong and i am missing things :s

    About the query iam not sure too!!!

    Please help thanks a lot !
    Attached Thumbnails Attached Thumbnails SQL.JPG  

  4. #4
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    ok, I notice you've got a lot of *_id fields on the entities, but you haven't mentioned IDs in your first post at all.

    What example ER diagram are you referencing? hehe.. I don't work with people on this much so I am not great at helping without showing you which is no good here. I will recommend this.. redo this diagram without referencing it but first write down all the pieces of data that you want to keep. Just write the names down on a list. Now take those and make your entities like User and give their pieces of data to them. The goal here is to figure out what you want to store and who it belongs to.

    all of this is information you mentioned:
    Project wants users to be able to initiate debates, post articles and get to the achieved items. A user should be able to write as many articles as desired after having register with login and passport

    However articles should contain these references for more http links, URL, summary of the content; 1 or more books, article, doc, case studies …
    ill give you a little 'push' I guess... in that diagram you showed a login entity and a users entity. Users is a noun and login is a verb. -Usually- only nouns are entities. Users login to Document (the system). So there isn't really a login entity and therefore password can't belong to something that doesn't exist. so... what does password belong to?
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  5. #5
    Join Date
    Nov 2007
    Posts
    8
    I think the password belongs to the login.

    A user_id is a login
    A login match a password

    So i have to create a user table with login_id with the password?

    So first i want :

    -The users' login with their password
    -The articles must be stored with their proper users
    -Articles can contain references (http links, URL, summary of the content; 1 or more books, article, doc, case studies)--> ??
    -Articles must be viewable by all --> don't know how to do with that one

    I am redoing my ER diagram, so i create only one table named User containing the login & password, there is still the documents table, but i have to do something about the documents references and the fact that they must be viewable by all, gimme 15 min i am working on it!

    Again and again thanks a lot for your help

  6. #6
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    hmm, are you creating a program or just worried about the database. I notice your descriptions seem to read out like use cases for programmers hehe. If you only have to design the db then just make it about the data and I encourage getting away from the process of how they will do 'stuff'.

    you say the password belongs to the login, but what is a login? doesn't the password belong to a person which is user that CAN login (an action)? a 'login match' is a program level check against two pieces of data and not something that is stored. yes, I would suggest a users table with user information like username(login_id) and password.

    So if a user is an entity then what other things do you have that are entities that you need to store information about?

    It might help (although tedious) if you make up some fake data and try to 'store' it on paper to see how they relate to each other. You might start to notice repetitive patterns that make it easier to break apart.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  7. #7
    Join Date
    Nov 2007
    Posts
    8
    Yeah by login i wanted to say username, in french login = username not in english sorry

    I just need to make the ER diagram of this database it's not a progammer stuff, i don't have any software it's just to understand the way of creating a database

  8. #8
    Join Date
    Nov 2007
    Posts
    8

    Lightbulb

    Am i on the right way?

    i just need now to translate in SQL two more things :

    -Articles must be viewable for all
    -Documents can contain references url, books....
    Attached Thumbnails Attached Thumbnails SQL.JPG  

  9. #9
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    oh ok no problem. people use login here as well but it could be a noun or verb hehe. it can be confusing.

    ha.. I had to put this in a translator :P... "Je vous aime mon petit chou" that is all someone taught me how to say in french (poorly).
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  10. #10
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    'articles must be viewable by all' is a programming implementation really. you just need to store articles.

    Are article = documents? Documents sounds like an entity to me, but it all still seems fuzzy to me. I am not sure exactly what you want to store. Go ahead an write out with fake data what you intend to store. Just make an example for you to reference.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  11. #11
    Join Date
    Nov 2007
    Posts
    8

    Lightbulb

    For me i create a table documents which contains all the articles written by the users.

    More over an articles can contains references, so i create a table named references with references_id.

    I make an example

    User one to many Documents many to many References
    Username : franck Article about SQL www.wikipedia.org
    Password : dottie


    I've tried another ER diagram in attachment
    Attached Thumbnails Attached Thumbnails SQL.JPG  

  12. #12
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    ok, so your users table will look like:
    Users
    franck, dottie
    sam, sottie
    pam, pottie
    wham, whottie

    can you draw out an example like that for your documents entity and your references entity? When you make your ER diagram you should fill out ALL the information you are going to store. Where are those URLs going to go? where are the book references going to go? where are the summaries going to go?
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  13. #13
    Join Date
    Nov 2007
    Posts
    8
    Documents
    Article SQL
    Article on Sustainable Development
    Article PQL
    Article LQL

    However articles should contain these references for more http links, URL, summary of the content; 1 or more books, article, doc, case studies

    About references i thought by creating a table named Reference i could store my URLs, summaries, books, articles, docs & case studies or do i have to create a table for each type of reference ??, huh quite lost there :s


    Again, i want to say i BIG thanks for your help!!!!! For taking time to explain me it's really nice,

    In french : Merci beaucoup, c'est très gentil de m'aider et de prendre le temps de m'expliquer, c'est vraiment sympas!

  14. #14
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    hmmm.. so a document is:

    written article by a user
    html link
    URL (is this different form html link?)
    summary
    1 or more books
    doc (what is this?)
    case studies (can you describe this more?)

    you could make a references table, but if all of those pieces of data are directly related to an article, which seems to me to be your primary key, then you could put them in the document table. Now, having said that, there are some things that would be broken out of that table through normalization. One obvious one is book. Since you can have 1 or more books related to a Article then you probably want a 1 to many relationship there.

    Hehe.. I know I am being vague a bit, but I really don't want to tell you how to do it. There are two main reasons: 1) I don't know as well as you do the data you are trying to store and, 2) its always better if you really understand what you are doing.

    Just try to expand out some sample data more. To get an idea of what I mean when I say that:

    Lets pretend I wanted to store information about a person. Well, I would tell myself I need: name, phone number, gender, birthdate, hair color.. and so on. Well to get a good look at what I am trying to store I could make up some fake data and fill it out..

    person
    name, phone number, gender, birthdate, hair color, ...., ..... (these are the columns for the table)
    amthomas, 555-2383, M, 20001123, Brown,...,..

    Writing out data like that I think will give you a better grasp of what you are trying to accomplish. At the very least, fill out your ER diagram further showing which attributes belong to which entities.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  15. #15
    Join Date
    Nov 2007
    Posts
    8
    Thanks,

    Now i've done my ER diagram, now ive got to do some queries this is what i've done :

    2) write the SQL query in order to get the http links (URL) and corresponding summary of a given article

    table name = article
    Select articleID=13456
    From article
    Where url=”http://www.website.com/articles/monarticle.html” AND summaryID=13456

    3) write the SQL query in order to list the number of articles that have been published.

    Select articleID=*
    From article
    Order by articleID ASC ;

    4) write thee SQL query in order to know who published what
    Select userID, login
    From article
    Where articleID =13456;

    Am i true???

Posting Permissions

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