写作COMPSCI 351编程、SQL程序

” 写作COMPSCI 351编程、SQL程序StudentName: COMPSCI 351StudentID:TEST 1 – Model SolutionsSECOND SEMESTER 2020/2021COMPUTER SCIENCEFundamentals of Database SystemsTime Allowed: FORTY FIVE (45) minutesNOTE: The test is closed book. No calculators are permitted. Attempt ALL questions in this test. A maximum of 30 marks is available in this test.11. The Relational Model of Data.(a) Consider the relation schema Client. It stores the client number cid, the client namecname and client birthday cbday. Which superkeys does the following relation overClient satisfy? [3 marks]cid cname cbday1 Catherine 01/02/19902 Catherine 03/04/19863 Caleb 03/04/1986Solution (1 mark for the two keys; 1 mark for the three proper superkeys; 1 mark ifnothing else has been defined): {cid}, {cid,cname}, {cid,cbday}, {cid,cname,cbday}, {cname,cbday}(b) Consider the relation schema Lawyer. It stores the number lno and name lname ofa lawyer, and the practice lpractice the lawyer works in. Write down a single relationover Lawyer that satisfies the two keys {lno,lname} and {lname,lpractice}, violates all superkeys not contained in the keys above, and has as few tuples as Possible. [4 marks]One solution (1 mark for violating {lname}, 1 mark for violating {lno,lpractice}, 1mark for satisfying the keys, 1 mark for not introducing anything else):lno lname lpractice1 Harvey Pearson2 Harvey Specter Litt2 Mike Specter Litt(c) Consider the relation schema Laywer from before, as well as the relation schemaPartner with attributes pno, pname, and psince, expressing which year a lawyer hasbeen a partner since. Write down a single relation over Lawyer and a single relationover Partner that satisfy the foreign keys [pno]Lawyer[lno] and [pname]Lawyer[lname] onPartner, and do not satisfy Partner[pno,pname]Lawyer[lno,lname] (inclusion dependency)which requires for each tuple t over Partner a tuple t0 over Lawyer such thatt[pno, pname] = t0[lno, lname] holds, and each have as few tuples as possible. [3 marks]One solution (1 mark for each of the bullet points above)Partnerpno pname psince1 Harvey 2011Lawyerlno lname lpractice1 Mike Pearson2 Harvey Pearson22. SQL. Consider the Relational database schema {Client, Lawyer, Case} as given below: Client={cid, cname, cbday} with key {cid} Lawyer={lno, lname, lpractice} with key {lno} Case={cid, lno, date, verdict} with key {cid, lno, date} and foreign keys [cid] Client[cid] [lno] Lawyer[lno].(a) Write an English language description of the following query:SELECT c.lno, c.date, COUNT(c.id) AS number of casesFROM Case cWHERE c.verdict guiltyGROUP BY c.lno, c.dateHAVING COUNT() = 2;[4 marks]Solution (1 mark each): For each lno and date, what is the number of cases a lawyer with the lno has represented on the date where the verdict is different from guilty and provided there were at least two cases?(b) Write the following query in SQL: What is the id of clients that had all their caseshandled by lawyers named Annalise or Annamae? [3 marks]Solution (1 mark for the first two lines, 2 marks for the sub-query including all theconditions in the WHERE clause):SELECT c.cidFROM Case cWHERE NOT EXISTS ( SELECT FROM Case c1, Lawyer lWHERE c.cid=c1.cid AND c1.lno=l.lno ANDl.lname Annamae AND l.lname Annalise ) ;(c) Write the following query in SQL: What are the names of clients that were representedby at least two different lawyers from Pearson on the same day? [3 marks]Solution (several different solutions possible; 1 mark for correct join; 1.5 marks for thecorrect conditions in the WHERE clause; 0.5 marks for correct attribute in the SELECTclause):SELECT c.cnameFROM Case c1, Case c2, Client c, Lawyer l1, Lawyer l2WHERE c1.cid=c.cid AND c2.cid=c.cid AND c1.lno=l1.lno ANDc2.lno=l2.lno AND l1.lpractice=Pearson ANDl2.lpractice=Pearson AND c1.date=c2.date AND c1.lno c2.lno;33. Relational algebra. Consider the relational database schema {Client, Lawyer, Case}as given below: Client={cid, cname, cbday} with key {cid} Lawyer={lno, lname, lpractice} with key {lno} Case={cid, lno, date, verdict} with key {cid, lno, date} and foreign keys [cid] Client[cid] [lno] Lawyer[lno].Write relational algebra queries using only operators defined on the lecture slides.(a) Write an English language description of the following query:date,lno(verdict=guilty(Case)) lno(lpractice=Pearson(Lawyer))[3 marks]Solution (1 mark each): What are the dates on which every lawyer that works in the practice Pearson is involved in some case with verdict guilty?(b) Write in relational Algebra: What is the id of clients that had all their cases handledby lawyers named Annalise or Annamae? [3 marks]Solution (1 mark each): Q1 = lno(lname=Annalise(Lawyer) lname=Annamae(Lawyer)) Q2 = cid(Case (Q1 ./ Case)) Q3 = cid(Client) Q2(c) Write in relational algebra: What are the names of clients that were representedby at least two different lawyers From Pearson on the same day? [4 marks]Solution (1 mark each): Q1 = lno7lno0,verdict7verdict0(Case) ./ Case ./ lpractice=Pearson(Lawyer) Q2 = Q1 lno=lno0(Q1) Q3 = cid(Q2) ./ Client Q4 = cname(Q3)请加QQ：99515681 或邮箱：99515681@qq.com WX：codehelp

“