Fundamentals Of Database Systems 7th Edition Ramez Elmasri, Shamkant Navathe - Solutions

Consider that the EMPLOYEE table’s constraint EMPSUPERFK as specified in Figure 6.2 is changed to read as follows:CONSTRAINT EMPSUPERFKFOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
Design a relational database schema for a database application of your choice.a. Declare your relations using the SQL DDL.b. Specify a number of queries in SQL that are needed by your database application.c. Based on your expected use of the database, choose some attributes that should have indexes
Specify the following queries in SQL on the COMPANY relational database schema shown in Figure 5.5. Show the result of each query if it is applied to the COMPANY database in Figure 5.6.a. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX
Write appropriate SQL DDL statements for declaring the LIBRARY relational database schema of Figure 6.6. Specify the keys and referential triggered actions.Data from figure 6.6 BOOK Book_id Title Publisher_name BOOK AUTHORS Book_id Author_name PUBLISHER Name Address Phone воOK COPIES Book id
Consider the LIBRARY relational database schema shown in Figure 6.6. Choose the appropriate action (reject, cascade, set to NULL, set to default) for each referential integrity constraint, both for the deletion of a referenced tuple and for the update of a primary key attribute value in a
Repeat Exercise 6.5, but use the AIRLINE database schema of Figure 5.8.Exercise 6.5Consider the database shown in Figure 1.2, whose schema is shown in Figure 2.1. What are the referential integrity constraints that should hold on the schema? Write appropriate SQL DDL statements to define the
How does SQL allow implementation of the entity integrity and referential integrity constraints described in Chapter 3? What about referential triggered actions?
List the data types that are allowed for SQL attributes.
How do the relations (tables) in SQL differ from the relations defined formally in Chapter 3? Discuss the other differences in terminology. Why does SQL allow duplicate tuples in a table or in a query result?ER ModelRelational ModelEntity typeEntity relation1:1 or 1:N relationship typeForeign
Why do we designate one of the candidate keys of a relation to be the primary key?
What is the difference between a key and a superkey?
Why are duplicate tuples not allowed in a relation?
Consider the ER diagram in Figure 3.24. Assume that a course may or may not use a textbook, but that a text by definition is a book that is used in some course. A course may not use more than five books. Instructors teach from two to four courses. Supply (min, max) constraints on this diagram.
Discuss the naming conventions used for ER schema diagrams.
Discuss the conventions for displaying an ER schema as an ER diagram.
What is meant by a recursive relationship type? Give some examples of recursive relationship types.
Define foreign key. What is this concept used for?
What is a transaction? How does it differ from an Update operation?
Discuss the entity integrity and referential integrity constraints. Why is each considered important?
What is the role of Armstrong’s inference rules (inference rules IR1 through IR3) in the development of the theory of relational design?
What is meant by the attribute preservation condition on a decomposition?
What is the dependency preservation property for a decomposition? Why is it important?
Illustrate how the process of creating first normal form relations may lead to multivalued dependencies. How should the first normalization be done properly so that MVDs are avoided?
What types of constraints are inclusion dependencies meant to represent?
Show that the relation schemas produced by Algorithm 15.5 are in BCNF.Algorithm 15.5. Relational Decomposition into BCNF with Nonadditive Join PropertyInput: A universal relation R and a set of functional dependencies F on the attributes of R.1. Set D := {R} ;2. While there is a relation schema Q
Specify all the inclusion dependencies for the relational schema in Figure 5.5.Figure 5.5 ΕMPLOYEE Fname Minit Lname Ssn Address Sex Salary Super_ssn Dno Bdate DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno
Consider the following decompositions for the relation schema R of Exercise 14.24. Determine whether each decomposition has (1) The dependency preservation property, and (2) The lossless join property, with respect to F. Also determine which normal form each relation in the
Why are disks, not tapes, used to store online database files?
What are the components of a disk block address?
How does double buffering improve block access time?
What are the reasons for having variable-length records? What types of separator characters are needed for each?
Suggest extensions to the relational calculus so that it may express the following types of operations that were discussed in Section 8.4: (a) Aggregate functions and grouping; (b) OUTER JOIN operations; (c) Recursive closure queries.
In what normal form is the LOTS relation schema in Figure 14.12(a) with respect to the restrictive interpretations of normal form that take only the primary key into account? Would it be in the same normal form if the general definitions of normal form were used?Figure 14.12(a) Candidate Key (a)
Why do spurious tuples occur in the result of joining the EMP_PROJ1 and EMP_ LOCS relations in Figure 14.5 (result shown in Figure 14.6)?Figure 14.5Figure 14.6 Ssn Pnumber Hours Pname Plocation Ename 123456789 1 32.5 ProductX Bellaire Smith, John B. 123456789 1 32.5 ProductX Bellaire English, Joyce
Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = {{A, B}→{C}, {A}→{D, E}, {B}→{F}, {F}→{G, H}, {D}→{I, J}}. What is the key for R? Decompose R into 2NF and then 3NF relations.
Repeat Exercise 14.24 for the following different set of functional dependenciesG = {{A, B}→{C}, {B, D}→{E, F}, {A, D}→{G, H}, {A}→{I}, {H}→{J}}.Exercise 14.24Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = {{A, B}→{C},
Consider the following relation:CAR_SALE(Car#, Date_sold, Salesperson#, Commission%, Discount_amt)Assume that a car may be sold by multiple salespeople, and hence {Car#, Salesperson#} is the primary key. Additional dependencies areDate_sold → Discount_amt andSalesperson# → Commission%Based on
Consider the following relation:TRIP (Trip_id, Start_date, Cities_visited, Cards_used)This relation refers to business trips made by company salespeople. Suppose the TRIP has a single Start_date but involves many Cities and salespeople may use multiple credit cards on the trip. Make up a mock-up
What is meant by the closure of a set of functional dependencies? Illustrate with an example.
When are two sets of functional dependencies equivalent? How can we determine their equivalence?
What is a minimal set of functional dependencies? Does every set of dependencies have a minimal equivalent set? Is it always unique?
Why can we not guarantee that BCNF relation schemas will be produced by dependency-preserving decompositions of non-BCNF relation schemas? Give a counterexample to illustrate this point.
What is the lossless (or nonadditive) join property of a decomposition? Why is it important?
Between the properties of dependency preservation and losslessness, which one must definitely be satisfied? Why?
Discuss the NULL value and dangling tuple problems.
How do template dependencies differ from the other types of dependencies we discussed?
Why is the domain-key normal form (DKNF) known as the ultimate normal form?
Define the following terms: disk, disk pack, track, block, cylinder, sector, interblock gap, and read/write head.
Discuss the process of disk initialization.
Why is accessing a disk block expensive? Discuss the time components involved in accessing a disk block.
Discuss the techniques for allocating file blocks on disk.
What is the difference between a file organization and an access method?
What are the typical record-at-a-time operations for accessing a file? Which of these depend on the current file record?
Discuss the techniques for record deletion.
Discuss the advantages and disadvantages of using (a) An unordered file,(b) An ordered file, and (c) A static hash file with buckets and chaining.Which operations can be performed efficiently on each of these organizations, and which operations are expensive?
Discuss the techniques for allowing a hash file to expand and shrink dynamically. What are the advantages and disadvantages of each?
What is the difference between the directories of extendible and dynamic hashing?
What are mixed files used for? What are other types of primary file organizations?
Consider the LIBRARY database schema shown in Figure 4.6. Write PHP code to create the tables of this schema.Figure 4.6 Figure 4.6 A specialization lattice with shared subclass ENGINEERING_MANAGER. EMPLOYEE SECRETARY| TECHNICIAN ENGINEER MANAGER HOURLY EMPLOYEE SALARIED_EMPLOYEE ENGINEERING MANAGER
What update anomalies occur in the EMP_PROJ and EMP_DEPT relations of Figures 14.3 and 14.4?Figures 14.3Figures 14.4 Redundancy ЕMP DEPT Ename Smith, John B. Wong, Franklin T. Zelaya, Alicia J. Wallace, Jennifer S. 987654321 1941-06-20 291 Berry, Bellaire, TX Narayan, Ramesh K. 666884444
Why do practical database designs typically aim for BCNF and not aim for higher normal forms?
Why is 5NF also called project-join normal form (PJNF)?
Define join dependency and fifth normal form.
Define fourth normal form. When is it violated? When is it typically applicable?
Does a relation with two or more columns always have an MVD? Show with an example.
Define Boyce-Codd normal form. How does it differ from 3NF? Why is it considered a stronger form of 3NF?
What is multivalued dependency? When does it arise?
In what way do the generalized definitions of 2NF and 3NF extend the definitions beyond primary keys?
What undesirable dependencies are avoided when a relation is in 3NF?
What undesirable dependencies are avoided when a relation is in 2NF?
Define first, second, and third normal forms when only primary keys are considered. How do the general definitions of 2NF and 3NF, which consider all keys of a relation, differ from those that consider only primary keys?
What does the term unnormalized relation refer to? How did the normal forms develop historically from first normal form up to Boyce-Codd normal form?
What is a functional dependency? What are the possible sources of the information that defines the functional dependencies that hold among the attributes of a relation schema?
State the informal guidelines for relation schema design that we discussed. Illustrate how violation of these guidelines may be harmful.
Why should NULLs in a relation be avoided as much as possible? Discuss the problem of spurious tuples and how we may prevent it.
Discuss attribute semantics as an informal measure of goodness for a relation schema.
Specify the following views as queries in XQuery on the company XML schema shown in Figure 13.5.a. A view that has the department name, manager name, and manager salary for every departmentb. A view that has the employee name, supervisor name, and employee salary for each employee who works in the
Consider the LIBRARY relational database schema in Figure 6.6. Create an XML schema document that corresponds to this database schema.Figure 6.6 BOOK Book id Title Publisher_name BOOK AUTHORS Book_id Author_name PUBLISHER Name Address Phone вооK COPIES Book id Branch_id No_of_copies BOOK LOANS
Create XML schema documents and XML DTDs to correspond to the hierarchies shown in Figures 13.14 and 13.15(c).Figures 13.14Figures 13.15 M STUDENT SECTION DEPARTMENT INSTRUCTOR COURSE1 INSTRUCTOR1 COURSE (c)
Create part of an XML instance document to correspond to the data stored in the relational database shown in Figure 5.6 such that the XML document conforms to the XML schema document in Figure 13.5.Figure 5.6Figure 13.5 Figure 13.5 (continued) An XML schema file called company.
What is the difference between XML schema and XML DTD?
What is the difference between attributes and elements in XML? List some of the important attributes used to specify elements in XML schema.
What are the differences between the use of tags in XML versus HTML?
Map the COMPANY ER schema in Figure 3.2 into ODL classes. Include appropriate methods for each class.Figure 3.2 Fname Minit Lname Bdate Name Address Salary Ssn Sex Locations N WORKS FOR Name Number EMPLOYEE Start_date Number_of_employees DEPARTMENT MANAGES CONTROLS Hours N. M N WORKS ON PROJECT
Consider the AIRPORT database described in Exercise 4.21. Specify a number of operations/methods that you think should be applicable to that application. Specify the ODL classes and methods for the database.Exercise 4.21 Figure 4.12 shows an example of an EER diagram for a
Consider the COMPANY ER schema in Figure 3.2. Think of what operations are needed for the entity types/classes in the schema. Do not consider constructor and destructor operations.Figure 3.2 Fname Minit Lname Bdate Name Address Salary Ssn Sex Locations N WORKS FOR Name Number EMPLOYEE Start_date
Consider the UNIVERSITY EER schema in Figure 4.10. Think of what operations are needed for the entity types/classes in the schema. Do not consider constructor and destructor operations.Figure 4.10 PERSON Name Ssn Birth_date Sex Address age ΕMPLOYE Ε ALUMNUS DEGREE STUDENT Salary Year Major_dept
Convert the example of GEOMETRY_OBJECTs given in Section 12.1.5 from the functional notation to the notation given in Figure 12.2 that distinguishes between attributes and operations. Use the keyword INHERIT to show that one class inherits from another class.Figure 12.2 define class EMPLOYEE type
Describe the steps of the algorithm for object database design by EER-to- OO mapping.
What are the main differences between designing a relational database and an object database?
Discuss the general principles behind the C++ binding of the ODMG standard.
What is meant by the type orthogonality of OQL?
Describe the following OQL concepts: database entry points, path expressions, iterator variables, named queries (views), aggregate functions, grouping, and quantifiers.
Why are the concepts of extents and keys important in database applications?
Discuss how persistence is specified in the ODMG object model in the C++ binding.
What are the differences and similarities of class inhertance via extends and interface inheritance via “:” in the ODMG object model?
What are the differences and similarities of attribute and relationship properties of a user-defined (atomic) class?
Describe the built-in structured literals of the ODMG object model and the operations of each.
List the basic operations of the following built-in interfaces of the ODMG object model: Object, Collection, Iterator, Set, List, Bag, Array, and Dictionary.
What are the differences and similarities between objects and literals in the ODMG object model?

Showing 400 - 500 of 606

Fundamentals Of Database Systems 7th Edition Ramez Elmasri, Shamkant Navathe - Solutions

Step by Step Answers