Appendix B
IT/260 Version 3
Associate Program Material
Appendix B
IT/260 Database Design Document
Final Database Design Document
Prepared By: Shatonya Jimmerson
Date Modified: May 19, 2014
Week: 9th
1
Appendix B
IT/260 Version 3
Part 1: Introduction (due Week in Two)
a) Application Summary
In the week one assignment, I chose a gift shop inventory. In a given business
entity, the databases is made up of a number of areas that include the point of sale,
accounting and inventory control. In the case of the gift shop inventory, the database is
going to contain search results, financial information, inventory catalogue, customer profile
and any other relevant information. The concrete relational data model is relating to the
database schema which is going to be optimized within storage, however, not specifically for
the data that is being used with the system.
b) Database Software
In the week one assignment, I chose a gift shop inventory. In a given business
entity, the databases is made up of a number of areas that include the point of sale,
accounting and inventory control. In the case of the gift shop inventory, the database is
going to contain search results, financial information, inventory catalogue, customer profile
and any other relevant information. The concrete relational data model is relating to the
database schema which is going to be optimized within storage, however, not specifically for
the data that is being used with the system.
c) Database Design Elements
2
Appendix B
IT/260 Version 3
There are four basic components of design that I will use in this database application.
The first element of design that I will use are tables. The main use of tables is to store
values and are defined based on the set of columns. Every row in a table is unique according
to the relation echoed by the table. In addition, a table is made of columns with every
column containing unique values. The second element of design that I will use is relationships
and they are meant to identify related data. The third element of design that I will use is
normalization and this is a process in which all the tables and fields are organized to reduce
incidences of data redundancy. The final basic component of design that I will use is
observing general integrity rules. This incorporates testing all the areas of the database to
ensure that they are working accordingly.
Part 2: Database Design (due in Week Three)
a) Entities and Attributes
There are four basic components of design that I will use in this database application.
The first element of design that I will use are tables. The main use of tables is to store
values and are defined based on the set of columns. Every row in a table is unique according
to the relation echoed by the table. In addition, a table is made of columns with every
column containing unique values. The second element of design that I will use is relationships
and they are meant to identify related data. The third element of design that I will use is
normalization and this is a process in which all the tables and fields are organized to reduce
incidences of data redundancy. The final basic component of design that I will use is
3
Appendix B
IT/260 Version 3
observing general integrity rules. This incorporates testing all the areas of the database to
ensure that they are working accordingly.
b) Normalization
Normalization table
Brand of the
Product name
Product size
Product color
Supplier of the
Name of the
Telephone of the
Email address
Particular
supplier
supplier
of the supplier
Manufacturer of
Telephone
The name of the
The physical
the product
number of the
manufacturer
address of the
product
products
manufacturer
Product
Product i.d
manufacturer
Product code
Product price
Part 3: Relationships, Components, Features, and Requirements (due in Week Four)
Before the creation of a database the filing system was so popular. It however led to a
lot of redundancy thus was so hard to update the records is it is the case while making use
of a database. A database thus helped centralize all activities thus enhance on speed as well
as control. The administrator thus exercised his administrative rights and to some extend
4
Appendix B
IT/260 Version 3
safeguarded the data. While much may have been derived from its application some rarely
know or perhaps have an in depth understanding on what exactly happens in such systems.
Before the actual creation of a database conceptualization in done to arrive at the actual
system functionality though abstract. It’s however developed while limiting the likelihood of
it never arriving at the intended solution which in this case aims at accuracy.
a) Define what an ERD is and why it is important as a tool in the database design
process.
This refers to entity relation diagrams; during conceptualization entity relation
diagrams are used while crafting the model. They show the interrelationship within the
database. Several tools are used during this process such as the case tools owing to their
ability to enhance the creation process within a short span of time. It also leads to accuracy
and can as well show all the relationships held by the various entities created. This thus
limits wastage of resource during conceptualization.
b) Entity-Relationship Diagram
Assume the key attribute forward and patient are Ward_Name and Patient_No
respectively.
Ward
Patients
Assigned to
5
Appendix B
IT/260 Version 3
WARD TABLE
WARD_NAME
LONGONOT
TANA
ELGON
KIRINYAGA
PATIENT TABLE
PATIENT_NO
1294
1201
1301
1152
1350
WARD_TYPE
ORTHOPAEDIC
CARDIAC
MENTAL
ORTHOPAEDIC
PATIENT_NAME
JAMES
MARY
CAROL
TED
SMITH
WARD_NAME
ELGON
LONGONOT
LONGONOT
TANA
KIRINYAGA
c) Components and Features
A component is a part of a system able to function on its own; for an entire system to
work, components are interlinked to limit isolation. They thus need to be connected to
facilitate this. One of the features considered is accuracy as well as speed. The system
developed ought to be user friendly to facilitate this without which time can be wasted.
People should as well access data with ease.
Part 4: Queries and Forms (due in Week Five)
a) Queries
Data is stored and retrieved in an orderly manner according to a specific database
model. There are ways of linking database, the common one being relational database model
and others include network model and hierarchical model. These modes form the basis upon
which retrieval of information is determined. A data base management system is software
6
Appendix B
IT/260 Version 3
used to organize, store and retrieve data according to the inputted command. In order to
create a complete chain of command the data base system is grouped according to the
model in which they support. The chosen model then determines the query languages
present in the system in order to make it possible to access the database, this makes it
possible to determine data retrieval which is then used in making decisions in a business.
Queries are used to quickly access specific information from the database by
determining whether it meets a specific criteria entered by the user who is retrieving data
using a database management system, which keeps records of constant aspects of the
database such as in a hospital it may include the in and out patients. There are two types of
queries select query that only allows the user to retrieve data whereas the action query
allows the user to retrieve data and use it to perform various tasks such as creating tables,
update data or add data to existing tables. There are several types of information that
should be retrieved from the database .The basic information which give details such as
name, address and foundation date and the location if necessary. Provision and utilization
that gives information about the already existing amenities such as in a hospital it may give
new in and out patients. Information about income shows the flow of cash within a system.
Expenditure is crucial information in determining the rate at which income is changing and
keeping track of spending. They include records which are derived from the input and are
stored in the database, inverted file, index and register of the database. Structured text
information is retrieved especially where the length of records and fields is long and when
some fields are multivalued.
b) Forms
7
Appendix B
IT/260 Version 3
Forms are described as data entry screens because they allow the user to work with
data and often contain command buttons. Forms are often used in viewing, editing and
entering data in tables .The user can add buttons which are linked to other forms or
reports or used perform other tasks. Forms are important in controlling interactivity
between users and database and also ensure that information is correctly filled and
protected.
Menus make it easier for users to know the possible answers they are expected to
fill in a form and also make the process more interactive. Forms make it possible and easy to
update the database instead of having to work with tables which take time and often prone
to errors .Making the form more attractive and as the introductory field in a data base will
make it easily accessible especially for users that are not professions in data base
applications.
Forms save on time and their incorporation into the data base application increase
interactivity to the user who is inputting data into the system. Making the forms more user
friendly will have helped the customers such as in a hospital patients may be allowed to fill
their own data base at their convenient and save on having a profession fill tables which are
boring and time consuming .Menus ensure that the data collected is standardized and this
makes it easy in the retrieval of information especially using queries that use IDs.
Part 5: Testing and Implementation Plan (due in Week Seven)
a) Testing Plan
8
Appendix B
IT/260 Version 3
Database testing is a very critical stage in the software implementation process. It involves
subjecting the database to various situations it could experience during usage to ensure
that it is able to meet the requirements adequately. The first step is creating a list of the
database requirements. After this, the requirements are broken down into simpler features
which can then be individually tested. Both the technical and non-technical aspects of the
database are subjected to different tests. The various tests are:
•
•
•
•
•
•
Logical database design test- this involves reviewing the logical representation of
data in the database. I will also check structure of the tables and verify that I have
set up the relationships between the database entities correctly as per the
database design.
Database performance – this involves obtaining statistical measures of various
aspects of the database’s performance. I will measure the database throughput and
response time. I will try to retrieve a moderately large amount of data from the
database to measure how much data can be retrieved from it. I will also try
simultaneous retrieval by multiple users to assess whether the throughput is able to
handle such situations. In addition, I will measure the time it takes to connect to
the database and communicate with it to see whether the response time and
latencies are within acceptable levels.
Database objects – This step involves testing of the various functions and
procedures written into the database. I will run each procedure individually to
ensure it functions as it should and returns the correct values. In addition, I will run
all the queries that will be used with the database to confirm that the results
returned are correct. This part will make me aware of any vital queries that may be
required I could have overlooked.
Constraints – this involves assessing the database’s ability to identify and deal with
invalid data. I will ensure that all fields have data type restrictions to reject input
of invalid data. I will then try feeding invalid data into each field to see whether it
will be rejected.
Database security – database security testing is very important as it ensures that
the data is stored safely. I will test the access rights of each database user to see
whether they have the rights they require without being able to access
unauthorized data from the database.
Data integrity – this involves verifying the quality of the data stored in the
database. I will assess the data to ensure it is correct and does not contain invalid
or incorrect components. I will also ensure that the data is stored in the correct
manner to mitigate the risk of data corruption.
9
Appendix B
IT/260 Version 3
b) Implementation Plan
Database implementation is the setting up of the database for use by the end users. The
steps to be taken during database implementation are:
•
•
•
•
•
Considering the infrastructural considerations of the database system. I will ensure
that the computer on which the database will be hosted on meets the minimum
system requirements. I will also consider the storage space available to confirm that
it is adequate for the system and will not be filled up.
Cleaning up of data. I will analyze the data to be stored in the database to ensure
that it is correct before transferring it into the main database. I will verify its
accuracy, eliminate any duplication that may be present and remove any invalid
entries such as empty records.
Data migration. This is the actual transfer of the data into the database. Depending
on the data and end user requirements, the data importation may be done
automatically or manually. For automatic data migration, I will write a query that
imports the data from an external source and put each element in the correct field
and record of the correct table.
End user training. This involves equipping the users of the database with the skills
and information they need to use the system. I will schedule intensive training for
the system administrators to ensure they will be comfortable handling any
exceptional situation that may come up under their watch. The end user training will
be less technical and geared towards enabling them to use the database in the most
efficient way depending on their roles. Based on the client, this could require
training of ‘in-house trainers’ who will then be able to transfer this knowledge to
their colleagues.
Database rollout. This is the stage where the newly developed database system
starts to be used. The rollout can either be phased, parallel or based on a switch
over. For a phased rollout, the client will begin using a few features of the database
system initially. The other features will then start being used progressively till the
entire system is in use. A parallel rollout involves using both the old and new system
at the same time before the users get used to it. The advantage of this method is
that it provides for a backup in case problems arise with the new system. A switchover rollout involves moving from the old system to the new one completely on a
specified date. This is usually done in cases where there are high levels of
confidence in the new system.
10
Appendix B
IT/260 Version 3
Part 6: File Maintenance and Storage Plan (due in Week Eight)
a) File Maintenance and Storage Plan
My file maintenance and storage plan is aimed at ensuring the consistency, integrity
and perpetual availability of the data. The most important component is data backup. I will
set up the system to provide a regular backup schedule. If the client has adequate
resources, I will provide for offsite backups for additional redundancy. I will also
continually perform space allocation checks on the hard disk storing the database data. This
is to keep it from getting full unexpectedly, which could crash the database application or
corrupt some of the data by overwriting. In addition, I will schedule regular index rebuilding
to defragment the data, improving integrity and consistency. Index rebuilding also improves
database performance.
11
Appendix B
IT/260 Version 3
Reference
The Entity-Relationship Model (2012): Toward a Unified View of Data." Peter P.S.
Chen. ACM Transactions on Database Systems (TODS)
A Relational Model of Data for Large Shared Data Banks. (2009) " E.F. Codd.
Communications of the ACM,
Carlos Coronel. (Cengage Learning). Database Systems: Design, Implementation, and
Management. Retrieved from Carlos Coronel, IT/260 website.
Corbet, J., Rubini, A., & Kroah-Hartman, G. (2005, 2001, 1998). LINUX DEVICE
DRIVERS (2nd ed.). Retrieved from
https://ecampus.phoenix.edu/secure/aapd/cwe/citation_generator/book_04
_01.asp.
12
Purchase answer to see full
attachment