KXO206 Database Management Systems - Assessment Task 1 - Database Maintenance Report
KXO206: Database Management Systems – 2024 Assignment 2
KXO206 Database Management Systems
Assessment Task 1 - Database Maintenance Report
Deadline for Submission: Assessment Weighting:
Submission:
Assignment Type:
The scenario
Friday 19th April 2024 by 11:59 pm (Shanghai-time) 25% of the total assessment for KXO206
Via MyLO
NOTE: All assignments are checked for plagiarism (copying) by the
Turnitin system. Turnitin checks your assignment against other students’ assignments as well as the Internet. For more information see: www.utas.edu.au/turnitin
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
Table Names (You are allowed to change the name and use appropriate table names for all)
Table Description
Local Businesses
Transaction Records
Inventory Data
Employee Records
Financial Data
Customer Feedback and Reviews
Security and Access Logs
Customer Information
Foreign Companies
Companies’ information
Global Supply Chain Data
Financial Data
Cross-Cultural Communication Data
Intellectual Property and Legal Data
Citizens
Residential Data. Healthcare Data
Education Data
Employment Data
Transportation Data
Social Media and Online Activity Data
Financial Data
Government Interaction Data
Personal Information
KXO206: Database Management Systems – 2024 Assignment 2
Tourists
Personal Information
Travel Itineraries
Booking and Reservation Data
Transportation Usage
Language Preferences
Cultural and Heritage Sites Visited
Shopping and Spending Habits
Tourism Feedback and Reviews
Emergency Contact Information
Partner organisations
Contact Information
Collaboration Agreements
Project and Program Data
Financial Data
Shared Resources
Intellectual Property
Performance Metrics
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
-
Page 3
3.
•
• •
• • • • • • • •
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.
KXO206: Database Management Systems – 2024 Assignment 2
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?
Page 5
Assessment Criteria and Guidelines
The assessment of each requirement in is based on the following criteria:
Criteria
High Distinction
Distinction
Credit
Level of
Understanding
, Creativity &
Attention to
Detail Weighting:
60% of marks for
each requirement
Demonstrated a clear understanding of RDBMSs and the use of SQL, a high level of creativity & an attention to detail in seeking out and developing optimal solutions to the stated problems / requirements.
Demonstrated a good understanding of
RDBMSs and the use of SQL, & an ability to select and develop appropriate mechanisms for any given problem situation and stated requirements.
Demonstrated a reasonable understanding of RDBMSs and the use of SQL, & an ability to select and develop appropriate mechanisms for the majority of the aspects of a given problem situation and stated requirements.
Script
Description &
Explanation
Weighting: 30% of
marks for each
requirement
Professional Report Format (10%)
Some basic meaningful descriptions & explanations of the major significant & relevant aspects of each script.
Partially structured the content into loosely-
Complete, meaningful, & accurate description & explanation of all significant & relevant aspects of each script.
Reasonably meaningful, complete, & accurate description & explanation of all the significant & relevant aspects of each script.
Reasonably meaningful, complete & mostly accurate description & explanation of the major significant & relevant aspects of each script.
KXO206: Database Management Systems – 2022 Assignment 2
|
Pass |
Fail |
|
Demonstrated a basic understanding of RDBMSs and the use of SQL, & an ability to select and develop basic functional mechanisms for the major aspects of the given problem situation and stated requirements. |
Failed to demonstrate an adequate understanding of RDBMSs and the use of SQL, and failed to demonstrate a basic ability to select and develop functional mechanisms for the majority of the aspects of the given problem situation and stated requirements. |
Incomplete,
meaningless
descriptions
explanations
significant
aspects of each script.
