Entity - relation modeling and transformation, Database Management System

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

Posted Date: 2/22/2013 12:59:04 AM | Location : United States







Related Discussions:- Entity - relation modeling and transformation, Assignment Help, Ask Question on Entity - relation modeling and transformation, Get Answer, Expert's Help, Entity - relation modeling and transformation Discussions

Write discussion on Entity - relation modeling and transformation
Your posts are moderated
Related Questions
What is the use of integrity constraints? Integrity constraints make sure that changes made to the database by authorized users do not result in a loss of data consistency. The

What is inheritance? Define it in detail?  Inheritance Inheritance can be at the levels of types, or at the level of tables we first consider inheritance of types, then i

Illustrate the class diagram for class room scheduling system If in any college, the number of classrooms are limited which have to be allocated to various classes and instruct

Normalize the following table upto and including the 3NF. Submit a 1 page printout of only the final set of normalized tables in Data Architect. Just use Data Architect to do the t

Can you scan double-sided documents - and remove blank pages? Our software and imaging systems supports duplex scanning, at the same time scanning both sides of a page. Program

Define DBMS? DBMS: database management system (DBMS) is computer software designed for the reason of supervision databases based on a variety of data models.

.#question. Discuss the problem which may arise during concurrency control and recovery in distributed database which are not encountered in controlized database environment.


read-set(T) : Each data items that are read by a transaction T Timestamps : for all transaction T, the start-time and the end time are reserved for all the three phases.

Explain the Natural join Natural join is a binary operator which is written as (R* S) in which R and S are relations. The effect of the natural join is the set of all combinati