How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask TheDoctor Your Own Question

TheDoctor
TheDoctor, Professional w/Adv. Degree
Category: Homework
Satisfied Customers: 1429
Experience:  M.S. in Internet Information Systems
Type Your Homework Question Here...
TheDoctor is online now
A new question is answered every 9 seconds

Creating a physical ERD!!!

Resolved Question:

Attachment: 2013-06-10_022146_wk4ilabpart1.docx

I was wondering if anyone could help me create a physical ERD (Fully-Attributed data model) for the ECS case? I have the previous Milestone solution.???

Submitted: 1 year ago.
Category: Homework
Expert:  Josie-Mod replied 1 year ago.
Hello,

I'm Josie and I'm a moderator for this topic.

We have been working with our professionals to try to help you with your question. Sometimes it may take a bit of time to find the right fit.

I was checking to see if you had already found your answer or if you still need assistance from one of our professionals.

Please let me know if you wish to continue waiting or if you would like for us to close your question?

Also remember that JustAnswer has a multitude of categories to help you with all your needs from Pet to Legal.

Thank you,

Josie~Moderator
Customer: replied 1 year ago.

I still havent gotten a response for the question i asked last night. Ill still wait because i need help with it. Jatechexpert helped me last with this kind of question, if he can help that be great because i need it.

Expert:  Josie-Mod replied 1 year ago.

Thank you for your reply. Your requested professional is unable to assist at this time.

 

We will continue to look for a professional to assist you. Please let me know if I can be of any further assistance while you wait.

 

Best,

Josie~Moderator

Customer: replied 1 year ago.

Well it says hes online, if i ask him and he responds can i do that?

Customer: replied 1 year ago.

Well this is the longest i have had to wait for something like this. Jatechexpert helped me out last time with this type of question and he did great. I just need help about this today at the latest. Thanks.

Expert:  TheDoctor replied 1 year ago.

Hello and thank you for using JustAnswer. It would be my pleasure to assist you with this.

Are you required to use Visio, or can you use other Database Modelling programs?

 

There are better tools for physical ERDs than Visio. Does your course require Visio? Most courses state that you can use any Modelling or drawing program.

 

Looking at your logical ERD, you did a good job. I do see some issues around the Equipment tables. I will describe them to you in detail once I hear back from you about the modelling tool and finish the physical model.

Thank you

Expert:  TheDoctor replied 1 year ago.
Hello again!

You can download the physical ERD here:

Attachment: 2013-06-10_193956_erd.docx



Notes on the changes to the database design:

In your logical design, you broke the equipment up into four tables. In Visio, you made a mistake that ended up labeling the equipid on the equipment table as a pK/fk. It is only a PK.

You then, in the other four three tables, have the equipid as the pk/fk. This is not done accurately. If attributes rely solely a primary key, then those attributes should be within the table where that primary key lives. What you have done is create three identifying relationships to simply-key strong entities. Do so does not increase the normal form. It simply increases the number of tables. It does not follow the concepts of a relational database and actually poses a significant efficiency issue to a database if this were the real world.

All of the fields in the three other tables can be consolidated into the equipment table. Since these are not weak entities, there is absolutely no reason to put them in separate tables. You may be thinking that they are in different tables because not all equipment will have an EquipBin. However, that is not the correct way to design such a paradigm. If there are fields that rely solely on the primary key of the equip table (as is the case here), and the equipment may not have that field, then you make the field nullable. You do not put it in a separate table.

With all these fields consolidated into the table, it is still in third normal form, does not violate any rules, and is extremely more efficient. Now, if you wanted to a list of all equipment that has an EquipBin, you could simply do:

<i>SELECT equipment_id FROM Equipment WHERE equipment_bin IS NOT NULL;</i>

That is an extremely fast and efficient query. The way that you had it, you would need to do:

<i>SELECT e.equipment_id FROM Equipment e JOIN Untracked_Equip u ON e.equipment_id = u.equipment_id;</i>

This query is at least 100% less efficient. And, as I have mentioned, it in no way increases or follow the rules of normal form. In a good database design, these extra tables would not exist. The design I am sending you is in at least Third Normal Form (3NF), without creating any arbitrary tables or superfluous identifying relationships.

Next you will notice that I changed some of the design around Employees and Skills. You had it so that an employee could have exactly one skill, yet equipment could be used by many different skill sets. This really does not make any logical sense. A person can have many different skills. In fact, a human being can have more skills than a piece of equipment has. Therefore, I created a weak entity to facilitate a many-to-many relationship between employee and skills.

Finally, I changed some of the logic around your purchase requests and purchase orders. The way you had it, a purchase request and order could be created for a single piece of equipment. If an order needs to be placed for five different types/pieces of equipment, it would need five different purchase orders.

I've dealt with many purchase orders in the real world, and I have never seen one that permits you to input only a single type of equipment. Yes, the database should track each equipment ID. But many different equipment IDs should be permitted within a request/order.

To accomplish this, you will see that there are new tables for request items and purchase order items. This allows individuals to request/order many different equipment within a single request. The actual purchase order may not contain the exact same items or quantity as the purchase, or it may. For instance, an individual may request three wrenches and five hammers. The actual purchase order may be exactly the same, or may be two wrenches and five hammers. It depends on the situation and the quantity on hand.

Finally, I wanted to mention the weak entities. Equipment_skill an EmployeeSkill are both weak entities. A weak entity is one that facilitates a many-to-many relationship. These types of entities have identifying relationships. This means that they have a compound primary key, where the partial keys are foreign keys to the primary keys of the strong entities to this entity is facilitate the many-to-many relationship for.

Identifying relationships are signified by solid relationship lines instead of dashed. You will notice that such relationships only really exist when you are dealing with weak entities. You were actually creating an identifying relationship between equipment and those tables that you didn't need. Creating an identifying relationship between two strong entities (what you were doing) is always indicative of a design issue.

Let me know if you have any questions. I'm here to help.

If you ever need assistance in the future, please feel free to request me directly by starting your questions with **For TheDoctor**.

Please remember to rate my answer. Thank you so much and have a wonderful day!
Expert:  TheDoctor replied 1 year ago.
Hello again!

Please let me know if you are required to use Visio. I used a different tool to create this physical design. If you are required, per your course, to use Visio, I can certainly move the diagram over. However, it will not have as much information as the diagram I created.

Let me know. Thank you.
Expert:  TheDoctor replied 1 year ago.
THIS ANSWER IS LOCKED!

You need to spend $3 to view this post. Add Funds to your account and buy credits.
TheDoctor, Professional w/Adv. Degree
Category: Homework
Satisfied Customers: 1429
Experience: M.S. in Internet Information Systems
TheDoctor and 6 other Homework Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Last | Next >
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
  • He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX
 
 
 

Meet The Experts:

 
 
 
  • Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4520
    More than 5000 online tutoring sessions.
< Last | Next >
  • http://ww2.justanswer.com/uploads/BU/BusinessTutor/2012-2-2_115741_Kouki2.64x64.jpg Manal Elkhoshkhany's Avatar

    Manal Elkhoshkhany

    Tutor

    Satisfied Customers:

    4520
    More than 5000 online tutoring sessions.
  • http://ww2.justanswer.com/uploads/LI/lindaus/2012-6-10_04811_IMG20120609164157.64x64.jpg Linda_us's Avatar

    Linda_us

    Finance, Accounts & Homework Tutor

    Satisfied Customers:

    3121
    Post Graduate Diploma in Management (MBA)
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Engineer

    Satisfied Customers:

    3035
    Expert in Java C++ C C# VB Javascript Design SQL HTML
  • http://ww2.justanswer.com/uploads/lanis/2009-4-1_233717_phput9xef_c1pm.jpg Lani S.'s Avatar

    Lani S.

    Tutor

    Satisfied Customers:

    2457
    Registered Nurse, Internet Researcher, Private Tutor
  • http://ww2.justanswer.com/uploads/chooser77/2009-08-18_162025_Chris.jpg Chris M.'s Avatar

    Chris M.

    M.S.W. Social Work

    Satisfied Customers:

    2341
    Master's Degree, strong math and writing skills, experience in one-on-one tutoring (college English)
  • http://ww2.justanswer.com/uploads/JawaadAhmed/2009-6-27_12137_SIs_SHadi.jpg F. Naz's Avatar

    F. Naz

    Chartered Accountant

    Satisfied Customers:

    1975
    Experience with chartered accountancy
  • http://ww2.justanswer.com/uploads/JK/jkcpa/2011-1-16_182614_jkcpa.64x64.jpg Bizhelp's Avatar

    Bizhelp

    CPA

    Satisfied Customers:

    1873
    Bachelors Degree and CPA with Accounting work experience