QAC20C155S Data Modelling & SQL Language

1Assignment 2 Brief Academic year and term: 2020/21 – Semester-2, Year 1Module title: Data Modelling & SQL LanguageModule code: QAC20C155SModule Convener: Piotr BednorzModule LearningoutcomesOn successful…

1Assignment 2 Brief

Academic year and term: 2020/21 – Semester-2, Year 1Module title: Data Modelling & SQL LanguageModule code: QAC20C155SModule Convener: Piotr BednorzModule LearningoutcomesOn successful completion of this module students will be able to1. Understand fundamental concepts of databasemanagement systems such data independence, datamodels and database technologies2. to design and implement relational database systems3. to use manipulation and querying tools, including the useof Structured Query Language (SQL) for managing dataheld in a relational database management system(RDBMS)4. to test and document relational database systemsLearning outcomesassessed within thispiece of work as agreedat the program levelmeeting3. to use manipulation and querying tools, including the useof Structured Query Language (SQL) for managing dataheld in a relational database management system(RDBMS)4. to test and document relational database systemsType of assessment: Individual Report Database Development (1500 words)Weighting 40% of total module marksSubmission deadline Submit the assignment on TBC (no later than 2pm). Thisshould be submitted via Turnitin on Moodle as a MicrosoftWord file.

Kind reminder: You MUST make a reasonable attempt at yourassignment and submit it. Failure to do so may result in CAPPED Resitand/or failure of the module.It is also student’s full responsibility to ensure that all assignments aresubmitted on the correct link and on time before the submission date.

2Assignment 2Attempt all the following tasks set in the assignment. Marks are for producing a properlydocumented system that meets the requirements as specified below. Provide screenshots of working solution and sample output as an evidence.Task 1: Structure Query Language (SQL) [20%]1. Discuss the following SQL joins using practical examplesI. Inner JoinII. Right JoinIII. Left Join2. Discuss the ACID property in a database with examples

I. AtomicityII. ConsistencyIII. IsolationIV. Durability

Task 2: Data Manipulation [50%]Consider the above ERD diagram and write the following SQL statements. EachSQL statement carries 5 marks.3Note: Firstly, you need to create the above database/tables.Refer to the SQL script in the appendix to create the above database/tablesand to insert the data)1. Write an SQL statement to list all employees with their full names, gender and salary2. Write an SQL statement to show the title of all employees and their department name.3. Write an SQL statement to show the full names and gender who belong todepartment number ‘d004’.4. Write an SQL statement to show the all departments and the department managers.5. Write an SQL statement to show a list of department managers who were hired after19906. Write an SQL statement to change any employee’s date of birth. Assume theemployee has just phoned in with his/her last name.7. Write an SQL statement to increase salaries of all employees up to 5.5% who areworking from 1997.8. Write an SQL statement to delete the record of any employees who belongs todepartment ‘d004’ and ID is 10003.9. Create a database view to list full names of all employees their departments andsalaries.10. Create a database view to list all departments and the department managers, whowere hired between 1990 and 2000.Task 3: Database Testing [25%]1. Discuss the Database testing and why it is important.2. Provide screen shots of the testing process on the employees database with thefollowing constraints

  field size validationNull values Referential integrity

Task 4: Referencing and Bibliography [5%]You are required to follow the Harvard referencing system when citing others’ work. Anaccompanying list of references as part of your report.4Marking Criteria:

Task Guidance/Criteria MarksSQL joinsHave you explained the SQL JoinsHave you included appropriate examples of eachstatement? 10ACIDHave you explained the ACID property in a database withexamples?10DataManipulationHave you developed correct syntax of all 10 SQLstatements? Have your SQL queries providedthe correct output?Have you included output screenshots for each statements?50DatabaseTestingHave you written about database testing, and whydatabase testing is important?10Have you provided the screen shots of the testing processon the employee database with the provided constraints?15ReferencingandBibliographyHave you correctly cited your work by using the Harvardreferencing style?5Total 100

5Assignment Preparation Guidelines1. All components of the assignment report must be word processed (hand written textor hand drawn diagrams are not acceptable), font size must be within the range of12 point to 14 point including the headings, body text and any texts within diagrams.2. Standard and commonly used fonts such as Times New Roman, Arial3. Your document must be aligned left or justified with line spacing of 1.54. All figures, graphs and tables must be numbered and labelled.5. You must provide screen shots of working SQL statements and their output.6. Material from external sources must be properly referenced and cited within the textusing the Harvard referencing system.7. All components of the assignment (text, diagrams, code, etc.) should be in oneword file.

Academic Integrity is a matter that is taken very seriously at theuniversity and student should endeavour to enforce it to all theirassignments. In other words, plagiarism, collusion (working andcopying from another student) and ghost writing will not be toleratedand will result in sanctions eg: capped resit, suspension and/orwithdrawal.

6Appendix*****************************************************************************CREATE DATABASE SCRIPT FOR EMPLOYEES DATABASEDROP 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 birth_date first_name last_name gender hire_date INT NOT NULL,DATE NOT NULL,VARCHAR(14) NOT NULL,VARCHAR(16) NOT NULL,ENUM (‘M’,’F’) NOT NULL,DATE NOT NULL,

PRIMARY KEY (emp_no));CREATE TABLE departments(

dept_no dept_name CHAR(4) NOT NULL,VARCHAR(40) NOT NULL,

PRIMARY KEY (dept_no),UNIQUE KEY (dept_name));CREATE TABLE dept_manager (

emp_no dept_no from_date to_date INT NOT NULL,CHAR(4) NOT NULL,DATE NOT NULL,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 dept_no from_date to_date INT NOT NULL,CHAR(4) NOT NULL,DATE NOT NULL,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 (7

emp_no title from_date to_date INT NOT NULL,VARCHAR(50) NOT NULL,DATE NOT NULL,DATE,

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,PRIMARY KEY (emp_no,title, from_date));CREATE TABLE salaries (

emp_no salary from_date to_date INT NOT NULL,INT NOT NULL,DATE NOT NULL,DATE NOT NULL,

FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,PRIMARY KEY (emp_no, from_date)) ;INSERT INTO `departments` VALUES (‘d001′,’Marketing’),(‘d002′,’Finance’),(‘d003′,’Human Resources’), (‘d004′,’Production’),(‘d005′,’Development’),(‘d006′,’Quality Management’),(‘d007′,’Sales’),(‘d008′,’Research’),(‘d009’,’CustomerService’);

INSERT INTO `employees` VALUES 26′),(10001,’1953-09-02′,’Georgi’,’Facello’,’M’,’1986-06-

(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′),8(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′);

Let’s block ads! (Why?)

Do you need any assistance with this question?
Send us your paper details now
We’ll find the best professional writer for you!

 



error: Content is protected !!