Question
. You are asked to design a database system for ABC companys training program. Your model will be used by an application that will meet
. You are asked to design a database system for ABC companys training program. Your model will be used by an application that will meet the expected database requirements for the company.
Business Rules are defined as follows:
- The ABC has 12 instructors and can handle up to 30 trainees per class.
- A trainee can take more than one class, and each class contains many (10 or more) trainees
- ABC offers five Advanced Technology courses, each of which may generate several classes.
- A class is taught by only one instructor, but an instructor can teach up to two classes.
- If a class has fewer than ten trainees, it will be canceled. Therefore, it is possible for a course not to generate any classes.
- Each instructor may teach up to two classes or may be assigned to do research only.
- Each trainee may take up to two classes per year.
Entities required for conceptual modelling: INSTRUCTOR, COURSE, CLASS, TRAINEE.
Attributes:
INSTRUCTOR: instructor number, instructor first name, instructor last name, instructor speciality.
COURSE: course code, course title, course description, course credits.
CLASS: class code, course code, instructor number, class section, class time
TRAINEE: trainee number, first name, last name, email, home phone number, home address (Street, City, Province, and Postal Code).
- Create relational data model. Make sure all the 5 important items are shown (Entities, Attributes, Relationships, Constraints and Cardinalities)
- Show relational participations (optional or mandatory)
- Show all weak and strong relationships
- If conceptual data model has any M:N relationship, modify the ERD by introducing a composite entity (also referred to as a bridge entity or an associative entity).
- A trainee usually has more than one phone number (Home, Work, Mobile, Pager, Fax, etc.). Based on this fact, extend your model by adding PHONE entity. It should have relation with TRAINEE entity. Show relationship type and cardinalities.
- Extend your model by adding PROVINCE entity. This entity should have a relation with TRAINEE entity. Province values in TRAINEE entity should come from PROVINCE entity. Show primary key/foreign key relationship and cardinalities.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started