i got the following MySQL code but its not working when i like compile/execute it:
CREATE DATABASE IF NOT EXISTS KWEADB;
USE KWEADB;
CREATE TABLE IF NOT EXISTS staff_member (
staff# SMALLINT(4) UNSIGNED NOT NULL,
staff_name VARCHAR(30) NOT NULL,
service VARCHAR(30) NOT NULL,
PRIMARY KEY (staff#));
CREATE TABLE IF NOT EXISTS placement (
opening# SMALLINT(4) UNSIGNED NOT NULL,
start_date DATE,
end_date DATE,
total_bonds INT(6),
rent SMALLINT(4)'
PRIMARY KEY (opening#));
CREATE TABLE IF NOT EXISTS tenant (
tenant# SMALLINT(4) UNSIGNED NOT NULL,
placement# SMALLINT(4),
tenant_name VARCHAR(30) NOT NULL,
postal_address VARCHAR(75) NOT NULL,
D.O.B DATE,
gender ENUM('m','f'),
marital_status VARCHAR(,
#_of_people TINYINT(2),
status ENUM(‘placed’, ‘waiting’)
phone VARCHAR(10),
comments VARCHAR(100),
PRIMARY KEY (tenant#),
FOREIGN KEY (placement#) REFERENCES placement (placement#));
CREATE TABLE IF NOT EXISTS property (
property# SMALLINT(4) UNSIGNED NOT NULL,
type VARCHAR(15),
#_of_rooms TINYINT(2),
address VARCHAR(75),
comments VARCHAR(100),
PRIMARY KEY (property#));
CREATE TABLE IF NOT EXISTS owner (
owner# SMALLINT(4) UNSIGNED NOT NULL,
owner_name VARCHAR(30) NOT NULL,
owner_address VARCHAR(30) NOT NULL,
owner_phone VARCHAR(10),
PRIMARY KEY (owner#),
FOREIGN KEY (property#) REFERENCES property (property#));
CREATE TABLE IF NOT EXISTS possesion (
property# SMALLINT(4) UNSIGNED NOT NULL,
owner# SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY (property#, owner#),
FOREIGN KEY (property#) REFERENCES property (property#),
FOREIGN KEY (owner#) REFERENCES owner (owner#));
CREATE TABLE IF NOT EXISTS report (
report# SMALLINT(4) UNSIGNED NOT NULL,
owner# SMALLINT(4) UNSIGNED NOT NULL,
rent SMALLINT(4) UNSIGNED,
rent_end DATE,
service_fee INT(4) UNSIGNED,
inspection VARCHAR(100),
PRIMARY KEY (report#),
FOREIGN KEY (property#) REFERENCES property (property#),
FOREIGN KEY (owner#) REFERENCES owner (owner#));
CREATE TABLE IF NOT EXISTS waiting_list (
waiting# SMALLINT(4) UNSIGNED NOT NULL,
opening# SMALLINT(4) UNSIGNED NOT NULL,
start_date DATE,
property_type VARCHAR(15),
rent_offered SMALLINT(4) UNSIGNED,
PRIMARY KEY (waiting#),
FOREIGN KEY (opening#) REFERENCES placement (opening#));
CREATE TABLE IF NOT EXISTS future_tenant (
property# SMALLINT(4) UNSIGNED NOT NULL,
waiting# SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY (property#, waiting#),
FOREIGN KEY (property#) REFERENCES property (property#),
FOREIGN KEY (waiting#) REFERENCES placement (waiting#));
CREATE TABLE IF NOT EXISTS priority (
property# SMALLINT(4) UNSIGNED NOT NULL,
staff# SMALLINT(4) UNSIGNED NOT NULL,
PRIMARY KEY (property#, staff#),
FOREIGN KEY (property#) REFERENCES property (property#),
FOREIGN KEY (staff#) REFERENCES staff_member (staff#));
Can someone help with it?
THANKS
