Database Systems Term Project

ABC Training Center

ABC is a training center that conducts various training courses. Each course is identified by a course no$,$ title, units, level, and subject. Courses are conducted by instructors, who can be full time or part time. Instructors are identified by an identifier, name, mobile phone number, email, address comprising of street name, city and country, and specialty. An instructor's employment status is determined at the time of appointment. Part time instructors work a number of hours each month and are paid according to a pay rate, which must be greater than $20,$ determined by the training center. Full time instructors have monthly salaries. In certain circumstances, full time instructors may be assigned additional training courses besides to their training load and they are paid as part time instructors. An instructor can train a group of students in one course for each training period. The same course may be conducted by several instructors at the same training periods. Each year is divided into

$4$ training periods. The starting and ending dates describe a training period. A student, described by an id$,$ student number, name, address, at least one email, and age, may participate in up to $3$ training courses per period. Students' information may be $.$queried using first and last names

:The project consists of four phases

:Conceptual Design Phase $\mathrm{.}1$

In this phase, you are to develop an Enhanced EntityRelationship $($EER$)$ diagram using UML notation for the database based on the information provided. If there are ambiguities, you must resolve them and state your assumptions. The EER diagram must conceptually model the data $.$requirements of the enterprise

Logical Design Phase $\mathrm{.}2$

In this phase, you must map the EER diagram into a set of relations with minimal nulls. Show primary keys and identify foreign keys

Relations Normalization Phase $\mathrm{.}3$

In this phase, you must normalize your relational database $.$design to the $3NF$

Database Implementation Phase $\mathrm{.}4$

In this phase, write SQL DDL statements that can be used to create the tables for the relations you obtained from phase $(3).$with sample data $($an average of $3$ rows per table$)$ Write an SQL statement to list all instructors who are full time $.$and worked on a part time basis in alphabetical order of names

Write an SQL statement to list all student names and their $.$completed courses