Use oracle database management to create tables: SUBMIT THE ASSIGNMENT IN A WORD
ID: 3705548 • Letter: U
Question
Use oracle database management to create tables:
SUBMIT THE ASSIGNMENT IN A WORD DOCUMENT FORMAT 1. Use Oracle Database Management System Interface to create the following tables Customer Table with the following columns . 1. QustomerNumber as number with precision 10 and scale 0 not null 2. CustomerName as a varchar2 with size 30 and not null 3. Address 4. City 5. State 6. ZipCode 7. Phone With as the primary key Order Table with the following columns » 1. QrderNumber as number with precision 10 and scale 0 not null 2. Date as number not null 3. Subtotal as number with precision of 10 and scale of 3 null 4. Tax as number with precision of 10 and scale of 3 null 5. Total as number with precision of 10 and scale of 3 null 6. QustomerNumber as number with precision of 10 and scale of 0 not null With QrderNumber as the primary key and CustomerNumber as a foreign key Item Table with the following columns . 1. ItemNumber as number with precision of 10 and scale of 0 not null 2. UxitPrice as number with precision of 10 and scale of 3 null 3. Description as varchar2 with size 100 With ItemNumber as the primary key . OrderLine ltem Table with the following columns 1. QrderNumber as number with precision of 10 and scale of 0 not null 2. LineNumber as number with precision of 10 and scale of 0 not null 3. Quantity as number with precision of 10 and scale of 0 not null 4. UvitPrice as number with precision of 10 and scale of 3 not null 5. Extended Price as number with precision of 14 and scale of 3 not null LtemNumber as number with precision of 10 and scale of 0 not null 6. With LineNumber as primary key ltemNumber and QrderNumber as foreign keysExplanation / Answer
Answer 1:
Query to create table Customer
Create table Customer
(
CustomerNumber number(10,0) not null,
CustomerName varchar2(30) not null,
Address varchar2(50),
City varchar2(10),
State varchar2(10),
ZipCode number(10),
Phone number(10),
CONSTRAINT pk PRIMARY KEY (CustomerNumber)
);
Query to create table ORDER
Create table Order
(
OrderNumber number(10,0) not null,
Date number not null,
Subtotal number(10,3) null,
Tax number(10,3) null,
Total number(10,3) null,
CustomerNumber number(10,0) not null,
CONSTRAINT pk1 PRIMARY KEY (OrderNumber),
CONSTRAINT fk FOREIGN KEY (CustomerNumber) REFERENCES Customer(CustomerNumber)
);
Query to create Item table
Create table Item
(
ItemNumber number(10,0) not null,
UnitPrice number(10,3) null,
Description varchar2(100),
CONSTRAINT pk2 PRIMARY KEY (ItemNumber)
);
Query to create Order_line_item table
Create table Order_Line_item
(
OrderNumber number(10,0) not null,
LineNumber number(10,0) not null,
Quantity number(10,0) not null,
UnitPrice number(10,3) not null,
ExtendedPrice number(14,3) not null,
ItemNumber number(10,0) not null,
CONSTRAINT pk3 PRIMARY KEY (LineNumber),
CONSTRAINT fk2 FOREIGN KEY (ItemNumber) REFERENCES Item(ItemNumber),
CONSTRAINT fk3 FOREIGN KEY (OrderNumber) REFERENCES Order(OrderNumber)
);
Answer 2.
Insert into Customer values(1,’ABC’,’Street1’,’New Delhi’,’Delhi’,110075,1234567891);
Insert into Customer values(2,’DEF’,’Street2’,’New Delhi’,’Delhi’,110075,9871234568);
Insert into Customer values(3,’GHI’,’Street3’,’New Delhi’,’Delhi’,110075,9865213243);
Insert into Order values(1,10081998,100,10,150,1);
Insert into Order values(2,10081998,100,10,150,2);
Insert into Order values(3,10081998,100,10,150,3);
Insert into Item values(5,100,’a1’);
Insert into Item values(6,200,’a2’);
Insert into Item values(7,300,’a3’);
Insert into Order_Line_Item values( 1, 11,101,400,600,5);
Insert into Order_Line_Item values( 2, 12,102,500,600,6);
Insert into Order_Line_Item values( 3, 13,103,600,700,7);
Answer 3:
Alter table Customer
Add Constraint c Check (City IN(‘Orlando’, ‘New York’,’Atlanta’,’Charlotte’, ‘Detroit’));
Answer 4:
Alter table Customer
Add email varchar2(20) not null;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.