MIS3306 Database Management Systems Module 4 Exercise

Use Microsoft Visio or Draw.io to draw the diagram. Diagrams made by any other tools will not be graded.

· Use IE (crow’s foot) notation. Diagrams with any other notations will not be graded.

· The diagram should specify entities, keys, attributes, relationship types (identifying or non-identifying), and cardinalities (MAX and min on each side). Divide an attribute further into simple attributes when appropriate. All many-to-many relationships, if any, should be converted into associative entities (the answer should not have many to many relationship). Relationship labels are not needed. Grading is based on the completeness of the diagram.

· Add a textbox with your full name inside the diagram (no point without your name). The figure below is an example. (Note: For this exercise, attributes are not needed.)

· Your answer is required when you see the red answer box like the box below.

Answer here:

<<This is an example. Answer whenever you see this.>>

· Upload this document to Blackboard as your submission.

Questions and Answers:

1. The Department-Employee Database (12 points) A department-employee database should meet the following business requirements. Use the following descriptions to create a Crow’s foot ERD.

· A division is identified by its division ID. Division name is needed. A department is identified by its department ID. Department name is needed. An employee is identified by the employee ID. Employees’ first names and last names are recorded. A project is identified by a project ID. Data about project name is needed.

· A division operates several departments, and each department is operated by one and only one division. One of the employees runs each division, and each division is run by only one employee.

· A department has many employees, and each employee is employed by one (and not more than one) department. Some employees do not belong to any department. Each department must have one employee as the manager. An employee cannot manage more than one department.

· An employee may work on no project or many projects. A project at least should have one employee assigned to it and normally may have several employees assigned to it.

Paste Your Answer Diagram Here:

2. The Invoice Database (12 points) You are building a database for an apparel company that sell designer clothes and accessories to clothing stores. The requirements are listed below:

· An invoice is written by a single sales representative (SALESREP). Each salesrep can write many invoices.

· Each customer can have many invoices. An invoice must have a customer on it.

· An invoice includes one to many detail line items (LINE). A line item describes one product sold to the customer and its quantity. (Several products can be included in one invoice.)

· Each product must have one vendor, and each vendor may supply several products. Some vendors’ data is still needed even the vendor does not sell any products to your company.

· An ID is needed for each entity. Data needed in the database includes (separate the attributes into smaller attributes when appropriate):

· A sales representative’s first name and last name,

· A customer’s store name, address, and phone number

· A vendor’s name, address, and phone number

· Invoice total amount, date

· A product’s name and description

Paste Your Answer Diagram Here:

3. The Training Database (14 points) You are asked to design a database for your company’s training program. There are in total 8 instructors and 5 different courses. Because of classroom capacity and different schedule needs, each course may have several class sections. Each instructor is responsible for up to five classes, and each class is taught by only one instructor. A class can handle up to 25 trainees per class. A trainee may register up to 5 classes at the same time. A class will be canceled if it has fewer than 5 trainees.

An ID is needed for each entity. Other data needed in the database includes (separate the attributes into smaller attributes when appropriate): instructor name, instructor e-mail, course name, course description, class name, class schedule, class capacity, trainee name, and trainee e-mail. Also, a grade for each trainee on each class should be recorded in the database.

Paste Your Answer Diagram Here:

Review

1. (2 points) Choose among the learning objectives of this module listed below and answer the following questions.

· Identify entities and attributes

· Describe how relationships between entities are defined, refined, and incorporated

· See how ERD components affect database design and implementation

a. Which learning objective have you learned the best? Why?

Answer:

b. What hands-on skill(s) have you learned? (answer like: I can use ______ to ______. )

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.