Get questions and answers for Databases

GET Databases TEXTBOOK SOLUTIONS

1 Million+ Step-by-step solutions
math books
Give some examples of database utilities.
Give some examples of vendor provided tools
Distinguish between the DBMS and a file management system.
List the major functions performed by the DBA
What do you understand by the term metadata?
Distinguish between logical and physical data independence.
List the major functions performed by the DBMS
Describe the sequence of steps involved in retrieving a particular external record occurrence.
Explain the following in your own words:
Write SQL statements to perform the following operations on the wine cellar database:
a. Get bin number, name of wine, and number of bottles for all Geyser Peak wines.
b. Get bin number and name of the wine for all wines for which there are more than five bottles in the stock. c. Get bin number for all red wines.
Show the effects of the following SQL update operations on the wine cellar database of fig:
a. INSERT INTO CELLAR(BIN#, WINE, PRODUCER, YEAR, BOTTLES, READY) VALUES(80, ‘Syrah’, ‘Meridian’, 1998, 12, 2003);
b. DELETE FROM CELLAR WHERE READY > 2004;
c. UPDATE CELLAR SET BOTTLES = 5 WHERE BIN# = 50;
d. UPDATE CELLAR SET BOTTLES = BOTTLES + 2 WHERE BIN# = 50;
Give in your own words an interpretation as a true proposition of a typical row from each of your answers to Exercise 1.5:
Show the effects of the following SQL retrieval operations on the wine cellar database of Fig:
a. SELECT WINE, PRODUCER.
FROM CELLAR WHERE BIN# = 72 ;
b. SELECT WINE, PRODUCER
FROM CELLAR WHERE YEAR > 2000;
c. SELECT BIN#, WINE, YEAR
FROM CELLAR WHERE READY < 2003;
d. SELECT WINE, BIN#, YEAR
FROM CELLAR WHERE PRODUCER = ‘Robt Mondavi’ AND BOTTLES > 6;
What do you understand by the term data model? Explain the difference between a data model and its implementation. Why is the difference important?
What do you understand by the term relational system? Distinguish between relational and non-relational system.
What are the advantages of using a database system? What are the disadvantages?
Explain the following in your own words:
• Binary relationship
• Command driven interface
• Concurrent access
• Data administration
• Database
• Database system
• Data independence
• DBA
• DBMS
• Entity
• Entity/relationship diagram
• Forms driven interface
• Integration
• integrity
• Menu driven interface
• Multi-user system
• Online application
• Persistent data
• Property
• Query language
• Redundancy
• Relationship
• Security
• Sharing
• Stored field
• Stored file
• Stored record
• transaction
Explain the following in your own words:
• Automatic navigation
• Base relvar
• Catalog
• Closure
• Commit
• Derived relvar
• Foreign key
• Join
• Optimization
• Predicate
• Primary key
• Projection
• Proposition
• Relational database
• Relational DBMS
• Relational model
• Restriction
• Rollback
• Set-level operation
• View
Sketch the contents of the catalog relvars TABLE and COLUMN for the suppliers and parts database.
As explained in the section 3.6, the catalog is self-describing that is, it includes entries for the catalog relvars themselves. Extend Fig. 3.6 to include the necessary entries for the TABLE and COLUMN relvars themselves.
Here is a query on the suppliers and parts database. What does it do? What is the predicate for the result? ((S JOIN SP) WHERE P# = P# (‘P2’) ) {S#, CITY}
Suppose the expression in Exercise 3.4 is used in a view definition: CREATE VIEW V AS (S JOIN SP) WHERE P# - P# (‘P2’) ) {S#, CITY}; Now consider this query: (V WHERE CITY = ‘London’ ) {S#} What does this query do? What is the predicate for the result? Show what is involved on the part of the DBMS in processing this query.
What do you understand by the terms atomicity, durability, isolation, and serializability as applied to transactions?
State the information Principle.
The fig shows some sample data values for an extended form of the suppliers and parts database called the suppliers parts projects database. Suppliers (S), parts (P), and projects (J) are uniquely identified by supplier number (S#), part number (P#), and project number (J#), respectively. The predicate for SPJ (Shipments) is: Supplier S# supplies part P# to project J# in quantity QTY (the combination {S#, P#, J#} is the primary key, as the figure indicates). Write an appropriate set of SQL definitions for this database. Note: This database will be used as the basis for numerous exercises in subsequent chapters.
Give SQL formulations for the following updates to the suppliers parts projects database:
a. Insert a new supplier S10 into table S (the name and city are Smith and New York respectively;
the status is not yet known.
b. Delete all projects for which there are no shipments.
c. Change the color of all red parts to orange.
Again using the suppliers parts projects database, write a program with embedded SQL statements to list all suppliers in supplier number order. Each supplier should be immediately followed in the listing by all projects supplied by that supplier, in project number order.
Let tables PART and PART_STRUCTURE be defined as follows: CREATE TABLE PART (P# P#, DESCRIPTION CHAR(100), PRIMARY KEY (P#)); CREATE TABLE PART STRUCTURE ( MAJOR P#, P#, MINOR P# P#, QTY QTY, PRIMARY KEY (MAJOR_P#, MINOR P#), FOREIGN KEY (MAJOR_P#) REFERENCES PART, FOREIGN KEY (MAJOR_P#) REFERENCES PART); Table PART_STRUCTURE shows which parts (MAJOR_P#) contain which other parts (MINOR_P#) as first level components. Write an SQL program to list all component parts of a given part, to all levels .
State the type rules for the assignment (“: =”) and equality comparison (“=”) operators.
Explain the following in you own words:
Coercion pseudo variable
Generated type selector
Literal strong typing
Ordinal type The-operator
Polymorphic operator type generator
Why are pseudo variable logically unnecessary?
Define and operator that, given a rational number, returns the cube of that number,
Define a read-only operator that, given a point with Cartesian coordinates x and y, returns the point with Cartesian coordinates f(x) and g(y), where f and g are predefined operators.
Repeat Exercise 5.6 but make the operator an update operator.
Give a type definition for a scalar type called CIRCLE, what selectors and The-operators apply to this type? Also:
a. Define a set of read only operators to compute the diameter, circumference, and area of a given circle.
b. Define an update operator to double the radius of a given circle (more precisely, to update a given Circle variable in such a way that its circle value is unchanged except that the radius is twice what it was before).
Give some examples of types for which it might be useful to define two or more distinct possible representations. Can you think of an example where distinct possible representations for the same type have different numbers of components?
Given an appropriate set of scalar type definitions for the suppliers-parts-projects database do not attempt to write the relvar definitions.

We pointed out that it is strictly incorrect to say that (e.g.) the quantity for a certain shipment is 100 (“a quantity is a value of type QTY, not a value of type INTEGER”). As a consequence, inasmuch as it pretends that it is correct to think of, for example, quantities as integers.
It is sometime suggested that types are really variables too, like relvar, for example, legal employee number might grow from three digits to four as a business expands, so we might need to update “the set of all possible employee number”, Discuss,

SQL has no formal row array literals. Explain and justify this observation.

What do you understand by the term cardinality?
Write tuple selector invocations for a typical tuple from each of the relvars in the suppliers-parts-projects database.
What do you understand by the term first normal form?
Give an example of your own of a relation with
(a) One relation-valued attribute and
(b) Two such attributes. Also, give two more relation that represent the same information as those relations but do not involve relation-valued attributes.
Write an expression that returns true if the current value of the parts relvar P is empty and false otherwise. Do not use the is-empty shorthand.
State the closed World Assumption.
It is sometimes suggested that a relvar is really just a traditional computer file, with “tuples” instead of records and “attributes” instead of fields. Discuss.
Give Tutorial d formulations for the following updates to the suppliers-parts-projects data-base;
(a) Insert a new shipment with supplier number S1, part number P1, project number J2, quantity 500.
(b) Insert a new supplier S10 into table s (the name and city are Smith and New York, respectively; the status is not yet known).
(c) Delete all blue parts.
(d) Delete all projects for which there are no shipments.
(e) Change the color of all red parts to orange.
(f) Replace all appearances of supplier number S1 by appearances of supplier number S9 instead.
We have seen that data definition operations cause updates to be made to the catalog. But the catalog is only a collection of relvars, just like the rest of the database; so could we not use the regular update operations insert. Delete, and update to update the catalog appropriately? Discuss.
In the body of the chapter, we said that any type whatsoever can be used as the basis for defining relational attributes, in general. That qualifier “in general” was there for a reason, however. Can you think of any exceptions to this general rule?
What do you understand by the SQL terms column, field, and attribute?
Consider the SQL type point and the SQL table points as defined in the subsection “Structured Types” Type Point has a representation involving Cartesian coordinates X and Y. What happens if we replace that type by a revised type point with a representation involving polar coordinates R and θ instead?
Which of the relational operators defined in this chapter have a definition that does not rely on tuple equality?
Given the usual suppliers-and-parts database, what is the value of the expression S join Sp join P? What is the corresponding predicate? Warning; there is a trap here.
Let r be a relation of degree n. How many different projections of r are there?
Union, intersection, product, and natural join are all both commutative and associative. Verify these claims.
Of Cod’s original set of eight operators, union, difference, product, restrict, and project can be considered as primitives. Give definitions of natural join, intersect, and (harder) divide in terms of those primitives.
In ordinary arithmetic, multiplication and division are inverse operation. Are times and divide by inverse operations in the relation algebra?
In ordinary arithmetic there are two special numbers, I and 0, with the properties that n * 1 = 1 * n = n
And n * 0 = 0 * n = 0 for all number n. What relations (if any) play analogous roles in the relational algebra? Investigate the effect of the algebraic operations discussed in this chapter on those relations.
Given that intersect is a special case of join, why do not both operators give the same result when applied to no relation at all?
Which (if any) of the following expressions are equivalent?
(a) Summarize r per r ( ) add count as ct
(b) Summarize r add count as ct
(c) Summarize r by ( ) add count as ct
(d) Extend TABLE-DEE add count (r) as ct
Let r be the relation denoted by the following expression; SP group () as x
Show what r looks like, given our usual sample value for SP. Also, show the result of; r ungroup x
Get all supplier-number/part-number/project-number triples such that the indicated supplier, part, and project are not all collocated.
Get all supplier-number/part-number/project-number triples such that no two of the indicated supplier, part, and project are collocated.
Get part numbers for parts supplied by a supplier in London to a project in London.
Get all pairs of city names such that a supplier in the first city supplies a project in the second city.
Get project numbers for projects supplied by at least one supplier not in the same city.
Get all pairs of part numbers such that some supplier supplies both the indicated parts.
Get the total number of projects supplied by supplier S1.
Get the total number of part P1 supplied by supplier S1
Get part number of parts supplied to some project in an average quantity of more than 350.
Get supplier number for suppliers with a status lower than that of supplier S1.
Get supplier number for projects supplied with part P1 in an average quantity greater than the greatest quantity in which any part is supplied to project J1.
Get supplier numbers for suppliers supplying some project with part P1 in a quantity greater than the average shipment quantity of part P1 for that project J1?
Get project numbers for projects not supplied with any red part by any London supplier.
Get part numbers for parts supplied to all projects in London.
Get project number for projects supplied with at least all parts available from supplier S1.
Get part numbers for parts that are supplied either by a London supplier or to a London project.
Get all pairs of supplier numbers, Sx and Su say, such that Sx and Sy supply exactly the same set of parts each. Note for simplicity, you might want to use the original suppliers-and-parts data-base for this exercise, instead of the expanded Suppliers-parts-projects database.
List four significant differences between a file-processing system and a DBMS.
This chapter has described severalmajor advantages of a database system.What are two disadvantages?
List five responsibilities of a database management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged.
What are five main functions of a database administrator?
List seven programming languages that are procedural and two that are nonprocedural. Which group is easier to learn and use? Explain your answer.
List six major steps that you would take in setting up a database for a particular enterprise.
Consider a two-dimensional integer array of size n × m that is to be used in your favorite programming language. Using the array as an example, illustrate the difference
(a) Between the three levels of data abstraction, and
(b) Between a schema and instances.
Explain the difference between physical and logical data independence.
Explain the distinctions among the terms primary key, candidate key, and superkey.
Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.
Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.
A university registrar’s office maintains data about the following entities:
(a) Courses, including number, title, credits, syllabus, and prerequisites;
(b) Course offerings, including course number, year, semester, and section number, instructor(s), timings, and classroom;
(c) Students, including student-id, name, and program; and
(d) Instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints.
Consider a database used to record the marks that students get in different exams of different course offerings.
a. Construct an E-R diagram that models exams as entities, and uses a ternary relationship, for the above database.
b. Construct an alternative E-R diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course offering pair, yet you can represent the marks that a student gets in different exams of a course offering.
Construct appropriate tables for each of the E-R diagrams in Exercises 2.2 to 2.4.
Design an E-R diagramfor keeping track of the exploits of your favourite sports team. You should store thematches played, the scores in eachmatch, the players in each match and individual player statistics for each match. Summary statistics should be modeled as derived attributes.
Extend the E-R diagram of the previous question to track the same information for all teams in a league.
Explain the difference between a weak and a strong entity set.
We can convert any weak entity set to a strong entity set by simply adding appropriate attributes.Why, then, do we have weak entity sets?
Define the concept of aggregation. Give two examples of where this concept is useful.
Consider an E-R diagram in which the same entity set appears several times. Why is allowing this redundancy a bad practice that one should avoidwhenever possible?
Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the exam entity set, as
• Course with attributes name, department, and c-number
• Section with attributes s-number and enrollment, and dependent as a weak entity set on course
• Room with attributes r-number, capacity, and building
a. Show an E-R diagram illustrating the use of all three additional entity sets listed.
b. Explain what application characteristics would influence a decision to include or not to include each of the additional entity sets
Showing 1 - 100 of 2960
Join SolutionInn Study Help for
1 Million+ Textbook Solutions
Learn the step-by-step answers to your textbook problems, just enter our Solution Library containing more than 1 Million+ textbooks solutions and help guides from over 1300 courses.
24/7 Online Tutors
Tune up your concepts by asking our tutors any time around the clock and get prompt responses.