Question

1. Please briefly describe in one short paragraph a valuable business problem that you are interested in solving and how designing a database can help in solving this problem. You should treat this database design project as something you can put on your CV and explain to potential employers (or potential investors if you are pursuing a startup).

2. Create three tables that you will use in your database. Write the SQL code (can be done in PgAdmin or in MS Word etc.). Then write a short paragraph explaining the purpose of each table.

Answer 1

Hi,

Please find the answer below:
-----------------------------------------

Startups are small newly built budding organizations. Often they don’t have the budget to license or acquire industry popular project management tools to manage projects and tasks of its employees.
Without proper time-sheet application for an organization it’s difficult to track productivity in the organization. Time-sheet application works as an effective tracking solution for the management in the startup.

With database for the application we can design tables to store employee’s data, different projects data and the tasks each employee is working on in the project. Database solves the problem of task assignments, backup of data etc. We can even integrate Time-sheet application to the payment module in the organization to manage pay cheque, leaves, appraisals etc in the organization.

-----------------------------------------

Sample Tables in the application

employee table

CREATE TABLE employee (
    empNumber   INT             NOT NULL,
    birthDate DATE            NOT NULL,
    firstName VARCHAR(20)     NOT NULL,
    lastName   VARCHAR(20)     NOT NULL,
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (empNumber)
);

project table

CREATE TABLE project (
projectId int NOT NULL AUTO_INCREMENT,
name varchar(20) ,
manager_id int ,
start_date date NOT NULL,
end_date date NOT NULL,
PRIMARY KEY (projectId),
);

task table

CREATE TABLE task (
taskId int NOT NULL AUTO_INCREMENT,
project_id int ,
start_date date NOT NULL,
end_date date NOT NULL,
PRIMARY KEY (taskId),
CONSTRAINT fk_task_project FOREIGN KEY (project_id) REFERENCES project (projectId)
);

timesheet table

CREATE TABLE timesheet (
id int NOT NULL AUTO_INCREMENT,
employee_id int NOT NULL,
date date,
from_time time,
to_time time,
taskId int NOT NULL,
date_submit date,
PRIMARY KEY (id),
   CONSTRAINT fk_time_account FOREIGN KEY (employee_id) REFERENCES employee (empNumber),
CONSTRAINT fk_ts_task FOREIGN KEY (taskId) REFERENCES task (taskId)
) ;

--------------------------------------------------------------------

Hope this helps.