1

(a)

(i)

CREATE TABLE Customer(

customer-id Int NOT NULL IDENTITY (1001,1),

name VarChar(30) NOT NULL,

contact Char(8) NOT NULL,

email VarChar(25) NULL,

address VarChar(60) NOT NULL,

CONSTRAINT Customer_PK PRIMARY KEY (customer-id),

CONSTRAINT Valid_Contact CHECK

(contact LIKE ‘[6|8|9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9])

);

(ii)

CREATE TABLE Renovation(

renovation-id Char(6) NOT NULL,

renovation-site VarChar(30) NOT NULL,

start-date Date NOT NULL,

expected-end-date Date NOT NULL,

customer-id Int NOT NULL,

CONSTRAINT Renovation_PK PRIMARY KEY (renovation-id),

CONSTRAINT Renovation_AK1 UNIQUE(renovation-site, start-date),

CONSTRAINT Customer_FK FOREIGN KEY(customer-id),

REFERENCES Customer(customer-id)

ON UPDATE NO ACTION

ON DELETE NO ACTION

CONSTRAINT Valid_Reno_ID CHECK

(renovation-id LIKE ‘[C|R] [0-9] [0-9] [0-9] [0-9] [0-9]),

CONSTRAINT DateCheck CHECK

(expected-end-date > start-date)

);

(iii)

CREATE TABLE Work(

work-id Int NOT NULL IDENTITY (1001,1),

renovation-id Char(6) NOT NULL,

job-description VarChar(25) NOT NULL,

category VarChar(10) NOT NULL DEFAULT ‘general’,

cost Numeric(9,2) NOT NULL,

CONSTRAINT Work_PK PRIMARY KEY (work-id),

CONSTRAINT Renovation_FK FOREIGN KEY (renovation-id),

REFERENCES Renovation(renovation-id)

ON UPDATE NO ACTION

ON DELETE CASCADE

CONSTRAINT Valid_Category CHECK

(category IN (‘masonry’, ‘metalwork’, ‘carpentry’, ‘paintwork’, ‘plumbing’, ‘electrical’, ‘general’)),

CONSTRAINT CostCheck CHECK

((cost > 0) AND (cost < 1000000))

);

(b)

(i)

Renovation is the parent table and Work is the child