A concern regulation is a policy, process, or criterion that an organisation has adopted. Business regulations are really of import in database design because they dictate controls that must be placed upon the informations. Most concern regulations can be enforced through manual processs that employees are directed to follow or logic placed in the application plans. However, each of these can be avoided – employees may bury or may take non to follow a manual process, and databases can be updated straight by authorised people, short-circuiting the controls included in the application plans. The database can function nicely as the last line of defence. Business regulations can be implemented in the database as restraints, which are officially defined regulations that restrict the information values in the database in some manner. I will lucubrate on restraints in chapter 3.1.4. Note that concern regulations are non usually shown on a conceptual information theoretical account diagram, as was done in Figure 3.1 for easy illustration. It is far more common to include them in a text papers that accompanies the diagram.[ 1 ]
Conceptual Model – Entity-Relationship ( ER ) Model
Conceptual database design involves analyzing and patterning the information in a technology-independent mode. The conceptual informations theoretical account that consequences can be theoretically implemented on any database, or even on a level file system.
The labelled points ( Entity, Attribute, Relationship, Business Rule, and Intersection Data ) of a database design ( Example ) in Figure 3.2 are the basic constituents 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 hold nil, one or, many leave types ‘ balances, but a leave balance can merely belong to one employee.
An employee has merely one section and employee place, whereas a section and employee place must belong to merely one employee.
An employee can hold nil, one or many leave minutess, but a leave dealing merely can belong to one employee.
An employee place must hold at least one or many leave entitlements, and a leave entitlement merely can belong to one employee place.
A leave type can hold nil, one or many leave entitlement, but a leave entitlement can merely belong to one leave type.
A leave type can hold one or many leave dealing, but a leave entitlement can merely belong to one leave type.
A leave dealing can update one or many leave balances, with no relation from leave balance to go forth dealing.
There are 9 types of leave types in the Leave Type entity. They are one-year, medical, hospitalization, pregnancy, paternity, child care, compassionate, matrimony and survey. Each employee will hold different leave entitlement of leave earned based on their occupation place ( Position_ID ) and leave type ( LeaveType_ID ) . Each employee has different Numberss of leave earned based on their place and the company policy on for each type of leave. Similarly, the pertinence of leave type to employees is determined on the gender, matrimonial position, the Numberss of kids they have, and on company-sponsored class. Examples, a married female staff is entitled to pregnancy and child care foliages if she is pregnant and already has one legitimate kid, as compared to a individual female staff who is non eligible to the pregnancy and child care foliages due to her non run intoing the standard of the earned foliages.
When an employee applies for a leave, the leave application can hold more than one O.K.ing officer. The ground being if one O.K.ing officer is absent from company, an alternate O.K.ing officer can let to O.K. or reject a leave dealing.
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 building informations theoretical accounts in database design. The entity-relationship ( E-R ) theoretical account has emerged as the criterion informations theoretical account. The basic elements of this E-R theoretical account was foremost published by Peter Chen in 1976, which later evolved into extended E-R theoretical account which is soon known as the term E-R theoretical account.
An entity is a individual, topographic point, thing, event, or construct about which information is collected. Each entity shown in the conceptual theoretical account represents the full category for that entity. For illustration, the Employee entity ( table ) represents the aggregation of all Vilbert Network Resource employees. The single employees are called cases of the entity.
An property is a unit fact that characterizes or describes an entity in some manner. 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 property ( or attributes ) that appears at the top of the rectangle ( above the horizontal line ) is the alone identifier for the entity. A alone identifier, as the name suggests, provides a alone value for each case of the entity. For illustration, the Customer_ID property is the alone identifier for the Customer entity, so each client must hold a alone value for that property. Keep in head that a alone identifier can be composed of multiple properties, but when this happens, it is still considered merely one alone identifier.
A primary key is a column or a set of columns that unambiguously identifies each row in a tabular array. A alone identifier in the conceptual design is therefore implemented as a primary key in the logical design.
Relationships are the associations among the entities. Because databases are all about hive awaying related informations, the relationships become the gum that holds the database together. Relationships are shown on the conceptual design diagram ( refer to Figure 3.0 ) as lines linking one or more entities. Each terminal of a relationship line shows the maximal cardinality of the relationship, which is the maximal figure of cases of one entity that can be associated with the entity on the opposite terminal of the line. The maximal cardinality may be one ( where the line has no particular symbol on its terminal ) or many ( where the line has a crow ‘s pes on the terminal ) . Just short of the terminal of the line is another symbol that shows the minimal cardinality, which is the minimal figure of cases of one entity that can be associated with the entity on the opposite terminal of the line. The minimal cardinality may be zero, denoted with a circle drawn on the line, or one, denoted with a short perpendicular line or tick grade drawn across the relationship line. Many informations modellers use two perpendicular lines to intend “ one and merely 1. ”[ 3 ]
In the entity-relationship theoretical account, relationships are classified by their cardinality, a word that means “ count ” . The maximal cardinality is the maximal figure of entity cases that can take part in a relationship case. The minimal cardinality is the minimal figure of entity cases that must take part in a relationship case.
In the E-R Diagram in ( Figure 3.2 ) , I have adopted the usage of ERwin Symbol ( crow ‘s pes symbol depict many side of relationship ) to demo the relationships between the entities.
Data Items & A ; Properties
( Mention to Appendix 1 )
Constraints & A ; Premises
A restraint is a regulation placed on a database object ( typically a tabular array or column ) that restricts the allowable informations values for that database object in some manner. These are most of import in relational databases in that restraints are the manner we implement both the relationships and concern regulations specified in the logical design.
A primary key is a column or a set of columns that unambiguously identifies each row in a tabular array. A alone identifier in the conceptual design is therefore implemented as a primary key in the logical design. When we define a primary key, the RDBMS implements it as a primary cardinal restraint to vouch that no two rows in the tabular array will of all time hold extra values in the primary cardinal column ( s ) . Note that for primary keys composed of multiple columns, each column by itself may hold extra values in the tabular array, but the combination of the values for the primary cardinal columns must be alone among all rows in the tabular array.
Primary key restraints are about ever implemented by the RDBMS utilizing an index, which is a particular type of database object that permits fast hunts of column values. As new rows are inserted into the tabular array, the RDBMS automatically searches the index to do certain the value for the primary key of the new row is non already in usage in the tabular array, rejecting the insert petition if it is. Indexs can be searched much faster than tabular arraies ; hence, the index on the primary key is indispensable in tabular arraies of any size so that the hunt for extra keys on every insert does n’t make a public presentation constriction.[ 4 ]
Business regulations from the conceptual design become restraints in the logical design. An unity restraint is a restraint ( as defined earlier ) that promotes the truth of the informations in the database. The cardinal benefit is that these restraints are invoked automatically by the RDBMS and can non be circumvented ( unless you are a DBA ) no affair how you connect to the database. The major types of unity restraints are NOT NULL restraints, CHECK restraints, and restraints enforced with triggers.[ 5 ]
e.g. default NULL values for employee entity in Gender & A ; MaritalStatus attributes in Employee Table
Each relationship between entities in the conceptual design becomes a referential restraint in the logical design. A referential restraint ( sometimes called a referential unity restraint ) is a restraint that enforces a relationship among tabular arraies in a relational database. By “ enforces, ” we mean that the RDBMS automatically checks to guarantee that each foreign cardinal value in a kid tabular array ever has a corresponding primary cardinal value in the parent tabular array.[ 6 ]
Logical Design & A ; Relational Headings
In planing a database system, it is both hard and of import. Determining the proper construction of tabular arraies, the proper relationships among tabular arraies, the appropriate information restraints, and other structural constituents is disputing. Databases arise from three beginnings: from bing informations, from new information system development and from redesign of bing databases.
To plan the LMS databases from the company ‘s bing structured and unstructured information or information which are manually managed, the undermentioned methodological analysiss are to be implemented: –
Requirement Analysis is through interviews, meeting, study and research with the users and assorted stakeholders of the present manual leave system. Furthermore, I will analyse the bing leave signifier to happen out more demands which are non highlighted.
Relational Model provides the ability to associate records as needed instead than predefined when the records are foremost stored in the database. This theoretical account constructed such that questions work with sets of informations instead than one record at a clip, it presents informations in familiar planar tabular arraies, and much like a spreadsheet does.
Entity Relationship Modeling is the iterative procedure of visually stand foring entities, properties, and relationships, bring forthing entity relationship diagram ( ERD ) . The chief advantage of ERD is that it can be understood by non-technical people while supplying great value to proficient people. ( Refer to Fig. 3.1 )
Standardization is the procedure of optimisation in which the potency for redundancy is removed. This is to forestall anomalousnesss in the database. Normalization procedure follows a series of phases called normal signifiers ( NF ) that are reached by using certain regulations.
Indexs improve the public presentation, or velocity, of your database when you run a question.
During the database design & A ; execution procedure, I had begun preliminary surveies on the LMS web site so that some of web pages design can be optimised with the database demand. The following countries which I was focused on for the web design are:
Architecture of the site, concentrating on the layout construction of the pages as a whole, and the options for voyaging the site. ( Refer to Figure 3.3 )
Develop aesthetic and feel of the site, with accent on in writing design and user interface ( UI ) . The UI must hold user-friendliness characteristics built-in for the employees when they are using for foliages and set abouting other leave procedures.
Gathering of information with the right context ( concern policy and civilization of Vilbert Network Resource ) , to be included on the LMS web pages. This can be done through questionnaires study 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 analyze the present manual leave application work flow.
Following, get down the planning procedure by inquiring a few of import inquiries on the web design pages. They are on the ends, completion day of the month and the mark audience of the sites. By replying and analyzing the inquiries, we can hold a glance on the aims of the LMS web pages so that we can plan and develop the web sites successfully to run into the stakeholders ‘ demands and wants.[ 7 ]
Activity Flow Chart
The system architecture in Figure 3.3 shows the basic activities flow chart that model the dynamic behaviour of the LMS.
Figure 3.3 Activity Flow Chart of LMS
From the above Activities Work Flow Diagram, you can cognize the flow of how the staff navigates the web application.
The LMS allows you to use, natural, withdraw and route all types of leave application electronically. You may besides look into your leave records and position ( balance ) of all your leave applications and leave types severally.
To use or retreat your leave, delight finish the leave application/withdrawal online signifier and direct it to your immediate supervisor or authorized approver for blessing. To call off a leave application/withdrawal, merely choose the relevant leave period from your list of pending signifiers and chink on the “ natural ” button consequently.
Once the supervisor/approver has decided on your leave application/withdrawal, an electronic mail will be generated and sent to inform you of the result of your application/withdrawal.
You may re-route your pending signifier to another approver if you have sent it to the incorrect individual. Similarly, if after directing your application, you found that your usual approver is non available ( e.g. off on leave ) to come in his determination for your application, you may re-route it to another authorized approver for action.
Conceptual Web Page Design ( Storyboard )
A storyboard is a study of how you want to near a peculiar undertaking. Making a storyboard before you begin your website design will assist you work on the computing machine more expeditiously because you ‘ll hold a program to follow and will hold already thought out the overall site organisation, the page layout and the page contents. The storyboard gives a “ large image ” position to the overall web undertaking while besides interrupting down a complex undertaking into feasible units that can be addressed separately. It is an easy manner to guarantee the all web pages flows good and is simple to voyage.
Storyboarding is an easy manner to see what information goes on which page and how that page will flux with the other pages on the site. It will besides let me to see how the site pilotage needs to be set up and avoid me holding to remake the bill of fare or content on multiple pages. I followed the 7 Stairss[ 8 ]in planing the LMS web sites. They are:
Roll up the information that you want to include on the Web site. This includes text every bit good as images.
Sort the information into subjects. Subjects should get down at the broadest and work toward the narrowest.
For each page of the Web site, find the layout for the page rubric, headers, sub headers and content. ( Refer to Figure 3.4 )
Plan the construction of the information and how the pages will associate. This needs to be logical. It can be additive, hierarchal or a subdivision design, in the signifier of a web.
Layout the bill of fare construction.
Layout your template page. This should be the interior page of the Web site.
Test the layout. Imagine that you are a visitant to the Web site. You can come in on any page. Does it work? If non, get down once more.
The preliminary design for the web page layout is shown in Figure 3.4.
Figure 3.4 Web Page Layout of LMS ( Storyboard )
In planing the UI for the LMS web pages, I had practiced consistence with the layout of pages throughout the LMS web site. This will assist the users in easiness of pilotage and familiar with the functionality of the characteristics when they visit different pages of the web site. I besides provided good affordance, visibleness and feedback in planing the buttons and bill of fares so as to supply the users ( employees ) with the interactivity and feel required in today web page.