Blog

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.

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

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 teaching in 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. If an instructor does administrator works, s/he will not have teaching load. A class can handle up to 25 trainees per class. 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.

(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 ______. )

Answer: