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.

Explanation / Answer

NOTE: The Question is not clear. How many tables are there and what are the columns of the tables, relationship between the tables etc are not provided.

Hence I will give the answer with the requirements given and provide the syntax.

Answer is provided step by step.

Given:

EventRequest=IADEventRequest

primary key fields=CustNo,LocNo,EventNo,PlanNo,EmpNo,ResNo,FacNo

Datatype for primary key fields=VARCHAR(8)

Foreign Keys(EventRequest.CustNo) and datatype= VARCHAR(8)

EventPlanLine.TimeStart,EventPlanLine.TimeEnd = DATETIME

Customer.Internal= CHAR(1)

1.Basic Create table statement.

syntax:

CREATE TABLE table_name(

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

....

);

CREATE TABLE IADEventRequest (

CustNo VARCHAR(8),

LocNo VARCHAR(8),

EventNo VARCHAR(8),

PlanNo VARCHAR(8),

EmpNo VARCHAR(8),

ResNo VARCHAR(8),

FacNo VARCHAR(8),

);

2. CREATE TABLE IADEventRequest (

CustNo VARCHAR(8) NOT NULL PRIMARY KEY,

LocNo VARCHAR(8) NOT NULL PRIMARY KEY,

EventNo VARCHAR(8) NOT NULL PRIMARY KEY,

PlanNo VARCHAR(8) NOT NULL PRIMARY KEY,

EmpNo VARCHAR(8) NOT NULL PRIMARY KEY,

ResNo VARCHAR(8)NOT NULL PRIMARY KEY,

FacNo VARCHAR(8) NOT NULL PRIMARY KEY,

CONSTRAINT PK_IADEventRequest PRIMARY KEY (PlanNo,LineNo)

);

Foreign Key: Relationship between two tables should be provided to proceed further with foreign key.

NOTE: Revert back with the proper list of tables present and the columns of the corresponding tables and their relationship to proceed further. Only then the question can be answered completely.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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