Study Of System And Database Design Computer Science Essay

Published: Last Edited:

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

A business rule is a policy, procedure, or standard that an organization has adopted. Business rules are very important in database design because they dictate controls that must be placed upon the data. Most business rules can be enforced through manual procedures that employees are directed to follow or logic placed in the application programs. However, each of these can be avoided - employees may forget or may choose not to follow a manual procedure, and databases can be updated directly by authorized people, bypassing the controls included in the application programs. The database can serve nicely as the last line of defense. Business rules can be implemented in the database as constraints, which are formally defined rules that restrict the data values in the database in some way. I will elaborate on constraints in chapter 3.1.4. Note that business rules are not normally shown on a conceptual data model diagram, as was done in Figure 3.1 for easy illustration. It is far more common to include them in a text document that accompanies the diagram. [1] 

Conceptual database design involves studying and modeling the data in a technology-independent manner. The conceptual data model that results can be theoretically implemented on any database, or even on a flat file system.

The labeled items (Entity, Attribute, Relationship, Business Rule, and Intersection Data) of a database design (Example) in Figure 3.2 are the basic components that make up a conceptual database design. [2] 

Figure 3.1 - Conceptual Database Design (Example)

Figure 3.2 - Entity-Relationship Diagram for LMS

In the relational database of LMS, the logics in the Figure 3.2 are as followed:

An employee can have nil, one or, many leave types' balances, but a leave balance can only belong to one employee.

An employee has only one department and employee position, whereas a department and employee position must belong to only one employee.

An employee can have nil, one or many leave transactions, but a leave transaction only can belong to one employee.

An employee position must have at least one or many leave entitlements, and a leave entitlement only can belong to one employee position.

A leave type can have nil, one or many leave entitlement, but a leave entitlement can only belong to one leave type.

A leave type can have one or many leave transaction, but a leave entitlement can only belong to one leave type.

A leave transaction can update one or many leave balances, with no relation from leave balance to leave transaction.

There are 9 types of leave types in the Leave Type entity. They are annual, medical, hospitalization, maternity, paternity, childcare, compassionate, marriage and study. Each employee will have different leave entitlement of leave earned based on their job position (Position_ID) and leave type (LeaveType_ID). Each employee has different numbers of leave earned based on their position and the company policy on for each type of leave. Similarly, the applicability of leave type to employees is determined on the gender, marital status, the numbers of children they have, and on company-sponsored course. Examples, a married female staff is entitled to maternity and childcare leaves if she is pregnant and already has one legitimate child, as compared to a single female staff who is not eligible to the maternity and childcare leaves due to her not meeting the criteria of the earned leaves.

When an employee applies for a leave, the leave application can have more than one approving officer. The reason being if one approving officer is absent from company, an alternative approving officer can allow to approve or reject a leave transaction.

Entity Types and Attributes for the ERD in Fig. 3.2

Employee (Employee_ID, Type, First_Name, Last_Name, Username, Password, Department_ID, , Position_ID, Tel_No, Email_Add, MaritalStatus, Gender, NoofChildren)

Employee_Position (Position_ID, Position_Description)

Department (Department_ID, Department_Description)

Leave_Transaction (LeaveTransact_ID, LeaveType_ID, Employee_ID, Date_From,

, Date_To, Session, Leave_Taken, Reason, Address, EmployeeCoverDuty, ApprovalTarget, ApprovalStatus, LastUpdatedDateTime, LastUpdated By)

Leave_Entitlement (LeaveType_ID, Position_ID, No_of_Day)

Leave_Balance (LeaveType_ID, Employee_ID, Leave_Earn, Leave_Pending, Leave, Leave_Taken, Leave_Balance)

Leave_Type (LeaveType_ID, LeaveType_Description)

There are many tools and techniques for constructing data models in database design. The entity-relationship (E-R) model has emerged as the standard data model. The basic elements of this E-R model was first published by Peter Chen in 1976, which subsequently evolved into extended E-R model which is presently known as the term E-R model.


An entity is a person, place, thing, event, or concept about which data is collected. Each entity shown in the conceptual model represents the entire class for that entity. For example, the Employee entity (table) represents the collection of all Vilbert Network Resource employees. The individual employees are called instances of the entity.


An attribute is a unit fact that characterizes or describes an entity in some way. These are represented on the conceptual design diagram shown in Figure 3.0 as names inside the rectangle that represents the entity to which they belong. The attribute (or attributes) that appears at the top of the rectangle (above the horizontal line) is the unique identifier for the entity. A unique identifier, as the name suggests, provides a unique value for each instance of the entity. For example, the Customer_ID attribute is the unique identifier for the Customer entity, so each customer must have a unique value for that attribute. Keep in mind that a unique identifier can be composed of multiple attributes, but when this happens, it is still considered just one unique identifier.


A primary key is a column or a set of columns that uniquely identifies each row in a table. A unique identifier in the conceptual design is thus implemented as a primary key in the logical design.


Relationships are the associations among the entities. Because databases are all about storing related data, the relationships become the glue that holds the database together. Relationships are shown on the conceptual design diagram (refer to Figure 3.0) as lines connecting one or more entities. Each end of a relationship line shows the maximum cardinality of the relationship, which is the maximum number of instances of one entity that can be associated with the entity on the opposite end of the line. The maximum cardinality may be one (where the line has no special symbol on its end) or many (where the line has a crow's foot on the end). Just short of the end of the line is another symbol that shows the minimum cardinality, which is the minimum number of instances of one entity that can be associated with the entity on the opposite end of the line. The minimum cardinality may be zero, denoted with a circle drawn on the line, or one, denoted with a short vertical line or tick mark drawn across the relationship line. Many data modelers use two vertical lines to mean "one and only one." [3] 

In the entity-relationship model, relationships are classified by their cardinality, a word that means "count". The maximum cardinality is the maximum number of entity instances that can participate in a relationship instance. The minimum cardinality is the minimum number of entity instances that must participate in a relationship instance.

In the E-R Diagram in (Figure 3.2), I have adopted the use of ERwin Symbol (crow's foot symbol depict many side of relationship) to show the relationships between the entities.

Data Items & Attributes

(Refer to Appendix 1)

Constraints & Assumptions

A constraint is a rule placed on a database object (typically a table or column) that restricts the allowable data values for that database object in some way. These are most important in relational databases in that constraints are the way we implement both the relationships and business rules specified in the logical design.

A primary key is a column or a set of columns that uniquely identifies each row in a table. A unique identifier in the conceptual design is thus implemented as a primary key in the logical design. When we define a primary key, the RDBMS implements it as a primary key constraint to guarantee that no two rows in the table will ever have duplicate values in the primary key column(s). Note that for primary keys composed of multiple columns, each column by itself may have duplicate values in the table, but the combination of the values for the primary key columns must be unique among all rows in the table.

Primary key constraints are nearly always implemented by the RDBMS using an index, which is a special type of database object that permits fast searches of column values. As new rows are inserted into the table, the RDBMS automatically searches the index to make sure the value for the primary key of the new row is not already in use in the table, rejecting the insert request if it is. Indexes can be searched much faster than tables; therefore, the index on the primary key is essential in tables of any size so that the search for duplicate keys on every insert doesn't create a performance bottleneck. [4] 

Business rules from the conceptual design become constraints in the logical design. An integrity constraint is a constraint (as defined earlier) that promotes the accuracy of the data in the database. The key benefit is that these constraints are invoked automatically by the RDBMS and cannot be circumvented (unless you are a DBA) no matter how you connect to the database. The major types of integrity constraints are NOT NULL constraints, CHECK constraints, and constraints enforced with triggers. [5] 

e.g. default NULL values for employee entity in Gender & MaritalStatus attributes in Employee Table

Each relationship between entities in the conceptual design becomes a referential constraint in the logical design. A referential constraint (sometimes called a referential integrity constraint) is a constraint that enforces a relationship among tables in a relational database. By "enforces," we mean that the RDBMS automatically checks to ensure that each foreign key value in a child table always has a corresponding primary key value in the parent table. [6] 

Logical Design & Relational Headings

In designing a database system, it is both difficult and important. Determining the proper structure of tables, the proper relationships among tables, the appropriate data constraints, and other structural components is challenging. Databases arise from three sources: from existing data, from new information system development and from redesign of existing databases.

To design the LMS databases from the company's existing structured and unstructured information or data which are manually managed, the following methodologies are to be implemented : -

Requirement Analysis is through interviews, meeting, survey and research with the users and various stakeholders of the present manual leave system. Furthermore, I will analyze the existing leave form to find out more requirements which are not highlighted.

Relational Model provides the ability to relate records as needed rather than predefined when the records are first stored in the database. This model constructed such that queries work with sets of data rather than one record at a time, it presents data in familiar two-dimensional tables, and much like a spreadsheet does.

Entity Relationship Modeling is the iterative process of visually representing entities, attributes, and relationships, producing entity relationship diagram (ERD). The main advantage of ERD is that it can be understood by non-technical people while providing great value to technical people. (Refer to Fig. 3.1)

Normalization is the process of optimization in which the potential for redundancy is removed. This is to prevent anomalies in the database. Normalization process follows a series of stages called normal forms (NF) that are reached by applying certain rules.

Indexes improve the performance, or speed, of your database when you run a query.

Web Design

During the database design & implementation process, I had begun preliminary studies on the LMS web site so that some of web pages design can be optimised with the database requirement. The following areas which I was focused on for the web design are:

Architecture of the site, focusing on the layout structure of the pages as a whole, and the options for navigating the site. (Refer to Figure 3.3)

Develop aesthetic and feel of the site, with emphasis on graphic design and user interface (UI). The UI must have user-friendliness features built-in for the employees when they are applying for leaves and undertaking other leave processes.

Gathering of information with the right context (business policy and culture of Vilbert Network Resource), to be included on the LMS web pages. This can be done through questionnaires survey of the company staff on how and what they want for the LMS, face-to face interactions with relevant stakeholders which have influence over the system design, and study the present manual leave application workflow.

Next, begin the planning process by asking a few important questions on the web design pages. They are on the goals, completion date and the target audience of the sites. By answering and analysing the questions, we can have a glimpse on the objectives of the LMS web pages so that we can design and develop the web sites successfully to meet the stakeholders' needs and wants. [7] 

Activity Flow Chart

The system architecture in Figure 3.3 shows the basic activities flow chart that model the dynamic behavior of the LMS.

Figure 3.3 Activity Flow Chart of LMS

From the above Activities Work Flow Diagram, you can know the flow of how the staff navigates the web application.

The LMS allows you to apply, cancel, withdraw and route all types of leave application electronically. You may also check your leave records and status (balance) of all your leave applications and leave types respectively.

To apply or withdraw your leave, please complete the leave application/withdrawal on-line form and send it to your immediate supervisor or authorized approver for approval. To cancel a leave application/withdrawal, just select the relevant leave period from your list of pending forms and click on the "cancel" button accordingly.

Once the supervisor/approver has decided on your leave application/withdrawal, an e-mail will be generated and sent to inform you of the outcome of your application/withdrawal.

You may re-route your pending form to another approver if you have sent it to the wrong person. Similarly, if after sending your application, you found that your usual approver is not available (e.g. away on leave) to enter his decision for your application, you may re-route it to another authorized approver for action.

Conceptual Web Page Design (Storyboard)

A storyboard is a sketch of how you want to approach a particular project. Creating a storyboard before you begin your website design will help you work on the computer more efficiently because you'll have a plan to follow and will have already thought out the overall site organization, the page layout and the page contents. The storyboard gives a "big picture" perspective to the overall web project while also breaking down a complex project into workable units that can be addressed individually. It is an easy way to ensure the all web pages flows well and is simple to navigate.

Storyboarding is an easy way to see what information goes on which page and how that page will flow with the other pages on the site. It will also allow me to see how the site navigation needs to be set up and avoid me having to redo the menus or content on multiple pages. I followed the 7 Steps [8] in designing the LMS web sites. They are:

Collect the information that you want to include on the Web site. This includes text as well as images.

Sort the information into topics. Topics should start at the broadest and work toward the narrowest.

For each page of the Web site, determine the layout for the page title, headings, sub headings and content. (Refer to Figure 3.4)

Plan the structure of the information and how the pages will link. This needs to be logical. It can be linear, hierarchical or a branch design, in the form of a web.

Layout the menu structure.

Layout your template page. This should be the interior page of the Web site.

Test the layout. Imagine that you are a visitor to the Web site. You can enter on any page. Does it work? If not, start again.

The preliminary design for the web page layout is shown in Figure 3.4.

Figure 3.4 Web Page Layout of LMS (Storyboard)

In designing the UI for the LMS web pages, I had practiced consistency with the layout of pages throughout the LMS web site. This will help the users in ease of navigation and familiar with the functionality of the features when they visit different pages of the web site. I also provided good affordance, visibility and feedback in designing the buttons and menus so as to provide the users (employees) with the interactivity and feel required in today web page.