INF3703 exams memo.

2015-6 SECTION B Question 2 [22] ABC is a company on the street of Soweto. You have been appointed by ABC Company as a Database Administrator (DBA) after the completion of your IT qualification. As a DBA, one of your managerial roles in the Company is to ensure backup and full recovery database data in case of loss. As a Database Administrator (DBA) do the following: 2.1. State and explain to the Company at least SIX (06) measures you would put in place to ensure backup and recovery of their database data. [18] The backup and recovery measures must include at least the following: 1. Periodic data and application backups – Some DBMSs include tools to ensure automatic backup and recovery of the database. Products such as IBM’s DB2 allow different types of backups: full, incremental, and concurrent. A full backup, also known as a database dump, produces a complete copy of the entire database. An incremental backup produces a backup of all data since the last backup date. A concurrent backup takes place while the user is working on the database. 2. Proper backup identification – Backups must be clearly identified through detailed descriptions and date information, thus enabling the DBA to ensure that the correct backups are used to recover the database. The most common backup medium has traditionally been tape. Other solutions include optical and disk-based backup devices. Such backup solutions include online storage based on network-attached storage (NAS), storage area networks (SAN), and cloud-based data storage. 3. Convenient and safe backup storage – Multiple backups of the same data are required, and each backup copy must be stored in a different location. The storage locations must include sites inside and outside the organization. The storage locations must be properly prepared, and they may include fire-safe and quakeproof vaults as well as humidity and temperature controls. 4. Physical protection of both hardware and software – Protection might include the use of closed installations with restricted access, as well as preparation of the computer sites to provide air conditioning, backup power, and fire protection. Physical protection also includes a backup computer and DBMS to be used in case of emergency. 5. Personal access control to the software of a database installation – Multilevel passwords and privileges as well as hardware and software challenge/response tokens can be used to identify authorized users of resources. 6. Insurance coverage for the data in the database - The DBA or security officer must buy an insurance policy to provide financial protection in the event of a database failure. The insurance might be expensive, but it is less expensive than the disaster created by massive data loss. p737 2.2. Make the company top management understand why you need to implement the measures above by explaining to them the importance of database backup and recovery for their company. [4] When data is not readily available, companies face potentially ruinous losses. Therefore, data backup and recovery procedures are critical in all database installations. The DBA must also ensure that data can be fully recovered in case of data loss or loss of database integrity. These losses can be partial or total; therefore, backup and recovery procedures are the cheapest database insurance you can buy. p736 Question 3 [26] 3.1. Draw (showing the flow) the three stages of database design. [3] Conceptual design → logical design → physical design 3.2. Explain in detail each of the three database design stages illustrated in question 3.1 and list the steps (actions) that are performed in each stage. [10] p452 3.3. Creating an ER diagram is an important part of database design. Given the business rules below for a car dealership company (NISSAN REPAIR SA), create an ER diagram for the company. Show the entity attributes including primary keys and ensure you label the relationship lines. A – A sales person can sell many cars, but each car is sold by only one salesperson. B – A customer can buy many cars, but each car is sold to only one customer. C – A sales person writes a single invoice for each car sold. D – A customer gets an invoice for each car(s) he or she buys. E – A customer might come in only to have a car serviced; that is, one needs not buy a car to be classified a customer. F – When a customer takes in one or more cars for repairs or service, one service ticket is written for each car. G – The car dealership maintains a service history for each car serviced. The service records are referenced by the car’s serial number. H – A car brought in for service can be worked on by many mechanics, and each mechanic can work on many cars. I – A car that is serviced may or may not need parts (e.g. parts are not necessary to adjust a carburettor or to clean a fuel injector nozzle). [13] – Question 4 [18] 4.1. Explain what is deadlock. Elaborate your answer with the help of an example. [4] A deadlock occurs when two transactions wait indefinitely for each other to unlock data. For example, a deadlock occurs when two transactions, T1 and T2, exist in the following mode: T1 = access data items X and Y T2 = access data items Y and X If T1 has not unlocked data item Y, T2 cannot begin; if T2 has not unlocked data item X, T1 cannot continue. Consequently, T1 and T2 each wait for the other to unlock the required data item. p500 4.2. State and explain the three basic techniques that can be used to control deadlock in an organisation. [6] The three basic techniques to control deadlocks are: • Deadlock prevention – A transaction requesting a new lock is aborted when there is the possibility that a deadlock can occur. If the transaction is aborted, all changes made by this transaction are rolled back and all locks obtained by the transaction are released. The transaction is then rescheduled for execution. Deadlock prevention works because it avoids the conditions that lead to deadlocking. • Deadlock detection – The DBMS periodically tests the database for deadlocks. If a deadlock is found, the “victim” transaction is aborted (rolled back and restarted) and the other transaction continues. • Deadlock avoidance – The transaction must obtain all of the locks it needs before it can be executed. This technique avoids the rolling back of conflicting transactions by requiring that locks be obtained in succession. p502 4.3. What is Java database connectivity (JDBC)? [3] JDBC is an API that allows a Java program to interact with a wide range of data sources, including relational databases, tabular data sources, spreadsheets, and text files. JDBC allows a Java program to establish a connection with a data source, prepare and send the SQL code to the database server, and process the result set. p691 4.4. What is the main advantage of JDBC? [2]