Create table, Drop table, Add constraint Exercise

Data Dictionary:

Table Name	Attribute Name	Data Description	Data Type	Length	Required?	PK or FK?	FK Referenced Table	Check
Customer	customerID	Uniquely identifies customer record	NUMBER	5	YES	PK
	customerFName	Customer first name	VARCHAR2	20
	customerLName	Customer last name	VARCHAR2	30
	customerPhone	Customer phone number	VARCHAR2	10
	customerStreet	Customer address	VARCHAR2	30
	customerCity	Customer city	VARCHAR2	30
	customerState	Customer state	CHAR	2
	customerZip	Customer zip code	VARCHAR2	9
	customerEmail	Customer email address	VARCHAR2	50
	employeeID	Uniquely identifies employee record	NUMBER	5	YES	FK	Employee
Supplier	supplierID	Uniquely identifies supplier record	NUMBER	5	Yes	PK
	supplierName	Supplier name	VARCHAR2	20
	supplierStreet	Supplier  address	VARCHAR2	30
	supplierCity	Supplier city	VARCHAR2	30
	supplierState	Supplier state	CHAR	2
	supplierZip	Supplier zip code	VARCHAR2	9
	supplierContact	Supplier contact	VARCHAR2	40
	supplierPhone	Supplier phone number	VARCHAR2	10
Program	programID	Uniquely identifies program record	NUMBER	5	YES	PK
	programName	Program name	VARCHAR2	30
	programRating	Program rating	NUMBER	1
	programLength	Program length	NUMBER	3
	programDescription	Description of the program	VARCHAR2	40
	channelTypeID	Uniquely identifies channel type record	NUMBER	5	YES	FK	Channel Type
	ratingID	Uniquely identifies rating record	NUMBER	5	YES	FK	Rating
	supplierID	Uniquely identifies supplier record	NUMBER	5	YES	FK	Supplier
Billing	billingID	Uniquely identifies billing record	NUMBER	5	YES	PK
	billingStatus	Billing status	VARCHAR2	10
	billingAmount	Billing amount	CURRENCY	6
	billingDate	Billing date	DATE
	customerID	Uniquely identifies customer record	NUMBER	5	YES	FK	Customer
Employee	employeeID	Uniquely identifies employee record	NUMBER	5	YES	PK
	employeeFName	Employee first name	VARCHAR2	20
	employeeLName	Employee last name	VARCHAR2	30
	employeePhone	Employee phone number	VARCHAR2	10
	employeeEmail	Employee e-mail address	VARCHAR2	50
Package	packageID	Uniquely identifies package record	NUMBER	5	Yes	PK
	packageName	Package name	VARCHAR2	20
	packageDescription	Description of the package	VARCHAR2	40
	packagePrice	Package price	CURRENCY	6
Subscription	subscriptionID	Uniquely identifies subscription record	NUMBER	5	YES	PK
	subscriptionPrice	Subscription price	CURRENCY	6
	customerID	Uniquely identifies customer record	NUMBER	5	YES	FK	Customer
	packageID	Uniquely identifies package record	NUMBER	5	YES	FK	Package
Channel	channelID	Uniquely identifies channel record	NUMBER	5	YES	PK
	channelName	Channel name	CHAR	10
	channelTypeID	Uniquely  identifies channel type record	NUMBER	5	YES	FK	Channel Type
Channel Package	channelID	Uniquely identifies channel record	NUMBER	5	YES	PK & FK	Channel
	packageID	Uniquely identifies package record	NUMBER	5	YES	PK & FK	Package
Survey	surveyID	Uniquely identifies survey record	NUMBER	5	YES	PK
	customerID	Uniquely identifies customer record	NUMBER	5		FK	Customer
	channelID	Uniquely identifies channel record	NUMBER	5		FK	Channel
Rating	ratingID	Uniquely identifies rating record	NUMBER	5	Yes	PK
	ratingCode	Rating code	VARCHAR2	4
	ratingDescription	Description of rating	VARCHAR2	40
Program Channel	programID	Uniquely identifies program record	NUMBER	5	YES	PK & FK	Program
	channelID	Uniquely identifies channel record	NUMBER	5	YES	PK & FK	Channel
Channel Type	channelTypeID	Uniquely identifies channel type record	NUMBER	5	YES	PK
	channelTypeDescription	Description of channel type	VARCHAR2	40

Using the Oracle SQL*Plus editor, develop a database for MiniQuest based on your data model. The database should contain all the tables and attributes discussed in the project specifications.

Be sure to use your data dictionary when creating your tables. To submit this task, you need to create a file in notepad called TEAM_f_TASK2.TXT. 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. If you include the wording CASCADE CONSTRAINTS as part of each DROP TABLE statement, then the order of the DROP statements will not mater.

For example, to drop a table named customer, you would state: DROP TABLE CUSTOMER CASCADE CONSTRAINTS PURGE; After testing and verifying that all of your create statements work, create a spooled output file with the SET ECHO ON session command set so that your code and the results will show. Deliverables for this task: comprised of the CREATE TABLE script file and the output file showing that it works. I only need to do a script for Package, Channel Package, Channel Type tables.