Database Tutorial

Course Tutorial Site

Site Admin

Lab #2 : Creating Tables

with 2 comments

This lab works on developing the students understanding of the syntax to create tables with database constraints. Specifically, you create tables that require PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints.

Objectives ↓

These are the lab objectives. They provide the description of the database tables. You have the table names, column names, and data types for each table. You also have designation of what the required database constraints.

Group Objectives

Group assignments are cooperative situations where you should learn from one another. Each team member should engage and type solution components individually. This tends to maximize the student learning opportunity. You should avoid the “expert and followers” paradigm, and become cooperative and interactive problem solvers. The “expert and followers” paradigm is where one person in a group does the work and learns, while others look over the shoulder at the work without truly understanding it. Team members become interactive problem solvers when they collaborate and work together on solving problems.

If something comes up, like an illness or excused absence, you may share files. The caution here is that the student receiving the work must master the concepts in the material before submitting the work to the instructor. The instructor may interview the student to determine their level of mastery and post group credit that may differ from other team members who performed the work.

  • Learn how to use the basic Data Definition Language (DDL) commands in an Oracle database, CREATE, ALTER, DROP, TRUNCATE, and RENAME.
  • Learn how to define SQL data types in an Oracle database.
  • Learn how to work with PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints in an Oracle database.

Individual Objectives

Individual assignments are situations where you apply the generalized concepts acquired in the group exercise, and transfer your skills to another database engine. You may ask questions but you should only receive general concepts, principles, or directions to published materials (such as the product documentation found in the resources). Likewise, those are the types of answers you should provide when asked questions by your peers. You should not share files in this part of the lab.

  • Learn how to use the basic Data Definition Language (DDL) commands in a MySQL database, CREATE, ALTER, DROP, TRUNCATE, and RENAME.
  • Learn how to define SQL data types in a MySQL database.
  • Learn how to work with PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints in a MySQL database.

Resources ↓

These are the lab resources.

Deliverables ↓

The table definitions are in the lab deliverables.

Table Name: CONTACT
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
CONTACT_ID PRIMARY KEY Integer Maximum
MEMBER_ID FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
CONTACT_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
LAST_NAME String 20
FIRST_NAME String 20
MIDDLE_INITIAL String 1
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL DATE Date

Table Name: ADDRESS
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
ADDRESS_ID PRIMARY KEY Integer Maximum
CONTACT_ID FOREIGN KEY CONTACT CONTACT_ID Integer Maximum
ADDRESS_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
CITY NOT NULL String 30
STATE_PROVINCE NOT NULL String 30
POSTAL_CODE String 20
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL DATE Date

Table Name: TELEPHONE
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
TELEPHONE_ID PRIMARY KEY Integer Maximum
CONTACT_ID FOREIGN KEY CONTACT CONTACT_ID Integer Maximum
ADDRESS_ID FOREIGN KEY ADDRESS ADDRESS_ID Integer Maximum
TELEPHONE_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
COUNTRY_CODE NOT NULL String 3
AREA_CODE NOT NULL String 6
TELEPHONE_NUMBER String 10
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL DATE Date

Table Name: SYSTEM_USER
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
SYSTEM_USER_ID PRIMARY KEY Integer Maximum
SYSTEM_USER_NAME NOT NULL String 20
SYSTEM_USER_GROUP_ID FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
SYSTEM_USER_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
LAST_NAME String 20
FIRST_NAME String 20
MIDDLE_INITIAL String 1
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL DATE Date

Table Name: COMMON_LOOKUP
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
COMMON_LOOKUP_ID PRIMARY KEY Integer Maximum
COMMON_LOOKUP_CONTEXT NOT NULL String 30
COMMON_LOOKUP_TYPE NOT NULL String 30
COMMON_LOOKUP_MEANING NOT NULL String 30
CREATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY SYSTEM_USER SYSTEM_USER_ID Integer Maximum
LAST_UPDATE_DATE NOT NULL DATE Date

Group Deliverables

Groups should demonstrate to the instructor or lab assistant during class time that they have developed a single re-runnable (found here by clicking on the Re-runnable Referential Integrity Scripts) script for Oracle that creates the above referenced tables. Team members should also show the following:

  • Every team member should be capable of running and explaining elements of the script running against an Oracle database.
  • Every team member can confidently explain the potential database constraints that are available when defining tables in an Oracle database.

Individual Deliverables

Individuals should demonstrate to the instructor or lab assistant during class time that you have developed a single re-runnable (found here by clicking on the Re-runnable Referential Integrity Scripts) script for MySQL that creates the above referenced tables.

  • Every team member should be capable of running and explaining elements of the script running against a MySQL database.
  • Every team member can confidently explain the potential database constraints that are available when defining tables in a MySQL database.

Written by michaelmclaughlin

December 29th, 2009 at 4:40 pm

Posted in

2 Responses to 'Lab #2 : Creating Tables'

Subscribe to comments with RSS or TrackBack to 'Lab #2 : Creating Tables'.

  1. SYSTEM_USER_GROUP_ID references COMMON_LOOKUP_ID.

    SYSTEM_USER_TYPE also references COMMON_LOOKUP_ID.

    Shouldn’t the TYPE reference COMMON_LOOKUP_TYPE and be a string, not an integer?

    Dan Y

    17 Sep 10 at 4:11 pm

  2. The types are stored in the COMMON_LOOKUP to be normalized. Often denormalization leads to inserting the value from a COMMON_LOOKUP_CODE column into the local table, and sanitizing QA checks against the lookup table periodically.

    michaelmclaughlin

    3 Oct 10 at 2:47 pm

Leave a Reply

Array