Logical Model and Physical Database Design -Chores Database

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.

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s