Assignment 3: Database Modification and Constraints Solution

Answers the following questions by writing SQL statements and get them verified in MySQL.




PART 1: SQL Database Modification




Database 1: A computer database schema consists of four relations, whose schemas are:




Product (maker, model, type)

PC (model, speed, ram, hdisk, price)




Laptop (model, speed, ram, hdisk, screen, price)

Printer (model, color, type, price)




Write the following database modifications in SQL, based on the above database schema.




Using two INSERT statements, store in the database the fact that PC model 1100 is made by manufacturer C, has speed 3.2, RAM 1024, hard disk 180, and sells for $2499.



Insert the facts that for every PC there is a laptop with the same manufacturer, speed, RAM, and hard disk, a 17-inch screen, a model number 1100 greater, and a price $500 more.
Delete all PC ’s with less than 100 gigabytes of hard disk.
Delete all laptops made by a manufacturer that doesn’t make printers.



Manufacturer A buys manufacturer B. Change all products made by B so they are now made by A.
For each PC, double the amount of RAM and add 60 gigabytes to the amount of hard disk.



For each laptop made by manufacturer B, add one inch to the screen size and subtract $100 from the price.



Database 2: Here is another database schema concerning World War II capital ships. It involves the following relations:




Classes (class, type, country, guns, bore, displacement)

Ships (name, class, launched)

Battles (name, bdate)




Outcomes (ship, battle, result)




Write the following database modifications in SQL, based on the above database schema.




The two British battleships of the Nelson class — Nelson and Rodney — were both launched in 1927, had nine 16-inch guns, and a displacement of 34,000 tons. Insert these facts into the database.



Two of the three battleships of the Italian Vittorio Veneto class — Vittorio Veneto and Italia — were launched in 1940; the third ship of that class, Roma, was launched in 1942. Each had nine 15-inch guns and a displacement of 41,000 tons. Insert these facts into the database.



Delete from Ships all ships sunk in battle.



Modify the Classes relation so that gun bores are measured in centimeters (one inch = 2.54 centimeters) and displacements are measured in metric tons (one metric ton = 1.1 tons). (Note: you may have to change the data type of bore and displacement accordingly.)



Delete all classes with fewer than three ships. (Note: This deletion may be rejected due to violation to foreign key constraints).
PART 2: Triggers




This assignment asks you to write integrity constraints in SQL. It uses the same database schema as used in Part 1.




Integrity constraints are specified in the SQL-99 standard. However, they are not fully implemented in database software products. Specifically, MySQL has the following limitations:




MySQL allows CHECK clause in the CREATE TABLE statement for compatibility only. It does NOT enforce the constraint.




MySQL does not support assertion.




MySQL supports triggers. But its support is limited to row-level triggers only. Further, only BEFORE and AFTER are supported as event time. INSTEAD OF is not supported.




MySQL triggers are still pretty powerful. As an example to answer the first question, you can put the following trigger in the database schema:




delimiter //




create trigger t1 before insert on printer

for each row

begin




if not exists (select * from product where model=new.model) then signal sqlstate '45000' set message_text = 'Model not found.'; end if;

end;//

delimiter ;




Consider each question independently, that is, express each requirement as if no other requirement exists.




Database 1: For the computer database, write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint.




When inserting a new printer, check that the model number exists in Product.
When updating the price of a PC, check that there is no lower priced PC with the same speed.



When making any modification to the Laptop relation, check that the average price of laptops for each manufacturer is at least $1500.



When updating the RAM or hard disk of any PC, check that the updated PC has at least 100 times as much hard disk as RAM.



When inserting a new PC, laptop, or printer, make sure that the model number did not previously appear in any of PC, Laptop, or Printer.



Database 2: For the battleship database, write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint.




When a new class is inserted into Classes, also insert a ship with the name of that class and a NULL launch date.



When a new class is inserted with a displacement greater than 35,000 tons, allow the insertion, but change the displacement to 35,000.



If a tuple is inserted into Outcomes, check that the ship and battle are listed in Ships and Battles, respectively, and if not, insert tuples into one or both of these relations, with NULL components where necessary.



When there is an insertion into Ships or an update of the class attribute of Ships, check that no country has more than 20 ships.



Check, under all circumstances that could cause a violation, that no ship fought in a battle that was at a later date than another battle in which that ship was sunk.



Prepare your answers in a plain text file and submit to e-learning. For each question, include in the file:

1. The question itself, 2. Your SQL statements or codes.