Database management

User Generated

FGNYYVBA

Computer Science

Description

For assignment 3, please pay attention to detail. You will need to show/list all PKs, FKs (including parent table names and column names), weak entities (if any), partial keys, and relationships (strong vs weak + 1:M, M:1, or M:N).

Unformatted Attachment Preview

Assignments An Employee Payroll System We plan to develop an employee payroll system for a certain company. We have gathered the following requirements: For each employee, we must record the name, the resident address and home phone number, and the Social Security Number which actually will serve as the unique Employee id number for each employee. • Every employee of the company is paid a monthly salary. • An employee is paid every month. If the employee gets a pay raise, the new salary becomes effective immediately on the work day, and the salary for that month is then computed accordingly, in proportion to the number of days for which the salary was effective in that month. The database must also keep track of the employee's salary change history. A new employee should have his/her salary change history indicating the starting salary and date. • In addition to the regular monthly salary, some employees may also get a commission pay when a customer order comes in by their referral. For each sales order an employee helped by making referral , the employee gets a commission pay, as a percentage of the total sales amount on that order. • A Sales Order may be involved with any number of employees in making referral, and an employee may also make referrals for more than one sales order. Commission - in terms of a percentage of the total sale amount on that Sales Order - is negotiated in each case by each employee (therefore may be different in each case, per order per employee). • For each Sales Order, we will need the Order Number which uniquely identifies the order, with the customer name, and the total sale amount. Design a conceptual schema for the database, and illustrate your design in an Entity-Relationship Diagram, showing all the entities, relationships, and attributes. Identify key attributes if appropriate, or partial key attributes for weak entity sets. Specify any structural constraints. Hand-drawn diagrams are acceptable, but please make sure that they are readable. (Recommended tool is MS Visio; many others are also good and reasonable.) Translate your conceptual schema into a relational database schema, showing the tables and the columns for each table. Indicate the primary key for each table, as well as any foreign keys. For each foreign key, indicate at the source tables which columns are involved, and name the target table that the foreign key refers to, along with the corresponding columns in the target table.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags