Part 1: SQL/Database work Scenario Development of a relational database system for a food producing company FoodRU is a Leicester-based food producing company. The company wants to keep details regarding both past and present employees and their assignment to shifts over time. At present, there are three defined shift patterns; the morning shift starts at 6am and finishes at 2pm, the day shift starts at 9am and finishes at 5pm, and the evening shift starts at 4pm and finishes at 12am (midnight). However, management have already indicated that they may need to add further shift patterns in the future (e.g., by adding a night shift to the existing ones so that the company can meet a high user demand for their foods). They therefore require shift details to be stored within a separate Shift table, with attributes that allow the storage of a shift name with its associated start and finish times (use the 24 hour clock for these times). Past and present employee details are to be kept in the same Employee table, and the details to be kept are the employee’s unique 6 digit reference number, the first name, surname and any other names (if there are any) of the employee, the employee’s gender, contact address and contact telephone, the date on which the employee started his/her employment at the company and the date on which the employee finished his/her employment at the company (should s/he be a past employee). Details regarding staff assignments to shifts include the date that an employee was allocated to work a particular shift, and the date that s/he was taken off the shift (if not still assigned to it). Employees can be assigned to different shifts over time and even to the same shift over different time periods, although they cannot be assigned to more than one shift at any one time. A new employee may not yet be assigned to a shift. Tasks: 1. Provide the table specifications for the THREE tables that are required by FoodRU to store employee, shift and assignment details. That is, for each of the three tables, you should provide, in a suitable presentation format, the name of the table and a specification of each its attributes to include: • Attribute name • Attribute brief description as to its meaning • a description of the attribute’s data type/integrity (e.g., date field, character field of length 20, number field <= 10, etc. – you can use the Oracle data types within these descriptions if you want to) • An indication as to whether the attribute is a primary key attribute and/or foreign key attribute • An indication as to whether the attribute can or cannot take null values Make sure your design specifies the appropriate links between the three tables. Remember to write down any additional integrity you need to enforce either at a specific table level or across two or more tables, if this is required. Also, remember to write down any justifications for the data types/integrity or for any other design features that you have introduced, and/or any assumptions that you have made at any time during your design. 2. Create the THREE tables you have designed in Question 1. above, and populate the tables with some example data (at least 5 employees (some past and some current), appropriate shift details, and at least one shift allocation for most (but not all) of the employees). For the Employee table ONLY, provide a series of tests and their results so that you can be confident that the table was set up properly. This involves testing any entity integrity, referential integrity and other data validation constraints using appropriate SQL statements. Print out the test and its results, and annotate to specify the intention of each test. 3. Create and justify THREE queries that you think FoodRU would like to use. Make sure that your queries, in total, show you are able to use the following facilities in Oracle SQL*Plus: o Selection of particular table columns o Selection of particular table rows o Inner Join of at least 2 tables o Use of a sorting/ordering facility o Use of DISTINCT o A condition using “[removed]”, IS NULL, IS NOT NULL, or similar. None of your queries should output an empty table! You are required to submit: • The completed table specifications for the required three tables. These should be written using an appropriate text editor. • All the CREATE TABLE and INSERT INTO Oracle SQL*Plus statements used to create the three tables and to populate them with suitable data, together with a printout of each of the Oracle tables (i.e., the extension of each table) that you have created and populated. • The series of tests, and their results and annotations, for the Employee table ONLY. • A printout of the three queries you devised, showing both the SQL code and the query result. You should provide a brief explanation of what you expect each query to achieve and why you think this query is relevant to FoodRU. Marking Criteria Generic explanation of criteria: In order to achieve an A grade, the work must be excellent in almost all respects, only very minor limitations. In order to achieve a B grade, the work should show strength in most respects, but perhaps has limitations in one or two areas. A good piece of work nevertheless. In order to achieve a C grade, the work should be of a satisfactory standard, showing strength in some areas, but perhaps let down by either poor presentation, poor practical work, or poor written explanations where required. In order to achieve a D grade, the work should be of a satisfactory standard but may have significant shortcomings in some areas. Nevertheless shows at least a basic understanding of the concepts and a basic practical ability. A E grade will be given to work that is just unsatisfactory. An F grade will be given where the work contains serious errors/limitations.   The following grid gives more guidance re: marking of specific tasks: TASK [removed]70% Table specifications Poor or no attempt Basic understanding displayed Fair understanding displayed Good Understanding displayed Excellent understanding displayed – full and appropriate table specifications in correct format Creation of tables with integrity rules implemented Inappropriate tables, poor attempt at implementing defined integrity rules Some tables and associated integrity rules created correctly Satisfactory but with some significant errors/ limitations As for A but with some minor errors/ limitations All tables created correctly, including foreign keys. Appropriate implementation of integrity rules Testing of Employee table creation Poor or no attempt Basic testing evident Fair testing evident Good testing evident Excellent, well thought out and complete series of tests and associated results Development of three queries as described above, with reasons for choice of query Little or no evidence of successful/ appropriate query development Some evidence of sensible choice and implementation of queries Satisfactory but with some significant errors/ limitations As for A but with some minor errors/ limitations. Excellent choice and implementation of queries, demonstrating appropriate use of all required querying facilities. OVERALL MARK: ANY OVERALL COMMENTS: Part 2: Haskell work Consider the following Haskell program. module Main where import Data.Char (isPunctuation,toLower) import Data.List (nub,sort) strip :: [Char] -> [Char] strip cs = [ c | c [removed] [Char] lower cs = [ toLower c | c <- cs ] prog = unlines . nub . sort . words . lower . strip main = interact prog You have been asked to give a lecture explaining in detail what this program does and how it works. You should produce a PowerPoint presentation containing about 20 slides in which you provide a full explanation (with examples) of the program and how it works. You may assume that your audience consists of first year functional programming students who have a reasonable working knowledge of Haskell. However, you should ensure that each aspect of the program (including programming language features, standard functions, etc.) is fully explained so that everyone has a chance to understand the lecture fully. You should ensure that your slides appear professional. Take care with general layout and avoid spelling mistakes. You should hand in a printed copy of your PowerPoint presentation by the deadline and you should also upload your slides to Blackboard.   The following grid gives more guidance re: marking of specific tasks: TASK [removed]70% Quantity of work (i.e. how many slides and what is on them) Poor or no attempt Bare minimum explanation Covers most of the main points Covers all the main points Covers all the main points and provides extra, appropriate and relevant, supporting information Quality of explanation (i.e. the depth of the understanding demonstrated and conveyed) Inadequate or superficial explanation Reasonable explanation of the some of the main concepts Good explanation of most of the main concepts Very good explanation of most of the main concepts Outstanding explanation that covers all of the main concepts and also includes some of the subtler points in the code Quality of slides (i.e. how do they look; how is the information presented?, is it a coherent lecture?) Confusing, badly structured, inconsistent, poor English, etc. Consistent presentable, Good set of slides: well structured Very good set of slides: well structured and presented Outstanding set of slides: thoroughly consistent, well laid out, good quality English, etc.