Introduction to Database technology
Introduction to Database Technology (DT) Final Marked Assignment (FMA) Introduction For the FMA, which carries 75% of the total marks for this module, you are required to create queries to retrieve data from the Playscheme database you created during the Hands-on exercises in the first half of the module and also document some aspects of this database. Full details of the requirements for the FMA are given below. Read through them carefully to ensure that you understand what is required before you start work. Preparing for the FMA Prior to commencing work on the FMA you need to have completed the tables for the Playscheme database and entered the data as specified in Hands-on 3 to 7. You must have created the Child, Carer, Activity and Childactivity tables and entered the data supplied. These tables must be included in your yourusernamedb on the college SQL server. The examiner will check that the queries you submit run correctly against this database. Completing the FMA You should work on your FMA both during and after class. Begin your work early, as the FMA is a substantial task that requires much planning and effort to complete satisfactorily. Getting support Support tutorials may be available up to 2 weeks before the FMA deadline, depending on tutor availability. You can also request feedback on your work by email. You can send your work once to your tutor for comment prior to submission. FMA Specifications 1: Information Retrieval Design queries to meet the following requirements: 1. Listing of children and their associated carer A listing showing details of each child together with the name and phone number of their associated carer. The query output should be in three columns: child name (first name and surname), carer name (first name and surname), carer phone The output should be ordered alphabetically by child surname, with children with the same surname being listed alphabetically by first name. This query should be used to create a view named Childlist and saved to your database (i.e yourusernamedb on the College SQL server). 2. Count of children each carer is responsible for This should include any carers who do not currently have children associated with them. This is not the case with the current data so in order to test the query you should first add yourself, using your real name but any data you like for address and phone, to the Carer table but DO NOT add any associated children. The output should be in two columns: Carer name (first name and surname), a count of the associated children. (This will be 0 for Carers with no associated children.) Save the output of this query to a spreadsheet named yourusername_output (replacing yourusername with your actual ITS username). Page 2 of 4 3. A register for a specified activity Before this query is run an activity name (e.g. ‘Art’) should be assigned to a variable. The query should then output a list of the children registered for the specified activity. The output should be in four columns: the activity name, the child’s first name and surname, the carer’s first name and surname, the carer’s phone number. The register should be sorted alphabetically by child surname, with children with the same surname being listed alphabetically by first name. 4. Total amount payable by each carer. The output for this query should show the parent/carer title, first name and surname in a single column, the total number of activities taken by all children registered to that parent/carer and the total the parent/carer is required to pay. This total should be shown as a currency amount (e.g. £4.50). Hint: You will need to use the CONCAT() and ROUND() functions in order to do this. Output should be sorted alphabetically by parent surname, first name. (E.g. John Little’s children are registered for a total of three activities; 2 x Football @ £2.00 and 1 x Art @ £2.50 making his total payable £6.50) Note: This query does not need to include carers with no associated children. 2: Referential Integrity A) Explain how referential integrity has been enforced in this database and illustrate, using specific examples, how this ensures the integrity of the data. B) Describe the process of deleting a carer record from the database assuming there is a child or children associated with this carer (for example, Liane Cartman) with the current foreign key settings of ‘On Delete: NO ACTION’. C) What would be the effect of changing the foreign key settings to ‘On Delete: CASCADE’? Explain the advantages and disadvantages of using this setting. 3: Design an extension to the database The current database only stores one instance of each activity. The playscheme manager now wishes to record specific instances of each activity with a start and end date for each. The day of the week for each activity remains as already recorded in the table. For example: Art might be scheduled to run from 6 Jan 2015 to 3 March 2015 and from 7 April 2015 to 26 May 2015 and so on. This data cannot be stored in the current activity table. To show how this requirement could be incorporated into the database you are required to: • Produce a simple ER diagram (in box/crow’s foot format) of the complete playscheme database including any new table(s) needed to meet this new requirement. • Produce a listing of the columns required for any new table(s) showing the column name and datatype and indicating which columns are primary and foreign keys. • Document any other changes that would need to be made to the database to implement this new requirement. Note: You are only required to produce the design for this requirement. You are not expected to implement it in the database. Page 3 of 4 4: Report Your work for this assignment should be submitted as a report. The report should have a cover sheet showing: Your name and ITS username Module name Tutor name Assignment title Date Pages should be numbered and the report should include a table of contents. Include references for any books or online resources you have consulted during the course of this assignment. Maximum length: 2000 words The report should contain the following sections: 1. Queries This section should contain the scripts you produced to meet the information retrieval requirements specified in section 1 above. In addition to the script you should include a brief commentary for each query. This should indicate how satisfied you are with it. How have you checked that the output is correct? If it does not run or does not produce the correct output, what steps have you taken to remedy this and where do you think the problem might lie? 2. Referential Integrity This section should provide your answers to the questions in section 2 above. 3. Database Extension This section should include the entity-relationship diagram and column listing as specified in section 3 above. Deliverables for assessment The following deliverables should be submitted to the Assignment Dropbox in Moodle by the stated FMA submission deadline for your class: 1. A report containing the sections specified in (4) above. This should be either a Word or .pdf document and named yourusername_dtfma_report (replacing yourusername with your ITS username). 2. An Excel spreadsheet named yourusername_output (replacing yourusername with your ITS username).containing the output from query 2 as specified above. The deliverables specified above should be submitted as a single zip file named yourusername_dtfma.zip. Note: The zip file must be submitted in .zip or .7z format. Other formats (such as .rar) are not acceptable and will not be marked. Page 4 of 4 Marking Criteria Marks will be allocated as follows: 1. Queries [30 Marks] • Query runs against your database • Query meets all requirements set • Query produces correct output • SQL is clearly written and well-constructed • Table and column aliases used as appropriate • Commentary gives a realistic evaluation of the query 2. Referential Integrity [30 Marks] • Clear definition of the concept • Demonstrate awareness of how it applies to this database • Able to differentiate between effects of different foreign key actions 3. Database Extension [30 Marks] • Clear ER diagram in specified format • Additional requirements correctly specified • Appropriate column definitions and key settings 4. Overall Presentation [10 Marks] • All elements of assessment attempted • Cover sheet included • Table of Contents included • Pages numbered • Clearly set-out using headings/sub-headings as appropriate • Good grammar and spelling (spellchecked). • All files correctly named Getting feedback Feedback on the marked FMA can be downloaded from Moodle and will normally be returned to you within 6-8 weeks of submission. Backing up files Always keep a back-up copy of all work submitted for assessment in case of unforeseen submission problems. Plagiarism Plagiarism, which is claiming the work of others as your own, is a serious offence and can result in your exclusion from all colleges of the University of London. You should be aware that we use a range of automated tools to spot potential plagiarism in spreadsheets, databases, programme code and text documents. Providing you clearly reference work done by others that you have included in your FMA you will not be penalised.