Basic CREATE TABLE Statement Requirements You should use the table descriptions in the Intercollegiate Database backgrou

[answerhappygod]

Basic CREATE TABLE Statement Requirements You should use thetable descriptions in the Intercollegiate Database backgrounddocument. You must use the same table and column names as specifiedin the background document. Here is some advice about data typeselections. 1. You should use standard SQL data types specified inthe notes except for using VARCHAR2 (an Oracle data type) insteadof VARCHAR for columns containing varying length character strings.For MySQL and PostgreSQL, you should use VARCHAR for variablelength strings. 2. For primary key fields (CustNo, LocNo, EventNo,PlanNo, EmpNo, ResNo, and FacNo), use the VARCHAR (or VARCHAR2 inOracle) data type with length 8. For consistency, correspondingforeign keys (such as EventRequest.CustNo) should also be the samedata type and length. 3. For Oracle, you should use the DATE datatype for the columns involving dates or times. TheEventPlanLine.TimeStart and EventPlanLine.TimeEnd columns willstore both date and time data so you should use the DATE data type.For MySQL, you should use the DATE data type for columns with justdate details (date columns in the EventRequest and EventPlantables) and DATETIME for columns with date and time details (timecolumns in the EventPlanLine table). For PostgreSQL, you should usethe DATE data type for columns with just date details (date columnsin the EventRequest and EventPlan tables) and TIMESTAMP for columnswith date and time details (time columns in the EventPlanLinetable). 4. Use CHAR(1) for the Customer.Internal column as Oracledoes not provide a BOOLEAN data type. MySQL has the Boolean datatype and PostgreSQL has the BIT(1) data type, but I suggest thatyou use CHAR(1) instead. 2. Constraints After writing the basicCREATE TABLE statements, you should modify the statements withconstraints. The CONSTRAINT clauses can be either inline in acolumn definition or separate after column definitions except wherenoted. You should specify a meaningful name for each CONSTRAINTclause. • For each primary key, you should specify a PRIMARY KEYconstraint clause. For single column primary keys (CustNo, LocNo,EventNo, PlanNo, EmpNo, ResNo, and FacNo), the constraint clausecan be inline or external. For multiple column primary keys(combination of PlanNo and LineNo), the CONSTRAINT clause must beexternal. • For each foreign key, you should specify a FOREIGN KEYconstraint clause. The constraint clauses can be inline orseparate. • Define NOT NULL constraints for all columns excepteventplan.empno, EventRequest.DateAuth, EventRequest.BudNo, andEventPlan.Notes. Make sure that you define NOT NULL constraints forthe PK of each table. Because of MySQL syntax limitations for NOTNULL constraints (inline with no constraint name and no CONSTRAINTkeyword), you should define inline NOT NULL constraints. • Define anamed CHECK constraint to restrict the eventrequest.status columnto have a value of “Pending”, “Denied”, or “Approved”. You can usethe IN operator in this constraint. In MySQL, the syntax does notallow the CONSTRAINT keyword and a constraint name for CHECKconstraints. You should use the CHECK keyword followed thecondition enclosed in parentheses. • Define named CHECK constraintsto ensure that the resource.rate and eventrequest.estaudience aregreater than 0. In MySQL, you cannot use a constraint name and theCONSTRAINT keyword for CHECK constraints. In MySQL, the syntax doesnot allow the CONSTRAINT keyword and a constraint name for CHECKconstraints. You should use the CHECK keyword followed thecondition enclosed in parentheses. • Define a named CHECKconstraint involving EventPlanLine.TimeStart andEventPlanLineTimeEnd. The start time should be smaller(chronologically before) than the end time. This CHECK constraintmust be external because it involves two columns. In MySQL, thesyntax does not allow the CONSTRAINT keyword and a constraint namefor CHECK constraints. You should use the CHECK keyword followedthe condition enclosed in parentheses.
. Initial CREATE TABLE Statements
To facilitate your work, you can use the CREATE TABLE statementsyou created in the practice assignment in module 03 for theCustomer, Facility, and Location tables. Thus, you only need towrite CREATE TABLE statements for the remaining five tables(ResourceTbl, Employee, EventRequest, EventPlan, andEventPlanLine). You still need to execute the CREATE TABLEstatements to create all of the tables and the INSERT statements topopulate all tables.