UFCFTK-30-1Resit CourseworkDeadline: 16th July 2019 at 14:00UFCFTK-30-1 Introduction to DatabasesResit Coursework (100 Marks)Deadline: 16/07/2019 at 14:00Contents1 Overview of the Assignment12 Task Specification22.1 Part 1 (50%) . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22.2 Part 2 (50%) . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23 Deliverables44 Plagiarism55 Marking Criteria55.1 Part 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .55.2 Part 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6A The Conference Logical Schema61 Overview of the AssignmentThis assignment requires you to finish 2 unrelated parts related to the differ-ent stages of a database system design. It assesses your understanding of thedifferent stages of a database system development, the principles of relationaldatabases and SQL, and the necessary skills to develop an end to end applica-tion.The assignment is described in detail in Section 2.This is an individual assignment.If you have questions about this assignment, please post them to the discus-sion board on Blackboard or contact the module leader.1
UFCFTK-30-1Resit CourseworkDeadline: 16th July 2019 at 14:002 Task Specification2.1 Part 1 (50%)A new pizza franchise calledBrizzlePizzahas chosen you to design a databasesystem for them to manage their pizza orders.The company owns a number of stores, each has a unique ID and has a streetaddress and a postcode. Each order (which is for one pizza only, i.e. if a customerwishes to orderndifferent pizzas, he/she needs to makendifferent orders) has aunique (across all stores) order number, an order date when the order was made,a completion date indicating when the order has been completed. Note that anyorder belongs to a single store but any store can issue many orders. Each menuitem (pizza) has a unique name, e.g. Tuna Heaven, and a unique menu itemID number. Each pizza on the menu has a pre-fixed set of toppings, i.e. noextra toppings are allowed. Note that the number of toppings for the differentpizzas vary, e.g. one pizza might have 3 toppings whereas another might have 5toppings.When making an order, the customer states the pizza name (or the corre-sponding menu item ID) they would like to order, and since each pizza has apre-fixed set of toppings, the customer just needs to choose the base type theywould like for their order. Each base has a unique name, e.g. thin crust, a priceand total number of calories. Note that all pizzas are of the same size so noneed to store information about the size.Each topping has a unique ID, a unique name, e.g. Tomato, a price (penceper gram) and calories (per gram).Each pizza is a combination of a set of toppings (each with a quantity ingrams) and a base. For instance, the menu item Tuna Heaven, consists of 200gof tuna, 150g of tomato, 100g of prawns. The cost of an order is computed asthe sum of the product of the cost (per gram) of each topping used in the pizzaused in that order by the quantity of that topping used plus the cost for thechosen base.You are required to finish all following tasks:•Task 1 (22 Marks):Design a conceptual schema (using UML notation)from the above description.•Task 2 (22 Marks):Give the logical relational schema for the databaseincluding all constraints for all involved relations. Apply the necessarynormalization (if required) to eliminate any redundancy from the relationsso that your final design is in third normal form.6 Markswill be given if the design reads as one cohesive design, i.e. thelogical schema is a valid translation of the corresponding conceptual schema.2.2 Part 2 (50%)The physical database schema which youmust usefor this part of the as-signment as well as some sample data can be found in the associatedCon-ference.sqlscript which can be downloaded from Blackboard (next to the as-signment specification). The tables also exist under the SHARED schema on2
UFCFTK-30-1Resit CourseworkDeadline: 16th July 2019 at 14:00the university’s Oracle server. Note that you only have SELECT access on thetables in the SHARED schema so you cannot update the tables. To test taskswhich require updating the tables, you have to run the associated script in yourown schema. The logical schema of the concerned database can be found inAppendix A.Note that when marking your work, we will be using thesame database schema as in Conference.sql, so you are not allowed toalter the structure of the tables.You are required to finish all the following 4 tasks:•Task 1 (SQL) (10 Marks):This task requires you to write Oracle SQLDML statements. For each of the following, give the Oracle SQL DMLquery which fulfils the request:a) List the IDs and full names of all authors, sorted alphabetically bylast name. (2 Marks)b) Return the number of British authors. (3 Marks)c) For each pending (has not been completed yet) paper review, list thethe title of the paper and the full name of the reviewer responsiblefor the review, ordering the result by paper submission date, earliestfirst. (5 Marks)•Task 2 (PL/SQL) (10 Marks):This task requires you to write OraclePL/SQL code:a) Write an Oracle stored procedure that receives as input a numberrepresenting a paper ID and then lists the full names of all reviewerswho have a conflict of Interest with the paper in question. You shouldhandle any necessary exceptions/errors which may arise, e.g. the pa-per ID does not exist. (10 Marks)•Task 3 (Python & Oracle) (22 Marks):This task requires you towrite a Python desktop application that interfaces with the Conferencedatabase. Your application needs to perform the following 2 tasks:a) Allow the user to add a new institute. The user will be prompted toenter an institute name and a country name and then the applicationadds the new institute to the INSTITUTE table. The ID for the newinstitute must be in sequence and takes into account the most re-cent existing InstituteID. Perform the required checks, e.g. to checkwhether the country exists and if there is any violation of any of theconstraints on the INSTITUTE table. (10 Marks)b) Allow the user to obtain the average score of a paper from its com-pleted reviews. The user will be prompted to enter a paper ID andthen the average score of that particular paper will be displayed to3
The post UFCFTK-30-1Resit Coursework appeared first on mynursinghomeworks.