Academic Integrity: tutoring, explanations, and feedback — we don’t complete graded work or submit on a student’s behalf.

I can\'t seem to add my schema. I can try to add my data dictionary though. I do

ID: 3681991 • Letter: I

Question

I can't seem to add my schema. I can try to add my data dictionary though.

I don’t think I have it right. Can someone that understands SQL look this over and help me correct my mistakes? Below is the actual assignment so you know what I am trying to do.

Directions DO NOT add or change the names of any entities or attributes within this template. Your responsibility is to provide the correct data types, PKs, composite keys, FKs, and data restrictions based on the business requirements. The VARCHAR or CHAR data types will NOT work for every attribute AND I will count points off if you use only VARCHAR or CHAR in place of the correct data type based on the attribute names and requirements. I am looking for you to analyze the business requirements and create a database your customer can use with ease. I will furnish ALL DATA for your term project so if you are asked to create data anywhere in the project just disregard the statement. You will be asked to modify data in one task but not insert new data. The INSERT statements will be furnished later in the term. If you would like to complete this task early and email your work to me, I would be willing to review it and provide constructive feedback so you can be better prepared for the INSERT statements. Using MySQL Workbench develop the SQL script which will create the database for “DirectTime” based on your data model. The database should contain all the entities and attributes discussed in the project specifications and provided by the IS605 Data Dictionary Solution Template. Make sure to use your completed data dictionary when creating the SQL CREATE script as I will compare the ERD to the Data Dictionary and the entities/attributes must match or points will be deducted. To submit this task you need to create a file named TASK2.SQL. In this file, create a heading called CREATE STATEMENTS and then develop the CREATE TABLE statements required to create your database tables. Be sure to include the DROP TABLE statements at the top of your file so that you can reuse the file as needed. When you generate the CREATE Scripts within MySQL Workbench you have an option to generate the DROP statements in the script. I would highly recommend moving the DROP TABLE statements to the beginning of your SQL script since the foreign keys will restrict your use of the DROP TABLE command. As you progress in the course you will understand the reason for the DROP TABLE statements. You will modify attributes and entities BUT you will need the ORIGINAL entities, attributes, and data for the final task of this project.

Deliverable for this task

1. Task 2 comprised of the CREATE TABLE script file. Make sure your scripts works before you submit them.

MBA609 TERM PROJECT DATA DICTIONARY SOLUTION TEMPLATE

Table Name

Attribute Name

Contents

Type

Format

Range

Required

PK/FK

FK Reference

Survey_T

Survey_No

Survey Number

NUMBER

Y

Survey_Date

Date of the Survey

DATE

mm/dd/yyyy

Cust_No

Customer Number

CHAR(10)

Y

FK

CUSTOMER

Channel_No

Channel Number

NUMBER (9,2)

Y

FK

CHANNEL

Channel_T

Channel_No

Channel Number

NUMBER (9,2)

1-10

Y

PK

Channel_Name

Channel Name

CHAR(20)

Y

Channel_Type

Channel Type

VARCHAR(20)

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Y

Prog_Channel_T

Channel_No

Channel Number

NUMBER (2)

Y

PK

Program_No

Program Number

VARCHAR(20)

Y

FK

PROGRAM_T

Prog_Channel_Date

Date of the program

DATE

mm/dd/yyyy

Prog_Channel_Time

Time program airs

DATE

HH:MI AM

Channel_Pack_T

Channel_No

Channel Number

NUMBER (9,2)

1-10

Y

FK

PROGRAM

Package_No

Package Number

VARCHAR(20)

Y

FK

PACKAGE

Customer_T

Cust_No

Customer Number

CHAR(10)

Y

PK

Cust_First_Name

Customer First Name

VARCHAR(20)

Y

Cust_Last_Name

Customer Last Name

VARCHAR(20)

Y

Cust_Street

Customer Address

VARCHAR(20)

Cust_City

Customer City

VARCHAR(20)

Cust_State

Customer State

CHAR(2)

Cust_Zip

Customer Zip

CHAR(5)

Cust_Phone

Customer Phone number

NUMBER

(000) 000-0000

Cust_Email

Customer Email

VARCHAR(20)

Package_No

Package Number

NUMBER

Y

FK

CHANNEL

Cust_Status

Customer Status

LOGICAL

'ACTIVE' or 'INACTIVE'

Cust_Deleted

Customer Deleted?

LOGICAL

'Y' or 'N'

Cust_Date_Deleted

Customer Deletion Date

DATE

Cust_Deleted_Reason

Reason Customer was Deleted

VARCHAR(20)

Emp_No

Employee Number who sold or deleted

CHAR(6)

y

FK

EMPLOYEE

Cust_Start_Date

Date customer started

DATE

Program_T

Program_No

Program Number

VARCHAR(20)

Y

PK

Program_Name

Short name of the program

VARCHAR(20)

Y

Program_Length

Length of the program

NUMBER(9,2)

Y

Program_Desc

Long description of the program

VARCHAR(20)

Program_Type

Program type identifier

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Rating_Code

Rating Number

CHAR(8)

Y

FK

RATING

Sup_No

Supplier number

CHAR(5)

Y

Package_T

Package_No

Package Number

CHAR(8)

Y

PK

Package_Name

Name of the package

VARCHAR(20)

Y

Package_Price

Price of the package

NUMBER(9,2)

Y

Employee_T

Emp_No

Employee Number

CHAR(8)

y

PK

Emp_First_Name

Employee First Name

VARCHAR(20)

Y

Emp_Last_Name

Employee Last Name

VARCHAR(20)

Y

Billing_T

Billing_No

Bill Number

CHAR(8)

Y

Billing_Due_Date

Bill due date

DATE

mm/dd/yyyy

Billing_Amt

Amount owed

NUMBER(2,9)

Billing_Amt_Paid

Amount paid

NUMBER(9,2)

Billing_Date_Paid

Date paid

DATE

Cust_No

Customer Number

CHAR

Y

FK

CUSTOMER

Supplier_T

Sup_No

Supplier number

CHAR(8)

Y

PK

Sup_Network

Supplier network

CHAR(3)

Y

Sup_Name

Supplier name

VARCHAR(20)

Y

Sup_Phone

Supplier phone number

NUMBER

(000) 000-0000

Sup_Address

Supplier Address

VARCHAR(20)

Sup_City

Supplier City

VARCHAR(20)

Sup_State

Supplier State

CHAR(2)

Sup_Zip

Supplier Zip

CHAR(5)

Rating_T

Rating_Code

Rating number (GUID)

CHAR(8)

Y

PK

Rating_Desc

Rating code

CHAR(8)

Y

MBA609 TERM PROJECT DATA DICTIONARY SOLUTION TEMPLATE

Table Name

Attribute Name

Contents

Type

Format

Range

Required

PK/FK

FK Reference

Survey_T

Survey_No

Survey Number

NUMBER

Y

Survey_Date

Date of the Survey

DATE

mm/dd/yyyy

Cust_No

Customer Number

CHAR(10)

Y

FK

CUSTOMER

Channel_No

Channel Number

NUMBER (9,2)

Y

FK

CHANNEL

Channel_T

Channel_No

Channel Number

NUMBER (9,2)

1-10

Y

PK

Channel_Name

Channel Name

CHAR(20)

Y

Channel_Type

Channel Type

VARCHAR(20)

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Y

Prog_Channel_T

Channel_No

Channel Number

NUMBER (2)

Y

PK

Program_No

Program Number

VARCHAR(20)

Y

FK

PROGRAM_T

Prog_Channel_Date

Date of the program

DATE

mm/dd/yyyy

Prog_Channel_Time

Time program airs

DATE

HH:MI AM

Channel_Pack_T

Channel_No

Channel Number

NUMBER (9,2)

1-10

Y

FK

PROGRAM

Package_No

Package Number

VARCHAR(20)

Y

FK

PACKAGE

Customer_T

Cust_No

Customer Number

CHAR(10)

Y

PK

Cust_First_Name

Customer First Name

VARCHAR(20)

Y

Cust_Last_Name

Customer Last Name

VARCHAR(20)

Y

Cust_Street

Customer Address

VARCHAR(20)

Cust_City

Customer City

VARCHAR(20)

Cust_State

Customer State

CHAR(2)

Cust_Zip

Customer Zip

CHAR(5)

Cust_Phone

Customer Phone number

NUMBER

(000) 000-0000

Cust_Email

Customer Email

VARCHAR(20)

Package_No

Package Number

NUMBER

Y

FK

CHANNEL

Cust_Status

Customer Status

LOGICAL

'ACTIVE' or 'INACTIVE'

Cust_Deleted

Customer Deleted?

LOGICAL

'Y' or 'N'

Cust_Date_Deleted

Customer Deletion Date

DATE

Cust_Deleted_Reason

Reason Customer was Deleted

VARCHAR(20)

Emp_No

Employee Number who sold or deleted

CHAR(6)

y

FK

EMPLOYEE

Cust_Start_Date

Date customer started

DATE

Program_T

Program_No

Program Number

VARCHAR(20)

Y

PK

Program_Name

Short name of the program

VARCHAR(20)

Y

Program_Length

Length of the program

NUMBER(9,2)

Y

Program_Desc

Long description of the program

VARCHAR(20)

Program_Type

Program type identifier

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Rating_Code

Rating Number

CHAR(8)

Y

FK

RATING

Sup_No

Supplier number

CHAR(5)

Y

Package_T

Package_No

Package Number

CHAR(8)

Y

PK

Package_Name

Name of the package

VARCHAR(20)

Y

Package_Price

Price of the package

NUMBER(9,2)

Y

Employee_T

Emp_No

Employee Number

CHAR(8)

y

PK

Emp_First_Name

Employee First Name

VARCHAR(20)

Y

Emp_Last_Name

Employee Last Name

VARCHAR(20)

Y

Billing_T

Billing_No

Bill Number

CHAR(8)

Y

Billing_Due_Date

Bill due date

DATE

mm/dd/yyyy

Billing_Amt

Amount owed

NUMBER(2,9)

Billing_Amt_Paid

Amount paid

NUMBER(9,2)

Billing_Date_Paid

Date paid

DATE

Cust_No

Customer Number

CHAR

Y

FK

CUSTOMER

Supplier_T

Sup_No

Supplier number

CHAR(8)

Y

PK

Sup_Network

Supplier network

CHAR(3)

Y

Sup_Name

Supplier name

VARCHAR(20)

Y

Sup_Phone

Supplier phone number

NUMBER

(000) 000-0000

Sup_Address

Supplier Address

VARCHAR(20)

Sup_City

Supplier City

VARCHAR(20)

Sup_State

Supplier State

CHAR(2)

Sup_Zip

Supplier Zip

CHAR(5)

Rating_T

Rating_Code

Rating number (GUID)

CHAR(8)

Y

PK

Rating_Desc

Rating code

CHAR(8)

Y

Explanation / Answer

Solution: See the updated specification below:

MBA609 TERM PROJECT DATA DICTIONARY SOLUTION TEMPLATE

Table Name

Attribute Name

Contents

Type

Format

Range

Required

PK/FK

FK Reference

Survey_T

Survey_No

Survey Number

INT(10)

Unsigned, Auto Increment

Y

PK

Survey_Date

Date of the Survey

DATE

mm/dd/yyyy

Cust_No

Customer Number

VARCHAR(10)

Y

FK

CUSTOMER

Channel_No

Channel Number

TINYINT(2)

Y

FK

CHANNEL

Channel_T

Channel_No

Channel Number

TINYINT(2)

Unsigned

1-10

Y

PK

Channel_Name

Channel Name

VARCHAR(20)

Y

Channel_Type

Channel Type

VARCHAR(20)

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Y

Prog_Channel_T

Channel_No

Channel Number

TINYINT(2)

Unsigned

Y

Composite key(Channel_No+Program_No), FK

Channel_T

Program_No

Program Number

INT(10)

Unsigned

Y

Composite key(Channel_No+Program_No), FK

PROGRAM_T

Prog_Channel_Date

Date of the program

DATE

mm/dd/yyyy

Prog_Channel_Time

Time program airs

DATE

HH:MI AM/PM

Channel_Pack_T

Channel_No

Channel Number

TINYINT(2)

Unsigned

1-10

Y

FK

Channel_T

Package_No

Package Number

CHAR(8)

Y

FK

PACKAGE

Customer_T

Cust_No

Customer Number

VARCHAR(10)

Y

PK

Cust_First_Name

Customer First Name

VARCHAR(20)

Y

Cust_Last_Name

Customer Last Name

VARCHAR(20)

Y

Cust_Street

Customer Address

VARCHAR(20)

Cust_City

Customer City

VARCHAR(20)

Cust_State

Customer State

CHAR(2)

Cust_Zip

Customer Zip

CHAR(5)

Cust_Phone

Customer Phone number

VARCHAR(15)

(000) 000-0000

Cust_Email

Customer Email

VARCHAR(255)

Package_No

Package Number

CHAR(8)

Y

FK

CHANNEL

Cust_Status

Customer Status

LOGICAL

'ACTIVE' or 'INACTIVE'

Cust_Deleted

Customer Deleted?

LOGICAL

'Y' or 'N'

Cust_Date_Deleted

Customer Deletion Date

DATE

Cust_Deleted_Reason

Reason Customer was Deleted

VARCHAR(20)

Emp_No

Employee Number who sold or deleted

CHAR(8)

y

FK

EMPLOYEE

Cust_Start_Date

Date customer started

DATE

mm/dd/yyyy

Program_T

Program_No

Program Number

INT(10)

Unsigned

Y

PK

Program_Name

Short name of the program

VARCHAR(20)

Y

Program_Length

Length of the program

INT(10)

Unsigned

Y

Program_Desc

Long description of the program

TEXT

Program_Type

Program type identifier

VARCHAR(20)

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Rating_Code

Rating Number

CHAR(8)

Y

FK

RATING

Sup_No

Supplier number

CHAR(5)

Y

Package_T

Package_No

Package Number

CHAR(8)

Y

PK

Package_Name

Name of the package

VARCHAR(20)

Y

Package_Price

Price of the package

DECIMAL(9,2)

Y

Employee_T

Emp_No

Employee Number

CHAR(8)

y

PK

Emp_First_Name

Employee First Name

VARCHAR(20)

Y

Emp_Last_Name

Employee Last Name

VARCHAR(20)

Y

Billing_T

Billing_No

Bill Number

INT(10)

Unsigned, Auto Increment

Y

Billing_Due_Date

Bill due date

DATE

mm/dd/yyyy

Billing_Amt

Amount owed

DECIMAL(9,2)

Billing_Amt_Paid

Amount paid

DECIMAL(9,2)

Billing_Date_Paid

Date paid

DATE

mm/dd/yyyy

Cust_No

Customer Number

CHAR(8)

Y

FK

CUSTOMER

Supplier_T

Sup_No

Supplier number

CHAR(8)

Y

PK

Sup_Network

Supplier network

CHAR(3)

Y

Sup_Name

Supplier name

VARCHAR(20)

Y

Sup_Phone

Supplier phone number

VARCHAR(15)

(000) 000-0000

Sup_Address

Supplier Address

VARCHAR(20)

Sup_City

Supplier City

VARCHAR(20)

Sup_State

Supplier State

CHAR(2)

Sup_Zip

Supplier Zip

CHAR(5)

Rating_T

Rating_Code

Rating number (GUID)

CHAR(8)

Y

PK

Rating_Desc

Rating code

VARCHAR(8)

Y


Updated entries are underlined. You can modify your data model accordingly.

MBA609 TERM PROJECT DATA DICTIONARY SOLUTION TEMPLATE

Table Name

Attribute Name

Contents

Type

Format

Range

Required

PK/FK

FK Reference

Survey_T

Survey_No

Survey Number

INT(10)

Unsigned, Auto Increment

Y

PK

Survey_Date

Date of the Survey

DATE

mm/dd/yyyy

Cust_No

Customer Number

VARCHAR(10)

Y

FK

CUSTOMER

Channel_No

Channel Number

TINYINT(2)

Y

FK

CHANNEL

Channel_T

Channel_No

Channel Number

TINYINT(2)

Unsigned

1-10

Y

PK

Channel_Name

Channel Name

VARCHAR(20)

Y

Channel_Type

Channel Type

VARCHAR(20)

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Y

Prog_Channel_T

Channel_No

Channel Number

TINYINT(2)

Unsigned

Y

Composite key(Channel_No+Program_No), FK

Channel_T

Program_No

Program Number

INT(10)

Unsigned

Y

Composite key(Channel_No+Program_No), FK

PROGRAM_T

Prog_Channel_Date

Date of the program

DATE

mm/dd/yyyy

Prog_Channel_Time

Time program airs

DATE

HH:MI AM/PM

Channel_Pack_T

Channel_No

Channel Number

TINYINT(2)

Unsigned

1-10

Y

FK

Channel_T

Package_No

Package Number

CHAR(8)

Y

FK

PACKAGE

Customer_T

Cust_No

Customer Number

VARCHAR(10)

Y

PK

Cust_First_Name

Customer First Name

VARCHAR(20)

Y

Cust_Last_Name

Customer Last Name

VARCHAR(20)

Y

Cust_Street

Customer Address

VARCHAR(20)

Cust_City

Customer City

VARCHAR(20)

Cust_State

Customer State

CHAR(2)

Cust_Zip

Customer Zip

CHAR(5)

Cust_Phone

Customer Phone number

VARCHAR(15)

(000) 000-0000

Cust_Email

Customer Email

VARCHAR(255)

Package_No

Package Number

CHAR(8)

Y

FK

CHANNEL

Cust_Status

Customer Status

LOGICAL

'ACTIVE' or 'INACTIVE'

Cust_Deleted

Customer Deleted?

LOGICAL

'Y' or 'N'

Cust_Date_Deleted

Customer Deletion Date

DATE

Cust_Deleted_Reason

Reason Customer was Deleted

VARCHAR(20)

Emp_No

Employee Number who sold or deleted

CHAR(8)

y

FK

EMPLOYEE

Cust_Start_Date

Date customer started

DATE

mm/dd/yyyy

Program_T

Program_No

Program Number

INT(10)

Unsigned

Y

PK

Program_Name

Short name of the program

VARCHAR(20)

Y

Program_Length

Length of the program

INT(10)

Unsigned

Y

Program_Desc

Long description of the program

TEXT

Program_Type

Program type identifier

VARCHAR(20)

'Movie' 'News' 'Lifestyle' 'Documentary' 'Sports'

Rating_Code

Rating Number

CHAR(8)

Y

FK

RATING

Sup_No

Supplier number

CHAR(5)

Y

Package_T

Package_No

Package Number

CHAR(8)

Y

PK

Package_Name

Name of the package

VARCHAR(20)

Y

Package_Price

Price of the package

DECIMAL(9,2)

Y

Employee_T

Emp_No

Employee Number

CHAR(8)

y

PK

Emp_First_Name

Employee First Name

VARCHAR(20)

Y

Emp_Last_Name

Employee Last Name

VARCHAR(20)

Y

Billing_T

Billing_No

Bill Number

INT(10)

Unsigned, Auto Increment

Y

Billing_Due_Date

Bill due date

DATE

mm/dd/yyyy

Billing_Amt

Amount owed

DECIMAL(9,2)

Billing_Amt_Paid

Amount paid

DECIMAL(9,2)

Billing_Date_Paid

Date paid

DATE

mm/dd/yyyy

Cust_No

Customer Number

CHAR(8)

Y

FK

CUSTOMER

Supplier_T

Sup_No

Supplier number

CHAR(8)

Y

PK

Sup_Network

Supplier network

CHAR(3)

Y

Sup_Name

Supplier name

VARCHAR(20)

Y

Sup_Phone

Supplier phone number

VARCHAR(15)

(000) 000-0000

Sup_Address

Supplier Address

VARCHAR(20)

Sup_City

Supplier City

VARCHAR(20)

Sup_State

Supplier State

CHAR(2)

Sup_Zip

Supplier Zip

CHAR(5)

Rating_T

Rating_Code

Rating number (GUID)

CHAR(8)

Y

PK

Rating_Desc

Rating code

VARCHAR(8)

Y

Related Questions

I can\'t figure this out. Can I get some help programing in C. Basic Double Coup
Question #3746062
I can\'t figure this out. Chapter 4, 22. It doesn\'t let me attach the picture!
Question #1848301
I can\'t figure this out. Please, help me. Process In note 2, Callaway discusses
Question #2523103
I can\'t find a definite answer to the issue anywhere but I suspect there are ju
Question #3561058
I can\'t find info in my book about Receptor-Ligand interaction, Scatchard analy
Question #178482
I can\'t find the answer of (d) and (e). Please, explain it more details. Thanks
Question #3224878
I can\'t find the correct answer for the acid-test ratio. Thank you! The balance
Question #2588537
I can\'t find the errors in my work can you help? Suppose the income statement f
Question #2472899
Hire Me For All Your Tutoring Needs
Integrity-first tutoring: clear explanations, guidance, and feedback.
Drop an Email at
drjack9650@gmail.com
Chat Now And Get Quote

Navigate

Previous
I can\'t see results unless i click the Save button Hi,, My workbook is working
Next
I can\'t seem to add my schema. I can try to add my data dictionary though. I do

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.