Due at Sam on 3/9718) Implement the logical design of the Eauction database syst

Question

Due at Sam on 3/9718) Implement the logical design of the Eauction database system described in this document (creating a relational database) on the ORACLE platform using SQL DDL Statements. The ER diagram is provided on the last page. The relational schema, data type, and sizes are given below. You cannot simply turn in the DDL statements generated by ERWin for ORACLE RDBMS of the ER diagram Rl: CUSTOMER (Email, Name, Phone) R2: ACCOUNT HOLDER (Cust ID. Birthday, Gender, Street. ZipCode, City, Email) ACCOUNT HOLDERJEmail] c CUSTOMER.[Email] R3: ACCOUNT (Accountt, Payment type. Date created, Cust ID) ACCOUNT.ICust IDl ACCOUNT_HOLDER.[Cust ID] R4: ITEM (Item#, Item Name, Item Description, of Buuver ID, Buy Date, Final Price) 10. Offer Date. Ask Price, Close Date, ITEM.IOffer IDJ c ACCOUNT HOLDER.Cust ID] ITEM.Buyer IDls ACCOUNT HOLDER.[Cust ID RS CATEGORY (Cat ID. Cat Name Cat Description) R6: BID (Itemf, Email, Bid Price, Bid Date) BLDLItem#1 ITEM. [Item#1 BID.[ Email] CUSTOMER. [Email] RT BELONG-TO (Item#, Cat ID) BE LONG-TOLItem#C ITEM. [Item#1 BELONG TO.[Cat ID} CATEGORY.[Cat IDI Data T Size Attribute Name Email Name Phone Data Type Size Varchar2 Varchar2 40 QfferID Number Varchar 2 15 Number Date Number Char Varchar2 20 Number Varchar2 30 Attribute Name Item Description Varchar2 30 30 Number All Price Attributes Number7 Item# Number 5 Number Number Varchar2 20 Account# All Date attributes Cust ID Gender Street Zip Code City 5 Buyer ID Cat DescriptionVarchar230

Explanation / Answer

Answer is as follows:

DDL statements for given sceanrio are as follows:

/* create table customer */
CREATE TABLE CUSTOMER(
Email varchar2(30) Not NULL,
Name varchar2(40),
Phone Number(10),
CONSTRAINT customers_pk PRIMARY KEY (Email)
);

/* Create table Account Holder */
CREATE TABLE ACCOUNT_HOLDER(
Cust_Id Number(5) NOT NULL ,
Birthday date,
Gender char(1) CHECK (Gender = 'M' OR Gender = 'F') ,
Street varchar(20),
Zip_code number(5),
city varchar2(30),
Email varchar2(30),
CONSTRAINT fk_customer
FOREIGN KEY (Email) References CUSTOMER(Email),
CONSTRAINT ach_pk PRIMARY KEY (Cust_Id)
);
  

/* Create table account*/
CREATE TABLE ACCOUNT(
Account number(5) not null,
Payment_Type varchar2(15) CHECK ( Payment_Type = 'Cash' OR Payment_Type = 'Check' OR Payment_Type = 'Credit' OR Payment_Type = 'Debit'),
Date_Created date,
Cust_Id number(5),
CONSTRAINT fk_accoount_holder
FOREIGN KEY (Cust_Id) References ACCOUNT_HOLDER(Cust_Id),
CONSTRAINT account_pk PRIMARY KEY (Account)
);
  

/* Create table item */
CREATE TABLE ITEM(
Item number(5) not null CHECK (Item >= 10000 AND Item <= 89999),
Item_Name varchar2(40),
Item_Description varchar2(30),
Offer_Id number(5),
Offer_date date,
Ask_Price number(7),
Close_Date date,
Buyer_Id number(5),
Buy_date date,
Final_Price number(7),
CONSTRAINT item_pk PRIMARY KEY (Item),
CONSTRAINT fk_accoount
FOREIGN KEY (Offer_Id) References ACCOUNT_HOLDER(Cust_Id),
FOREIGN KEY (Buyer_Id) References ACCOUNT_HOLDER(Cust_Id)
);
  

/* create table category*/
CREATE TABLE CATEGORY(
Cat_Id number(5) not null CHECK (Cat_Id >= 1 OR Cat_Id <= 8),
Cat_name varchar2(20) CHECK (Cat_name = 'Art' OR Cat_name = 'Jewelry' OR Cat_name = 'Bedding' OR Cat_name = 'Kithchen' OR Cat_name = 'Book' OR Cat_name = 'Furniture' OR Cat_name = 'Electronics' OR Cat_name = 'Misc'),
Cat_Description varchar2(30)
);
  

/* create table bid */
CREATE TABLE BID(
Item_id number(5),
Email varchar2(30),
Bid_Price number(7),
Bid_date date,
CONSTRAINT fk_bid
FOREIGN KEY (Item_Id) References ITEM(Item_Id),
FOREIGN KEY (Email) References CUSTOMER(Email)
);
  

/* create table belong to*/
CREATE TABLE BELONG_TO
(
Item_Id number(5),
Cat_Id number(5),
CONSTRAINT fk_accoount
FOREIGN KEY (Item_Id) References Item(Item_Id),
FOREIGN KEY (Cat_Id) References CATEGORY(Cat_Id)
);

if there is any query please ask in comments....

if there is any error in these than let me know before down-voting..

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

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