Introduction to Databases Assignment

Page 1 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social med ia groups), stored in a retrieval system or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use a nd distribution of programme materials please see the Arden Student Terms and Conditions. COM4008 Introduction to Databases Database Design & Development Date for Submission: Please refer to the timetable on ilearn (The submission portal on ilearn will close at 14:00 UK time on the date of submission) Page 2 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Assignment Brief As part of the formal assessment for the programme you are required to submit a Introduction to Databases assignment. Please refer to your Student Handbook for full details of the programme assessment scheme and gen eral information on preparing and submitting assignments. Learning Outcomes: After completing the module , you should be able to: 1. Demonstrate a knowledge and understanding of databases and data management systems. 2. Understand key principles of relational database design techniques. 3. Apply knowledge and understanding of database design and development to design, implement, and test a relational database solution for a given scenario. 4. Graduate Attribute : Discipline Expertise: Know ledge and understanding of chosen field. Possess a range of skills to operate within this sector, have a keen awareness of current developments in working practice being well positioned to respond to change. Page 3 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Guidance Your assignment should include: a title page containing your student number, the module name, the submission deadline and the exact word count of your submitted document ; the appendices if relevant; and a reference list in AU Harvard system . You should a ddress all the elements of the assignment task listed below . Please note that tutors will use the assessment criteria set out below in assessing your work. You must not include your name in your submission because Arden University operates anonymous marking, which means that markers should not be aware of the identity of the student. However, please do not forget to include your STU number. Maximum word count: 3000 word s (equivalent) Please refer to t he full word count policy which can be found in the Student Policies section here: Arden University | Regulatory Framework Please note the following: Students are required to indicate the exact word count on the title page of the assessment . The word count includes everything in the main body of the assessment (including in text citations and references). The word count excludes numerical data in tables , figures, diagrams, footnotes, reference list and appendices. ALL other printed words ARE i ncluded in the word count. Please note that exceeding the word count by over 10% will result in a 10 -percentage point deduction . Page 4 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Assignment Task Scenario: You have been assigned as a database developer to create a database for a community library management syst em that provides books and magazines for the local community . Until now all information related to book stock , members and library transactions has been done manual ly using a ledge r. However, there have been times when the data has been inconsistent or lost and there are concerns around this manual way of processing the data so i t has recommended that a database should be develop ed to manage all data related to the library. Currently the l ibrary keeps the following data: Members Member ID, address, contact number and reading interests. Books Book ID, book name, book type, author, publication date and keywords. Ledger Th is keeps all transaction details for every issue and return of the books. Library transactions for July 2022 are provided in Task 1 – table 1. Your role is to rationalise this into a relational database design ; then to develop and test the database . Page 5 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Task 1 For this task tables and diagrams will be considered equivalent to word count contributions The following information for the library transactions can be seen below and can also be found as a spreadsheet from the Assessment tab within the ilearn module: Table 1. Library Transactions - July Transaction_ID Book_ID Member_ID Issue_Date To_Be_Returned_Date Actual_Return_Date TR0025 B0111 MEM87 01/07/2022 04/07/2022 04/07/2022 TR0026 B0122 MEM62 05/07/2022 08/07/2022 06/07/2022 TR0027 B0303 MEM87 06/07/2022 09/07/2022 08/07/2022 TR0028 B0454 MEM90 10/07/2022 13/07/2022 12/07/2022 TR0029 B0675 MEM65 10/07/2022 13/07/2022 14/07/2022 TR0030 B0896 MEM66 12/07/2022 15/07/2022 17/07/2022 TR0031 M0297 MEM87 12/07/2022 15/07/2022 14/07/2022 TR0032 M0298 MEM68 12/07/2022 15/07/2022 13/07/2022 TR0033 B0122 MEM79 13/07/2022 16/07/2022 16/07/2022 TR0034 B0190 MEM77 15/07/2022 18/07/2022 16/07/2022 TR0035 B0461 MEM91 15/07/2022 18/07/2022 15/07/2022 TR0036 B0162 MEM22 17/07/2022 20/07/2022 19/07/2022 TR0037 M0213 MEM53 20/07/2022 23/07/2022 21/07/2022 TR0038 M0234 MEM54 21/07/2022 24/07/2022 21/07/2022 TR0039 B0455 MEM54 21/07/2022 24/07/2022 23/07/2022 TR0040 B0122 MEM66 23/07/2022 26/07/2022 26/07/2022 TR0041 M0227 MEM77 24/07/2022 27/07/2022 25/07/2022 TR0042 B0038 MEM98 24/07/2022 27/07/2022 27/07/2022 TR0043 B0190 MEM77 27/07/2022 30/07/2022 07/08/2022 TR0044 B0120 MEM88 28/07/2022 31/07/2022 08/08/2022 TR0045 B0122 MEM41 28/07/2022 31/07/2022 30/07/2022 TR0046 B0303 MEM32 28/07/2022 31/07/2022 02/08/2022 TR0047 B0873 MEM63 30/07/2022 02/08/2022 13/08/2022 TR0048 B0784 MEM32 30/07/2022 02/08/2022 03/08/2022 TR0049 M0298 MEM85 31/07/2022 03/08/2022 04/08/2022 Page 6 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Logical Model Based on the scenario and the data provided, create a logical model for the database using a crow’s foot style (e.g. Barker/UML, do NOT use the Chen diagramming style). Make sure to normalise the data to 3 NF , adding keys and identifier columns as appropriate. Make sure to note any assumptions made. Physical Model Using your logical model as a basis, create a physical ERD using crow’s foot notation for an Oracle database. Make sure to use appropriate naming conventions and to identify data types, keys, and constraints. Note any assumptions made or further changes to the struct ure beyond those in the logical model. Implementation From the ERD constructed above, create the database using appropriate SQL in Oracle APEX. Add the data from the provided information. Sample SQL Queries Design and run 3 SQL test queries of your choice to verify that your database functions and meets the needs of the scenario. (2100 words equivalent) (70 marks ) (LOs : 2 & 3) Page 7 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Task 2 Provide solutions for the following problems in the form of SQL code and a brief explanation of your reasoning . Apply your code to a copy of the sample database and include screenshots evidencing this in each case mentioned below : Case 1: Search To demonstrate the efficiency of searching to the librarian, create sea rch es based on the below mentioned criteria. a. Find out the specific transaction details based on the Book _id. b. Find out the specific transaction details based on the Issue_ date. c. Find out the specific transaction details based on the Member_id. d. Find out the specific transaction details based on the Transaction_id. Case 2: Transaction Acknowledgement Whenever there is a transaction inserted or updated , the transaction details should be given as a print receipt to the member . Show evidence for a transaction update and its print receipt . Case 3: Statistics a. Display the most rented book and magazine of the library according to the given data . b. Count the total number of books, magazines rented, and total number of members used the library in the selected month . Case 4: Appreciation System The library is eager to start a new initiative to encourage members to look after the books and return them on time. Members can get a certificate of appreciation based on the ir total appreciation points for their transactions in each month . Add the new column s to store the Comment s and Appreciation Points for each transaction. Ensure it can only accept the comments and points listed below . Comments Appreciation Points Reasons Excellent 2 Returned on time/earlier and in good condition Good 1 Returned on time ; might have some damage. Poor -1 Delayed return W hich member has rented the most books and magazines in the selected month ? List the borrowed book _IDs and magazines _IDs of the most frequent member for the July month and calculate the ir total Appreciation_Points . Page 8 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Case 5: Library Fine s The library would like to implement a system that fines members if they do not return a book by the return date. L ist the members who returned books late and show how many days late they were . The library wants to specify a fine of one penny (1 p) for each day the book is late. Add a column named Fine that calculate s the actual fee for the defaulters and displays a 0 for on -time /early return s. Finally , display the total money from all the f ine s for the month. (900 words equivalent) (30 marks ) (LO : 1) End of questions Page 9 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Formative Feedback You have the opportunity to submit a report to receive formative feedback . The feedback is designed to help you develop areas of your work and it helps you develop your skills as an independent learner. If you are a distance learning student, you should submit your work, by email, to your tutor, no later than 2 weeks before the actual submission deadline. If you are a blended learning student, your tutor will give you a deadline for formative feedback and further details. Formative feedback will not be given to work submitted after the above date or the date specified by your tutor - if a blended learning student . Referencing Guidance You MUST underpin your analysis and evaluation of the key issues with appropriate and wide ranging academic research and ensure this is referenced using the AU Harvard system. Follow this link to find the referencing guides for your subject: Arden Library Page 10 of 15 [1589] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private social media groups), stored in a retrieval sys tem or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Submission Guidance Assignments submitted late will not be accepted and will be marked as a 0% fail. Your assessment can be submitted as a single Word (MS Word) or PDF file, or, as multiple files. If you chose to submit multiple files, you must name each document as the question/part you are answering along with your student numbe r ie Q1 Section A STUXXXX. If you wish to overwrite your submission or one of your submissions, you must ensure that your new submission is named exactly the same as the previous in order for the system to overwrite it. You must ensure that the submitted assignment is all your own work and that all sources used are correctly attributed. Penalties apply to ass ignments which show evidence of academic unfair practice. (See the Student Handbook which i s available on the A-Z key information on iLearn .) Page 11 of 15 [1589 ] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private soci al media groups), stored in a retrieval system or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Assessment Criteria (Learning objectives covered - all) Level 4 is the first stage on the student journey into undergraduate study. At Level 4 students will be developing their knowledge an d understanding of the discipline and will be expected to demonstrate some of those skills and competences. Student are expected t o express their ideas clearly and to structure and develop academic arguments in their work. Students will begin to apply the theory which underpins the subject and will start to explore how this relates to other areas of their learning and any ethical con siderations as appropriate. Students will begin to develop self -awareness of their own academic and professional development. Grade Mark Bands Generic Assessment Criteria First (1) 80%+ Outstanding performance which demonstrates the ability to analyse the subject area and to confidently apply theory whilst showing awareness of any relevant ethical considerations. The work shows an excellent level of competence and confidence in managing appropriate sources and materials, initiative and exce llent academic writing skills and professional skills (where appropriate). The work shows originality of thought. 70-79% Excellent performance which demonstrates the ability to analyse the subject and apply theory whilst showing some awareness of any relevant ethical considerations. The work shows a high level of competence in managing sources and materials, initiative and very good academic writing skills and professional skills (where appropriate). The work shows originality of thought. Upper second (2:1) 60-69% Very good performance which demonstrates the ability to analyse the subject and apply some theory. The work shows a good level of competence in managing sources and materials and some initiative. Academic writing skills are good, and e xpression remains accurate overall. Good professional skills (where appropriate). The work shows some original thought. Lower second (2:2) 50-59% A satisfactory to good performance which begins to analyse the subject and apply some underpinning theory. The work shows a sound level of competence in managing basic sources and materials. Academic writing skills are satisfactory and expression remains accurate overall although the piece may lack struc ture. Satisfactory professional skill s (where appropriate). The work lacks some original thought. Third (3) 40-49% Basic level of performance in which there are some omissions in understanding the subject, its underpinning theory and ethica l considerations. The work shows a basic use of sources and materials. Academic writing skills are limited and there are some errors in expression and the work may lack structure overal l. There are some difficulties in developing professional skills (where appropriate). The work lacks original thought a nd is largely imitative. Marginal fail 30-39% Limited performance in which there are omissions in understanding the subject, its underpinning theory and ethical considerat ions. The work shows a limited use of sources and materials. Academic writing skills are weak and there are errors in expression and the work may lack structure overall. There are difficulties in developing professional skills (where appropriate). The work lacks original thought and is largely imitative. 29% and below A po or performance in which there are substantial gaps in knowledge and understanding , underpinning theory and ethical considerations. The work shows little evidence in the use of appropriate sources and materials. Academic writing skills are very weak and th ere are numerous errors in expression. The work l acks structure overall. Professional skills (where appropriate) are not developed. The work is imitative. Page 12 of 15 [1589 ] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private soci al media groups), stored in a retrieval system or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Assessment specific grading criteria: Task 1 Measures: “Understand key principles of relational database design techniques.” “Apply knowledge and understanding of database design and development to design, implement, and test a relational database solution for a given scenario.” < 30% 31 -40% 41 -50% 51 -60% 61 -70% 71 -80% > 80% Logical Model (25%) Little to no evidence of an understanding of key principles of ER diagram and normalisation upto 3NF. Key principles included but are not limited to: Identifying entities and attributes, entity relationship diagrams and symbols, normalisation and decomposition, cardinalities, and keys. Some evidence of key principles of ER diagram and normalisation upto 3NF on a basic level, though many aspects are missing or misapplied. Evidence is provided in an inconsistent manner that indicates some guesswork rather than the consistent misapplication of a principle. Some evidence of key principles of ER diagram and normalisation upto 3NF, though some aspects are missing or regularly misapplied. Decision s are described rather than explained. e.g. The student may have missed some core aspect of normalisation, though they have attempted to apply it in general. Cardinalities may be incorrect demonstrating a consistent misunderstanding of the concept. Good evidence of key principles of ER diagram and normalisation upto 3NF, though a few aspects are sometimes misapplied. Decisions are accompanied by basic explanations. e.g. Cardinalities may be incorrect in a few instances. A few keys not optimally ident ified. Very good evidence of key principles of ER diagram and normalisation upto 3NF, though a few aspects are sometimes misapplied. Decisions are well explained with occasional citation. e.g. Cardinalities may be incorrect in one or two instanc es. One or two keys not optimally identified. Excellent clear evidence of key principles of ER diagram and normalisation upto 3NF, though one or two aspects are misapplied. Decisions are justified with some citation. e.g. Cardinalities are consistent and appropriate. Normalisation is comprehensive and accompanied with reasoned explanations. Exceptionally clear evidence of key principle of ER diagram and normalisation upto 3NF s, with consistent application. Decisions are well justified wi th appropriate citations. e.g. Consistent application of cardinalities, insight into decomposition demonstrated with references to appropriate literature. < 30% 31 -40% 41 -50% 51 -60% 61 -70% 71 -80% > 80% Physical Model (15%) Inaccurately represents the Some minor alignment with the Mostly al igns with the proposed logical Aligns with the proposed logical Aligns with the proposed logical Aligns with the proposed logical Aligns with the proposed logical Page 13 of 15 [1589 ] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private soci al media groups), stored in a retrieval system or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. logical model. Lacks appropriate naming conventions, data types and constraints. proposed logical model. Lacks multiple appropriate naming conventions, data types and constraints. model, though explanations of differences may be missing. Completely lacks any one of appropriate naming conventions, data types or constraints. model, though any reference to differences are descriptive rather than explanatory. Some attempt to apply appropriate naming conventions, data types and constraints. model, with at least some differences sufficiently explained and some assumptions noted. A somewhat consistent attempt to apply appropriate naming conventions, data types and constraints. model, with all differences sufficiently explained and any assumptions noted. A consistent application of appropriate naming conventions, data types and constraints. model, with any differences or assumptions well justified. A comprehensive, justified application of appropriate naming conventions, data types and constraints. < 30% 31 -40% 41 -50% 51 -60% 61 -70% 71 -80% > 80% Implementation (18%) Database Implementation is missing, barely attempted or otherwise wholly deficient. e.g. Does not relate to the proposed model. There are grievous errors in syntax which would make the code non - functional. The code is provided but the results are not Database Implementation is partially representative of the prop osed model or there are large deficiencies in the execution with limited constraints. e.g. Datatypes are wholly inappropriate. Primary and/or foreign keys constraints are not properly identified. Database Implementation is mostly representative of the pr oposed model, though there may be some deficiencies in execution along with few of the constraints. e.g. Null constraints are not wholly consistently applied. One or two data types are inappropriate. Database Implementation is nearly completely repres entative of the proposed model with most of the constraints, though simple and little consideration has been given to aspects like appropriate allocation of string size limits. e.g., VARCHAR2(10) Database Implementation is wholly representative of the proposed model, including some moderate complexities, with the required constraints. e.g. appropriate string size limits and foreign key constraints Database Implementation is wholly representative of the pr oposed model, including multiple complex considerations along with all the constraints. e.g. Composite key / foreign key constraints applied correctly to junction tables. Database Implementation is wholly representative of the proposed model and the e xecution demonstrates near professional level skills along with all the constraints. e.g. The use of SQL scripting or columns defined/ populated by use of aggregate functions. Page 14 of 15 [1589 ] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private soci al media groups), stored in a retrieval system or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. evidenced or vice - versa. for a first name field. consistently applied. Sample SQL queries (12%) Queries are not provided, do not function, or do not address the any of the requirements of the scenario. Queries are not provided, are partially functional or do not address the any of the requirements of the scenario. Queries are provided, mostly functional but may only be tangentially connected to the scenario or demonstrate a surface level understanding. Queries are provided, functional and address the requirements of the scenario, but demonstrate a surface level understanding. Queries are provided, functional, address the requirements of, and demonstrates some critical consideration of the scenario. Queries are provided, functional, address the requirements of, and demonstrates a comprehensive critical consideration of the scenario. Queries are provided, functional, addr ess the requirements of, and demonstrates both a critical and creative consideration of the scenario. Task 2 Measures: “Demonstrate a knowledge and understanding of databases and data management systems.” < 30% 31 -40% 41 -50% 51 -60% 61 -70% 71 -80% > 80% In each case for: Case 1 (4%) Case 2 (2%) Case 3 (4%) Case 4 (8%) Little to no evidence of understanding is demonstrated. Problem is not solved, or the SQL is non -functional. Insufficient evidence of understanding SQL, terms may be confused or padded with irrelevant information. Incomplete solution is presented, or solution demonstrates a slight misunderstanding of the problem. A basic level of understanding of SQL has been demonstrated. Terminology is mostly used correctly, though inconsistently. Solution to the problem is mostly complete, though basic in approach. Good evidence and understanding of SQL have been demonstrated. Terminology is mostly used correctly and consistently. Solution to the problem is complete, though it may be inelegant in approach. Very good demonstration and understanding of SQL Terminology is used correctly and consistently. Solution is complete and demonstrates a well -considered approach to the problem. Excellent demonstration and understanding of SQL; explanations are given, which are clear, well -illustrated and expressed in the students’ own words. Terminology is used correctly and consistently. Solution is co mplete, demonstrates a considered approach, Outstanding demonstration and understanding of SQL; explanations are given, which are clear, well - illustrated and expressed in the students’ own words. Termin ology is used correctly and consistently. Solution is complete and Page 15 of 15 [1589 ] Arden University © reserves all rights of copyright and all other intellectual property rights in the learning materials and this publication. N o part of any of the learning materials or this publication may be reproduced, shared (including in private soci al media groups), stored in a retrieval system or transmitted in any form or means, including without limitation electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of Arden University. To find out more about the use and distribution of programme materials please see the Arden Student Terms and Conditions. Case 5 (12%) with sound justifications supplied in each case. demonstrates a well -considered approach, with sound, well referenced, justifications.