Download Database Systems Assignment with Solution | CPSC 310 and more Assignments Deductive Database Systems in PDF only on Docsity!
Assigned 6/16/08, DUE in class on 6/23/
Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet
These problems pertain to writing constraints in relational algebra and SLQ. You will
also have to use database system documentation.
1. 5.5.2 b, p. 236
b. π bore > 14 (σ numGuns > 9 ( Classes ))= ∅
2. 7.1.5, p. 326
CREATE TABLE classes ( class varchar(20) NOT NULL PRIMARY KEY, type char(2), country varchar(20), numGuns int, bore int, disp int ); CREATE TABLE battles ( name varchar(20) NOT NULL PRIMARY KEY, date varchar(20) ); CREATE TABLE outcomes ( ship varchar(20) NOT NULL, battle varchar(20) NOT NULL, result varchar(10), CONSTRAINT oc_pk PRIMARY KEY(ship, battle) ); CREATE TABLE ships ( name varchar(20), class varchar(20), launched int ); If the ships table is already created in DERBY, we could say: ij> alter table ships alter column name not null; 0 rows inserted/updated/deleted ij> alter table ships add constraint s_pk primary key(name); 0 rows inserted/updated/deleted ij>
3. 7.1.6 c, p. 327
alter table ships add constraint s_pk primary key(name); alter table outcomes add constraint o_fk foreign key(ship) references ships(name);
In MySQL:
mysql> use ward-ships;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
| Tables_in_ward-ships |
Assigned 6/16/08, DUE in class on 6/23/
Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet
| battles |
| classes |
| outcomes |
| ships |
4 rows in set (0.00 sec)
mysql> describe ships;
| Field | Type | Null | Key | Default | Extra |
| name | varchar(20) | YES | | NULL | |
| class | varchar(20) | YES | | NULL | |
| launched | int(11) | YES | | NULL | |
3 rows in set (0.01 sec)
mysql> alter table ships add constraint s_pk primary key(name);
Query OK, 21 rows affected (0.02 sec)
Records: 21 Duplicates: 0 Warnings: 0
mysql> describe ships;
| Field | Type | Null | Key | Default | Extra |
| name | varchar(20) | NO | PRI | | |
| class | varchar(20) | YES | | NULL | |
| launched | int(11) | YES | | NULL | |
3 rows in set (0.01 sec)
mysql> describe outcomes;
| Field | Type | Null | Key | Default | Extra |
| ship | varchar(20) | YES | | NULL | |
| battle | varchar(20) | YES | | NULL | |
| result | varchar(10) | YES | | NULL | |
3 rows in set (0.01 sec)
mysql> alter table outcomes add foreign key(ship) references ships(name);
Query OK, 15 rows affected (0.01 sec)
Records: 15 Duplicates: 0 Warnings: 0
Assigned 6/16/08, DUE in class on 6/23/
Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet
ij> insert into product values ('H', NULL, 'printer'); 1 row inserted/updated/deleted ij> delete from product where model is NULL; 1 row inserted/updated/deleted ij> alter table Product add constraint modnNotNull CHECK (MODEL is not NULL); 0 rows inserted/updated/deleted ij> insert into product values ('H', NULL, 'printer'); ERROR 23513: The check constraint 'MODNNOTNULL' was violated while performing an INSERT or UPDATE on table '"APP"."PRODUCT"'. ij> alter table product drop constraint modnnotnull; 0 rows inserted/updated/deleted ij> Notice the SQLState 23513 issued above
5. 7.2.5, p. 333
a).
mysql> alter table PC add constraint a_check CHECK(NOT (speed<1200 and
price>1500));
Query OK, 13 rows affected (0.01 sec)
Records: 13 Duplicates: 0 Warnings: 0
mysql>
Interestingly, DERBY won’t add this constraint since rows in the table violate it already.
The same data is in the MySQL version of the table!! What does this day about MySQL?
ij> alter table PC add constraint a_check CHECK(NOT(speed<1200 and price > 1500); ERROR X0Y59: Attempt to add or enable constraint(s) on table '"APP"."PC"' failed because the table contains 2 row(s) that violate the following check constraint(s): A_CHECK. ij> select * from PC; MOD&|SPEED |RAM |HD |RD |PRICE
1001|700 |64 |10 |48xCD | 1002|1500 |128 |60 |12xDVD| 1003|866 |128 |20 |8xDVD | 1004|866 |64 |10 |12xDVD| 1005|1000 |128 |20 |12xDVD| 1006|1300 |256 |40 |16xDVD| 1007|1400 |128 |80 |12xDVD| 1008|700 |64 |30 |24xCD | 1009|1200 |128 |80 |16xDVD| 1010|750 |64 |30 |40xCD | 1011|1100 |128 |60 |16xDVD| 1012|350 |64 |7 |48xCD | 1013|733 |256 |60 |12xDVD|
13 rows selected ij>
b).
mysql> alter table Laptop add constraint b_check CHECK(NOT(screen<15 and
(hd<20 and price>2000)));
Assigned 6/16/08, DUE in class on 6/23/
Turn in a hardcopy with the Assignment Cover page and post your solutions to CSNet
Query OK, 10 rows affected (0.08 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql>
Ditto adding this constraint to the DERBY version of the data.
ij> alter table Laptop add constraint b_check CHECK(NOT(screen<15 and (hd<20 and price>2000))); ERROR X0Y59: Attempt to add or enable constraint(s) on table '"APP"."LAPTOP"' failed because the table contains 1 row(s) that violate the following check constraint(s): B_CHECK. ij> select * from laptop; MOD&|SPEED |RAM |HD |SCRE&|PRICE
2001|700 |64 |5 |12.1 | 2002|800 |96 |10 |15.1 | 2003|850 |64 |10 |15.1 | 2004|550 |32 |5 |12.1 | 2005|600 |64 |6 |12.1 | 2006|800 |96 |20 |15.7 | 2007|850 |128 |20 |15.0 | 2008|650 |64 |10 |12.1 | 2009|750 |256 |20 |15.1 | 2010|366 |64 |10 |12.1 |
10 rows selected ij>
6. 7.3.2 d, p. 336. Does MySQL support your answer? Explain. Graduate students also
work part e.
d).
mysql> alter table classes add constraint d_check CHECK(numGuns <= 14);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
MySQL accepted the constraint even though existing data in the table violates the
constraint. In other words, the data is inconsistent once the constraint is added.
ij> alter table classes add constraint d_check CHECK(numGuns <= 14); ERROR X0Y59: Attempt to add or enable constraint(s) on table '"APP"."CLASSES"' failed because the table contains 1 row(s) that violate the following check constraint(s): D_CHECK. ij>
e).
When would you check this constraint? Assume a ship can’t be inserted into outcomes
unless it is already in ships, which means its launch date is set first. Also assume that
battle dates are inserted before outcomes is loaded. So, to insert a ship in outcomes
implies it has been in some battle. At this point we’d check outcomes when a tuple is
inserted into it. The CHECK constraint is a table constraint involving the tuple’s ship and
battle. The following is what I have in mind (but the date fields are not so easily
comparable given the actual data).
