Concept explainers
Explanation of Solution
a.
- Mention a query as a manager of Denver plant to know the amount of time employees are spending on special project:
To know the amount of time employees are spending on special project can be answered directly by the Denver Sum query which is accurate, relevant and sufficient. The answer to amount of time employees are spending on special project can also be answered by the Plant Sum query for Denver and the other two plants.
- Mention a query as a manager of Reno plant to find the amount of time employees are spending on special projects:
There is not a single query that answers the amount of time employees are spending on special project directly; however, Plant sum query can answer this question. Plant sum query is accurate and sufficient but adds extra information as it calculates the total hours for Denver and Chicago plants also.
- Mention a query to know how much time employees are spending on Quota Computation project:
The total amount of time employees are working on Quota computation project can be calculated using Sorted Query 1. These query lists the total hours worked by employees in quota computation project in Chicago project which can be manually added to find the total hours spend on quota computation project by Chicago plant employees. Here the data is sufficient but needs manipulation to produce relevant result and hence accuracy is questionable. The data is sufficient as there is information in the query that is not really needed.
- Mention a query to find the number of hours worked by employees in the quota computation project in all three plants:
To find the number of hours worked by employees in the quota computation project in all three plants cannot be answered directly by any query...
Explanation of Solution
b.
Mention the conclusion that can be drawn by this exercise:
It can be said that one can create queries to answer questions quickly and easily but queries need to be formulated ...
Explanation of Solution
c.
Mention the advantages and disadvantages of databases as compared to spreadsheet:
- It can be inferred that queries in databases are easy to use and can be constructed to produce specific answers...
Want to see the full answer?
Check out a sample textbook solutionChapter AE Solutions
Using MIS (10th Edition)
- Discuss the use of stored procedures and triggers in database development, and provide real-world examples of their application.arrow_forwardYou are given a database containing Customer, Service, Call and Rates tables to provide some results for MMC. (That is, your CC will provide you with the database). You should: 1. Rename the database as MGMT2006_G# , where G# is your group number. 2. Create relationships between pairs of tables and enforce referential integrity. 3. Add TWO new Customers and associate them with a Service. 4. Also add TEN new Calls along with their information for any ten customers. Be sure to include calls for your two additional customers. 5. Create the following queries: a. List the names of customers whose status is disconnected. Name this query Q1 – NOTCONNECTED. b. What is the total duration of calls, pre-paid and post-paid for customers with data-plans and for customers without data-plans? Round the duration to a fixed number with no decimal places. Sort in descending order by duration. Name this query Q2 – CallDuration. c. For each customer who has a post-paid service, show how many of these…arrow_forward2. A table is in 2NF if it is in 1NF and it includes no partial dependencies: a)True b) False 3) A depenndency of one nonprime attrute on another nonprime attribute is a partial dependency a)True b) False 5)A transition period is not required for some database refactoring types a)True b) Falsearrow_forward
- Within this database, you will be using tables throughout entire model, and pick the appropriate tables for each question for this assignment. Based on assignment readings and slides, perform each of the following queries from the fil table. Guided Queries: For the 5 following questions, your submission will be the query that you would write that would return the needed result Write a query that returns the Staff ID for the staff member who is involved in the most rentals. Write a query that returns all the distinct list of film category names. Write a query that counts the rentals of films, by category, and returns the category name and count of rentals. Write a query that returns the First_Name and Last_Name of each actor, and the count of the number of times their films have been rented. Write a query that returns the First_Name and Last_Name of the actor, and the count of the number of time their film has been rented, of the actor with the most rentalsarrow_forwardRelational database integrity rules are especially important to gooddatabase design. Relational database management systems(RDBMSs) enforce integrity rules automatically, but it ismuch safer to make sure your application design conforms to theentity and referential integrity rules.As an upcoming database designer, explain in your own words,entity and referential integrity ruarrow_forwardHi, For this proble I need to create a table called instructor_course_nums within a university database schema. 1. I need to write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the instructor_course_nums table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses. 2. If the instructor already has an entry in the table, then the procedure makes sure the total number of courses taught in the instructor_course_nums table is up-to-date. Here is what I have that answers the first part of the question: CREATE TABLE instructor_course_nums ( id varchar (10), name varchar (30), tot_courses integer, CONSTRAINT instructor_course_nums_pkey PRIMARY KEY (id)); CREATE OR REPLACE PROCEDURE Student_00_insCourseNumsProc(ID varchar(10), name varchar (25), tot_courses…arrow_forward
- . Normalization is the process of organizing data in a database. Thisincludes creating tables and establishing relationship between thosetables.a. Given the data in a tabular format in Table 1 showing the studentsrecords, normalize this table to Second Normal Form (2NF) and statethe primary key.arrow_forwardYou have decided to build the database for John’s system. This question centres around the development of John’s database.Q.3.1 Draw any three tables you will include in John’s database. For each table, provide the following:a. Three fields.b. One record in each table.c. One primary key for each table.d. One foreign key.Q.3.2 Design a logical view for the tables you have specified in Q.3.1.Note: You may use any software tool to create your viewarrow_forwardWhy is an index on a database column created? A:To optimize data integrity B:To optimize data retrievalsC:To optimize data updates D:To optimize data insertionsarrow_forward
- Specify the following queries in SQL on the database schema of attached image (Picture1.png). Retrieve the names of all senior students majoring in ‘CS’ (computer science). Retrieve the names of all courses taught by Professor King in 1998 and 1999. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. Retrieve the name and transcript of each senior student (Class = 5) majoring in CS. A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student. Retrieve the names and major departments of all straight- A students (students who have a grade of A in all their courses). Retrieve the names and major departments of all students who do not have a grade of A in any of their courses.arrow_forwardDiscuss the concept of indexing in database management. How does indexing improve query performance, and what are the considerations for choosing appropriate indexing strategies?arrow_forwardSingle level ordered indexes are used in every DBMS, sometimes deployed automatically, sometimes chosen by the database engineer during implementation. In fig 2, what type of single level ordered index would be deployed by the DBMS for the attribute RENTALS.CID? Group of answer choices B-Tree Primary Index Common Index Clustering Indexarrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr