20F-AS3 SOL PDF

Preview

Summary

assignment 3...

Description

COMP 3005 Assignment #3 Due: October 14 Instruction 1. The assignment must be typed, completed on an individual basis, and submitted as a word/PDF file to culearn. Never email the assignment to the instructor or TAs! 2. For the Person table, Lastname is your own last name. If your own last name is not showing correctly in the result, you will get 0 mark for the question. 3. Relation and attribute names are not case sensitive. Part 1 (90 marks) Given the following NHL Fans database with tables Person, Team, and Fan. Use domain calculus to express the following queries. Each query is 7 marks and the result 2 marks. As the query result is a relation, there cannot be two attributes with the same name. If so, 2 marks will be deducted from the query part. Also, if a query can be represented in one DRC expression but more than one is used, 2 marks will be deducted Person P# Name Age P1 Lastname 20

City Ottawa

P2 Jones P3 Blake

30 25

Toronto Calgary

P4 Clark

20

Montreal

P5 Adams

30

Vancouver

Team T# Name T1 Maple Leafs T2

Canucks

T3 Canadiens T4 Senators

City Toronto Vancouver Montreal Ottawa

Fan P# T# P1 T1 P1 T2 P1 T3 P1 T4 P2 T1 P2 T2 P2 T3 P3 T1 P3 T2 P4 T1

1. Get the names of all teams that Lastname is a fan of. {N | (exists P, T)(Person(P, 'Lastname', _, _) and Team(T, N, _) and Fan (P, T))}; 2. Get the names of all persons who are fan of the team in Ottawa. {N | (exists P, T)(Person(P, N, _, _) and Team(T, _, 'Ottawa') and Fan (P, T))}; 3. Get the names of all persons who are not fans of any NHL teams. {N | (exists P)(Person(P, N, _, _) and not (exists T)(Fan (P, T)); 4. Get the names of all persons who are fans of all NHL teams. {N | (exists P)(Person(P, N, _, _) and (forall T)(not Team(T, _, _) or Fan(P, T))};

Name Maple Leafs Canucks Canadiens Senators Name Lastname Name Adams Name Lastname

5. Get the names of all persons who are fans of all teams except Senators. {N | (exists P)(Person(P, N, _, _) and (forall T) (not (exists M)(Team(T, M, _) and M!='Senators' or Fan(P, T))) or (not (exists M)(Team(T, M, _) and M='Senators' or not Fan(P, T))))};

Name Jones

6. Get all city name pairs such that the person in the first city is a fan of the team in the second city. You cannot have two attributes with the same name in the result. T(Pcity, Tcity) := {N,M |(exists P, T)(Person(P,_,Pcity) and Team(T,_,Tcity) and Fan(P,T))}; {C, D | T(C, D)}; Pcity Ottawa

Tcity Toronto

Ottawa Vancouver Ottawa Montreal Ottawa Ottawa Toronto Toronto

Name Count(*) Lastname 4 Jones 3

Name1 Name2 Lastname Jones

Name1 Name2 Adams Lastname

Blake Clark

Adams Adams

Jones Blake

Adams

Clark

Blake

2

Lastname Lastname

Clark

1

Jones

Lastname

Jones Jones

Blake Clark

Blake

Lastname

Blake

Adams

Blake Blake

Jones Clark

Clark

Adams

Calgary Vancouver

Name Lastname

Montreal

Jones

Clark Clark Clark

Lastname Jones Blake

Toronto Vancouver Toronto Calgary

Montreal Toronto Toronto

Lastname Adams Jones Adams

7. Get all person name/the number of team pair such that the person is a fan of the teams, not including the person who is not a fan of any team {N, count(T) | (exists P)(Person(P,N,_,_) and Fan(P, T)}; 8. Get all the names of persons who are fans of more than two teams. T(Name, Count) := {N, count(T) | (exists P)(Person(P,N,_,_) and Fan(P, T)}; {N | (exists C)(T1(N, C) and C > 2}; 9. Get all person name pairs such that they are fans of common teams. You cannot have two attributes with the same name in the result. T(Name1, Name2) := {N1, N2 |(exists P1,P2,T)(Person(P1,N1,_,_) and Person(P2,N1,_,_) and Fan(P1, T) and Fan(P2,T) and P1 != P2}; {N1,N2 | T(N1,N2); 10. Get all person name pairs such that they are not fans of common teams. You cannot have two attributes with the same name in the result. T(Name1, Name2) := {N1, N2 |(exists P1,P2)(Person(P1,N1,_,_) and Person(P2,N1,_,_) and not (exists T)(Fan(P1, T) and Fan(P2,T)}; {N1,N2 | T(N1,N2);

Part 2. (30 marks) You can either use Oracle VM on SCS Openstack (https://carleton.ca/scs/tech-support/scs-openstack/), or download and install Oracle VM (http://scs.carleton.ca/coursevms). Detail about how to install and use Oracle VM can be found in the file Oracle-VM.doc. You should test your statements on Oracle VM and take necessary screenshot to demonstrate your statements executed successfully for 1 and 2 and finally use “select * from table_name” to display the tables you have created and then put all the screenshots in the document for this assignment. 1. Use SQL DDL to create the above NHL Fans database. You should properly define primary keys and foreign keys with additional integrity constraints using each of unique, no null, checking, and default once. Each constraint is 2 marks (20) create table Person ( P# char(3) primary key, Name varchar2(10) unique, age int, City varchar2(10), check (age > 18));

create table Team ( T# char(3) primary key, Name varchar2(10) default 'Ottawa', City varchar2(10) not null);

create table Fan ( P# char(3), T# char(3), primary key (P#,T#), foreign key (P#) references Person (P#),foreign key (T#) ref references Team (T#));

2. Use SQL DML to populate the database created in 1. insert into Person values ('P1', 'Lastname', 20, 'Ottawa'); insert into Person values ('P2', 'Jones', 30, 'Toronto'); insert into Person values ('P3', 'Blake', 25, 'Calgary'); insert into Person values ('P4', 'Clark', 20, 'Montreal'); insert into Person values ('P5', 'Adams', 30, 'Vancouver');

insert into Team values ('T1', 'Maple Leafs', 'Toronto'); insert into Team values ('T2', 'Canucks', 'Vancouver'); insert into Team values ('T3', 'Canadiens', 'Montreal'); insert into Team values ('T4', 'Senators', 'Ottawa'); insert into Fan values ('P1', 'T1');

insert into Fan values ('P1', 'T2'); insert into Fan values ('P1', 'T3'); insert into Fan values ('P1', 'T4'); insert into Fan values ('P2', 'T1'); insert into Fan values ('P2', 'T2'); insert into Fan

(10)

values ('P2', 'T3'); insert into Fan values ('P3', 'T1'); insert into Fan values ('P3', 'T2'); insert into Fan values ('P4', 'T1');...