For each academic, give the acnum, givename, famname and the

For each academic, give the acnum, givename, famname and the

Question 1. SQL (10 points).In addition to the lecture notes, you should also study by yourself the SQL*Plus tutorial onCanvas (the Oracle section) and other resources for syntax and useful functions.The relational schema for the Academics database is as follows:DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode)ACADEMIC(acnum, deptnum*, famname, givename, initials, title)PAPER(panum, title)AUTHOR(panum*, acnum*)FIELD(fieldnum, id, title)INTEREST(fieldnum*, acnum*, descrip)Some notes on the Academics database:? An academic department belongs to one institution (instname) and often has manyacademics. An academic only works for one department.? Research papers (PAPER) are often authored by several academics, and of course anacademic often writes several papers (AUTHOR).? A research field (FIELD) often attracts many academics and an academic can haveinterest in several research fields (INTEREST).Primary keys are underlined and foreign keys are marked with *. You should download theSQL script for defining and populating the database academics.sql from Canvas (the Oraclesection) and run academics.sql in your Oracle account to build the database.Write ONE SQL query for each of questions 1.1) through to 1.10). Your query must run inOracle SQL Developer. Each query is worth 1 point.Notes for marking:? Each question is worth 1 point.? For questions with “You must …” or “You must not …” requirement, queries failing tomeet the requirement receive maximum 0.5 mark. For example, question 1.2) has “Youmust use a subquery”. A query not using the subquery operator receives maximum 0.5mark.? Do not include the output of the query or the script used to create the tables.? Your query should not output duplicates but use DISTINCT only if necessary.? Queries are marked in terms of both correctness and efficiency. Unnecessary joins willincur deduction.21.1) For each academic, give the acnum, givename, famname and the total number of papers s/hehas written. Note that if an academic has not written any paper, his/her total should be zero. Youcan use or not use JOIN operators.1.2) List departments where at least one academic does not have any research interest. Listthe deptnum, deptname and instname of these departments. You must use a subquery.1.3) List the fieldnum, title and the total number of interested academics (under the heading”NO. ACADEMICS INTERESTED”) for each research field that some academics areinterested in. The list should be in increasing order of fieldnum. Note: research fields thatno academics are interested in are excluded.1.4) Find research fields that have at least ten interested academics. Give the fieldnum, titleand the number of interested academics for these research fields.1.5) Return the acnum of academic(s) who wrote the largest number of papers. You must NOTuse MAX. An SQL query that lists all academics in decreasing order of their total numberof papers is incorrect.1.6) Give the total number of academics who have not written any papers. You must use theNOT IN operator.1.7) Find research fields whose title contains the string ‘data’ and has at least one interestedacademic from the department with deptnum 100. List the fieldnum and title of theseresearch fields. You must use the EXISTS operator. Ensure your query iscase-insensitive.1.8) List papers (panum) that have authors from the same department. Do NOT use any JOINoperator. Do NOT use any subqueries.1.9) The SQL query below is meant to list research fields (fieldnum) where no academics from‘RMIT CS’ (deptnum=126) have an interest. But it is incorrect. Give the correct SQL query.select fieldnumfrom interest, academicwhere interest.acnum=academic.acnum and deptnum != 126;1.10) Consider the SQL query given below. Give the English explanation for the output of a) thesubquery, and b) the whole SQL query. Literal explanation will receive zero marks.select S.acnumfrom interest Swhere upper(S.descrip) like ‘%LOGIC%’and exists (select fieldnumfrom interestwhere acnum = S.acnum3and upper(descrip) not like ‘%LOGIC%’);Question 2. The Relational model (4 points).Consider the Customer Order database of the ABC company, which keeps data forcustomers and their orders. A database of only one relation is designed, as shown below:ABC(customerNo, customerName, phone, streetAddr, city, state, postCode, orderNo,productNo, quantity, unitPrice, salesRep)? A customer has a unique customer number (customerNo) and is also described byname, phone number, street address (streeAddr), city, state, and postCode.? An order is identified by a unique order number (orderNo).? A product has a unique product number (productNo) and has a unit price (unitPrice).? A customer can place several orders and an order is made by only one customer.? An order can include several products and each has some quantity, and a productcan be on several orders.? An order has one sales representative (salesRep) and one sales representative canbe responsible for several orders.Answer questions:2.1) (1 point) Give all likely FDs.2.2) (1 point) Give the candidate keys for the ABC relation. Explain your answer.2.3) (1 point) Give {CustomerNo}+ and {orderNo, salesRep}+ based on the FDs forQuestion 2.1).2.4) (1 point) Is the relation ABC in BCNF or 3NF? Explain your answe

"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.

Discount Code: CIPD30

WHATSAPP CHAT: +1 (781) 253-4162

Click ORDER NOW..