Homework answers / question archive / SQL TEST CASES TEST CASE 1 For all doctors, display the prescription ID, quantities, and dates of patients that it is written for with their age

SQL TEST CASES TEST CASE 1 For all doctors, display the prescription ID, quantities, and dates of patients that it is written for with their age

Computer Science

Share With

SQL TEST CASES TEST CASE 1 For all doctors, display the prescription ID, quantities, and dates of patients that it is written for with their age. Also display doctor’s id, specialty, and years of experience. For all Doctors, list the: Doctor’s id, Specialty, Years of experience, The prescription ID, Quantity of prescription, date of prescription, Patient’s name, Patient’s age. SELECT dc.docssn DOC_ID, dc.specialty DOC_Specialty, dc.yrsofexp, pr.presid, pr.qty Presc_Qty, pr.presdate, pa.patientname, pa.age Patient_Age FROM patient pa, doctor dc, prescription pr WHERE pa.docSSN = pr.docSSN AND dc.docSSN = pr.docSSN; Test Case 2 a. Which patient do BIANCA have? b. Display the patient number and name, as well as the doctors number and specialty. SELECT dc.docssn, dc.docname, dc.specialty Doctor_Specialty, pa.patientssn, pa.patientname FROM patient pa, doctor dc WHERE pa.docssn = dc.docssn AND dc.docname LIKE 'Bianca'; TEST CASE 3 Display all the prescription who were written during 2020, giving the doctor’s id, patient’s id and prescription date. SELECT docssn DOC_ID, patientssn PATIENT_ID, presdate PRESCIPTION_DATE FROM prescription WHERE EXTRACT ( YEAR FROM presdate) = 2020; TEST CASE 4 Find the minimum and maximum years of experience of doctors where the average year of experience is greater than 5. Also display the doctor’s id, name, specialty, minimum and maximum years of experience. SELECT docssn, docname, specialty, MIN(yrsofexp) Min_Year_Of_Exp, MAX(yrsofexp) Max_Year_Of_Exp FROM doctor GROUP BY specialty, docssn, docname HAVING AVG(yrsofexp) > 5; TEST CASE 5 List all details of patients with least age among those with higher-than-average age. with higher_age as (select* from patient pa where age > ( select avg(age) from patient where age (d1) CREATE (p1) –[:SELLS {price: 10} ] –> (d2) CREATE (p1) –[:SELLS {price: 10} ] –> (d7) CREATE (p2) –[:SELLS {price: 12} ] –> (d3) CREATE (p2) –[:SELLS {price: 12} ] –> (d5) CREATE (p3) –[:SELLS {price: 10} ] –> (d4) CREATE (p4) –[:SELLS {price: 12} ] –> (d8) CREATE (p5) –[:SELLS {price: 12} ] –> (d6) CREATE (p1) –[:CONTRACTS {supervisorID:100, text: "some text p1pc3", sDate: date("2017-06-12"), eDate: date("2027-06-11")}] –> (pc3) CREATE (pc2) –[:CONTRACTS {supervisorID:101, text: "some text pc2p3", sDate: date("2012-01-19"), eDate: date("2032-01-18")}] –> (p3) CREATE (pc1) –[:CONTRACTS {supervisorID:102, text: "some text pc1p2", sDate: date("2020-11-22"), eDate: date("2035-11-21")}] –> (p2) CREATE (pc4) –[:CONTRACTS {supervisorID:103, text: "some text pc4p4", sDate: date("2012-01-19"), eDate: date("2032-01-18")}] –> (p4) CREATE (p5) –[:CONTRACTS {supervisorID:104, text: "some text p5pc5", sDate: date("2020-11-22"), eDate: date("2035-11-21")}] –> (pc5) OUTPUT: CYPHER QUERIES 1) Which drugs do Pharmacy with phone number 777-126 sell? Retrieve the: i) Trade names of the Drugs ii) Name of Pharmacy where the drugs are sold iii) Address of Pharmacy where the drugs are sold SOLUTION: MATCH(p1:Pharmacy{pphoneNr:"777-126"})-[:SELLS] –>(d:Drug) RETURN d.tradeName, p1.pName, p1.pAddress OUTPUT: 2) How much are drugs on sale in pharmacies located in Bolton? Retrieve the: i) trade name of the drugs ii) drugs formula iii) prices for which drugs are on sale iv) Name(s) of the pharmacies SOLUTION: MATCH(p:Pharmacy{pAddress:"Bolton"})-[s:SELLS] –>(d:Drug) RETURN d.tradeName, d.dFormula, s.price, p1.pName OUTPUT: 3) Which supervisors are in charge of contracts entered in to Emzor Pharmaceutical Company? Retrieve the: i) Name(s) of pharmacies Emzor is contracted to ii) Supervisor’s ID(s) iii) Contract start date(s) iv) Contract end date(s) SOLUTION: MATCH(pc:pCompany{pcName:"Emzor"})-[c:CONTRACTS] –>(p:Pharmacy) RETURN p.pName, c.supervisorID, c.sDate, c.eDate OUTPUT: 4) Which Pharmacies sell more than one drugs and how many drugs exactly? Retrieve the: i) Name of the pharmacies ii) Pharmacies’ addresses iii) Corresponding number of drugs Order the results by number of drugs. SOLUTION: MATCH(p:Pharmacy)-[:SELLS] –>(d:Drug) WITH p, count(*) AS pd WHERE pd>1 RETURN p.pName, p.pAddress, pd ORDER BY pd OUTPUT: 5) Calculate the total cost of drugs in each Pharmacy. Retrieve the: i) Name of the pharmacies ii) Total cost of drugs in each pharmacy Order the results by total cost of drugs (descending order). Limit result to 3 SOLUTION: MATCH(p:Pharmacy) OPTIONAL MATCH(p:Pharmacy)-[s:SELLS] –>(d:Drug) RETURN p.pName, sum(s.price) ORDER BY sum(s.price) DESC LIMIT 3 OUTPUT: January 2021 v1 Faculty of Science and Technology - Department of Computing and Informatics Unit Title: Data Processing and Analytics Assessment Title: Project report and video on data processing and analytics topics Unit Level: 7 Assessment Number: 1 of 1 Credit Value of Unit: 20 Date Issued: 07/01/2021 Marker(s): Avleen Malhi Submission Due Date: 14/05/2021 Time: 12.30pm Quality Assessor: Hamid Bouchachia Submission Location: Brightspace Feedback method: Brightspace This is group assignment with individual elements which carries 100% of the final unit mark ASSESSMENT TASK This is a group assignment with individual elements where you are asked to design and implement databases for selected use cases and perform data analytics on given datasets. A. The first part of the assignment focuses on your understanding and implementation of various database technologies. You are given three use case scenarios as below: Use-case 1 UniqueTeam is an application that models soccer teams, the games they play, and the players in each team. There are a set of teams; each team has an ID (unique identifier), name, main stadium, and the city where it is based. Each team has many players and each player belongs to one team. Each player has a number (unique identifier), name, DoB, start year, and shirt number. Teams play matches and, in each match, there is a host team and a guest team. The match takes place in the stadium of the host team. For each match, there is need to keep track of the following: the date on which the game is played, the final result of the match, the players who participated in the match. For each player, we record the number of goals he scored, whether or not he took a yellow card, and whether or not he took a red card. During the match, one player may substitute another player. You should keep track of the substitution and the time at which it took place. Each match has exactly three referees. For each referee we have an ID (unique identifier), name, DoB, years of experience. One referee is the main referee and the other two are assistant referee. Use-case 2 You always wanted to be an artist, so you set up a database company, ArtBase, that builds artwork items for art galleries. The core of this company is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the database should include the artist’ name, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works of the 19th century, etc. A given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique name, address, total amount of dollars spent in the gallery, the artists and groups of art that the customer tends to like. Page 1 of 6 January 2021 v1 Use-case 3 You want to design a database for pharmaceutical companies that supplies medicines. Each pharmaceutical company is identified by a name and a phone number. Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmacies, and a pharmacy can contract with several pharmaceutical companies. For each contract, you have to store a start date, an end date and the text of the contract. Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract. If a pharmaceutical company is deleted, you need not keep track of its products any longer. Each pharmacy has a name, an address, and a phone number. Each pharmacy sells several drugs. For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company and the trade name identifies a drug uniquely from among the products of that company. A drug could be sold at several pharmacies and the price could vary from one pharmacy to another. Patients are identified by an SSN, their names, addresses, and ages. Doctors are identified by an SSN, name, specialty, and years of experience. Doctors prescribe drugs for patients. Every doctor has at least one patient, but a patient has one doctor. A doctor could prescribe one or more drugs to patients. A patient could obtain prescriptions from several doctors. Each prescription has a date and quantity drugs associated with it. You should assume that if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored. Choose one of the above use case scenarios and: 1. Develop an Entity-Relationship model of the information requirements for the selected scenario. 2. Translate your model into an equivalent relational schema. Specify all relation headings, indicating primary and foreign keys. 3. Implement at least three important entities from your relational schema in SQL, MongoDB and Neo4J (could be different three entities for each technology) and generate sample data for all of the implementations. Show samples of generated data. 4. Explain why you chose the particular entities to implement with each technology. 5. Come up with 5 test cases for each database technology and implement those using queries. Show the queries code and the output for each database technology. Include the answers to each of the five points above in your technical report. B. In an era of growing data complexity and volume, feature selection and construction techniques play a key role in understanding our data in helping reduce the dimensionality and improve learnability in data analytics problems. Both for data and big data processing and analytics feature selection techniques are important for reducing the time required to build machine learning models and improving the performance of these algorithms. Moreover, principal component analysis is an important algorithm used in data and big data processing for the purpose of data visualisation, as well as for dimensionality reduction and for gaining insight in the knowledge hidden in the data. For the submission, you are given 3 datasets (below this section) and you are asked to define the classification problem of your choice, select one dataset and perform the following tasks: 1. Define the training and testing set for your dataset. 2. Implement neural network and one other classification algorithm of your choice and compare the performance for the dataset you choose. 3. Apply Principal Component Analysis to the dataset and explain its outcome. How does the number of principal components affect the percentage of variance covered for this dataset? 4. Apply any feature selection method of your choice and compare the performance using any one algorithm used in question 2 before and after applying the feature selection algorithm. 5. Discuss the challenges and implications regarding the time required to build the required models. Compare the times with and without feature selection method. You must perform the tests on the test data set to evaluate the results. Use the AUC (area under curve) and accuracy as a metric for comparing the performance. Include the answers to each of the five points above in your technical report. Page 2 of 6 January 2021 v1 Datasets (Select one): • Human Activity Recognition Using Smartphones Data Set • WISDM Smartphone and Smartwatch Activity and Biometrics Dataset Data Set • OPPORTUNITY Activity Recognition Data Set Groups • It is your responsibility to decide whom you would like to work with. You also need to be prepared for any unforeseen circumstances that may arise when working as a team. You should inform the unit leader via email about your group members within three weeks of the issue date of this coursework. Otherwise, the remaining students will be randomly assigned into groups. • Each group should consist of four members. If you would like to form a group of a different size (minimum: 3, maximum: 5), you can do this in exceptional circumstances only, and you must explain these circumstances in your report. In the case of you being in a group of 5, you should be adding new methods (not covered in class) or adding some extra results etc. We assume that all members in the group will receive equal marks for the technical report (80%) of the assignment. If group members do not agree with this default mark distribution and raise an issue (e.g. somebody not-engaged or made a poor contribution), the unit leader will contact the group members via email to resolve the issue and marks can be reallocated. SUBMISSION FORMAT There will be two deliverables for this assignment: 1. Technical report (80%) - Group submission. The technical report needs to address both tasks A and B. 2. Individual presentation (20%) – Individual submission. Prepare a ten-minute video presentation, where you discuss the problem statement, your role in the group, your contribution to the final submission and the steps that you followed for completing the tasks. The links to the video presentation of every group member should be included in the project report. The word count for the groups submission and individual submission are as follows: • 3 members – 9,000 words total (7,500 for group report, 500 each for individual presentation) • 4 members – 12,000 words total (10,000 for group report, 500 each for individual presentation) • 5 members – 15,000 words total (12,500 for group report, 500 each for individual presentation) MARKING CRITERIA The following criteria will be used to assess the assignment: Technical report: Task A: 40% Subtask Expectations 1. Entity Relationship • Model (5%) • • • • 2. Conversion into • Relational Schema (5%) • • ILO All required entities, attributes are listed. Multivalued or any special attributes are identified. Relations and cardinalities (including the optional participation) are correctly identified and described. Type of participation (one-one, one-many, many-many) are correctly identified. State your assumptions which you used in creation of ER diagram (if any) ERD is correctly translated into relational schema by depicting all the relations and their attributes. Identification of primary and foreign keys. Used the 8 step conversion rules. Page 3 of 6 1 1, 2 January 2021 v1 3. Conversion into • Databases and sample data (10%) • • 4. Design choices • explanation (5%) • 5. Use cases and • queries (15%) • • Design the database appropriately which corresponds to the proposed ER diagram using SQL, MongoDB and Neo4J. All the required constraints are added and clearly shown. All of the entities have sufficient sample data, and it is clearly shown in all three databases - SQL, MongoDB and Neo4J Explanation of the choice of entities for database technologies How your choice fits the characteristics of these technologies. Use cases are clear and the queries address them well. Queries are correct and deliver valid results, which are clearly shown. Demonstrate the 5 use cases (can be different for each technology), query code and output for each database technology - SQL, MongoDB and Neo4J 1 mark for each query (test case, code and result) for all three databases and hence 5 marks for each database technology SQL, MongoDB and Neo4J. 2 2 2 Task B: 40% Subtask 1. Training testing set (5%) Expectations and • • 2. Classification • models (10%) • • • 3. PCA (10%) • • 4. Feature Selection • method (10%) • • 5. Challenges and • Implications (5%) • • ILO Training and testing data loaded Detailed description with all the values displayed and loaded efficiently Clearly list the 2 models chosen and justification of your choice Implementation of both the models All results displayed and explained clearly Performance comparison of both methods chosen PCA implemented on the dataset with adequate outcome explanation. The question in step 3 of Part B of assignment answered with proper justification from results obtained Justification of the feature selection method chosen. Result explanation after application of feature selection method on any of the algorithm point 2 Result comparison with and without feature selection method Time analysis of the selected models. Time analysis comparison of the models with and without feature selection algorithms. Challenges and implications 4 4 4,5 4,5 3 Individual video 20%: Subtask Expectations Clarity of explanation • (5%) • • Presentation slides • (10%) • • • • • ILO Problem analysis Your role in the group activity Approach used for group work Explain the methods used Steps followed for completing tasks Your contribution to the final submission More pictorial representation in slides Slides should be well organized with not too much text Presentation skills Page 4 of 6 3 5 January 2021 v1 Summary and • conclusions (5%) Thorough elaboration explanation. on the results with meaningful 5 To get higher marks in this assignment: • • • • You should make assumptions about more possibilities which might exist while designing ERD diagrams apart from the ones listed in the use case scenarios (Task A). You should use more complex queries involving multiple entities (more than 2) in the test use cases (Task A). You should use at least one classification model which is not covered in the lecture and also at least one feature selection algorithm not covered in lecture (Task B). The better, the results are presented and compared in the form of graphs, tables and relevant visualization tools, more marks will be awarded (Task B). INTENDED LEARNING OUTCOMES (ILOs) This assignment tests your ability to: 1. 2. 3. 4. Perform and critically analyse data modelling. Understand the underlying technology of various database systems. Gain critical understanding of Data analytics’ challenges. Gain critical understanding of the most significant pattern recognition algorithms for dealing with Data and Big Data. 5. Be able to interpret the results from Data and Big Data analytics’ algorithms and use the appropriate methods for reporting the results. QUESTIONS ABOUT THE BRIEF Any issues about the assignment can be raised with the lecturer during lectures/seminars or by appointment. Email will be used for handling questions about the brief when no seminar/lab session is scheduled between the time the questions arise and the submission deadline. Signature Marker Avleen Malhi Page 5 of 6 January 2021 v1 HELP AND SUPPORT • If a piece of coursework is not submitted by the required deadline, the following will apply: 1. If coursework is submitted within 72 hours after the deadline, the maximum mark that can be awarded is 50%. If the assessment achieves a pass mark and subject to the overall performance of the unit and the student’s profile for the level, it will be accepted by the Assessment Board as the reassessment piece. The unit will count towards the reassessment allowance for the level; This ruling will apply to written coursework and artefacts only; This ruling will apply to the first attempt only (including any subsequent attempt taken as a first attempt due to exceptional circumstances). 2. If a first attempt coursework is submitted more than 72 hours after the deadline, a mark of zero (0%) will be awarded. 3. Failure to submit/complete any other types of coursework (which includes resubmission coursework without exceptional circumstances) by the required deadline will result in a mark of zero (0%) being awarded. The Standard Assessment Regulations can be found on Brightspace. • If you have any valid exceptional circumstances which mean that you cannot meet an assignment submission deadline and you wish to request an extension, you will need to complete and submit the Exceptional Circumstances Form for consideration to your Programme Support Officer (based in C114) together with appropriate supporting evidence (e.g, GP note) normally before the coursework deadline. Further details on the procedure and the exceptional circumstances form can be found on Brightspace. Please make sure that you read these documents carefully before submitting anything for consideration. For further guidance on exceptional circumstances please see your Programme Leader. • You must acknowledge your source every time you refer to others’ work, using the BU Harvard Referencing system (Author Date Method). Failure to do so amounts to plagiarism which is against University regulations. Please refer to http://libguides.bournemouth.ac.uk/bu-referencing-harvardstyle for the University’s guide to citation in the Harvard style. Also be aware of Self-plagiarism, this primarily occurs when a student submits a piece of work to fulfill the assessment requirement for a particular unit and all or part of the content has been previously submitted by that student for formal assessment on the same/a different unit. Further information on academic offences can be found on Brightspace and from https://www1.bournemouth.ac.uk/discover/library/using-library/howguides/how-avoid-academic-offences • Students with Additional www.bournemouth.ac.uk/als Learning Needs may contact Learning Support on Disclaimer: The information provided in this assignment brief is correct at time of publication. In the unlikely event that any changes are deemed necessary, they will be communicated clearly via e-mail and Brightspace and a new version of this assignment brief will be circulated. Page 6 of 6 Page 1 of 6 January 2021 v1 Use-case 3 You want to design a database for pharmaceutical companies that supplies medicines. Each pharmaceutical company is identified by a name and a phone number Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmacies, and a pharmacy can contract with several pharmaceutical companies. For each contract, you have to store a start date, an end date and the text of the contract. Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract. If a pharmaceutical company is deleted, you need not keep track of its products any longer Each pharmacy has a name, an address, and a phone number. Each pharmacy sells several drugs For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company and the trade name identifies a drug uniquely from among the products of that company. A drug could be sold at several pharmacies and the price could vary from one pharmacy to another. Patients are identified Chiccames, addresses, and ages. Doctors are identified by an SSN, name, special years of experience. Doctor be drugs for patients. Every doctor has at least one calent, but a patient has one doctor. A doctor could probe one or more drugs to patients A patie it could obtain prescriptions from several doctors. Each prescription has a date and quantity druge associated with it. You should assume that if a doctor prescribes the sal drug torame patif it more than once, only the last such prescription needs to be stored. Chose one of the above use case scenarios and 1. Develop an Entity-Relationship model of the information requirements for the selecte scenario 2. Translate your model into an equivalent relational schema. Specify all relation headings indicating primary and foreign keys. 3. Implement at least three important entities from your relational schema in SQL, MongoDE and Neo4J (could be different three entities for each technology) and generate sample data for all of the implementations. Show samples of generated data. 4. Explain why you chose the particular entities to implement with each technology 5. Come up with 5 test cases for each database technology and implement those usin queries. Show the queries code and the output for each database technology. Include the answers to each of the five points above in your technical report. B an era of growing data complexity and volume, feature solo construction techniques pla key role in understanding our noping reduce the dimensionality and improve learnay olduwy prodlems. Both for data and big data processing and analytics feature selection techniques are important for reducing the time required to build machine learning models and improving the performance of these algorithms. Moreover, principal component analysis is an important algorithm used in data and big data processing for the purpose of data visualisation, as well as for dimensionality reduction and for gaining insight in the knowledge hidden in the data. For the submission, you are given 3 datasets (below this section) and you are asked to define the classification problem of your choice, select one dataset and perform the following tasks: 1. Define the training and testing set for your dataset. 2 Implement neural network and one other classification algorithm of your choice and compare the performance for the dataset you choose. 3. Apply Principal Component Analysis to the dataset and explain its outcome. How does the number of principal components affect the percentage of variance covered for this dataset? 4. Apply any feature selection method of your choice and compare the performance using any one algorithm used in question 2 before and after applying the feature selection algorithm 5. Discuss the challenges and implications regarding the time required to build the required models. Compare the times with and without feature selection method. You must perform the tests on the test data set to evaluate the results. Use the AUC (area under curve) and accuracy as a metric for comparing the performance. Include the answers to each of the five points above in your technical report.