Tables for the Assignments
Below are
links to several files that will be used with the Oracle PL/SQL assignments.
Following that are descriptions of the files. The files can be saved as text files
that can be run in SQL as @x:\dir\file.txt where x is the drive they are saved on, dir is the path to the files and file.txt is the name of the file. The files
include:
·
demog, econ and electr
- in the World group
·
whales1, whales2,
whales3 and whales4 in the Whales group
·
planet and moon in
the Planets group
·
alex in the Furniture group
·
books in the Books group and
· baseball in the Baseball group.
Description
World Tables: Three tables are created as follows:
1.a. Demographic Table - this table will be named world_pop. It will hold information about the countries of the world and will contain the following columns:
1.b. HDI_Rank – this is a number field that contains the UN Rank of the country on its Human Development Index; this is the primary key for this table
1.c. Country – this is a varchar2 with a length of 25; it gives the country’s name
1.d. 1970 Population – this is a number field called pop_mil_70; it gives the country’s population in 1970 in millions
1.e. 1995 Population – this is a number field called pop_mil_95; it gives the country’s population in 1995 in millions
1.f. Estimated 2015 Population – this is a number field called pop_mil_15; it gives the country’s estimated population in 2015 in millions
1.g. Economics Table – this table will be named world_econ. It will hold information on each country’s economy and will contain the following columns:
1.h. HDI_Rank – this is a number field that contains the UN Rank of the country on its Human Development Index; this is the primary key for this table and is also a foreign key that references world_pop
1.i. GDP – a number field that gives the gross domestic product for the country
1.j. Agri_pc – a number field that gives the per cent of a country’s GDP that comes from agriculture
1.k. Ind_pc – a number field that gives the per cent of a country’s GDP that comes from industry
1.l. Serv_pc – a number field that gives the per cent of a country’s GDP that comes from services
1.m. Cons_priv_pc – a number field that gives the per cent of a country’s GDP that is based on private consumption
1.n. Cons_gov_pc – a number field that gives the per cent of a country’s GDP that is based on government consumption
1.o. Gov_expend_pc – a number field that gives the per cent of a country’s GDP that is based on government expenditures
1.p. Exports_pc – a number field that gives the per cent of a country’s GDP that is based on exports
1.q.
Imports_pc – a number field that gives the per cent of
a country’s GDP that is based on imports
1.r. Electronics table– this table will be named world_electr. It will hold information on each country’s economy and will contain the following columns:
1.s. HDI_Rank – this is a number field that contains the UN Rank of the country on its Human Development Index; this is the primary key for this table and is also a foreign key that references world_pop
1.t. Radio – a number field that indicates the number of radios per thousand inhabitants
1.u. TV – a number field that indicates the number of TVs per thousand inhabitants
1.v. phone_line – a number field that indicates the number of phone-lines per thousand inhabitants
1.w. Fax – a number field that indicates the number of fax machines per thousand inhabitants
1.x. Cell_phone – a number field that indicates the number of cell-phone users per thousand inhabitants
1.y. Internet_users – a number field that indicates the number of internet users per thousand inhabitants
1.z.
computers – a number field that indicates the number of
computers per thousand inhabitants
WhaleTables: Seven tables are created as follows:
1.
Oceans Table - this table will be named Oceans. It will hold
information about the oceans in which ceteceans live and will contain the
following columns
1.a.
Ocean - a varchar2 field with a length of 15; this
field will hold the name of the ocean
1.b.
OCode – a char field
with a length of 2; this field will hold the unique code for each ocean; this
field is the primary key for this table
2.
Water Temperature
table - this table will be named
create table WaterTemp. It will hold information about the water temperature in
which the cetaceans are generally found and will contain the following columns
2.a.
TCode - a char field
of length 2; it is the primary key for this table
2.b.
TClass – a varchar2
field with a length of 10; it describes the temperature class (tropical,
temperate or arctic) of the water
2.c.
AveTempCel – a number field which gives the
average temperature in Celsius
3.
Family table - this table will be named WhaleFam. It will hold
information about the families, orders and classes in the animal kingdom in
which cetaceans are found. It will contain the following columns:
3.a.
Kingdom - a varchar2
with a length of 10; it gives the name of the kingdom (animalia)
3.b.
Phylum - a varchar2
with a length of 10; it gives the name of the phylum (chordata)
3.c.
Subphylum a varchar2
with a length of 10; it gives the name of the subphylum (vertebrata)
3.d.
Class - a varchar2
with a length of 10; it gives the name of the class (mammalia)
3.e.
Subclass - a
varchar2 with a length of 10; it gives the name of the subclass (cetecea)
3.f.
Type – a varchar2
with a length of 15; it gives the type of cetecean (baleen or toothed)
3.g.
TaxOrder a varchar2
with a length of 10; it gives the name of the order
3.h.
Family - a varchar2
with a length of 20; it gives the name of the family
3.i.
ID Number – a number
field, it uniquely identifies each row and is the primary key
4.
Whales table – this table will be named Whales. This table
will contain information on the various whale and dolphin species which the
center studies. Its columns will include:
4.a.
IDNo – a number
field which is the primary key for the table
4.b.
WFID – a number
field which is foreign key referencing the whale families table
4.c.
Genus_species - a varchar2 field with a length of 50 that
gives the scientific name for the species
4.d.
CommonName – a
varchar2 field with a length of 50 that gives the common name for the species
4.e.
AveLengthM – a
number field which gives the average length in meters for the species
4.f.
AveWeightKG – a
number field which gives the average weight in kilograms for the species
4.g.
DivingMin – a number
field which gives the average length in minutes of time under water during
dives for the species
4.h.
GestationMon – a
number field which gives the average length in months of the gestation period
for the species
4.i.
LifeYr – a number
field which gives the average lifespan in years for the species
4.j.
VertCervical – a
number field which gives the average number of cervical vertebrae for the
species
4.k.
VertThoracic – a
number field which gives the average number of thoracic vertebrae for the
species
4.l.
VertLumbar – a
number field which gives the average number of lumbar vertebrae for the species
4.m.
VerCalidal – a
number field which gives the average number of calidal vertebrae for the
species
5.
Distribution
table - this table will be named
whaledist and will contain information
on the distribution of whales in the oceans. Its columns will include:
5.a.
IDNo – a number
field which is a foreign key that references the ID # in the whales table
5.b.
OCode – a char field
of length 2 that is a foreign key that references the Ocean code in the oceans
table
5.c.
TCode a char field
of length 2 that references the code in the water temperature table
6.
Individuals table – this table will be named indivwhales and will
contain information about the the individual whales that the center monitors.
Its columns will include the following:
6.a.
WhID – a number
field that is the primary key for the table; it is created using a sequence
6.b.
Name – a varchar2
field that gives the name of the individual being monitored
6.c.
DOB – a date field
that contains the date of birth of the individual
6.d.
IDNo – a number
field which is a foreign key that references the ID # in the whales tables
7.
Sightings table – this table will be named whalesightings and
will contain information about sightings of whales monitored by the center. Its
columns will include:
7.a.
WhID – a number
field that is a foreign key that references the indivwhales table
7.b.
SID – a number field
that is the primary key for the table; it is created using a sequence
7.c.
Sight_Date – a date
field that gives the date of the sighting
7.d.
LengthM – a number
field which gives the length in meters for the individual
7.e.
WeightKG – a number
field which gives the weight in kilograms for the whale
7.f.
DivingMin – a number
field which gives the length in minutes of time under water during dives for
the whale
PlanetTables: Two tables are created as follows:
1. Planet Table - this table will hold information on the planets and will contain the following columns:
1.a. name – the name of the planet is a varchar2 column of length 10
1.b. id – is the astronomical id of the planet, a Roman numeral; it is a varchar2 field with a length of 5 and it is the primary key for the table
1.c. distance_km – the distance of the planet from the sun in kilometers, a number field
1.d. orbperiod_days – the orbital period (time it takes to go around the sun) in days, a number field
1.e. discoverer – the person, if any, who discovered the planet, a varchar2 field of length 8
1.f. datediscovery – the year of the planet’s discovery, a number field
1.g. radius_km – the radius of the planet in kilometers, a number field
1.h. mass_kg – the mass of the planet in kilograms, a number field
1.i. rotperiod_days – the rotational period of the planet in days, a number field
1.j. Moon Table – this table will hold information on each planet’s moons and will contain the following columns:
1.k. name – the name of the moon is a varchar2 column of length 10
1.l. mid – the astronomical id of the moon, a Roman numeral; it is a varchar2 field with a length of 5 and is the primary key for the table
1.m. id – is the astronomical id of the moon’s planet, a Roman numeral; it is a varchar2 field with a length of 5 and it is a foreign key which references the planet table
1.n. distance_km – the distance of the moon from the planet in kilometers, a number field
1.o. orbperiod_days – the orbital period (time it takes to go around the planet) in days, a number field
1.p. discoverer – the person, if any, who discovered the moon, a varchar2 field of length 8
1.q. date_disc – the year of the moon’s discovery, a number field
1.r. other_names – other names for the moon, a varchar2 field with a length of 25
1.s. radius_km – the radius of the moon in kilometers, a number field
1.t. mass_kg – the mass of the moon in kilograms, a number field
Furniture Tables: Six tables are created as follows:
1.
Employee Table - this table will be named AFEmp. It will hold
information about Alexander Furniture employees and will contain the following
columns
1.a.
Employee ID - a
number field with a precision of four (4); this field should be created
automatically with a sequence and it is the primary key for this table
1.b.
First Name - a
varchar2 field with a length of 10
1.c.
Last Name - a
varchar2 field with a length of 15
1.d.
Address - a varchar2
field with a length of 25
1.e.
City - a varchar2
field with a length of 15
1.f.
State - a char field
with a length of 2
1.g.
Zip Code - a
varchar2 field with a length of 5
1.h.
Phone - a varchar2
field with a length of 15
1.i.
Hire Date - a date
field
1.j.
Monthly Salary - a
number field with a precision of 7 and a scale of two
2.
Customer table - this table will be named AFCust. It will hold
information about Alexander Furniture customers and will contain the following
columns
2.a.
Customer number - a
number field with a precision of four (4); this field should be created
automatically with a sequence and it is the primary key for this table
2.b.
First Name - a
varchar2 field with a length of 10
2.c.
Last Name - a
varchar2 field with a length of 15
2.d.
Address - a varchar2
field with a length of 25
2.e.
City - a varchar2
field with a length of 15
2.f.
State - a char field
with a length of 2
2.g.
Zip Code - a
varchar2 field with a length of 5
2.h.
Phone - a varchar2
field with a length of 15
2.i.
Birthday - a date
field
2.j.
Employee ID - a
number field with a precision of four (4); this field is a foreign key that
relates the customer to the employee who is his/her primary contact
3.
Vendor table - this table will be named AFVend. It will hold
information about Alexander Furniture vendors (from whom they buy products) and
will contain the following columns
3.a.
Vendor number - a
number field with a precision of four (4); this field should be created
automatically with a sequence and it is the primary key for this table
3.b.
First Name - a
varchar2 field with a length of 10
3.c.
Last Name - a
varchar2 field with a length of 15
3.d.
Address - a varchar2
field with a length of 25
3.e.
City - a varchar2
field with a length of 15
3.f.
State - a char field
with a length of 2
3.g.
Zip Code - a
varchar2 field with a length of 5
3.h.
Phone - a varchar2
field with a length of 15
3.i.
Company - a varchar2
field with a length of 20
3.j.
Employee ID - a
number field with a precision of four (4); this field is a foreign key that
relates the vendor to the employee who is his/her primary contact
4.
Inventory Table - this table will be named AFInv. It will hold
information about Alexander Furniture's inventory and will contain the
following columns
4.a.
Item number - a char
field with a length of four (4); this is the primary key for this table and
will be of the form X123, where X is an alphabetic character indicating whether
the item is E(lectronics), A(ppliances) or F(urniture) and the last three
characters identify the specific product
4.b.
Item Name - a
varchar2 field with a length of 10
4.c.
Item Cost - the
purchase price of the item, it is a number with precision seven and scale two
4.d.
Item Price - the sale
price of the item, it is a number with a precision of seven and a scale of two
4.e.
Item Quantity - a
number field that shows the quantity of the item in stock
4.f.
Vendor Number - a
number field with a precision of four (4); this field is a foreign key that relates
the item to the vendor who supplies it
5.
Purchase Table - this table will be named AFPur. It will hold
information about Alexander Furniture's purchases and will contain the
following columns
5.a.
Purchase order
number - a number field with a precision of four (4); this field should be
created automatically with a sequence and it is the primary key for this table
5.b.
Item Number - a char
field with a length of four (4); this field is a foreign key that relates the
item to stock in inventory
5.c.
Vendor Number - a
number field with a precision of four (4); this field is a foreign key that
relates the item to the vendor who supplies it
5.d.
Item Quantity - a
number field that shows the quantity purchased
5.e.
Arrival Date - a
date field that indicates the date the stock arrived
5.f.
Total Cost - a
number field with a precision of seven and a scale of two that shows the total
cost of the items purchased
6.
Sales Table - this table will be named AFSale. It will hold
information about Alexander Furniture's sales and will contain the following
columns
6.a.
Invoice number - a
number field with a precision of four (4); this field should be created
automatically with a sequence and it is the primary key for this table
6.b.
Item Number - a char
field with a length of four (4); this field is a foreign key that relates the
item to stock in inventory
6.c.
Customer Number - a
number field with a precision of four (4); this field is a foreign key that
relates the item to the customer who bought it
6.d.
Item Quantity - a
number field that shows the quantity purchased
6.e.
Sales Date - a date
field that indicates the date the item was sold
6.f.
Total Price - a
number field with a precision of seven and a scale of two that shows the total
price of the items sold
6.g. Employee ID - a number field with a precision of
four (4); this field is a foreign key that relates the item to the employee who
sold it
Book Table: One table is created as follows:
1. Book Table - this table will hold information on the books in stock and will contain the following columns:
1.a. Genre – a varchar2 field with a length of 6 which indicates whether the book is a mystery or scifi novel
1.b. name – a varchar2 field with a length of 40 which gives the book’s title
1.c. afn – a varchar2 field with a length of 20 which gives the author’s first name
1.d. aln – a varchar2 field with a length of 20 which gives the author’s last name
1.e. price – a number field which gives the selling price of the book
1.f. cost – a number field that gives the purchase cost of the book
1.g. quantity –a number field that indicates the quantity of the book in stock
1.h. type – a varchar2 field with a length of 10 which indicates whether the book is hardcover or paperback
1.i. pages – a number field for the number of pages in the book
1.j. pub_date – a varchar2 field with a length of 20 which gives the book’s month and year of publication
1.k. isbn – a varchar2 field with a length of 20 which gives the book’s isbn number; it is the primary key
1.l. character – a varchar2 field with a length of 40 which gives the book’s title primary character
1.m. description – a varchar2 field with a length of 250 which gives a description of the book
Baseball Table: One table is created as follows:
1. Baseball Table - this table will hold information on the players and will contain the following columns:
1.a. First_Name – a varchar2 field with a length of 15 that holds the player’s first name
1.b. Last_Name – a varchar2 field with a length of 20 that holds the player’s last name
1.c. BARank – a number field with a length of 3 and no decimal places that gives the player’s rank based on batting averages
1.d. Batting_Ave – a number field with a length of 6 and 3 decimal places that gives the player’s lifetime batting average
1.e. HRRank – a number field with a length of 3 and no decimal places that gives the player’s rank based on home runs
1.f. Home_Runs – a number field with a length of 3 and no decimal places that gives the number of lifetime home runs for the player
1.g. RBIRank – a number field with a length of 3 and no decimal places that gives the player’s rank based on RBIs
1.h. RBIs Number(4,0– a number field with a length of 4 and no decimal places that gives the number of lifetime RBIs for the player
1.i. SARank – a number field with a length of 3 and no decimal places that gives the player’s rank based on slugging averages
1.j. Slugging_Ave – a number field with a length of 6 and 3 decimal places that gives the player’s slugging average
1.k. HitRank – a number field with a length of 3 and no decimal places that gives the player’s rank based on number of hits
1.l. Hits– a number field with a length of 4 and no decimal places that gives the number of lifetime hits for the player
1.m. DOB Date– a date field that gives the player’s date of birth
1.n. DOD Date– a date field that gives the player’s date of death, if any
Back to Oracle Page | Back to Chris Riddiough's Home Page