Victoria College Database Management System Case Study Everything requirementsfor this assessment will be in the pdf what I have send the file down the bel

Victoria College Database Management System Case Study Everything requirementsfor this assessment will be in the pdf what I have send the file down the below. Running head: TECHNOLOGY
1
Research report Database Systems
Student ID : 42741 Siva Sankar
Student ID :43832 Akshay thokola
Database Systems
Introduction:
A database management system is a software tool used to store, analyze, and retrieve the
user’s input while maintaining appropriate security measures. It is programmed with a group of
programs that instructs the database. A database management system accepts the user’s request for
data manipulation from an application and requests the operating system to provide the required
data. DBMS allows the user to create their self database as per their requirements. DBMS helps
the application and third party to store and retrieve the data required. The database management
system acts as an interface between the data and the software application. Initially, the institutions
have been always dependent on the database operations and the daily activities will be conducted
with the help of the same. It provides a means for the institution to maintain department data and
use the same to refer at any point of time they need. This particular paper has determined the
security challenges that could occur with databases and the requirements that are necessary if the
TECHNOLOGY
2
institution has to counter-attack and challenges providing a secure base and determining the
security roles by establishing the weaknesses [1]. The information cannot be exploited in any
manner regardless and one of the solutions that has been used in this research to protect the
database is the encryption of sensitive data which is one of the powerful ways to address databaserelated challenges and keep up the security and accuracy of data.
Body of the Report:
The great challenge for humans is to analyze the data especially when the data is large.
That’s why the database management system acts as an extension of human logic. It helps you to
analyze large data, correlate, and report the collected data for your organization. DBMS allows the
user to access the data within the organization. A centralized database system is easily accessible
by all users and provides quick answers to queries that sound like repetitive and analytical [4]. A
simple query or search of data in a spreadsheet might quickly answer the information needed.
Whatever may be the complexity of the query, the DBMS responds instantly. Automation is the
only key to provide accurate and efficient information. Generally, we use labors to do a similar
task, similarly, automation is used to perform repetitive tasks. By this, it reduces the manual power
and makes a better process. The database is a single software tool that includes many forms,
spreadsheets, and tables rather than the single sheet created and managed by the entire
organization. It also helps to transfer the entire to the several systems within minutes.
The companies have been facing a lot of issues due to the lack of security aspects in
maintaining the database and the model to protect the private sector and public sector database
when they are working on their business operations has to be amended. This will also keep the
human resources expertise defended when they are developing a database of everything and the
model does not have to overlook the importance of high degree when it comes to safety and validity
of access control.
The most used databases worldwide is MSSQL and Oracle which means reassurance in
security of the database become the necessity and should be applicable for all the principles that
connects the entire population within the premises and the data inside the Framework must be able
to oversee them with user concentrating on the insurance of databases with interactive
TECHNOLOGY
3
improvements in security perspectives [3]. The security of the database includes the framework
and procedures with which the protection will be implemented and the essential insurance will be
provided and it will cover the misuse of data, malicious attempt to insert the data or take malicious
usage of the credentials of the approved users and so on. The security vulnerabilities present in the
databases will be caused by the errors present in the software and there could be an issue with the
structural system. Also, the configuration of the system rules will be improper including the
configuration of the entire Security System which will make it wrong to external attacks hereby
making it unsafe authorization privileges.
When there are multiple updates in a business transaction, there arises the need to update
multiple databases that are on the different servers. It happens due to the partitioned database
architecture of the microservice. Compared to Monolithic architecture, testing the microservice
applications is more complex. There is also complexity in the deployment of the microservicesbased application.
On June 4, 2015, the office of personnel management of the United States confirmed cyber
intrusion attacking their information technology system including the data that could have
compromised the personal details of 4.2 million former and existing Federal employees. Later in
the same month, they reported a different size incident that has targeted the databases of the
organization, which focuses on the background investigation record. This breach is estimated to
have compromised on 21.5 million individuals and their sensitive data. Regardless of the
criticisms, the agency has responded to the intrusion and tried taking a step to secure the
information system according to the director of OPM. Also, the electronic questionnaires have
been sent out for the investigation processing to make sure it helps in conducting the background
investigation that can be taken offline to improve the security systems.
Such incidents in the Federal space are already happening which is increasing the need for
having databases secure. Particularly there are three different layers of data: the security starting
from the database level, access level to the perimeter level. The security present at the database
level will be occur in the database and data will be live there while the access layer security will
be focusing on the control of whoever is able to access the data and when it comes to perimeter
TECHNOLOGY
4
level who can and cannot have access to the database will be determined and unique security
solution is required for every particular level considering the criticality.
Physical database security is something that no user should be overlooked as it deals with
storage, maintenance and manipulation of the data by locking the rooms. The critical respect of
this particular database security practice is to make sure they are backups and recovery measures
and there is no physical Catastrophe that is disturbing this setup [2]. On the other hand, web
applications and firewalls could also be installed and when it comes to the usage of firewalls in
web applications, it will help the user in preventing the intruders from illegally accessing the IT
network of the organization and they are crucial for the cybersecurity prospectors. On the other
hand, specifically, the web applications will be able to interact with the databases and protect with
the access management software of the application and this measure will be similar to any access
control list formed by the organization while the firewall will provide the same benefits.
Another effective method for database security is database encryption and organizations
will be able to use this encryption when the data is in motion or rest regardless of the flow and
volume of data. This data will further appear like it is an unknown language and sticky is provided
to decrypt the data and find out the meaning present in the same until which the entire data will
look meaningless to anyone who is even able to gain access to it if they do not have the key. The
encryption key has become one of the security revolutions in the industry as the hackers are not
able to crack the potential combination of numbers that could be any.
Likewise, the password information should be managed with the help of access control
which becomes equally critical for the organization, and in certain instances, this practice will
require the updation of the access list and change your password whenever required. Also, this
method could be time-consuming and requires much concentration compared to other security
measures and if the company is able to find out the critical data from the pool of data they are
dealing with, then it is always better to isolate the sensitive database to make sure it is active and
protected.
Any security policy provided must be updated every now and then according to the
requirements and periodically reviewed for the Complaints if you as well [5]. The database
TECHNOLOGY
5
auditing could be unavoidable if the protection of data should be promised. It is always wise to
have a separate team that will be dealing with such auditing and security policies reviewing issues
to make sure it is consistently happening.
Conclusion:
Different approaches to database security have been given and some best practices will be
helping the organizations to keep the database safe and best practices will also help organizations
in reducing the vulnerabilities while improving the protection. Some of the approaches could be
deployed specifically and it will help in the protection of the database against any circumstances
that will hurt the accuracy and integrity of the data. References:
[1]H. B. Hashim, “Challenges and Security Vulnerabilities to Impact on Database Systems,” AlMustansiriyah Journal of Science, vol. 29, no. 2, p. 117, Nov. 2018.
[2]E. Blake, “Network and Database Security: Regulatory Compliance, Network, and Database
Security – A Unified Process and Goal,” Journal of Digital Forensics, Security and Law, 2007.
[3]P. Morrison, “Database Security,” Network Security, vol. 2003, no. 6, pp. 11–12, Jun. 2003.
[4]M. Muntjir, S. Aljahdali, M. Asadullah, and J. Haq, “Security Issues and Their Techniques in
DBMS – A Novel Survey,” International Journal of Computer Applications, vol. 85, no. 13, pp.
39–43, Jan. 2014.
[5]J. Weis and J. Alves-Foss, “Securing Database as a Service: Issues and Compromises,” IEEE
Security & Privacy Magazine, vol. 9, no. 6, pp. 49–55, Nov. 2011.
MITS4003 Assignment 3
MITS4003
Database Systems
Assignment 3
Case Study
March 2020
MITS5507 Assignment 3
Case Study (Group Assignment) – 30% (Due Week 12)
Objectives(s)
This assessment item relates to the unit learning outcomes as in the unit descriptor. This
assessment is designed to improve students’ skills to analyze organization al database
requirements, develop a data model to reflect the organization’s business rules and use data
manipulation language to manage database. This assessment covers the following LOs.
LO1.
Synthesize user requirements/inputs and analyse the matching data processing needs,
demonstrating adaptability to changing circumstances.
LO2.
Develop an enterprise data model that reflects the organization’s fundamental business
rules; refine the conceptual data model, including all entities, relationships, attributes,
and business rules.
LO3.
Derive a physical design from the logical design taking into account application, hardware,
operating system, and data communications networks requirements. further use of data
manipulation language to query, update, and manage a database
LO4.
Identify functional dependencies, referential integrity, data integrity and security
requirements; Further integrate and merge physical design by applying normalization
techniques.
LO5.
Design and build a database system using the knowledge acquired in the unit as well as
through further research on recent trends to demonstrate competence in various
advanced tasks with regard to modelling, designing, and implementing a DBMS including
Data warehousing, Data Management, DB Security.
Note: This is a group assignment and each group can have maximum 4 students. Students need to inform
lecturer about their groups by week 5
Assignment Requirements and Deliverables:
Part A (Designing and Modelling) – 10%
Part A requires you to analyze the data requirements for the business model of Prestige
Automobile Rental (Scenario contained within this document) and design a reasonable
database to meet their data requirements. In this section you need to perform the following
tasks
•
•
•
Develop an Entity Relationship Diagram
Make a Relational Schema (including Primary, Foreign Keys and all attributes).
Provide Supplementary Design requirements (data attribute information)
Copyright © 2015-2018 VIT, All Rights Reserved.
2
MITS5507 Assignment 3
•
Discuss physical design and any assumptions made during design
Part B – 10%
Part B is a SQL implementation of your design developed in Part A. Your code must work on
MySQL database and have the following features
SQL code required:
•
•
•
•
•
CREATE TABLE statements for all tables in your ERD including integrity constraints.
INSERT INTO statements for populating the database
o You must at least enter 5 customer details.
o You must at least put 10 vehicle details
o Create at least three rentals with different customers
o Data may need to be inserted in a particular order to comply with integrity
constraints.
SELECT statement that will return all vehicle that have not been rented
o The details to be included (Vehicle ID, type, registration number,
year/make/model, vin number)
SELECT statement that will produce the total amount paid per customer
o The details to be included (customerID, customer name (first and last
combined), phone number and total amount
CREATE STORE PROCEDURE that will calculate the age of customers
Part C (Research and Discussion) – 10%
Part C requires you to do research on the following topics.
•
•
Discuss what is normalization including the normal form of each entity in your design
and why that is optimal. Also discuss how normalization was achieved for each entity.
Your entities must be in 3NF unless there is a compelling reason provided to keep a
relation in 2NF.
Discuss the database security and control measures required in the above case study.
Also discuss the commands used by SQL server to enforce those security controls.
Elaborate the data integrity problems you encounter during concurrent user accesses
and technique of sharing and recovery.
Submission Guidelines
All submissions are to be submitted through turn-it-in. Drop-boxes linked to turn-it-in will be set
up in the Unit of Study Moodle account. Assignments not submitted through these drop-boxes
will not be considered.
Copyright © 2015-2018 VIT, All Rights Reserved.
3
MITS5507 Assignment 3
Submissions must be made by the due date and time (which will be in the session detailed above)
and determined by your Unit coordinator. Submissions made after the due date and time will be
penalized at the rate of 10% per day (including weekend days).
The turn-it-in similarity score will be used in determining the level if any of plagiarism. Turn-it-in
will check conference websites, Journal articles, the Web and your own class member
submissions for plagiarism. You can see your turn-it-in similarity score when you submit your
assignment to the appropriate drop-box. If this is a concern you will have a chance to change
your assignment and re-submit. However, re-submission is only allowed prior to the submission
due date and time. After the due date and time have elapsed you cannot make re-submissions
and you will have to live with the similarity score as there will be no chance for changing. Thus,
plan early and submit early to take advantage of this feature. You can make multiple submissions,
but please remember we only see the last submission, and the date and time you submitted will
be taken from that submission.
For this assignment you need to submit following three files
•
•
•
Part A- Submit a word file including you assumptions and constraints, ER Diagram,
relational schema and discussion about attributes and physical design.
Part B- Submit all your code in .sql file. Before submitting make sure all the
commands works in SQL.
Part C- Submit a word file answering the question of Part C.
Important: It is very important to put all these files in a one folder and zip the folder. Please
name the zip folder as “Assignment3_GroupX_YY.zip” (where X is your group number and YY
are student IDs).
Copyright © 2015-2018 VIT, All Rights Reserved.
4
MITS5507 Assignment 3
Case Study:
Prestige Automobile Rental (PAR) is a vehicle rental company that rents old vehicle s
to the public. PAR has been using manual methods for keeping track of their
customers and their rentals. However, the company would now like to go online and
allow customers to search the available vehicles and see their rental history.
For the first time, when the customer rents a vehicle from PAR their details (name,
address, phone number, driving license number and credit card number) are
recorded. The date they become a customer is also stored. There are certain types of
vehicles which are classified as heavy vehicles. It is a policy of PAR that these vehicles
are only rented to customers who have no demerit points on their driving license.
The information about the vehicle (type, registration number, year/make/model, vin
number, distance travelled and current condition) are also stored. Each vehicle has a
unique ID. The customer can search for their desired vehicle and can see if it is
available.
All rentals are for 7 days. Rental charges are based on the type of vehicle (Car (C) or
Heavy Duty (HD)). Rental must be paid for on collection. Customers can rent up to 2
vehicles at a time. Each rentalID is for a single vehicle for one customer. (The rentalID
is an auto number). When the rental is taken out the date of checkout is recorded
along with calculated due date (7 days from checkout date). When the rental is
returned, the date is recorded in the returned date.
PAR would like to store ‘demerit’ points for the renter in order to identify bad renters.
These demerit points are accumulated at a rate of one point per day a rental is
overdue. PAR will cancel the membership of the customers who have too many
demerit points.
Copyright © 2015-2018 VIT, All Rights Reserved.
5
MITS5507 Assignment 3
THIS PAGE INTENTIONALLY LEFT BLANK
Copyright © 2015-2018 VIT, All Rights Reserved.
6
MITS4003 Assignment 3
Group Participation Form. (Database Systems Group Assignment)
This form is to be completed by the group and returned to your tutor/lecturer as soon as possible.
We, the undersigned, agree to contribute individually and as a team to complete the Group Assignment for MITS4003 Database Systems in the
time specified. (It should be noted that failure to participate in a group may result in a fail for the assignment component of the subject.)
Group membership:
Surname
First name
Student ID
Date
Signature
1.
______________________
___________________
__________
___/___/___
_______________________
2.
______________________
___________________
__________
___/___/___
_______________________
3.
______________________
___________________
__________
___/___/____
_______________________
4.
______________________
___________________
__________
___/___/____
_______________________
* All members in the team will receive the same mark for an assignment, unless there are extenuating circumstances whereby an individual’s
mark has to be altered by the tutor/lecturer, or if the peer group assessment warrants it.
** Team members should contact their tutor/lecturer immediately if problems arise within the team that may cause completion of an assignment
to be severely delayed, or the quality of the submission to be substantially lowered.
*** No additions or deletions from Teams allowed unless agreed to by your Instructor…
Purchase answer to see full
attachment