BSQL Assignment 201
Barem: Each sub-question is 10%.
Objective: H5SD - SQL skills
Problem Description:
In the database design for the Employee Management System of FSOFT Company,
given tables of the application with below:
EMPLOYEE:
● EmpNo: employee code, primary key.
● EmpName: employee name.
● BirthDay: birthday of the employee.
● DeptNo: department code of the employee.
● MgrNo: manager code, not null.
● StartDate: starting date of work.
● Salary: salary of the employee, data type is money (VND).
● Level: level of the employee (accepts value range from 1 to 7 only).
● Status: status of the employee (0: working, 1: unpaid leave, 2: out)
● Note: some note about an employee, free text.
EMP_SKILL:
● SkillNo: skill code, foreign key.
● EmpNo: employee code, foreign key.
● SkillLevel: skill level of the employee (accepts value range from 1 to 3 only).
● RegDate: registration date.
● Description: skill description, free text.
Primary key (SkillNo, EmpNo)
SKILL:
● SkillNo: skill code, primary key, auto-increment.
● SkillName: name of skill.
● Note: some note about skill, free text.
DEPARTMENT:
● DeptNo: department code, primary key, auto-increment.
● DeptName: department name.
● Note: some note about department, free text.
Questions to answer:
Q1:
1. Create an EMPLOYEE table with the most appropriate/economic field/column
constraints & types. All fields are mandatory except Note field.
2. Create a SKILL table with the most appropriate/economic field/column
constraints & types, all fields are mandatory except Note field.
3. Create a DEPARTMENT table with the most appropriate/economic
field/column constraints & types, all fields are mandatory except Note field.
4. Create EMP_SKILL table with the most appropriate/economic field/column
constraints & types, all fields are mandatory except Description field.
Q2:
1. Add an Email field to EMPLOYEE table and make sure that the database will
not allow the value for Email to be inserted into a new row if that value has
already been used in another row.
2. Modify EMPLOYEE table to set default values to 0 of MgrNo and Status
fields.
Q3:
1. Add the FOREIGN KEY constrain of DeptNo field to the EMPLOYEE table
that will relate the DEPARTMENT table.
2. Remove the Description field from the EMP_SKILL table.
Q4:
1. Add at least 5 records into each of the created tables.
2. Create a VIEW called EMPLOYEE_TRACKING that will appear to the user as
EmpNo, Emp_Name and Level. It has Level satisfied the criteria: Level >=3
and Level <= 5.
Estimated Time to complete:180 mins.
