A First Course in Database Systems（数据库基础教程 第三版）课后答案——2.3.1\2.3.2\2.4.1

A First Course in Database Systems（数据库基础教程 第三版）课后答案——2.3.1\2.3.2\2.4.1

2.3.1

In this exercise we introduce one of our running examples of a relational database schema. The database schema consists of four relations, whose schemas are:

Product(maker, model, type)

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

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

Printer(model, color, type, price)

The Product relation gives the manufacturer, model number and type (PC, laptop, or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types; that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number. The PC relation gives for each model number that is a PC the speed (of the processor, in gigahertz), the amount of RAM (in megabytes), the size of the hard disk (in gigabytes), and the price. The Laptop relation is similar, except that the screen size (in inches) is also included. The Printer relation records for each printer model whether the printer produces color output (true, if so), the process type (laser or ink-jet, typically), and the price. Write the following declarations:

a) A suitable schema for relation Product.

CREATE TABLE `product` (
  `maker` char(50) DEFAULT NULL,
  `model` int(11) NOT NULL,
  `p_type` char(20) DEFAULT NULL,
  PRIMARY KEY (`model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

b) A suitable schema for relation PC.

CREATE TABLE `pc` (
  `model` int(11) DEFAULT NULL,
  `speed` double DEFAULT NULL,
  `ram` int(11) DEFAULT NULL,
  `hd` int(11) DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

c) A suitable schema for relation Laptop.

CREATE TABLE `laptop` (
  `model` int(11) DEFAULT NULL,
  `speed` double DEFAULT NULL,
  `ram` int(11) DEFAULT NULL,
  `hd` int(11) DEFAULT NULL,
  `screen` double DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

d) A suitable schema for relation Printer.

CREATE TABLE `printer` (
  `model` int(11) DEFAULT NULL,
  `color` tinyint(1) DEFAULT NULL,
  `p_type` char(20) DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

e) An alteration to your Printer schema from (d) to delete the attribute color.

alter table printer drop color;

f) An alteration to your Laptop schema from © to add the attribute od (optical-disk type, e.g., cd or dvd). Let the default value for this attribute be ’none’ if the laptop does not have an optical disk.

alter table laptop add od char(10) default 'none';

2.3.2

This exercise introduces another running example, concerning World War II capital ships. It involves the following relations:

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

Ships(name, class, launched) Battles(name, date)

Outcomes(ship, battle, result)

Ships are built in “classes” from the same design, and the class is usually named for the fifirst ship of that class. The relation Classes records the name of the class, the type (’bb’ for battleship(战舰) or ’bc’ for battlecruiser(战列巡航舰)), the country that built the ship, the number of main guns, the bore (diameter of the gun barrel, in inches) of the main guns, and the displacement (weight, in tons). Relation Ships records the name of the ship, the name of its class, and the year in which the ship was launched. Relation Battles gives the name and date of battles involving these ships, and relation Outcomes gives the result (sunk, damaged, or ok) for each ship in each battle. Write the following declarations:

a) A suitable schema for relation Classes.

create table Classes (
	class char(20),
    s_type char(10),
    country char(20),
    numGuns int,
    bore float,
    displacement float
);

b) A suitable schema for relation Ships.

create table Ships(
	s_name char(100),
    class char(20),
    lauched date
);

c) A suitable schema for relation Battles.

create table Battles(
	s_name char(100),
    b_date date
);

d) A suitable schema for relation Outcomes.

create table Outcomes(
	ship char(100),
    battle char(100),
    result char(10)
);

e) An alteration to your Classes relation from (a) to delete the attribute bore.

alter table Classes drop bore;

f) An alteration to your Ships relation from (b) to include the attribute yard giving the shipyard where the ship was built.

alter table Ships add yard char(100);

2.4.1

This exercise builds upon the products schema of Exercise 2.3.1.

Recall that the database schema consists of four relations, whose schemas are:

Product(maker, model, type)

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

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

Printer(model, color, type, price)

Some sample data for the relation Product is shown in Fig. 20. Sample data for the other three relations is shown in Fig. 21. Manufacturers and model

numbers have been “sanitized,” but the data is typical of products on sale at the beginning of 2007. Write expressions of relational algebra to answer the following queries. You may use the linear notation of Section 4.13 if you wish. For the data of Figs. 20 and 21, show the result of your query. However, your answer should work for arbitrary data, not just the data of these figures.

Product:
+-------+-------+---------+
| maker | model | p_type  |
+-------+-------+---------+
| A     |  1001 | pc      |
| A     |  1002 | pc      |
| A     |  1003 | pc      |
| B     |  1004 | pc      |
| B     |  1005 | pc      |
| B     |  1006 | pc      |
| C     |  1007 | pc      |
| D     |  1008 | pc      |
| D     |  1009 | pc      |
| D     |  1010 | pc      |
| E     |  1011 | pc      |
| E     |  1012 | pc      |
| E     |  1013 | pc      |
| E     |  2001 | laptop  |
| E     |  2002 | laptop  |
| E     |  2003 | laptop  |
| A     |  2004 | laptop  |
| A     |  2005 | laptop  |
| A     |  2006 | laptop  |
| B     |  2007 | laptop  |
| F     |  2008 | laptop  |
| F     |  2009 | laptop  |
| G     |  2010 | laptop  |
| E     |  3001 | printer |
| E     |  3002 | printer |
| E     |  3003 | printer |
| D     |  3004 | printer |
| D     |  3005 | printer |
| H     |  3006 | printer |
| H     |  3007 | printer |
+-------+-------+---------+
Pc:
+-------+-------+------+------+-------+
| model | speed | ram  | hd   | price |
+-------+-------+------+------+-------+
|  1001 |  2.66 | 1024 |  250 |  2114 |
|  1002 |   2.1 |  512 |  250 |   995 |
|  1003 |  1.42 |  512 |   80 |   478 |
|  1004 |   2.8 | 1024 |  250 |   649 |
|  1005 |   3.2 |  512 |  250 |   630 |
|  1006 |   3.2 | 1024 |  320 |  1049 |
|  1007 |   2.2 | 1024 |  200 |   510 |
|  1008 |   2.2 | 2048 |  250 |   770 |
|  1009 |     2 | 1024 |  250 |   650 |
|  1010 |   2.8 | 2048 |  300 |   770 |
|  1011 |  1.86 | 2048 |  160 |   959 |
|  1012 |   2.8 | 1024 |  160 |   649 |
|  1013 |  3.06 |  512 |   80 |   529 |
+-------+-------+------+------+-------+

Laptop:
+-------+-------+------+------+--------+-------+
| model | speed | ram  | hd   | screen | price |
+-------+-------+------+------+--------+-------+
|  2001 |     2 | 2048 |  240 |   20.1 |  3673 |
|  2002 |  1.73 | 1024 |   80 |     17 |   949 |
|  2003 |   1.8 |  512 |   60 |   15.4 |   549 |
|  2004 |     2 |  512 |   60 |   13.3 |  1150 |
|  2005 |  2.16 | 1024 |  120 |     17 |  2500 |
|  2006 |     2 | 2048 |   80 |   15.4 |  1700 |
|  2007 |  1.83 | 1024 |  120 |   13.3 |  1429 |
|  2008 |   1.6 | 1024 |  100 |   15.4 |   900 |
|  2009 |   1.6 |  512 |   80 |   14.1 |   680 |
|  2010 |     2 | 2048 |  160 |   15.4 |  2300 |
+-------+-------+------+------+--------+-------+

Printer:
+-------+-------+---------+-------+
| model | color | p_type  | price |
+-------+-------+---------+-------+
|  3001 |     1 | ink-jet |    99 |
|  3002 |     0 | laser   |   239 |
|  3003 |     1 | laser   |   899 |
|  3004 |     1 | ink-jet |   120 |
|  3005 |     0 | laser   |   120 |
|  3006 |     1 | ink-jet |   100 |
|  3007 |     1 | laser   |   200 |
+-------+-------+---------+-------+

a) What PC models have a speed of at least 3.00?

select model from pc where speed > 3.0;

$R1={\sigma }_{speed>3.00}(Pc)$

$R2={\pi }_{model}(R1)$

+-------+
| model |
+-------+
|  1005 |
|  1006 |
|  1013 |
+-------+

b) Which manufacturers make laptops with a hard disk of at least 100GB?

select maker from product natural join (select * from laptop where hd >= 100) as T1;

$R1={\sigma }_{hd\ge 100}(Laptop)$​​​​

$R2=Product\bowtie R1$

$R3={\pi }_{maker}(R2)$​

+-------+
| maker |
+-------+
| E     |
| A     |
| B     |
| F     |
| G     |
+-------+

c) Find the model number and price of all products (of any type) made by manufacturer B.

$R1={\pi }_{model,price,maker}(Product\bowtie Laptop)$​​

$R2={\pi }_{model,price,maker}(Product\bowtie PC)$​​

$R3={\pi }_{model,price,maker}(Product\bowtie Printer)$​​

$R4=R1\cup R2\cup R3$​

$R5={\pi }_{mode,price}({\sigma }_{maker{=}^{\prime }{B}^{\prime }}(R4))$

select model,price from ((select model,price,maker from product natural join laptop as T1) 
union all (select model,price,maker from product natural join pc as T2) 
union all (select model,price,maker from product natural join printer as T3)) as T where maker='B';

+-------+-------+
| model | price |
+-------+-------+
|  2007 |  1429 |
|  1004 |   649 |
|  1005 |   630 |
|  1006 |  1049 |
+-------+-------+

d) Find the model numbers of all color laser printers.

${\pi }_{model}({\sigma }_{color=trueandp\_type{=}^{\prime }lase{r}^{\prime }}(Printer))$​​​

select model from printer where color=true and p_type='laser';

e) Find those manufacturers that sell Laptops, but not PC’s.

$R1={\pi }_{maker}({\sigma }_{p\_type{=}^{\prime }lapto{p}^{\prime }}(Product))$​

$R2={\pi }_{maker}({\sigma }_{p\_type{=}^{\prime }p{c}^{\prime }}(Product))$​

$R3=R1-R2$

select distinct maker from product where maker in (select maker from product where p_type='laptop') 
and maker not in (select maker from product where p_type='pc');

+-------+
| maker |
+-------+
| F     |
| G     |
+-------+

!f) Find those hard-disk sizes that occur in two or more PC’s

select distinct pc1.hd from pc pc1, pc pc2 where ( pc1.hd=pc2.hd and pc1.model<>pc2.model);

+------+
| hd   |
+------+
|  250 |
|   80 |
|  160 |
+------+

! g) Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once; e.g., list (i, j) but not (j,i).

$R1={\rho }_{p{c}_1}(Pc)$

$R2={\rho }_{p{c}_2}(Pc)$

$R3=R1{\bowtie }_{(pc1.speed=pc2.speedandpc1.ram=pc2.ram)}R2$​

$R4={\pi }_{pc1.model,pc2.model}(R3)$

select pc1.model pc1_model,pc2.model pc2_model from pc pc1, pc pc2 where (pc1.speed=pc2.speed and pc1.ram=pc2.ram and pc1.model < pc2.model);

+-----------+-----------+
| pc1_model | pc2_model |
+-----------+-----------+
|      1004 |      1012 |
+-----------+-----------+

!! h) Find those manufacturers of at least two different computers (PC’s or laptops) with speeds of at least 2.80.

$R1={\pi }_{model}({\sigma }_{speed\ge 2.80}(Pc)\cup {\sigma }_{speed\ge 2.8}(Laptop))$​

$R2={\pi }_{model,maker}(R1\bowtie Product)$

$R3={\rho }_{R3(model2,maker2)}(R2)$​​

$R4={\pi }_{maker}(R2{\bowtie }_{maker=maker2andmodel<>model2}R3)$​​

create view temp_view  as select * from product natural join (select * from (select model from pc where speed >= 2.80 union all select model from laptop where speed >= 2.80) as T1) as T2;
select distinct v1.maker from temp_view v1, temp_view v2 where v1.model > v2.model and v1.maker = v2.maker;
drop view if exists temp_view;

+-------+
| maker |
+-------+
| B     |
| E     |
+-------+

!! i) Find the manufacturer(s) of the computer (PC or laptop) with the highest available speed.

$R1={\pi }_{model,speed}(Pc)$;

$R2={\pi }_{model,speed}(Laptop)$;

$R3=R1\cup R2$;

$R4={\rho }_{R4(model2,speed2)}(R3);$

$R5=R3{\bowtie }_{speed<speed2}R4$

$R6=R3-R5$

$R7={\pi }_{maker}(R6\bowtie Product);$​

 create view R1 as select model,speed from Pc;
 create view R2 as select model, speed from Laptop;
 create view R3 as select * from (select * from R1 union all select * from R2) as T;
 create view R4 as select model as model2,speed as speed2 from R3;
 create view R5 as select model,speed from R3,R4 where speed<speed2;
 create view R6 as select model,speed from R3 where model not in (select model from R5);
 create view R7 as select distinct maker from (R6 natural join product);
 select * from R7;

+-------+
| maker |
+-------+
| B     |
+-------+

!! j) Find the manufacturers of PC’s with at least three different speeds.

$R1={\pi }_{maker,speed}(Product\bowtie Pc)$

$R2={\rho }_{R2(maker2,speed2)}(R1)$

$R3={\rho }_{R3(maker3,speed3)}(R1)$

$R4=R1{\bowtie }_{maker=maker2andspeed<>speed2}R2$

$R5=R4{\bowtie }_{maker3=makerandspeed3<>speed2andspeed3<>speed}R3$​

$R6={\pi }_{maker}(R5)$

create view R1 as select maker,speed from (product natural join pc);
create view R2 as select maker maker2, speed speed2 from R1;
create view R3 as select maker maker3, speed speed3 from R1;
create view R4 as select * from R1,R2 where maker=maker2 and speed <> speed2;
create view R5 as select * from R4,R3 where maker3=maker and speed3<>speed2 and speed3<>speed;
create view R6 as select distinct maker from R5;
select * from R6;

+-------+
| maker |
+-------+
| A     |
| D     |
| E     |
+-------+

!! k) Find the manufacturers who sell exactly three different models of PC.

$R1={\pi }_{maker,model}(Product\bowtie Pc)$

$R2={\rho }_{R2(maker2,model2)}(R1)$

$R3={\rho }_{R3(maker3,model3)}(R1)$​

$R4={\rho }_{R4(maker4,model4)}(R1)$

$R5=R1{\bowtie }_{maker=maker2andmodel<>model2}R2$

$R6=R3{\bowtie }_{maker3=makerandmodel3<>model2andmodel3<>model}R5$

$R7=R4{\bowtie }_{maker4=makerand(model4=modelormodel4=model2ormodel4=model3)}R5$

$R8={\pi }_{maker}(R7)$​​

create view R1 as select maker,model from (product natural join pc);
create view R2 as select maker maker2, model model2 from R1;
create view R3 as select maker maker3, model model3 from R1;
create view R4 as select maker maker4, model model4 from R1;
create view R5 as select * from R1,R2 where maker=maker2 and model<>model2;
create view R6 as select * from R3,R5 where maker3=maker and model3<>model2 and model3<>model;
create view R7 as select * from R4,R6 where maker4=maker and (model4=model or model4=model2 or model=model3);
select distinct maker from R7;

+-------+
| maker |
+-------+
| A     |
| B     |
| D     |
| E     |
+-------+

数据文件

-- MySQL dump 10.13  Distrib 8.0.19, for Win64 (x86_64)
--
-- Host: localhost    Database: first_course_to_dbms
-- ------------------------------------------------------
-- Server version	8.0.18

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `laptop`
--

DROP TABLE IF EXISTS `laptop`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `laptop` (
  `model` int(11) DEFAULT NULL,
  `speed` double DEFAULT NULL,
  `ram` int(11) DEFAULT NULL,
  `hd` int(11) DEFAULT NULL,
  `screen` double DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `laptop`
--

LOCK TABLES `laptop` WRITE;
/*!40000 ALTER TABLE `laptop` DISABLE KEYS */;
INSERT INTO `laptop` VALUES (2001,2,2048,240,20.1,3673),(2002,1.73,1024,80,17,949),(2003,1.8,512,60,15.4,549),(2004,2,512,60,13.3,1150),(2005,2.16,1024,120,17,2500),(2006,2,2048,80,15.4,1700),(2007,1.83,1024,120,13.3,1429),(2008,1.6,1024,100,15.4,900),(2009,1.6,512,80,14.1,680),(2010,2,2048,160,15.4,2300);
/*!40000 ALTER TABLE `laptop` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pc`
--

DROP TABLE IF EXISTS `pc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pc` (
  `model` int(11) DEFAULT NULL,
  `speed` double DEFAULT NULL,
  `ram` int(11) DEFAULT NULL,
  `hd` int(11) DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pc`
--

LOCK TABLES `pc` WRITE;
/*!40000 ALTER TABLE `pc` DISABLE KEYS */;
INSERT INTO `pc` VALUES (1001,2.66,1024,250,2114),(1002,2.1,512,250,995),(1003,1.42,512,80,478),(1004,2.8,1024,250,649),(1005,3.2,512,250,630),(1006,3.2,1024,320,1049),(1007,2.2,1024,200,510),(1008,2.2,2048,250,770),(1009,2,1024,250,650),(1010,2.8,2048,300,770),(1011,1.86,2048,160,959),(1012,2.8,1024,160,649),(1013,3.06,512,80,529);
/*!40000 ALTER TABLE `pc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `printer`
--

DROP TABLE IF EXISTS `printer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `printer` (
  `model` int(11) DEFAULT NULL,
  `color` tinyint(1) DEFAULT NULL,
  `p_type` char(20) DEFAULT NULL,
  `price` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `printer`
--

LOCK TABLES `printer` WRITE;
/*!40000 ALTER TABLE `printer` DISABLE KEYS */;
INSERT INTO `printer` VALUES (3001,1,'ink-jet',99),(3002,0,'laser',239),(3003,1,'laser',899),(3004,1,'ink-jet',120),(3005,0,'laser',120),(3006,1,'ink-jet',100),(3007,1,'laser',200);
/*!40000 ALTER TABLE `printer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `product` (
  `maker` char(50) DEFAULT NULL,
  `model` int(11) NOT NULL,
  `p_type` char(20) DEFAULT NULL,
  PRIMARY KEY (`model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `product`
--

LOCK TABLES `product` WRITE;
/*!40000 ALTER TABLE `product` DISABLE KEYS */;
INSERT INTO `product` VALUES ('A',1001,'pc'),('A',1002,'pc'),('A',1003,'pc'),('B',1004,'pc'),('B',1005,'pc'),('B',1006,'pc'),('C',1007,'pc'),('D',1008,'pc'),('D',1009,'pc'),('D',1010,'pc'),('E',1011,'pc'),('E',1012,'pc'),('E',1013,'pc'),('E',2001,'laptop'),('E',2002,'laptop'),('E',2003,'laptop'),('A',2004,'laptop'),('A',2005,'laptop'),('A',2006,'laptop'),('B',2007,'laptop'),('F',2008,'laptop'),('F',2009,'laptop'),('G',2010,'laptop'),('E',3001,'printer'),('E',3002,'printer'),('E',3003,'printer'),('D',3004,'printer'),('D',3005,'printer'),('H',3006,'printer'),('H',3007,'printer');
/*!40000 ALTER TABLE `product` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-01-16 20:02:02