Starter Text File:

create table Worker ( WID number (9) Constraint pk_Wkr primary key, First_Name varchar2 (30) constraint nn_FN not null, Last_Name varchar2 (30) constraint nn_LN not null, MI varchar2 (1), Gender varchar2 (1) constraint nn_gen not null, constraint ck_gen check (Gender in ('M', 'F')), BDate date, Address varchar2 (50), Salary number (8) constraint ck_sal check (Salary between 30000 and 70000), FranID number (9), constraint un_wfranid UNIQUE (FranID), FID number (3), constraint un_emp unique (First_Name, MI, Last_Name) ); create table Franchise ( FID number (3) constraint pk_franchise primary key, constraint ck_fid check (FID between 1 and 10), Franchise_Name varchar2 (55) constraint ck_fname check (Franchise_name in ('McDonDon', 'Wendels', 'Burger Queen')), constraint un_fname unique (Franchise_name), FranID number (9), constraint un_franid UNIQUE (FranID), Fran_Start_Date date ); create table Location ( LocID number (9) constraint pk_loc primary key, Location_Name varchar2 (50) constraint ck_loc check (Location_Name in ('Santa Monica', 'OTR', 'Kansas', 'Ontario')), Value number (9) ); create table Own ( FID number (3), LocID number (9), constraint pk_own primary key (FID, LocID), constraint fk_loco foreign key (LocID) references Location (LocID), constraint fk_fo foreign key (FID) references Franchise (FID) ); create table Food ( FoID number (9) constraint pk_foID primary key, constraint ck_foID check (FoID between 1 and 30), Food_name varchar2 (50), FID number (9), constraint un_foodname unique (Food_Name) ); create table Toy ( WID number (9), ToyID number (9), Released varchar2 (1) constraint nn_rel not null,

constraint ck_rel check (Released in ('Y', 'N')), Date_released date, Type_of_Toy varchar2 (30) constraint ck_toy check (Type_of_Toy in ('Car', 'Plane', 'Slime')), constraint pk_toy primary key (WID, ToyID) ); create table Cook ( WID number (9), FoID number (9), Cook_mins number (9) constraint ck_cookmin check (Cook_mins between 1 and 50), constraint pk_design primary key (WID, FoID) ); insert all into Worker values (123456789, 'Terry', 'Lawson', 'B', 'M', '1-Jul-24', '10 Pass Lane', 32000, null, 2) into Worker values (234567891, 'Melinda', 'Bradshaw', 'J', 'F', '14-Feb- 92', '1 Red Road', 40000, null, 1) into Worker values (345678912, 'Joey', 'Smith', 'P', 'M', '7-Jan-64', 'Independence Way', 41000, 234567891, 1) into Worker values (456789123, 'Sam', 'Po', null, 'M', '4-Aug-02', '1919 Bearcat Ln', 53000, 123456789,3) into Worker values (567891234, 'Bert', 'Segura', 'L', 'M', '12-Jan-76', '846 Ossuary Dr.', 38000, null, 2) into Worker values (678912345, 'Lexis', 'Caleb', 'A', 'F', '1-Jan-76', '4321 Dorado Dr.', 60000, null, 1) into Worker values (789123456, 'Don', 'Walter', 'W', 'M', '23-Jan-40', '221 Hawk Landing', 32000, 567891234, 2) select * from dual; insert all into Franchise values (1, 'McDonDon', 123456789, '25-Dec-39') into Franchise values (2, 'Wendels', 234567891, '5-Nov-16') into Franchise values (3, 'Burger Queen', 567891234, '13-Sep-05') select * from dual; insert all into Location values (1, 'Santa Monica', 483333) into Location values (2, 'OTR', 123123) into Location values (3, 'Kansas', 747200) into Location values (4, 'Ontario', 790809) select * from dual; insert all into Own values (1, 1) into Own values (1, 2) into Own values (2, 3) into Own values (3, 4) select * from dual; insert all into Food values (1, 'Burger', 2)

into Food values (6, 'Snack Wrap', 1) into Food values (8, 'Chicken Tenders', 2) into Food values (9, 'Baked Potato', 3) into Food values (15, 'Chicken Sandwich', 1) into Food values (29, 'Tacos', 3) select * from dual; insert all into Toy values (123456789, 13467913, 'Y', '2-Aug-15', 'Car') into Toy values (234567891, 24576835, 'Y', '2-Jan-22', 'Car') into Toy values (345678912, 12435768, 'N', null, 'Car') into Toy values (456789123, 98675342, 'Y', '4-Apr-09', 'Plane') into Toy values (567891234, 78495216, 'N', null, 'Slime') into Toy values (567891234, 36295184, 'N', null, 'Slime') select * from dual; insert all into Cook values (123456789, 1, 32) into Cook values (123456789, 6, 10) into Cook values (234567891, 6, 22) into Cook values (234567891, 8, 20) into Cook values (345678912, 8, 24) into Cook values (345678912, 9, 36) into Cook values (456789123, 9, 10) into Cook values (456789123, 15, 22) into Cook values (567891234, 15, 31) into Cook values (567891234, 29, 49) into Cook values (678912345, 29, 23) into Cook values (789123456, 1, 21) select * from dual; alter table Worker add constraint fk_franchisee foreign key (FranID) references Worker (WID); alter table Worker add constraint fk_franchise foreign key (FID) references Franchise (FID); alter table Worker add constraint ck_WID check (WID FranID); alter table Franchise add constraint fk_franid foreign key (FranID) references Worker (FranID); alter table Food add constraint fk_FID foreign key (FID) references Franchise (FID) ON DELETE CASCADE; alter table Toy add constraint fk_TWID foreign key (WID) references Worker (WID) ON DELETE CASCADE; alter table Cook add constraint fk_CWID foreign key (WID) references Worker (WID);

alter table Cook add constraint fk_FoID foreign key (FoID) references Food

Part 1A (10 points): To begin, you'll need to drop all tables that are currently in your Oracle account besides the tables from Lab Assignment 4. - View all tables currently in your account using the statement SELECT TABLE NAME FROM USER TABLES; Then, delete each table using a drop statement: DROP TABLE CASCDE CONSTRAINTS; Part 1B (10 points): You are tasked with updating the extensive company database in Oracle to incorporate the following changes. It is industry standard to save all your work in notepad or by equivalent means in order to reference later. You will need to make changes to the database before it will allow you to enter data, so decide what needs to be done and in what order. Changes made directly to the original code provided (database structure start script) will NOT receive credit. 1. On January 10 of this year, the company hired a new worker: Betty Wood with a salary of $125,000 and a WID \& FranID of 230132678. She was born on June 9, 1993 and her Franchise is a newly built, September 10, 2022, "Bobs Burgers" (FID =11), located in Vancouver (LocID =5), which sells the newly created 'Cheeseburger' (FoID=31). 2. Currently, Betty is working on a new type of food called "Bacon" (FoID=32), which "Bobs Burgers" is responsible for. She has decided narrowed down the cook time to roughly 83 minutes and hopes that everyone else will be able to cook it in a similar amount of time. Betty is planning to charge a lot for this item, and many others, given the extreme value of the location of Vancouver of $511,943. 3. Even though you work in database management, you have a strong interest in the work of fellow employees, so you ask for details regarding's Betty's toys given out. Betty has given out two "Plane" toys (ToyID 22244485 \& 81122234). 22244485 was released on March 14, 2012, while 81122234 was written 3 years before the day Betty was hired. Betty is also hoping to release a slime toy (34134112), but it hasn't been released yet. Betty created all of the toys in her home on 1234MainSt ). Part 2 (20 points): Using the "Company database" that you have created in Oracle, construct queries in SOL (ANSI 1991) to answer the following questions and execute the queries on the Oracle platform (4 points each). DML: 1. Retrieve the First name, Middle initial, Last name, Birthdate, Address, and WID of the workers along with their type of toys. The list should include ALL workers. (Use Outer join) 2. List the WID of workers who don't have any toys. a. Demonstrate the use of the MINUS operator in this query. b. Write the query without using the MINUS operator. 3. List the WID of workers who are franchisers (they own a franchise). a. Demonstrate the use of INTERSECT operator in this query. b. Write the query without using the INTERSECT operator. 4. Retrieve the name and address of all workers; if the worker is a franchiser, include the name of their franchise. If the worker has toys, include the date of release a. Demonstrate the use of outer join operation in this query. -if an employee has multiple research papers it is okay for their name to be in multiple records 5. List the Toy ID of the toys whose owner created "Snack Wrap". Deliverables: - Your SQL work for Part 1 in a notepad text file showing the changes made to the database and the proper data entries made. - Either using the spool function, spool each query for Part 2 and its results immediately following the query, or copy your query results in a notepad text file after each query. Part 1A (10 points): To begin, you'll need to drop all tables that are currently in your Oracle account besides the tables from Lab Assignment 4. - View all tables currently in your account using the statement SELECT TABLE NAME FROM USER TABLES; Then, delete each table using a drop statement: DROP TABLE CASCDE CONSTRAINTS; Part 1B (10 points): You are tasked with updating the extensive company database in Oracle to incorporate the following changes. It is industry standard to save all your work in notepad or by equivalent means in order to reference later. You will need to make changes to the database before it will allow you to enter data, so decide what needs to be done and in what order. Changes made directly to the original code provided (database structure start script) will NOT receive credit. 1. On January 10 of this year, the company hired a new worker: Betty Wood with a salary of $125,000 and a WID \& FranID of 230132678. She was born on June 9, 1993 and her Franchise is a newly built, September 10, 2022, "Bobs Burgers" (FID =11), located in Vancouver (LocID =5), which sells the newly created 'Cheeseburger' (FoID=31). 2. Currently, Betty is working on a new type of food called "Bacon" (FoID=32), which "Bobs Burgers" is responsible for. She has decided narrowed down the cook time to roughly 83 minutes and hopes that everyone else will be able to cook it in a similar amount of time. Betty is planning to charge a lot for this item, and many others, given the extreme value of the location of Vancouver of $511,943. 3. Even though you work in database management, you have a strong interest in the work of fellow employees, so you ask for details regarding's Betty's toys given out. Betty has given out two "Plane" toys (ToyID 22244485 \& 81122234). 22244485 was released on March 14, 2012, while 81122234 was written 3 years before the day Betty was hired. Betty is also hoping to release a slime toy (34134112), but it hasn't been released yet. Betty created all of the toys in her home on 1234MainSt ). Part 2 (20 points): Using the "Company database" that you have created in Oracle, construct queries in SOL (ANSI 1991) to answer the following questions and execute the queries on the Oracle platform (4 points each). DML: 1. Retrieve the First name, Middle initial, Last name, Birthdate, Address, and WID of the workers along with their type of toys. The list should include ALL workers. (Use Outer join) 2. List the WID of workers who don't have any toys. a. Demonstrate the use of the MINUS operator in this query. b. Write the query without using the MINUS operator. 3. List the WID of workers who are franchisers (they own a franchise). a. Demonstrate the use of INTERSECT operator in this query. b. Write the query without using the INTERSECT operator. 4. Retrieve the name and address of all workers; if the worker is a franchiser, include the name of their franchise. If the worker has toys, include the date of release a. Demonstrate the use of outer join operation in this query. -if an employee has multiple research papers it is okay for their name to be in multiple records 5. List the Toy ID of the toys whose owner created "Snack Wrap". Deliverables: - Your SQL work for Part 1 in a notepad text file showing the changes made to the database and the proper data entries made. - Either using the spool function, spool each query for Part 2 and its results immediately following the query, or copy your query results in a notepad text file after each query.Not the question youre looking for?Post any question and get expert help quickly.Start learning Chegg Products & ServicesChegg Study HelpCitation GeneratorDigital Access CodesGrammar CheckerMath SolverMobile AppsSolutions ManualPlagiarism CheckerChegg PerksCompanyCompanyAbout CheggChegg For GoodCollege MarketingInvestor RelationsJobsJoin Our Affiliate ProgramMedia CenterSite MapChegg NetworkChegg NetworkBusuuCitation MachineEasyBibMathwayThinkfulCustomer ServiceCustomer ServiceGive Us FeedbackCustomer ServiceManage SubscriptionEducatorsEducatorsAcademic IntegrityHonor ShieldInstitute of Digital Learning 2003-2024 Chegg Inc. All rights reserved.Cookie NoticeYour Privacy ChoicesDo Not Sell My InfoGeneral PoliciesPrivacy Policy (New)Honor CodeIP Rights Part 1A (10 points): To begin, you'll need to drop all tables that are currently in your Oracle account besides the tables from Lab Assignment 4. - View all tables currently in your account using the statement SELECT TABLE NAME FROM USER TABLES; Then, delete each table using a drop statement: DROP TABLE CASCDE CONSTRAINTS; Part 1B (10 points): You are tasked with updating the extensive company database in Oracle to incorporate the following changes. It is industry standard to save all your work in notepad or by equivalent means in order to reference later. You will need to make changes to the database before it will allow you to enter data, so decide what needs to be done and in what order. Changes made directly to the original code provided (database structure start script) will NOT receive credit. 1. On January 10 of this year, the company hired a new worker: Betty Wood with a salary of $125,000 and a WID \& FranID of 230132678. She was born on June 9, 1993 and her Franchise is a newly built, September 10, 2022, "Bobs Burgers" (FID =11), located in Vancouver (LocID =5), which sells the newly created 'Cheeseburger' (FoID=31). 2. Currently, Betty is working on a new type of food called "Bacon" (FoID=32), which "Bobs Burgers" is responsible for. She has decided narrowed down the cook time to roughly 83 minutes and hopes that everyone else will be able to cook it in a similar amount of time. Betty is planning to charge a lot for this item, and many others, given the extreme value of the location of Vancouver of $511,943. 3. Even though you work in database management, you have a strong interest in the work of fellow employees, so you ask for details regarding's Betty's toys given out. Betty has given out two "Plane" toys (ToyID 22244485 \& 81122234). 22244485 was released on March 14, 2012, while 81122234 was written 3 years before the day Betty was hired. Betty is also hoping to release a slime toy (34134112), but it hasn't been released yet. Betty created all of the toys in her home on 1234MainSt ). Part 2 (20 points): Using the "Company database" that you have created in Oracle, construct queries in SOL (ANSI 1991) to answer the following questions and execute the queries on the Oracle platform (4 points each). DML: 1. Retrieve the First name, Middle initial, Last name, Birthdate, Address, and WID of the workers along with their type of toys. The list should include ALL workers. (Use Outer join) 2. List the WID of workers who don't have any toys. a. Demonstrate the use of the MINUS operator in this query. b. Write the query without using the MINUS operator. 3. List the WID of workers who are franchisers (they own a franchise). a. Demonstrate the use of INTERSECT operator in this query. b. Write the query without using the INTERSECT operator. 4. Retrieve the name and address of all workers; if the worker is a franchiser, include the name of their franchise. If the worker has toys, include the date of release a. Demonstrate the use of outer join operation in this query. -if an employee has multiple research papers it is okay for their name to be in multiple records 5. List the Toy ID of the toys whose owner created "Snack Wrap". Deliverables: - Your SQL work for Part 1 in a notepad text file showing the changes made to the database and the proper data entries made. - Either using the spool function, spool each query for Part 2 and its results immediately following the query, or copy your query results in a notepad text file after each query.Not the question youre looking for?Post any question and get expert help quickly.Start learning Chegg Products & ServicesChegg Study HelpCitation GeneratorDigital Access CodesGrammar CheckerMath SolverMobile AppsSolutions ManualPlagiarism CheckerChegg PerksCompanyCompanyAbout CheggChegg For GoodCollege MarketingInvestor RelationsJobsJoin Our Affiliate ProgramMedia CenterSite MapChegg NetworkChegg NetworkBusuuCitation MachineEasyBibMathwayThinkfulCustomer ServiceCustomer ServiceGive Us FeedbackCustomer ServiceManage SubscriptionEducatorsEducatorsAcademic IntegrityHonor ShieldInstitute of Digital Learning 2003-2024 Chegg Inc. All rights reserved.Cookie NoticeYour Privacy ChoicesDo Not Sell My InfoGeneral PoliciesPrivacy Policy (New)Honor CodeIP Rights