Database Design Concepts British Insurance Company - Assignment Example

Database Design Concepts: British Insurance Company Task 1C: The tool Balsamiq Mockups has been used to create the set of screenshots. The screenshots illustrate the working of the portal of the British Insurance Company. The first screenshot illustrates the working of the customer's Section. When the system administrator will click on the "Customers" button located at the top of the main panel, he will be redirected to the Customer's Section. Here, the admin will be able to add new customers, view existing customers and delete a customer. An admin will also be able to perform a customer related search in the search box provided at the bottom of the page. When the admin will click on any of the buttons, he will be redirected to the related page. The second screenshot illustrates the working of the function "Add new customer". When the system admin will click on the "Add new customer", he will be redirected to this page. The page will inquire for the following details. System Admin can also upload the picture of a customer. Once all the details have been provided, the admin will click on the "save" button in order to record the details in the database. The third screenshot illustrates the working of the Product's Section. When the system administrator will click on the "Products" button located at the in the main panel, he will be redirected to the Product's Section. Here, the admin will be able to add new Products, view existing Products, make a web search to update the product's details and delete a Product. An admin will also be able to perform a Product related search in the search box provided at the bottom of the page. When the admin will click on any of the buttons, he will be redirected to the related page. Task 1D: Role: Manager Features: View Customer Detail Create Customer Account Grant Privileges View Products View Quotations Edit Quotation Test Data: View Customer Detail Grant Privileges Role: Customer Features: View Quotation Schedule Meeting Enter Complain View Contract Details Test Data: View Contract Details Screenshot 1: This Screenshot shows the working of the customer module. A manager or admin of the system can add new customer, view existing customers or delete a customer account through the buttons on the page. A manager or admin can also make a search for any query regarding the customer to make a quick search. Screenshot 2: This Screenshot shows the working of the Product module. A manager or admin of the system can add new product, view existing products, delete a product or make a web search to gather details for the product through the buttons on the page. A manager or admin can also make a search for any query regarding the product to make a quick search. Screenshot 3 When the admin clicks on the button "Add new customer", he will be redirected to the "Add new customer" page. The admin has to enter the mentioned details and click on the "Save" button in order to create a new customer account. Task 2A: Data Dictionary: Table Field Type Field Size Field Description Customer Text Up to 500 characters The details of the customers will be stored in the Customer table Product Memo Up To 64,000 characters The details of the customers will be stored in the Customer table Quotation Text Up to 1000 characters The quotations made by the advisors will be saved in the quotations table Advisor Text Up to 255 characters Names of the advisors will be saved in the advisor table Task 2B: The tool used to create the ER diagram is Microsoft Visio. ER Diagram: 5 entities have been considered in the ER diagram. The product entity contain the following attributes; Description, Product No. and Name. The Database Admin entity contain the following attribute; ID Number. The Customer entity contains the following attributes; Name, reg no, age, address, contract number, date and advisor. The quotation entity contains the following attributes; reg no, client name, advisor name, estimate and date. Similarly the web search entity will make searches for a product from the internet in order to update the details of a product. The Database Admin has a relationship will all other 4 entities of the system. The admin can control the entities by adding, creating and modifying their details. The system has been designed in such a way that the database admin is able to create a new product and add its details, add new customer and add/edit its details. Similarly, the admin can make a web search for a product to update its details. A database admin can enter a quotation sent by an advisor and the admin can forward this quotation to a customer. Task 2C: 1NF: In 1NF, each entry of the table is in a single cell with no repeating entity. Customer Name Contact Number Age Reg No. Address Advisor Quotation No. Quotation Estimate Contract Date Expiry Date Product Taken John 12345 24 1 St5 Steves 45 60,000$ 2013 2015 115 Maria 4567 45 2 St10 Henry 67 15,000$ 2010 2013 223 Anna 3353 34 3 St4 Paul 65 45,000$ 2009 2015 115 John 12345 24 1 St5 Steves 45 60,000$ 2013 2015 118 Maria 4567 45 2 St10 Henry 67 15,000$ 2010 2013 121 John 12345 24 1 St5 Steves 45 60,000$ 2013 2015 121 2NF: For a table to be in 2NF it should be in 1NF and there should be no redundancy. In the above table, the data us being repeated because John and Maria have taken multiple products. The redundancy can be removed by dividing the tables into two tables. In 2NF, we have divided our major table into two sub tables; Customer table and product table. Customer Table: Customer Name Contact Number Age Reg No. Address Advisor Quotation No. Quotation Estimate Contract Date Expiry Date John 12345 24 1 St5 Steves 45 60,000$ 2013 2015 Maria 4567 45 2 St10 Henry 67 15,000$ 2010 2013 Anna 3353 34 3 St4 Paul 65 45,000$ 2009 2015 Product Table: Customer Number Product Taken 1 115 2 223 3 115 1 118 2 121 1 121 3NF: For a table to be in 3NF it should be in 2NF and there should be no transitive functional dependency.In the above tables, the customer is dependent on the advisor for the quotation related attributes. Similarly, the advisor is dependent on the quotation number for the rest of quotation attributes. Therefore, the tables are subdivided into further tables in order to remove the transitive functional dependencies. Customer Table: Customer Name Contact Number Age Reg No. Address Advisor John 12345 24 1 St5 Steves Maria 4567 45 2 St10 Henry Anna 3353 34 3 St4 Paul Product Table: Customer Reg No. Product Taken 1 115 2 223 3 115 1 118 2 121 1 121 Advisor Table: Advisor Quotation No. Steves 45 Henry 67 Paul 65 Quotation Table: Quotation No. Quotation Estimate Contract Date Expiry Date 45 60,000$ 2013 2015 67 15,000$ 2010 2013 65 45,000$ 2009 2015 Task 3A: MySQL Database has been used to create the database. Four tables have been created inside the database. Advisor Customer Product Quotation Task 3B: Test Data has been put into the tables. 3 test entries are inserted into the Advisor table. 6 test entries have been inserted into the Product table. 1 test data has been inserted into the Customer table. Task 3C: User Help Guide: The system has been designed to smooth the flow of the work at the British Insurance Company. The system has been designed so that it is able to perform the following functions: a. Enter the details of a new customer. b. Enter the details of a new product. c. Enter the details insurance quotations sent to customers. d. Enter quotations made by advisors. e. Record valuable ‘web-search’ details of products that have come on the market. Other than these essential features, the system can also perform the following functions: Modify details of a customer View existing customers View existing products Delete a product Delete a Customer View Quotation Make a web search Do a search Login page: When you will access the portal, you will be asked to login into the system by providing the username and password. Once you will successfully provide the username and password, you will be redirected to the login page. Home Page: The Home page will redirect you to all the functions that have been mentioned previously through different buttons. The panel located at the right of the page contains button of all entities of the module. In order to access any of the entity, click on its related button. Customer Page: As a system administrator, you can perform the following functions through this page: Add a new customer View existing customers Delete a customer Search Products page: As a system administrator, you can perform the following functions through this page: Add a product View existing products Delete a Product Make a web search Search Advisor Page: As a system administrator, you can perform the following functions through this page: View Advisor View Advisor respective Quotations Accept Advisor's Quotations Forward Advisor's Quotations to a customer View Advisor Region Quotations Page: As a system administrator, you can perform the following functions through this page: Add a new Quotation View Estimates Forward quotations to customers Web-Search Page: As a system administrator, you can perform the following functions through this page: Make a web search Record web search Update product's details Navigation of the system: User of the system will be redirected to next page through related buttons. A navigation of the system within the pages is explained using the screenshot below. As a system admin, when you login into the system, you will be redirected the main home page. On this homepage, you can access the different modules of the database through the buttons provided at the left panel. In the above screenshot, the user has clicked on the Customer button. On clicking the customer button, the "Customer's Section" is opened. On this page, Customer related info can be added, updated and deleted. The user then clicked on the "Add new Customer" button. A new page opened that asked for the customer related info. This info can be recorded into the database by clicking on the "save" button. Task 3D: Lesson Learned Log: S.no Title Description Impact 1 PhpMyAdmin We were able to explore the tool Phpmyadmin which is a free and open source tool written in PHP in order to administer MySQL Positive 2 Teamwork We were able to get a slight idea of how professional software companies work together when they build efficient and reliable databases Positive 3 Balsamiq It is tool used to create mockups. We got to experience this tool in order to create the screenshots provided throughout the report. Positive 4 Deadline We learned to manage time in order to complete the tasks within the allotted deadlines. Positive 5 Strong concepts of Database The project helped us in sharpening our Database skills and taught us how to practically create a database and insert records into the database. Positive 6 Development Skills Through this project, we were able to get an idea of the development projects. Positive Improvements: Backups: In case of a failure in the database. A backup can help in making an immediate recovery of the system. Data can be retrieved through the backup so that the operations can continue their normal flow. Database Security: Special emphasis should be put upon the security of the system. Databases are vulnerable to multiple threats and risks. One of the most common attacks to a database is the SQL injection. Security must be integrated into the database in order to mitigate security risks. Authentication: The system should only login the user into the system who is authenticated to access the system. The login mechanism should be powerful enough to detect any kind of attack and reject any false authentications Other techniques: The performance of a database can be made fast using a lot of techniques such as cursors, indexes, stored procedures and caches etc. Read More