Database Design and Implementation using ER Model and Microsoft SQL Server 2012, High school final essays of Computer science

Download Database Design and Implementation using ER Model and Microsoft SQL Server 2012 and more High school final essays Computer science in PDF only on Docsity! Higher Nationals Internal verification of assessment decisions – BTEC (RQF) INTERNAL VERIFICATION – ASSESSMENT DECISIONS Programme title Assessor Internal Verifier Unit(s) Unit 04: Database Design & Development Assignment title Data base system for the SmartMovers Transport Company Student’s name List which assessment criteria the Assessor has awarded. Pass Merit Distinction INTERNAL VERIFIER CHECKLIST Do the assessment criteria awarded match those shown in the assignment brief? Y/N Is the Pass/Merit/Distinction grade awarded justified by the assessor’s comments on the student work? Y/N Has the work been assessed accurately? Y/N Is the feedback to the student: Give details: • Constructive? • Linked to relevant assessment criteria? • Identifying opportunities for improved performance? • Agreeing actions? Y/N Y/N Y/N Y/N Does the assessment decision need amending? Y/N Assessor signature Date Internal Verifier signature Date Programme Leader signature (if required) Date Confirm action completed Remedial action taken Give details: Assessor signature Date Internal Verifier signature Date Higher Nationals - Summative Assignment Feedback Form Student Name/ID Unit Title Unit 04: Database Design & Development Assignment Number 1 Assessor Submission Date Date Received 1st submission Re-submission Date Date Received 2nd submission Assessor Feedback: LO1 Use an appropriate design tool to design a relational database system for a substantial problem Pass, Merit & Distinction Descripts P1 M1 D1 LO2 Develop a fully functional relational database system, based on an existing system design Pass, Merit & Distinction Descripts P2 P3 M3 M3 D2 LO3 Test the system against user and system requirements. Pass, Merit & Distinction Descripts P4 M4 D2 LO4 Produce technical and user documentation. Pass, Merit & Distinction Descripts P5 M5 D3 Grade: Assessor Signature: Date: Resubmission Feedback: Grade: Assessor Signature: Date: Internal Verifier’s Comments: Signature & Date: * Please note that grade decisions are provisional. They are only confirmed once internal and external moderation has taken place and grades decisions have been agreed at the assessment board. Programme Leader signature (if required) Date Page 5 of 102 Ahammed Asjath Database Assessment Unit 03 General Guidelines 1. A cover page or title page should be attached to your assignment. Use page 1 of this assignment brief as your cover page and make sure all details are accurately filled. 2. The entire assignment brief should be attached as the first section of your assignment. 3. The assignment should be prepared using a word processing software. 4. The assignment should be printed single sided in an A4 sized paper. 5. Allow 1” margin on top, bottom and right sides of the paper and 1.25” on the left side (for binding). Word Processing Rules 1. The font size should be 12 point, and should be in the style of Time New Roman. 2. Set line spacing to 1.5. Justify all paragraphs. 3. Ensure that all headings are consistent in terms of size and font style. 4. Use footer function on the word processor to insert your name, unit, assignment no, and page number on each page. This is useful if individual sheets get detached from the submission. 5. Use the spell check and grammar check function of the word processing application to review the use of language on your assignment. Page 6 of 102 Ahammed Asjath Database Assessment Unit 03 Important Points: 1. Carefully check carefully the hand in date and the instructions given with the assignment. Late submissions will not be accepted. 2. Ensure that sufficient time is spent to complete the assignment by the due date. 3. Do not wait till the last minute to print or bind the assignment. Such excuses will not be accepted for late submissions. 4. You must be responsible for efficient management of your time. 5. If you are unable to hand in your assignment on time and have valid reasons such as illness, you may apply (in writing) for an extension. 6. Failure to achieve at least a PASS grade will result in a REFERRAL grade. 7. Non-submission of work without valid reasons will lead to an automatic REFERRAL. You will then be asked to complete an alternative assignment. 8. If you use other people’s work or ideas in your assignment, it must be properly referenced, using the HARVARD referencing system, in your text or any bibliography. Otherwise, you’ll be found guilty of committing plagiarism. 9. If you are caught plagiarising, your grade will be reduced to a REFERRAL or at worst, you could be excluded from the course. Page 7 of 102 Ahammed Asjath Database Assessment Unit 03 Student Declaration I hereby, declare that I know what plagiarism entails, namely to use another’s work and to present it as my own without attributing the sources in the correct form. I further understand what it means to copy another’s work. 1. I know that plagiarism is a punishable offence because it constitutes theft. 2. I understand the plagiarism and copying policy of Edexcel UK. 3. I know what the consequences will be if I plagiarise or copy another’s work in any of the assignments for this program. 4. I declare therefore that all work presented by me for every aspect of my program, will be my own, and where I have made use of another’s work, I will attribute the source in the correct way. 5. I acknowledge that the attachment of this document signed or not, constitutes a binding agreement between myself and Edexcel UK. 6. I understand that my assignment will not be considered as submitted if this document is not attached to the assignment. Student’s Signature: Date: (Provide E-mail ID) (Provide Submission Date) Page 10 of 102 Ahammed Asjath Database Assessment Unit 03 The final system should be able to provide the required information for management decision making and to handle daily operations efficiently and effectively. Activity 01 Activity 1.1 a. Identify the user and system requirements to design a database for the above scenario. b. Identify entities and attributes of the given scenario and design a relational database system using conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships. c. Convert the ER Model into logical database design using relational database model including primary keys foreign keys and referential Integrities. It should contain at least five interrelated tables. Note:-It is allowed to have your own assumptions and related attributes within the scope of the case study given. Activity 1.2 a. Explain data normalization with examples. Check whether the provided logical design in task 1.1 is normalised. If not, normalize the database by removing the anomalies. b. Design set of simple interfaces to input and output for the above scenario using Wireframe or any interface-designing tool. c. Explain the validation mechanisms to validate data in the tables with examples. Activity 1.3 a. Check whether the given design (ERD and Logical design) is representing the identified user and system requirements to access the effectiveness of the design. Page 11 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 2 Activity 2.1 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables. a. Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements). b. Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database c. Implement the validation methods explained in task 1.2-part c. d. Display the details payment with the job details and the customer details using Join queries Activity 2.2 a. Explain the usage of DML with below mentioned queries by giving at least one single example per each case from the developed database. Select Update Delete Note: - The queries you have selected should be meaningful and relevant to the given scenario. Activity 2.3 a. Explain how security mechanisms have been used and the importance of these mechanisms for the security of the database. Implement proper security mechanisms (EX: -User groups, access permissions) in the developed database. Page 12 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 2.4 a. Explain the usage of the below SQL statements with the examples from the developed database Group by Order by Having Between Where Activity 3 Activity 3.1 a. Provide a suitable test plan to test the system against user and system requirements b. Provide relevant test cases for the database you have implemented Note:- Learner needs to give expected results in a tabular format and screenshots of the actual results with the conclusion Activity 3.2 a. Explain how the selected test data in task 3.1 b) can be used improve the effectiveness of testing. Activity 3.3 a. Get independent feedback on your database solution from the non- technical users and some developers (use surveys, questioners, interviews or any other feedback collecting method) and make recommendations and suggestions for improvements in a separate conclusion/recommendations section. Page 15 of 102 Ahammed Asjath Database Assessment Unit 03 M2 Implement a fully functional database system that includes system security and database maintenance. M3 Assess whether meaningful data has been extracted using query tools to produce appropriate management information. LO3 Test the systems against user and system requirements P4 Test the system against user and system requirements. M4 Assess the effectiveness of the testing, including an explanation of the choice of test data used. LO2 & LO3 D2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements. LO4 Produce technical and user documentation P5 Produce technical and user documentation. M5 Produce technical and user documentation for a fully functional system, including diagrams showing movement of data through the system, and flowcharts describing how the system works. D3 Assess any future improvements that may be required to ensure the continued effectiveness of the database system. Page 16 of 102 Ahammed Asjath Database Assessment Unit 03 Acknowledgement Acknowledgement is not only a formal, so far additionally an obligation to every one of the persons who have helped in the finishing procedure of the venture. One of the most wonderful angles in meeting the important and critical data and assembling it is the chance to thank every one of the persons who effectively add to it First of all, I Would Thank our ESOFT Manager Mr. V. Niruban, Senior Lecturer of ESOFT Metro Campus and Mr. Piragan ESOFT Metro Campus who gave the specialized way and consistent management all the same his busy timetable Finally, I would like to thank ESOFT Metro Campus and its decent showing board for giving important information and help all through the HND Computing and Systems Improvement program. I am additionally grateful to my family mostly to my parents, for supporting also, promising me to finish this project effectively. Page 17 of 102 Ahammed Asjath Database Assessment Unit 03 Page 20 of 102 Ahammed Asjath Database Assessment Unit 03 Explain the usage of the below SQL statements with the examples from the developed database ....................................................................................................................................... 69 Activity 03 ............................................................................................................................................ 72 Activity 3.1 ....................................................................................................................................... 72 Provide a suitable test plan to test the system against user and system requirements. ........ 72 Provide relevant test cases for the database you have implemented ...................................... 76 Activity 3.2 ....................................................................................................................................... 84 Explain how the selected test data in task 3.1 b) can be used improve the effectiveness of testing. .......................................................................................................................................... 84 Activity 3.3 ....................................................................................................................................... 85 Get independent feedback on your database solution from the non-technical users and some developers (use surveys, questioners, interviews or any other feedback collecting method) and make recommendations and suggestions for improvements in a separate conclusion/recommendations section. ....................................................................................... 85 Recommendations and suggestions for improvements in a separate conclusion / recommendations section. .......................................................................................................... 87 Activity 04 ............................................................................................................................................ 88 Activity 4.1 ....................................................................................................................................... 88 Prepare a simple users’ guide and a technical documentation for the support and maintenance of the software. ...................................................................................................... 88 Activity 4.2 ....................................................................................................................................... 95 Your technical documentation should include some of the UML diagrams (Use case diagram Class diagram, etc.), flow charts for the important functionalities, context level DFD and the Level 1 DFD. ......................................................................................................... 95 Activity 4.3 ....................................................................................................................................... 98 Suggest the future improvements that may be required to ensure the continued effectiveness of the database system. ......................................................................................... 98 Self-Criticism ....................................................................................................................................... 99 Conclusion ......................................................................................................................................... 100 References .......................................................................................................................................... 101 Page 21 of 102 Ahammed Asjath Database Assessment Unit 03 List of Figures Figure 1 ER Diagram for Smart Movers .......................................................................................... 27 Figure 2 Logical Schema .................................................................................................................... 31 Figure 3 Process of Normalization .................................................................................................... 32 Figure 4 User Login ............................................................................................................................ 36 Figure 5 Main Menu ........................................................................................................................... 37 Figure 6 Customer Form .................................................................................................................... 37 Figure 7 Load Form ............................................................................................................................ 38 Figure 8 Product Form ....................................................................................................................... 38 Figure 9 Payment Form ...................................................................................................................... 39 Figure 10 Job Form ............................................................................................................................ 39 Figure 11 Employee Form .................................................................................................................. 40 Figure 12 Employee Form .................................................................................................................. 40 Figure 13 Depot Form ........................................................................................................................ 41 Figure 14 Lorry Form ........................................................................................................................ 41 Figure 15 Container Form ................................................................................................................. 42 Figure 16 Trailer Form ...................................................................................................................... 42 Figure 17 Job Interface Design .......................................................................................................... 45 Figure 18 Product Interface ............................................................................................................... 46 Figure 19 Database on Databases ...................................................................................................... 52 Figure 20 Database Create query ...................................................................................................... 52 Figure 21 User Interface using Visual Studio IDE ........................................................................... 55 Figure 22 Before Data Add on Data Table ....................................................................................... 56 Figure 23 Insert Function Interface .................................................................................................. 56 Figure 24 After Data Save on Data Table ......................................................................................... 57 Figure 25 Before Delete Data row on Data Table ............................................................................ 57 Figure 26 Want to Delete Data Row .................................................................................................. 58 Figure 27 Delete Data Interface ......................................................................................................... 58 Figure 28 Before Update Cus_Id=6 ................................................................................................... 59 Figure 29 After Data Update.............................................................................................................. 59 Figure 30 Data Has Been Changed .................................................................................................... 60 Figure 31 Range Validation Method Example ................................................................................. 60 Figure 32 Finding Data Validation error ......................................................................................... 61 Figure 33 Finding Field Validation ................................................................................................... 61 Figure 34 Select Query ....................................................................................................................... 64 Figure 35 Update Query ..................................................................................................................... 64 Figure 36 Delete Query ....................................................................................................................... 65 Figure 37 Login Interface Form ........................................................................................................ 67 Figure 38 Username Password gone Wrong ..................................................................................... 68 Figure 39 Username and Password is Correct ................................................................................. 68 Figure 40 Group by Query ................................................................................................................. 69 Figure 41 Order by Query ................................................................................................................. 70 Figure 42 Having Query ..................................................................................................................... 70 Figure 43 Between Query ................................................................................................................... 71 Figure 44 Where Query ...................................................................................................................... 71 Figure 45 Test Plan for Smart Movers .............................................................................................. 72 Figure 46 Test Case No1 ..................................................................................................................... 76 Page 22 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 47 Test Case No2 ..................................................................................................................... 77 Figure 48 Test Case No3 ..................................................................................................................... 78 Figure 49 Test Case No4 ..................................................................................................................... 79 Figure 50 Test Case No5 ..................................................................................................................... 80 Figure 51 Test Case No 6 .................................................................................................................... 81 Figure 52 Test Case No 6 .................................................................................................................... 81 Figure 53 Test Case No 7 .................................................................................................................... 82 Figure 54 Test Case No 8 .................................................................................................................... 82 Figure 55 Test Case No 8 .................................................................................................................... 83 Figure 56 Test Case No 8 .................................................................................................................... 83 Figure 57 User Manual for Smart Mover ......................................................................................... 88 Figure 58 Instruction Login Page ...................................................................................................... 91 Figure 59 Username or Password gone Wrong ................................................................................ 91 Figure 60 Username and Password is Correct ................................................................................. 92 Figure 61 Main Menu for This System ............................................................................................. 93 Figure 62 Customer Form in This System ........................................................................................ 93 Figure 63 Software Hardware Requirements................................................................................... 95 Figure 64 Use Case Diagram .............................................................................................................. 96 Figure 65 Flow Chart .......................................................................................................................... 97 Page 25 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 01 Activity 1.1 A Requirement Analysis This is where every software project starts. Requirements serve many purposes for a software development project. For starters, requirements define what the software is supposed to do. The software requirements serve as the basis for all the future design, coding, and testing that will be done on the project. Typically, requirements start out as high-level general statements about the software's functionality as perceived by the users of the software. Requirements are further defined through performance, look and feel, and other criteria. Each top-level requirement is assigned to one or more subsystems within an application. Subsystem-level requirements are further refined and allocated to individual modules. As this process points out, requirements definition is not just a process that takes place at the start of a development project, but an ongoing process. This is especially true when a spiral development model is used. The ability to manage the definition and tracking of requirements is a key process area required of level 2 organizations by the SW-CMM. (Kern, 2001) Smart Movers Provides Four Types Requirements 1. User Requirement 2. System Requirement 3. Software Requirement 4. Hardware Requirement Smart Movers User Requirement 1. Customer records store in Database 2. Customer needs to Deliver Product, Delivery Details must be stored 3. Employee records Details 4. Transport Details 5. Payment Details Smart Movers System Requirement 1. Customer reports (week, daily, month wise) 2. Customer must add in a job. Job have added products details, customer table, product table and job table must in database 3. Transport unit are employees, every transport unit have driver, assistant, lorry, container, trailer, so transport unit, employee, container, trailer tables must in database. Page 26 of 102 Ahammed Asjath Database Assessment Unit 03 Smart Movers Software and Hardware Requirement Table 1 Smart Movers Software and Hardware Requirement Software Requirement Hardware Requirement MS SQL Server 2012 Hard Drive Disk(500 GB) and RAM(2GB) Operating System: Windows 10 Printer(Canon) Visual Studio 2012 Bar Code Reader Drivers Keyboard & Mouse (Wireless) Ice Cream PDF Reader Internet Router (Dialog) Page 27 of 102 Ahammed Asjath Database Assessment Unit 03 ER Diagram for Smart Movers An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An entity in this context is an object, a component of data. An entity set is a collection of similar entities. These entities can have attributes that define its properties. By defining the entities, their attributes, and showing the relationships between them, an ER diagram illustrates the logical structure of databases. ER diagrams are used to sketch out the design of a database. (Anon., 2019) Figure 1 ER Diagram for Smart Movers Page 30 of 102 Ahammed Asjath Database Assessment Unit 03 Foreign Key A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key. A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table (Anon., n.d.) Table 4 Foreign Key Degrees of Relationships (Cardinality) 1. 1 to 1 (1:1) 2. 1 to Many (1:M) 3. Many to Many (M:M) According to the ER Diagram 1 to 1 (Relationship) • One Load has One Payment • One Lorry Connects One Container • One Container attach One Trailer According to the ER Diagram 1 to M (Relationship) • One Customer Sent Many Products • One Transport Unit Have Many Depots • One Depot have Many Lorry’s According to the ER Diagram M to M (Relationship) • Many Products Assign Many Loads • Many Customers creates Many Jobs Entity Foreign Key Customer Product Cus_ID, L_ID Load Payment Cus_ID , P_ID , Tr_ID , J_ID Job Cus_ID , P_ID , Tr_ID , L_ID , D_ID ,E_ID Employee Transport Unit Depot Lorry C_ID , T_ID Container Trailer Page 31 of 102 Ahammed Asjath Database Assessment Unit 03 Logical Schema for Smart Movers A logical schema is a design-centric database structure built to meet your business requirements. It is a model that exists on a white board or in a diagramming tool. It is like the architect's drawings of your database. (Anon., 2014) Figure 2 Logical Schema Page 32 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 1.2 Data normalization Normalization is the way toward reorganizing Data in a database with the goal that it meets two fundamental fundamentals: There is no excess of Data (all Data is put away in just one spot) and Data conditions are smart (every related datum thing are put away together). Normalization is significant for some reasons, however mostly on the grounds that it enables databases to inhabit as scanty circle room as could be allowed, bringing about expanded execution. Normalization is else called Data Normalization. Purpose of the Data Normalization The main purpose of normalization is to minimize the redundancy and remove Insert, Update and Delete Anomaly. It divides larger tables to smaller tables and links them using relationships. Data redundancy happens when the same piece of data is held in two separate places. Process of Normalization Figure 3 Process of Normalization Page 35 of 102 Ahammed Asjath Database Assessment Unit 03 Third Normal Form (3NF) Third normal form (3NF) is the third step in normalizing a database and it builds on the first and second normal forms, 1NF and 2NF. 3NF states that all column reference in referenced data that are not dependent on the primary key should be removed. Another way of putting this is that only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table Page 36 of 102 Ahammed Asjath Database Assessment Unit 03 Design set of simple interfaces to input and output for the above scenario using Wireframe or any interface-designing tool. There some Interface Design Using Wireframe Figure 4 User Login Page 37 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 5 Main Menu Figure 6 Customer Form Page 40 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 11 Employee Form Figure 12 Employee Form Page 41 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 13 Depot Form Figure 14 Lorry Form Page 42 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 15 Container Form Figure 16 Trailer Form Page 45 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 1.3 Check whether the given design (ERD and Logical design) is representing the identified user and system requirements to access the effectiveness of the design. The database is designed according to the user and system requirements. To accomplish these requirements writer designed some interfaces for each function. The database is designed according to the user and system requirements. To accomplish these requirements Developer designed some interfaces for each function. By Using This Interface User Can insert Job Details for the Database. There are some interfaces also there ex. Product, Payment, Customer and etc. Nonfunctional requirements also include for the design of the database. There is a login interfaces for keep a good security. It is also a user requirement. This system is easy to maintain an easy to use. User main requirement is to use the system very easy and Secure. This system design according to the system and user requirements. This design characterize all the requirement according to the scenario. Visual studio 2012 software and SQL server 2012 software helped to create this system. Figure 17 Job Interface Design Page 46 of 102 Ahammed Asjath Database Assessment Unit 03 Ex: user wants to update Customer Table. Then Administrator created update function in Customer interface. Therefore, this requirement represented by this interface. Users wants to know about the products in the company. Then Administrator created a Product interface for it. Therefore, this design represent user and system requirements. Figure 18 Product Interface Page 47 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 02 Activity 2.1 Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements). Create Database SMART_MOVER_DB Use SAMART_MOVER_DB Create Table Customer_Tbl ( Cus_Id int Identity, Cus_Fname Varchar (100) not null, Cus_Lname varchar (100)not null, Cus_Telno Numeric (10)not null, Cus_Address Varchar (250)not null, Cus_Type Varchar (5)not null, Constraint C_pk Primary Key (Cus_Id), constraint chk_Cus check (Cus_Type in ('C1','C2','C3')) ); Create Table Product_Tbl ( P_Id int Identity, P_Name Varchar (100) not null, P_Type Varchar (15) not null, Cus_Id int, L_Id int, Constraint p_pk Primary Key (P_ID), Constraint Chk_P Check (P_Type in ('High Risk','Low Risk')), Constraint cus_fk foreign key(Cus_Id) references Customer_Tbl(Cus_Id) on delete cascade, Constraint load_fk foreign key(L_Id) Page 50 of 102 Ahammed Asjath Database Assessment Unit 03 ( J_Id int Identity, Start_Location Varchar (100) not null, Delivery_Location Varchar (100) not null, Cus_Id int, P_Id int, Tr_Id int, L_Id int, D_Id int, E_Id int, Constraint J_pk Primary Key (J_Id), Constraint cs_fk foreign key(Cus_Id) references Customer_Tbl(Cus_Id) on delete cascade, Constraint pro_fk foreign key(P_Id) references Product_Tbl(P_Id), Constraint Tr_fk foreign key(Tr_Id) references TransportUnit_Tbl(Tr_Id), Constraint L_fk foreign key(L_Id) references Load_Tbl(L_Id), Constraint D_fk foreign key(D_Id) references Depot_Tbl(D_Id) on delete cascade, Constraint E_fk foreign key(E_Id) references Employee_Tbl(E_Id) on delete cascade, ); Page 51 of 102 Ahammed Asjath Database Assessment Unit 03 Create table Lorry_Tbl ( Lr_Id int Identity, Lr_Lic Varchar (100), L_Unit Varchar (100), Cus_Id int, Tr_Id int, Constraint Lorry_pk Primary Key (Lr_Id), Constraint cos_lorry_fk foreign key(Cus_Id) references Customer_Tbl(Cus_Id) on delete cascade, Constraint trans_fk foreign key(Tr_Id) references TransportUnit_Tbl(Tr_Id) on delete cascade, ); Create Table Container_Tbl ( Con_Id Int Identity, Con_Type Varchar (25), Constraint Con_pk Primary Key (Con_Id), Constraint chk_C Check (Con_Type in ('Small','Medium','Large')) ); Create Table Trailer_Tbl ( T_Id Int Identity, T_Type Varchar (250), Constraint Trail_pk Primary Key (T_Id), Constraint chk_T Check (T_Type in ('Small','Medium','Large')) ); Page 52 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 20 Database Create query Figure 19 Database on Databases Database Created Page 55 of 102 Ahammed Asjath Database Assessment Unit 03 Visual Studio Is this IDE Software. Which me using to create Interfaces and System for above Scenario. In Visual Studio have so many Versions. I Used for This Interfaces Visual Studio 2012. This is the Interface Which I Create Using Visual Studio IDE Figure 21 User Interface using Visual Studio IDE Page 56 of 102 Ahammed Asjath Database Assessment Unit 03 Insert Function This Picture was before Add Data row on Data Table After Data Save on Data Table Figure 22 Before Data Add on Data Table Figure 23 Insert Function Interface Page 57 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 24 After Data Save on Data Table Delete Function This Picture was before Delete Data row on Data Table Figure 25 Before Delete Data row on Data Table Page 60 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 30 Data Has Been Changed Implement the validation methods explained in task 1.2-part c. Range Validation This validation method is used to check the field range. It means Cus_Telno data type is numeric (10). This field only can enter 10 characters. This is The Range Validation Method Example: Figure 31 Range Validation Method Example Page 61 of 102 Ahammed Asjath Database Assessment Unit 03 Data Type Validation Each field takes a data type. It will be only can enter numbers, characters or together. I Used to Find Data Type Validation the Cus_Id is field data type is int. So, it cannot enter characters and it only allocate numbers. After entering a character for that field, the system shows an error message. Figure 32 Finding Data Validation error Field Validation The Customer Address field is empty and an error message will show when inserting data. Developer used field validation for this system. If a field is empty, then an error message will display. It is easy to the users to identify the error. Then users can avoid from errors. Figure 33 Finding Field Validation Page 62 of 102 Ahammed Asjath Database Assessment Unit 03 Display the details payment with the job details and the customer details using Join queries SELECT Payment_Tbl.*, Customer_Tbl.*, Job_Tbl.* FROM Payment_Tbl INNER JOIN Customer_Tbl ON Payment_Tbl.Cus_Id = Customer_Tbl.Cus_Id INNER JOIN Job_Tbl ON Payment_Tbl.J_Id = Job_Tbl.J_Id AND Customer_Tbl.Cus_Id = Job_Tbl.Cus_Id Page 65 of 102 Ahammed Asjath Database Assessment Unit 03 Delete Query DELETE FROM tableName WHERE someColumn = someValue DELETE FROM Customer_Tbl WHERE Cus_Id = 4 Figure 36 Delete Query Page 66 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 2.3 Explain how security mechanisms have been used and the importance of these mechanisms for the security of the database. Implement proper security mechanisms (EX: -User groups, access permissions) in the developed database. Database Security Database security refers to the collective measures used to protect and secure a database or database management software from illegitimate use and malicious threats and attacks. It is a broad term that includes a multitude of processes, tools and methodologies that ensure security within a database environment. (techopedia, n.d.) Security plan • Identify the user community. • Gather the database information. • Determine the types of user account (i.e. associate database objects and user roles). • Undertake a threat analysis. • Establish DBA Developer ties and procedures. • Establish policies for managing (creating, deleting, auditing) user accounts. • Determine the user tracking policy. • Establish the user identification method. • Define security incidents and reporting procedure. • Assess the sensitivity of specific data objects. • Establish standards and enforcement procedures (as well as back-up and recovery plans, of course). Page 67 of 102 Ahammed Asjath Database Assessment Unit 03 Developer has tool the system with security mechanism above mention. There are some samples in below: Login Interface Form Figure 37 Login Interface Form User can easily enter system using this login interface form. anybody can’t access this login those who have username and Password. This Type of Login Very Secure Page 70 of 102 Ahammed Asjath Database Assessment Unit 03 Order By SELECT * FROM Customer_Tbl ORDER BY Cus_Lname Asc; Having SELECT count (Cus_Id) , Cus_Id FROM Customer_Tbl Group by Cus_Id HAVING count (Cus_Id)<=5 ; Figure 41 Order by Query Figure 42 Having Query Page 71 of 102 Ahammed Asjath Database Assessment Unit 03 Between SELECT * FROM Customer_Tbl WHERE Cus_Id BETWEEN 2 AND 8 ; Where SELECT * FROM Customer_Tbl WHERE Cus_Id=3 Figure 43 Between Query Figure 44 Where Query Page 72 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 03 Activity 3.1 Provide a suitable test plan to test the system against user and system requirements. Figure 45 Test Plan for Smart Movers Page 75 of 102 Ahammed Asjath Database Assessment Unit 03 Testing Procedure Table 7 Test Case Interface Test case no Description 01 Login when entering valid username and password 02 Login when entering invalid username and password 03 Insert details 04 Update details 05 Delete details 06 Insert a record to the database using SQL Query. 07 Update a record to the database using SQL Query. 08 Delete a record to the database using SQL Query. Page 76 of 102 Ahammed Asjath Database Assessment Unit 03 Provide relevant test cases for the database you have implemented Test Login Test Case No 1 : Login when entering valid username and password Table 8 Test Case No1 Actual Result Test case Input data Expected result Actual result Test outcome (Did/Didn’t) Tester has entering valid username and password Username: User Password: User123 Display a “Login Successful” Displayed a “Login Successful” Did Figure 46 Test Case No1 Page 77 of 102 Ahammed Asjath Database Assessment Unit 03 Test Case No 2: Login when entering invalid username and password Table 9 Test Case No 2 Actual Result Figure 47 Test Case No2 Test case Input data Expected result Actual result Test outcome (Did/Didn’t) Test Has entering invalid username and password Username: Admin Password: Admin123 Display a “Please Check Username or Password” Displayed a “Please Check Username or Password” Did Page 80 of 102 Ahammed Asjath Database Assessment Unit 03 Test Case No 5: Delete details Table 12 Test Case No 5 Actual Result Figure 50 Test Case No5 Test case Input data Expected result Actual result Test outcome (Did/Didn’t) Test Has entering delete a data row in the customer table. Delete Row Cus_Id: 9 Display a “Deleted Successfully” Displayed a “Deleted Successfully” Did Page 81 of 102 Ahammed Asjath Database Assessment Unit 03 Test Case No 6: Insert a record to the database using SQL Query. Table 13 Test Case No 6 Figure 52 Test Case No 6 Test case Input data Expected result Actual result Test outcome (Did/Didn’t) Test Has entering Insert a record to the Customer table using SQL Query. Cus_Id:11 Cus_Fname: Mohamed Cus_Lname: Shiraz Cus_Telno: 0774521212 Cus_Address:Kattankudi Cus_Type:C1 Display a “1 row(s) affected” Displayed a “1 row(s) affected” Did Figure 51 Test Case No 6 Page 82 of 102 Ahammed Asjath Database Assessment Unit 03 Test Case No 7: Update a record to the database using SQL Query. Table 14 Test Case No 7 Figure 53 Test Case No 7 Figure 54 Test Case No 8 Test case Input data Expected result Actual result Test outcome (Did/Didn’t) Test Has entering Update a record to the Customer table using SQL Query. Cus_Fname: Mohamed Cus_Lname: Shiraz Cus_Telno: 0774521212 Cus_Address: Kalmunai Cus_Type:C1 Display a “1 row(s) affected” Displayed a “1 row(1) affected” Did Page 85 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 3.3 Get independent feedback on your database solution from the non-technical users and some developers (use surveys, questioners, interviews or any other feedback collecting method) and make recommendations and suggestions for improvements in a separate conclusion/recommendations section. Figure 57 supervisor Feedback Page 86 of 102 Ahammed Asjath Database Assessment Unit 03 Figure 58 Peer Friend Feedback Page 87 of 102 Ahammed Asjath Database Assessment Unit 03 Recommendations and suggestions for improvements in a separate conclusion / recommendations section. According to the feedback summary there are some references for this system. Manageability is not satisfied of this software. Therefore developer have to develop it. To increase Manageability developer have to reduce mouse clicks. It have to easy to install an update. Graphical User Interface should be developed according to user’s feedbacks. Graphical User Interface should have easy to use. Developer can encode the password for tool the security. System functionalities have to divide for advantaged. This can help to improve effectiveness of the system. The developer can well-arranged the system for users which have attraction. Considering these references Developer can tool the system. Then the users can have a superiority system. Page 90 of 102 Ahammed Asjath Database Assessment Unit 03 Introduction User Manual It is technical document future to give help to people using a particular system. It helps to people to understand a software application. It contains simple language format and simple sentences. This simple language format helps to users to understand it. User can get a clear idea about the system by using user manual. User manual include all the details to execute the system step by step. Purpose of the Database System. Main purpose of the user manual is to deliver strong information for users. How to implement the system step by step. Database System This is a transport system for Smart movers Company. The main process is to transport products for the customers. Customer stand registered for the system. Other register tasks are Payment, Product , Load Etc. All the records are store in the transport database. Developer can edit Data on Database. Page 91 of 102 Ahammed Asjath Database Assessment Unit 03 Instruction for Users If the user already have an account then they can enter the username and password. After entering it click the login button. Then move into next interface. If User Have Account they can’t access this System If Username or Password gone Wrong System will Display: Figure 60 Instruction Login Page Figure 61 Username or Password gone Wrong Page 92 of 102 Ahammed Asjath Database Assessment Unit 03 If Username and Password is Correct System Will Display: Figure 62 Username and Password is Correct Page 95 of 102 Ahammed Asjath Database Assessment Unit 03 Activity 4.2 Your technical documentation should include some of the UML diagrams (Use case diagram Class diagram, etc.), flow charts for the important functionalities, context level DFD and the Level 1 DFD. Software and Hardware Requirements. Figure 65 Software Hardware Requirements Page 96 of 102 Ahammed Asjath Database Assessment Unit 03 Use Case Diagram Figure 66 Use Case Diagram Page 97 of 102 Ahammed Asjath Database Assessment Unit 03 Flow Chart Figure 67 Flow Chart