New Zealand Diploma in IT: Database Development and Design Report

This report addresses the database development and design for UniPharma, focusing on the implementation of data backup plans, access privileges, and data consistency. The report includes a relational schema, an ER diagram, and discusses design changes. It explores the importance of data backup, comparing disk and remote backup methods. Furthermore, it details access privileges for different employee roles, including pharmacy managers, pharmacists, accountants, data entry operators, and database administrators. The report also highlights the significance of database administrator's role in ensuring data integrity and security. Finally, the report identifies categories of database failures and proposes methods for formulating a comprehensive recovery plan. This document provides a thorough examination of database design and management principles, offering valuable insights for students and professionals alike.

Running head: DATABASE DEVELOPMENT AND DESIGN
Database Development and Design
Name of the Student
Name of the University
Author Note

1DATABASE DEVELOPMENT AND DESIGN
Table of Contents
Task 1: Relational Schema..............................................................................................................2
Task 2: ER diagram of Preceding Information................................................................................3
Task 3: Design Change....................................................................................................................4
Task 4: Administer Changes............................................................................................................4
Task 5: Description of Data Backup Plan........................................................................................5
Back-up of Disk:..........................................................................................................................5
Back-up of Remote:.....................................................................................................................6
Task 6: Access Privileges level of five type of Employees.............................................................6
Task 7: Different Access Levels to the Employees.........................................................................7
Task 8: Ensuring Consistency of the Database................................................................................8
Task 9: Categories of Failures and Methods to Formulate a Recovery Plan.................................10
References:....................................................................................................................................11

2DATABASE DEVELOPMENT AND DESIGN
Task 1: Relational Schema
Patient
Specialty Age Years of Experience SSN
Doctor
Name Age Address SSN
Drug
Formula Trade name
Prescription
Quantity Date
Pharmaceutical Company
Name Phone Number
Pharmacy
Phone Number PH.ID Name Address

3DATABASE DEVELOPMENT AND DESIGN
Task 2: ER diagram of Preceding Information

4DATABASE DEVELOPMENT AND DESIGN
Task 3: Design Change
Task 4: Administer Changes
PatientPrescription
Doctor
Drug
Takes
Has
Prescribe
Date and Time
Quantity

5DATABASE DEVELOPMENT AND DESIGN
Task 5: Description of Data Backup Plan
The UniPharma needs to back-up their data on weekly basis and for that they need to
implement an appropriate type of data back-up plan. In this context it has been determined that
there are two types of data back-up plan which can be utilized by UniPharma. The first one is the
back-up disks and the second one is the back-up of remote. In the following section both of the
back-up techniques are discussed briefly.
Back-up of Disk:
This technique of back-up technique is one of the oldest technique of back-up procedure.
In this case a local storage device is used for the backup purpose. Here, electronic storage media
is used for the back-up purpose. This type of back-up technique is also known as the magnetic
back-up procedure (Li et al., 2015). The main reason behind implementing this type of back-up
procedure is that making this type database backup is very much easy and quick and with that
high amount of data can be stored using this type of backup technique. In this type of backup
procedure also the utilized energy is very much less while consistency of this type of data back-
up technique is quite higher (Langer, 2017). Due to all of these facts this method of the data
back-up procedure is one of the best data back-up procedure. Thought this type of data back-up
is one of the best process for the data back-up it minimizing gradually with the time as the
technology is upgrading day by day.
The main advantages of this type of data back-up procedure is that high capacity of back-
up can be achieved in this case. The traditional type of storages offers considerably high amount
of storages with a low price which is one of the main advantage (Kaczmarczyk & Dubnicki,

6DATABASE DEVELOPMENT AND DESIGN
2015). In one tape of this type of storage 6.25 TB of data can be stored. Sizes of these storages is
quite less compared with the storage they offers. Portability is one of the biggest advantage
which this type of storage technique offers. Portable storages are very much helpful for in the
cases of natural disasters. All the back-up storages can be moved easily from one location to
another location in the case of any natural disaster. The cost of using this type of backup
procedures is also very much less. Due to this fact back-up of disk is used for the database back-
up purpose.
Back-up of Remote:
The back-up of remote is actually offsite type of back-up. In this type of back-up process
the back-up is done remotely from somewhere else location (Chang, Tsai & Guo, 2016). This
type of back-up can be accessed from anywhere for execution of the back-up restoring process.
This type database back-up process is very much costly compared to other back-up process.
Also, while restoring this type of back-up the time for the restoration process take a huge amount
of time. But, actually using this type of back-up procedure is quite sage in nature.
The main advantages of this type of back-up procedure is that the security of this type of
back-up is higher compared with the local back-up process. Thus in the case of any disasters
having this type back-up process is quite advantageous. Also, management of this type back-up
process is quite easy while compared with any other database back-up procedures.
Task 6: Access Privileges level of five type of Employees
The five type of employees which are related with the system of UniPharma are the
pharmacy manager, pharmacists, accountant, data entry operator and database administrator. In
the aspects of database access privilege is very much important concept. This access privilege to
a user is the level of access which are possessed by users of database (Ahuja, Mohanty &
Sakurai, 2017). This access privileges decides which user of the database will be able to use the
data or read. Reading and using of the existing data in the database is low level access to the
database. In the higher case of access the employees will be able to write and modify the values
present in the database. Here the five employees of the UniPharma performs different of
operations on the database and for that they are having different types access privileges.

7DATABASE DEVELOPMENT AND DESIGN
 Pharmacy Manager: In this case the pharmacy manager will be having the lowest level
of access privilege to the database of UniPharma. Managers will only have the read
access to the database. Data Entry Operator: The data entry operator needs to enter the data to the database
manually. Due to this reason they must be having a writing access to the database. So, the
access privilege of the data entry operator is higher than the pharmacy manager. Accountant: The accountant also needs to update the accounts information to the
database so that everyone can see the important information. Thus the accountant also
must be having writing and modification privileges to the database. Pharmacists: The pharmacists may need to check important information but they do not
requires any type of changes to the database. Due to this only reading privileges is
enough for the pharmacists. Database Administrator: The database administrator is having the highest access
privileges to the database. The database administrator itself can write and read
information to the database, but the database administrator is also having power of
modifying the access privilege. The database administrator is capable of modifying the
current access privileges to the other users of the database. The database administrator is
also having the privileges of performing all types of configuration that the database
currently supports. The database administrator is also responsible for performing the
maintenance of the database. The database administrator is also capable of dropping the
actual database to the SQL server (Mukherjee, 2019).
Task 7: Different Access Levels to the Employees
The database administrator or the DBA have the privileges of providing different types of
access levels to the employees of the organization as the DBA are having higher level of access
privileges to the database (Hogan, 2018). In this aspect as a database administrator can provide
three types of important access to the users of the database. All of these access privileges is very
much important in this case. In the following section these different access levels are discussed. Security Admin: The employees at the security admin level have considerably higher
type of access level to the database. In this aspect the employees at the security admin
level can modify the existing membership role. The employees who are having these

8DATABASE DEVELOPMENT AND DESIGN
access privilege can also elevate their privileges but the actions taken by them should be
monitored. Writer: The employees who are currently having the writing access privileges is having a
fixed type of database operating role. In this context the writers of the database system is
having the accessibility of adding, deleting and modifying all the data in the present in
the database. Reader: The employees who are having the reading access privileges is having the least
access privileges to the database. In this context the employees who are only having the
reading access privilege can read all types of data that is currently present in the database
of the system.
Task 8: Ensuring Consistency of the Database
The database administrator is an administrative person that is used for the management
and also for the assurance of the organization of different kinds of the data units. The quality of
data includes for the disaster and also the security and control of the personnel, physical access
controls and also the maintenance of the data. The need of DBA includes:
 Selection of the hardware and also the software
 Keeps the trends of the latest needs to the technology.
 Predicts the changes in the near future
 Emphases on the establishment of the products of the company
 Management of the privacy and also the data security
 Needs firewall for doing this.
 Establishes of the privileges of the customer
 Uses of the complex distributed systems such as the accessing of the internet and
also the technology related to the client (Derbyshire et al., 2015).
The major threats related to the database security involve:
 Loss of the data due to the error of the human error or also the
software or the hardware error.

9DATABASE DEVELOPMENT AND DESIGN
 Loss of the integrity of the data
 Loss of the availability of the data by using the virus, sabotage,
and also the worm.
 Management of the integrity related to the data
 Controlling of the integrity of the data from the use of the unauthorized
access.
 Consistency related to the data.
 Maintains the relationship of the data.
 Sets authorized values of the values related to the database
 It enforces the conditions related to the database.
 Data backup
 He or she assumes that the database will be failed.
 Establishes the process
 Gives the facility of the periodic backup on the basis of the weeekkly or
monthly.
 Provides the facility of the cold backup.
 Provides the facility of the hot backup.
 Stored back-up is very much secure and also in the off-site location.
 Recovery of the database systems
 Provides facility of the re installation of the database after the crash.
 There are other facilities related to recovery includes checkpoint, backup
journal point and many others.

10DATABASE DEVELOPMENT AND DESIGN
Task 9: Categories of Failures and Methods to Formulate a Recovery Plan
There are three kinds of the database failures includes System failure, media failure, and
also errors of the application software. The system crash means when the system crashes, it is
going towards the re installation of the affected processes or worse the data recovery
(Nowakowski, Ciszewski & Łukasik, 2019). It usually refers for any kind of the errors or the
malfunction of the hardware in the operating system or the software of the database. Media
failure are caused by the crash of the head or the unreadable media. These types of the failures
are considered of the one of the most serious because it is possible for the loss of the total data.
In the application software errors, the limit of the resources is exceeded, logical errors and also
the bad input or any other factors that is related to the application of the software that can be
compromised and also the transaction can fail giving the way of the failure of the database.
The database recovery can be recovered by the crash recovery, immediate update
recovery. DBMS may be complex system with many transactions that can be executed in each
second. The sturdiness and also hardiness of the software that can rely upon the complex systems
that can be designed in such a manner. The DBA also include the facility for the recovery of the
updating in the immediate manner. The transaction T reaches to the commit point when all the
operations related to the access of the database has reached successfully. Once the transaction is
committed it is stored in the database in permanently in to the database. The commitment
involves the writing on the commit entry for the logging on the disk. At the time when the
system crashes thee item is searched back to the log that contains the transaction T that have the
written the starting of the transaction entry in to the log but have no commit yet. These
transaction may have to be rolled back to the undo into the database at time of the process of the
recovery.

11DATABASE DEVELOPMENT AND DESIGN
References:
Ahuja, R., Mohanty, S. K., & Sakurai, K. (2017). A scalable attribute-set-based access control
with both sharing and full-fledged delegation of access privileges in cloud
computing. Computers & Electrical Engineering, 57, 241-256.
Chang, B. R., Tsai, H. F., & Guo, C. L. (2016). Applying Intelligent Adaptation to Remote
Cloud Datacenter Backup. Journal of Advanced Computational Intelligence and
Intelligent Informatics, 20(6), 928-940.
Derbyshire, M. K., Gonzales, N. R., Lu, S., He, J., Marchler, G. H., Wang, Z., & Marchler-
Bauer, A. (2015). Improving the consistency of domain annotation within the Conserved
Domain Database. Database, 2015.
Hogan, R. (2018). A practical guide to database design. Chapman and Hall/CRC.
Kaczmarczyk, M., & Dubnicki, C. (2015, May). Reducing fragmentation impact with forward
knowledge in backup systems with deduplication. In Proceedings of the 8th ACM
International Systems and Storage Conference (p. 17). ACM.
Langer, M. (2017). Developing a Data Backup Strategy. Risk Management, 64(10), 12-14.
Li, Y. K., Xu, M., Ng, C. H., & Lee, P. P. (2015). Efficient hybrid inline and out-of-line
deduplication for backup storage. ACM Transactions on Storage (TOS), 11(1), 2.
Mukherjee, S. (2019). Popular SQL Server Database Encryption Choices. arXiv preprint
arXiv:1901.03179.
Nowakowski, W., Ciszewski, T., & Łukasik, Z. (2019, February). The Software Framework for
Simulating Railway Automation Systems Failures. In International Conference on
Transport Systems Telematics (pp. 44-56). Springer, Cham.