12 Replies - 861 Views - Last Post: 05 July 2013 - 02:23 PM Rate Topic: ***-- 2 Votes

#1 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Structuring a structured database

Posted 25 June 2013 - 06:08 PM

Hi everyone.... im new to database...
we all know that before we can construct a structured database we should also create a structured erd, an entity relationship diagram...
since so far i have no problems dealing with creating the sql... i need to enhance my analysis skills for me to construct a structured erd...

so here is it.... the erd i am going to structure is all about an online leave of absence system....
for employees here they all know that if you are a regular employee then you are given a 30 days of leave.... so 15 days each for vacation leave and sick leave and for not regular employees then the number of days of leave will increased depending on the business rules of the company...

so far.. i have four entities..
the user_account for log in processes which includes the username and password
the employee_details for the name of the employee and their employee id
the leave_details, these are the needed details in applying leave in which the employee will fill up if they want to file a leave of absence request..
the remarks, this entity will be seen only with the employees who wished to request a leave of absence and the only one who is capable to approve of disapprove the request is the administrator of the system...

i passed the erd to our professor and she told me that our erd is completely wrong...these are the questions she gave us...
-if an employee send a leave of absence request then how will she know if her request if being approved or disapprove..
-how will the employee know if the number of days left for 30 or 20 days is already over?
-how will the employee know if she will be sending a leave of request with pay or without pay?

gosh!!!!! i had so many questions... and i am having trouble with this erd..
any help will be appreciated.....

Is This A Good Question/Topic? 0
  • +

Replies To: Structuring a structured database

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10446
  • View blog
  • Posts: 38,686
  • Joined: 27-December 08

Re: Structuring a structured database

Posted 25 June 2013 - 09:11 PM

I think your professor had a lot of fair points.

Quote

-if an employee send a leave of absence request then how will she know if her request if being approved or disapprove..

Wouldn't this make sense to associate with the leave_requests? Is the status approved, disapproved, or pending?

Quote

-how will the employee know if the number of days left for 30 or 20 days is already over?

When does the leave start? When does it end?

Quote

-how will the employee know if she will be sending a leave of request with pay or without pay?

This one should be fairly easy to do. Look at the leave_details entity.
Was This Post Helpful? 1
  • +
  • -

#3 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Structuring a structured database

Posted 30 June 2013 - 09:18 PM

Well, it doesn't look like you are completely wrong, because you have the three entities (users, employees, leave). However, remarks is an attribute of one of these. (Remarks about what?) Also, you clearly haven't given enough thought to all the attributes (the "needed details") of each entity, given your professor's questions.
Was This Post Helpful? 1
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5796
  • View blog
  • Posts: 12,631
  • Joined: 16-October 07

Re: Structuring a structured database

Posted 01 July 2013 - 04:17 AM

View Postbrerallia, on 25 June 2013 - 09:08 PM, said:

so far.. i have four entities..


Why are you making me do word problems, when we're talking about data entities?

Different layout:
employee
- employee id (PK)
- name

leave
- employee id (FK)
- ??? and?

remarks
???
- FK to leave?

user_account
- username (PK)
- password
- Would this map to an employee?



Your professor also wants detail on that leave entity. Shouldn't it a have timestamp? Why aren't "remarks" in the leave entity? What is the status of the request? Who changed that status? Each question can be answered with a field on your entity.

An ERD is an abstraction, but not so much of an abstraction that lacks detail. The ERD adds clarity to how a database structure is used. However, the structure is what is important. You can have a database without an ERD, you can't have one without data entities.

Note, dates are powerful things and a database server knows what time it is. They are the key to your system.

Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#5 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Re: Structuring a structured database

Posted 01 July 2013 - 06:19 AM

So all of you must be very confuse to this... i will make this very understandable.

EMPLOYEE
-employee id (PK)
-name

USER ACCOUNT
-username (PK)
-password (PK)
-employee id (FK)

LEAVE DETAILS
-leave number (PK)
-date of leave
-leave type whether it is a vacation leave or sick leave type
-payment whether it is a with pay or without pay
-reason
-duration in hours

REMARKS
-remarks number (PK)
-leave number (FK)
-decision of the administrator whether it is approve or disapprove



So far, i have four entities... and our professor ask us questions, a lot of questions that she said will not be answered since our erd is not well structured

1. if the employee is newly hired, the number of days left given in the company is only 10 days each leave type and for regular employees will be 15 days each. So how will the

database know the status of the employee and be given the particular number of days left if there is no indication in it?
My thoughts: Do i have to classify the employees and construct a subtype-supertype relationship in the employee entity for them to know the status and the number of days left given

by the company?
2. how will the employee know the specific number of days left if there is no indication? the employees will not be aware of the number of days left.
My thoughts: Will i create a new entity and name it LEAVE CREDITS with an attribute of number of days left and connect it to the LEAVE DETAILS entity and the leave details entity will

have the foreign key from the leave credits named as credit number?

Additional Information:
The company allows the employee to send a request of leave (this is if the system is being implemented). Any types of employee are allowed to send a leave of request as long as they

can access the computer and type in the address. Regular employees are given 15 days in vacation leave and 15 days sick leave number of days, for a total of 30 days. Newly hired

employees in the company are given 10 days in each leave type. This particular number of days in each leave type is in pay, that means, if an employee is on leave whatever the leave

type is deducted. Let us set an example and assume employee number 1, a regular employee is on a vacation leave because her brother is to be married and has sent a leave of

request for 5 days. Her request is being approved by the administrator and allows her to leave the company, since she is a regular employee, she is given 15 days in each leave type.

Since she requests for a vacation leave then 15 - 5 = 10 days left. She will then have 10 days left in vacation leave and 15 days in sick leave since she didnt request for it. For the 5

days out the company she will still receive a salary, as long as the 30 number of days left is not yet fully consume , that is payment type - WITH PAY. That means, if the 30 days in 1

year is already consume and has only 0 number of days left and she still sends a leave of request because she is sick and needs to leave the company for 7 days, then that means, in

the 7 days out of the company she will not be receiving the salary. That 30 days is given every year. If an employee doesnt consume all the 30 days given or the 20 days given for

newly hired employees then after 1 year, the number of days given will NOT BE added for the current year. If employee number 1 has consume only 25 days, there is 5 days left in

sick leave type and at the end of the year the number of days left will go back to 30 and not 35, it will not be added.

Any help or respond will be really APPRECIATED!!!
Was This Post Helpful? 0
  • +
  • -

#6 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Re: Structuring a structured database

Posted 01 July 2013 - 05:15 PM

macosxnerd101
BobRodes
baavgai

What can you say with my earlier post?

This post has been edited by brerallia: 01 July 2013 - 05:17 PM

Was This Post Helpful? 0
  • +
  • -

#7 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10446
  • View blog
  • Posts: 38,686
  • Joined: 27-December 08

Re: Structuring a structured database

Posted 01 July 2013 - 06:27 PM

Quote

My thoughts: Do i have to classify the employees and construct a subtype-supertype relationship in the employee entity for them to know the status and the number of days left given

Status and total days per status seem like good pieces of data for the database to store. To get the number of days the employee has used, I would just use a query based on the leave details to select the sum of the days used based on the employee_id.

Quote

What can you say with my earlier post?

It's really not necessary to bump your thread. :)
Was This Post Helpful? 1
  • +
  • -

#8 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Re: Structuring a structured database

Posted 01 July 2013 - 07:29 PM

View Postmacosxnerd101, on 01 July 2013 - 06:27 PM, said:

Quote

Status and total days per status seem like good pieces of data for the database to store. To get the number of days the employee has used, I would just use a query based on the leave details to select the sum of the days used based on the employee_id.


So, how about the status? If the employee is newly hired, the business rules here in company is: The newly hired employee must be in the company for 3 years for them to be a regular employee so that she will be given 30 days each in leave from the 20 days she had as a beginner. That means, the number of years increased based on the employee she has been in the company.
Does the query will do the status in it? and also we both know that it is really important to store the status of the employee? Does the subtype-supertype has to do with it?

This post has been edited by brerallia: 02 July 2013 - 05:46 PM

Was This Post Helpful? 0
  • +
  • -

#9 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5796
  • View blog
  • Posts: 12,631
  • Joined: 16-October 07

Re: Structuring a structured database

Posted 02 July 2013 - 03:53 AM

I'm afraid we seem to have moved away from "I'm lost, this is what I tried" to "here's my homework, do it."

1. How do you define newly hired? Perhaps have a hire date for your employee?

2. Again, "a database server knows what time it is." Given a start date, and a current date from the system, you should be able to figure this out.

Password should NOT be a primary key. How does the user change their password?

REMARKS still appears redundant. Unless you want more than one remark per leave request. Then, you're still missing who added the remark.

Who is an administrator? Is there only one? Can any administrator authorize any employee?
Was This Post Helpful? 2
  • +
  • -

#10 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Re: Structuring a structured database

Posted 02 July 2013 - 05:41 PM

View Postbaavgai, on 02 July 2013 - 03:53 AM, said:

Quote

I'm afraid we seem to have moved away from "I'm lost, this is what I tried" to "here's my homework, do it."


Oh my gosh!!! Im sorry, i didnt think i have almost beyond this rules here. Im just trying you to understand the business rules in the company, i didnt mean it. Im sorry.

Quote

1. How do you define newly hired? Perhaps have a hire date for your employee?


Because the design of our system is : Only the administrator can register all her employees in the company, so that means, the homepage of our system had no registration link. We will register the administrator first into the system by manually inserting the data into the database. Once she is successfully register, she then can log in to the system and in her page, (because employees and administrator have different pages) the administrator can then register the employees. So in the registration form. We required the options "Status" with only two selections that is regular or not regular, then that status will be then stored into the database to determine the number of days the company will be giving to them, whether that is a 30 day or a 20 day. I sort of like the date hired, i tried to think of it since my friends told me to but it will be very difficult to track the day they were employed.

Quote

2. Again, "a database server knows what time it is." Given a start date, and a current date from the system, you should be able to figure this out.


I have a hard time understanding this one, maybe i will not go to this suggestion... Im sorry.

Quote

Password should NOT be a primary key. How does the user change their password?


That is what i am talking about!!! Thank you for this question. You know what, i tried to implement the erd to the postgre and once i made the user account tables i was just wondering how to set an action in the primary key which is the password. our professor told us that only foreign keys can set actions such as cascade or restrict or no action at all. So that means, the password will be an attribute only in the user account entity? but this is the question that has been running through my mind if the password is not the primary key -- when the system is being implemented that means if they type in the right username and leaves the password blank, will the user be able to log in to the page? because the password is not required?

Quote

REMARKS still appears redundant. Unless you want more than one remark per leave request. Then, you're still missing who added the remark.


I didnt find anything redundant in the remarks entity. Or am i just that idiot to not find it out? our professor just told us that we need to store the decision of the administrator whether the request is being approved or disapprove so that is why i created a remarks entity for it to store the decision.

Quote

Who is an administrator? Is there only one? Can any administrator authorize any employee?


So that is what my friend said, there will be a total of 3 administrators first the president of the company, the assistant vice president and the human resource manager. We conduct an interview through it.
So they can all authorize all the employees.

This post has been edited by brerallia: 02 July 2013 - 05:45 PM

Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5796
  • View blog
  • Posts: 12,631
  • Joined: 16-October 07

Re: Structuring a structured database

Posted 03 July 2013 - 03:53 AM

View Postbrerallia, on 02 July 2013 - 08:41 PM, said:

if the password is not the primary key -- when the system is being implemented that means if they type in the right username and leaves the password blank, will the user be able to log in to the page? because the password is not required?


The properties of a primary key is that it must be UNIQUE and NOT NULL. A primary key will also be indexed. The unique requirement is called a constraint. The NOT NULL requirement is also a constraint. The unique constraint is almost always implemented with an index, so you get two for the price of one.

The constraints associated with a primary key are also used with other fields. Any field can have a NOT NULL constraint. Any field can have a UNIQUE constraint ( meaning is also has a unique index .)

For your password, you absolutely need a NOT NULL constraint. But that's all you need.


View Postbrerallia, on 02 July 2013 - 08:41 PM, said:

there will be a total of 3 administrators


In which case, you need a way to determine who they are. You then also need to identify which one did an administrative task. Reasonably, a flag in either the user or employee table will work. Note, if a user can only be an employee, then you don't need two tables for this.

View Postbrerallia, on 02 July 2013 - 08:41 PM, said:

I didnt find anything redundant in the remarks entity.


Ok. So, why can't this be one the the leave entity? If multiple remarks can be applied to a single leave request, then you need it. If only one is ever added, then it's really just a field in leave.


Time... this is vendor specific, but I can't think of a single database that doesn't offer access to the system clock.

Using such a value, you can figure out the state of anything with a time stamp. e.g.
-- find all employees currently on leave
select e.name, a.start_dt, a.end_dt, end_dt - now() as days_left
   from leave a
      inner join employee e
          on e.employee_id=a.employee_id
   where now() between a.start_dt and a.end_dt


Was This Post Helpful? 2
  • +
  • -

#12 brerallia  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 146
  • Joined: 21-January 13

Re: Structuring a structured database

Posted 03 July 2013 - 05:56 PM

View Postbaavgai, on 03 July 2013 - 03:53 AM, said:

Quote

For your password, you absolutely need a NOT NULL constraint. But that's all you need.

Yes!!! thank you i got it... but i need to find a way to make the password attribute to a NOT NULL CONSTRAINT in postgresql.

Quote

In which case, you need a way to determine who they are. You then also need to identify which one did an administrative task. Reasonably, a flag in either the user or employee table will work. Note, if a user can only be an employee, then you don't need two tables for this.

To be honest, i kinda find it hard to determine the administrator in the establishing the erd.
I mean, if one employee is sending a leave of absence request and either one of the administrators, the president of the company is accessing our site and found out request then he can just either decide to approve or disapprove the request. I mean, it depends in who of the administrators can access the site first and approved or disapprove the leave request. Is this concept possible using the same structure of the erd?

Quote

Ok. So, why can't this be one the the leave entity? If multiple remarks can be applied to a single leave request, then you need it. If only one is ever added, then it's really just a field in leave.

Oh now, i get that at last. I need only one remarks in each leave entity. So that means i will just have to merge it in the leave entity right? But this is the question. If the remarks will be then an attribute in the leave entity and the relationship is between employee entity and leave entity then does that mean, if a regular employee sends a leave of absence request she can also access the remarks?, because it is in the leave entity? the leave and the employee entity are connected to each other. So that means it is accessible to the non-administrators?

Quote

Time... this is vendor specific, but I can't think of a single database that doesn't offer access to the system clock.

Using such a value, you can figure out the state of anything with a time stamp. e.g.
-- find all employees currently on leave
select e.name, a.start_dt, a.end_dt, end_dt - now() as days_left
   from leave a
      inner join employee e
          on e.employee_id=a.employee_id
   where now() between a.start_dt and a.end_dt


Okay, so i get this code but it will only take effect with attributes having the a.start_dt, a.end_dt, end_dt - now(), in which i didnt have any of this attributes...

I really appreciate your help to make me understand better with constructing the erd and all its concepts that ive just known by now. Thank you...Hope that you will not stop letting me understand about database as long as i am not violating the rule... --hope im not... :)

Was This Post Helpful? 0
  • +
  • -

#13 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Structuring a structured database

Posted 05 July 2013 - 02:23 PM

I've put my answers to some of your questions in brackets:

1. if the employee is newly hired, the number of days left given in the company is only 10 days each leave type and for regular employees will be 15 days each. So how will the

database know the status of the employee [that's an attribute of the employee entity] and be given the particular number of days left if there is no indication in it? [by adding up the number of hours used in the leave details]
My thoughts: Do i have to classify the employees and construct a subtype-supertype relationship in the employee entity for them to know the status and the number of days left given
by the company? [You have to put Employees and LeaveRequests in a one-to-many (what you are calling a subtype-supertype) relationship. You can find the number of days left by checking the employee status and adding up the number of hours used in the leave details.]
2. how will the employee know the specific number of days left if there is no indication? the employees will not be aware of the number of days left.[that has nothing to do with how you store your data. The question you need to be asking is how you will be able to calculate the number of days left from the available data, and I have explained this.]
My thoughts: Will i create a new entity and name it LEAVE CREDITS with an attribute of number of days left and connect it to the LEAVE DETAILS entity and the leave details entity will

have the foreign key from the leave credits named as credit number?
[No. Any time you need to know the number of days left, you can (and should) calculate it from available data. You don't need to store the results of calculations in the database. Not unless you're doing millions of calculations, that is, but you don't need to look at denormalization techniques yet.]

[none of the additional information is relevant to the ERD.]

Other points:

You don't need to have a field for whether the leave is with or without pay. Why not?
You don't need to have remarks as a separate table. How can you reduce the number of tables to three?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1