The graded problems in Module 3 provide experience with the CREATE TABLE statement. You should execute the statements using either Oracle, PostgreSQL, or MySQL.

To facilitate grading, please number the SQL statements and format them neatly. You need to show the result tables. Indicate in the beginning of your document if you used Oracle, PostgreSQL, or MySQL.

If you use Oracle, you will need to use the Oracle SQL Developer to connect to an Oracle server. If you use MySQL, you can use the MySQL Workbench to connect to a MySQL server. If you use PostgreSQL, you can use pgAdmin client to connect to a PostgreSQL server.

1. Basic CREATE TABLE Statement Requirements

You should use the table descriptions in the Intercollegiate Database background document. You must use the same table and column names as specified in the background document. Here is some advice about data type selections.

You should use standard SQL data types specified in the notes except for using VARCHAR2 (an Oracle data type) instead of VARCHAR for columns containing varying length character strings. For MySQL and PostgreSQL, you should use VARCHAR for variable length strings.

For primary key fields (CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo), use the VARCHAR (or VARCHAR2 in Oracle) data type with length 8. For consistency, corresponding foreign keys (such as EventRequest.CustNo) should also be the same data type and length.

For Oracle, you should use the DATE data type for the columns involving dates or times. The EventPlanLine.TimeStart and EventPlanLine.TimeEnd columns will store 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 just date details (date columns in the EventRequest and EventPlan tables) and DATETIME for columns with date and time details (time columns in the EventPlanLine table). For PostgreSQL, you should use the DATE data type for columns with just date details (date columns in the EventRequest and EventPlan tables) and TIMESTAMP for columns with date and time details (time columns in the EventPlanLine table).

Use CHAR(1) for the Customer.Internal column as Oracle does not provide a BOOLEAN data type. MySQL has the Boolean data type and PostgreSQL has the BIT(1) data type, but I suggest that you use CHAR(1) instead.

2. Constraints

After writing the basic CREATE TABLE statements, you should modify the statements with constraints. The CONSTRAINT clauses can be either inline in a column definition or separate after column definitions except where noted. You should specify a meaningful name for each CONSTRAINT clause.

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), the constraint clause can be inline or external. For multiple column primary keys (combination of PlanNo and LineNo), the CONSTRAINT clause must be external.

For each foreign key, you should specify a FOREIGN KEY constraint clause. The constraint clauses can be inline or separate.

Define NOT NULL constraints 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. Because of MySQL syntax limitations for NOT NULL constraints (inline with no constraint name and no CONSTRAINT keyword), you should define inline NOT NULL constraints.

Define a named CHECK constraint to restrict the eventrequest.status column to have a value of “Pending”, “Denied”, or “Approved”. You can use the IN operator in this constraint. In MySQL, the syntax does not allow the CONSTRAINT keyword and a constraint name for CHECK constraints. You should use the CHECK keyword followed the condition enclosed in parentheses.

Define named CHECK constraints to ensure that the resource.rate and eventrequest.estaudience are greater than 0. In MySQL, you cannot use a constraint name and the CONSTRAINT keyword for CHECK constraints. In MySQL, the syntax does not allow the CONSTRAINT keyword and a constraint name for CHECK constraints. You should use the CHECK keyword followed the condition enclosed in parentheses.

Define a named CHECK constraint involving EventPlanLine.TimeStart and EventPlanLineTimeEnd. The start time should be smaller (chronologically before) than the end time. This CHECK constraint must be external because it involves two columns. In MySQL, the syntax does not allow the CONSTRAINT keyword and a constraint name for CHECK constraints. You should use the CHECK keyword followed the condition enclosed in parentheses.

3. Populating Tables

The course website contains a text file containing SQL INSERT statements to populate the tables depending on the database server. You need to create the tables before inserting rows in each table. You need to insert rows in parent tables before child tables that reference parent tables. The INSERT statements in the file are in a proper order for populating the tables.

4. Initial CREATE TABLE Statements

To facilitate your work, you can use the CREATE TABLE statements you created in the practice assignment in module 03 for the Customer, Facility, and Location tables. Thus, you only need to write CREATE TABLE statements for the remaining five tables (ResourceTbl, Employee, EventRequest, EventPlan, and EventPlanLine). You still need to execute the CREATE TABLE statements to create all of the tables and the INSERT statements to populate all tables.

Answer rating: 100% (QA)

Answer 1 Basic CREATE TABLE Statement Requirements Create table MYSQL syntax example below the basic ... View the full answer