Logical Database Design

Only available on StudyMode
  • Download(s) : 45
  • Published : February 24, 2013
Open Document
Text Preview
CIT 214 – Assignment 1 – Logical Database Design, The Big Boys and Girls Gym Database

View 1

MEMBER (MEM_ID, MEM_LAST, MEM_FIRST, MEM_EMAIL, MEM_LEVEL, MEM_BDAY)
AK MEM_EMAIL
SKMEM_LAST

EMAIL (EMAIL_ID, MEM_ID, C_ID, EVENT_ID, EMAIL_DESCRIP)
SK MEM_ID, C_ID, EVENT_ID
FK MEM_ID ( MEMBER
FK C_ID ( CLASS
FK EVENT_ID ( EVENT

CLASS (C_ID, C_NUMBER, C_NAME, C_START_TIME, C_END_TIME, C_ACTUAL_DATE)
SK C_NUMBER

EVENT (EVENT_ID, EVENT_DESCRIP)
SK EVENT_DESCRIP

View 2

INSTRUCTOR (INST_ID, INST_LAST, INST_FIRST)
SK INST_LAST

INSTRUCTOR SPECIALTIES (SPEC_ID, INST_ID)
FK SPEC_ID ( SPECIALTIES
INST_ID ( INSTRUCTOR

SPECIALTIES (SPEC_ID, SPEC_DESCRIPTION)
SK SPEC_DESCRIPTION

View 3

MEMBER (MEM_ID, MEM_LAST, MEM_FIRST, MEM_PHONE, MEM_EMAIL, MEM_LEVEL)
SKMEM_LAST

SESSION (SESS_ID, MEM_ID, INST_ID, SESS_DATE, SESS_FEE, SESS_QUANTITY, SESS_PMT)
SK MEM_ID, INST_ID
FKMEM_ID ( MEMBER
INST_ID ( INSTRUCTOR

INSTRUCTOR (INST_ID, INST_LAST, INST_FIRST, INST_PHONE, INST_EMAIL)
AK INST_PHONE, INST_EMAIL
SKINST_LAST

View 4

INSTRUCTOR (INST_ID, INST_LAST, INST_FIRST, INST_PHONE, INST_EMAIL)
AK INST_PHONE, INST_EMAIL
SKINST_LAST

CLASS (C_ID, C_NUMBER, C_NAME, C_START_DATE, C_END_DATE, C_START_TIME, C_END_TIME, C_ACTUAL_DATE, INST_ID)
SKC_NUMBER, C_NAME
FKINST_ID ( INSTRUCTOR

View 5

MEMBER (MEM_ID, MEM_LAST, MEM_FIRST, MEM_LEVEL)
SKMEM_LAST

ROSTER (MEM_ID, C_ID, MEM_PAID)
FKMEM_ID ( MEMBER
C_IS ( CLASS

CLASS (C_ID, C_NUMBER, C_NAME)
SKC_NUMBER

View 6

MEMBER (MEM_ID, MEM_LAST, MEM_FIRST, MEM_LEVEL)
SKMEM_LAST

PLAN (PLAN_ID, MEM_ID, DIET_ID, PLAN_START, PLAN_END)
SKMEM_ID, DIET_ID
FKMEM_ID ( MEMBER
DIET_ID ( DIET

DIET (DIET_ID, DIET_DESCRIPTION)
SKDIET_DESCRIPTION

Combined View Relational Diagram

MEMBER (MEM_ID, MEM_LAST, MEM_FIRST, MEM_PHONE, MEM_EMAIL, MEM_LEVEL, MEM_BDAY)
AK MEM_PHONE, MEM_EMAIL
SKMEM_LAST

EMAIL (EMAIL_ID, MEM_ID, C_ID, EVENT_ID, EMAIL_DESCRIP)
SK MEM_ID, C_ID, EVENT_ID
FK MEM_ID ( MEMBER
FK C_ID ( CLASS
FK EVENT_ID ( EVENT

CLASS (C_ID, C_NUMBER, C_NAME, C_START_DATE, C_END_DATE, C_START_TIME, C_END_TIME, C_ACTUAL_DATE, INSTR_ID)
SKC_NUMBER, C_NAME
FKINST_ID ( INSTRUCTOR

EVENT (EVENT_ID, EVENT_DESCRIP)
SK EVENT_DESCRIP

INSTRUCTOR (INST_ID, INST_LAST, INST_FIRST, INST_PHONE, INST_EMAIL)
AK INST_PHONE, INST_EMAIL
SKINST_LAST

INSTRUCTOR SPECIALTIES (SPEC_ID, INST_ID)
FK SPEC_ID ( SPECIALTIES
INST_ID ( INSTRUCTOR

SPECIALTIES (SPEC_ID, SPEC_DESCRIPTION)
SK SPEC_DESCRIPTION

SESSION (SESS_ID, MEM_ID, INST_ID, SESS_DATE, SESS_FEE, SESS_QUANTITY, SESS_PMT)
SK MEM_ID, INST_ID
FKMEM_ID ( MEMBER
INST_ID ( INSTRUCTOR

ROSTER (MEM_ID, C_ID, MEM_PAID)
FKMEM_ID ( MEMBER
C_IS ( CLASS

PLAN (PLAN_ID, MEM_ID, DIET_ID, PLAN_START, PLAN_END)
SKMEM_ID, DIET_ID
FKMEM_ID ( MEMBER
DIET_ID ( DIET

DIET (DIET_ID, DIET_DESCRIPTION)
SKDIET_DESCRIPTION

View 1 Technical Summary
Email notifications consist of several parts: upcoming events, classes, and birthday greetings. In order to accomplish all of these things, three one-to-many relationships were necessary, with Email being a new intersection table (the many entity). Members can receive many emails, but the email will only be sent to one Member (per birthday, event, or class notification). The same method goes with the Classes and Events. The Email entity then takes on the primary keys (PKs) of each existing table as attributes. A new PK (EMAIL_ID) was created so each Email would be unique. Email then will have multiple foreign keys (FKs), one for each “one” entity it has a relationship with. View 2 Technical Summary

There are many Specialties and many Instructors. Each...
tracking img