Create a table for database transformation , Database Management System

Step 1. Using Oracle, create the tables identified in the preferred solution for Assignment 2.

Technicians (ZipCode, TechnicianID, FirstName, LastName, Street, City, State, HomePhone, WorkPhone)

ZipCode is the Primary Key and should be 5 character fixed length data. TechnicianID - should be 3 character fixed length data. State should be 2 character fixed length data.

Customers (CustomerNum, FirstName, LastName, Street, City, State, ZipCode, Phone, OutsideOnly)

CustomerNum is the Primary Key and should be 3 character fixed length data. State should be 2 character fixed length data. ZipCode - Is the Foreign Key relating the Technician and the Customers tables and should be 5 character fixed length data. OutsideOnly is a 1 character fixed length data.

Chemicals (ChemicalNum, ChemicalName, PricePerPound)

ChemicalNum is the Primary Key and should be 2 character fixed length data. PricePerPound should be numeric data. 4 digits maximum with two allocated to the right of the decimal place.

Treatments Table

Create Table Treatments ( ServiceDate Date, ChemicalNum Char(2) Constraint Treatments_ServiceDate_FK References Chemicals(ChemicalNum), CustomerNum Char(3) Constraint Treatments_CustomerNum_FK References Customers(CustomerNum), QuantityUsed Number, Constraint Treatements_3Combo_PK Primary Key(ServiceDate, ChemicalNum, CustomerNum) );

Due to the complexity of the Treatment Table, the create table code has been provided to you. Note that we are setting up the service Date with the Data datatype and the ChemicalNum and CustomerNum are Foreign Keys relating respectively to the Chemicals and Customers tables. QuanitiyUsed is set up as a floating numeric data field, because it can accept numbers who have a floating decimal place or whole numbers. Note that this table has a combination primary key that consist of the ServiceDate, ChemicalNum and the CustomerNum in combination.

STRUCTURE NOTES:

 Use the proper naming convention for your constraints: Example: Constraint TableName_FieldName_ConstraintID (Customer_CusNum_PK) Set up the Primary Keys for each table with Constraints listed. Add Your Foreign Keys for each table with Constraints listed. All Required fields are listed above in redand should have constraints set up for them. Make the Data Types for the remaining fields either fixed or variable length character data. I will leave it up to you do decide which of the remaining fields should be fixed vs. variable, as well as determining their size.

 Step 2.

Create the queries listed below:

NOTE: Before you begin to run Queries 1 - 11, Enter the following two commands at the SQL Prompt:

Set Linesize 120 Set Pagesize 70

This will allow you to see all of your data without it wrapping.

1. Select all records from each table - Label Q1a-Q1d

2. List all of your constraints in the database one table at a time. - Label Q2a-Q2d

3. List all of your table names in the database. - Label Q3

4. List the Columns and data types of each table - Label Q4a-Q4d

5. List the customer information of any customer who only gets outside service. Label Q5

6. List the customers information for any customer who lives in Potsdam or Pensville and who do not get outside service only. Label Q6

7. List the Technician ID, and their first and last name along with each of their customer's number and first and last name. Use Table Aliases of T for Technicians Table and C for Customers Table. Also use Column Aliases as follows: TechFirst, TechLast, CusFirst, CustLast . Label Q7

8. List the first name , last name and street address of any pair of customers whose address contains "Rd". Label Q8

9. List the Technician's ID, Customer Number, Date of Service, Chemical used and the amount of chemical used. Primary Sort Key is Technician's ID in ascending order and the Secondary Sort key is Customer Number in descending order. Label Q9

10. Repeat Question 9 above, but lets add the price per pound of each chemical and lets calculate the total cost of each chemical used at each house. Place price per before quantity used and the total chemical cost after quantity used as a column aliases named "Chemical Charge". Label Q10

11. List the name Chemical number, name and price per pound of any chemical that has not been used at a house. Hint: Combination of two very difficult SQL Concepts.

Posted Date: 3/11/2013 5:55:40 AM | Location : United States







Related Discussions:- Create a table for database transformation , Assignment Help, Ask Question on Create a table for database transformation , Get Answer, Expert's Help, Create a table for database transformation Discussions

Write discussion on Create a table for database transformation
Your posts are moderated
Related Questions
Consider the entity type dependent related to employee entity, which is used to keep track of the dependents of every employee. The attributes of dependents are: name, sex, birth d

What is indexed sequential file organization? What are the applications of this organization?  Ans: An index file can be employed to effectively overcome the problem of storing

(A) For the relation; CarSales (RegNo, BuyerName, Address, SalesPerson, Make, Model, Commission) The following functional dependences hold, RegNo _ID à Make, Model Reg

1. Determine the set of minimal functional dependencies for the following data. The local bakery, Happy Cakes, needs your help designing a database that will support a kiosk in

Explain the Natural join Natural join is a binary operator which is written as (R* S) in which R and S are relations. The effect of the natural join is the set of all combinati

What are the ways in which signal can be generated A signal might be generated in many ways such as a Signal is result of particular physical condition. Whenever a process writ

The PROJECT operation The project operation is used to choose the records with specified attributes as discarding the others based on some related criteria. This is denotes as ∏

What is 2NF? A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully functionally dependent on primary key.

Define the concept of aggregation.  Give two examples where this concept is useful. Ans: Aggregation transforms a relationship among the objects into a higher-level object. A

Which is another name for weak entity? Child is another name of weak entity.