Here are the directions I need only the SQL code not the database itself: 1. Bas

Question

Here are the directions I need only the SQL code not the database itself:

1. Basic CREATE TABLE Statement Requirements Use the table descriptions in the Intercollegiate Database background document. To ensure uniqueness, add the prefix, “IAD” to each table name. For example, “EventRequest” should be “IADEventRequest”. For primary key fields (CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo), use the VARCHAR data type with length 8. For consistency, corresponding foreign keys (such as EventRequest.CustNo) should also be the same data type and length. • For the columns involving only dates use the DATE data type. The EventPlanLine.TimeStart and EventPlanLine.TimeEnd columns will store both date and time data so you should use the DATETIME data type. • Use CHAR(1) for the Customer.Internal column.

2. Constraints After writing the basic CREATE TABLE statements, modify the statements with constraints and default clauses. You should specify a meaningful name for each CONSTRAINT clause and end with an appropriate suffix. • For each primary key, you should specify a PRIMARY KEY constraint clause. For single column primary keys (CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo), only one field is in parentheses. For multiple column primary keys (combination of PlanNo and LineNo), all relevant keys are in parentheses. • For each foreign key, you should specify a FOREIGN KEY constraint clause. The constraint clauses should be separate. Specify the ON DELETE CASCADE clause for the foreign key constraints supporting the relationships from EventPlan to EventPlanLine and EventRequest to EventPlan. (Database Creation) Define NOT NULL constraint clauses for all columns except eventplan.empno, EventRequest.DateAuth, EventRequest.BudNo, and EventPlan.Notes. Make sure that you define NOT NULL constraints for the PK of each table. • Set the default value using the DEFAULT keyword as follows: “Pending” for EventRequest.Status, “Y” for Customer.Internal, “80217” for Customer.Zip, and today’s date (use the GETDATE() function) for EventRequest.DateReq. You need to specify the DEFAULT keyword before the CONSTRAINT keyword for column definitions containing inline constraint clauses. String constants must be specified inside single quotes. You must use straight quotes not smart quotes. Use an editor such as Notepad or Notepad++ to ensure no spurious characters are included. • Define a named CHECK constraint to restrict the eventrequest.status column to have a value of “Pending”, “Denied”, or “Approved”. See CHECK constraint examples on textbook page 654. You should use the IN operator in this constraint. • Define named CHECK constraints to ensure that the resource.rate and eventrequest.estaudience are greater than 0. • Define a named CHECK constraint involving EventRequest.DateAuth and EventRequest.DateReq. Date authorized should be larger (chronologically later) than date requested. The tricky part is that DateAuth can have null values. For example, when an event is initially requested, the authorization date is not known. To receive credit for this constraint, your rule should allow a row with a null (blank) value for DateAuth. When the DateAuth value is not null, it should be larger (chronologically later) than the DateReq value. • Define a named CHECK constraint involving EventPlanLine.TimeStart and EventPlanLineTimeEnd. The start time should be smaller (chronologically before) than the end time.

3. Loading Data I will provide a text file containing SQL INSERT statements for loading the data. You need to create the tables before inserting rows in each table. You need to insert rows in parent tablesbefore child tables that reference parent tables. The INSERT statements in the file are in a proper order for loading into the tables.

- 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

Hi!

-- Table 'IADCustomer'

-- -----------------------------------------------------

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 ,

)

-- -----------------------------------------------------

-- Table '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

)

-- -----------------------------------------------------

-- Table 'IADFacility'

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS 'IADFacility' (

'facno' VARCHAR(8) NOT NULL ,

'facname' VARCHAR(45) NULL

)

-- -----------------------------------------------------

-- Table 'IADLocation'

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS 'IADLocation' (

'locno' VARCHAR(8) NOT NULL ,

'facno' VARCHAR(8) NULL ,

'locname' VARCHAR(45) NULL

)

-- -----------------------------------------------------

-- Table 'IADResource'

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS 'IADResource' (

'resno' VARCHAR(8) NOT NULL ,

'resname' VARCHAR(45) NULL ,

'rate' VARCHAR(45) NULL

)

-- -----------------------------------------------------

-- Table 'IADEventRequest'

-- -----------------------------------------------------

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

)

-- -----------------------------------------------------

-- Table 'IADEventPlan'

-- -----------------------------------------------------

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

)

-- -----------------------------------------------------

-- Table 'IADPlanLine'

-- -----------------------------------------------------

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

)

  

  

Answer 2

  

-- -----------------------------------------------------

-- Table 'IADCustomer'

-- -----------------------------------------------------

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;

-- -----------------------------------------------------

-- Table 'IADEmployee'

-- -----------------------------------------------------

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;

-- -----------------------------------------------------

-- Table 'IADFacility'

-- -----------------------------------------------------

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;

-- -----------------------------------------------------

-- Table 'IADLocation'

-- -----------------------------------------------------

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;

-- -----------------------------------------------------

-- Table 'IADResource'

-- -----------------------------------------------------

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)

)

-- -----------------------------------------------------

-- Table 'IADEventRequest'

-- -----------------------------------------------------

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')

)

-- -----------------------------------------------------

-- Table 'IADEventPlan'

-- -----------------------------------------------------

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;

-- -----------------------------------------------------

-- Table 'IADPlanLine'

-- -----------------------------------------------------

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') <To_CHAR(timeend,'YYYY-MM-DD')

)

Thanks

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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