Recent Questions and Assignment Topics

Assessment item 3 back to top Normalisation Value: 15% Due Date: 13 -Sep -2019 Return Date: 09 -Oct -2019 Submission method options: Alternative submission method Task back to top Suppose that you have been given the table structure and data shown in Table below, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees, and can edit more than one journal. Table 1: Sample PROFESSOR Records Attribute Name Sample Value Sample Value Sample Value Sample Val ue EMP_NUM 123 104 118 PROF_RANK Professor Asst. Professor Assoc. Professor Assoc. Professor EMP_NAME Ghee Rankin Ortega Smith DEPT_CODE CIS CHEM CIS ENG DEPT_NAME Computer Info. Systems Chemistry Computer Info. Systems English PROF_OFFICE KDD -567 BLF -119 KDD -562 PRT -345 ADVISEE 1215, 2312, 3233, 2218, 2098 3102, 2782, 3311, 2008, 2876, 2222, 3745, 1783, 2134, 2789, 3456, 2002, 2046, 2018, 2764 2873, 2765, 2238, 2901, 2308 2378 COMMITTEE_CODE PROMO, TRAF APPL, DEV DEV SPR, TRAF PROMO, SPR DEV JOURNAL_CODE JMIS, QED, JMGT JCIS, JMGT Given the information in Table 1: a. Draw the dependency diagram. (3 marks) b. Identify the multivalued dependencies. (3 marks) c. Create the dependency diagrams to yield a set of table structures in 3NF. (6 marks) e. Draw the Crow’s Foot ERD to reflect the dependency diagrams you drew in Part c. ( Note: You might have to create additional attributes to define the proper PKs and FKs. Make sure that all of your attributes conform to the naming conventions.) (3 marks) NOTE For guidance on how to normalise database relations from a dependency diagram, watch this very short YouTube video: https://www.youtube.com/watch?v=vkUyuLLgmwQ Manually drawing the dependency diagram, or the set of 3NF relations or the relatio nal schema is NOT acceptable. Rationale back to top This assessment task will assess the following learning outcome/s: • be able to apply normalisation techniques to optimise a database design. Marking criteria and standards back to top The marking criteria for t his assignment is: Criteria HD DI be able to apply Analysis Analysis normalisation techniques to optimise a database design. identifies all PKs and functional, partial and transitive dependencies. Accurately identifies all 3NF relations, including PKs, relevant attributes and referential integrity constraints. Creates an ERD that represents all 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. identifie s PKs and functional, partial and transitive dependencies with minor omissions. Accurately identifies 3NF relations, including PKs, relevant attributes and referential integrity constraints with minor omissions. Creates an ERD that represents identified 3NF relations and includes all entities, PKs, attributes, relationships, associative entities (if applicable), cardinalities and optionalities. Requirements back to top Either save your diagrams in a PDF file or embed them in a Word document. Don't use any other file formats Please include your student ID, name and subject code in your submission. Assessment item 4 back to top Query databases using SQL Value: 15% Du e Date: 04 -Oct -2019 Return Date: 28 -Oct -2019 Submission method options: Alternative submission method Task back to top Task: Complex SQL Quer ies back to top NOTES • This assignment requires you to use SQLite to complete the tasks listed below. • For guidance on how to install SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=9ynCDVg54H0 • For guidance on how to create tables using SQLite, watch this short YouTube video: https://www.youtube.com/watch?v=kij5lPw0F -o • Typing the SQL statements or the results is NOT acceptable. Instructions From Resources under Main Menu in Interact 2 go to Assignment 4 Folder Copy sqlit e3.exe file from the Assignment 4 Folder into a folder in your machine Copy a4.txt from the Assignment 4 Folder into the folder in your machine where your SQLite file is located From the SQLite Command Prompt execute the a4.txt script. To do this, at t he SQLite Command Line type .read a4.txt [to create the tables you will use to answer this assignment’s questions] At the SQLite Command Line type .headers on At the SQLite Command Line type .mode column Write and run SQL statements to complete the f ollowing tasks (Each Query : 1.5 (0.75 query+ 0.75 for screenshot) Marks) 1. Show sums of line units for each invoice. 2. Show the details of the products that do not have a value for the attribute v_code. 3. Show the details of the invoices whose subtotal is great er than 24 but less than 76. 4. Show the details of the invoice who has the minimum subtotal. 5. Show the codes and names of the vendors who supplied products. 6. Using EXCEPT show the codes of the vendors who did not supply any products. 7. Using ‘NOT IN’ show the c odes and names of the vendors who did not supply any products. 8. List the names and codes of vendors and the number of products each vendor has supplied, i.e. vendor XXX has supplied xxx products, and vendor YYY has supplied yyy products etc. 9. Show the detail s of the employees who are located in area code 615. 10. Using inner join, list the details of the products whose line price is greater than 99. You are required to submit: 1. The SQL statements for each query, which should be copied from your SQLite3 Command Line and pasted into your submission file. If you type .echo on at the SQLite3 Command Line, SQLite will output your SQL statement with the results of the query making it easy to copy and paste both. 2. The screenshots of the results of the S QL statements which are to be copied from your SQLite3 Command Line and pasted into your submission file immediately after you execute the SQL query. If you used an output file please submit only the output file. Rationale back to top This assessment task will assess the following learning outcome/s: • be able to use complex SQL commands to query a database. Marking criteria and standards back to top The marking criteria for this assignment is: Criteria HD DI be able to use complex SQL commands to query a database. SQL syntax re turns all attributes that meet the business requirement from the tables that hold the data and applies the correct restrictions to retrieve the required information. SQL syntax returns all attributes that meet the business requirement from the tables that hold the data with minor omissions and applies appropriate restrictions to retrieve the required information. Presentation back to top The document must be prepared in a professional manner with clarity and sound organization. Requirements back to top Either submit your assignment as a PDF or a Word document. Don't use any other file formats. Please include your student ID, name and subject code in your submission.