KXO206 Database Management Systems - Assessment Task 1 - Database Maintenance Report
KXO206 Database Management Systems Assessment Task 1 - Database Maintenance Report
NOTE: All assignments are checked for plagiarism (copying) by the
Individual
The Shanghai Council for the Promotion of International Trade, founded in November 1956, serves as the oldest local branch of the China Council for the Promotion of International Trade (CCPIT). With a population exceeding 26 million people, the council plays a pivotal role in facilitating business activities within the city. Currently, the council relies on a centralized Oracle database system to manage its operations efficiently. However, it's noteworthy that approximately 30% of the council's records remain in electronic format and have not been migrated to the Oracle DBMS.
As of the date of this report, approximately 70% of the council's records have been successfully migrated to the
Oracle DBMS. Efforts are ongoing to complete the migration process and ensure that all pertinent data is
integrated into the centralized database system.
The council has five major customers, Local Businesses, Foreign Companies, Citizens, Tourists and Partner
Organisations. Tourists can book value pack trips all around Shanghai, if needed.
Stringent measures have been implemented to safeguard the confidentiality, integrity, and availability of the data stored within the Oracle database system. Access controls are in place to restrict unauthorized access, and regular security audits are conducted to identify and address potential vulnerabilities.
Regular backups of the Oracle database are performed to mitigate the risk of data loss in the event of system failures, natural disasters, or other unforeseen circumstances. Additionally, comprehensive disaster recovery plans have been developed to ensure the swift restoration of services in the event of a catastrophic event. Database Maintenance Report Question:
Task description
The council is running out of time for the next trade exhibition as such, they need a new database developer to complete the migration of the remaining 30% database as originally planned.
• The former developer failed to provide proper documentation of the work that they had completed. Page 1
-
The entire database tables need to be created again oracle SQL.
-
The only document that does exist is the placeholder definitions of the database table, plus there is
some test data contained in the SQL script file: Asst02Setup.sql.
-
The table names in the Asst02Setup.sql are all place holders. Please create your own table
names
Proposed tables and their corresponding attributes
Task Requirement
-
You are required to document all the work and the processes you are undertaking for this project be documented in a formal business report format.
-
The report is to have a one-page Introduction that identifies what has been achieved, and what (if anything) remains to be done. Each specific requirement is to be documented:
-
a) Have a new page;
-
b) Have the requirement number as the major heading;
-
c) with sub-headings for: - the SQL Script;
-the Query Results; and
-detailed Comments which describe and explain what each script does / achieves
-
Each requirement is to be constructed through an SQL script with the following typical structure:
This letter corresponds to the drive letter where your scripts are SPOOL C:\206\R1.txt located, and the output text file will be stored. Substitute this with @@requirement 1
the path to your scripts, for example D:\KXO206\Q1.txt
@@Asst02Setup SET LINESIZE 120
SET PAGESIZE 70 SELECT * FROM TableA;
SELECT * FROM TableB; SELECT * FROM TableC;
SELECT * FROM TableD; SELECT * FROM TableE; SELECT * FROM TableE ; SPOOL OFF
• Substitute this number with the number of the requirement you are running.
For example, for requirement 2 you would use: @@requirement 2
(with a space between requirement and the number)
The script requirement.sql accepts the number as a parameter
(requirement.sql doesn’t need to be modified)
1.
The file Requirement.sql (and also Asst2Setup.sql) can be downloaded from the Assessment page on the unit’s MyLO site. Both files should be stored on the same drive, and in the same directory, as should your answer scripts to each requirement. Requirement.sql must be run at the start of each requirement’s answer and provided with the appropriate requirement number to identify you as the user, plus the date/time and location of your test run, as well as identify which requirement you are attempting to meet. Your output for each requirement should be spooled to a file (here assumed to be on ‘D: drive’, but you can use whatever drive you wish).
The content of these spool files should be listed as part of your report. The execution of the Requirement.sql script results in the following typical introduction to each spool file:
USER SERVER SESSION_ID
--------------- ------------------------------ ----------
SYSTEM XE 230191
TERMINAL USED_ON USED_AT
---------------- -------------------- ---------- MAZINOAMUNO-PC
07-APR-2020 04:41 A.M.
The council is aware that some of the requests might prove difficult, and accordingly asks that if you encounter a problem, that you provide scripts that are partial solutions, on the basis that something is better than nothing. It does ask, however, that your report’s Introduction highlight any such problems.
Further Requirements
The following specific requirements have been identified:
Use the file Asst02Setup.sql (available through MyLO) to set up the database. Then display the structure and content of each database table created.
2. Write an SQL script to determine:
-
a) all unique IDs for each table and their associated attributes in the database ordered in ascending
order;
-
b) a listing of at least TEN (10) tourists ordered by their date of booking and reservation.
-
c) a listing of at least (5) citizens who travel by the Metro.
3. In your own words (200 words) address the following question in the conclusion of your report:
How can we assess the effectiveness of our database maintenance procedures and identify opportunities for improvement using oracle DBMS?
