3 Questions with Solution of Database - Assignment 7 | CPSC 310 | Assignments Deductive Database Systems

CPSC 310 HW 7, Summer 2008, Ronnie Ward

Assigned 7/23/08, DUE in class on 7/30/08

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

These exercises were suggested by Ed Sciore for SimpleDB to help you better understand

MetaDataMgr, implementation of relational algebra, and SQL processing in SimpleDB.

Answers in RED.

1. The ProductScan and ProductPlan classes implement the relational algebra operator

‘cross product’. Using them as examples, write Plan and Scan classes for the UNION

relational operator. Do not remove duplicates. Using TestSort.java for illustrative

purposes, write a TestUnion.java program that tests your UnionPlan and UnionScan

classes. Make sure you enforce the union compatible rules discussed in class for the

union operator. BTW, as part of this problem, you do NOT have to ‘hook’ UnionPlan and

UnionScan into SimpleDB so that it knows how to use your new operators. That’s a later

problem for someone else ☺.

According to the class lecture on query processing, to implement bag union of two

relations R and S, we need to first verify union compatibility, then output all the tuples of

R followed by all the tuples of S. The tables R and S are union compatible if they have

the same number of columns and corresponding columns have the same data type.

One tricky issue is the bug in ProductScan methods getVal, getInt, and getString. The

bug surfaces when two scans have one or more common fields. You can’t just use the

same scan logic either. The UnionScan needs to track whether it is serving records from

s1, or from s2. I used a boolean scanningS1, initially true, for this purpose and replaced

every s1.next() with scanningS1=s1.next(). This flag is then used to correctly implement

getVal, getInt, getString. See attached code, but getInt is reproduced here as an example.

public int getInt(String fldname) {

if (scanningS1)

if (s1.hasField(fldname))

return s1.getInt(fldname);

return s2.getInt(fldname);

}

Below is the output of the attached TestUnion program.

new transaction: 1

creating new database

transaction 1 committed

new transaction: 2

1 2003 Dinosaur

2 2004 Isle of M

3 1997 Character

4 1994 Paula Abd

5 2004 The Rise

5 MOVIE records inserted.

6 1997 Sick

7 1992 8 Man

8 2004 What the

9 1991 Class of

10 2001 Fighter

5 MOVIE records inserted.

1 2003 Dinosaur

2 2004 Isle of M

3 1997 Character

Partial preview of the text

Download 3 Questions with Solution of Database - Assignment 7 | CPSC 310 and more Assignments Deductive Database Systems in PDF only on Docsity!

Assigned 7/23/08, DUE in class on 7/30/

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

These exercises were suggested by Ed Sciore for SimpleDB to help you better understand

MetaDataMgr, implementation of relational algebra, and SQL processing in SimpleDB.

Answers in RED.

1. The ProductScan and ProductPlan classes implement the relational algebra operator

‘cross product’. Using them as examples, write Plan and Scan classes for the UNION

relational operator. Do not remove duplicates. Using TestSort.java for illustrative

purposes, write a TestUnion.java program that tests your UnionPlan and UnionScan

classes. Make sure you enforce the union compatible rules discussed in class for the

union operator. BTW, as part of this problem, you do NOT have to ‘hook’ UnionPlan and

UnionScan into SimpleDB so that it knows how to use your new operators. That’s a later

problem for someone else ☺.

According to the class lecture on query processing, to implement bag union of two

relations R and S, we need to first verify union compatibility, then output all the tuples of

R followed by all the tuples of S. The tables R and S are union compatible if they have

the same number of columns and corresponding columns have the same data type.

One tricky issue is the bug in ProductScan methods getVal, getInt, and getString. The

bug surfaces when two scans have one or more common fields. You can’t just use the

same scan logic either. The UnionScan needs to track whether it is serving records from

s1, or from s2. I used a boolean scanningS1, initially true, for this purpose and replaced

every s1.next() with scanningS1=s1.next(). This flag is then used to correctly implement

getVal, getInt, getString. See attached code, but getInt is reproduced here as an example.

public int getInt(String fldname) { if (scanningS1) if (s1.hasField(fldname)) return s1.getInt(fldname); return s2.getInt(fldname); }

Below is the output of the attached TestUnion program.

new transaction: 1 creating new database transaction 1 committed new transaction: 2 1 2003 Dinosaur 2 2004 Isle of M 3 1997 Character 4 1994 Paula Abd 5 2004 The Rise 5 MOVIE records inserted. 6 1997 Sick 7 1992 8 Man 8 2004 What the 9 1991 Class of 10 2001 Fighter 5 MOVIE records inserted. 1 2003 Dinosaur 2 2004 Isle of M 3 1997 Character

Assigned 7/23/08, DUE in class on 7/30/

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

4 1994 Paula Abd 5 2004 The Rise 6 1997 Sick 7 1992 8 Man 8 2004 What the 9 1991 Class of 10 2001 Fighter transaction 2 committed

The ProductPlan method distinctValues() is buggy in the event that the schemas have

common fieldnames. To correctly handle the case of which scan is active I added a

method scanS1 that returns scanningS1, which can be used to indicate which plan is

operative. The other issue is when and how to implement the check for union

compatibility, and whether or not to cause an exception. I chose to do this by returning a

null Scan if the schemas are not compatible. See attached code for UnionPlan.

2. The TableMgr class in the MetadataMgr package is used to create a table. Revise it to

include a dropTable() method, which removes a table from the database. Make any

necessary modification to FileMgr class to support the dropTable() method. Write a test

program that demonstrates proper operation of dropTable(). For example, modify the

TestIndex program to dropTable() the movie table it creates. Without actually modifying

SimpleDB to ‘hook’ your dropTable() feature into the database system, write a paragraph

outlining what classes in SimpleDB would need to be changed to support an SQL

command, “DROP TABLE tablename”.

The following method was added to MetaDataMgr.

public boolean dropTable(String tblname, Transaction tx) { return tblmgr .dropTable(tblname, tx); }

The following method was added to TableMgr. As suggested in class, this code needs to

be beefed up to also remove any indexes files, and any other information stored as

metadata regarding the table being dropped.

public boolean dropTable(String tblname, Transaction tx) { boolean tblDropped = false ;

// drop the table from tblcat RecordFile tcatfile = new RecordFile(tcatInfo, tx); while (tcatfile.next()){ if (tcatfile.getString("tblname").equals((Object) tblname)){ tcatfile.delete(); tblDropped = true ; break ; } } tcatfile.close(); if (tblDropped){ RecordFile fcatfile = new RecordFile(fcatInfo, tx); while (fcatfile.next()) {

if (fcatfile.getString("tblname").equals((Object) tblname)) fcatfile.delete(); } fcatfile.close();

Assigned 7/23/08, DUE in class on 7/30/

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

this .pred = pred; this .ordbyflds = ordbyflds; } /**

Returns the order by clause fields mentioned in the select clause.
@return a collection of field names */ public Collection ordbyflds() { return ordbyflds; }

Third, I changed the default planner in SimpleDB server to a BasicQueryPlanner, and

then modified the BasicQueryPlanner class to support the order by clause fields if present

in the QueryData.

//Step 3.5: Add a sort plan for the order by clause if (data.ordbyflds() != null ) p = new SortPlan(p, data.ordbyflds(), tx);

3 Questions with Solution of Database - Assignment 7 | CPSC 310, Assignments of Deductive Database Systems

Related documents

Partial preview of the text

Download 3 Questions with Solution of Database - Assignment 7 | CPSC 310 and more Assignments Deductive Database Systems in PDF only on Docsity!

Assigned 7/23/08, DUE in class on 7/30/

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

These exercises were suggested by Ed Sciore for SimpleDB to help you better understand

MetaDataMgr, implementation of relational algebra, and SQL processing in SimpleDB.

Answers in RED.

1. The ProductScan and ProductPlan classes implement the relational algebra operator

‘cross product’. Using them as examples, write Plan and Scan classes for the UNION

relational operator. Do not remove duplicates. Using TestSort.java for illustrative

purposes, write a TestUnion.java program that tests your UnionPlan and UnionScan

classes. Make sure you enforce the union compatible rules discussed in class for the

union operator. BTW, as part of this problem, you do NOT have to ‘hook’ UnionPlan and

UnionScan into SimpleDB so that it knows how to use your new operators. That’s a later

problem for someone else ☺.

According to the class lecture on query processing, to implement bag union of two

relations R and S, we need to first verify union compatibility, then output all the tuples of

R followed by all the tuples of S. The tables R and S are union compatible if they have

the same number of columns and corresponding columns have the same data type.

One tricky issue is the bug in ProductScan methods getVal, getInt, and getString. The

bug surfaces when two scans have one or more common fields. You can’t just use the

same scan logic either. The UnionScan needs to track whether it is serving records from

s1, or from s2. I used a boolean scanningS1, initially true, for this purpose and replaced

every s1.next() with scanningS1=s1.next(). This flag is then used to correctly implement

getVal, getInt, getString. See attached code, but getInt is reproduced here as an example.

Below is the output of the attached TestUnion program.

Assigned 7/23/08, DUE in class on 7/30/

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

The ProductPlan method distinctValues() is buggy in the event that the schemas have

common fieldnames. To correctly handle the case of which scan is active I added a

method scanS1 that returns scanningS1, which can be used to indicate which plan is

operative. The other issue is when and how to implement the check for union

compatibility, and whether or not to cause an exception. I chose to do this by returning a

null Scan if the schemas are not compatible. See attached code for UnionPlan.

2. The TableMgr class in the MetadataMgr package is used to create a table. Revise it to

include a dropTable() method, which removes a table from the database. Make any

necessary modification to FileMgr class to support the dropTable() method. Write a test

program that demonstrates proper operation of dropTable(). For example, modify the

TestIndex program to dropTable() the movie table it creates. Without actually modifying

SimpleDB to ‘hook’ your dropTable() feature into the database system, write a paragraph

outlining what classes in SimpleDB would need to be changed to support an SQL

command, “DROP TABLE tablename”.

The following method was added to MetaDataMgr.

The following method was added to TableMgr. As suggested in class, this code needs to

be beefed up to also remove any indexes files, and any other information stored as

metadata regarding the table being dropped.

Assigned 7/23/08, DUE in class on 7/30/

Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet

Third, I changed the default planner in SimpleDB server to a BasicQueryPlanner, and

then modified the BasicQueryPlanner class to support the order by clause fields if present

in the QueryData.

Lastly, when I run the TestSort program (uncomment the print), the table is printed in

year order! Cool!