《数据库系统设计、实现与管理（基础篇）》 DreamHome租赁数据库

CREATE TABLE IF NOT EXISTS Branch (
   branchNo VARCHAR(4) NOT NULL,
	 street VARCHAR(40) NOT NULL,
	 city VARCHAR(20) NOT NULL,
	 postcode VARCHAR(10) NOT NULL,
	 PRIMARY KEY (branchNo)
)

INSERT INTO Branch (branchNo, street, city, postcode)
VALUES ("B005", "22 Deer Rd", "London", "SW1 4EH"),
			 ("B007", "16 Argyll St", "Aberdeen", "AB2 3SU"),
			 ("B003", "163 Main St", "Glasgow", "G11 9QX"),
			 ("B004", "32 Manse Rd", "Bristol", "BS99 1NZ"),
			 ("B002", "56 Clover Dr", "London", "NW10 6EU")

CREATE TABLE IF NOT EXISTS Staff (
   staffNo VARCHAR(4) NOT NULL,
	 fName VARCHAR(20) NOT NULL,
	 lName VARCHAR(20) NOT NULL,
	 position VARCHAR(20) NOT NULL, 
	 sex VARCHAR(1) NOT NULL, 
	 DOB DATE NOT NULL,
	 salary INT UNSIGNED,
	 branchNo VARCHAR(4),
	 PRIMARY KEY (staffNo)
)

INSERT INTO Staff (staffNo, fName, lName, position, sex, DOB, salary, branchNo)
VALUES ("SL21", "John", "White", "Manager", "M", '1945-10-01', 30000, "B005"),
			 ("SG37", "Ann", "Beech", "Assistant", "F", '1960-11-10', 12000, "B003"),
			 ("SG14", "David", "Ford", "Supervisor", "M", '1958-03-24', 18000, "B003"),
			 ("SA9", "Mary", "Howe", "Assistant", "F", '1970-02-19', 9000, "B007"),
			 ("SG5", "Susan", "Brand", "Manager", "F", '1940-06-03', 24000, "B003"),
			 ("SL41", "Julie", "Lee", "Assistant", "F", '1965-06-13', 9000, "B005")

CREATE TABLE IF NOT EXISTS PropertyForRent (
   propertyNo VARCHAR(4) NOT NULL,
	 street VARCHAR(40) NOT NULL,
	 city VARCHAR(20) NOT NULL,
	 postcode VARCHAR(10) NOT NULL,
	 type VARCHAR(10) NOT NULL,
	 rooms TINYINT UNSIGNED NOT NULL,
	 rent INT UNSIGNED NOT NULL,
	 ownerNo VARCHAR(4) NOT NULL,
	 staffNo VARCHAR(4),
	 branchNo VARCHAR(4) NOT NULL,
	 PRIMARY KEY (propertyNo)
)

INSERT INTO PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)
VALUES ("PA14", "16 Holhead", "Aberdeen", "AB7 5SU", "House", 6, 650, "CO46", "SA9", "B007"),
			 ("PL94", "6 Argyll St", "London", "NW2", "Flat", 4, 400, "CO87", "SL41", "B005"),
			 ("PG4", "6 Lawrence St", "Glasgow", "G11 9QX", "Flat", 3, 350, "CO40", NULL, "B003"),
			 ("PG36", "2 Manor Rd", "Glasgow", "G32 4QX", "Flat", 3, 375, "CO93", "SG37", "B003"),
			 ("PG21", "18 Dale Rd", "Glasgow", "G12", "House", 5, 600, "CO87", "SG37", "B003"),
			 ("PG16", "5 Navor Dr", "Glasgow", "G12 9AX", "Flat", 4, 450, "CO93", "SG14", "B003")

CREATE TABLE IF NOT EXISTS Client (
   clientNo VARCHAR(4) NOT NULL,
	 fName VARCHAR(20) NOT NULL,
	 lName VARCHAR(20) NOT NULL,
	 telNo VARCHAR(20) NOT NULL,
	 prefType VARCHAR(10) NOT NULL,
	 maxRent INT UNSIGNED NOT NULL,
	 eMail VARCHAR(40) NOT NULL,
	 PRIMARY KEY (clientNo)
)

INSERT INTO Client (clientNo, fName, lName, telNo, prefType, maxRent, eMail)
VALUES ("CR76", "John", "Kay", "0207-774-5632", "Flat", 425, "john.kay@gmail.com" ),
			 ("CR56", "Aline", "Stewart", "0104-848-1825", "Flat", 350, "astewart@hotmail.com" ),
			 ("CR74", "Mike", "RitChie", "01475-392178", "House", 750, "mritchie01@yahoo.co.uk" ),
			 ("CR62", "Mary", "Tregear", "01224-196720", "Flat", 600, "maryt@hotmail.co.uk" )

CREATE TABLE IF NOT EXISTS PrivateOwner (
   ownerNo VARCHAR(4) NOT NULL,
	 fName VARCHAR(20) NOT NULL,
	 lName VARCHAR(20) NOT NULL,
	 address VARCHAR(40) NOT NULL,
	 telNo VARCHAR(20) NOT NULL,
	 eMail VARCHAR(40) NOT NULL,
	 password VARCHAR(20) NOT NULL,
	 PRIMARY KEY (ownerNo)
)

INSERT INTO PrivateOwner ()
VALUES ("CO46", "Joe", "Keogh", "2 Fergus Dr, Aberdeen AB2 7SX", "01224-861212", "jkeogh@lhh.com", "12345678" ),
			 ("CO87", "Carol", "Farrel", "6 Achray St, Glasgow G32 9DX", "0141-357-7419", "cfarrel@gmail.com", "12345678" ),
			 ("CO40", "Tina", "Murphy", "63 Well St, Glasgow G42", "0141-943-1728", "tinam@hotmail.com", "12345678" ),
			 ("CO93", "Tony", "Shaw", "12 Park Pl, Glasgow G4 0Qr", "0141-225-7025", "tony.shaw@ark.com", "12345678" )

CREATE TABLE IF NOT EXISTS Viewing (
	 clientNo VARCHAR(4) NOT NULL,
	 propertyNo VARCHAR(4) NOT NULL,
	 viewDate DATE NOT NULL,
	 comment VARCHAR(40)
)

INSERT INTO Viewing (clientNo, propertyNo, viewDate, comment)
VALUES ("CR56", "PA14", '2013-03-24', "too small" ),
			 ("CR76", "PG4", '2013-04-20', "too remote" ),
			 ("CR56", "PG4", '2013-05-26', NULL ),
			 ("CR62", "PA14", '2013-05-14', "no dining room" ),
			 ("CR56", "PG36", '2013-04-28', NULL )

CREATE TABLE IF NOT EXISTS Registration (
	 clientNo VARCHAR(4) NOT NULL,
	 branchNo VARCHAR(4) NOT NULL,
	 staffNo VARCHAR(4) NOT NULL,
	 dateJoined DATE NOT NULL
)

INSERT INTO Registration (clientNo, branchNo, staffNo, dateJoined)
VALUES ("CR76", "B005", "SL41", '2013-01-02' ),
			 ("CR56", "B003", "SG37", '2012-04-11' ),
			 ("CR74", "B003", "SG37", '2011-11-16' ),
			 ("CR62", "B007", "SA9", '2012-05-07' )