Description read chapters 3,4,5,6,and 7 and answer these question doc Answer 25 Review Questions ...
Description read chapters 3,4,5,6,and 7 and answer these question doc Answer 25 Review Questions at the end of the chapter. Your answers must include the page number(s) of where the topic is introduced in the textbook. To provide you an example, I have answered the first question in the link above. UNFORMATTED ATTACHMENT PREVIEW Chapter Three – Structured Query Language Chapter 3 Review Questions & SQL Code 3.1 What does SQL stand for? Structured Query Language (Page 151). 3.2 What is a data sublanguage? 3.3 Explain the importance of SQL-92. 3.4 Why is it important to learn SQL? 3.5 Describe in your own words the purpose of the two business rules listed on page 158. 3.6 Why do some standard SQL-92 statements fail to run successfully in Microsoft Access? Use the following tables for your answers to questions 3.7 through 3.48: PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET (PetID, PetName, PetType, PetBreed, PetDOB, OwnerID) Sample data for these tables are shown in Figures 3-27 and 3-28. For each SQL statement you write, show the results based on these data. If possible, run the statements you write for the questions that follow in an actual DBMS, as appropriate, to obtain results. Use data types that are consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types by using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. © 2024 Pearson Education, Inc. Page 1 of 5 Chapter Three – Structured Query Language 3.7 Write an SQL CREATE TABLE statement to create the PET_OWNER table, with OwnerID as a surrogate key. Justify your choices of column properties. If you are using an actual DBMS, also insert the data using SQL. 3.8 Write an SQL CREATE TABLE statement to create the PET table without a referential integrity constraint on OwnerID in PET. Justify your choices of column properties. Why not make every column NOT NULL? If you are using an actual DBMS, also insert the data using SQL. 3.9 Create a referential integrity constraint on OwnerID in PET. Assume that deletions should not cascade. 3.10 Create a referential integrity constraint on OwnerID in PET. Assume that deletions should cascade. © 2024 Pearson Education, Inc. Page 2 of 5 Chapter Three – Structured Query Language The following table schema for the PET_2 table is an alternate version of the PET table- use it to answer review questions 3.11 and 3.12: PET_2 (PetName, PetType, PetBreed, PetDOB, OwnerID) 3.11 Write the required SQL statements to create the PET_2 table. 3.12 Is PET or PET_2 a better design? Explain your rationale. 3.13 Write the SQL statements necessary to remove the PET_OWNER table from the database. Assume that the referential integrity constraint is to be removed. Do not run these commands in an actual database! 3.14 Write the SQL statements necessary to remove the PET_OWNER table from the database. Assume that the PET table is to be removed. Do not run these commands in an actual database! 3.15 Write an SQL statement to display all columns of all rows of PET. Do not use the asterisk (*) notation. 3.16 Write an SQL statement to display all columns of all rows of PET. Use the asterisk (*) notation. 3.17 Write an SQL statement to display the breed and type of all pets. 3.18 Write an SQL statement to display the breed, type, and DOB of all pets having the type Dog. © 2024 Pearson Education, Inc. Page 3 of 5 Chapter Three – Structured Query Language 3.19 Write an SQL statement to display the PetBreed column of PET. 3.20 Write an SQL statement to display the PetBreed column of PET. Do not show duplicates. 3.21 Write an SQL statement to display the breed, type, and DOB for all pets having the type Dog and the breed Std. Poodle. 3.22 Write an SQL statement to display the name, breed, and type for all pets that are not of type Cat, Dog, or Fish. 3.23 Write an SQL statement to display the Pet ID, breed, and type for all pets having a four-character name starting with K. Note that the RTRIM function will be needed in the solution that uses a CHAR column, but not for one that uses a VARCHAR column. 3.24 Write an SQL statement to display the last name, first name, and email of all owners who have an email address ending with somewhere.com. Assume that email account names can be any number of characters. Note that the RTRIM function will be needed in the solution that uses a CHAR column, but not for one that uses a VARCHAR column. 3.25 Write an SQL statement to display the last name, first name and email of any owner who has a NULL value for OwnerPhone. © 2024 Pearson Education, Inc. Page 4 of 5 Chapter Three – Structured Query Language SQL (Structured Query Language) Code After finishing the hands-on portion of Assignment 3, copy and paste your SQL (Structured Query Language) code below. 1. SQL code to retrieve “All Customers” in the “CUSTOMER” table. 2. SQL code to retrieve all customers from “Oklahoma” in the “CUSTOMER” table. 3. SQL code to retrieve all customers from “Tulsa” in the “CUSTOMER” table. 4. SQL code created to retrieve all “phone” contacts using a “JOIN” operation with the “CUSTOMER” and “CONTACT” table. 5. SQL code created to retrieve all contacts created since January 1, 2024 using a “JOIN” operation with the “CUSTOMER” and “CONTACT” table. © 2024 Pearson Education, Inc. Page 5 of 5 Chapter Four – Data Modeling and the Entity-Relationship Model Chapter 4 Review Questions 4.1 What is a decision? A decision is a selected action that we should take in our current situation. (Page 292). 4.2 What is data? 4.3 What is information? 4.4 What is a system? What is an information system? 4.5 What is a computer-based information system? Describe the five components of a computer-based information system. 4.6 What is a business process? 4.7 How do information systems support business processes? 4.8 Describe how information systems include processes. Chapter Four – Data Modeling and the Entity-Relationship Model 4.9 What is systems analysis and design? 4.10 Describe the systems development life cycle (SDLC) model. 4.11 What is a user requirements document (URD)? What purpose does it serve? 4.12 Name the three stages in the process of developing database systems. Summarize the tasks in each. 4.13 What is a data model, and what is its purpose? 4.14 What is a prototype, and what is its purpose? 4.15 What is a use case, and what is its purpose? 4.16 Give an example of a data constraint. 4.17 Give an example of a business rule that would need to be documented in a database development project. Chapter Four – Data Modeling and the Entity-Relationship Model 4.18 Define the term entity, and give an example other than those used in this book. 4.19 Explain the difference between an entity class and an entity instance. 4.20 Define the term attribute, and give examples for the entity you described in question 4.18. 4.21 Define the term identifier, and indicate which attribute defined in your answer to question 4.20 identifies the entity. 4.22 Define the term composite identifier, and give an example other than those used in this book. 4.23 Define the term relationship, and give an example other than those used in this text. 4.24 Explain the difference between a relationship class and a relationship instance. 4.25 Define the term degree of relationship. Give an example, other than one used in this text, of a relationship greater than degree two. Chapter Four – Data Modeling and the Entity-Relationship Model Entity-Relationship Model Copy and paste your E-R Model here. Chapter 5: Review Questions 5.1 Explain how entities are transformed into tables. 1. Create a table for each entity. Specify primary keys. Specify properties for each field (column). Verify normalization. 2. Create relationships between the tables. Include: Strong relationships (1:1, 1:N, N:M). Weak entities. Relationships with Subtypes. Recursive relationships. (Page 368). 5.2 Explain how attributes are transformed into columns. What column properties do you take into account when making the transformations? 5.3 Why is it necessary to apply the normalization process to the tables created according to your answer to question 5.1? 5.4 What is denormalization? 5.5 When is denormalization justified? 5.6 Explain the problems that unnormalized tables have for insert, update, and delete actions. 5.7 Explain how the representation of weak entities differs from the representation of strong entities. Chapter Five – Database Design 5.8 Explain how supertype and subtype entities are transformed into tables. 5.9 List the three types of binary relationships and give an example of each. Do not use the examples given in this text. 5.10 Define the term foreign key and give an example. 5.11 Show two different ways to represent the 1:1 relationship in your answer to question 5.9. Use IE Crow’s Foot E-R diagrams. 5.12 For your answers to question 5.11, describe a method for obtaining data about one of the entities, given the primary key of the other. Describe a method for obtaining data about the second entity, given the primary key of the first. Describe methods for both of your alternatives in question 5.11. 5.13 Code SQL statements to create a join that has all data about both tables from your work for question 5.11. 5.14 Define the terms parent and child as they apply to tables in a database design and give an example of each. © 2024 Pearson Education, Inc. Page 2 of 4 Chapter Five – Database Design 5.15 Show how to represent the 1:N relationship in your answer to question 5.9. Use an IE Crow’s Foot E-R diagram. 5.16 For your answer to question 5.15, describe a method for obtaining data for all the children, given the primary key of the parent. Describe a method for obtaining data for the parent, given a primary key of the child. 5.17 For your answer to question 5.15, code an SQL statement that creates a table that has all data from both tables. 5.18 For a 1:N relationship, explain why you must place the primary key of the parent table in the child table, rather than place the primary key of the child table in the parent table. 5.19 Give examples of binary 1:N relationships, other than those in this text, for (a) an optional-to-optional relationship, (b) an optional-to-mandatory relationship, (c) a mandatory-to-optional relationship, and (d) a mandatory-to-mandatory relationship. Illustrate your answer by using IE Crow’s Foot E-R diagrams. 5.20 Show how to represent the N:M relationship in your answer to question 5.9. Use an IE Crow’s Foot E-R diagram. 5.21 Explain the meaning of the term intersection table. © 2024 Pearson Education, Inc. Page 3 of 4 Chapter Five – Database Design 5.22 Explain how the terms parent table and child table relate to the tables in your answer to question 5.20. 5.23 For your answers to questions 5.20, 5.21, and 5.22, describe a method for obtaining the children for one of the entities in the original data model, given the primary key of the table based on the second entity. Also, describe a method for obtaining the children for the second entity, given the primary key of the table based on the first entity. 5.24 For your answer to question 5.20, code an SQL statement that creates a relation that has all data from all tables. 5.25 Why is it not possible to represent N:M relationships with the same strategy used to represent 1:N relationships? © 2024 Pearson Education, Inc. Page 4 of 4 Chapter 7: Review Questions 7.1 What are BI systems? Business Information (BI) systems are information systems that assist managers and organizations to help support decision-making by an analysis of current and past activities. (Page 496). 7.2 How do BI systems differ from transaction processing systems? 7.3 Name and describe the two main categories of BI systems. 7.4 What are the three sources of data for BI systems? 7.5 Summarize the problems with operational databases that limit their usefulness for BI applications. 7.6 What is an ETL system, and what functions does it perform? 7.7 What problems in operational data create the need to clean data before loading the data into a data warehouse? 7.8 What does it mean to transform data? Give an example other than the ones used in this book. 7.9 Define data mining. Chapter Seven –Business Intelligence Systems, Data Warehouses, and Big Data 7.10 Explain the difference between unsupervised and supervised data mining. 7.11 Name five popular data mining techniques. 7.12 Explain how a decision tree classifies a new record. 7.13 Why are data warehouses necessary? 7.14 Give examples of data warehouse metadata. 7.15 Explain the difference between a data warehouse and a data mart. Give an example other than the ones used in this book. 7.16 What is the enterprise data warehouse (EDW) architecture? 7.17 Describe the differences between operational databases and dimensional databases. The differences are summarized in Figure 7-15 below: 7.18 What is a star schema? 7.19 What is a fact table? What type of data are stored in fact tables? © 2024 Pearson Education, Inc. Page 2 of 3 Chapter Seven –Business Intelligence Systems, Data Warehouses, and Big Data 7.20 In relation to fact tables, what is a measure? 7.21 What is a dimension table? What types of data are stored in dimension tables? 7.22 What is a slowly changing dimension? 7.23 Why is the time dimension important in a dimensional model? 7.24 What is a conformed dimension? 7.25 What does OLAP stand for? SCREEN CAPTURE of SQL Exercises © 2024 Pearson Education, Inc. Page 3 of 3 Purchase answer to see full attachment User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.
