Please see attached document for more instructions.
a.
Objectives. Specify 3 high priority business objectives and how each business objective will be supported by components in the DBMS and the database. Keep the business objectives general. Use concepts in the
DAMA DMBOK
as your starting point.
https://www.dama-dk.org/onewebmedia/DAMA%20DMBOK2_PDF.pdf
A business objective involves infrastructure growth, revenue increases, and cost reductions. How do
specific
tables, queries, and database objects support these objectives?
Don’t be vague.
Name them or specify their use
.
i.
BULLET form is sufficient
1.
Objective one
2.
Objective two
3.
Objective three
b.
Guidelines. Include 5 original guidelines for designing, implementing, supporting, and/or improving the database design.
Use concepts from database environments and the design process. A guideline
is not an activity. A guideline
is doing an activity well.
“requirement’s analysis”
- is not a guideline.
“Testing the validity of each requirement during each stage of the design process
to ensure the correctness of the requirement.
”
- is a guideline
“
use business naming
”
- is not a guideline.
“Names of tables and queries must use business vocabulary and be structured the way the business operates to increase acceptance and reduce training efforts.”
- is a guideline
1.
Guideline one
2.
Guideline two
3.
Guideline three
4.
Guideline four
5.
Guideline five
c.
Assumptions. Includes how to calculate the expected storage space needed for the database. Remember an estimate is a ballpark size. Estimates are not exact. Remember to include the size of indexes needed for high performance. Use the business information provided, determine the size of the new database. List what you will need to do to ensure the new database can accommodate the data described below.
Do not use page sizes in your calculations.
Use the row size and number of rows.
·
80% of the data for the database is going to be 80% of the data for the database is going to be broken up into three main tables: products, customers, and invoices.
·
There will be 1,000 products in the products table with each row being 5,000 characters. Each character is
2 bytes
.
·
There will be 1 million customers in the customer table with each row being 10,000 characters. Each character is
2 bytes
. The company plans to expand their customer base by 10% each year.
·
Invoices will likely include 5 products. The invoice table includes a primary key, invoice key, product key, and customer key. There are four columns/keys. Assume each key is
20 bytes
in length.
·
A customer will be invoiced 10 times per year.
·
Reporting requires there to be an index on invoices for sorting by products. Indexes are
10 to 20 bytes
in size for each column value indexed.
·
Assume the database will be used for 10 years when determining your estimate.
·
System requires 5000 concurrent users
|
Specify the size requirements in GB required for the tablespace all three tables (plus 20%). Size of a table is estimated as (Size of Row) x (Number of Rows). Record the information using the table on the next page. You will need to add multiple rows.
Please Note:
GiB is 1024 MiB
MiB is 1024 KB
KiB is 1024 Bytes
Table
|
Group
|
Row Size/Width
(Bytes)
|
Number of Rows
|
Total
(GiB)
|
PRODUCT
|
Total
|
---
|
---
|
|
CUSTOMER
|
Year 1
|
|
|
|
Year 2
|
|
|
|
Year 3
|
|
|
|
Year 4
|
|
|
|
Year 5
|
|
|
|
Year 6
|
|
|
|
Year 7
|
|
|
|
Year 8
|
|
|
|
Year 9
|
|
|
|
Year 10
|
|
|
|
Total
|
---
|
---
|
|
INVOICE
|
Year 1
|
|
|
|
Year 2
|
|
|
|
Year 3
|
|
|
|
Year 4
|
|
|
|
Year 5
|
|
|
|
Year 6
|
|
|
|
Year 7
|
|
|
|
Year 8
|
|
|
|
Year 9
|
|
|
|
Year 10
|
|
|
|
Total
|
|
|
|
20% from other tables
|
Total
|
---
|
---
|
|
Other Objects
|
Total
|
---
|
---
|
|
Total Size
|
?
|
a.
DBA Confirmation.
In this section provide a screen shot for each of the following --
i.
Installed. Window of Oracle Instance Manager with database running.
ii.
Available. Successful log into SQLPLUS at the DOS prompt.
iii.
Running.
Successful execution of an INSERT statement and a SELECT of the data that was inserted..
b.
Estimation.
In this section provide you are to fill out the an estimation table.
You will examine the default settings for the default Oracle install and provide an estimation for the needed memory and disk space to support the required database.
i.
Log into SQLPLUS at the DOS prompt.
SQLPLUS / AS SYSDBA
ii.
Get the estimated SGA memory for your default install.
SELECT ROUND(SUM(VALUE)/1024/1024/1024) "TOTAL SGA (GiB)" FROM V$SGA;
iii.
Get the estimated PGA memory for a database instance for your default database install with low process utilization.
SELECT ROUND(SUM(PGA_MAX_MEM)/1024/1024) "TOTAL MAX PGA (MiB)" FROM V$PROCESS;
iv.
Get the maximum allocated for each user session for the default install.
SELECT ROUND(MAX(P.PGA_MAX_MEM)/1024/1024) "PGA MAX MEMORY OF USER SESSION (MiB)"
FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR AND S.USERNAME IS NOT NULL;
v.
Get the maximum number of processes that a database instance can manage for your default database install.
SELECT NAME, VALUE FROM V$PARAMETER
WHERE NAME = 'processes';
vi.
Calculate the PGA memory for full process utilization using this formula. Round to the nearest GiB.
Total PGA = PGA low utilization
+ (Number of Processes * Max Allocated per Session)
vii.
Calculate total RAM needed for the default install by using this formula. (an additional 20% is needed for the OS). Round to the nearest GiB.
Default install RAM = (SGA + Total PGA) / 0.8
viii.
Fill out the table below with the default install information.
ix.
Calculate the required memory based on 5000 process/concurrent users. Use this formula. (For a ball park estimat SGA size equals PGA size) Round to the nearest GiB.
Estimated RAM needed
= 2 * (Number of Processes * Max Allocated per Session) / 0.8
x.
Fill out the table below with the estimated RAM needed information.
xi.
Fill out the table below with the estimated database size.
|
SGA
(GiB)
|
PGA
(low util)
(GiB)
|
PGA
(high util)
(GiB)
|
TOTAL
RAM
(GiB)
|
TOTAL
DISK SPACE
(GiB)
|
Default Install
|
|
|
|
|
N/A
|
Estimation
|
|
N/A
|
|
|
|