Question

The common requirements of all questions are the same. Read each question carefully and submit a query to fulfill for each question. Also, students need to submit the result of the corresponding query under the query. Also, assignment has to be written with Microsoft Word for feedback. For technical issue, other formats, such as image, sql, are not appropriate to receive feedback and comments. Don’t delete or change questions. For assignment 5 and 6, you don’t need to submit screenshots. HOWEVER, YOUR SQLS MUST BE SUBMITTED, AND THE SQLS MUST BE EXECUTED CORRECTLY AND APPLIED TO THE DATABASE. I will check that your SQLs have been applied to your Database and will give a full score only if I can find outputs in your Database as SQLs are submitted.

1. Create the following tables (Considering that your company starts a new business of catering for universities) (70 points)

campuses (campus_id, campus_name, street, city, state, zip, phone, campus_discount)

positions (position_id, position, yearly_membership_fee)

members (member_id, last_name, first_name, campus_address, campus_phone, campus_id, position_id, contract_duration)

      fk   campus_id --> campus(campus_id)

             position_id --> positions(position_id)

prices (food_item_type_id, meal_type, meal_price)

food_items (food_item_id, food_item_name, food_item_type_id)

     fk    food_item_type_id --> prices(food_item_type_id)

food_orders (order_id, member_id, order_date)

     fk   member_id --> members(member_id)

food_order_lines (order_id, food_items_id, quantity)

     fk   order_id --> food_orders(order_id)

           food_items_id --> fooditems(fooditem_id)

• Requirements
• 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 Order_Line Table has a composite Primary Key
• Add Your Foreign Keys for each table with your Constraints listed.
• Make the Data Types for all the Primary Keys and their corresponding Foreign Keys Varchar2(5).
• Make the Data Type for Order_Date Varchar2(25) not Date.
• Make the Data Types for the Meal_Price and Yearly_Membership_Fee 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 Contract_Duration, and Quantity Integer with 3 digits maximum for calculation purposes.
• Make the Data Type for Campus_Discount Decimal, 2 digits maximum with 2 digits to the right of the decimal place.

SQL Statements

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. (30 points)

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
1,'Beer/Wine', 5.50
2,'Dessert', 2.75
3,'Dinner', 15.50
4,'Soft Drink', 2.50
5,'Lunch', 7.25

Food_Items:
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

Food_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'

Order_Line:
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

SQL Statements

Answer 1

1 ) CREATE TABLE campuses ( campus_id  Varchar2(5) NOT NULL, campus_name  Varchar2(100), street Varchar2(50), city Varchar2(100), state Varchar2(50) , zip Varchar2(50) , phone Varchar2(12), campus_discount decimal (2,2));

ALTER TABLE campuses ADD CONSTRAINT campuses_campus_id_pk PRIMARY KEY (campus_id);

2) CREATE TABLE positions (position_id Varchar2(5), position Varchar2(100), yearly_membership_fee decimal (7,2));

ALTER TABLE positions ADD  CONSTRAINT position_position_id_pk PRIMARY KEY (position_id);

3) CREATE TABLE members ( member_id Varchar2(5), last_name Varchar2(100), first_name Varchar2(100), campus_address Varchar2(200), campus_phone Varchar2(12), campus_id Varchar2(5), position_id Varchar2(5) ,contract_duration integer );

ALTER TABLE members  ADD CONSTRAINT members_member_id_pk PRIMARY KEY (member_id);

ALTER TABLE members ADD CONSTRAINT members_campus_id_fk FOREIGN KEY (campus_id) REFERENCES campuses;

ALTER TABLE members ADD CONSTRAINT members_position_id_fk FOREIGN KEY (position_id) REFERENCES positions;

4) CREATE TABLE prices ( food_item_type_id  Varchar2(5) , meal_type Varchar2(100),meal_price  decimal(7,2));

ALTER TABLE prices ADD CONSTRAINT prices_food_item_type_id_pk PRIMARY KEY (food_item_type_id);

CREATE SEQUENCE prices_food_item_type_id_SEQ  START WITH 1 INCREMENT BY 1 ;

5) CREATE TABLE food_items ( food_item_id Varchar2(5), food_item_name Varchar2(100), food_item_type_id Varchar2(5));

ALTER TABLE food_items ADD CONSTRAINT food_items_food_item_id_pk PRIMARY KEY (food_item_id);

ALTER TABLE food_items ADD CONSTRAINT food_items_food_item_id_fk  FOREIGN KEY (food_item_type_id) REFERENCES prices;

6) CREATE TABLE food_orders (order_id Varchar2(5), member_id Varchar2(5),order_date Varchar2(25));

ALTER TABLE food_orders ADD CONSTRAINT orders_order_id_pk PRIMARY KEY (order_id);

ALTER TABLE food_orders ADD CONSTRAINT food_orders_member_id_fk FOREIGN KEY (member_id)                     REFERENCES members;

7) CREATE TABLE food_order_lines(order_id Varchar2(5),food_items_id Varchar2(5),quantity INTEGER);

ALTER TABLE food_order_lines ADD  CONSTRAINT food_order_lines_order_food_id_pk PRIMARY KEY (order_id, food_items_id);

ALTER TABLE food_order_lines ADD CONSTRAINT food_orders_order_id_fk FOREIGN KEY (order_id) REFERENCES food_orders;

ALTER TABLE food_order_lines ADD CONSTRAINT food_orders_food_items_id_fk FOREIGN KEY (food_items_id)

REFERENCES food_items(food_item_id);          

PART 2 -> INSERTING DATA

CAMPUS

INSERT INTO campuses VALUES ('1', 'IUPUI', '425 University Blvd.','Indianapolis', 'IN', '46202', '317-274-4591',.08 );

INSERT INTO campuses VALUES ('2', 'Indiana University', '107 S. Indiana Ave.','Bloomington', 'IN', '47405', '812-855-4848',.07 );

INSERT INTO campuses VALUES ('3', 'Purdue University', '475 Stadium Mall Drive','West Lafayette', 'IN', '47907', '765-494-1776',.06 );

Position

INSERT INTO positions VALUES ('1', 'Lecturer', 1050.50);

INSERT INTO positions VALUES ('2', 'Associate Professor', 900.50);

INSERT INTO positions VALUES ('3', 'Assistant Professor', 875.50);

INSERT INTO positions VALUES ('4', 'Professor', 700.75);

INSERT INTO positions VALUES ('5', 'Full Professor', 500.50);

Members

INSERT INTO members VALUES ('1', 'Ellen', 'Monk', '009 Purnell', '812-123-1234', '2', '5', 12);

INSERT INTO members VALUES ('2', 'Joe', 'Brady', '008 Statford Hall', '765-234-2345', '3', '2', 10);

INSERT INTO members VALUES ('3', 'Dave', 'Davidson', '007 Purnell', '812-345-3456', '2', '3', 10);

INSERT INTO members VALUES ('4', 'Sebastian', 'Cole', '210 Rutherford Hall', '765-234-2345', '3', '5', 10);

INSERT INTO members VALUES ('5', 'Michael', 'Doo', '66C Peobody', '812-548-8956', '2', '1', 10);

INSERT INTO members VALUES ('6', 'Jerome', 'Clark', 'SL 220', '317-274-9766', '1', '1', 12);

INSERT INTO members VALUES ('7', 'Bob', 'House', 'ET 329', '317-278-9098', '1', '4', 10);

INSERT INTO members VALUES ('8', 'Bridget', 'Stanley', 'SI 234', '317-274-5678', '1', '1', 12);

INSERT INTO members VALUES ('9', 'Bradley', 'Wilson', '334 Statford Hall', '765-258-2567', '3', '2', 10);

PRICES

INSERT INTO prices VALUES (prices_food_item_type_id_SEQ.NEXTVAL, 'Beer/Wine', 5.50);

INSERT INTO Prices VALUES (prices_food_item_type_id_SEQ.NEXTVAL, 'Dessert', 2.75);

INSERT INTO Prices VALUES (prices_food_item_type_id_SEQ.NEXTVAL, 'Dinner', 15.50);

INSERT INTO Prices VALUES (prices_food_item_type_id_SEQ.NEXTVAL, 'Soft Drink', 2.50);

INSERT INTO Prices VALUES (prices_food_item_type_id_SEQ.NEXTVAL, 'Lunch', 7.25);

FOOD_ITEMS

INSERT INTO food_items VALUES ('10001', 'Lager', '1');

INSERT INTO food_items VALUES ('10002', 'Red Wine', '1');

INSERT INTO food_items VALUES ('10003', 'White Wine', '1');

INSERT INTO food_items VALUES ('10004', 'Coke', '4');

INSERT INTO food_items VALUES ('10005', 'Coffee', '4');

INSERT INTO food_items VALUES ('10006', 'Chicken a la King', '3');

INSERT INTO food_items VALUES ('10007', 'Rib Steak', '3');

INSERT INTO food_items VALUES ('10008', 'Fish and Chips', '3');

INSERT INTO food_items VALUES ('10009', 'Veggie Delight', '3');

INSERT INTO food_items VALUES ('10010', 'Chocolate Mousse', '2');

INSERT INTO food_items VALUES ('10011', 'Carrot Cake', '2');

INSERT INTO food_items VALUES ('10012', 'Fruit Cup', '2');

INSERT INTO food_items VALUES ('10013', 'Fish and Chips', '5');

INSERT INTO food_items VALUES ('10014', 'Angus and Chips', '5');

INSERT INTO food_items VALUES ('10015', 'Cobb Salad', '5');

FOOD_ORDERS

INSERT INTO food_orders VALUES ( '1', '9', 'March 5, 2005' );

INSERT INTO food_orders VALUES ( '2', '8', 'March 5, 2005' );

INSERT INTO food_orders VALUES ( '3', '7', 'March 5, 2005' );

INSERT INTO food_orders VALUES ( '4', '6', 'March 7, 2005' );

INSERT INTO food_orders VALUES ( '5', '5', 'March 7, 2005' );

INSERT INTO food_orders VALUES ( '6', '4', 'March 10, 2005' );

INSERT INTO food_orders VALUES ( '7', '3', 'March 11, 2005' );

INSERT INTO food_orders VALUES ( '8', '2', 'March 12, 2005' );

INSERT INTO food_orders VALUES ( '9', '1', 'March 13, 2005' );

ORDER_LINE

INSERT INTO food_order_lines VALUES ( '1', '10001', 1 );

INSERT INTO food_order_lines VALUES ( '1', '10006', 1 );

INSERT INTO food_order_lines VALUES ( '1', '10012', 1 );

INSERT INTO food_order_lines VALUES ( '2', '10004', 2 );

INSERT INTO food_order_lines VALUES ( '2', '10013', 1 );

INSERT INTO food_order_lines VALUES ( '2', '10014', 1 );

INSERT INTO food_order_lines VALUES ( '3', '10005', 1 );

INSERT INTO food_order_lines VALUES ( '3', '10011', 1 );

INSERT INTO food_order_lines VALUES ( '4', '10005', 2 );

INSERT INTO food_order_lines VALUES ( '4', '10004', 2 );

INSERT INTO food_order_lines VALUES ( '4', '10006', 1 );

INSERT INTO food_order_lines VALUES ( '4', '10007', 1 );

INSERT INTO food_order_lines VALUES ( '4', '10010', 2 );

INSERT INTO food_order_lines VALUES ( '5', '10003', 1 );

INSERT INTO food_order_lines VALUES ( '6', '10002', 2 );

INSERT INTO food_order_lines VALUES ( '7', '10005', 2 );

INSERT INTO food_order_lines VALUES ( '8', '10005', 1 );

INSERT INTO food_order_lines VALUES ( '8', '10011', 1 );

INSERT INTO food_order_lines VALUES ( '9', '10001', 1 );