Entity - relation modeling and transformation, Database Management System

Assignment Help:

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


Related Discussions:- Entity - relation modeling and transformation

Explain the disadvantages of a file processing system, Explain the disadvan...

Explain the disadvantages of a file processing system? Ans: Disadvantages of File Processing Systems include: 1) Data Redundancy 2) Data Inconsistency 3) Difficult to acce

Assign, #quesDefine a job scheduling strategy that will meet business requi...

#quesDefine a job scheduling strategy that will meet business requirement of reporting availability by 6am CST for the following cubes? Show the job scheduling dependencies in a pi

Data base design for a museum information system, In this practical assignm...

In this practical assignment, we will run through a complete database application development cycle, starting with the schema design, from an initial data import to database applic

Balance transfer, Scenario 1: Power goes out after the first instruction. ...

Scenario 1: Power goes out after the first instruction. What happens?

What is called a query -execution engine, What is called a query -execution...

What is called a query -execution engine? The query implementation engine takes a query evaluation plan, implements that plan, and returns the answers to the query.

Explain the reason for keeping legacy system, In order to keep pace with co...

In order to keep pace with competitors, Space Ltd has decided to implement an ERP system in order to replace its old legacy and manual systems. Management does not have much knowle

Explain optimistic locking and pessimistic locking, Explain the difference ...

Explain the difference between optimistic locking and pessimistic locking. Optimistic locking supposes no transaction conflict will occur and deals with the consequences if it

Key constraint - relational constraints, This constraint shows that the key...

This constraint shows that the key attribute value in each tuple must be unique, i.e., no two tuples have the similar value for the key attribute. This is because the value of the

Give an example of multiple inheritance, Give an example of multiple inheri...

Give an example of multiple inheritance.  One example of multiple inheritance is a committee for students affair which include faculty and administrative staff member.

Explain the log based recovery method, Explain the Log Based Recovery Metho...

Explain the Log Based Recovery Method? The system log that is generally written on stable storage consists of the redundant data required to recover from volatile storage failu

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd