Database system

Running Head: DATABASE MANAGEMENT SYSTEM 1

Customer Appointment and Billing Database Management System

IT610

Data Modeling and Requirements

Eman Khalifeh

9/2/2020

DATABASE MANAGEMENT SYSTEM 2

Table of Contents Dante’s Customer Appointment and Billing Database Management System .............................................. 3

Introduction .............................................................................................................................................. 3

Project objectives ...................................................................................................................................... 3

Specific Objective .................................................................................................................................. 3

General objectives ................................................................................................................................ 3

Feasibility study and Analysis.................................................................................................................... 3

Technical factor ......................................................................................................................................... 4

Legal Factors ............................................................................................................................................. 5

Scheduling ................................................................................................................................................. 5

Cost and Time Estimates ........................................................................................................................... 5

Project Cost Estimates .............................................................................................................................. 5

Project Time Estimates ............................................................................................................................. 6

Project Justifications ................................................................................................................................. 6

System Design ............................................................................................................................................... 8

Conceptual Database Design .................................................................................................................... 8

Database Entities .................................................................................................................................. 8

Customer Entity .................................................................................................................................... 8

Appointments Entity ............................................................................................................................. 9

Billing Entity .......................................................................................................................................... 9

Entity Relationship Diagram ................................................................................................................... 10

Problem description and data requirements .......................................................................................... 11

Problem description ................................................................................................................................ 11

Data Requirements ................................................................................................................................. 12

The solution to the problem ................................................................................................................... 13

Database SQL Statements ....................................................................................................................... 15

Database Administration ............................................................................................................................ 18

SQL statements to support the efficient execution of transactions ....................................................... 18

Backup Recovery Model ......................................................................................................................... 19

References .................................................................................................................................................. 20

DATABASE MANAGEMENT SYSTEM 3

Dante’s Customer Appointment and Billing Database Management System

Introduction

The database system is one of the information system components that play essential

roles in managing the vast data that the file system cannot control. The system will be crucial for

Dante, an organization offering beauty services to a wide range of customers. Currently, the

organization has no software or database system in place to manage most of its crucial activities,

which has led to difficulties in the management function. The project shall give a solution to the

issue through the implementation of a database management system.

Project objectives

Specific Objective

To develop customer appointments and Billing database management system that will

help the organization in the management of records.

General objectives

• To conduct a study on the best way to implement the solution to the problem through the

use of the database management system.

• To collect the system requirements that will be critical for the development of the

database management system.

• To analyze the requirements about the system and furnish them for proper

implementation of the system.

• Develop the conceptual data diagram that will lead to the implementation of the system.

• To test the database system

• Write the SQL statements to query the database management system

Feasibility study and Analysis

The feasibility study is one of the crucial activities conducted before arriving on the

decision to implement a project. The activity goals are to find out the viability of the project

DATABASE MANAGEMENT SYSTEM 4

before implementation or execution. The project team members ought to consider several factors

surrounding the concerned scheme and other external factors having an impact on the project.

Some of the goals of the feasibility study include. First, to gain an understanding of the features

of the project, concepts, and plan. Second, feasibility creates awareness of potential issues

anticipated in the project implementation process. Finally, a feasibility study helps to determine

the viability of the project after the consideration of all the significant factors. The following

consists of the factors that the project will consider.

Economic Factors

The economic study feasibility study determines whether the project is viable,

considering the prevailing economic factors. It is essential to assess the economy of the business

to determine if it has enough resources to develop and implement the project. Also, the factor

finds out the effects of the project on its finances after the implementation of the database system

project.

Currently, Dante has enough resources to fund the project; the business offers the best

services presently as compared to other firms in the industry, which has resulted in substantial

revenues. Also, the cost of operating the database system is minimal as compared to the file

system currently in place that is prone to errors and damages. Therefore, the implementation of

the project shall greatly help Dante to manage its resources and activities effectively result in the

generation of more revenues.

Technical factor

Though Dante does not have any computers to implement the project, the financial

resources that it generates are far much enough to buy the equipment, which consists of software

and hardware. The project will not require expensive resources that will strain the financial

capability of the organization.

DATABASE MANAGEMENT SYSTEM 5

Also, the database will require the organization to hire a database administrator will

foresee the activities in the system after implementation. However, the anticipated benefits that t

organization will realize shall enable it to meet the cost associated with hiring this technical

employee.

Legal Factors

The legal factors that affect the implementation of the database system are critical, since

if the organization breaks will lead to detrimental effects that might drain its financial resources.

There are not current issues associated with the database implementation that might affect its

application. However, the organization should adhere to the legal requirements that govern the

use of the system.

Scheduling

It is essential to consider the time factors or the period that the project will take to its

implementation. Time has direct proportional to the project cost. Therefore, if the time increases,

the expenses of the project will also rise.

This project will not take a considerable amount of time if all the requirements and

resources are available. Therefore, on availing or the funds, the project will take approximately

one month to implement, which viable considering the cost the whole process will take.

Cost and Time Estimates

The size of the project determines the cost and the time it will take to implement it. The

project manager has to come up with the estimates and present them to Dante’s management fo

approval. The following consist of the time and costs estimates of the project.

Project Cost Estimates

The costs are subdivided into different categories according to various cost resources and

services offered in the process. The following are different costs associated with the project.

DATABASE MANAGEMENT SYSTEM 6

Project Activity or Resource Cost

Labor 200

Software Resource 200

Hardware 350

Database Development 500

Maintenance 250

Project Time Estimates

The time that the project takes for implementation should be reasonable enough

considering the resources available and the cost of each activity in the process. The following

are some of the events and their estimated duration. The time excludes the weekends.

Project Activity Duration (Days)

Requirement Gathering Analysis 2

Database Design 5

Implementation 5

Testing and Evaluation 10

Deployment 2

Project Justifications

The database project is crucial for the organization because it solves the issues the

organization is currently going through due to the ineffectiveness of the current system. The

following justify why Dante requires the system.

First, the organization, through the current system, has experienced lots of problems

keeping the records of the customers. Therefore, it brings issues associated with categorizing so

that they can serve them effectively. The system will enable us to understand the customers and

the services that these customers want.

Second, the customers make appointments to avail time of service might. The current file

system has no functionality to schedule the appointments of the client. Some of the clients end

up missing the appointments, which make the organization to lose customers and resources.

DATABASE MANAGEMENT SYSTEM 7

Third, billing has is another issue facing the current system. It is challenging for the management

accounts for payment made by clients and the cost of inputs. The database system shall,

therefore, help the organization to keep records regarding different bills incurred.

Lastly, Dante has experience issues regarding the management of its employees.

Therefore, it has experience issues regarding the control of the employees. For instance, the

problems have affected the process of accomplishing payments to its staff, which might lead to

unaccounted for amounts.

DATABASE MANAGEMENT SYSTEM 8

System Design

Conceptual Database Design

Before the actual development of the database, it is important to come up with concept of

its implementation. The is process is where we come up with conceptual design of the product.

Database Entities

The relation database is composed of entities which represent it tables. The entities interact with

each other in the database to perform different action. The relationship among the entities show

how each is related to each other and when writing queries to extract and manipulate the data.

The following are some of the entities that our system will have.

Customer Entity

The customer entity records the information regarding the customer who wants Dante’s services.

The data or attributes that this entity will record include

the customer ID, Age, telephone Number, and Billing

information.

Employee Entity

The entity will record the information concerning the employees in the organization. The

employee servers the customer, deals with billing

information’s and confirms appoints from the customers.

The diagram below. Show the employee entity.

Customer

Customer_IDPK

Customer_name

Customer_telephone

Billing

Employee

Employee_IDPK

Employee_name

Telephone

Customer

DATABASE MANAGEMENT SYSTEM 9

Appointments Entity

The appointment entity is used to records the appointments made by the clients. The information

that is included in this entity include the information

customers information and employee information.

Therefore, the entity is related to the customer and

employee entities.

Billing Entity

The billing entity records the information regarding the bills made in the process offering

services in the organization. the information includes in

this entity include the customer information of whom the

service is offered, the employee serving the customer,

and the information regarding billing.

These entities record the crucial information regarding the services offered by Dante

Beauty. It through this information that the organization will be able to offers different services

in the organization without any issue. For example, if the client makes call to of the employee to

make appointments, he or she will be giving information regarding cost of services they wants

and the available time to be scheduled. Also, it will be possible to track different billings to

identify the responsible customers and clients (Tomoda, Isoda, & Ushijima2019). Lastly, the system

allows the management to know the number of appoints and decide on whether or not they

should her contractual employee based on the bulk of work.

Billing

Billing_IDPK

Customer

Employee

Apointment

Appointment_IDPK

Appoinment_Date

Employee

Customer

DATABASE MANAGEMENT SYSTEM 10

Entity Relationship Diagram

An entity relationship diagram is a conceptual representation of the information captured

in the databases and how different entities relates to each other. The diagram helps the database

designers to describe the overall design of the database concisely and accurately. The database

designer can easily transform the diagram into relation schema which hasten the design process.

The diagram has entities which as attributes thus writing queries to deal with the

information in the databases will be an easy process. the following is the entity relationship

diagram for the organization.

The entity relationship diagram was designed to prevent anomalies associated with the

database systems. These anomalies can affect the data integrity as well as the security. The

database design in our case has removed the anomalies through the following ways. First, all

records will be containing data; there will be no null entry. Secondly, the database also has

implemented accuracy regarding to the data by ensuring consistency; the approach prevents the

Employee

Customer

Billing

Appointment

Employee_IDPK

Employee_Name

telephone

CustomerFK

Customer_IDPK

Customer_Telephone

Customer_Telphone

Billing_IDPK

CustomerFK

EmployeeFK

appointment_IDPK

Appointement_date

CustomerFK

EmployeeFK

DATABASE MANAGEMENT SYSTEM 11

perceived data integrity issues. Thirdly, the database will not allow the existence of the similar

data in the entire database, which will avoid the data redundancy in the database (Kadivar, 2015).

The database will be therefore free from any kind of anomalies that might arise when it

has been deployed to a working environment. The team will conduct vigorous test to ensure that

the anomalies are resolved in the database system.

Problem description and data requirements

The entity-relationship diagram shows the high-level interactions of entities and how they can

work to achieve the goals. Dante’s aim of implementing the system is to ensure that all its operations are

smooth and secure (Tomoda, Isoda, & Ushijima2019). For instance, the billing ought to record the

amount paid by the customer, the employee who recorded the information, and the service that the

customer wants.

Problem description

Dante faces issues regarding the management of appointments, billing information, and employee

management. The appointment and billing database will thus offer a solution to these issues. some of the

problems associated with billing, appointments, and employee include:

First, Dante finds it hard to schedule the appoints of clients, and many of the end up missing to

receive the services at the time of appointment. For instance, since many of the appointments are made

via the phone call. It hard for the management to schedule a time for the client about the available

workforce.

Second, Dante faces issues with billing in that most of them are recorded on hard copy or files.

These files can easily get misplaced in the process. Looking for these files is time-consuming, which

might also be translated to the loss of service that could have been used to generate income(Tomoda,

Isoda, & Ushijima2019). Besides, the frustrated client might ruin the organization’s reputation, which

could have a long-term impact.

DATABASE MANAGEMENT SYSTEM 12

Lastly, on the employee, Dante depends on full-time as well as contractual employees depending

on the availability of work. For instance, when they are many appointments, the management can resort to

employee other workers on contract ease the bulk. Thus, the use of manual systems makes it hard for the

firm to determine the number of employees required regarding the available task; sometimes, the

organization might have few employees and times less.

Therefore, the use of the conceptual diagram can help in conceptualizing the solution of the issue

and plan on how to implement the solution by developing the database system. The entities and the

entity-relationship chart conceptualize the main areas of operations in the organization and how they can

interact.

Data Requirements

The interaction among different entities in the system primarily happens through the use of the

data. Even the design of the relationships occurs through the exchange of the data in many ways. It is,

therefore, essential to coming up with a conceptual diagram that can help to design the data that each

entity will have and how such data will interact with each other in the database. The following are the

data required for each entity presented in the database.

Appointments: clients make appointments so that their time of service can be scheduled. When

the customer books an appointment, that data will be taken on their appointment information, which

includes appointment id, appointment date, customer name, billing, and the assigned employee. The

customer information will relate to the information provided in the customer database.

Billing: the entity takes the information regarding the payment made by the client. The

information taken here includes the billing id, the customer, and the employee. The employee in this

particular case is important because he is the one who will offer the service to the customer. The

employee billing goes directly to their payment records. The field is important because of sometime

organization employee workers on a contract basis with different terms of the contract.

DATABASE MANAGEMENT SYSTEM 13

Customer: the entity represents a table in the relational database that is used to store information

regarding the customer in the database. The information about the customer record that is stored includes

the customer id, customer name, customer telephone number, and the billing information. We can see that

the customer table is related to the billing through the billing information recorded in it.

Employee: This entity keeps the information of the employees in the database. The attributes

held in the employee entity include employee name, employee id, customer, and telephone number. The

data taken in this entity is critical for managing the employee information, which consists of the monthly

payments as for the case of the permanent employee and the period and payments per service for the

employee on the contractual term. The information kept allows Dante to track the expenses regarding the

employee in the organization.

Handling data during the development period is critical for ensuring that they can be related

effectively and could be used to get the target activity when required. Therefore, coming up with the

conceptual model of the database can help to highlight the issues that might affect the development

process of the database. The diagram we have come with represents the individual entity and the data that

each will hold. The entity-relationship diagram is thus the conceptual representation of the whole system.

The solution to the problem

The solution to the problem discussed is to design the most effective conceptual diagram. The

entity-relationship diagram simulates the entire system to identify issues and see if it handles the case

presented. The following are some of the problems that the conceptual diagram enhances their solution.

First, the billing issues associated with the current systems emanate from deprived record keeping. It is

not clear no what information that the client wants to save from the client related to the bills. The

conceptual diagram shows the attributes that the user needs to store to process the billing effectively.

Also, the relationship between the billing helps the customer to link some given bills to individual

customers.

DATABASE MANAGEMENT SYSTEM 14

Second, the issue of scheduling appointments is a problem to the organization, currents the

organization can schedule the appointment but cannot ensure that those appointments are adhered to as

the number of customers can change any time. This might render some of the appointments ineffective.

The conceptual diagram solves the issue by showing how the customer, appointment, employee-related.

Therefore, on the implementation of the actual system, the customer will have known already the

employee that will serve them.

Last, employees in the organization are a bit of a problem with handling the task with Dante.

Sometimes some employees do not find work while at the same time, some employees have a huge

amount of work. This problem is brought about by the inconsistencies present in the system. With the

diagram it possible to conceptualize how the incoming beauty task is distributed across the employee of

the organization. Also, the organization can plan to contract the employees considering the available

tasks.

DATABASE MANAGEMENT SYSTEM 15

Database SQL Statements

The database operates through SQL statements that are designed to perform certain task

in the database. There different language with different functions used in the database. These

languages include Database Manipulation Languages (DML), Database Definition Language

(DDL), Database Control Language (DCL), and Transaction Control Language (TCL).

The DDL is used for creating the database schemas, which are the skeletal structure

represent the logical view of the database (W3schools, 2020). The DDL and other database

language operates through the use of SQL statements. The following statements are the DDL

which create the database and table schemas of the Dante’s Database

Statement to Create the Database

create database dante_database;

Statement to create the tables for the database

Statement to create customer table

create table customer (

customer_ID INT (255),

customer_Name TEX (255),

cutstomer_telephone (255)

PRIMARY KEY (customer_ID)

);

SQL Statement to create Employee table

create table employee (

employee_ID INT (255),

employee Name TEXT (255),

DATABASE MANAGEMENT SYSTEM 16

customer (255),

Telephone (255)

PRIMARY KEY (employee_ID)

FOREIGN key (customer) REFERENCES customer(customer_ID)

;

SQL statement to create Billing Table

create table billing (

billing_ID INT (255),

employee INT (255),

customer INT (255),

PRIMARY KEY (billing_ID)

FOREIGN key (customer) REFERENCES customer(customer_ID)

FOREIGN key (Employee) REFERENCES customer (Employee_ID)

);

SQL statement to create appointment table

create table appointment (

appointment_ID INT (255),

employee INT (255),

customer INT (255),

PRIMARY KEY (appointment_ID)

FOREIGN key (customer) REFERENCES customer(customer_ID)

FOREIGN key (Employee) REFERENCES customer (Employee_ID)

DATABASE MANAGEMENT SYSTEM 17

);

DATABASE MANAGEMENT SYSTEM 18

Database Administration

The database administration is concerned with making of the database to perform some

specific task. We use the DML to query the data to perform certain operations with data such the

extraction of the data. The data manipulation is the main target of the database so that it

retrieves the desired information during regular work time.

SQL statements to support the efficient execution of transactions

The following are some of the SQL statements to perform some of the crucial function.

i. The SQL statement to retrieve information from the customer name, appointment

date, and billing amount.

SELECT customers. customerName, appointment.appointment_date,

billing.amount

FROM customer

INNER JOIN Customers ON billing.customer_id=customers.customer_id;

ii. The SQL statements to retrieve billing information with the associate customer

SELECT customers. customerName, billing. amount

FROM customer

INNER JOIN Customers ON billing.customer_id=customers.customer_id;

iii. SQL statement to insert data into the customer table

INSERT INTO Customers (Customer_ID, customer_name, billing,

Customer-telephone)

VALUES ('234578', 'Tom B. Erichsen', '20', '1235456');

DATABASE MANAGEMENT SYSTEM 19

Backup Recovery Model

It is important to backup the data so that they can be retrieved back in case the data in the local

database is lost. The retrieval process of the data happens through the use of the backup recovery

model. The choice of the model depends on the data that an organization is targeting. The

Dante database shall implement the full recovery model (Martin et al., 2017).

The full back recovery plan is the best since it retrieves all the data that had been ack. Therefore,

no single data will be compromised or lost.

DATABASE MANAGEMENT SYSTEM 20

References

Martin, C. B., Poyatos, J. M., Sierra, M. R., & Pérez, A. S. (2017). Performing data mining

operations within a columnar database management system. 9,798,783. Washington, DC:

U.S.

Tomoda, A., Isoda, Y., & Ushijima, K. (2019). Database management system and method

Washington, DC: U.S. Patent and Trademark Office.

Kadivar, M. (2015). Entity relationship diagram approach to defining cyber-attacks (Doctoral

dissertation, Carleton University).

W3schools. (2020). MySQL what is DDL, DML and DCL? Retrieved from

https://www.w3schools.in/mysql/ddl-dml-dcl/