question number 4, 5, 6, 7, 8, 10. on the practical assignments!!

The Physical Data Mo Review Questions What are the main goals of physical data design het DBMS features are used for performing physical design? How does storage organization attect performance on the database What types of organized storage do you know? When is it recommended to use clusters? to what situations is it beneficial to distribute date? what are the benefits of the stier architecture of a database does the DBMS read data from a table and process the data? What table storage parameters do you know? What is the role of each of them? What types of organized data storage in Oracle do you know? How would you implement transparency for a distributed database? Practical Assignments Describe the database storage hierarchy. Explain how a table's data is stored in a database. Explain how parameters PCTFREE and PCTUSED control the packing of data in data blocks 3Choose the data types for the table Student (ID, name, dateOfBirth) and explain your decisions. Choose data types for the columns of the table T (A, B, C, D) and specify column constraints for the following conditions: Values of the column B are alphanumeric strings five symbols long, and for each row of the table, the column B must always have a value and this value has to be unique. The column C is designed to store numeric values not greater than 50000; users often request totals of the column C. The column D stores dates of the year 2005. 5. Estimate PCTFREE and PCTUSED for the following tables and situations a CREATE TABLE Ti (fieldl NUMBER, field2 CHAR(3), field3 DATE). Intensive updating, inserting and deleting activities are expected. b. CREATE TABLE T2 (fieldl NUMBER, field2 VARCHAR(250)). Intensive inserting and deleting activities are expected. c. CREATE TABLE T3 (fieldi VARCHAR2(50), VARCHAR2(300)). Intensive updating activities are expected. d. CREATE TABLE T1 (fieldi NUMBER, field2 CHAR(3), field3 DATE). Intensive retrieving activities are expected. e CREATE TABLE T3 (field1 VARCHAR2(50), field2 VARCHAR2(300)). Intensive retrieving activities are expected. A database contains two tables TI (A, B, C) and T2 (C, D, E). The column C of Ti is the foreign key to T2. Explain in which of the following situations it is reasonable to use clusters, and create clusters and clustered tables. 1. The data in the tables are often modified. elational Databases Essential Aspects of Physical Design and Implementation of Relational D HERE C = x on the table WHERE C x on the table 1 b. The performance of select queries with conditions ... WHERE C T1 is important. c. The performance of queries with conditions ... WHERE C important. d. The performance of queries with conditions ... WHERE C>xo important. e. The performance of the queries on the join of the tables is important > x on the table This 7. Explain when it is beneficial to store the table T (A, B, C)_as index-organized 8. Define the difference in conditions for your decisions to store the table Employee clustered or partitioned by the attribute emplType. 9. Define the difference in conditions for your decisions to store the table Employee as distributed or partitioned by the attribute emplType. 10. What would be your choice of the storage organization of the table Employee if the following querying patterns were the most important ones: a.... WHERE ID - x; b.... WHERE empl Name - X; C.... WHERE dept Code ; d. ... WHERE titleCode BETWEEN X and y; e.... WHERE titleCode =x; 11. Describe distributed data design for one of the case studies from Appendix 1. 12 Build the physical data model for one of the case studies from Appendix 1 The Physical Data Mo Review Questions What are the main goals of physical data design het DBMS features are used for performing physical design? How does storage organization attect performance on the database What types of organized storage do you know? When is it recommended to use clusters? to what situations is it beneficial to distribute date? what are the benefits of the stier architecture of a database does the DBMS read data from a table and process the data? What table storage parameters do you know? What is the role of each of them? What types of organized data storage in Oracle do you know? How would you implement transparency for a distributed database? Practical Assignments Describe the database storage hierarchy. Explain how a table's data is stored in a database. Explain how parameters PCTFREE and PCTUSED control the packing of data in data blocks 3Choose the data types for the table Student (ID, name, dateOfBirth) and explain your decisions. Choose data types for the columns of the table T (A, B, C, D) and specify column constraints for the following conditions: Values of the column B are alphanumeric strings five symbols long, and for each row of the table, the column B must always have a value and this value has to be unique. The column C is designed to store numeric values not greater than 50000; users often request totals of the column C. The column D stores dates of the year 2005. 5. Estimate PCTFREE and PCTUSED for the following tables and situations a CREATE TABLE Ti (fieldl NUMBER, field2 CHAR(3), field3 DATE). Intensive updating, inserting and deleting activities are expected. b. CREATE TABLE T2 (fieldl NUMBER, field2 VARCHAR(250)). Intensive inserting and deleting activities are expected. c. CREATE TABLE T3 (fieldi VARCHAR2(50), VARCHAR2(300)). Intensive updating activities are expected. d. CREATE TABLE T1 (fieldi NUMBER, field2 CHAR(3), field3 DATE). Intensive retrieving activities are expected. e CREATE TABLE T3 (field1 VARCHAR2(50), field2 VARCHAR2(300)). Intensive retrieving activities are expected. A database contains two tables TI (A, B, C) and T2 (C, D, E). The column C of Ti is the foreign key to T2. Explain in which of the following situations it is reasonable to use clusters, and create clusters and clustered tables. 1. The data in the tables are often modified. elational Databases Essential Aspects of Physical Design and Implementation of Relational D HERE C = x on the table WHERE C x on the table 1 b. The performance of select queries with conditions ... WHERE C T1 is important. c. The performance of queries with conditions ... WHERE C important. d. The performance of queries with conditions ... WHERE C>xo important. e. The performance of the queries on the join of the tables is important > x on the table This 7. Explain when it is beneficial to store the table T (A, B, C)_as index-organized 8. Define the difference in conditions for your decisions to store the table Employee clustered or partitioned by the attribute emplType. 9. Define the difference in conditions for your decisions to store the table Employee as distributed or partitioned by the attribute emplType. 10. What would be your choice of the storage organization of the table Employee if the following querying patterns were the most important ones: a.... WHERE ID - x; b.... WHERE empl Name - X; C.... WHERE dept Code ; d. ... WHERE titleCode BETWEEN X and y; e.... WHERE titleCode =x; 11. Describe distributed data design for one of the case studies from Appendix 1. 12 Build the physical data model for one of the case studies from Appendix 1