Current address scaddr and phone scphone

CH 8: THE ENHANCED ENTITY-RELATIONSHIP (EER) MODEL

#8.17 - Consider the BANK ER schema of Figure 7.21, and suppose that it is necessary to keep track of different types of ACCOUNTS (SAVINGS_ACCTS, CHECKING_ACCTS, ...) and LOANS (CAR_LOANS, HOME_LOANS, ...). Suppose that it is also desirable to keep track of each account's TRANSACTIONs (deposits, withdrawals, checks, ...) and each loan's PAYMENTs; both of these include the amount, date, time, ... Modify the BANK schema, using ER and EER concepts of specialization and generalization. State any assumptions you make about the additional requirements. (You may use Visio)

#9.4 - Figure 9.8 shows an ER schema for a database that may be used to keep track of transport ships and their locations for maritime authorities. Map this schema into a relational schema, and specify all primary keys and foreign keys.

CH 10: PRACTICAL DATABASE DESIGN METHODOLOGY AND USE OF UML DIAGRAMS

SSN CHAR(9) PRIMARY KEY,

HOMEPHONE VARCHAR(14),

);

Discuss the following detailed design decisions:

e. Any constraints on individual fields.

#10.24 - What naming conventions can you develop to help identify foreign keys more efficiently?

CH 12: XML – EXTENSIBLE MARKUP LANGUAGE

#12.9 – Consider the LIBRARY relational database schema in Figure 4.6. Create an XML schema document that corresponds to this database schema.

1. The university keeps track of each student's name (SNAME), student number (SNUM), social security number (SSSN), current address (SCADDR) and phone (SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate (BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate), major department (MAJORDEPTCODE), minor department (MINORDEPTCODE) (if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both ssn and student number have unique values for each student.

2. Each department is described by a name (DEPTNAME), department code (DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and college (DEPTCOLLEGE). Both name and code have unique values for each department.

#15.20 - What update anomalies occur in the EMP_PROJ and EMP_DEPT relations of Figure 15.3 and 15.4?

#15.21 - In what normal form is the LOTS relation schema in Figure 15.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?

3. Does the resulting relation have an MVD? If so, what is it?

4. What will the final decomposition look like?

CH 17: DISK STORAGE, BASIC FILE STRUCTURES, AND HASHING

#17.28 - A file has r=20,000 STUDENT records of fixed-length. Each record has the following fields: NAME (30 bytes), SSN (9 bytes), ADDRESS (40 bytes), PHONE (9 bytes), BIRTHDATE (8 bytes), SEX (1 byte), MAJORDEPTCODE (4 bytes), MINORDEPTCODE (4 bytes), CLASSCODE (4 bytes, integer), and DEGREEPROGRAM (3 bytes). An additional byte is used as a deletion marker. The file is stored on the disk whose parameters are given in Exercise 17.27.

#17.31 - A PARTS file with Part# as hash key includes records with the following Part# values: 2369, 3760, 4692, 4871, 5659, 1821, 1074, 7115, 1620, 2428, 3943, 4750, 6975, 4981, 9208. The file uses 8 buckets, numbered 0 to 7. Each bucket is one disk block and holds two records. Load these records into the file in the given order using the hash function h(K)=K mod 8. Calculate the average number of block accesses for a random retrieval on Part#.

#17.42 - Suppose that a file initially contains r=120,000 records of R=200 bytes each in an unsorted (heap) file. The block size B=2400 bytes, the average seek time s=16 ms, the average rotational latency rd=8.3 ms and the block transfer time btt=0.8 ms. Assume that 1 record is deleted for every 2 records added until the total number of active records is 240,000.

#18.19 - A PARTS file with Part# as key field includes records with the following Part# values: 23, 65, 37, 60, 46, 92, 48, 71, 56, 59, 18, 21, 10, 74, 78, 15, 16, 20, 24, 28, 39, 43, 47, 50, 69, 75, 8, 49, 33, 38. Suppose the search field values are inserted in the given order in a B + -tree of order p=4 and p leaf =3; show how the tree will expand and what the final tree looks like.

CH 19: ALGORITHMS FOR QUERY PROCESSING AND OPTIMIZATION

#20.8 – Illustrate the types of changes to SQL queries that may be worth considering for improving the performance during database tuning.

CH 21: INTRODUCTION TO TRANSACTION PROCESSING CONCEPTS and THEORY

else write_item(X);

Discuss the final result of the different schedules in Figure 21.3 (a) and (b), where M = 2 and N = 2, with respect to the following questions. Does adding the above condition change the final outcome? Does the outcome obey the implied consistency rule (that the capacity of X is 90)?

#22.26 - Repeat Exercise 22.25, but use the multi-version timestamp ordering method.

CH 23: DATABASE RECOVERY TECHNIQUES

CH 24: DATABASE SECURITY

24.33 - Suppose that privilege (a) of exercise 24.32 is to be given with GRANT OPTION but only so that account A can grant it to at most five accounts, and each of these accounts can propagate the privilege to other accounts but without the GRANT OPTION privilege. What would the horizontal and vertical propagation limits be in this case?