Here is my SQL code so far I need the SQL INSERT statements to mirror the data p

Question

Here is my SQL code so far I need the SQL INSERT statements to mirror the data pictures above.

CREATE TABLE IF NOT EXISTS 'IADCustomer' (
'custno' VARCHAR(8) NOT NULL ,
'custname' VARCHAR(45) NULL ,
'address' VARCHAR(90) NULL ,
'Internal' CHAR(1) NULL ,
'contact' VARCHAR(45) NULL ,
'phone' VARCHAR(45) NULL ,
'city' VARCHAR(45) NULL ,
'state' VARCHAR(45) NULL ,
'zip' VARCHAR(45) NULL ,)

CREATE TABLE IF NOT EXISTS 'IADEmployee' (
'empno' VARCHAR(8) NOT NULL ,
'empname' VARCHAR(45) NULL ,
'department' VARCHAR(45) NULL ,
'email' VARCHAR(45) NULL ,
'phone' VARCHAR(45) NULL)

CREATE TABLE IF NOT EXISTS 'IADFacility' (
'facno' VARCHAR(8) NOT NULL ,
'facname' VARCHAR(45) NULL)

CREATE TABLE IF NOT EXISTS 'IADLocation' (
'locno' VARCHAR(8) NOT NULL ,
'facno' VARCHAR(8) NULL ,
'locname' VARCHAR(45) NULL)

CREATE TABLE IF NOT EXISTS 'IADResource' (
'resno' VARCHAR(8) NOT NULL ,
'resname' VARCHAR(45) NULL ,
'rate' VARCHAR(45) NULL)

CREATE TABLE IF NOT EXISTS 'IADEventRequest' (
'eventno' VARCHAR(8) NOT NULL ,
'dateheld' DATE NULL ,
'datereq' DATE NULL ,
'facno' VARCHAR(8) NULL ,
'custno' VARCHAR(8) NULL ,
'dateauth' DATE NULL ,
'status' VARCHAR(45) NULL ,
'estcost' VARCHAR(45) NULL ,
'estaudience' VARCHAR(45) NULL ,
'budno' VARCHAR(45) NULL)

CREATE TABLE IF NOT EXISTS 'IADEventPlan' (
'planno' VARCHAR(8) NOT NULL ,
'eventno' VARCHAR(8) NULL ,
'workdate' DATE NULL ,
'notes' VARCHAR(90) NULL ,
'activity' VARCHAR(45) NULL ,
'empno' VARCHAR(8) NULL)

CREATE TABLE IF NOT EXISTS 'IADPlanLine' (
'planno' VARCHAR(8) NOT NULL ,
'linenumber' VARCHAR(45) NULL ,
'timestart' DATETIME NULL ,
'timeend' DATETIME NULL ,
'number' VARCHAR(45) NULL ,
'locno' VARCHAR(8) NULL ,
'resno' VARCHAR(8) NULL)

DROP TABLE IF EXISTS 'IADCustomer' ;
CREATE TABLE IF NOT EXISTS 'IADCustomer' (
'custname' VARCHAR(45) NULL ,
'custno' VARCHAR(8) NOT NULL ,
'address' VARCHAR(90) NULL ,
'Internal' CHAR(1) NULL DEFAULT 'Y' ,
'contact' VARCHAR(45) NULL ,
'phone' VARCHAR(45) NULL ,
'city' VARCHAR(45) NULL ,
'state' VARCHAR(45) NULL ,
'zip' VARCHAR(45) NULL DEFAULT '80217' ,
PRIMARY KEY (`custno`) )
ENGINE = InnoDB;

DROP TABLE IF EXISTS 'IADEmployee' ;
CREATE TABLE IF NOT EXISTS 'IADEmployee' (
'empno' VARCHAR(8) NOT NULL ,
'empname' VARCHAR(45) NULL ,
'department' VARCHAR(45) NULL ,
'email' VARCHAR(45) NULL ,
'phone' VARCHAR(45) NULL ,
PRIMARY KEY (`empno`) )
ENGINE = InnoDB;

DROP TABLE IF EXISTS 'IADFacility' ;
CREATE TABLE IF NOT EXISTS 'IADFacility' (
'facno' VARCHAR(8) NOT NULL ,
'facname' VARCHAR(45) NULL ,
PRIMARY KEY (`facno`) )
ENGINE = InnoDB;

DROP TABLE IF EXISTS 'IADLocation' ;
CREATE TABLE IF NOT EXISTS 'IADLocation' (
'locno' VARCHAR(8) NOT NULL ,
'facno' VARCHAR(8) NULL ,
'locname' VARCHAR(45) NULL ,
PRIMARY KEY (`locno`) ,
CONSTRAINT 'facility'
FOREIGN KEY (`facno' )
REFERENCES 'IADFacility' (`facno' )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

DROP TABLE IF EXISTS 'IADResource' ;
CREATE TABLE IF NOT EXISTS 'IADResource' (
'resno' VARCHAR(8) NOT NULL ,
'resname' VARCHAR(45) NULL ,
'rate' VARCHAR(45) NULL ,
PRIMARY KEY (`resno`),
CONSTRAINT rate CHECK (rate>0))

DROP TABLE IF EXISTS 'IADEventRequest' ;
CREATE TABLE IF NOT EXISTS 'IADEventRequest' (
'eventno' VARCHAR(8) NOT NULL ,
'dateheld' DATE NOT NULL ,
'datereq' DATE NOT NULL DEFAULT GETDATE() ,
'facno' VARCHAR(8) NOT NULL ,
'custno' VARCHAR(8) NOT NULL ,
'dateauth' DATE NULL ,
'status' VARCHAR(45) NOT NULL DEFAULT 'Pending' ,
'estcost' VARCHAR(45) NOT NULL ,
'estaudience' VARCHAR(45) NOT NULL ,
'budno' VARCHAR(45) NULL ,
PRIMARY KEY (`eventno`) ,
CONSTRAINT 'cust'
FOREIGN KEY (`custno' )
REFERENCES 'IADCustomer' (`custno' )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'facilityeventrequest'
FOREIGN KEY (`facno' )
REFERENCES 'IADFacility' (`facno' )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
CONSTRAINT STATUS_CHK1 CHECK
(status IN ('PENDING', 'APPROVED', 'DENIED'))
CONSTRAINT estaudience CHECK (estaudience>0),
CONSTRAINT reasonable_date CHECK(
TO_CHAR(dateauth, 'YYYY-MM-DD') >To_CHAR(datereq,'YYYY-MM-DD'))

DROP TABLE IF EXISTS 'IADEventPlan' ;
CREATE TABLE IF NOT EXISTS 'IADEventPlan' (
'planno' VARCHAR(8) NOT NULL ,
'eventno' VARCHAR(8) NULL ,
'workdate' DATE NOT NULL ,
'notes' VARCHAR(90) NULL ,
'activity' VARCHAR(45) NOT NULL ,
'empno' VARCHAR(8) NOT NULL ,
PRIMARY KEY (`planno`) ,
CONSTRAINT 'eventrequest'
FOREIGN KEY (`eventno' )
REFERENCES 'IADEventRequest' (`eventno' )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT 'employeenumber'
FOREIGN KEY (`empno' )
REFERENCES 'IADEmployee' (`empno' )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

DROP TABLE IF EXISTS 'IADPlanLine' ;
CREATE TABLE IF NOT EXISTS 'IADPlanLine' (
'planno' VARCHAR(8) NOT NULL ,
'linenumber' VARCHAR(45) NOT NULL ,
'timestart' DATETIME NULL ,
'timeend' DATETIME NULL ,
'number' VARCHAR(45) NULL ,
'locno' VARCHAR(8) NULL ,
'resno' VARCHAR(8) NULL ,
PRIMARY KEY (`planno`, 'linenumber`) ,
CONSTRAINT 'eventplanline'
FOREIGN KEY (`planno' )
REFERENCES 'IADEventPlan' (`planno' )
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT 'resource'
FOREIGN KEY (`resno' )
REFERENCES 'IADResource' (`resno' )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'location'
FOREIGN KEY (`locno' )
REFERENCES 'IADLocation' (`locno' )
ON DELETE NO ACTION
ON UPDATE NO ACTION),
CONSTRAINT reasonable_date CHECK(
TO_CHAR(timestart, 'YYYY-MM-DD')

- Relationships EventPlan Customer custmo custname address Internal contact phone city state zip EventRequest eventno workdate notes dateheld oo datereq facno custno dateauth status estcost estaudience budno oo activity empno Employee empname department email phone EventPlanLine neno timestart timeend resource resno resname rate Location Facility facno facname oo number facno locname locno resno

Explanation / Answer

Please find the inserts below:-

INSERT INTO IADCustomer VALUES('C100','FootBall','Box 352200', 'Yes', 'Marv Manager', '6857100', 'Boulder', 'CO', '80309');
INSERT INTO IADCustomer VALUES('C101','Mens BasketBall','Box 352200', 'Yes', 'Sally Supervisor', '5431700', 'Boulder', 'CO', '80309');

insert into IADEmployee values ('E100', 'Chuck Coordinator', 'Administration', 'chuck@colarodo.edu', '3-111');
insert into IADEmployee values ('E101', 'Mary Manager', 'Football', 'mary@colarodo.edu', '5-111');

INSERT INTO IADFacility VALUES ('F100', 'Football Stadium');
INSERT INTO IADFacility VALUES ('F101', 'BasketBall Arena');

INSERT INTO IADLocation VALUES ('L100', 'F100', 'Locker Room');
INSERT INTO IADLocation VALUES ('L101', 'F100', 'Plaza');

INSERT INTO IADResource VALUES ('R100', 'Attendant', '$10.00');
INSERT INTO IADResource VALUES ('R101', 'Police', '$15.00');

COMMIT;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---