Represent the following scenarios with E-R diagrams drawn using the ER Assistant software. The diagrams should indicate cardinality and optionality of relationships. Transform each diagram into a set of corresponding BCNF relations indicating primary and foreign keys.
1. The employee database stores data about employees and related information. There are three types of employees identified by employee number (EMPNO): salespeople, developers, and secretarial staff. Employees identified by employee number (EMPNO) work in departments identified by department number (DEPTNO). Each department must have at least one employee working in it; employees must belong to a single department. Employees may manage other employees; each employee (except the CEO) has a manager identified by MGRNO. The following information requirements have been identified during analysis for the employee database:
EMPNO employee number
ENAME employee name
SAL employee salary
COM employee commission (applies only to salespeople)
DEPTNO department number of the department employee works in
DNAME name of the department employee works in
MGRNO employee number of the manager of the employee 2
2. Each client identified by a client number (CLIENTNO) who applies for a loan is assigned to one bank loan manager identified by employee number (EMPNO). Each bank loan manager may have many clients. The bank loan manager assists the client to complete a loan application identified by a unique application number (APPNO). Each loan application must have a single client who is a primary applicant, and may have one or more clients who are secondary applicants. The loan amount (LOANAMT), date of the application (APPDATE), income (INCOME) and expenses (EXPENSES) of the primary applicant are recorded on the application. Each application must have at least one (but may have several) guarantors, who must also be clients of the bank. The total assets (TOTALASSETS) are recorded for each guarantor. Clients who are applicants (primary or secondary) on one application may be guarantors on another application, and vice versa. But the same client cannot be both applicant and guarantor on one application. The following information requirements have been identified during analysis:
CLIENTNO client number
CLIENTNAME client name
CLIENTADDRESS client address
EMPNO bank loan manager employee number
EMPNAME bank loan manager employee name
APPLNO loan application number
APPDATE date of application
LOANAMT loan amount
INCOME income of the primary applicant
EXPENSES expenses of the primary applicant
TOTALASSETS total assets of a guarantor