Fundamentals of Database Systems Final Exam Review Questions Dr. Fahmi Abboushi True/False Indicate whether the statement is true or false. ___T__1. A database language must enable the user to create database and table structures to perform basic data management chores. ___T2. A database language must enable the user to perform complex queries designed to transform the raw data into useful information. ___F3. SQL is considered hard to learn; its command set has a vocabulary of more than 300 words. ___F_4. All SQL commands must be issued on a single line.
To restore the values of a table, provided you have not used the COMMIT command, you must use the ROLLBACK command. ___T_6. SQL allows the use of logical restrictions on its inquiries such as OR, AND, and NOT. ___F_7. You cannot insert a row containing a null attribute value using SQL. ___F8. SQL requires the use of the ADD command to enter data into a table. ___T_9. The conditional LIKE must be used in conjunction with wildcard characters.
__ T__10. Because COUNT is designed to tally the number of non-null “values” of an attribute, it is used in conjunction with the DISTINCT clause. _T__11. Distributed processing shares the database’s logical processing among two or more physically independent sites that are connected through a network. __F_12. One of the advantages of DDBMS is that the data is located near the “least demand” site. __T_13. One of the advantages of DDBMS is growth facilitation. __T__14. One of the advantages of DDBMS is improved communication. __F15. One of the disadvantages of DDBMS is greater danger of a single-point failure. __F_16. One of the advantages of DDBMS is processor dependence. __T_17.
Current DDBMSs are subject to some problems, such as the complexity of management and control. __F_18. Distributed processing does not require a distributed database, and a distributed database does not require distributed processing. __T_19. In order to manage distributed data, copies or parts of the database processing functions must be distributed to all data storage sites. __T__20. A fully distributed DBMS must perform all the functions of a centralized DBMS, and it must handle all necessary functions imposed by the distribution of data and processing. __F__21.
Distributed database systems do not require complex mechanisms to manage transactions and ensure the database’s consistency and integrity. __T_22. A remote request allows the user to access data to be processed by a single remote database processor. The SQL statement can reference data at one remote site. __T_23. A remote transaction, composed of several requests, may access data at only one single site. __F__24. Greater ease in managing the data environment is one of the advantages of DDBMS. __T_25. The TP is the software component found in each computer that requests data. Multiple Choice
Identify the choice that best completes the statement or answers the question. ___A_1. The SQL command that lets you insert data into a table, one row at a time, is ____. |a. |INSERT | |b. |SELECT | |c. |COMMIT | |d. |UPDATE | __C_2. The SQL command that lets you save your work to disk is ____. |a. |INSERT | |b. |SELECT | |c. |COMMIT | |d. |UPDATE | ___B_3. The SQL command that lets you list the table contents is ____. a. |INSERT | |b. |SELECT | |c. |COMMIT | |d. |UPDATE | ___D_4. The SQL command that enables you to make changes in the data is ____. |a. |INSERT | |b. SELECT | |c. |COMMIT | |d. |UPDATE | ___B_5. To list all the contents of the PRODUCT table, you would use ____. |a. |LIST * FROM PRODUCT; | |b. |SELECT * FROM PRODUCT; | |c. DISPLAY * FROM PRODUCT; | |d. |SELECT ALL FROM PRODUCT; | ___D_6. Which command would you use when making corrections to the PRODUCT table? |a. |CHANGE PRODUCT | | |SET P_INDATE = ’01/18/2004′ | | |WHERE P_CODE = ’13-Q2/P2′; | |b. ROLLBACK PRODUCT | | |SET P_INDATE = ’01/18/2004′ | | |WHERE P_CODE = ’13-Q2/P2′; | |c. |EDIT PRODUCT | | |SET P_INDATE = ’01/18/2004′ | | |WHERE P_CODE = ’13-Q2/P2′; | |d. UPDATE PRODUCT | | |SET P_INDATE = ’01/18/2004′ | | |WHERE P_CODE = ’13-Q2/P2′; | ___D_7. Which command is used to restore the table’s contents to their default values? |a. |COMMIT; RESTORE; | |b. |COMMIT; BACKUP; | |c. COMMIT; ROLLBACK; | |d. |ROLLBACK; | ___A_8. Which command would be used to delete the table row where the P_Code = ‘2238/QPD’? |a. |DELETE FROM PRODUCT | | |WHERE P_CODE = ‘2238/QPD’; | |b. REMOVE FROM PRODUCT | | |WHERE P_CODE = ‘2238/QPD’; | |c. |ERASE FROM PRODUCT | | |WHERE P_CODE = ‘2238/QPD’; | |d. |ROLLBACK FROM PRODUCT | | |WHERE P_CODE = ‘2238/QPD’; | __D_9. What is the SQL syntax requirement to list the table contents for either V_CODE = 21344 or V_CODE = 24288? |a. |SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE | | |FROM PRODUCT | | |WHERE V_CODE = 21344 | | |OR V_CODE 24288 | |c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE | | |FROM PRODUCT | | |WHERE V_CODE = 21344 | | |OR V_CODE > 24288 | |d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE | | |FROM PRODUCT | | |WHERE V_CODE = 21344 | | |OR V_CODE = 24288 | __A__10. SQL allows the use of a special operator in conjunction with the WHERE clause. The special operator used to define a range limit is ____. a. |BETWEEN | |b. |NULL | |c. |LIKE | |d. |IN | __A_11. The basic SQL aggregate function that gives the number of rows containing not null values for the given column is ____. |a. COUNT | |b. |MIN | |c. |MAX | |d. |SUM | __D__12. The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is ____. |a. |COUNT | |b. |MIN | |c. MAX | |d. |SUM | ____13. The basic SQL aggregate function that gives the arithmetic mean for the specific column is ____. |a. |COUNT | |b. |AVG | |c. |MAX | |d. |SUM | __B_14. To delete a row from the PRODUCT table, use the ____ command. |a. |KILL | |b. DELETE | |c. |COMMIT | |d. |ERASE | __B_15. Before the COMMIT command is used, you can retrieve deleted records by using the ____ command. |a. |UNDELETE | |b. |ROLLBACK | |c. |UNSAVE | |d. BACK | __A__16. A(n) ____ is an alternate name given to a column or table in any SQL statement. |a. |alias | |b. |data type | |c. |stored function | |d. |trigger | _B_17. SQL allows the use of special operators in conjunction with the WHERE clause. A special operator used to check whether an attribute value is null is ____. |a. |BETWEEN | |b. |IS NULL | |c. |LIKE | |d. |IN | _C_18. SQL allows the use of special operators in conjunction with the WHERE clause. A special operator used to check for similar character strings is ____. |a. |BETWEEN | |b. |IS NULL | |c. |LIKE | |d. |IN | __A__19.
The ____ command is used to modify the table by deleting a column. |a. |DROP | |b. |ALTER | |c. |DELETE | |d. |ERASE | __B_20. To make data entries in an existing row’s columns, SQL employs the ____ command. |a. ENTER | |b. |UPDATE | |c. |MODIFY | |d. |ADD | __C__21. ____ is used to tally the number of specific “values” of an attribute. |a. |SUM | |b. TOTAL | |c. |COUNT | |d. |ADD | __A_22. The ____ function is used to find the highest value in a table column. |a. |MAX | |b. |TOTAL | |c. |SUM | |d. |TOP | __D__23. What feature is a DDBMS disadvantage? |a. Data is located near the “greatest demand” site | |b. |Growth facilitation | |c. |Reduced operating cost | |d. |Lack of standards | __C__24. What feature is a DDBMS disadvantage? |a. |Processor independence | |b. User-friendly interface | |c. |Security | |d. |Faster data access | __A__25. What feature is a DDBMS advantage? |a. |Reduced operating cost | |b. |Complexity of management and control | |c. Increased storage requirements | |d. |Increased training cost | __B__26. What feature is a DDBMS advantage? |a. |Greater difficulty in managing the data environment | |b. |Less danger of a single-point failure | |c. |Increased training cost | |d. |Complexity of management and control | __B_27.
The ____ processor is the software component found in each computer that represents data. It receives and processes the application’s data requests. |a. |database | |b. |transaction | |c. |data | |d. |network | _C_28. The ____ processor is the software component residing on each computer that stores and retrieves data located at the site. |a. |transaction | |b. |network | |c. |data | |d. |management | _ A__29.
Distributed database system protocols determine how the distributed database system will ____. |a. |interface with the network to transport data and commands between processors | |b. |synchronize all data received from data processors and route retrieved data to the appropriate transaction processors | |c. |ensure common database functions in a distributed system | |d. |handle the storage of data that is accessed by a particular location | __B__30.
Under the ____ scenario, multiple processes run on different computers sharing a single data repository. |a. |single-site processing, single-site data | |b. |multiple-site processing, single-site data | |c. |single-site processing, multiple-site data | |d. |multiple-site processing, multiple-site data | _D__31. The ____ scenario describes a fully distributed DBMS with support for multiple data processors and transaction processors at multiple sites. |a. |multiple-site processing, single-site data | |b. |single-site processing, multiple-site data | |c. |single-site processing, single-site data | |d. multiple-site processing, multiple-site data | __A__32. ____ distributed database systems integrate only one type of centralized DBMS over a network. |a. |Homogeneous | |b. |Heterogeneous | |c. |Fully heterogeneous | |d. |Fully homogeneous | ___B_33. ___ distributed database systems integrate different types of centralized DBMSs over a network. |a. |Homogeneous | |b. |Heterogeneous | |c. |Fully homogeneous | |d. |Fully heterogeneous | __A__34. A ____ distributed database system will support different DBMSs that may even support different models running under different computer systems. a. |fully heterogeneous | |b. |fully homogeneous | |c. |homogeneous | |d. |heterogeneous | ___A_35. ____ transparency allows a distributed database to be treated as a single logical database. |a. Distribution | |b. |Transaction | |c. |Failure | |d. |Performance | ___A_36. ____ transparency allows a transaction to update data at several network sites. |a. |Transaction | |b. Distribution | |c. |Failure | |d. |Performance | __D__37. ____ transparency allows the integration of several different local DBMSs under a common, or global, schema. |a. |Transaction | |b. |Performance | |c. Distribution | |d. |Heterogeneity | __B_38. The ____ contains the description of the entire database as seen by the database administrator. |a. |distributed global dictionary | |b. |distributed data dictionary | |c. |distributed global schema | |d. distributed data schema | __D__39. A distributed ____ allows a transaction to reference several different database processing sites. |a. |request | |b. |site | |c. |data location | |d. |transaction | __C__40. A distributed ____ lets us reference data from several remote database processing sites. a. |site | |b. |request | |c. |transaction | |d. |data location | __D__41. ____ query optimization takes place at execution time. |a. |Automatic | |b. Static | |c. |Manual | |d. |Dynamic | __B_42. ____ costs are generally higher in a distributed model than they would be in a centralized model, sometimes even to the extent of offsetting operational and hardware savings. |a. |Software | |b. Training | |c. |Network | |d. |Database | __B__43. ____ fragmentation refers to the division of a relation into subsets of tuples. |a. |Vertical | |b. |Horizontal | |c. |Data | |d. |Mixed | _C__44. ____ fragmentation refers to the division of a relation into attribute subsets. |a. |Data | |b. |Horizontal | |c. |Vertical | |d. |Mixed | __A45. With ____ data allocation, the entire database is stored at one site. a. |centralized | |b. |decentralized | |c. |partitioned | |d. |replicated | __A_46. Data ____ refers to the storage of data copies at multiple sites served by a computer network. |a. |replication | |b. fragmentation | |c. |persistence | |d. |optimization | __B_47. Which of the following factors influences replication? |a. |Database content | |b. |Database size | |c. |Data style | |d. Number of users | __C_48. Because the trend toward distributed databases is firmly established, many database vendors have used the ____ label to indicate distributed database capability. |a. |unrestricted | |b. |Internet | |c. |client/server | |d. |high-capacity | __C49. What is a disadvantage of a client/server application? a. |There are more people with PC skills than with mainframe skills in the job market. | |b. |The PC is well established in the workplace. | |c. |An increase in the number of users and processing sites often paves the way for security problems. | |d. |There is a considerable cost advantage to offloading applications development from the mainframe to powerful PCs. | __A_50. Which of the following is a problem with a centralized database? |a. |High cost | |b. Good performance from remote locations | |c. |Poor performance from the main location | |d. |Lack of storage | Focus on the following questions: SQL Chapter Question: 1. Define what SQL is. SQL Functions fit into two broad categories, briefly define each one of them. SQL functions fit into two broad categories: Data definition language
SQL includes commands to: • Create database objects, such as tables, indexes, and views • Define access rights to those database objects 2. Determine which of the following is a Data Definition Command (DDC) or a Data Manipulation Command (DMC): |Command |Type | |Create Table |DDC | |Insert |DMC | |Select |DMC | |Index |DDC | |View |DDC | |Update |DMC | |Drop Table |DDC | Commit |DMC | |Drop Table |DDC | |Delete |DMC | 3. Describe what each one of these “Special Operators” does: Between, Null, Like. (slide 30) <> <> 4. Given the following table, write the SQL command for THREE of the following 4 queries: |store_name |Sales |Date | |Los Angeles |$1500 |Jan-05-1999 | |San Diego |$250 |Jan-07-1999 | |Los Angeles |$300 |Jan-08-1999 | |Boston |$700 |Jan-08-1999 | A. The highest Sales figure:
Select max(sales) as maxsales from site_information B. The Average of all Sales: Select avg(sales) as avgsales from site_information C. Change the Sales for Los Angeles on Jan-08-1999 to $500 instead of $300. Update site_information sales=’$500′ where store_name=’Los Angeles’ and date=’Jan-08-1999′; D. We decided NOT to keep any information on Los Angeles in this table. Delete from site_information where store_name=’Los Angeles’; Distributed Database Questions: 1. List 4 of the advantages of Distributed Database management Systems (DDBMS), and 4 of the disadvantages.
Advantages include: • Data Faster data access • Faster data processing • Growth facilitation • Improved communications Disadvantages include: – Security – Lack of standards – Increased storage requirements – Increased training cost 2. List three differences between Single-Site data processing and Multiple-Site data processing. Single-Site Data (SPSD) • All processing is done on single CPU or host computer (mainframe, midrange, or PC) • All data are stored on host computer’s local disk • Processing cannot be done on end user’s side of system
Multiple-Site Processing, (MPSD) • Multiple processes run on different computers sharing single data repository • MPSD scenario requires network file server running conventional applications that are accessed through LAN • Many multiuser accounting applications, running under personal computer network, fit such a description 3. List four of the DDBMS Components. 1. Computer workstations 2. Network hardware and software 3. Communications media 4. Transaction processor 4. List four of the Transparency features of DDBMS . 1. Distribution transparency 2.
Transaction transparency 3. Failure transparency 4. Performance transparency 5. What is Data Fragmentation? List the three strategies of data fragmentation. Data fragmentation • How to partition database into fragments Strategies • Horizontal fragmentation i. Division of a relation into subsets (fragments) of tuples (rows) • Vertical fragmentation i. Division of a relation into attribute (column) subsets • Mixed fragmentation i. Combination of horizontal and vertical strategies 6. What is the difference between a homogenous and a Heterogeneous DDBMS? Homogeneous DDBMSs Integrate only one type of centralized DBMS over a network Heterogeneous DDBMSs • Integrate different types of centralized DBMSs over a network 7. List two of the advantages of Client/Server DBMS and two disadvantages. Client/server advantages • Less expensive than alternate minicomputer or mainframe solutions • Allow end user to use microcomputer’s GUI, thereby improving functionality and simplicity Client/server disadvantages • Creates more complex environment • Different platforms (LANs, operating systems, and so on) are often difficult to manage
Cite this Intro to Database
Intro to Database. (2016, Oct 22). Retrieved from https://graduateway.com/intro-to-database/