Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
You are required to develop a simple database application for a small recruiting firm that wishes to keep track of all the employees at th firm; storing details about their name, phone numbers, hiredate, Gender, Date of Birth, Salary, and Commission. The firm has many departments and there are 5 to 20 employees in each department. The department information includes department name, description and total number of employees in that department. The company also provides vehicles for some of its employees. An employee mayb allocated one car. To ensure timely maintenance of vehicle, the company would like to store the following details of the vehicle: make, model, next_maintenance_date. Each employee has a position (manager, accountant, administrator, clerk, HR employee, etc.). Various allowances are allocated to each position. For example, the managers have fuel allowance, house allowance, social allowance and managers allowance. On the other hand, the administrators have house allowance and social allowance. Clerks and HR employees have social allowance and uniform allowance. The name and description of each allowance needs to be stored. The database keeps track of all Projects in the organization. Each department is assigned to one or more project. Project can be assigned to one or more department (Interdisciplinary projects). The HR Employee has his own credentials to login into the system to manipulate the database. The credentials are stored in a table (Login). You will also need a table to store transactions (Transaction Date/Time, User Name, Position, and Employee Number). The system must store employees' grades. Each employee will have a grade (Example A, B, C). The grade should have a predefined salary range.

You are required to develop a simple database application for a small recruiting firm that wishes to keep track of all the employees at th firm; storing details about their name, phone numbers, hiredate, Gender, Date of Birth, Salary, and Commission. The firm has many departments and there are 5 to 20 employees in each department. The department information includes department name, description and total number of employees in that department. The company also provides vehicles for some of its employees. An employee mayb allocated one car. To ensure timely maintenance of vehicle, the company would like to store the following details of the vehicle: make, model, next_maintenance_date. Each employee has a position (manager, accountant, administrator, clerk, HR employee, etc.). Various allowances are allocated to each position. For example, the managers have fuel allowance, house allowance, social allowance and managers allowance. On the other hand, the administrators have house allowance and social allowance. Clerks and HR employees have social allowance and uniform allowance. The name and description of each allowance needs to be stored. The database keeps track of all Projects in the organization. Each department is assigned to one or more project. Project can be assigned to one or more department (Interdisciplinary projects). The HR Employee has his own credentials to login into the system to manipulate the database. The credentials are stored in a table (Login). You will also need a table to store transactions (Transaction Date/Time, User Name, Position, and Employee Number). The system must store employees' grades. Each employee will have a grade (Example A, B, C). The grade should have a predefined salary range.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question
Entities: - Employee ename, hire_date, DOB, Phone NO, Gender - Transaction: Date-Time, username, job, empno - Login: ID, password - Grade: salary range - Bonus :Bname, job, Commission, salary - Position: pname -Allowance: aname, description - Vehicle: model, make, next_maintenance - Project: project rw, project Name - Department: DeptName, description, total Empno
Transcribed Image Text:Entities: - Employee ename, hire_date, DOB, Phone NO, Gender - Transaction: Date-Time, username, job, empno - Login: ID, password - Grade: salary range - Bonus :Bname, job, Commission, salary - Position: pname -Allowance: aname, description - Vehicle: model, make, next_maintenance - Project: project rw, project Name - Department: DeptName, description, total Empno
DataBase; You are required to develop a simple database application for a small recruiting firm that wishes to keep track of all the employees at the firm; storing details about their name, phone numbers, hiredate, Gender, Date of Birth, Salary, and Commission. The firm has many departments and there are 5 to 20 employees in each department. The department information includes department name, description and total number of employees in that department. The company also provides vehicles for some of its employees. An employee maybe allocated one car. To ensure timely maintenance of vehicle, the company would like to store the following details of the vehicle: make, model, next_maintenance_date. Each employee has a position (manager, accountant, administrator, clerk, HR employee, etc.). Various allowances are allocated to each position. For example, the managers have fuel allowance, house allowance, social allowance and managers allowance. On the other hand, the administrators have house allowance and social allowance. Clerks and HR employees have social allowance and uniform allowance. The name and description of each allowance needs to be stored. The database keeps track of all Projects in the organization. Each department is assigned to one or more project. Project can be assigned to one or more department (Interdisciplinary projects). The HR Employee has his own credentials to login into the system to manipulate the database. The credentials are stored in a table (Login). You will also need a table to store transactions (Transaction Date/Time, User Name, Position, and Employee Number). The system must store employees' grades. Each employee will have a grade (Example A, B, C). The grade should have a predefined salary range. The system must store information about employee's overtime base on night or day shift, number of working hours, and grade. An employee may or may not work overtime. The calculation of overtime is based on Hourly pay rate per grade * Number of working Hours * Day or Night Shift. The maximum number of hours for overtime is 8 hours. Night time shift pay is twice the Day time shift pay. The ourly pay rate is based on the Grade. Example (Grade A receives $20 Grade B Receives $150 and So on). You need to have the following constraints in your system: Primary and foreign keys. The employee's salary should be between the grade range (Example: Grade A - Salary between 30000-50000). Employee's phone number, Hiredate, Date of Birth should not be empty. The age should be between 18 and 60. Vehicle next maintenance is within 3 month of current date. Each position should not exceed the predefined allowances. The System will need a constraint for Gender ('M','F'). The Username must be Unique in Table Login. Mandatory fields must not be NULL. i did already ERD but i need to; a.Mapping ERD to Relational Schema. b. Adding Foreign Keys. c. Resolve any M:N relationship between the entities. Projectno PROJECT Project Name (make model VEHICLE next maintenance ALLOWANCE aname assign provided llocated description description Dept Name total Emmo DEPARTMENT PhoneNo employs (DOB) Gender POSITION (Pname hiredate ename JEMPLOYEE receives enters BONUS has Salary Bname job Commission empno Date Time TRANSACTION manipulare LOGIN Usemama Password GRADE Salary range
Transcribed Image Text:DataBase; You are required to develop a simple database application for a small recruiting firm that wishes to keep track of all the employees at the firm; storing details about their name, phone numbers, hiredate, Gender, Date of Birth, Salary, and Commission. The firm has many departments and there are 5 to 20 employees in each department. The department information includes department name, description and total number of employees in that department. The company also provides vehicles for some of its employees. An employee maybe allocated one car. To ensure timely maintenance of vehicle, the company would like to store the following details of the vehicle: make, model, next_maintenance_date. Each employee has a position (manager, accountant, administrator, clerk, HR employee, etc.). Various allowances are allocated to each position. For example, the managers have fuel allowance, house allowance, social allowance and managers allowance. On the other hand, the administrators have house allowance and social allowance. Clerks and HR employees have social allowance and uniform allowance. The name and description of each allowance needs to be stored. The database keeps track of all Projects in the organization. Each department is assigned to one or more project. Project can be assigned to one or more department (Interdisciplinary projects). The HR Employee has his own credentials to login into the system to manipulate the database. The credentials are stored in a table (Login). You will also need a table to store transactions (Transaction Date/Time, User Name, Position, and Employee Number). The system must store employees' grades. Each employee will have a grade (Example A, B, C). The grade should have a predefined salary range. The system must store information about employee's overtime base on night or day shift, number of working hours, and grade. An employee may or may not work overtime. The calculation of overtime is based on Hourly pay rate per grade * Number of working Hours * Day or Night Shift. The maximum number of hours for overtime is 8 hours. Night time shift pay is twice the Day time shift pay. The ourly pay rate is based on the Grade. Example (Grade A receives $20 Grade B Receives $150 and So on). You need to have the following constraints in your system: Primary and foreign keys. The employee's salary should be between the grade range (Example: Grade A - Salary between 30000-50000). Employee's phone number, Hiredate, Date of Birth should not be empty. The age should be between 18 and 60. Vehicle next maintenance is within 3 month of current date. Each position should not exceed the predefined allowances. The System will need a constraint for Gender ('M','F'). The Username must be Unique in Table Login. Mandatory fields must not be NULL. i did already ERD but i need to; a.Mapping ERD to Relational Schema. b. Adding Foreign Keys. c. Resolve any M:N relationship between the entities. Projectno PROJECT Project Name (make model VEHICLE next maintenance ALLOWANCE aname assign provided llocated description description Dept Name total Emmo DEPARTMENT PhoneNo employs (DOB) Gender POSITION (Pname hiredate ename JEMPLOYEE receives enters BONUS has Salary Bname job Commission empno Date Time TRANSACTION manipulare LOGIN Usemama Password GRADE Salary range
Expert Solution
steps

Step by step

Solved in 3 steps with 1 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Transaction Processing
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS