I created an SQL table and I can\'t run it. Can anybody check it for errors? Tha

Question

I created an SQL table and I can't run it. Can anybody check it for errors? Thank you.

CREATE TABLE PROPERTY(
   Poperty_ID        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Address        VARCHAR(50) NOT NULL,
P_Type            VARCHAR(20),
No_guests        DECIMAL(1,0),
No_bedrooms    DECIMAL(1,0),
No_bathrooms    DECIMAL(1,0),
No_beds        DECIMAL(1,0),
Owner_ID        SMALLINT UNSIGNED,
CONSTRAINT OD_FK FOREIGN KEY (Owner_ID)
   REFERENCES Owner (Owner_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);
  
CREATE TABLE OWNER(
   Owner_ID        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Name            VARCHAR(50) NOT NULL,
Residence        VARCHAR(50),
Phone_no        CHAR(10) UNIQUE,
Referral_ID    SMALLINT UNSIGNED,
CONSTRAINT RI_FK FOREIGN KEY (Referral_ID)
   REFERENCES Owner (Owner_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);
  
CREATE TABLE AMENITIES(
   Amenities_ID TINYINT UNSIGNED PRIMARY KEY,
Internet    ENUM (‘Y’, ‘N’),
Cable        ENUM (‘Y’, ‘N’),
Washer        ENUM (‘Y’, ‘N’),
Dryer        ENUM (‘Y’, ‘N’),
Pool        ENUM (‘Y’, ‘N’)
);
  
CREATE TABLE PROP_AMENITIES(
   Prop_Amen_ID    TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Property_ID    SMALLINT,
Amenities_ID    TINYINT,
CONSTRAINT PID_FK FOREIGN KEY (Property_ID)
   REFERENCES Property (Property_ID)
ON DELETE SET NULL ON UPDATE SET NULL,
   CONSTRAINT AID_FK FOREIGN KEY (Amenities_ID)
REFERENCES Amenities (Amenities_ID)
ON DELETE SET NULL ON UPDATE SET NULL

);
  
CREATE TABLE HOUSEKEEPING(
   Housekeeping_ID TINYINT UNSIGNED PRIMARY KEY,
Description    TEXT,
Date            DATE NOT NULL,
Cost            DECIMAL(5,2),
Property_ID    SMALLINT UNSIGNED,
CONSTRAINT PID_FK FOREIGN KEY (Property_ID)
   REFERENCES Property (Property_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);

CREATE TABLE MAINTENANCE(
   Maintenance_ID    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Description    TEXT,
Date            DATE NOT NULL,
Cost            DECIMAL(6,2),
Property_ID    SMALLINT UNSIGNED,
CONSTRAINT PID_FK FOREIGN KEY (Property_ID)
   REFERENCES Property (Property_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);

CREATE TABLE PRICES(      
   Price_ID    TINYINT UNSIGNED PRIMARY KEY,
Start_date    DATE NOT NULL,
End_date    DATE,
Price        DECIMAL(6,2),
Property_ID SMALLINT,
CONSTRAINT PID_FK FOREIGN KEY (Property_ID)
   REFERENCES Property (Property_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);

CREATE TABLE CUSTOMERS(
   Customer_ID    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   Name            VARCHAR(50) NOT NULL,
   Address        VARCHAR(50),
   Phone_no        CHAR(10) UNIQUE
);

CREATE TABLE PROP_CUSTOMERS(
   Prop_Cust_ID    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   Property_ID    SMALLINT UNSIGNED,
Customer_ID       SMALLINT UNSIGNED,
Dates            DATE NOT NULL,
No_guests        TINYINT UNSIGNED,
CONSTRAINT PCID_FK FOREIGN KEY (Property_ID)
REFERENCES Property (Property_ID)
ON DELETE SET NULL ON UPDATE SET NULL,
   CONSTRAINT PCID_FK FOREIGN KEY (Customer_ID)
REFERENCES Customer (Customer_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);

CREATE TABLE PAYMENTS(
   Payment_ID    SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Amount        DECIMAL(6,2) NOT NULL,
Date        DATE,
Customer_ID SMALLINT
CONSTRAINT CID_FK FOREIGN KEY (Customer_ID)
REFERENCES Customer (Customer_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);

CREATE TABLE REVIEWS(
   Review_ID   SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
Date       DATE NOT NULL,
Ratings       DECIMAL(1,0),
Comments    TEXT,
Property_ID   SMALLINT UNSIGNED,
CONSTRAINT PID_FK FOREIGN KEY (Property_ID)
   REFERENCES Property (Property_ID)
ON DELETE SET NULL ON UPDATE SET NULL
);

Explanation / Answer

CREATE TABLE AMENITIES(
     Amenities_ID TINYINT UNSIGNED PRIMARY KEY,
     Internet     ENUM (‘Y’, ‘N’),
     Cable         ENUM (‘Y’, ‘N’),
     Washer         ENUM (‘Y’, ‘N’),
     Dryer         ENUM (‘Y’, ‘N’),
     Pool         ENUM (‘Y’, ‘N’)
);

ENUM datatype present in MySQL NOT in SQL.Use Other datatype in this table.To excute the SQL DDL Query.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.