Solution Manual For Oracle 12c: SQL, 3rd Edition

Preview (16 of 69 Pages)

100%

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 1 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 11Chapter 1 SolutionsReview Questions1.What is the purpose of an E-Rmodel?To identify the entities about which the databaseshould store data and the relationshipsamong those entities.2.What is an entity?An entity is any person, place, or thing having attributes, orcharacteristics, of interest to the organization.3.Give an example of three entities that might exist in a database for a medical office and someattributes that would be stored in a table for each entity.Doctor:name, address, SocialSecurity Number, medical ID number; Patient:name, address, Social Security Number,insurance policy information, medical history; Appointment:date, time, patient, doctor.4.Define a one-to-many relationship.An occurrence of data in one entitycanresult inzero,one,ormanyoccurrencesofthedata in the other entity.Zero(or no)related records canoccuronlyin optional relationships.5.Discuss the problems that can be caused by data redundancy.Can create data anomalies orinconsistencies in the data,making it unreliable.6.Explain the role of a primary key.The primary key is used to uniquely identify each row in atable.7.Describe how aforeign keyis different from a primary key.A foreign key is used toreference or join data in different tables. In most cases, the foreign key references a primarykey in another table. In a one-to-many relationship, the foreign key is stored in the “many”entity.8.List the steps of the normalization process.First, a primary key is identified and anyrepeating groups are identified(1NF).Second,any partial dependencies are eliminated(2NF).Third, any transitive dependencies are eliminated(3NF).9.What type of relationship can’t be stored in a database? Why?A many-to-many relationshipcan’t be stored in a databasebecausethere would be no way to restructure or rejoin the datacorrectly.10.Identify at least three reasons an organization might analyze historical sales data stored inits database.Answers will vary.To determine the necessary inventory levels to support salesfluctuations,toproject employee-scheduling requirements,todetermine appropriatemarketing campaigns based on historic purchasing patterns, and so forth.

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 2 preview image

Loading page ...

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 3 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 12Multiple Choice1. d2. b3. d4. a5. c6. a7. a8. d9. d10. c11. b12. b13. a14. b15. b16. d17. c18. c19. a20. cHands-On Assignments1.Which tables and fields would you access to determinewhich booktitles have beenpurchasedby a customer and when the order shipped?CUSTOMERS:Customer#;ORDERS:Order#,Shipdate,Customer#; ORDERITEMS:Order#, ISBN; BOOKS: ISBN,Title2.How would you determine which orders have not yet been shipped to the customer?Identifyall orders that don’t have an entry for the date shipped.3.If management needed to determine which book category generated the most sales in April2009, which tables and fields wouldtheyconsult to derive this information?ORDERS:Orderdate,Order#; ORDERITEMS:Order#, ISBN,Quantity, Paideach; BOOKS: ISBN,Category4.Explain howyou woulddetermine how much profit was generated from ordersplaced inApril 2009.Determine the amount of profit generated by each book on an order item(Paideach-Cost), multiply the profit for each book by the quantity purchased,andthen totalthe amountof profit generated by all orders placed in April.

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 4 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 135.If a customer inquired about a book written in2003by an author named Thompson, whichaccess path (tables and fields) would you need to follow to find the list of books meeting thecustomer’s request?AUTHOR: Lname, AuthorID; BOOKAUTHOR: AuthorID, ISBN;BOOKS: ISBN, Pubdate.6.A college needs to track placement test scores for all incoming students. Each studentcantake a variety of tests,including English andmath. Some studentsarerequired to takeplacement testsbecause ofprevious coursework.StudentsTests7.Every employee in a company is assigned to one department. Every department can containmany employees.DepartmentsEmployees8.A movie megaplex needs to collect and analyze movie attendance data. The companymaintains16theaters in a single location. Each movie offeredcanbe shown in one or moreof the available theaters and is typically scheduled forthree to six showings in a day. Themovies are rotated through the theaters to ensurethateach is shown in one of the stadium-seating theaters at least once.MoviesShowingsTheaters9.An online retailer of coffee beans maintains a long list of unique coffee flavors.The companypurchases beans from a number of suppliers; however, each specific flavor of coffee ispurchased from only a single supplier.Many of the customers are repeat purchasers andtypically order at least fiveflavorsof beansin each order.SuppliersProductsOrder_itemsOrdersCustomers

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 5 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 1410.Data for an information technology conference needs to be collected. The conference has avariety of sessions scheduled over a two-day period.Allattendeesmust register for thesessionstheyplan to attend. Some speakers are presentingonlyone session,whereasothersare handling multiple sessions. Each session has only one speaker.SpeakersSessionsRegistrationsAttendeesAdvanced ChallengeResults of the normalization process will vary,depending on the assumptions made by thestudent.Unnormalized:first name, last name, billing address, quantity, retail price, shipping address, order date, shipdate1NF:CUSTOMERS:customer #, first name, last name, billing addressORDERS:order #, shipping address,quantity,retail price, order date, ship date2NF:CUSTOMERS:customer #, first name, last name, billing addressORDERS:order #, shipping address, order date, ship dateORDERITEMS:order #,item#, quantity, retail price, ISBN3NF:CUSTOMERS:customer #, first name, last name, billing addressORDERS:order #, shipping address, order date, ship date

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 6 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 15ORDERITEMS:order #,item#, quantity, ISBNBOOKS: ISBN, retail priceCase Study: City JailTheappearance of the E-Rmodelwill vary depending on the notations or modeling softwarestudentsare using.An example is shownon the next page.Additionalentities and/or attributes: Answers will vary greatly.AJailsentityis anexample ofapossibleadditional entity. Image items,such as a criminal photo and fingerprints,are examplesof additional attributes thatmightbe required.

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 7 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 16Criminal_IDLastFirstStreetCityStateZipPhoneV_statusP_statusCriminalsAlias_IDCriminal_IDAliasCity Jail Database E-RModelAliasesCrime_IDCriminal_IDClassificationDate_chargedStatusHearing_dateAppeal_cut_dateCrimesAppeal_IDCrime_IDFiling_dateHearing_dateStatusAppealsOfficer_IDLastFirstPrecinctBadgePhoneStatusOfficersCrime_IDOfficer_IDSentence_IDCriminal_IDTypeProb_IDStart_dateEnd_dateViolationsCrime_officersSentencesCrime_chargesCharge_IDCrime_IDCrime_codeCharge_statusFine_amountCourt_feeAmount_paidPay_due_dateProb_IDLastFirstStreetCityStateZipPhoneEmailStatusProb_OfficersCrime_codeCode_descriptionCrime_codes

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 8 preview image

Loading page ...

Oracle12cSQL, ISBN9781305251038Ch. 21Chapter 2 SolutionsReview Questions1.What is adata dictionary?A collection of objects the DBMSmanagesto maintain information aboutthe database,such as table names, column names,and column data types. This information is oftenreferred as metadata.2.What are the two required clauses for a SELECT statement?SELECT and FROM3.What is the purpose of the SELECT statement?It’s used to retrieve data from database tables.4.What does the use of an asterisk (*) in the SELECT clause of a SELECT statement represent?Allcolumnsin the referenced table5.What is the purpose of a column alias?Provides another name for a columnthat’sdisplayed as thecolumn heading in the output6.How do you indicate that a column alias should be used?Include the AS keyword followed by thealias, or list the alias immediately after the column name without a separating comma.7.When is it appropriate to use a column alias?To provide a more descriptive column heading8.What are the guidelines to keep in mind when using a column alias?If the column alias includes ablank spaceorspecial symbols, or should retain the specifiedlettercase, it must be enclosed inquotation marks.9.How can you concatenate columns in a query?Separate the column names with two vertical bars ( || )rather than a comma.10.What is a NULL value?A NULL value indicates an absence of value.If no value was placed in a fieldof a row, the field value is empty or NULL.Multiple Choice1. c2. d3. d4. b5. b6. c7. c8. c9. d10. d11. a12. c

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 9 preview image

Loading page ...

Oracle12cSQL, ISBN9781305251038Ch. 2213. d14. d15. c16. a17. a18. b19.d20. bHands-OnAssignments1.SELECT *FROM books;2.SELECT titleFROM books;3.SELECT title, pubdate "Publication Date"FROM books;4.SELECT customer#, city, stateFROM customers;5.SELECT name, contact "Contact Person", phoneFROM publisher;6.SELECT DISTINCT categoryFROM books;orSELECT UNIQUE categoryFROM books;7.SELECT DISTINCT customer#FROM orders;orSELECT UNIQUE customer#FROM orders;

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 10 preview image

Loading page ...

Oracle12cSQL, ISBN9781305251038Ch. 238.SELECT category, titleFROM books;9.SELECT lname || ', ' || fnameFROM author;10.SELECT order#, item#, isbn, quantity, paideach, quantity*paideach "Item Total"FROM orderitems;Advanced Challenge1.SELECT lastname || ', ' || firstname "Name", address, city || ', ' || state "Location", zipFROM customers;2.SELECT title, (retail-cost)/cost*100 "Profit %"FROM books;Case Study:City JailResumes in Chapter 3.

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 11 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 31Chapter3SolutionsReview Questions1.Which command is used to create a table based on data already contained in anexisting table?CREATE TABLE... AScommand with a subquery2.List four datatypes supported by Oracle12c, and provide an example of data thatcould be stored by each datatype.DATE:20-JAN-09, VARCHAR2:HELLO,NUMBER:5.23, CHAR:VA3.What guidelines shouldyoufollow when naming tables and columns in Oracle11g?A maximum of 30 charactersisallowed, the first character must be a letter, noblank spaces or special symbols other thantheunderscore and dollar sign can beincluded, andreservedwords can’t be used as a table or column name.4.What is the difference between dropping a column and setting a column as unused?When dropped, the storage space is released immediately. When set as unused, thestorage space isn’t released until later.5.How many columns can be dropped in one ALTER TABLE command?Only one at atime6.What happens to the existing rows of a table if the DEFAULT value of a column ischanged?Nothing;they aren’t affected.7.Explain the difference between truncating a table and deleting a table.Truncating atable removesall data but retainsthe table structure;deleting (dropping) a tableremovesdataandthe table structure from the database.8.If you add a new column to an existing table, wheredoesthe column appearinrelationto existing columns?It appearsas the last column in the table.9.What happens if you try to decrease the scale or precision of a NUMBER column toa value less than the data already stored in the field?You can’t change the scale orprecision of a NUMBER column containingdata.10.Area table and the datacontained in the table erased from the systempermanentlyif a DROPTABLE command is issued on the table?The table is erasedpermanentlyonly if the PURGE option is used in the DROP TABLE command. Otherwise, thetableismoved to the recyclebin.Multiple Choice1. c2. c3. a4. b5. d6. d

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 12 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 327. b8. d9. b10. a11. d12. a13. a14.b15. d16. d17. b18. d19.c20. bHands-OnAssignments1.CREATE TABLE category(catcode VARCHAR2(2),catdesc VARCHAR2(10));2.CREATE TABLEemployees(emp#NUMBER(5),lastnameVARCHAR2(15),firstname VARCHAR2(10),job_class VARCHAR2(4));3.ALTER TABLE employeesADD(empdate DATE DEFAULT SYSDATE,enddate DATE);4.ALTER TABLEemployeesMODIFYjob_classVARCHAR2(2);5.ALTER TABLE employeesDROP column enddate;6.RENAMEemployeesTOjl_emps;

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 13 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 337.CREATE TABLE book_pricing (id, cost, retail, category)AS (SELECT isbn, cost, retail, categoryFROM books);8.ALTER TABLEbook_pricingSET UNUSED (category);SELECT *FROM book_pricing;9.TRUNCATE TABLEbook_pricing;SELECT *FROM book_pricing;10.DROP TABLEbook_pricing PURGE;DROP TABLE jl_emps;FLASHBACK TABLE jl_empsTO BEFORE DROP;SELECT *FROM jl_emps;Advanced ChallengeALTER TABLEacctmanagerADD(comm_id NUMBER(2) DEFAULT 10,Ben_id NUMBER(2));CREATE TABLE commrate(comm_idNUMBER(2),comm_rank VARCHAR2(15),rate NUMBER(2,2));CREATE TABLE benefits(ben_id NUMBER(2),ben_plan CHAR(1),ben_provider NUMBER(3),active CHAR(1));Case Study:City Jail

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 14 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 34Notes:In Section A, students need to developCREATE TABLEstatements for each table listed.The listing indicates DEFAULT column values whenapplicable. The listing “Codingkeyfor selected columns” is providedonlyto help studentsvisualize what type of data will beentered in selected code columns. Thisinformationwill be a valuable reference forperformingqueries in later chapters.In Section B, students need to use the ALTER TABLE command to make the requestedtable modifications.Warning: If you modify this assignment,consider howitwill affect case studyassignments insubsequentchapters. Recall that the City Jail Case Study is a cumulativecase used throughout the textbook.Section ACREATE TABLE aliases(alias_id NUMBER(6),criminal_id NUMBER(6),alias VARCHAR2(10));CREATE TABLE criminals(criminal_id NUMBER(6),last VARCHAR2(15),first VARCHAR2(10),street VARCHAR2(30),city VARCHAR2(20),state CHAR(2),zip CHAR(5),phone CHAR(10),v_status CHAR(1) DEFAULT 'N',p_status CHAR(1) DEFAULT 'N' );CREATE TABLE crimes(crime_id NUMBER(9),criminal_id NUMBER(6),classification CHAR(1),date_charged DATE,status CHAR(2),hearing_date DATE,appeal_cut_date DATE);CREATE TABLE sentences(sentence_id NUMBER(6),criminal_id NUMBER(6),type CHAR(1),prob_id NUMBER(5),

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 15 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 35start_date DATE,end_date DATE,violations NUMBER(3));CREATE TABLE prob_officers(prob_id NUMBER(5),last VARCHAR2(15),first VARCHAR2(10),street VARCHAR2(30),city VARCHAR2(20),state CHAR(2),zip CHAR(5),phone CHAR(10),email VARCHAR2(30),status CHAR(1) DEFAULT 'A' );CREATE TABLE crime_charges(charge_id NUMBER(10),crime_id NUMBER(9),crime_code NUMBER(3),charge_status CHAR(2),fine_amount NUMBER(7,2),court_fee NUMBER(7,2),amount_paid NUMBER(7,2),pay_due_date DATE);CREATE TABLE crime_officers(crime_id NUMBER(9),officer_id NUMBER(8));CREATE TABLE officers(officer_id NUMBER(8),last VARCHAR2(15),first VARCHAR2(10),precinct CHAR(4),badge VARCHAR2(14),phone CHAR(10),status CHAR(1) DEFAULT 'A' );CREATE TABLE appeals(appeal_id NUMBER(5),crime_id NUMBER(9),filing_date DATE,hearing_date DATE,status CHAR(1) DEFAULT 'P' );

Solution Manual For Oracle 12c: SQL, 3rd Edition - Page 16 preview image

Loading page ...

Oracle12cSQL, ISBN:9781305251038Ch. 36CREATE TABLE crime_codes(crime_code NUMBER(3),code_description VARCHAR2(30));SectionBALTER TABLE crimesMODIFY (classification DEFAULT 'U');ALTER TABLE crimesADD (date_recorded DATE DEFAULT SYSDATE);ALTER TABLE prob_officersADD (pager# CHAR(10));ALTER TABLE aliasesMODIFY (alias VARCHAR2(20));

Preview Mode

This document has 69 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

Class Notes For Oracle 12c: SQL, 3rd Edition

DevOps and Its Importance Study Notes

Veeam Certified Engineer VMCE-V12

Modules 1 - 3 Basic Network Connectivity and Commu

Notes CCNA

CCNA 200-301 Portable Command Guide 5th Edition 20

CCNA 200-301 Portable Command Guide 5th Edition 22

CCNA 200-301 Portable Command Guide 5th Edition 36

Foundations of Novell Networking Section 26

Modulo 7 DHCPv4

Company

Explore

Study Tools