Oracle Database Development Project II continues developing the Oracle database
ID: 3706833 • Letter: O
Question
Oracle Database Development
Project II continues developing the Oracle database based on the Company database. Run SQL queries to test the Investment database in your Oracle account.
The project would typically go through the following phases:
1. Create the Company database with five constraints in the six appropriate tables and load data into the database via ORACLE SQL*PLUS.
Submission: Printout of SQL DDL script (Database creation script), database structure (DESC TableName) and database instance (SELECT * FROM TableName)
2. Test your Company relational database via ORACLE SQL*PLUS, which includes SQL statements, SQL solutions and output and save them in the MS word file.
Your SQL DML statements must include insert, delete, update and view
Your SELECT statements must include join tables, subqueries, Group by …. Having and function statements.
The sample of company database instances are listed below.
TAXID CNAME CADDRESS
--------------- ------------------------- -----------------------------------
282-33-5555 Kim 79 Second Ave., LA, CA90012
353-88-2345 James 8 Code St., Rosemead, CA91801
67-55-1234 Smith 20 Point Ave., Arcadia, CA91802
11-57-6789 Kong 1 New Ave., Alhambra, CA91805
77-26-8799 Scott 96 Gale Ave., Walnut, CA91866
STOCK
STOCK SNAME RATINGS PRINBUSINESS SCURRENTPRICE SA NNUALHIGH SANNUALLOW ONEYRRETURN FIVEYRRETURN
------------------------------------------------------------------------------------------------------------
CSCO Cisco Systems C Networking Equipment 15.42 24.13 11.04 -.04 17.87
ORCL Oracle Corp. B Systems Software 8.01 20.2 7.5 40.2 50.56
PFE Pfizer Inc. B Pharmaceuticals 36.22 45.04 34 45.1 31.82
WMT Wal-Mart Stores B Merchandise Stores 53.66 63.94 41.5 20.1 21.62
BA The Boeing Co. A Aerospace 43.63 69.85 27.6 25.9 14.62
MUTUAL_FUND
FUNDTICKER FNAME PRINOBJECTIVE FCURRENTPRICE FANNUALHIGH FANNUALLOW YIELD FFAMILYID
---------- ----------------------------------- ---------------------------------
JAGIX Janus Growth Fund 28.15 30.42 27.84 -.23 JAN11
FEQIX Fidelity Equity_Income 48.07 55.11 42.46 -1.53 FIE22
RPMGX T Rowe Mid-Gap Growth 37.84 40.06 36.18 -1.18 TRW55
VERDX Veredus Growth Fund 15.74 19.88 15.74 -13.91 ABN97
GEEQX GE US Equity Fund 26.02 28.12 25.92 -3.24 GE777
FUND_Family
FFAMILYID FMNAME FMADDRESS
---------- --------------- --------------------------------------------------
JAN11 Janus 3773 Cherry Creek North Dr., Denver, CO 80209
FIE22 Fidelity 2300 Litton Lane, Herbron, KY41048
TRW55 T Rowe Price 100 East Pratt Street, Baltimore, MD21202
ABN97 ABN AMRO 208 S. LaSalle St., 4th Floor, Chicago, IL60604
GE777 GE 3003 Summer St., Stamford, CT06912
FUND_PORTFOLIO
TAXID FUNDTICKER FNUMBERSHARES
--------------- ---------- ---------------
282-33-5555 JAGIX 500
282-33-5555 FEQIX 200
282-33-5555 RPMGX 500
353-88-2345 JAGIX 100
353-88-2345 VERDX 200
353-88-2345 FEQIX 300
67-55-1234 VERDX 300
67-55-1234 FEQIX 1000
11-57-6789 RPMGX 200
11-57-6789 GEEQX 500
77-26-8799 GEEQX 100
77-26-8799 RPMGX 300
STOCK_PORTFOLIO
TAXID STOCK SNUMBERSHARES
--------------- ----- ---------------
282-33-5555 CSCO 500
282-33-5555 ORCL 200
282-33-5555 WMT 500
353-88-2345 CSCO 100
353-88-2345 BA 200
353-88-2345 PFE 300
67-55-1234 ORCL 300
67-55-1234 WMT 1000
11-57-6789 CSCO 200
11-57-6789 WMT 500
77-26-8799 CSCO 100
77-26-8799 BA 300
Explanation / Answer
Question 1) Creating the given table in database.
The create statement for creating the given tables is below. Each table has a primary key and associated foreign key is also defined. Apart from primary and foreign key constraint CHECK and NOT NULL constraint are also defined whereever necessary.
// Creating table CUSTOMER
CREATE TABLE CUSTOMER
(
TAXID VARCHAR(15),
CNAME VARCHAR(30) NOT NULL,
CADDRESS VARCHAR(100) NOT NULL,
PRIMARY KEY (TAXID)
);
// Creating table STOCK
CREATE TABLE STOCK
(
STOCK CHAR(10),
SNAME VARCHAR(30),
RATINGS CHAR(1),
PRINBUSINESS VARCHAR(50),
SCURRENTPRICE NUMERIC(8,2),
SANNUALHIGH NUMERIC(8,2),
SANNUALLOW NUMERIC(8,2),
ONEYERETURN NUMERIC(8,2),
FIVEYRRETURN NUMERIC(8,2),
PRIMARY KEY (STOCK)
);
// Creating table FUND_Family
CREATE TABLE FUND_Family
(
FFAMILYID VARCHAR(10),
FMNAME VARCHAR(25),
FMADDRESS VARCHAR(100),
PRIMARY KEY (FFAMILYID)
);
// Creating table MUTUAL_FUND
CREATE TABLE MUTUAL_FUND
(
FUNDTICKER VARCHAR(10),
FNAME VARCHAR(20),
PRINOBJECTIVE VARCHAR(50) CHECK (PRINOBJECTIVE IN ('Growth Fund', 'Equity_Income', 'Mid-Gap Growth', 'Equity Fund')),
FCURRENTPRICE NUMERIC(8,2) CHECK (FCURRENTPRICE > 0),
FANNUALHIGH NUMERIC(8,2),
FANNUALLOW NUMERIC(8,2),
YIELD NUMERIC(8,2),
FFAMILYID VARCHAR(10),
PRIMARY KEY (FUNDTICKER),
FOREIGH KEY (FFAMILYID) REFERENCES FUND_Family (FFAMILYID)
);
// Creating table FUND_PORTFOLIO
CREATE TABLE FUND_PORTFOLIO
(
TAXID VARCHAR(15),
FUNDTICKER VARCHAR(10),
FNUMBERSHARES INTEGER,
PRIMARY KEY (TAXID, FUNCTICKER),
FOREIGN KEY (TAXID) REFERENCES CUSTOMER (TAXID),
FOREIGN KEY (FUNDTICKER) REFERENCES MUTUAL_FUND (FUNDTICKER)
);
// Creating table STOCK_PORTFOLIO
CREATE TABLE STOCK_PORTFOLIO
(
TAXID VARCHAR(15),
STOCK CHAR(10),
SNUMBERSHARES INTEGER,
PRIMARY KEY (TAXID, STOCK),
FOREIGN KEY (TAXID) REFERENCES CUSTOMER (TAXID),
FOREIGN KEY (STOCK) REFERENCES STOCK (STOCK)
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.