Contents
1. Project Plan. 4
Introduction. 4
Project Description. 4
Metadata. 5
2. Logical Design. 6
Introduction. 6
Logical Schema. 6
ER Diagram.. 7
Entity Description. 7
Relationship Description. 8
Business Rules. 8
Constraints. 9
Normalization. 9
3. Physical Design. 10
Introduction. 10
Naming Conventions. 10
Physical Schema. 10
4. Conclusion. 11
1. Project Plan
Introduction
The objective of this project is to design a database that could be used to store and retrieve information about daily chores. The reason behind choosing this application is the need for maintaining daily chores in a central database so that it can be tracked accurately for pending or left-out chores and helps in future forecasting based on previous history. Above all, maintaining a central database would remove duplication of data entry thereby saving time and effort. This sort of application will not only helps an individual in tracking his or her daily chores but also helps cleaning and maintenance companies to keep track of chore activities with the associated employees and clients. Thus, the project will be designed in a high level such that it can be adopted with no or less modifications by both individual users as well as cleaning companies.
Project Description
As explained in the introduction section, this project would aim in designing a database that would keep track of household chores so that the activities can be tracked and reports be generated. The database would be designed to store information about the following data.
Person: This data would represent information about persons responsible for chores such as name, contact details, etc. For company use, this data could be details of employees responsible for each chore.
Chores: Information about chore including chore description, estimated and actual time, assigned person, etc will be maintained. In addition, companies can also add ID of the associated client with each chore.
Clients: Information about clients including client name, address and contact details will be tracked.
Profit: Monthly profits for each year will also be maintained. Profit will be calculated using queries relating the actual and allocated cost for each chore.
Reports
Various reports can be generated from the information stored in the database. Some of the reports include retrieving all the pending activities for the current day or current week, profit variance for the current year, estimated duration for work for each employee for a particular day or week, list of all completed tasks for each employee/person for a particular month, etc. One of the sample reports that shows all the chores that the company is currently handling along with its description, frequency, category, estimated minutes, minutes per week and person responsible is provided below.
Figure 1: Sample report generated from Chores database
Entities
This database will contain the following entities with the following attributes:
- Persons
- Attributes: name, DOB, address, contactNo, email and salary
- Chores
- Attributes: description, category, frequency, estimated minutes, actual minutes, status, assigned person and associated client (if any)
- Clients
- Attributes: name, address, address, contactNo and email
- Profit
- Attributes: month, year and profit amount
Metadata
Person responsible for the chore (person), Person requesting the chore (client), cost, time
Database Application
As compared to manual maintenance of household chores in spreadsheets or flat files, database maintenance is very effective and time consuming. Adopting a central database for storing and retrieving information will avoid wastage time in unnecessary duplication of data and helps in faster data retrieval. Further, it minimizes errors that could arise while entering same data in multiple places thereby increasing accuracy and precision of data. Above all, data can be easily portable and can be shared with less effort.
2. Logical Design
Introduction
This section describes the logical design of the proposed database. It starts with a detailed logical schema of the chores database along with an ER diagram. In addition, detailed description about each entities, constraints and associated entities will also be provided.
Logical Schema
The detailed list of all entities and their corresponding attributes are shown in below table.
Entity |
Attributes |
Person |
Person ID |
|
Person Name |
|
Person Address |
|
Person Contact No |
|
Person Email |
|
Person Salary |
Client |
Client ID |
|
Client Name |
|
Client Address |
|
Client Contact No |
|
Client Email |
Chores |
Chore ID |
|
Chore Description |
|
Chore Category ID |
|
Chore Frequency |
|
Chore Estimated Minutes |
|
Chore Actual Minutes |
|
Chore Assigned Person |
|
Chore Associated Client |
|
Chore Status |
Profit |
Profit ID |
|
Profit Year |
|
Profit Month |
|
Profit Amount |
Chore Categories |
Category ID |
|
Category Description |
|
Category Cost per hour |
Person Expertise |
Person ID |
|
Person Expertise |
ER Diagram
Based on the above logical schema, the ER diagram is drawn as shown in the below diagram (figure 2).
Figure 2: ER diagram of the chores database
Entity Description
The detailed description of each of the entities in the ER diagram is given below:
Person: This entity contains information about persons or employees responsible for one or more household chores. Attributes include ID, name, address, contact number and email.
Client: This entity contains information about clients for whom chore is added in the database. It may be self or any other family members in case of personal application. For company applications, it may hold details of clients. Attributes include ID, name, address, contact number and email.
Chore: This entity contains information about every chore that may be handled by the company or an individual. Attributes include ID, chore description, category ID, associated client ID, frequency, estimated time, actual time, assigned employee and chore status.
Profit: This entity would hold information about the monthly profits for each year. Attributes include ID, year, month and amount.
Chore category: This entity acts a look up table for chores entity and contains name and cost of all the possible categories of chore that may be handled by the company or individual. Attributes include ID, description and cost per hour.
Person Expertise: This entity is added for ease in selection of persons while assigning them to chores. It contains all the expertise of a person and each expertise would be on among the available chore categories. Attributes include person ID and Expertise ID.
Relationship Description
The relationships mentioned in the ER diagram are explained below in detail:
Handles is a one-to-many relationship between a person and a chore. This implies that one person can handle one or many chores but no single chore can be handled by more than one employee.
Request is also a one-to-many relationship that exists between a client and a chore. A client can place one or more request to the cleaning company. For personal use, any individual can place one or more household chores in the database according to their individual needs. However, each request should be made by one and only one client.
Makes is a one-to-many relationship between a choreCategory and profit entity. Each chore category makes some profit based on the allocated cost and actual cost. These profits would be calculated every month and stored in this entity for each year.
ExpertIn is weak one-to-many relationship between person and personExpertise entity and relates to choreCategory entity via another weak one-to-one relationship PersonBelongsTo. This entity does not stand alone with a unique primary key but is a combined relationship of two entities: Person and ChoreCategory.
BelongsTo is a many-to-one relationship between chore and choreCategory entities. Each chore will belong to one and only category but more than one chore can belong to the same category.
Business Rules
The following business rules are identified for the database design:
- No two persons can handle the same chore. Each chore must be handled by one and only one person.
- No two clients can share the same chore. Each chore identified in the chore entities must be from one and only one client.
- Each chore must belong to a category, it should be one, and only one category identified from the chore category entity.
- Each person should have an expertise entity in the personExpertise entity. And each entry in the personExpertise entity should be for one and only one person.
- Each expertise in the person Expertise entity should belong to one and only one category in the choreCategory entity.
Constraints
The database is designed with both entity integrity and referential integrity. In order to enforce entity integrity, each entity will be identified by a not NULL unique primary key. In order to enforce referential integrity, foreign keys defined in each table will exactly match the primary key of the referenced table. The foreign keys will be a not NULL field and the data type will exactly match that of the primary key of the referenced table. The details of referential integrity and entity integrity as enforced on the database are shown in below figure.
Figure 3: Entity Integrity and Referential Integrity on Chores database
Normalization
The database is designed such that it confirms to 1NF, 2 NF and 3NF. In order to confirm with 1NF, each entity is designed with a unique not null primary key. In order to confirm to 2 NF, the tables are designed such that each non key attribute of the table depends on the primary key. Further, in order to confirm to 3 NF, all the non key attributes that does not depend solely on the primary key alone has been moved to a separate table. For example the personExpertise and choreCategories table has been introduced in order to confirm the design to 3 NF.
3. Physical Design
Introduction
This section gives brief introduction about physical design of the database including the naming conventions and physical schema.
Naming Conventions
In order to standardize the naming convention across the database, each column in each entity is identified by its entity name followed by column description. For example each column in person entity will have a prefix ‘person’ followed by the actual attribute (personID, personName, etc).
Physical Schema
The physical schema of the database is given in the below table.
Table Name |
Column Name |
Data type |
PK/FK |
Is Null |
Person |
PersonID |
Integer (Auto Increment) |
PK |
NOT NULL |
|
PersonName |
Varchar(40) |
|
NOT NULL |
|
PersonAddress |
Varchar(100) |
|
NOT NULL |
|
PersonContact No |
Varchar(12) |
|
NOT NULL |
|
PersonEmail |
Varchar(50) |
|
|
|
PersonSalary |
Float |
|
|
Client |
ClientID |
Integer (Auto Increment) |
PK |
NOT NULL |
|
ClientName |
Varchar(40) |
|
NOT NULL |
|
ClientAddress |
Varchar(100) |
|
NOT NULL |
|
ClientContact No |
Varchar(12) |
|
NOT NULL |
|
ClientEmail |
Varchar(50) |
|
|
Chores |
ChoreID |
Integer (Auto Increment) |
PK |
NOT NULL |
|
ChoreDescription |
Varchar(60) |
|
NOT NULL |
|
ChoreCategoryID |
Integer |
|
NOT NULL |
|
ChoreFrequency |
Varchar(20) |
|
NOT NULL |
|
ChoreEstimatedMinutes |
Integer |
|
NOT NULL |
|
ChoreActualMinutes |
Integer |
|
|
|
ChoreAssignedPerson |
Integer |
|
|
|
ChoreAssociatedClient |
Integer |
|
|
|
ChoreStatus |
Varchar(10) |
|
NOT NULL |
Profit |
ProfitID |
Integer (Auto Increment) |
PK |
NOT NULL |
|
ProfitYear |
Integer |
|
NOT NULL |
|
ProfitMonth |
Integer |
|
NOT NULL |
|
ProfitAmount |
Float |
|
NOT NULL |
Chore Categories |
CategoryID |
Integer (Auto Increment) |
PK |
NOT NULL |
|
CategoryDescription |
Varchar(60) |
|
NOT NULL |
|
CategoryCostperhour |
Float |
|
NOT NULL |
Person Expertise |
PersonExpertise |
Integer (Auto Increment) |
PK |
NOT NULL |
|
PersonID |
Integer |
|
NOT NULL |
|
ExpertiseID |
Integer |
|
NOT NULL |
4. Conclusion
Thus, the logical and physical schema for the Chores database has been designed and documented successfully. All the available business rules have been identified and discussed. All the necessary constraints and normalization has also been enforced.