提供高质量的essay代写,Paper代写,留学作业代写-天才代写

首頁 > > 詳細

代做COMPSCI 351編程、SQL程序調試、SQL程序代寫代做SPSS|代寫R語言編程

StudentName: COMPSCI 351
StudentID:
TEST 1 - Model Solutions
SECOND SEMESTER 2020/2021
COMPUTER SCIENCE
Fundamentals of Database Systems
Time Allowed: FORTY FIVE (45) minutes
NOTE:
– 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.
1
1. The Relational Model of Data.
(a) Consider the relation schema Client. It stores the client number cid, the client name
cname and client birthday cbday. Which superkeys does the following relation over
Client satisfy? [3 marks]
cid cname cbday
1 Catherine 01/02/1990
2 Catherine 03/04/1986
3 Caleb 03/04/1986
Solution (1 mark for the two keys; 1 mark for the three proper superkeys; 1 mark if
nothing 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 of
a lawyer, and the practice lpractice the lawyer works in. Write down a single relation
over 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}, 1
mark for satisfying the keys, 1 mark for not introducing anything else):
lno lname lpractice
1 Harvey Pearson
2 Harvey Specter Litt
2 Mike Specter Litt
(c) Consider the relation schema Laywer from before, as well as the relation schema
Partner with attributes pno, pname, and psince, expressing which year a lawyer has
been a partner since. Write down a single relation over Lawyer and a single relation
over Partner that
– satisfy the foreign keys [pno]⊆Lawyer[lno] and [pname]⊆Lawyer[lname] on
Partner, and
– do not satisfy Partner[pno,pname]⊆Lawyer[lno,lname] (inclusion dependency)
which requires for each tuple t over Partner a tuple t
0 over Lawyer such that
t[pno, pname] = t
0
[lno, lname] holds, and
– each have as few tuples as possible. [3 marks]
One solution (1 mark for each of the bullet points above)
Partner
pno pname psince
1 Harvey 2011
Lawyer
lno lname lpractice
1 Mike Pearson
2 Harvey Pearson
2
2. 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 cases
FROM Case c
WHERE c.verdict <> ‘guilty’
GROUP BY c.lno, c.date
HAVING 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 cases
handled by lawyers named Annalise or Annamae? [3 marks]
Solution (1 mark for the first two lines, 2 marks for the sub-query including all the
conditions in the WHERE clause):
SELECT c.cid
FROM Case c
WHERE NOT EXISTS ( SELECT ∗
FROM Case c1, Lawyer l
WHERE c.cid=c1.cid AND c1.lno=l.lno AND
l.lname <> ‘Annamae’ AND l.lname <> ‘Annalise’ ) ;
(c) Write the following query in SQL: What are the names of clients that were represented
by 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 the
correct conditions in the WHERE clause; 0.5 marks for correct attribute in the SELECT
clause):
SELECT c.cname
FROM Case c1, Case c2, Client c, Lawyer l1, Lawyer l2
WHERE c1.cid=c.cid AND c2.cid=c.cid AND c1.lno=l1.lno AND
c2.lno=l2.lno AND l1.lpractice=‘Pearson’ AND
l2.lpractice=‘Pearson’ AND c1.date=c2.date AND c1.lno <> c2.lno;
3
3. 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 handled
by 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 represented
by at least two different lawyers from Pearson on the same day? [4 marks]
Solution (1 mark each):
– Q1 = δlno7→lno0
,verdict7→verdict0(Case) ./ Case ./ σlpractice=‘Pearson’(Lawyer)
– Q2 = Q1 − σlno=lno0(Q1)
– Q3 = πcid(Q2) ./ Client
提供高质量的essay代写,Paper代写,留学作业代写-天才代写 – Q4 = πcname(Q3)

聯系我們
  • QQ:1067665373
  • 郵箱:1067665373@qq.com
  • 工作時間:8:00-23:00
  • 微信:Badgeniuscs
熱點文章
程序代寫更多圖片

聯系我們 - QQ: 1067665373 微信:Badgeniuscs
? 2021 uk-essays.net
程序代寫網!

在線客服

售前咨詢
售后咨詢
微信號
Essay_Cheery
微信
全优代写 - 北美Essay代写,Report代写,留学生论文代写作业代写 北美顶级代写|加拿大美国论文作业代写服务-最靠谱价格低-CoursePass 论文代写等留学生作业代做服务,北美网课代修领导者AssignmentBack 北美最专业的线上写作专家:网课代修,网课代做,CS代写,程序代写 代码代写,CS编程代写,java代写北美最好的一站式学术代写服务机构 美国essay代写,作业代写,✔美国网课代上-最靠谱最低价 美国代写服务,作业代写,CS编程代写,java代写,python代写,c++/c代写 代写essay,作业代写,金融代写,business代写-留学生代写平台 北美代写,美国作业代写,网课代修,Assignment代写-100%原创 北美作业代写,【essay代写】,作业【assignment代写】,网课代上代考