Database Tutorial

Course Tutorial Site

Site Admin

Lab #3 : Modeling Data

without comments

This lab works on developing the students understanding of the table definition and syntax to model tables into Unified Modeling Language (UML) static class diagrams. Specifically, you create a Microsoft Visio drawing that represents and annotates relationships between tables. Relationships are provided by lines that depict two relations. Annotation has two facets. One is the annotation of the cardinality governing each relation. The other is descriptive verbiage that adds meaning to relations.

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. You will need to create the verbiage to describe relations.

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 translate basic textual descriptions into visual drawings.
  • Learn how to describe relationship cardinality.
  • Learn how to describe relationship with words to tell meaningful stories.
  • Learn how to use Microsoft Visio to draw UML class diagrams.

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 Quest’s Toad Development Tool to produce Entity Relationship Models (ERMs), which are also known as Entity Relationship Diagrams (ERDs).
  • Learn how the relationship between the database catalog necessary to re-engineer database models.
  • Learn how to work with columns that have PRIMARY KEY, FOREIGN KEY relationships without having conmensurate database-level constraints.

Resources ↓

These are the lab resources.

Deliverables ↓

The table definitions are in the lab deliverables.

Table Name: MEMBER
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
MEMBER_ID PRIMARY KEY Integer Maximum
MEMBER_TYPE FOREIGN KEY
NOT NULL		 COMMON_LOOKUP
 		 COMMON_LOOKUP_ID
 		 Integer
 		 Maximum
 
ACCOUNT_NUMBER String 10
CREDIT_CARD_NUMBER String 19
CREDIT_CARD_TYPE Integer Maximum
CREATED_BY FOREIGN KEY
NOT NULL		 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
 /td>
LAST_UPDATE_DATE NOT NULL DATE Date

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
NOT NULL		 MEMBER
 		 MEMBER_ID
 		 Integer
 		 Maximum
 
CONTACT_TYPE FOREIGN KEY
NOT NULL		 COMMON_LOOKUP
 		 COMMON_LOOKUP_ID
 		 Integer
 		 Maximum
 
LAST_NAME String 20
FIRST_NAME String 20
MIDDLE_INITIAL String 1
CREATED_BY FOREIGN KEY
NOT NULL		 SYSTEM_USER SYSTEM_USER_ID Integer Maximum
CREATION_DATE NOT NULL DATE Date
LAST_UPDATED_BY FOREIGN KEY
NOT NULL		 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: STREET_ADDRESS
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
STREET_ADDRESS_ID PRIMARY KEY Integer Maximum
ADDRESS_ID FOREIGN KEY ADDRESS ADDRESS_ID Integer Maximum
STREET_ADDRESS 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

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: RENTAL
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
RENTAL_ID PRIMARY KEY Integer Maximum
CUSTOMER_ID FOREIGN KEY CONTACT CONTACT_ID Integer Maximum
CHECK_OUT_DATE NOT NULL Date
RETURN_DATE Date
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: ITEM
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
ITEM_ID PRIMARY KEY Integer Maximum
ITEM_BARCODE NOT NULL String 20
ITEM_TYPE FOREIGN KEY COMMON_LOOKUP COMMON_LOOKUP_ID Integer Maximum
ITEM_TITLE NOT NULL String 60
ITEM_SUBTITLE String 60
ITEM_DESC Text 65,535
ITEM_BLOB Binary 65,535
ITEM_BFILE String 255
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: RENTAL_ITEM
Column Name Constraint Data
Type		 Physical
Size
Type Reference Table Reference Column
RENTAL_ITEM_ID PRIMARY KEY Integer Maximum
RENTAL_ID FOREIGN KEY RENTAL RENTAL_ID Integer Maximum
ITEM_ID FOREIGN KEY ITEM ITEM_ID Integer Maximum
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 Microsoft Visio document that qualifies all relationships between entities. The drawing should also qualify cardinality and annotate relations with English phrases.

  • Every team member should be capable of explaining the idea of binary relationships.
  • Every team member should be capable of explaining the idea of cardinality.
  • Every team member should be capable of annotating ERDs with meaningful English phrases.

Individual Deliverables

Individuals should demonstrate to the instructor or lab assistant during class time that you have installed Quest’s Toad for MySQL and can successfully generate an ERD with the software (note that these are not annotated consistent with a complete ERM.

  • Every team member should install and learn how to operate Quest’s Toad for MySQL.
  • Every team member should demonstrate how to create a ERD with Quest’s Toad for MySQL.

Written by michaelmclaughlin

December 29th, 2009 at 4:40 pm

Posted in

Leave a Reply

Array