Assignment 2: Campus (Campusid, Campusname, Street, City, State, Zip, Phone

Assignment 2
Overview
In this assignment you will build a sample database similar to the preferred
solution for Assignment 1. You will create the tables in Oracle, add sample data
and create several queries.
Activities
Step 1.
Using Oracle, create the tables identified below in the following order:
DATABASE TABLES:
Campus (CampusID, CampusName, Street, City, State, Zip, Phone,

CampusDiscount)
Position (PositionID, Position, YearlyMembershipFee)
Members (MemberID, LastName, FirstName, CampusAddress, CampusPhone,

CampusID, PositionID, ContractDuration)
FK CampusID --> Campus(CampusID)
PositionID --> Position(PositionID)
Prices (FoodItemTypeID, MealType, MealPrice)
FoodItems (FoodItemID, FoodItemName, FoodItemTypeID)

FK FoodItemTypeID --> Prices(FoodItemTypeID)
Orders (OrderID, MemberID, OrderDate)

FK MemberID --> Members(MemberID)
OrderLine (OrderID, FoodItemsID, Quantity)

FK OrderID --> Orders(OrderID)
FoodItemsID --> FoodItems(FoodItemID)
STRUCTURE NOTES:
Use the proper naming convention for your constraints:

Example: Constraint
TableName_FieldName_ConstraintID(Campus_CampusID_PK)
 Set up the Primary Keys for each table with Constraints listed.
Note: The OrderLine Table has a composite Primary Key
 Add Your Foreign Keys for each table with your Constraints listed.
 Set up your Sequence for the Prices table ONLY. Remember to follow the
proper naming convention.
The Sequence will be used in the insert commands to add your auto
numbering into the Primary Key (FoodItemTypeID) fields. Name the
Sequence "Prices_FoodItemID_Seq"
 Make the Data Types for all the Primary Keys and their corresponding
Foreign Keys Varchar2(5).
 Make the Data Type for OrderDate Varchar2(25). (we won't worry about
the date format, way too complicated for what we are doing).
 Make the Data Types for the MealPrice and YearlyMembershipFee Decimal,
7 digits maximum with 2 digits to the right of the decimal place, so that
we can perform calculations on them.
 Make the Data Types for ContractDuration, and Quantity Integer with 3
digits maximum for calculation purposes.
 Make the Data Type for CampusDiscount Decimal, 2 digits maximum with
2 digits to the right of the decimal place.
Step 2.
Use the Insert Into Command to add your data to each table. Add data to your
primary tables first and then to your secondary tables. Also, remember to use the
sequence code with your insert statement to add the auto number value to each
primary key field.
DATA TO BE INSERTED:
Campus:
'1','IUPUI','425 University Blvd.','Indianapolis', 'IN','46202', '317-274-4591',.08
'2','Indiana University','107 S. Indiana Ave.','Bloomington', 'IN','47405', '812-
855-4848',.07
'3','Purdue University','475 Stadium Mall Drive','West Lafayette', 'IN','47907',
'765-494-1776',.06

Position:
'1','Lecturer', 1050.50
'2','Associate Professor', 900.50
'3','Assistant Professor', 875.50
'4','Professor', 700.75
'5','Full Professor', 500.50

Members:
'1','Ellen','Monk','009 Purnell', '812-123-1234', '2', '5', 12
'2','Joe','Brady','008 Statford Hall', '765-234-2345', '3', '2', 10
'3','Dave','Davidson','007 Purnell', '812-345-3456', '2', '3', 10

'4','Sebastian','Cole','210 Rutherford Hall', '765-234-2345', '3', '5', 10
'5','Michael','Doo','66C Peobody', '812-548-8956', '2', '1', 10
'6','Jerome','Clark','SL 220', '317-274-9766', '1', '1', 12
'7','Bob','House','ET 329', '317-278-9098', '1', '4', 10
'8','Bridget','Stanley','SI 234', '317-274-5678', '1', '1', 12
'9','Bradley','Wilson','334 Statford Hall', '765-258-2567', '3', '2', 10
Prices: Note - Remember that these Primary Key Values should be entered
using the Sequence (autonumber)
'1','Beer/Wine', 5.50
'2','Dessert', 2.75
'3','Dinner', 15.50
'4','Soft Drink', 2.50
'5','Lunch', 7.25
FoodItems:
'10001','Lager', '1'
'10002','Red Wine', '1'
'10003','White Wine', '1'
'10004','Coke', '4'
'10005','Coffee', '4'
'10006','Chicken a la King', '3'
'10007','Rib Steak', '3'
'10008','Fish and Chips', '3'
'10009','Veggie Delight', '3'
'10010','Chocolate Mousse', '2'
'10011','Carrot Cake', '2'
'10012','Fruit Cup', '2'
'10013','Fish and Chips', '5'
'10014','Angus Beef Burger', '5'
'10015','Cobb Salad', '5'

Orders:
'1', '9', 'March 5, 2005'
'2', '8', 'March 5, 2005'
'3', '7', 'March 5, 2005'
'4', '6', 'March 7, 2005'
'5', '5', 'March 7, 2005'
'6', '4', 'March 10, 2005'
'7', '3', 'March 11, 2005'
'8', '2', 'March 12, 2005'
'9', '1', 'March 13, 2005'

OrderLine:
'1','10001',1
'1','10006',1
'1','10012',1
'2','10004',2
'2','10013',1
'2','10014',1
'3','10005',1
'3','10011',1
'4','10005',2
'4','10004',2
'4','10006',1
'4','10007',1
'4','10010',2
'5','10003',1
'6','10002',2
'7','10005',2
'8','10005',1
'8','10011',1
'9','10001',1

Step 3.
Create the queries listed below:
NOTE: Before you begin to run Queries 1 - 8, Enter the following two commands
at the SQL Prompt:
Set Linesize 110

Set Pagesize 90
This will allow you to see most, if not all, of your data without it wrapping.
1. Select all records from each table

2. List all of your constraints in the database
3. List all of your table names in the database
4. List your sequence name in the database
select sequence_name
from user_sequences;
5. List the Columns and Datatypes of each table
6. Create a listing of all Faculty Members (First and Last), their Faculty
Position and the University that they are affiliated with (Name), along with
their Monthly_Dues (Calculated Field with a column alias). Sort the records
in descending order by University and then by Faculty's last name in
ascending order.
7. Create a listing that shows the various food items that the faculty club
serves (Name of the food item, type of food item and the price of the food
item). Note: List no alcoholic beverages. Sort the records in ascending
order by price.
8. List the OrderID, Order Date, Faculty Member's Name, Campus Name,
each FoodItem that makes up a given order, the type of meal, cost of the
meal, quantity ordered and the total line total (calculated field and column
alias). Sort by Order IDs in descending order.
Hand In
Submit one Clean combined text file that has each query name, query
command and the output directly under the command in the order that they
are listed in the assignment above.

Assignment 2: Campus (Campusid, Campusname, Street, City, State, Zip, Phone

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

Assignment 2: Campus (Campusid, Campusname, Street, City, State, Zip, Phone

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignment 2: Campus (Campusid, Campusname, Street, City, State, Zip, Phone

Uploaded by

Copyright:

Available Formats

Assignment 2

Campus (CampusID, CampusName, Street, City, State, Zip, Phone,

Position (PositionID, Position, YearlyMembershipFee)

Members (MemberID, LastName, FirstName, CampusAddress, CampusPhone,

Prices (FoodItemTypeID, MealType, MealPrice)

FoodItems (FoodItemID, FoodItemName, FoodItemTypeID)

Orders (OrderID, MemberID, OrderDate)

OrderLine (OrderID, FoodItemsID, Quantity)

Use the proper naming convention for your constraints:

'3','Dave','Davidson','007 Purnell', '812-345-3456', '2', '3', 10

Create the queries listed below:

Set Linesize 110

1. Select all records from each table

You might also like