Assessing The Different Database Types Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Flat-files database are advantageous for storing small simple lists and data values that are human readable or adjustable by hand. Mostly, all information is kept in a single string file or a table. Examples of this included the dBase III, Paradox and Filemaker. Closely today, a Spreadsheet serves as a better example

This database becomes a disadvantage when the data complicity expands. In this sense, it becomes increasingly difficult to maintain data integrity particularly with the fact that the locking mechanism lacks.

Heathcote (2003, p.93) argues that flat-file databases are problematic in that it becomes increasingly hard to update or modify data, chances of typing errors are high as there are so many records to alter resulting to lack of data integrity and finally data redundancy is the order of the day making data retrieve a nightmare.

On the other hand is the relational database which is a complex organization of data into disparate tables that are linked (related) to each other logically. MySQL, Microsoft SQL Server and Oracle are examples. This database writes off the problems of a flat-file database in that it ensures data integrity, it is more secure in that privileges and access mechanisms are clearly defined making it clear who, how, when to access data and information.

Furthermore, relational database allows for easier data manipulation in the sense that with built in functions such as use of use of SQL, it's possible to query the data in more diverse aspects and also 'Insert', 'Update', 'Delete', 'Create', 'Drop' records in tables seamlessly.

Expanding a relational database for future use is possible compared to flat-file database. This is with the fact that data is held in separate tables.

Data redundancy is reduced in a relational database and this adds to saving storage space (O'Reilly Media, Inc 2005, p.158).There is faster access to data with relational database, particularly where data organization is done to perfection.

It would not be ideal to deploy a relational database if the data is small in amount and one that is rarely updated. In this case scenario, a flat-file database would surface. For frequent usage of data in a large organization with multi-variant data and scalability in mind, relational database would be the best choice.

Principles and structure of the relational database type

Often referred to as the father of relational databases, E. F. Codd, a renowned mathematician and computer scientist while working at IBM in 1960, came up with basic fundamental rules that defines how a relational database should operate. Taking a student relational database as an example, the following is how the principles fits in;

That each row in a table be unique;

Given that relational databases operate in a mix of multiple tables that are linked up, it is only ideal to identify each record in any table uniquely. A student table showing records of students should then have a field for unique IDs to identify each record separate from the other. Any table in particular should then have a column with unique identity tag.

That data be single valued

This implies that data in any field/column should not contain multiple values. Spread data across tables. Linkage should be done via relationships between tables. Normalization in conceptual database design greatly emphasize on this. Better to have multiple tables than to have few tables with multiple valued data fields.

That data should be atomic

Data in a field should be stored in its mot simplest format e.g. in a fields' named name in a student's table, we should not have firstname, lastname. surnames there. Rather these child data items should have their own fields. In other words, different elements of data should be stored in their own data columns.

Keep away from data redundancy.

At all times, any piece of data need be stored at only one place, once.

For example, an additional student should be captured once in the system. If a unique ID is assigned to the student, you are guaranteed of retaining only one aspect of that student's information in the system

Keep away storage of dependant data

This implies that data store in a table should be in its raw format. For example a student's course table showing performances in various subjects should have that data on raw format. It is understood from these figures, several computations can be done. It's the work of the application program to do this on raw data when that kind of results is required. This adds to data integrity. Relational database permits manipulation and update of data and it will be wise to do so while acting on raw data (Communications of the ACM 13(6), 377-387).

Requirements for the EI Database

Organization: XYZ Micro Finance

Scenario: XYZ Micro Finance is small medium bank institution for lending and financing soft loans to its clients. The bank manager concerns is about tracking the clients borrowing pattern so as to project who better qualifies for long term financing as well as the financial growth of the company. For example, he needs to find the clients who are always keeping their word in term of loans repayments agreement with a possibility of discussing a better/higher package for them.

There is a cap as to how much a client can borrow in terms of amount borrowed, the duration, past repayment pattern / ratings (Poor, Good, Delayed).

The Customer care manager is in hand to attend to clients enquiries. He is consulted by the bank manager regarding clients.

The loans are in several categories and are classified as per the amount and depends on client's repayment pattern. For example, A client whose repayment pattern is good can qualify for max loan. Poor repayment qualifies for No loan at all.

XYZ Micro Finance has a number of branches across the country with a capacity to expand globally. The company has stipulated the need for an all inclusive system to cater for this.

Solution/ Requirements

The bank manager should be able to accomplish his tasks freely, faster and with utmost accuracy.

The data entry clerks should be in a position to register the clients seamlessly.

The customer care manager should be able to trace effortlessly client records in attending to their claims.

A staff table to include information about the staff's role.

Staff registers clients.

Clients table to hold information about clients details names, location, telephone, ratings (borrowing pattern), etc

Loans table to hold information about loan types/packages.

An online database system is vital as there are many branches.

Scalability considerations(Robert and Stephen 2007, p.277).

Security arrangement for the protection of client information.

Allow archival of data and information.

Assumptions

The users of the database system will be the bank staff. (Data entry clerks, customer care managers, bank managers). Various user levels is thus eminent requiring access privileges to be clearly defined.

Loans package may be adjusted in the future as per bank manager's analysis of clients. This will require an adjustment of the loans table.

User interface will differ among the users. The data entry clerk will need an interface that can allow capture/registration of clients while a bank manager will require an interface that allows manipulation of already fed data for financial analysis.

Integration is key to the design of this system to cater for all branches in operation.

A fast and robust solution is required(David 2003, p.266).

The choice of database development lies with the designer.

End user training is key when the system is done.

The designer team is not limited to choice of database system. In fact he should propose the best to the company.

Top management is part of the design necessitating the need for prototypes in the design process.

Conceptual design for the EI Database

Conceptual design is the first step in the three major steps of database design and as Morris (1994 p,8) puts it, it translates the scenario into a modeling requirements as visualized by the developer.

This design is better captured as an ERD (Entity Relationship Diagram), so as to define the information system in terms that are familiar to the designer. It is not an implementation per see but rather a framework of requirements as depicted from a given scenario, necessary for further development but which can be refined further as need be. It is this design that is later translated in database schema in step two and physical design in last step.

Generated from a given scenario by identifying actors or the main entities. Then the attributes about the actors are noted. Between the entities are the interactions that define how the information flows.

Conceptual design model shows the entities, their attributes and the relationships that may exist.

Entities (data entry clerk, bank manager, customer care manager, client, loan)

Attributes (such as staff name, location, address, loan package, loan date, loan borrowed, log in time, logout time, date established)

Relationships (registers, consults, attends to, analyses, qualifies)

Data Entry Clerk Manager

Bank Manager

Loan

Client

Customer care Manager

Analyses

Registers

Qualifies

Attends to

Consults

Fig: ER Diagram XYZ Micro Finance

name

role

package

date

name

address

location

cid

role

name

name

role

staff ID

staff ID

staff ID

rating

loan borrowed

date

DBMS for the EI Database

An ideal DBMS for this EI database would be the MySQL. Widenius & Axmark (2002 p.4) enlighten us that MySQL is the largest open source RDBMS (Relational Database Management System) that runs on a server thus providing a client / server architecture. This server can operate for computers in a standalone or distributed structure.

Noting that the scenario for XYZ Micro Finance requires a database system that should manage widespread bank branches across the country, this choice of DBMS fits the occasion. Integration among widespread offices becomes a reality and an easier task.

Further, it is the widespread advantages of this server that makes it more applicable.

Security is a major requirement as depicted from the scenario. As MySQL provides securities features in the form of data encryption and access privileges, then the element of security in terms of system usage and data transmission is perfectly handled.

The scenario depicts a busy organization. This means that it deals with large volumes of data of which updates and manipulation is a frequent agenda. MySQL allows this process seamlessly. This is in the fact that MySQL supports the most strong and frequently used functions and commands i.e. full support of SQL functions and the common combination of select and where parts of querying structure. Extensive usage of these functions ensures that data is widely explored and mined.

As the developer gets to work, the customization/localization aspect of MySQL comes in mind to aid in development, testing and debugging, making the development process smooth and faster. An array of pre-built features accompanies MySQL and will be in handy to the designers' aide (Kahate 2004, p.52).

Scalability is an element of MySQL an ideology which XYZ Micro Finance puts itself in. Thus it would be suitable to adopt this RDBMS so as to take advantage of this great feature.

The choice is between the free open source version of the MySQL server or the commercialized server. Either can work giving the company a boost. The open version is widely used and as such adapting it would be cost saving.

As MySQL supports majority of the programming languages, so will the developer have multiple choices. This eliminates language selection limitations that could hinder faster development on the part of the designer (Kofler 2005, p.7).

Logical data model for the EI Database

Continue from the Conceptual design the logical design. Singh (2011, p.306) outlines this as a process of creating a conceptual schema from the conceptual model created above into a data model as governed by the choice of DBMS selected. Thus this stage takes the Entity Relationship Diagram and translates it into a relational schema with normalized relations.

Guided by the principles of relational database, the following should then be achieved;

Making sure that each table's name is unique.

Each attribute value is atomic

The names of the attributes in tables are distinctive.

The order of the columns or rows is immaterial.

ERD's attributes translates to table fields while entities translates to table

Normalization of the SQL tables where tables are added or dropped.

In summary the following are main steps, though not followed in linear fashion, towards accomplishing a logical database design;

Classify and model the entities.

Classify and model the relationships between the entities.

Classify and model the attributes.

Thus first three already done in conceptual database design process, Solution No 4.

Classify unique identifiers for each entity.

Normalization of the data model.

From earlier ER diagram, and after normalization process, we can get four distinct tables name (Staff, Client, Loan and Branch)

Thus when mapped into relational schema, we get the following;

Staff

#staffID

name

role

Login_time

Logout_time

Client

#clientID

name

address

location

rating

Loan

#loanID

Loan_type

clientID

Loan_borrowed

Date_of_borrowing

Date_of_repayment

Branch

#branchID

name

location

address

date_established

staffID

clientID

Staff table holds data about the three staff members namely Data entry clerks, Customer care manager and the Banks manager. #staffID is the unique column field to distinctively identify each staff member. Depending on their role, they will have different access privileges to Client and to Loan tables. staffID is also a foreign key the in Branch table to uniquely identify the branch the Staff works at.

Client table holds data about the clients and are uniquely identified by #clientID. Note also that this is the foreign key in Loan table thus creating a relationship between the client table's and loan's table. ClientID is also a foreign in Branch table to uniquely identify the branch the Client operates from.

Loan table holds data about loans particular the loan types and those that have been applied for by the clients. The #loanID is the primary key identifying each particular record therein. A foreign key, clientID exists to provide linkage between loans applications and the clients.

Branch table holds data about the braches the company operates. #branchID is the primary key to uniquely identify each branch. Foreign keys here are staffID to identify whose staff works in which branch and the clientID to uniquely link up a client to his/her branch.

Physical design for the EI database

Physical design is summarized as the definition of the database. In broad sense it is that process that translates the logical database model into a physical structure that is more defined ready to be turned into SQL statements and ultimately the database itself.

Singh (2011 p.307) attributes physical database design as that process of defining the file organizations structure, base relations, data types and indexes vital to achieve access and manipulation of data. All elements in a table are defined in what is termed as data definition and the schema represented in a format commonly called database dictionary. In a nutshell, it aims at generating a narrative of the database to be implemented.

When the above logical databases design models are translated into physical design models, the following is achieved;

Table

Column

Data Type

Notes

Staff

staffID

Int (11)

Staff ID which is unique and is the primary key

role

TEXT (20)

Role of the staff

name

TEXT (20)

Name of the staff

Login_time

DATE

Time the staff logs in

Logout_time

DATE

Time the staff logs out.

Client

#clientID

Int (11)

Unique ID for the client. It's a primary key for this table

name

TEXT (20)

Name of the client

location

TEXT (30)

Location for the client

address

TEXT (30)

Address details for the client

rating

TEXT (10)

Loans rating/status

(Poor, Good, Delays)

Loan

#loanID

Int (11)

Unique ID for the loan. It's a primary key for this table

Loan_type

TEXT (30)

The types of loans on offer

clientID

Int (11)

Clients unique ID and a foreign key

Loan_borrowed

float

Amount of loan borrowed in currency format

Date_of_borrowing

DATE

Date when loan was applied and given to the client

Date_of_repayment

DATE

Date when the client should repay the loan.

Branch

#branchID

Int (11)

Branch ID for the company' branch and also the primary key for this table

name

TEXT (30)

Name of the branch

location

TEXT (30)

Location of the branch

address

TEXT (30)

Address of the branch

date_established

DATE

Date when the branch was established

staffID

Int (11)

Staff ID. A foreign key linking to staff table

clientID

Int (11)

Client ID. A foreign key linking to staff table

From the model the column tables shows the table that are going to be created, the columns shows the fields/column that should be in each respective table, the data types is the characteristics of data that will be held in the respective columns and finally the notes tab is a description of what the fields and data type are. Notes are not a requirement but they help greatly in maintenance of the database particularly where there is change of maintaining staff (Halpin and Morgan 2010, p. 27).

Writing data this way makes generation of SQL statements much simpler and smoothens the process of database design.

SQL statements to create and interrogate the EI Database

After physical design, the next vital process is about translating the physical design into SQL commands towards creation of the database, governed by the DBMS earlier chosen. The choice of MySQL guarantees that the at this stage, SQL statements are creatable and more useful functions applicable for data manipulations.

First are the SQL statements to creating the database, then the database tables and finally data manipulations.

Create database SQL statement

CREATE {DATABASE } [IF NOT EXISTS] xyz_micro_finance

This creates a database named xyz_micro_finance if it does not already exist.

Tables are created on an existing database(Dyer, 2009 p.84).

Create table SQL Statements

CREATE table staff (staffID INT(11) NOT NULL AUTO_INCREMENT, role TEXT(20), name TEXT(20), login_time DATE, logout_time DATE, PRIMARY KEY (staffID));

This creates a table named Staff with fields role, name, staffID, login_time, logout_time with staffID as the tables' Primary key.

CREATE table client (clientID INT(11) NOT NULL AUTO_INCREMENT, name TEXT(20), location TEXT(30), address TEXT(30), rating TEXT(10), PRIMARY KEY (clientID));

This creates a table named Client with fields name, clientID, addess, location with clientID as the tables' Primary key.

CREATE table loan (loanID INT (11) NOT NULL AUTO_INCREMENT, clientID INT (11), loan_type TEXT(30), Loan_borrowed FLOAT, Date_of_borrowing DATE, date_of_repayment DATE, Foreign Key (clientID) references CLIENT(clientID),

PRIMARY KEY (loanID));

This create a table named Loan with fields loan_type, loanID, Loan_borrowed, Date_of_borrowing , date_of_repayment, clientID with clientID as the Foreign key and loanID as the tables' Primary key (Sheldon & Moe, 2005 p.157).

CREATE table branch (branchID INT (11) NOT NULL AUTO_INCREMENT, name TEXT(30), location TEXT(30), address TEXT(30), staffID INT(11), clientID INT(11), Foreign Key (clientID) references CLIENT(clientID), Foreign Key (staffID) references STAFF(staffID),

PRIMARY KEY (branchID));

This create a table named Branch with fields name, address, location, clientID, branchID, staffID with branchID as the tables' Primary key while staffID and clientID are the Foreign keys.

SQL Statements to interrogate the EI Database

SELECT * FROM staff;

This returns all records from staff table.

SELECT * FROM client;

This returns all records from client table.

SELECT * FROM loan;

This returns all records from loan table.

SELECT * FROM branch;

This returns all records from branch table.

Insert case;

INSERT INTO client VALUES ('','Patriz','NY','One street','Good'')

This will insert a new record into the client table. The record will have an auto clientID no as the field is defined as an Auto Incrementing one.

Delete case

DELETE FROM staff WHERE staffed = 1002;

This will delete from staff a record whose staff ID is 1002.

Relational case

SELECT * FROM loan, branch where loan.clientID = branch.clientID

This will return all records in loan and branch table where the client ID is stipulated.

SELECT * FROM loan, branch where loan.clientID = branch.clientID and loan. Loan_borrowed >5000

This will return all records in loan and branch tables who have borrowed more than 5000.

Update case

UPDATE staff SET name = "zacks" and role = 'Bank Manager' WHERE staffed = 75

This will update the staff table with the details as required on the record where staff ID is equal to 75.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.