New Semester
Started
Get
50% OFF
Study Help!
--h --m --s
Claim Now
Question Answers
Textbooks
Find textbooks, questions and answers
Oops, something went wrong!
Change your search query and then try again
S
Books
FREE
Study Help
Expert Questions
Accounting
General Management
Mathematics
Finance
Organizational Behaviour
Law
Physics
Operating System
Management Leadership
Sociology
Programming
Marketing
Database
Computer Network
Economics
Textbooks Solutions
Accounting
Managerial Accounting
Management Leadership
Cost Accounting
Statistics
Business Law
Corporate Finance
Finance
Economics
Auditing
Tutors
Online Tutors
Find a Tutor
Hire a Tutor
Become a Tutor
AI Tutor
AI Study Planner
NEW
Sell Books
Search
Search
Sign In
Register
study help
business
database management systems
Modern Database Management 12th Global Edition Topi Hoffer, Venkataraman - Solutions
List all raw materials that are made of cherry and that have dimensions (thickness and width) of 12 by 12.
List the number of customers living at each state that is included in the Customer_T table.
List the customers who live in California or Washington.Order them by zip code, from high to low.
Which employees were hired during 2005?
List the employees whose last names begin with an L.
Use the Pine Valley database to answer the following questions:a. How many work centers does Pine Valley have?b. Where are they located?
Add an order to the Order_T table and include a sample value for every attribute.a. First, look at the data in the Customer_T table and enter an order from any one of those customers.b. Enter an order from a new customer. Unless you have also inserted information about the new customer in the
Enter sample data of your own choosing into QtyOnHand in the Product_T table. Test the modification you made in Problem and Exercise 6-54 by attempting to update a product by changing the inventory to 10,000 units. Test it again by changing the inventory for the product to–10 units. If you do not
Modify the Product_T table by adding an attribute QtyOnHand that can be used to track the finished goods inventory. The field should be an integer field of five characters and should accept only positive numbers.
What is the average length of time a student stayed (or has stayed) in the program?
Which tutors have a Dropped status and have achieved their certification after 4/01/2015?
How long had each student studied in the adult literacy program?
Which student has the highest Read score?
How many students were matched with someone in the first five months of the year?
What are the TutorIDs for tutors who have not yet tutored anyone?
List the IDs of the tutors who are currently tutoring more than one student.
What is the average Read score for all students? What are the minimum and maximum Read scores?
How many tutors have a status of Temp Stop? Which tutors are active?
Write SQL queries to answer the following questions:a. List the numbers of all sections of course ISM 3113 that are offered during the semester “I-2015.”b. List the course IDs and names of all courses that start with the letters “Data.”c. List the IDs of all faculty members who are
Write SQL queries to answer the following questions:a. What are the courses included in the Section table? List each course only once.b. List all students in alphabetical order by StudentName.c. List the students who are enrolled in each course in Semester I, 2015. Group the students by the
Write SQL queries to answer the following questions:a. Which students are enrolled in Database and Networking?(Hint: Use SectionNo for each class so you can determine the answer from the Registration table by itself.)b. Which instructors cannot teach both Syst Analysis and Syst Design?c. Which
Write SQL queries to answer the following questions:a. How many students are enrolled in Section 2714 in the first semester of 2015?b. Which faculty members have qualified to teach a course since 2008? List the faculty ID, course, and date of qualification.
Write SQL queries to answer the following questions:a. Which students have an ID number that is less than 50000?b. What is the name of the faculty member whose ID is 4756?c. What is the smallest section number used in the first semester of 2015?
Write SQL commands for the following:a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table.b. Now write a command that will remove Lopez from the Student table.c. Create an SQL command that will modify the name of
Write SQL data definition commands for each of the following queries:a. How would you add an attribute, Class, to the Student table?b. How would you remove the Registration table?c. How would you change the FacultyName field from 25 characters to 40 characters?
Because of referential integrity, before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table. Write an SQL assertion that will enforce this constraint.
Use SQL to define the following view:StudentID StudentName 38214 Letersky 54907 Altvater 54907 Altvater 66324 Aiken
The database is not fully normalized. Explain how. What problems could this cause?
Is it possible to use both a WHERE clause and a HAVING clause in the same SQL SELECT statement? If so, what are the different purposes of these two clauses?
Discuss the pros and cons of using dynamic views.
What can be changed about a table definition, using the SQL command ALTER? Can you identify anything about a table definition that cannot be changed using the ALTER command?
Explain the purpose of the CHECK clause within a CREATE TABLE SQL command. Explain the purpose of the WITH CHECK OPTION in a CREATE VIEW SQL command.
In an ORDER BY clause, what are the two ways to refer to the columns to be used for sorting the results of the query?
Within which clauses of an SQL statement can a derived table be defined?
What is a materialized view, and when would it be used?
SQL:2006 and SQL:2008 introduced a new keyword, MERGE. Explain how using this keyword allows one to accomplish updating and merging data into a table using one command rather than two.
What is an identity column? Explain the benefits of using the identity column capability in SQL.
When would the use of the LIKE keyword with the CREATE TABLE command be helpful?
Explain why SQL is called a set-oriented language.
How do you determine the order in which the rows in a response to an SQL query appear?
What are some of the standard SQL functions that can be used in the SELECT clause?
How is the HAVING clause different from the WHERE clause?
If an SQL statement includes a GROUP BY clause, the attributes that can be requested in the SELECT statement will be limited. Explain that limitation.
What is the evaluation order for the Boolean operators(AND, OR, NOT) in an SQL command? How can a query writer be sure that the operators will work in a specific, desired order?
What is the difference between COUNT, COUNT DISTINCT, and COUNT(*) in SQL? When will these three commands generate the same and different results?
What three clauses are contained in most SQL retrieval statements?
Explain and provide at least one example of how to qualify the ownership of a table in SQL. What has to occur for one user to be allowed to use a table in a database owned by another user?
Drawing on material covered in prior chapters, explain the factors to be considered in deciding whether to create a key index for a table in SQL.
What steps should be followed when preparing to create a table?
Explain why it is necessary to limit the kinds of updates performed on data when referencing data through a view.
Explain some possible purposes of creating a view using SQL. In particular, explain how a view can be used to reinforce data security.
Explain how referential integrity is established in databases that are SQL:1999 compliant. Explain how the ON UPDATE RESTRICT, ON UPDATE CASCADE, and ON UPDATE SET NULL clauses differ from one another. What happens if the ON DELETE CASCADE clause is set?
Explain the three classes of SQL commands and when they would be used.
What were the original purposes of SQL, and does SQL as we know it today live up to those standards?
What are some of the advantages and disadvantages to an SQL standard?
Describe a relational DBMS (RDBMS), its underlying data model, its data storage structures, and how data relationships are established.
Explain what capabilities the new temporal features added to the SQL standard in SQL:2011.
What are SQL-92, SQL:1999, and SQL:2011? Briefly describe how SQL:2011 differs from SQL:1999.
Contrast the following terms:a. base table; viewb. dynamic view; materialized viewc. catalog; schema
Define each of the following terms:a. base tableb. data definition languagec. data manipulation languaged. dynamic viewe. materialized viewf. referential integrity constraint g. relational DBMS (RDBMS)h. schema i. virtual table
Create a strategy for reviewing your deliverables generated so far with the appropriate stakeholders. Which stakeholders should you meet with? What information would you bring to this meeting? Would you conduct the reviews separately or together? Who do you think should sign off on your logical and
Create the physical data model for the relational schema you developed in Chapter 4 (and potentially modified in 5-56 above), clearly indicating data types, primary keys, and foreign keys.
Create a data dictionary similar to the metadata table shown in Table 1-1 in Chapter 1 to document your choices.For each table in the relational schema you developed earlier, provide the following information for each field/data element: field name, definition/description, data type, format,
Do you see any justifiable opportunities to denormalize the tables? If so, provide appropriate justification and create a new denormalized schema. Do you need to update your ER diagram based on these decisions?Why or why not?
Contact a database designer or administrator in an organization with which you are familiar. Ask what file organizations are available in the various DBMSs used in that organization. Interview this person to learn what factors he or she considers when selecting an organization for database files.
Denormalization can be a controversial topic among database designers. Some believe that any database should be fully normalized (even using all the normal forms discussed in Appendix B, available on the book’s Web site). Others look for ways to denormalize to improve processing performance.
Using the Web site for this text and other Internet resources, investigate the parallel processing capabilities of several leading DBMSs. How do their capabilities differ?
Find out which database management systems are available at your university for student use. Investigate which data types these DBMSs support. Compare these DBMSs based on the data types supported and suggest which types of applications each DBMS is best suited for, based on this comparison.
Refer to Figure 4-5.For each of the following reports(with sample data), indicate any indexes that you feel would help the report run faster as well as the type of index:a. State, by products (user-specified period)
Consider the EER diagram for Pine Valley Furniture shown in Figure 3-12.Figure 5-9 looks at a portion of that EER diagram.Let’s make a few assumptions about the average usage of the system:• There are 50,000 customers, and of these, 80 percent represent regular accounts and 20 percent represent
Consider the composite usage map in Figure 5-1.After a period of time, the assumptions for this usage map have changed, as follows:• There is an average of 50 supplies (rather than 40) for each supplier.• Manufactured parts represent only 30 percent of all parts, and purchased parts represent
Create a join index on the CustomerID fields of the Customer_T and Order_T tables in Figure 4-4.
Consider the EER diagram in Figure 4-33.Let’s make the following assumptions:• There are 12,500,000 customers.• These customers have altogether 40,000,000 card accounts. Of these 80 percent are credit card accounts and 20 percent are debit card accounts.• There are 3,200,000 merchants who
Parallel query processing, as described in this chapter, means that the same query is run on multiple processors and that each processor accesses in parallel a different subset of the database. Another form of parallel query processing, not discussed in this chapter, would partition the query so
Suggest an application for each type of file organization.Explain your answer.
Consider Figure 4-38 and your answer to Problem and Exercise 4-48 in Chapter 4.Assume that the most important reports that the organization needs are as follows:• A list of the current developer’s project assignments• A list of the total costs for all projects• For each team, a list of its
Specify the format for Oracle date data type. How does it account for change in century? What is the purpose of ‘TIMESTAMP WITH LOCAL TIMEZONE’? Suppose system time zone in database in City A =-9:00 and city B=-4:00. A client in city B inserts TIMESTAMP “2004-6-14 7:00:00 -4:00” in city A
Consider the relations specified in Problem and Exercise 5-37.Assume that the database has been implemented without denormalization. Further assume that the database is global in scope and covers thousands of leagues, tens of thousands of teams, and hundreds of thousands of players. In order to
Assume that the table BOOKS in a database with primary key on BookID has more than 25,000 records. A query is frequently executed in which Publisher of the book appears in the Where clause. The Publisher field has more than 100 different values and length of this field is quite long. Using the
Suppose each record in a file were connected to the prior record and the next record in key sequence using pointers. Thus, each record might have the following format:Primary key, other attributes, pointer to prior record, pointer to next recorda. What would be the advantages of this file
Search the Internet for at least three examples where parallel processing is applied. How was underlying database prepared for this?What were the advantages of this implementation?
Use the Internet to search for examples of each type of horizontal partitioning provided by Oracle. Explain your answer.
Consider the following normalized relations for a sports league:TEAM(TeamID, TeamName, TeamLocation, TeamManager)PLAYER(PlayerID, PlayerFirstName, PlayerLastName, PlayerDateOfBirth, PlayerSpecialtyCode)SPECIALTY(SpecialtyCode, SpecialtyDescription)Salary)LOCATION(LocationID, CityName, CityState,
Consider the relations in Problem and Exercise 5-35.Please identify possible opportunities for denormalizing these relations as part of the physical design of the database. Which ones would you be most likely to implement?
Consider the following set of normalized relations from a database used by a mobile service provide to keep track of its users and advertiser customers.USER(UserID, UserLName, UserFName, UserEmail, UserYearOfBirth, UserCategoryID, UserZip)ADVERTISERCLIENT(ClientID, ClientName, ClientContactID,
Pick any financial institution such as bank or an insurance company. Identify the regulations they comply with for financial data reporting. Interview the database administrator of the firm and understand which data integrity controls they have implemented into their database. What impact such
When students fill forms for admission to various courses or to write exam, they leave many missing values. This may lead to issues while compiling data. Can this be handled at the data capture stage? What are the alternate approaches to handling such missing data?
In a normalized database, all customer information is stored in Customer table, his invoices are stored in Invoice table, and related account manager information in Employee table. Suppose a customer changes his address and then demands old invoices with manager information, will denormalization be
Say that you are interested in storing the numeric value 3,456,349.2334. What will be stored, with each of the following Oracle data types:a. NUMBER(11)b. NUMBER(11,1)c. NUMBER(11,-2)d. NUMBER(11,6)e. NUMBER(6)f. NUMBER
Explain in your own words what the precision (p) and scale(s) parameters for the Oracle data type NUMBER mean.
Choose Oracle data types for the attributes in the normalized relations that you created in Problem and Exercise 4-47 in Chapter 4.
Choose Oracle data types for the attributes in the normalized relations in Figure 5-4b.
Consider the following two relations for a firm:EMPLOYEE(EmployeeID, EmployeeName, Contact, Email) PERFORMANCE(EmployeeID, DepartmentID, Rank)Following is a typical query against these relations:SELECT Employee_T.EmployeeID, EmployeeName, DepartmentID, Grade FROM Employee _T, Performance_T
Explain the reasons underlying the significant performance gains in query performance that can be achieved with parallel processing.
Database servers frequently use one of the several parallel processing architectures. Discuss which elements of a query can be processed in parallel.
Explain why an index is useful only if there is sufficient variety in the values of an attribute.
One of the recommendations regarding indexes is to specify a unique index for each primary key. Explain the justification for this recommendation.
State nine rules of thumb for choosing indexes.
What is the most important mechanism that database designers can use to impact database performance in specific use situations?
What is the purpose of EXPLAIN/EXPLAIN PLAN command?
Showing 500 - 600
of 3225
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Last
Step by Step Answers