Social Buttons
  1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Creating a Entity Relationship

Discussion in 'Databases' started by peterZweschert, Jun 4, 2020.

0
  1. #1
    I need to create an ER-MODEL (Entity-Relationship-Model) managing Projects in a company. Therefore, the following data needs to be stored as the following:

    Data about the employees: Surname, name and title

    Data about the customer: name, phone number, branch, status, E-Mail
    SEMrush
    Data, that needs to be noted down when contacting the customer: date, description, name of the employee that conducted the interview, duration of the interview (in minutes), type of contact (or contact mode)

    Data for categorizing the customer: type of contact (contact mode) e.g. by phone, mail, meeting

    Data to differentiate the customer in different branches: e.g. automotive, steel, groceries

    Data about the appointments with the customer: topic, date, time, customer, employee and details

    Furthermore, I should avoid redundancy, depict primary and foreign keys and consider the third normal form

    Please find my first try attached. I am not sure if I can do the ER Model like this. I would be very thankful if you could give me some advice how to make it better, or how to redraw the ER Model.
     

    Attached Files:

    peterZweschert, Jun 4, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,867
    Best Answers:
    108
    Trophy Points:
    665
    #2
    Is this a real project or an assignment. There are additional tables that I'd add if you're doing this for real.

    As for the styling of the ER model, if it's real then I'd do just list the columns rather than putting them into ovals. If it's an assignment then you need to follow the style you've been taught in class.

    Some comments:
    Customer
    should have a parent id so that the hierarchy in a company is noted.
    "Company" should be able to have multiple addresses - showroom, manufacturing, service may occupy an entire block and have different delivery addresses.
    Company should also have their own staff. They can be stored in your Employee table and linked to the Company.

    I use "tags" in lots of my tables to allow users to create ad hoc but searchable categories in their data.
     
    sarahk, Jun 4, 2020 IP
  3. peterZweschert

    peterZweschert Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Thank you for your respond sarahk. I should have mentioned that this is an assignment. What do you mean by "parent id" for the customer, and how could I indicate that in the ER Model? And if I got it right, you mean I should create a new entity called "company", right? I am not sure about that, because it's not mentioned in the assignment, so should I still do it?. All I got is the information above.

    At the end I should use this ER Model and implicate it in MySQL and use it as follows: For a mailing campaign, list customer names, telephone numbers, e-mail addresses and the complete industry sector description. Assign the following column titles "Customer", "Phone", "E-mail" and "Industry". Select only those data records whose mail address ends with ".com". Sort the result by the customer name in descending order. Furthermore I should than
    list the project names, customer names, industry names, names of employees responsible for the project and the date the project ends.
    Also list records in which customers have not been assigned an industry.

    List customer names, the full industry name and status. Assign the following headings to the columns: "Customer", "Industry" and "Status". List only prospects and sort the result by customer name in descending order.
    Display only customers who have not yet been assigned an industry sector.

    Sorry for the long text, I just wanted to make it as clear as possible. This is what I have to do after creating the ER Model, so the Model should be best suited for this situation I think.
     
    peterZweschert, Jun 5, 2020 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    26,241
    Likes Received:
    3,867
    Best Answers:
    108
    Trophy Points:
    665
    #4
    Hi

    Company and Customer are essentially the same thing, but given this is an assignment I'd stick to the words used in the assignment.

    parent_id is so that you can have a customer like 3M with branches in Berlin, Munich, Cologne etc and each branch could have a different industry.

    log data should have a foreign_id for project that can be null

    you can make things like address be part of the customer table, your design doesn't have to mirror the real world.

    ================
    I am probably out of date with current thinking but this is what I expect from an ER model

    [​IMG]
     
    sarahk, Jun 5, 2020 IP
  5. peterZweschert

    peterZweschert Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #5
    Thank you! It was so helpful
     
    peterZweschert, Jun 9, 2020 IP