This report provides information on database development, including employees without title or department, departments and their names, and SQL queries for update and join statements.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
1 Submission Coversheet (All Programmes) Student ID Number(Do not include student name as anonymous marking is implemented) COR17454166 Programme TitleBSc Computing Technologies (Day) Module TitleData Modelling & SQL Language Module Code (listed on Moodle and in LTAFP)QAC020C155A Module ConvenerSharjeel Aslam Coursework TitleCourse 2 Academic Declaration:Students are reminded that the electronic copy of their essay may be checked, at any point during their degree, with Turnitin or other plagiarism detection software for plagiarised material. Word Count1667Date Submitted19/12/2018 COR17454166
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
2 Contents Database Development Report....................................................................................3 Database Testing..........................................................................................................8 Testing on Employee Database...................................................................................9 Referencing and Bibliography....................................................................................13 COR17454166
3 Database Development Report Please note there are some employees without title that are not displayed in the table. There are some employees without department and salary. However they are not displayed here in this table. COR17454166
4 There is only one employee in the department Human resources. The name of all departments are displayed along with the name(Microsoft, 2018a). Year function has been used here(Refsnes Data, 2018a). COR17454166
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
5 UPDATEtitlesSETtitle='Admin'fromemployeesfulljointitleson employees.emp_no = titles.emp_no where last_name = 'Bamford'; Select * from titles inner join employees on titles.emp_no = employees.emp_no; Information regarding update and join statement has displayed using he SQL queries update(Tech on the Net, 2018a) and (Ben Nadel, 2018). DELETE from dept_emp where EXISTS (select * from employees, departments wheredept_emp.emp_no=employees.emp_noanddepartments.dept_no= dept_emp.dept_no and departments.dept_no = 'd004' and employees.emp_no = '10003'); COR17454166
6 select*fromemployees,departments,dept_empwheredept_emp.emp_no= employees.emp_noanddepartments.dept_no=dept_emp.dept_noand departments.dept_no = 'd004' and employees.emp_no = '10003'; 10003 has been successfully removed from the tables. SELECT * FROM `emp_details` There are some employees who are not working in any department and has not assigned with any manager. The salary of listed managers are displayed. COR17454166
7 SELECT * FROM `dept_managers` UPDATE salaries, employees, dept_emp SET salary = salary * 1.1 WHEREemployees.emp_no=salaries.emp_noandemployees.emp_no= dept_emp.emp_no and dept_emp.dept_no = 'd001'; SELECT*FROM`salaries`,employeesWHEREemployees.emp_no= salaries.emp_no COR17454166
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
8 From this it can be understood that there is no employee from department marketing (Microsoft, 2018b). Database Testing Database testing plays a very crucial part of software testing. Database testing is important for ensuring that all the values associated with an application has retrieved or stored into the database accurately. It is important to ensure that the tester is having the knowledge regarding SQL and the techniques used in SQL(Test Triangle, 2012). The reason behind the wide use of database testing is it offers different IT companies with effective solution, this are explained below: For verifying data mapping Mainlyalltheapplicationcontains3layersthatareUI,businessand database.UIhelpsincapturingtheinteractions,thebusinesslayerhelpsin processing this interaction and finally all the data are mapped and saved in the database. This type of interaction with database helps in manipulating a lot of data and hence it is needed to be scrutinized. The front end interaction taking place within the system and this triggers the functions such as create, update, delete or read action for the data stored within the database. This interactions are further compared to idea behaviour that are mentioned within the requirement document. Moreover verifying data mapping helps by decreasing the need for intense manual testing. Validation of ACID property: COR17454166 U I BUISNESS DATABASE
9 Database testing helps us to ensure that every database is being developed by following the ACID properties. Atomicity: this helps in providing software assurance as it depends on the rule “all or nothing”. Consistency:alltransactionneedstobeconsistenceandonlyvalid information needs to be stored. Isolation: multiple transaction does not affect any single transaction Durability: after the transaction has occurred the data cannot be manipulated by any external factors such as power outage or system crashes. Verifies business rules It is important for every organization to ensure that the database interaction is performed by business rules(HelpingTesters, 2018). Testing on Employee DatabaseField size validation with an example INSERTINTO`departments`(`dept_no`,`dept_name`)VALUES('d000000002', 'test1'); The above query is used for testing. The test results are provided below: COR17454166 ATOMACITYCONSISTENCY ISOLATIONDURABILITY
10 It is observed that the data in field is truncated. Output: SELECT * FROM `departments` Not Null values with an example INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`, `gender`, `hire_date`) VALUES ('123', '', '', '', '', ''); The above query is used for testing. The test results are provided below: COR17454166
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
11 Output SELECT * FROM `employees` Primary Key with example COR17454166
12 Result is that duplicate entry into database is not allowed.Foreign Keywith example INSERTINTO`salaries`(`emp_no`,`salary`,`from_date`,`to_date`)VALUES ('1007', '200', '2017-08-22', '2018-07-16'); The above query is used for testing. The test results are provided below: Screenshots in employee database with contraints COR17454166
13 COR17454166
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.