This Document Contains Chapters 1 to 8 Chapter 1 Solutions Review Questions 1. What is the purpose of an E-R model? To identify the entities about which the database should store data and the relationships among those entities. 2. What is an entity? An entity is any person, place, or thing having attributes, or characteristics, of interest to the organization. 3. Give an example of three entities that might exist in a database for a medical office and some attributes that would be stored in a table for each entity. Doctor: name, address, Social Security 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 entity can result in zero, one, or many occurrences of the data in the other entity. Zero (or no) related records can occur only in optional relationships. 5. Discuss the problems that can be caused by data redundancy. Can create data anomalies or inconsistencies 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 a table. 7. Describe how a foreign key is different from a primary key. A foreign key is used to reference or join data in different tables. In most cases, the foreign key references a primary key 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 any repeating 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 relationship can’t be stored in a database because there would be no way to restructure or rejoin the data correctly. 10. Identify at least three reasons an organization might analyze historical sales data stored in its database. Answers will vary. To determine the necessary inventory levels to support sales fluctuations, to project employee-scheduling requirements, to determine appropriate marketing campaigns based on historic purchasing patterns, and so forth. Multiple Choice 1. d This Document Contains Chapters 1 to 8 2. b 3. d 4. a 5. c 6. a 7. a 8. d 9. d 10. c 11. b 12. b 13. a 14. b 15. b 16. d 17. c 18. c 19. a 20. c Hands-On Assignments 1. Which tables and fields would you access to determine which book titles have been purchased by a customer and when the order shipped? CUSTOMERS: Customer#; ORDERS: Order#, Shipdate, Customer#; ORDERITEMS: Order#, ISBN; BOOKS: ISBN, Title 2. How would you determine which orders have not yet been shipped to the customer? Identify all 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 April 2009, which tables and fields would they consult to derive this information? ORDERS: Orderdate, Order#; ORDERITEMS: Order#, ISBN, Quantity, Paideach; BOOKS: ISBN, Category 4. Explain how you would determine how much profit was generated from orders placed in April 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, and then total the amount of profit generated by all orders placed in April. 5. If a customer inquired about a book written in 2003 by an author named Thompson, which access path (tables and fields) would you need to follow to find the list of books meeting the customer’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 student can take a variety of tests, including English and math. Some students are required to take placement tests because of previous coursework. 7. Every employee in a company is assigned to one department. Every department can contain many employees. 8. A movie megaplex needs to collect and analyze movie attendance data. The company maintains 16 theaters in a single location. Each movie offered can be shown in one or more of the available theaters and is typically scheduled for three to six showings in a day. The movies are rotated through the theaters to ensure that each is shown in one of the stadium- seating theaters at least once. 9. An online retailer of coffee beans maintains a long list of unique coffee flavors. The company purchases beans from a number of suppliers; however, each specific flavor of coffee is purchased from only a single supplier. Many of the customers are repeat purchasers and typically order at least five flavors of beans in each order. 10. Data for an information technology conference needs to be collected. The conference has a variety of sessions scheduled over a two-day period. All attendees must register for the sessions they plan to attend. Some speakers are presenting only one session, whereas others are handling multiple sessions. Each session has only one speaker. Advanced Challenge Results of the normalization process will vary, depending on the assumptions made by the student. Unnormalized: first name, last name, billing address, quantity, retail price, shipping address, order date, ship date 1NF: CUSTOMERS: customer #, first name, last name, billing address ORDERS: order #, shipping address, quantity, retail price, order date, ship date 2NF: CUSTOMERS: customer #, first name, last name, billing address ORDERS: order #, shipping address, order date, ship date ORDERITEMS: order #, item#, quantity, retail price, ISBN 3NF: CUSTOMERS: customer #, first name, last name, billing address ORDERS: order #, shipping address, order date, ship date ORDERITEMS: order #, item#, quantity, ISBN BOOKS: ISBN, retail price Case Study: City Jail The appearance of the E-R model will vary depending on the notations or modeling software students are using. An example is shown on the next page. Additional entities and/or attributes: Answers will vary greatly. A Jails entity is an example of a possible additional entity. Image items, such as a criminal photo and fingerprints, are examples of additional attributes that might be required. Chapter 2 Solutions Review Questions 1. What is a data dictionary? A collection of objects the DBMS manages to maintain information about the database, such as table names, column names, and column data types. This information is often referred as metadata. 2. What are the two required clauses for a SELECT statement? SELECT and FROM 3. 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? All columns in the referenced table 5. What is the purpose of a column alias? Provides another name for a column that’s displayed as the column heading in the output 6. How do you indicate that a column alias should be used? Include the AS keyword followed by the alias, 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 heading 8. What are the guidelines to keep in mind when using a column alias? If the column alias includes a blank space or special symbols, or should retain the specified letter case, it must be enclosed in quotation 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 field of a row, the field value is empty or NULL. Multiple Choice 1. c 2. d 3. d 4. b 5. b 6. c 7. c 8. c 9. d 10. d 11. a 12. c 13. d 14. d 15. c 16. a 17. a 18. b 19. d 20. b Hands-On Assignments 1. SELECT * FROM books; 2. SELECT title FROM books; 3. SELECT title, pubdate "Publication Date" FROM books; 4. SELECT customer#, city, state FROM customers; 5. SELECT name, contact "Contact Person", phone FROM publisher; 6. SELECT DISTINCT category FROM books; or SELECT UNIQUE category FROM books; 7. SELECT DISTINCT customer# FROM orders; or SELECT UNIQUE customer# FROM orders; 8. SELECT category, title FROM books; 9. SELECT lname || ', ' || fname FROM author; 10. SELECT order#, item#, isbn, quantity, paideach, quantity*paideach "Item Total" FROM orderitems; Advanced Challenge 1. SELECT lastname || ', ' || firstname "Name", address, city || ', ' || state "Location", zip FROM customers; 2. SELECT title, (retail-cost)/cost*100 "Profit %" FROM books; Case Study: City Jail Resumes in Chapter 3. Chapter 3 Solutions Review Questions 1. Which command is used to create a table based on data already contained in an existing table? CREATE TABLE ... AS command with a subquery 2. List four datatypes supported by Oracle 12c, and provide an example of data that could be stored by each datatype. DATE: 20-JAN-09, VARCHAR2: HELLO, NUMBER: 5.23, CHAR: VA 3. What guidelines should you follow when naming tables and columns in Oracle11g? A maximum of 30 characters is allowed, the first character must be a letter, no blank spaces or special symbols other than the underscore and dollar sign can be included, and reserved words 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, the storage space isn’t released until later. 5. How many columns can be dropped in one ALTER TABLE command? Only one at a time 6. What happens to the existing rows of a table if the DEFAULT value of a column is changed? Nothing; they aren’t affected. 7. Explain the difference between truncating a table and deleting a table. Truncating a table removes all data but retains the table structure; deleting (dropping) a table removes data and the table structure from the database. 8. If you add a new column to an existing table, where does the column appear in relation to existing columns? It appears as the last column in the table. 9. What happens if you try to decrease the scale or precision of a NUMBER column to a value less than the data already stored in the field? You can’t change the scale or precision of a NUMBER column containing data. 10. Are a table and the data contained in the table erased from the system permanently if a DROP TABLE command is issued on the table? The table is erased permanently only if the PURGE option is used in the DROP TABLE command. Otherwise, the table is moved to the recycle bin. Multiple Choice 1. c 2. c 3. a 4. b 5. d 6. d 7. b 8. d 9. b 10. a 11. d 12. a 13. a 14. b 15. d 16. d 17. b 18. d 19. c 20. b Hands-On Assignments 1. CREATE TABLE category (catcode VARCHAR2(2), catdesc VARCHAR2(10)); 2. CREATE TABLE employees (emp# NUMBER(5), lastname VARCHAR2(15), firstname VARCHAR2(10), job_class VARCHAR2(4)); 3. ALTER TABLE employees ADD (empdate DATE DEFAULT SYSDATE, enddate DATE); 4. ALTER TABLE employees MODIFY job_class VARCHAR2(2); 5. ALTER TABLE employees DROP column enddate; 6. RENAME employees TO jl_emps; 7. CREATE TABLE book_pricing (id, cost, retail, category) AS (SELECT isbn, cost, retail, category FROM books); 8. ALTER TABLE book_pricing SET UNUSED (category); SELECT * FROM book_pricing; 9. TRUNCATE TABLE book_pricing; SELECT * FROM book_pricing; 10. DROP TABLE book_pricing PURGE; DROP TABLE jl_emps; FLASHBACK TABLE jl_emps TO BEFORE DROP; SELECT * FROM jl_emps; Advanced Challenge ALTER TABLE acctmanager ADD (comm_id NUMBER(2) DEFAULT 10, Ben_id NUMBER(2)); CREATE TABLE commrate (comm_id NUMBER(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 Notes: In Section A, students need to develop CREATE TABLE statements for each table listed. The listing indicates DEFAULT column values when applicable. The listing “Coding key for selected columns” is provided only to help students visualize what type of data will be entered in selected code columns. This information will be a valuable reference for performing queries in later chapters. In Section B, students need to use the ALTER TABLE command to make the requested table modifications. Warning: If you modify this assignment, consider how it will affect case study assignments in subsequent chapters. Recall that the City Jail Case Study is a cumulative case used throughout the textbook. Section A CREATE 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), start_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' ); CREATE TABLE crime_codes (crime_code NUMBER(3), code_description VARCHAR2(30)); Section B ALTER TABLE crimes MODIFY (classification DEFAULT 'U'); ALTER TABLE crimes ADD (date_recorded DATE DEFAULT SYSDATE); ALTER TABLE prob_officers ADD (pager# CHAR(10)); ALTER TABLE aliases MODIFY (alias VARCHAR2(20)); Chapter 4 Solutions Review Questions 1. What is the difference between the PRIMARY KEY constraint and a UNIQUE constraint? A column with a PRIMARY KEY constraint can’t contain NULL values, but a UNIQUE constraint does allow NULL values. 2. How can you verify the constraints that exist for a table? USER_CONSTRAINTS and USER_CONS_COLUMNS data dictionary views 3. A table can have a maximum of how many PRIMARY KEY constraints? One 4. Which type of constraint can be used to make certain the category for a book is included when a new book is added into inventory? A NOT NULL constraint 5. Which type of constraint should you use to ensure every book has a profit margin between 15% and 25%? A CHECK constraint 6. How is adding a NOT NULL constraint to an existing table different from adding other types of constraints? It can be added only by using a MODIFY clause; the other types are added by using an ADD clause. 7. When must you define constraints at the table level rather than the column level? If the constraint is based on a composite (more than one) column 8. To which table do you add a FOREIGN KEY constraint if you want to make certain every book ordered exists in the BOOKS table? The constraint is placed on the ORDERITEMS table and references the PRIMARY KEYof the BOOKS table. 9. What is the difference between disabling a constraint and dropping a constraint? A disabled constraint still exists and can be enabled later, but a dropped constraint no longer exists in the database. 10. What is the simplest way to determine whether a particular column can contain null values? Use the DESCRIBE command. Multiple Choice 1. d 2. a 3. c 4. a 5. e 6. a 7. c 8. b 9. e 10. c 11. b 12. c 13. d 14. e 15. d 16. a 17. d 18. b 19. a 20. d Hands-On Assignments 1. CREATE TABLE store_reps (rep_id NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), comm CHAR(1) DEFAULT 'Y', CONSTRAINT storereps_id_pk PRIMARY KEY (rep_id) ); 2. ALTER TABLE store_reps MODIFY (last NOT NULL, first NOT NULL); 3. ALTER TABLE store_reps ADD CONSTRAINT storereps_comm_ck CHECK (comm IN('Y','N')); 4. ALTER TABLE store_reps ADD base_salary NUMBER(7,2) CONSTRAINT storereps_basesalary_ck CHECK (base_salary > 0); 5. CREATE TABLE book_stores (store_id NUMBER(8), name VARCHAR2(30) NOT NULL, contact VARCHAR2(30), rep_id VARCHAR2(5), CONSTRAINT bookstores_storeid_pk PRIMARY KEY (store_id), CONSTRAINT bookstores_name_uk UNIQUE (name) ); 6. ALTER TABLE book_stores MODIFY rep_id NUMBER(5); ALTER TABLE book_stores ADD CONSTRAINT bookstores_id_fk FOREIGN KEY (rep_id) REFERENCES store_reps (rep_id); 7. ALTER TABLE book_stores DROP CONSTRAINT bookstores_id_fk; ALTER TABLE book_stores ADD CONSTRAINT bookstores_id_fk FOREIGN KEY(rep_id) REFERENCES store_reps (rep_id) ON DELETE CASCADE; 8. CREATE TABLE rep_contracts (store_id NUMBER(8), name NUMBER(5), quarter CHAR(3), rep_id NUMBER(5), CONSTRAINT repcontracts_pk PRIMARY KEY (store_id, quarter, rep_id), CONSTRAINT repcontracts_storeid_fk FOREIGN KEY (store_id) REFERENCES book_stores (store_id), CONSTRAINT repcontracts_repid_fk FOREIGN KEY(rep_id) REFERENCES store_reps (rep_id) ); 9. SELECT constraint_name, search_condition FROM user_constraints WHERE table_name = 'STORE_REPS'; 10. ALTER TABLE store_reps DISABLE CONSTRAINT storereps_basesal_ck; ALTER TABLE store_reps ENABLE CONSTRAINT storereps_basesal_ck; Advanced Challenge CREATE TABLE project (proj# NUMBER(4), p_name VARCHAR2(20) NOT NULL, p_desc VARCHAR 2(25), p_budget VARCHAR 2(15), CONSTRAINT project_proj#_pk PRIMARY KEY (proj#), CONSTRAINT project_p_name_uk UNIQUE (p_name)); CREATE TABLE workorders (wo# NUMBER(4), proj# NUMBER(4) NOT NULL, wo_desc VARCHAR2(25) NOT NULL, wo_assigned VARCHAR2(25), wo_hours NUMBER(4) NOT NULL, wo_start DATE, wo_due DATE, wo_complete CHAR(1), CONSTRAINT workorders_wo#_pk PRIMARY KEY (wo#), CONSTRAINT workorders_proj#_fk FOREIGN KEY (proj#) references project (proj#), CONSTRAINT workorders_wo_desc_uk UNIQUE (wo_desc), CONSTRAINT workorders_wo_hours_ck CHECK (wo_hours >0), CONSTRAINT workorders_wo_comp_ck CHECK (wo_complete IN('Y','N')); Case Study: City Jail Notes: This part of the case study addresses adding constraints to the City Jail database constructed in Chapter 3. Students need to reference the E-R model solution from Chapter 1 and the database descriptions from the Chapters 1 and 3 case study sections to determine all the constraints required. You need to give students details of the requirements. For example, the provided solution file assumes the following: • Constraint naming: All constraints except NOT NULL constraints should be assigned a name. • Foreign key columns: No NULL values should be allowed in FK columns except the Prob_ID column of the Sentences table because sentences aren’t always probation. If the sentence is not probation, the Prob_ID column is NULL. To give students practice in both the CREATE TABLE and ALTER TABLE commands, the solution file also assumes the student is instructed to do the following: • First, drop the APPEALS, CRIME_OFFICERS, and CRIME_CHARGES tables constructed in Chapter 3. These three tables are to be built last, using a CREATE TABLE command that includes all the necessary constraints. • Second, use the ALTER TABLE command to add all constraints to the existing tables. Note that the sequence of constraint addition has an impact. Any tables referenced by FOREIGN KEYs must already have the PRIMARY KEY created. • Third, use the CREATE TABLE command to build the three tables dropped in the first step. Constraint list: Table Column Constraint type Condition Aliases Alias_ID PRIMARY KEY Criminal_ID FOREIGN KEY Ref Criminals table Criminal_ID NOT NULL Criminals Criminal_ID PRIMARY KEY V_status CHECK IN (‘Y’,’N’) P_status CHECK IN (‘Y’,’N’) Crimes Crime_ID PRIMARY KEY Criminal_ID FOREIGN KEY Ref Criminals table Criminal_ID NOT NULL Classification CHECK IN (‘F’,’M’,’O’,’U’) Status CHECK IN (‘CL’,’CA’,’IA’) Sentences Sentence_ID PRIMARY KEY Criminal_ID FOREIGN KEY Ref Criminals table Criminal_ID NOT NULL Prob_ID FOREIGN KEY Ref Prob_Officers table Prob_ID NOT NULL Type CHECK IN (‘J’,’H’,’P’) Prob_officers Prob_ID PRIMARY KEY Status CHECK IN (‘A’,’I’) Crime_charges Charge_ID PRIMARY KEY Crime_ID FOREIGN KEY Ref Crimes Table Crime_ID NOT NULL Crime_code FOREIGN KEY Ref Crime_codes table Crime_code NOT NULL Charge_status CHECK IN(‘PD’,’GL’,’NG’) Crime_officers Crime_ID and Officer_ID PRIMARY KEY Crime_ID FOREIGN KEY Ref Crimes table Officer_ID FOREIGN KEY Ref Officers table Officers Officer_ID PRIMARY KEY Status CHECK IN (‘A’,’I’) Appeals Appeal_ID PRIMARY KEY Crime_ID FOREIGN KEY Ref Crimes table Crime_ID NOT NULL Status CHECK IN (‘P’,‘A’,’D’) Crime_codes Crime_code PRIMARY KEY DROP TABLE appeals; DROP TABLE crime_officers; DROP TABLE crime_charges; ALTER TABLE criminals ADD CONSTRAINT criminals_id_pk PRIMARY KEY (criminal_id); ALTER TABLE criminals ADD CONSTRAINT criminals_vstatus_ck CHECK (v_status IN('Y','N')); ALTER TABLE criminals ADD CONSTRAINT criminals_pstatus_ck CHECK (p_status IN('Y','N')); ALTER TABLE aliases ADD CONSTRAINT aliases_id_pk PRIMARY KEY (alias_id); ALTER TABLE aliases ADD CONSTRAINT appeals_criminalid_fk FOREIGN KEY (criminal_id) REFERENCES criminals(criminal_id); ALTER TABLE aliases MODIFY (criminal_id NOT NULL); ALTER TABLE crimes ADD CONSTRAINT crimes_id_pk PRIMARY KEY (crime_id); ALTER TABLE crimes ADD CONSTRAINT crimes_class_ck CHECK (classification IN('F','M','O','U')); ALTER TABLE crimes ADD CONSTRAINT crimes_status_ck CHECK (status IN('CL','CA','IA')); ALTER TABLE crimes ADD CONSTRAINT crimes_criminalid_fk FOREIGN KEY (criminal_id) REFERENCES criminals(criminal_id); ALTER TABLE crimes MODIFY (criminal_id NOT NULL); ALTER TABLE prob_officers ADD CONSTRAINT probofficers_id_pk PRIMARY KEY (prob_id); ALTER TABLE prob_officers ADD CONSTRAINT probofficers_status_ck CHECK (status IN('A','I')); ALTER TABLE sentences ADD CONSTRAINT sentences_id_pk PRIMARY KEY (sentence_id); ALTER TABLE sentences ADD CONSTRAINT sentences_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id); ALTER TABLE sentences MODIFY (crime_id NOT NULL); ALTER TABLE sentences ADD CONSTRAINT sentences_probid_fk FOREIGN KEY (prob_id) REFERENCES prob_officers(prob_id); ALTER TABLE sentences ADD CONSTRAINT sentences_type_ck CHECK (type IN('J','H','P')); ALTER TABLE officers ADD CONSTRAINT officers_id_pk PRIMARY KEY (officer_id); ALTER TABLE officers ADD CONSTRAINT officers_status_ck CHECK (status IN('A','I')); ALTER TABLE crime_codes ADD CONSTRAINT crimecodes_code_pk PRIMARY KEY (crime_code); CREATE TABLE appeals (appeal_id NUMBER(5), crime_id NUMBER(9) NOT NULL, filing_date DATE, hearing_date DATE, status CHAR(1) DEFAULT 'P', CONSTRAINT appeals_id_pk PRIMARY KEY (appeal_id), CONSTRAINT appeals_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id), CONSTRAINT appeals_status_ck CHECK (status IN('P','A','D')) ); CREATE TABLE crime_officers (crime_id NUMBER(9), officer_id NUMBER(8), CONSTRAINT crimeofficers_cid_oid_pk PRIMARY KEY (crime_id,officer_id), CONSTRAINT crimeofficers_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id), CONSTRAINT crimeofficers_officerid_fk FOREIGN KEY (officer_id) REFERENCES officers(officer_id) ); CREATE TABLE crime_charges (charge_id NUMBER(10), crime_id NUMBER(9) NOT NULL, crime_code NUMBER(3) NOT NULL, charge_status CHAR(2), fine_amount NUMBER(7,2), court_fee NUMBER(7,2), amount_paid NUMBER(7,2), pay_due_date DATE, CONSTRAINT crimecharges_id_pk PRIMARY KEY (charge_id), CONSTRAINT crimecharges_crimeid_fk FOREIGN KEY (crime_id) REFERENCES crimes(crime_id), CONSTRAINT crimecharges_code_fk FOREIGN KEY (crime_code) REFERENCES crime_codes(crime_code), CONSTRAINT crimecharges_status_ck CHECK (charge_status IN('PD','GL','NG')) ); Chapter 5 Solutions Review Questions 1. Which command should you use to copy data from one table and have it added to an existing table? INSERT INTO with a subquery 2. Which command can you use to change the existing data in a table? UPDATE command 3. When do changes generated by DML operations become stored in database table permanently? After they are committed 4. Explain the difference between explicit and implicit locks. An explicit lock occurs when the user executes the LOCK TABLE command and implicit locks occur when DML or DDL commands are executed. 5. If you add a record to the wrong table, what’s the simplest way to remove the record from the table? If a commit has not occurred, issue the ROLLBACK command. 6. How does Oracle11g identify a substitution variable in an SQL command? The variable is preceded by an ampersand (&). 7. How are NULL values included in a new record being added to a table? Type NULL, substitute two single quotes, or omit the column name from the column list in the INSERT INTO clause. 8. When should the VALUES clause be omitted from the INSERT INTO command? When a subquery is being included to identify the data to be added to the table. 9. What happens if a user attempts to add data to a table that will cause the record to violate an enabled constraint? The entire row is rejected. 10. What two methods can be used to activate a column’s DEFAULT option in an INSERT command? Exclude the column from the INSERT command, or use the keyword DEFAULT for the column value. Multiple Choice 1. e 2. d 3. a 4. c 5. e 6. b 7. a 8. b 9. b 10. c 11. e 12. c 13. a 14. a 15. d 16. d 17. d 18. d 19. d 20. d Hands-On Assignments 1. INSERT INTO orders (order#, customer#, orderdate) VALUES (1021, 1009, '20-JUL-09'); 2. UPDATE orders SET shipzip = '33222' WHERE order# = 1017; 3. COMMIT; 4. INSERT INTO orders (order#, customer#, orderdate) VALUES (1022, 2000, '06-AUG-09'); **Foreign key error due to customer 2000 not existing in the CUSTOMERS table 5. INSERT INTO orders (order#, customer#, orderdate) VALUES (1023, 1009); **Constraint error due to Orderdate having a NOT NULL constraint 6. UPDATE books SET cost = &cost WHERE isbn = '&isbn'; 7. Execute the statement using substitution variables. If this statement is saved in a script file, use START or @ in the SQL*Plus client tool or select Open, File and then Run, Script from the menu in SQL Developer. 8. ROLLBACK; 9. Note: Deleting the master order record from the ORDERS table raises an error because child records exist in the ORDERITEMS table. To eliminate an order, the child records and then the master record must be deleted. DELETE FROM orderitems WHERE order# = 1005; DELETE FROM orders WHERE order# = 1005; 10. ROLLBACK; Advanced Challenge CREATE TABLE category (catcode VARCHAR2(3), catdesc VARCHAR2(11) NOT NULL, CONSTRAINT category_code_pk PRIMARY KEY (catcode) ); INSERT INTO category VALUES ('BUS', 'BUSINESS'); INSERT INTO category VALUES ('CHN', 'CHILDREN'); INSERT INTO category VALUES ('COK', 'COOKING'); INSERT INTO category VALUES ('COM', 'COMPUTER'); INSERT INTO category VALUES ('FAL', 'FAMILY LIFE'); INSERT INTO category VALUES ('FIT', 'FITNESS'); INSERT INTO category VALUES ('SEH', 'SELF HELP'); INSERT INTO category VALUES ('LIT', 'LITERATURE'); COMMIT; ALTER TABLE books ADD (catcode VARCHAR2(3), CONSTRAINT books_catcode_fk FOREIGN KEY (catcode) REFERENCES category(catcode)); UPDATE books SET catcode = 'BUS' WHERE category = 'BUSINESS'; UPDATE books SET catcode = 'CHN' WHERE category = 'CHILDREN'; UPDATE books SET catcode = 'COK' WHERE category = 'COOKING'; UPDATE books SET catcode = 'COM' WHERE category = 'COMPUTER'; UPDATE books SET catcode = 'FAL' WHERE category = 'FAMILY LIFE'; UPDATE books SET catcode = 'FIT' WHERE category = 'FITNESS'; UPDATE books SET catcode = 'SEH' WHERE category = 'SELF HELP'; UPDATE books SET catcode = 'LIT' WHERE category = 'LITERATURE'; COMMIT; ALTER TABLE books DROP column category; Case Study: City Jail 1a. INSERT INTO criminals (criminal_id, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (&id, '&last','& first','&street', '&city', '&state', '&zip', '&phone', '&v_status', '&p_status'); 1b. --use script from 1a. 1c. ALTER TABLE criminals ADD (mail_flag CHAR(1)); 1d. UPDATE criminals SET mail_flag = 'Y'; 1e. UPDATE criminals SET mail_flag = 'N' WHERE street IS NULL; 1f. UPDATE criminals SET phone = '7225659032' WHERE criminal_id = 1016; 1g. DELETE FROM criminals WHERE criminal_id = 1017; 2a. INSERT INTO crimes (crime_id, criminal_id, classification, date_charged, status) VALUES (100, 1010, 'M', '15-JUL-09', 'PD'); --FK violation - criminal id OR check violation - status 2b. INSERT INTO crimes (crime_id, criminal_id, classification, date_charged, status) VALUES (130, 1016, 'M', '15-JUL-09', 'PD'); --Check violation - status 2c. INSERT INTO crimes (crime_id, criminal_id, classification, date_charged, status) VALUES (130, 1016, 'P', '15-JUL-09', 'CL'); --Check violation - classification Chapter 6 Solutions Review Questions 1. How can a sequence be used in a database? To generate a series of sequential numbers as primary keys or for internal control purposes 2. How can gaps appear in values generated by a sequence? If the integers are cached and the server crashes or is shut down. 3. How can you indicate that the values generated by a sequence should be in descending order? Include a negative value in the INCREMENT BY clause. 4. When is an index appropriate for a table? If searches on a large table normally return less than 10% of the rows and the table is not updated frequently. 5. What is the difference between the B-tree and bitmap index structures? The B-tree index structure is like a tree, with leaves or nodes holding the value ranges and ROWIDs mapping to actual table rows. A bitmap index is useful for improving queries on columns that have low selectivity (low cardinality, or a small number of distinct values). The index is a two-dimensional array containing one column for each distinct value in the column being indexed. Each row is linked to a ROWID and contains a bit (0 or 1) that indicates whether the column value matches this index value. 6. When does Oracle11g automatically create an index for a table? When a PRIMARY KEY or UNIQUE index is created 7. Under what circumstances should you not create an index for a table? If the table is updated frequently or searches normally return more than 10% of the table rows in the results. 8. What is an IOT and under what circumstances might it be useful? This structure stores the contents of the entire table in a B-tree index with rows sorted in the primary key value order. It combines the index and table into a single structure. Search and sort operations involving the primary key column can be improved with this index. 9. What command is used to modify an index? Except for a name change, there’s no way to modify an index; it must be dropped and re-created. 10. What is the purpose of a synonym? A synonym provides an alternative name for a database object. Multiple Choice 1. c 2. c 3. d 4. a 5. c 6. b 7. b 8. a 9. d 10. b 11. b 12. g 13. c 14. c 15. a 16. e 17. b 18. c 19. c 20. c Hands-On Assignments 1. CREATE SEQUENCE cust_seq START WITH 1021 NOMAXVALUE NOMINVALUE NOCACHE NOCYCLE; 2. INSERT INTO customers (customer#, lastname, firstname, zip) VALUES (cust_seq.NEXTVAL, 'SHOULDERS', 'FRANK', '23567'); 3. CREATE SEQUENCE my_first_seq INCREMENT BY -3 START WITH 5 MAXVALUE 5 MINVALUE 0 NOCYCLE; 4. SELECT my_first_seq.NEXTVAL FROM DUAL; Error: Caused by the sequence running out of values to issue, as the minimum value of 0 was reached and the CYCLE option is set to NOCYCLE. 5. ALTER SEQUENCE my_first_seq MINVALUE -1000; 6. CREATE TABLE email_log (emailid NUMBER GENERATED AS IDENTITY PRIMARY KEY, emaildate DATE, customer# NUMBER(4)); INSERT INTO email_log (emaildate, customer#) VALUES (SYSDATE, 1007); INSERT INTO email_log (emailid, emaildate, customer#) VALUES (DEFAULT, SYSDATE, 1008); INSERT INTO email_log (emailid, emaildate, customer#) VALUES (25, SYSDATE, 1009); SELECT * FROM email_log; 7. CREATE SYNONYM numgen FOR my_first_seq; 8. SELECT numgen.currval FROM dual; DROP SYNONYM numgen; DROP SEQUENCE my_first_seq; 9. CREATE BITMAP INDEX customers_state_idx ON customers(state); SELECT index_name FROM user_indexes; DROP INDEX customers_state_idx; 10. CREATE INDEX customers_last_idx ON customers(lastname); SELECT index_name FROM user_indexes; DROP INDEX customers_last_idx; 11. CREATE INDEX orders_shipdays_idx ON orders(shipdate-orderdate); Advanced Challenge Student responses will vary. Sequences could be applied to all primary key columns. Index additions can support searches, such as for author’s last name and publisher name. An example of a drawback is minimizing indexes to only the columns required for frequent searches, such as customer’s last name. Minimizing the number of indexes helps improve DML processing efficiency because fewer indexes need to be updated. Case Study: City Jail 1. CREATE SEQUENCE criminals_seq START WITH 1018 NOCACHE NOCYCLE; CREATE SEQUENCE crimes_seq START WITH 10001 NOCACHE NOCYCLE; INSERT INTO criminals (criminal_ID, last, first, street, city, state, zip, v_status, p_status) VALUES (criminals_seq.NEXTVAL, 'Capps','Johnny','111 Main', 'Portsmouth', 'VA', '04578', 'N', 'N'); INSERT INTO crimes (crime_ID, criminal_ID, classification, date_charged, status) VALUES (crimes_seq.NEXTVAL, criminals_seq.CURRVAL, 'M', '15-JUL-05', 'CL'); 2. CREATE INDEX criminals_last_idx ON criminals(last); CREATE INDEX criminals_street_idx ON criminals(street); CREATE INDEX criminals_phone_idx ON criminals(phone); 3. Bitmap indexes are quite useful for columns with low selectivity. Some candidate columns from the City Jail database include Criminals/V_status, Criminals/P_status, Crimes/Classification, Crimes/Status, Sentences/Type, Prob_officers/Status, Crime_charges/Charge_status, Officers/Status, and Appeals/Status. 4. Synonyms could simplify object references for the City Jail database. If a variety of users are accessing or developing applications to access the City Jail database objects, creating public synonyms simplifies object reference, as the schema doesn’t have to be included in all object references. Chapter 7 Solutions Review Questions 1. What is the purpose of data security? Data security measures ensure the protection of data from threats ranging from natural disasters to computer crimes. 2. What does a database account with the CREATE SESSION privilege allow the user to do? Connect to the Oracle database 3. How is a user password assigned in Oracle 12c? With the IDENTIFIED BY clause 4. What is a privilege? The right or permission to perform a type of operation 5. If you’re logged in to Oracle 12c, how can you determine which privileges are currently available to your account? Query the SESSION_PRIVS view. 6. What types of privileges are available in Oracle 12c? Define each type. Object privileges allow the user to perform operations on specific objects, and system privileges allow the user to perform system operations and most DDL operations. 7. What is the purpose of a role in Oracle 12c? A role is a collection of privileges or other roles and makes it easier for the DBA to manage user privileges. 8. How can you assign a password to a role? With the IDENTIFIED BY clause 9. What happens if you revoke an object privilege that was granted with the WITH GRANT OPTION? What if the privilege is removed from a user who had already granted the same object privilege to three other users? When the privilege is revoked, any subsequent users who had been assigned that privilege by the user also lose their privileges. 10. How can you remove a user account from Oracle 12c? With the DROP USER command Multiple Choice 1. e 2. d 3. b 4. b 5. b 6. a 7. a 8. a 9. c 10. d 11. a 12. d 13. c 14. a 15. d 16. b 17. d 18. d 19. d 20. d Hands-On Assignments 1. CREATE USER finitiallastname IDENTIFIED BY apassword; 2. Connection will fail because the CREATE SESSION privilege is required. 3. GRANT CREATE SESSION, CREATE TABLE, ALTER ANY TABLE TO finitiallastname; 4. CREATE ROLE customerrep; GRANT INSERT, DELETE ON orders TO customerrep; GRANT INSERT, DELETE ON orderitems TO customerrep; 5. GRANT customerrep TO finitiallastname; 6. SELECT * FROM SESSION_PRIVS; SELECT * FROM SESSION_ROLES; 7. REVOKE delete ON orders FROM customerrep; REVOKE delete ON orderitems FROM customerrep; 8. REVOKE customerrep FROM finitiallastname; 9. DROP ROLE customerrep; 10. DROP USER finitiallastname; Advanced Challenge Student responses will vary. The following chart is one possible list of table privilege assignments. Customer Service Rep Role Table Select Insert Update Delete CUSTOMERS ✓ ✓ ✓ ORDERS ✓ ✓ ✓ ✓ ORDERITEMS ✓ ✓ ✓ ✓ Case Study: City Jail Notes: Answers will vary, depending on department duty interpretation. This project should provide a good source for discussion. For example, given the Data Officer duty of data removal, should any of the other departments be assigned delete privileges for data modification duties? Also, do users who need to modify or update data need the SELECT privilege? Do they need to query data first to locate existing records? Department Privileges Needed Criminal Records CREATE SESSION, SELECT, INSERT, UPDATE ON criminals, crimes, crime_charges, aliases, officers, crime_codes Court Recording CREATE SESSION, SELECT, INSERT, UPDATE ON appeals, sentences, prob_officers Crimes Analysis CREATE SESSION, SELECT ANY TABLE Data Officer CREATE SESSION, SELECT, DELETE ON crimes, sentences, appeals, crime_charges Create a user for each employee: CREATE USER username IDENTIFIED BY password PASSWORD EXPIRE; Create roles and grant privileges for each department: Criminal Records Department CREATE ROLE c_rec_role; GRANT CREATE SESSION TO c_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.criminals TO c_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.crimes TO c_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.crime_charges TO c_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.aliases TO c_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.officers TO c_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.crime_codes TO c_rec_role; GRANT c_rec_role TO username; Court Recording Department CREATE ROLE crt_rec_role; GRANT CREATE SESSION TO crt_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.appeals TO crt_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.sentences TO crt_rec_role; GRANT SELECT, INSERT, UPDATE ON schema.prob_officers TO crt_rec_role; GRANT crt_rec_role TO username; Crimes Analysis Department CREATE ROLE c_analysis_role; GRANT CREATE SESSION TO c_analysis_role; GRANT SELECT ANY TABLE TO c_analysis_role; GRANT c_analysis_role TO username; Data Officer Department CREATE ROLE d_officer_role; GRANT CREATE SESSION TO d_officer_role; GRANT SELECT, DELETE ON schema.sentences TO d_officer_role; GRANT SELECT, DELETE ON schema.crimes TO d_officer_role; GRANT SELECT, DELETE ON schema.crime_charges TO d_officer_role; GRANT SELECT, DELETE ON schema.appeals TO d_officer_role; GRANT d_officer_role TO username; Chapter 8 Solutions Review Questions 1. Which clause of a SQL query is used to restrict the number of rows returned? WHERE clause 2. Which clause of a SQL query displays the results in a specific sequence? ORDER BY clause 3. Which operator can you use to find any books with a retail price of at least $24.00? >= 4. Which operator should you use to find NULL values? IS NULL 5. The IN comparison operator is similar to which logical operator? OR 6. When should single quotation marks be used in the WHERE clause? When nonnumeric data is specified in the condition 7. What’s the effect of using the NOT operator in a WHERE clause? Reverses the meaning of the search condition and searches for records that don’t match the condition. 8. When should a percent sign (%) be used with the LIKE operator? To represent more than one character 9. When should an underscore symbol ( _ ) be used with the LIKE operator? To represent exactly one character in a specific position 10. Because % is a wildcard character, how can the LIKE operator search for a literal percent sign (%) in a character string? Use the ESCAPE option to include an escape character in the search pattern to indicate the value should be treated literally. Multiple Choice 1. c 2. c 3. d 4. d 5. a 6. a 7. a 8. a 9. b 10. c 11. c 12. e 13. c 14. e 15. d 16. c 17. c 18. d 19. a 20. e Hands-On Assignments 1. SELECT lastname, firstname, state FROM customers WHERE state = 'NJ'; 2. SELECT order#, shipdate FROM orders WHERE shipdate > '01-APR-09'; 3. SELECT title, category FROM books WHERE category 'FITNESS'; 4. SELECT customer#, lastname, state FROM customers WHERE state = 'GA' or state = 'NJ' ORDER BY lastname; or SELECT customer#, lastname, state FROM customers WHERE state IN ('GA', 'NJ') ORDER BY lastname; 5. SELECT order#, orderdate FROM orders WHERE orderdate < '02-APR-09'; or SELECT order#, orderdate FROM orders WHERE orderdate = '01-JAN-05' AND pubdate = 10 ORDER BY retail-cost desc 2. SELECT * FROM books WHERE category IN ('COMPUTER', 'FAMILY LIFE') AND pubid IN (1, 3) AND retail >= 45; Case Study: City Jail Warning: The City Jail database must be rebuilt with the CityJail_8.sql script, which is in the Chapter 8 solution files. This script isn’t included in the student data files because case study assignments in previous chapters include table creation challenges. 1. SELECT alias FROM aliases WHERE alias LIKE 'B%'; 2. SELECT crime_ID, criminal_ID, date_charged, classification FROM crimes WHERE date_charged BETWEEN '01-OCT-08' AND '31-OCT-08'; SELECT crime_ID, criminal_ID, date_charged, classification FROM crimes WHERE date_charged >= '01-OCT-08' AND date_charged 14; 6. SELECT criminal_ID, last, zip FROM criminals WHERE zip = '23510' ORDER BY criminal_ID; 7. SELECT crime_ID, criminal_ID, date_charged, hearing_date FROM crimes WHERE hearing_date IS NULL; 8. SELECT sentence_ID, criminal_ID, prob_ID FROM sentences WHERE prob_ID IS NOT NULL ORDER BY prob_ID, criminal_ID; 9. SELECT crime_ID, criminal_ID, classification, status FROM crimes WHERE classification = 'M' AND status = 'IA'; 10. SELECT charge_ID, crime_ID, fine_amount, court_fee, amount_paid, (fine_amount+court_fee)-amount_paid "Owed" FROM crime_charges WHERE (fine_amount+court_fee)-amount_paid > 0; 11. SELECT officer_ID, last, precinct, status FROM officers WHERE precinct IN ('OCVW', 'GHNT') AND status = 'A' ORDER BY precinct, last; SELECT officer_ID, last, precinct, status FROM officers WHERE (precinct = 'OCVW' OR precinct = 'GHNT') AND status = 'A' ORDER BY precinct, last; Solution Manual for Oracle 12c: SQL Joan Casteel 9781305251038
Download
Close
Close
