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
Discuss the differences among the candidate keys and the primary key of a relation. Give instance to describe your answer? A candidate key is one that can be used as primary ke

Given the following set of functional dependencies {cf→ bg, g → d, cdg → f, b → de, d → c} defined on R(b,c,d,e,f,g) a. Is cf→ e implied by the FDs? b. Is dg a superkey?

Explain natural join? Natural Join - Similar as equi-join except in which the join attributes (having similar names) are not involved in the resulting relation. Only one sets

a project based on normalization with first, second and third normalization. There need to be a diagram also

Now that she understands more about the benefits of databases, the play-scheme manager would like to extend the play-scheme database to include data for schools and instructors as

What disadvantage of ODBC does OLE DB overcome? By breaking the features and the methods of a DBMS into COM objects, OLE DB characteristic overcomes a main disadvantage of ODBC

Define 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 pictori

What is known as fudge factor? The number of partitions is enhanced by a small value called the fudge factor, which is usually 20 percent of the number of hash partitions compu

What are the failures of Controlling Events Between Objects  Failure Unintentional termination of system. Several possible causes: failure of system hardware, o