ITDR 3104 American Academy of English Information Technology Department Discussion

program: Oracle online All requirements in the attachment

Similarity should be less than5%.

Plagrisim report is needed

For any clarification please ask

Ministry of Higher Education
Colleges of Applied Sciences
Information Technology Department
ITDR3104 – Project [15%]
Primary objective:
The primary objective of this project is to understand the principles of Advanced Database
Design Concepts. The project starts with the analysis of the physical usage of a moderately
realistic database system, and from this analysis to identify potential performance problem
areas in the database. Then the task to choose, implement and test an appropriate combination
of physical tuning techniques for the database you create. There are three main elements to be
considered: choosing, implementing and testing. Each is important.
Topic:
You can choose your own topic for this project. But the topic should be rich in details so that
you will get some reasonable size of database and data to test for your tuning methods.
Tasks:
•
Imagine the Database system you are creating is for a medium sized company and they are
converting from computer file system to a database system.
•
If you are appointed as a data administrator of the company then list out the tasks you do
for the company and the system. (Like what kind of DBMS DBA choose, policies you
make, planning and etc..). Justify your choices.
•
Determine what is needed to manage your database system. Provide a comprehensive list
of all of the records that would need to be tracked, and which systems would be linked and
which would be independent. Determine the database staffing needs required to manage
such a system, and provide a plan for how the database responsibilities would be partitioned
among the database staff.
•
After the basic details for the company system, you need to move to the specific database
creation. You need to develop the following three phases of the project:
o Conceptual Design
o Logical Design
o Physical Design
•
In the conceptual Design, First draw the ERD of your database. Identify potential problems
in the representation of fields in the database (such as different ways that dates, names, or
addresses might be written), and propose a remedy for each. For example, the date “23May-1966” can be written “May 23, 1966” or “05-23-1966”, and might be better
represented as “19662305”. Find the problems related to relationship between the tables
and suggest the solution for each.
•
In logical design, you will convert the ERD to proper database using coding. Discuss the
issues related to it and suggest the solutions. Also find the possibilities for the integrity
constraints for the data fields and include it and explain the need of it.
•
The Physical design is the most important phase of your project.
o Find ten most important transactions that occur frequently in system. Include
details of the purpose of the transaction, which tables are accessed, and how the
tables are accessed. State clearly which transactions are accessing more than one
table and overlapping in terms of the tables accessed. (Try to cover a mixture of
different transaction types, such as simple vs. complex, read-only vs. read/write vs.
write-only). Your transactions may overlap in terms of the tables accessed.
o Based on the transactions chosen above, find the problem areas of your system.
Discuss in detail about the possibilities and issues of these problem areas and
suggest appropriate combination of physical tuning techniques for the problem
areas that you are solving. The techniques that you choose must not conflict. You
should explain why you chose the techniques that you did, and also discuss any
specific details of how each technique will be implemented, such as partition
bounds, keys, etc.
o Write the code required to implement your chosen techniques, and insert it into your
schema. Include your modified schema also in your report. Remember to always
explicitly specify a tablespace for all schema objects, including indexes.
o Test your implementation and demonstrate that the techniques that you have
implemented do in fact improve database performance. This will typically be
achieved by executing appropriate queries and including tidily formatted output
from the likes of EXPLAIN PLAN or similar. This should be done both before and
after the tuning techniques have been applied. Test each technique in isolation, and
then test all of your techniques in combination, to ensure that your entire integrated
solution still improves database performance.
Grading:
Grading will be done based on the following criteria:
•
•
•
Complete detailed report which provides solutions to the above mentioned tasks. Parts
of the project include introduction, description, conceptual design, logical design,
physical design and etc…
Presentation of your project
Personal questioning of every member of the project.
Write the code required to implement your chosen
techniques, and insert it into your schema. Include
your modified schema also in your report. Remember
to always explicitly specify a tablespace for all schema
objects, including indexes.
o Test your implementation and demonstrate that the
techniques that you have implemented do in fact
improve database performance. This will typically be
achieved by executing appropriate queries and
including tidily formatted output from the likes of
EXPLAIN PLAN or similar. This should be done both
before and after the tuning techniques have been
applied. Test each technique in isolation, and then test
all of your techniques in combination, to ensure that
your entire integrated solution still improves database
performance.