I can\'t seem to add my schema. I can try to add my data dictionary though. I do
ID: 3682016 • 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
CREATE VIEW vWhatever
AS
SELECT (whatever you want to select)
FROM [Database A].[schema - usually dbo].DSNA_tblMaiin AS a
JOIN [Database B].[dbo].DSNB_tblMaiin AS b ON a.Serialnumber = b.SerialNumber
JOIN [Database C].[dbo].DSNC_tblMaiin AS c ON a.SerialNumber = c.SerialNumber
WHERE (whatever you want to filter by)
GO
If the assets were in one of the tables, but not all three (and you didn't know which one), you could use a UNION within the view to accomplish it - it will essentially bring all three tables together, appending the fields that you specify (of the same datatype) until you have one large "virtual table" ex:
CREATE VIEW vWhatever
AS
SELECT col1, col2, SerialNumber, etc.
FROM [Database A].[dbo].DSNA_tblMaiin
UNION ALL
SELECT col1, col2, SerialNumber, etc.
FROM [Database B].[dbo].DSNB_tblMaiin
UNION ALL
SELECT col1, col2, SerialNumber, etc.
FROM [Database C].[dbo].DSNC_tblMaiin
GO
Dot notation is exactly what you're looking at. And if you were extending the query to another server, it would just be [ServerName].[DatabaseName].[ObjectSchema].[ObjectName] instead of [DatabaseName].[ObjectSchema].[ObjectName]
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.