MySQL Lab2 答案与解析

一、table 创建部分：

CREATE TABLE Branch (
    branchNo CHAR(4),
    street VARCHAR(45) NOT NULL,
    city VARCHAR(45) NOT NULL,
    postcode VARCHAR(45) NOT NULL,
    PRIMARY KEY (branchNo)
);
/*alter table Branch modify street VARCHAR(45) NOT NULL;
alter table Branch modify city VARCHAR(45) NOT NULL;
alter table Branch modify postcode VARCHAR(45) NOT NULL;*/

CREATE TABLE Staff (
    staffNo VARCHAR(4),
    fName VARCHAR(45) NOT NULL,
    lName VARCHAR(45) NOT NULL,
    position VARCHAR(45) NOT NULL,
    sex VARCHAR(10) NOT NULL,
    DoB DATE NOT NULL,
    salary INT NOT NULL,
    branchNo CHAR(4),
    PRIMARY KEY (staffNo),
    FOREIGN KEY (branchNo)
        REFERENCES Branch (branchNo)
        ON UPDATE CASCADE ON DELETE SET NULL
);
-- drop table Staff;



CREATE TABLE PrivateOwner (
    ownerNo CHAR(4),
    fName VARCHAR(45) NOT NULL,
    lName VARCHAR(45) NOT NULL,
    address VARCHAR(45) NOT NULL,
    telNo VARCHAR(45) NOT NULL,
    PRIMARY KEY (ownerNo)
);

-- 更改表中的字段方法：
/*alter table PrivateOwner change owner ownerNo CHAR(4);
select *
from PrivateOwner;
*/
CREATE TABLE PropertyForRent (
    propertyNo VARCHAR(4),
    street VARCHAR(45) NOT NULL,
    city VARCHAR(45) NOT NULL,
    postcode VARCHAR(45) NOT NULL,
    type VARCHAR(45) NOT NULL,
    rooms INT NOT NULL,
    rent INT NOT NULL,
    ownerNo CHAR(4),
    staffNo VARCHAR(4),
    branchNo CHAR(4),
    PRIMARY KEY (propertyNo),
    FOREIGN KEY (ownerNo)
        REFERENCES PrivateOwner (ownerNo)
        ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (staffNo)
        REFERENCES Staff (staffNo)
        ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (branchNo)
        REFERENCES Branch (branchNo)
        ON UPDATE CASCADE ON DELETE SET NULL
);
-- drop table  PropertyForRent;
-- alter table PropertyForRent add  FOREIGN KEY (staffNo) REFERENCES Staff (staffNo);
-- alter table PropertyForRent add foreign key(ownerNo) references PrivateOwner(ownerNo);
-- drop table PropertyForRent;
  
CREATE TABLE Client (
    clientNo CHAR(4) NOT NULL,
    fName VARCHAR(45) NOT NULL,
    lName VARCHAR(45) NOT NULL,
    telNo VARCHAR(45) NOT NULL,
    preType VARCHAR(45) NOT NULL,
    maxRent INT NOT NULL,
    PRIMARY KEY (clientNo)
);

CREATE TABLE Registration (
    clientNo CHAR(4),
    branchNo CHAR(4),
    staffNo VARCHAR(4),
    dataJoined DATE NOT NULL,
    PRIMARY KEY (clientNo , branchNo , staffNo),
    FOREIGN KEY (staffNo)
        REFERENCES Staff (staffNo)
        ON UPDATE CASCADE,
    FOREIGN KEY (branchNo)
        REFERENCES Branch (branchNo)
        ON UPDATE CASCADE,
    FOREIGN KEY (clientNo)
        REFERENCES Client (clientNo)
        ON UPDATE CASCADE
);

CREATE TABLE Viewing (
    clientNo CHAR(4),
    propertyNo VARCHAR(4),
    viewDate DATE,
    comment VARCHAR(45),
    PRIMARY KEY (clientNo , propertyNo , viewDate),
    FOREIGN KEY (clientNo)
        REFERENCES Client (clientNo)
        ON UPDATE CASCADE,
    FOREIGN KEY (propertyNo)
        REFERENCES PropertyForRent (propertyNo)
        ON UPDATE CASCADE
);

二、插入数据

insert into Branch values('B005','22 Deer Rd','London','SW1 4EH');
insert into Branch values('B007','16 Argylly Rd','Aberdeen','AB2 3SU');
insert into Branch values('B003', '163 Main St', 'Glasgow', 'G11 9QX');
insert into Branch values('B004', '32 Manse Rd', 'Bristol', 'BS99 1NZ');
insert into Branch values('B002', '56 Clover St', 'London', 'NW10 6EU');

insert into Staff values('SL21','John','White','Manager','M','1945-10-1',30000,'B005');
insert into Staff values('SG37', 'Ann', 'Beech', 'Assistant', 'F', '1960-11-10', 12000, 'B003');
insert into Staff values('SG14', 'David', 'Ford', 'Supervisor', 'M', '1958-03-24', 18000, 'B003');
insert into Staff values('SA9', 'Mary', 'Howe', 'Assistant', 'F', '1970-02-19', 9000, 'B007');
insert into Staff values('SG5', 'Susan', 'Brand', 'Manager', 'F', '1940-06-03', 24000, 'B003');
insert into Staff values('SL41', 'Julie', 'Lee', 'Assistant', 'F', '1965-06-13', 9000, 'B005');

insert into PropertyForRent values ('PA14','16 Holhead','Aberden','AB7 5SU','House',6,650,'CO46','SA9','B007');
insert into PropertyForRent values('PL94', '6 Argyll St', 'London', 'NW2', 'Flat', 4, 400, 'CO87', 'SL41', 'B005');
insert into PropertyForRent values('PG4', '6 Lawrence St', 'Glasgow', 'G11 9QX', 'Flat', 3, 350, 'CO40', NULL, 'B003');
insert into PropertyForRent values ('PG36', '2 Manor Rd', 'Glasgow', 'G32 4QX', 'Flat', 3, 375, 'CO93', 'SG37', 'B003');
insert into PropertyForRent values('PG21', '18 Dale Rd', 'Glasgow', 'G12', 'House', 5, 600, 'CO87', 'SG37', 'B003');
insert into PropertyForRent values('PG16', '5 Novar Dr', 'Glasgow', 'G12 9AX', 'Flat', 4, 450, 'CO93', 'SG14', 'B003');

insert into PrivateOwner values ('CO46','Joe','Keogh','2 Fergus Dr,Aberdeen AB2 7SX','01224-861212');
insert into PrivateOwner values ('CO87', 'Carol', 'Farrel', '6 Achray St, Glasgow G32 9DX', '0141-357-7419');
insert into PrivateOwner values ('CO40', 'Tina', 'Murphy', '63 Well St, Glasgow G42', '0141-943-1728');
insert into PrivateOwner values ('CO93', 'Tony', 'Shaw', '12 Park Pl, Glasgow G4 0QR', '0141-225-7025');

insert into Client values ('CR76','John','Kay','0207-774-5632','Flat',425);
insert into Client values ('CR56', 'Aline', 'Stewart', '0141-848-1826', 'Flat', 350);
insert into Client values('CR74', 'Mike', 'Ritchie', '01475-392178', 'House', 750);
insert into Client values('CR62', 'Mary', 'Tregear', '01224-196720', 'Flat', 600);

insert into Viewing values('CR56','PA14','2004-05-24','too small');
insert into Viewing values('CR76', 'PG4', '2004-04-20', 'too remote');
insert into Viewing values('CR56', 'PG4', '2004-05-26', NULL);
insert into Viewing values('CR62', 'PA14', '2004-05-14', 'no dining room');
insert into Viewing values('CR56', 'PG36', '2004-04-28', NULL);

insert into Registration values('CR76','B005','Sl41','2004-1-2');
insert into Registration values('CR56', 'B003', 'SG37', '2003-04-11');
insert into Registration values('CR74', 'B003', 'SG37', '2002-11-16');
insert into Registration values('CR62', 'B007', 'SA9', '2003-03-07');

三、问题回答：（注意看注释）

-- question 2 try to violate the constraint on primary key:
insert into Registration values(null, 'B007', 'SA9', '2003-03-07');
/*
Error Code: 1048. Column 'clientNo' cannot be null
*/

DELETE FROM Branch 
WHERE
    branchNo = 'B005';
-- Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`lab2`.`registration`, CONSTRAINT `registration_ibfk_2` FOREIGN KEY (`branchNo`) REFERENCES `branch` (`branchNo`) ON UPDATE CASCADE)

-- question3-a-2 update tuples in the parent tuples
-- note: 需要先把安全模式关掉：
SET SQL_SAFE_UPDATES = 0;

UPDATE Staff 
SET 
    salary = salary * 1.1
WHERE
    branchNo IS NOT NULL;

-- 查看相关结果：
SELECT 
    *
FROM
    Staff;
UPDATE Branch 
SET 
    branchNo = 'B00X'
WHERE
    branchNo = 'B005';
SELECT 
    *
FROM
    Branch;
SELECT 
    *
FROM
    Registration;
SELECT 
    *
FROM
    PropertyForRent;
/* 发现这些entity里面的branchNo 都随之发生了更新*/

-- question 3-b-1 insert tuples in child table without a matching candidate key value in the parent table

insert into Staff values('SL25','John','White','Manager','M','1945-10-1',30000,'B005');
/*
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`lab2`.`staff`, 
CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`branchNo`) REFERENCES `branch` (`branchNo`) ON DELETE SET NULL ON UPDATE CASCADE)
*/

UPDATE Staff 
SET 
    branchNo = 'B005'
WHERE
    branchNo = 'B00X';

/* 一样的报错 是不能更新的*/

-- question 4 use alter table statement to add、delete a column in the table 
-- ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
alter table Branch add test varchar(45)  not null;
SELECT 
    *
FROM
    branch;
alter table Branch drop test ;

-- question5 update satement in MySQL
-- 5-a increase all mangers' salary by 10%
UPDATE Staff 
SET 
    salary = 0
WHERE
    position = 'manager';

SELECT 
    *
FROM
    Staff;

-- 5-b change Julie Lee's position to supervisor and a salary increase of 5%
UPDATE Staff 
SET 
    position = 'supervisor',
    salary = 10
WHERE
    fName = 'Julie' AND lName = 'Lee';

-- 5-c give an increase on rent by 2% on all properties in Glasgow
UPDATE PropertyForRent 
SET 
    rent = rent * 1.02
WHERE
    city = ' Glasgow';
    
select * from PropertyForRent ;