Question

subject. Database system

i need the answer for 8 & 9 please.

8. Improve the performance and backup plan for the CUNY database. This database student records enrolled in 24 Colleges. Propose four recommendations to correct or ems identified. You have no budget to purchase additional hardware. You need to make recommendations using hardware already available (15 points) includes 400,000 You will be evaluated based on your improvements in a) performance, b) security, c) backup and d) hardware utilization. You independently analyzed the database and interviewed CUNY employees and stakeholders. These are the results of your investigation: Storage: The current database size is 9 TB saved to 10 disks. Availability: The database is operational 21 hours a day six days a week. . Growth: The DBA originally projected a 2 TB database and expected 10,000 new student records a year. The current database size is 9TB. The number of new students added was actually twice the expected number. As a result, more disks were purchased to accommodate the tremendous increase in the database size. . Security: All CUNY registrars, instructors and bursars can view the enrollment and financial history of all students. This is a problem since only selected staff need to view enrollment and financial history Backup: The daily nightly backups don't always complete. The current backup strategy attempts to copy the full CUNY database nightly to tape, but the time required to complete each backup is longer than the database downtime. . Performance: The database is very slow when searching, updating and adding records. . Hardware: There is one database server with 10 disks located in NYC. CUNY assigned each college its own disk or shares a disk with another College. For instance, City College and Queens College data is saved to one disk. Baruch, Hunter, Brooklyn and Borough of Manhattan Community College data is saved to another disk Your recommendations can include reconfiguring existing hardware or shutting down the database for a few days during the winter break to implement your improvements. You have no budget to purchase additional hardware. A very long answer does not necessarily mean a better answer Clearly state any assumptions, be descriptive and use appropriate terminology Propose four recommendations to correct or minimize each problem. Propose only one recommendation for each problem (don't propose three recommendations to fix one problem). Recommendation 1 Security improvements Recommendation 2 erfomance improvements to correct or mininine each probliem Propose onl Propose four Recommendation 2 Performancs improvements 8 failed and data loss and your se of . Recovery Scenario 2: A database peogrammer,who is an wothoiedccil all student grades to a C and then committed the changes

Answer 1

8)         -> 1) Security improvements

Granting read access to only selected staffs (those who are really required to view the data) for viewing the enrollment and financial history and revoking the read access for all the other staffs

            ->2) Performance improvements

                        Implementing various RAID (Redundant Array of Independent/ Inexpensive Disks) levels in the SQL DB will in turn increase the IOPS (Input Output operations per second) and also increase the fault tolerance level of the DB

            ->3) Backup improvements

                        Instead of going with the daily backup of full CUNY database, we can backup only those records that are created/ modified on that day and also coupling it with RAID level 5 will in turn complete the backup within the pre-defined time of 3 hours (24 hours a day – 21 operational hours)

            ->4) Hardware improvements

                        A work around to the existing hardware would be to implement RAID levels in the DB instead of going with separate disks for each colleges or a group of colleges as it would increase the data security and reliability

9)         ->1) Recovery Scenario 1

                        As we have implemented RAID level, if a drive fails the data is automatically copied to the replacement drive so, even if the backup happens at a later time we will have the data in the replacement drive

           

->2) Recovery Scenario 2

Though the DB programmer had committed the changes, we can always perform a Roll Back to the nearest checkpoint and recover the data.