Scribd
Upload
13 views4 pages

ICA Database CREATE TABLE Assignment

The Module 3 assignment focuses on creating tables for the Intercollegiate Athletic Database using the CREATE TABLE statement in Oracle Cloud or PostgreSQL. Students are required to follow specific guidelines for data types, constraints, and populating tables with SQL INSERT statements. The final submission must include formatted CREATE TABLE statements and evidence of successful table creation with populated data.

Uploaded by

kashishrawat286
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views4 pages

ICA Database CREATE TABLE Assignment

The Module 3 assignment focuses on creating tables for the Intercollegiate Athletic Database using the CREATE TABLE statement in Oracle Cloud or PostgreSQL. Students are required to follow specific guidelines for data types, constraints, and populating tables with SQL INSERT statements. The final submission must include formatted CREATE TABLE statements and evidence of successful table creation with populated data.

Uploaded by

kashishrawat286
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Assignment for Module 3

The assignment in Module 3 provides experience with the CREATE TABLE statement

for the Intercollegiate Athletic (ICA) Database. Note that this assignment extends the practice

problems for the ICA database. You should use Oracle Cloud or PostgreSQL for this assignment.

For each DBMS, you need to use a client to connect to the database server such as the SQL

Developer for Oracle Cloud or pgAdmin for PostgreSQL.

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 advice about data type selections.

 You should use standard SQL data types specified in the notes. For columns with

variable length text, you should use VARCHAR, the SQL standard data type.

 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 [Link]) should also be the same

data type and length.

 For Oracle, you should use the DATE data type for columns involving dates or

times. The [Link] and [Link] columns will store

both date and time details so you should use the DATE data type. 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).


2/22/25 Assignment for Module 3 Page 2

 Use CHAR(1) for the [Link] column as Oracle does not provide a

BOOLEAN data type. 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 external.

 The foreign key constraint for PlanNo in the EventPlanLine table should cascade
deletions. Cascading deletes means that deletion of a parent row in the EventPlan table
causes deletion of related rows in the EventPlanLine table. Hint: use the ON DELETE
CASCADE clause.

 Define NOT NULL constraints for all columns except [Link],


[Link], [Link], and [Link]. NOT NULL
constraints for the PK of each table are optional as not null is implied with a primary key
constraint.

 Define a named CHECK constraint to restrict the [Link] column to have a

value of “Pending”, “Denied”, or “Approved”. You can use the IN operator in this

constraint.
2/22/25 Assignment for Module 3 Page 3

 Define named CHECK constraints to ensure that [Link] and

[Link] are greater than 0.

 Define a named CHECK constraint involving [Link] 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.

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 wrote in the ICA

practice problems of Module 3 for the Customer, Facility, and Location tables. Module 3

contains a document with solutions for the ICA practice problems. 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 the tables and the INSERT statements to populate all tables.

5. Submission
The submission requirements involve CREATE TABLE statements and evidence that you

executed the statements and created the tables in Oracle or PostgreSQL. You should submit 1

document containing a CREATE TABLE statement and screen snapshot for each table. You

should neatly format your CREATE TABLE statements. You should use the same table and

column names as specified in the ICA database background document. For the screen snapshot,
2/22/25 Assignment for Module 3 Page 4

you need to capture a screen showing most columns and rows of the populated table. You can

use a feature of the Oracle or PostgreSQL client to show the rows in a table. Alternatively, you

can execute an SQL statement (for example, SELECT * FROM ResourceTbl) to show the

columns and rows.

Common questions

Powered by AI

Inserting rows into parent tables before child tables is crucial because child tables contain foreign key references that depend on the existence of corresponding primary key entries in parent tables. This order enforces referential integrity, ensuring that a child row exists in relation to a valid parent row, as dictated by foreign key constraints .

The NOT NULL constraint should be omitted for columns such as EventPlan.EmpNo, EventRequest.DateAuth, EventRequest.BudNo, and EventPlan.Notes, since certain fields do not require mandatory data entry. Additionally, primary key constraints imply a NOT NULL requirement automatically, so explicitly defining it for primary key fields is unnecessary .

In Oracle databases, the BOOLEAN data type is not available. Consequently, CHAR(1) is recommended for boolean-like fields, such as the Customer.Internal column, to represent true/false conditions using single-character values, thereby ensuring compatibility and simplicity in implementation .

The ON DELETE CASCADE clause ensures that when a row in a parent table is deleted, all corresponding rows in child tables that refer to the deleted parent row are also automatically removed. This is useful in the ICA database, specifically for the foreign key constraint of PlanNo in the EventPlanLine table, to maintain referential integrity and prevent orphaned records in child tables upon parent row deletions .

To display populated table content for submission, one can utilize client features from Oracle or PostgreSQL, such as accessing table view options. Alternatively, executing SQL statements like SELECT * FROM ResourceTbl can retrieve and display the rows and columns of a table, fulfilling the ICA assignment submission requirements by providing a visual representation of the data .

A named CHECK constraint is defined to ensure that the EventPlanLine.TimeStart value is smaller (chronologically before) than the EventPlanLine.TimeEnd. This constraint is external as it involves two columns .

The recommendation is to use the VARCHAR data type with length 8 for primary key fields such as CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo. For consistency, the corresponding foreign keys should also use the same data type and length .

In Oracle, the DATE data type is used universally for columns involving dates or times. However, in PostgreSQL, the DATE data type is used for date-only columns, while the TIMESTAMP data type is used for columns requiring date and time details. This distinction leverages PostgreSQL’s support for more granular time tracking .

For a composite primary key, the CONSTRAINT clause must be external due to its involvement of multiple columns. This is necessary because inline constraint definitions are suitable for single-column keys, whereas composite keys require a definition after all columns are declared to ensure proper linkage and enforcement .

Meaningful names for CONSTRAINT clauses enhance readability and maintainability of the database schema, allowing developers and database administrators to quickly identify the purpose and enforcement criteria of each constraint. This is particularly useful in large databases like the ICA, where understanding the specific business rules applied, such as the limitations on status values or cascading delete actions, ensures ongoing data integrity and clarity for future modifications .

You might also like