Results 1 to 9 of 9

Thread: ERD Help

  1. #1
    Join Date
    Oct 2013
    Posts
    7

    ERD Help

    Draw the corresponding ERD for the following data structure:
    Products(ProID, Descrip, Cost, Price, CatID)
    People(ID, FName, LName, Phone)
    Clients(CID, CreditLimit)
    Employees(EID, DOH, DOB, SupervisorID)
    ClientAddress(CLID, CID, Street1, Street2, City, State, Zip)
    PriceHistory(PrID, ProductID, Price, StartDate, EndDate)
    InvDetail(InvNum, DetID, Qty, ProdID, UnitPrice, Discount)
    InvHeader(InvNum, Date, ClientID, CliAddrID, SalesPersonID, Memo)
    Categories(ID, Description)
    Underline stands for primary key
    Italic stands for Foreign key.

    I'm confused about the relationships. Any help will be greatly appreciated.
    Attached Thumbnails Attached Thumbnails ERD First Draft.png  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Do you have a specific question? We don't just do student's homework here, but we will respond to well-constructed specific questions.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Oct 2013
    Posts
    7

    Erd

    I don't think I asked anyone to do my homework. That's why I made sure to do it first. I didn't think anything was wrong with posting a finished product and asking questions.

    Well if you could help, I am confused about how to decide which object is a many and which object is a one, many to many, one to many etc.

    I feel you don't feel comfortable helping I totally understand.

    Does an object with a the foreign key always indicate a many object?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Chuck,
    None of us mind helping you out, but as I said on other board you just have to actually pose a question. Now that you have we can give you some answers.
    Does an object with a the foreign key always indicate a many object?
    Most of the time, yes, but it could be a one to one relationship as well. I took a quick look at your ERD and the relationships look pretty well defined with the one to many on the correct tables.

    A couple of items, probably out of your hands and came from your instructor. The people and employee tables, should they really be separate tables? I could see having them separate if your clients were booked in as people, but they aren't. The other one I would take issue with is having the products and the price history. I could see having the price separated and keeping its history, but not the current price in the products and the old prices in the history.

  5. #5
    Join Date
    Oct 2013
    Posts
    7
    OK that makes since, I cant control the separate object tables, I did find them confusing as well.

    The products object has price then you have another price history table. So I see what you mean. I watched so many you tube videos, I started to confuse myself. I was under the impression that every table with a foreign key was a many object, thank you so much for clearing that up for me.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    I just did one last double check on this. Is your relationship from categories to product correct?

  7. #7
    Join Date
    Oct 2013
    Posts
    7
    I related those tables, based off them both having the 'description' as an entity. But now that I look maybe it should be related to the people table, they both have Id as the primary key.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    I don't think so. Looks like you have a category id. You will have a lot of ID's they are not always related to each other as they are an ID for each object. I think you were relating the tables in the right places. I was mentioning the type of relationship you had on those 2 objects in question.
    Dave

  9. #9
    Join Date
    Nov 2013
    Posts
    1

    very very new to ERD can some help to construct one or give the right direction

    "Limerick transport is responsible for running a fleet of buses throughout the MId-Western region. The buses are housed in one of three depots,Ennis, Limerick,and Nenagh. Each depot is identified by it's depot number; in addition,the depot name and address are recorded.
    Each bus is identified its registration number. details of the buses models are also held, for example Ennis/Kilrush. Each route is identified by its route. Only buses from particular depots will travel on a particular route,so for example,only buses from the Ennis depot will travel on the Ennis/ kilrush route. Buses are classified by various types such as minibus, double-Decker, ect. There are restrictions on some bus types for some of the routes, or example, those with low bridges may exclude double-Decker buses for this reason buses are designated to particular routes.
    The bus company employs bus drivers to operate the buses and cleaners who help maintain them.Both the bus drivers and cleaners work at one particular depot.Drivers and cleaners have an employee number,name and salary. In addition the company holds information on the date that the driver passed his/her PCV test.
    For cleaning purposes, the depots are organized with cleaners being responsible for a number of buses; each bus has one cleaner who is particularly responsible for that bus. In the case of the bus drivers, they can only drive buses where they have completed training for the type of bus, and the date when training is completed is recorded.In addition, bus drivers can only drive buses where they have practice on particular routes.
    Due to the recession the company has got involved in tours which it organizes from its Limerick depot. Each tour has a tour name, size,departure date,and length of tour. Staff such as the driver of the bus on tour needs to be recorded.Some drivers act as tour guides on occasion. The tours stop at a number of hotels,which have their,name, address,phone number,standard room rate and number of rooms recorded.The Company need to know which tours are at what hotel for any given day.
    Passengers booked on each tour have their name,contact,address,and phone number and a number of other details recorded. Particulars the company needs to know include whether passengers have any allergies, medical conditions, if they have booked other tours with the company, and if they have paid for the current tour. Passengers who have booked but not paid are not permitted to start a tour

Posting Permissions

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