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

Introduction to database technology, 1. Create the tables for the play-sche...

1. Create the tables for the play-scheme database as specified in the TMA. 2. Enter the data as given in the spreadsheet. 3. Create queries to retrieve specified information

Differentiate between b-tree and b+ tree, Differentiate between B-tree and ...

Differentiate between B-tree and B + tree    Ans:  A B-tree (or Balanced Tree) is a search tree along with additional constraints that makes sure that the tree is all the time

Fragmentation, let us consider a table having 2200 records and the fixed si...

let us consider a table having 2200 records and the fixed size of the table to be considered as 25.then how many numbers of table is requredto perform horizontal fragmentation

Explain the equijoin, Explain the θ-join and equijoin Refer tables Car ...

Explain the θ-join and equijoin Refer tables Car and Boat which list models of cars and boats and their relevant prices. Assume a customer wants to buy a car and a boat, but sh

MS Access 2010 - Form Updates Table, Hi there, Have an issue with my Databa...

Hi there, Have an issue with my Database. I have: TblOrderDetails and FmOrderDetails TblCircuit and FmCircuit Whenever I add data into the Form, it will update the table as expect

Determine the operation that do not participate to be union, 1. Which relat...

1. Which relational algebra operations do not need the participating tables to be union-compatible? Ans: Join is the relational algebra operations that do not need the particip

There are two major types of fragmentation, There are two major types of fr...

There are two major types of fragmentation: Horizontal& Vertical. Horizontal fragments, as the name suggests are subsets of tuples and vertical fragments are subsets of attributes

What are the desirable properties of a decomposition, Define What are the d...

Define What are the desirable properties of a decomposition Ans: What are the desirable properties of decomposition - dependency preserving.  Because Lossless join and dependen

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