COS 597A, Fall 2011

Solutions to Problem Set 3

 

Problem 1

Part A.  Is Result_not_in_Rome =  πbname(Acct ) - Result_in_Rome ?  No.    Consider a branch that has one account owned by a customer living in Rome and another account with no owner living in Rome.    The first account satisfies the WHERE condition of  Result_in_Rome;  therefore, the branch is in Result_in_Rome and not in  πbname(Acct ) - Result_in_Rome.   The second account satisfies the WHERE condition of  Result_not_in_Rome;  therefore, the branch is in Result_not_in_Rome.

 
Part B. 

SELECT A.bname
FROM Acct A, Owner D, Cust C
WHERE A.acctn = D.acctn AND D.name = C.name AND C.city=‘Rome’j

The results for this query may contain more duplicates than the original:   a branch name will appear once for every owner of every account that has owners who live in Rome.   For the original query, the branch name appears once for every account that has any owner who lives in Rome.    If we use "SELECT DISTINCT A.bname" for each query, they will truly be identical.


Problem  2

 

Part 1.

 

(a)

 

SELECT DISTINCT  A.author

FROM                         main M, authors A

WHERE                     (M.date = 'June 2002' OR

M.date = 'July 2002' OR

M.date = 'August 2002') AND

M.id = A.id

 

28 matches found:

author

Tsioutsiou, Kostas

Tzanetakis, George

Wang, Limin

Pai, Vivek

Peterson, Larry

Walker, David

Ligatti, Jay

Bauer, Lujo

Shalaby, Nadia

Gottlieb, Yitzchak

Wawrzoniak, Mike

Craver, Scott A.

McGregor, John P.

Wu, Min

Liu, Bede

Stubblefield, Adam

Swartzlander, Ben

Wallach, Dan S.

Dean, Drew

Felten, Edward W.

Correa, Wagner T.

Klosowski, James T.

Silva, Claudio T.

Pang, Ruoming

Qie, Xiaohu

Essl, George

Ji, Minwen

Kleinstein, Steven H.

 

 

(b)  

 

SELECT                    M.id, M.title, M.date

FROM                         main M

WHERE                     M.abstract LIKE '%security%'

 
39 matches found:


id title date
TR-323-91 Hyperfile, A Database Manager for Documents (Thesis) June 1991
TR-501-95 Security Flaws in the HotJava Web Browser November 1995
TR-520-96 A Revocable Backup System March 1996
TR-523-96 Studies in Computational Number Theory with Applications to Cryptography July 1996
TR-540-96 Web Spoofing: An Internet Con Game December 1996
TR-546-97 Extensible Security Architectures for Java April 1997
TR-558-96 Security and Document Compatability for Electronic Refereeing October 1996
TR-560-97 Experience with Secure Multi-Processing in Java September 1997
TR-566-97 Java Security: Web Browsers and Beyond February 1997
TR-567-97 A Java Filter December 1997
TR-619-00 Machine Instruction Syntax and Semantics in Higher Order Logic April 2000
TR-634-01 Access Control for Ad-hoc Collaboration (Thesis) January 2001
TR-636-01 Models for Security Policies in Proof-Carrying Code March 2001
TR-638-01 A Proof-Carrying Authorization System April 2001
TR-647-02 JVM TCB: Measurements of the Trusted Computing Base of Java Virtual Machines April 2002
TR-648-02 A Trustworthy Proof Checker April 2002
TR-649-02 More Enforceable Security Policies June 2002
TR-657-02 Reading Between the Lines: Lessons from the SDMI Challenge July 2002
TR-655-02 A Calculus for Composing Security Policies August 2002
TR-677-03 Access Control for the Web via Proof-Carrying Authorization (Thesis) September 2003
TR-679-03 Analysis of the MediaMax CD3 Copy-Prevention System October 2003
TR-681-03 Edit Automata: Enforcement Mechanisms for Run-time Security Policies May 2003
TR-687-03 Secure Linking: a logical framework for policy-enforced component (Thesis) December 2003
TR-699-04 A Language and System for Composing Security Policies April 2004
TR-714-04 Network Security Management with High-level Security Policies September 2004
TR-710-04 Nicephorus: Striking a Balance between the Recovery Capability and the Overhead of Byzantine Detection October 2004
TR-715-04 Safe Heterogeneous Applications: Curing the Java Native Interface October 2004
TR-718-04 Policy-based Multihost Multistage Vulnerability Analysis December 2004
TR-720-05 Enforcing Non-safety Security Policies with Program Monitors January 2005
TR-735-05 A logic-programming approach to network security analysis September 2005
TR-744-06 Windows Access Control Demystified January 2006
TR-752-06 Policy Enforcement via Program Monitoring May 2006
TR-775-07 A Formal Approach to Practical Network Security Management February 2007
TR-783-07 A Cryptographic Study of Secure Internet Measurement May 2007
TR-784-07 Morpheus: Making Routing Programmable June 2007
TR-787-07 Towards Internet-wide Multipath Routing June 2007
TR-795-07 Analyzing Security Advice in Functional Aspect-oriented Programming Languages August 2007
TR-800-07 A Cryptographic Study of Secure Fault Detection in the Internet October 2007
TR-808-07 How Small Groups Can Secure Interdomain Routing December 2007

 

(c)

 

SELECT DISTINCT  B.author

FROM                         main M, authors A, authors B

WHERE                     M.id = A.id AND

M.id = B.id AND

A.author LIKE 'Appel%' AND

M.date LIKE '%2001%' AND

NOT (B.author LIKE 'Appel%')

 

2 matches found:

author

Chen, Juan

Felten, Edward W.

 

 

(d) 

 

SELECT                    A.author, COUNT(M.id) AS 'Number of TRs'

FROM                         main M, authors A

WHERE                     M.id = A.id AND

M.date LIKE '%2004%'

GROUP BY                A.author

HAVING                     COUNT(M.id) > 1

 
18 matches found:

author Number of TRs
Appel, Andrew W. 3
Avramopoulos, Ioannis 2
Garg, Nitin 2
Govindavajhala, Sudhakar 2
Kobayashi, Hisashi 2
Krishnamurthy, Arvind 5
Lai, Junwen 2
Li, Kai 3
Nakao, Akihiro 2
Ou, Xinming 3
Pai, Vivek 2
Peterson, Larry 2
Rusinkiewicz, Szymon M. 2
Sobti, Sumeet 2
Tan, Gang 2
Walker, David 3
Wang, Randolph Y. 6
Zheng, Fengzhou 2



 

(e)


SELECT                    M.entry as entries, COUNT(*) as number

FROM                        main M

GROUP BY                M.entry

HAVING                     COUNT(*) >= all  (SELECT        COUNT(*)
                                                                    FROM            main N
                                                                    GROUP BY   N.entry         )

1 match found:

entries number
1994-04-04 101


Part 2. 

 

In the given database schema, the author-lists of the technical reports are stored in a separate table. This makes it convenient to record an arbitrary number of authors for any technical report, and it also allows one to write sophisticated queries involving authors.

 


Problem  3

Part 1

mysql> create table consultant (
    -> name varchar(30),
    -> street varchar(30),
    -> city varchar(30),
    -> primary key (name)
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> describe consultant;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(30) | NO   | PRI | NULL    |       |
| street | varchar(30) | YES  |     | NULL    |       |
| city   | varchar(30) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> create table client (
    -> c_name varchar(30),
    -> street varchar(30),
    -> city varchar(30),
    -> primary key (c_name)
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)
mysql> describe client;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_name | varchar(30) | NO   | PRI | NULL    |       |
| street | varchar(30) | YES  |     | NULL    |       |
| city   | varchar(30) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> create table works (
    ->    name varchar (30),
    ->    c_name varchar (30),
    ->    fee_rate float,
    ->    primary key (name, c_name),
    ->    foreign key (name) references consultant (name),
    ->    foreign key (c_name) references client (c_name) on delete cascade
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> describe works;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(30) | NO   | PRI |         |       |
| c_name   | varchar(30) | NO   | PRI |         |       |
| fee_rate | float       | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> create table supervised_by (
    ->    name varchar (30),
    ->    c_name varchar (30),
    ->    supervisor_name varchar (30),
    ->    primary key (name, c_name),
    ->    foreign key (name, c_name) references works (name, c_name),
    ->    foreign key (supervisor_name) references consultant (name) on delete cascade
    -> ) engine=InnoDB;
Query OK, 0 rows affected (0.10 sec)
mysql> describe supervised_by;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| name            | varchar(30) | NO   | PRI |         |       |
| c_name          | varchar(30) | NO   | PRI |         |       |
| supervisor_name | varchar(30) | YES  | MUL | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


Part 2


mysql> insert into consultant values('ctant1', 'st1', 'city');
Query OK, 1 row affected (0.04 sec)
mysql> insert into consultant values('ctant2', 'st2', 'city');
Query OK, 1 row affected (0.05 sec)
mysql> insert into consultant values('ctant3', 'st3', 'city');
Query OK, 1 row affected (0.05 sec)
mysql> insert into consultant values('ctant4', 'st4', 'city');
Query OK, 1 row affected (0.07 sec)
mysql> select * from consultant;
+--------+--------+------+
| name   | street | city |
+--------+--------+------+
| ctant1 | st1    | city |
| ctant2 | st2    | city |
| ctant3 | st3    | city |
| ctant4 | st4    | city |
+--------+--------+------+
4 rows in set (0.00 sec)


mysql> insert into client values ('c1', 'st', 'city');
Query OK, 1 row affected (0.05 sec)
mysql> insert into client values ('c2', 'st', 'city');
Query OK, 1 row affected (0.04 sec)
mysql> insert into client values ('c3', 'st', 'city');
Query OK, 1 row affected (0.04 sec)
mysql> insert into client values ('c4', 'st', 'city');
Query OK, 1 row affected (0.05 sec)
mysql> select * from client;
+--------+--------+------+
| c_name | street | city |
+--------+--------+------+
| c1     | st     | city |
| c2     | st     | city |
| c3     | st     | city |
| c4     | st     | city |
+--------+--------+------+
4 rows in set (0.00 sec)


mysql> insert into works values ('ctant1', 'c1', 20.0);
Query OK, 1 row affected (0.05 sec)
mysql> insert into works values ('ctant1', 'c2', 20.0);
Query OK, 1 row affected (0.05 sec)
mysql> insert into works values ('ctant2', 'c3', 20.0);
Query OK, 1 row affected (0.05 sec)
mysql> insert into works values ('ctant2', 'c4', 20.0);
Query OK, 1 row affected (0.06 sec)
mysql> select * from works;
+--------+--------+----------+
| name   | c_name | fee_rate |
+--------+--------+----------+
| ctant1 | c1     |       20 |
| ctant1 | c2     |       20 |
| ctant2 | c3     |       20 |
| ctant2 | c4     |       20 |
+--------+--------+----------+
4 rows in set (0.00 sec)


mysql> insert into supervised_by values ('ctant1', 'c1', 'ctant3');
Query OK, 1 row affected (0.04 sec)
mysql> insert into supervised_by values ('ctant1', 'c2', 'ctant3');
Query OK, 1 row affected (0.06 sec)
mysql> select * from supervised_by;
+--------+--------+-----------------+
| name   | c_name | supervisor_name |
+--------+--------+-----------------+
| ctant1 | c1     | ctant3          |
| ctant1 | c2     | ctant3          |
+--------+--------+-----------------+
2 rows in set (0.00 sec)



Part 3


i)
mysql> delete from consultant where name='ctant1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`aslp/works`, CONSTRAINT `works_ibfk_1` FOREIGN KEY (`name`) REFERENCES `consultant` (`name`))


ii)
mysql> delete from client where c_name='c1';
Query OK, 1 row affected (0.03 sec)

mysql> select * from client;
+--------+--------+------+
| c_name | street | city |
+--------+--------+------+
| c2     | st     | city |
| c3     | st     | city |
| c4     | st     | city |
+--------+--------+------+
3 rows in set (0.00 sec)

mysql> select * from works;
+--------+--------+----------+
| name   | c_name | fee_rate |
+--------+--------+----------+
| ctant1 | c2     |       20 |
| ctant2 | c3     |       20 |
| ctant2 | c4     |       20 |
+--------+--------+----------+
3 rows in set (0.00 sec)

iii)
mysql> update consultant set name='ctant5' where name='ctant1';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`aslp/works`, CONSTRAINT `works_ibfk_1` FOREIGN KEY (`name`) REFERENCES `consultant` (`name`))


iv)
mysql> insert into works values ('ctant3', 'c1', 20.0);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`aslp/works`, CONSTRAINT `works_ibfk_2` FOREIGN KEY (`c_name`) REFERENCES `client` (`c_name`) ON DELETE CASCADE)