Understand fundamental concepts

Assignment 2 Brief Academic year and term: 2018/19 Semester-2, Year 1 Module title: Data Modelling & SQL Language Module code: QAC020C155A Module Convener: Sharjeel Aslam Module Learning outcomes On successful completion of this module students will be able to Understand fundamental concepts of database management systems such data independence, data models and database technologies Be able to design and implement relational database systems Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS) Be able to test and document relational database systems Learning outcomes assessed within this piece of work as agreed at the programme level meeting Be able to use manipulation and querying tools, including the use of Structured Query Language (SQL) for managing data held in a relational database management system (RDBMS) Be able to test and document relational database systems Type of assessment: Individual Report Database Development (1500 words) Weighting 40% of total module marks Submission deadline Database Development report should to be submitted on 19/12/2018 (no later than 2pm). This should be submitted via Turnitin on Moodle as a word file which should contain screen shots of the SQL queries and sample output (see the deliverables required for this assignment) Assignment 2 Attempt all the following tasks set in the assignment. Marks are awarded for producing a properly documented system that meets the requirements as specified below. Provide screen shots of working solution and sample output as an evidence. Task 1:Data Manipulation[60%] Consider the above ERD diagram andwrite the following SQL statements. Each SQL statement has6 marks. Note: First need to create above database/tables Kindly see the SQL script in appendix to create the above database / tables and to insert the data) WriteanSQL statement to list all employees with their full names, hire date and title Write anSQL statement to show the salaryof all employees and their department name. Write anSQL statement to show the full names and gendersof HR department staff. Write anSQL statement to showthe all departments name and their departments managers. Write anSQL statement to show a list of department managers who were hired after 1986 Write anSQL statement to change anyemployees title. Assume the employee has just phoned in with his/her last name. Write anSQL statement to delete employees record who belongs to department d004 and ID 10003. Create a database view to list full names of all employees, their department managersand salaries. Create a database view to list alldepartments and their department managers, who were hired between 1980 and 1990. Write anSQL statement to increase salaries of all employees up to 10% who are working in marketing department Task 2:Database Testing[35%] Discuss why database testing is important and give examples to illustrate your answer? (15 marks) Provide the screen shots of the testing process on Employees databasewith the following constraints field size validation with an example (5 marks) Not Null values with an example (5 marks) Primary Key with example (5 marks) Foreign Keywith example (5 marks) Task 4: Referencing and Bibliography[5%] You are required to follow the Harvard referencing system when citing others work. An accompanying list of references must also be provided as part of your report. Marking Criteria: Task Guidance/Criteria Marks Data Manipulation You have developed correct syntax of all 10 SQL statements. You have provided correct output of all SQL queries. You have provided comments in SQL statements as appropriate. You have included output screenshots for each SQL statement. 60 Database Testing You have provided a rationale of database testing and explained how database testing helps to deliver successful projects? You have provided an example of database testing in the given employees database. You have used references to justify your answer. 15 You have provided definitions and examples of the following database constraints: Field Size Null values Primary Key Foreign Key You have provided screen shots of the testing process on employees database with the given constraints. 20 Referencing and Bibliography You have correctly cited others work using the Harvard referencing system? 5 Total 100 Assignment Preparation Guidelines All components of the assignment report must be word processed (hand written text or hand drawn diagrams are not acceptable), font size must be within the range of 12 point to 14 point including the headings, body text and any texts within diagrams. Standard and commonly used fonts such as Times New Roman, Arial or Calibri should be used. Your document must be aligned left or justified with line spacing of 1.5. All figures, graphs and tables must be numbered and labelled. You must provide screen shots of working SQL statements and their output. Material from external sources must be properly refereed and cited within the text using the Harvard referencing system. All components of the assignment (text, diagrams. Code etc.) must be submitted in one wordfile. Appendix ***************************************************************************** CREATE DATABASE SCRIPT FOR EMPLOYEES DATABASE ***************************************************************************** DROP DATABASE IF EXISTS employees; CREATE DATABASE IF NOT EXISTS employees; USE employees; SELECT CREATING DATABASE STRUCTURE as INFO; DROP TABLE IF EXISTS dept_emp, dept_manager, titles, salaries, employees, departments; CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATE NOT NULL, first_nameVARCHAR(14) NOT NULL, last_nameVARCHAR(16) NOT NULL, gender ENUM (M,F) NOT NULL, hire_date DATE NOT NULL, PRIMARY KEY (emp_no) ); CREATE TABLE departments ( dept_noCHAR(4) NOT NULL, dept_nameVARCHAR(40) NOT NULL, PRIMARY KEY (dept_no), UNIQUE KEY (dept_name) ); CREATE TABLE dept_manager ( emp_no INT NOT NULL, dept_noCHAR(4) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,dept_no) ); CREATE TABLE dept_emp ( emp_no INT NOT NULL, dept_noCHAR(4) NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,dept_no) ); CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no,title, from_date) ) ; CREATE TABLE salaries ( emp_no INT NOT NULL, salary INT NOT NULL, from_date DATE NOT NULL, to_date DATE NOT NULL, FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE, PRIMARY KEY (emp_no, from_date) ) ; INSERT INTO SCRIPT FOR EMPLOYEES DATABASE INSERT INTO SCRIPT FOR EMPLOYEES DATABASE ************************************************************************* INSERT INTO `departments` VALUES (d001,Marketing), (d002,Finance), (d003,Human Resources), (d004,Production),(d005,Development), (d006,Quality Management),(d007,Sales), (d008,Research),(d009,Customer Service); INSERT INTO `employees` VALUES (10001,1953-09-02,Georgi,Facello,M,1986-06-26), (10002,1964-06-02,Bezalel,Simmel,F,1985-11-21), (10003,1959-12-03,Parto,Bamford,M,1986-08-28), (10004,1954-05-01,Chirstian,Koblick,M,1986-12-01), (10005,1955-01-21,Kyoichi,Maliniak,M,1989-09-12), (10006,1953-04-20,Anneke,Preusig,F,1989-06-02), (10007,1957-05-23,Tzvetan,Zielinski,F,1989-02-10), (10008,1958-02-19,Saniya,Kalloufi,M,1994-09-15), (10009,1952-04-19,Sumant,Peac,F,1985-02-18), (10010,1963-06-01,Duangkaew,Piveteau,F,1989-08-24), (10011,1953-11-07,Mary,Sluis,F,1990-01-22), (10012,1960-10-04,Patricio,Bridgland,M,1992-12-18), (10013,1963-06-07,Eberhardt,Terkki,M,1985-10-20), (10014,1956-02-12,Berni,Genin,M,1987-03-11); ******************************************************************************** INSERT INTO `dept_emp` VALUES (10001,d005,1986-06-26,9999-01-01), (10002,d007,1996-08-03,9999-01-01), (10003,d004,1995-12-03,9999-01-01), (10004,d004,1986-12-01,9999-01-01), (10005,d003,1989-09-12,9999-01-01), (10006,d005,1990-08-05,9999-01-01), (10014,d005,1993-12-29,9999-01-01), INSERT INTO `dept_manager` VALUES (10013,d001,1985-01-01,1991-10-01), (10001,d001,1991-10-01,9999-01-01), (10002,d002,1985-01-01,1989-12-17), (10008,d002,1989-12-17,9999-01-01), (10012,d003,1985-01-01,1992-03-21), (10011,d003,1992-03-21,9999-01-01), (10014,d004,1985-01-01,1988-09-09), (10003,d004,1988-09-09,1992-08-02); INSERT INTO `salaries` VALUES (10001,60117,1986-06-26,1987-06-26), (10001,62102,1987-06-26,1988-06-25), (10002,66074,1988-06-25,1989-06-25), (10003,66596,1989-06-25,1990-06-25), (10004,66961,1990-06-25,1991-06-25), (10005,71046,1991-06-25,1992-06-24), (10006,74333,1992-06-24,1993-06-24), (10007,75286,1993-06-24,1994-06-24), (10008,75994,1994-06-24,1995-06-24); *************************************************************************** INSERT INTO `titles` VALUES (10001,Senior Engineer,1986-06-26,9999-01-01), (10002,Staff,1996-08-03,9999-01-01), (10003,Senior Engineer,1995-12-03,9999-01-01), (10004,Engineer,1986-12-01,1995-12-01), (10004,Senior Engineer,1995-12-01,9999-01-01), (10005,Senior Staff,1996-09-12,9999-01-01), (10005,Staff,1989-09-12,1996-09-12), (10006,Senior Engineer,1990-08-05,9999-01-01), (10007,Senior Staff,1996-02-11,9999-01-01), (10007,Staff,1989-02-10,1996-02-11), (10008,Assistant Engineer,1998-03-11,2000-07-31); ***************************************************************************

Pssst…We can write an original essay just for you.

Any essay type. Any subject. We will even overcome a 6 hour deadline.

<< SAVE15 >>

Place your first order with code to get 15% discount right away!

Impressive sample results