CST2355 – Database Systems: Assignment 1CST8276 - Advanced Database TopicsAssignment 1: Designing and Assessing Databases for a Team Environment (10%)This assignment relates to the following...

1 answer below »
Need help with assignment and can only use information mentioned you presented in the 3 modules


CST2355 – Database Systems: Assignment 1 CST8276 - Advanced Database Topics Assignment 1: Designing and Assessing Databases for a Team Environment (10%) This assignment relates to the following Course Learning Requirements: CLR 1 - Plan for and install a database management system (including the creation of a database) CLR 2 - Administer a DBMS using knowledge of SQL, database security features, globalization and database architecture (storage, memory and processes) CLR 6 - Build database systems that directly support internationalization and globalization CLR 7 - Explore and gain practical experience in current advanced database technology Objectives of the Assignment You will draft a design and implementation report for a customer database. The report will also include the steps for implementing Oracle and how the default configuration should be changed. Instructions Enter your answers directly in this file based on what you have learned from the activities covered in Modules 1, 2, and 3. Use the Oracle manuals for reference. Your report will include the following sections: 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. BULLET form is sufficient Objective one Objective two Objective three 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 Guideline one Guideline two Guideline three Guideline four Guideline five 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 ? Oracle Solution. Install and do a basic default configuration of Oracle. Installation Instructions for Oracle: Preparation: Download the Oracle software using the link your facilitator provided you. Create a new folder to hold the ‘unzipped file’ – short name like C:\oracleinstall. Unzip the Oracle compressed files into this new single common folder. Open a “cmd” window as administrator (i.e., run as administrator). Running the installation scripts MUST BE RUN WITH ADMINISTRATOR privileges. Google how to ‘run cmd as administrator, if you are uncertain. Set your location within CMD mode (e.g. cd c:\oraclecinstall) and run the following command: setup -ignorePrereq -J"-Doracle.install.db.validate.supportedOSCheck=false" Respond appropriately to the installation customization queries. The critical steps are highlighted – they are mandatory for this course. Step 1: Configure Security Updates: Email option – leave blank, does not apply Deselect the update notification option. You do NOT want to receive security updates. Press 'Next' Ignore the warning, press 'Yes'. Step 2: Installation Option: Select 'Create and configure a database', then press 'Next' Step 3: System Class: Select 'Desktop Class', then 'Next' Step 4: Home User Selection Select 'Use Built-in Windows Account, then press 'Next' Ignore the security warning and proceed. Step 5: Typical Installation Configuration: Accept defaults EXCEPT for the following: Global DB name: change to 'orcl' Make sure to uncheck the ‘create as container database” checkbox Admin (SYS) password: 'yourFirstNamesOracle123' (e.g., dougOracle123) If you receive a warning('INS-30011') that your password strength is poor, ignore it 'Next' / 'Yes' to continue Step 6: Prerequisite Check is performed. This may take some time. Sometimes the installer will pop-up a window underneath a visible window (so that you cannot see the pop-up) so some manual intervention may be required. Step 7: Summary of response is displayed. 'Save Response File' and 'Install'. Step 8: Install Product message is displayed. If you receive a Windows firewall message, allow access for Java. Several components are installed during this portion. In the Password Management screen, change the password for user SYSTEM to : 'yourFirstNamesystem' (e.g., cherylsystem). You may unlock and change passwords for some other accounts at the same time (or just leave them defaulted for now. The website below has the list of default Oracle usernames and default passwords. https://www.orafaq.com/wiki/List_of_default_database_users Click 'OK' Ignore any additional password standards warning message. 'Continue? Yes' / 'OK' Windows 10: If Services for Microsoft Transaction Server cannot start – that is fine. Click Next and Yes when asked are you sure you want to continue. You now have Oracle installed. DBA Confirmation. In this section provide a screen shot for each of the following -- Installed. Window of Oracle Instance Manager with database running. Available. Successful log into SQLPLUS at the DOS prompt. Running. Successful execution of an INSERT statement and a SELECT of the data that was inserted.. 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. Log into SQLPLUS at the DOS prompt. SQLPLUS / AS SYSDBA Get the estimated SGA memory for your default install. SELECT ROUND(SUM(VALUE)/1024/1024/1024) "TOTAL SGA (GiB)" FROM V$SGA; 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; 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; 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'; 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) 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 Fill out the table below with the default install information. 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 Fill out the table below with the estimated RAM needed information. 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 Submission. Submit your document. Include your name, student number, and the name of the assignment. Please note: Algonquin College has a policy regarding Plagiarism. The consequences are immediate zeroes for assignments and potentially an instant fail for the whole course. It is only natural for students to work together to complete assignments. DO NOT PLAGIARIZE each other’s work. Assignment 1 Grading Rubric (10%) Table 1 - Assignment 1 Grading Rubric Exemplary Accomplished Developing Incomplete Criteria 3 2 1 0 Objectives Included 3 business objectives. Each objective includes how the components in the DBMS / database supports that objective. Included 2 business objectives. Each objective includes how the components in the DBMS / database supports that objective. Included a business objective with a vague description of supporting components. Not completed Guidelines Included 5 original guidelines that are complete and concise. Included 3 or 4 original guidelines that are complete. Included 1 or 2 guidelines that are in-concise or incomplete. Not completed Assumptions - Included at least 80% of the correct assumptions for storage size. All numbers are correctly calculated. Included at least 60% of the correct assumptions for storage size. Most numbers are correctly calculated. Included less than 60% of the correct assumptions OR Very few of the numbers are correctly calculated. Solution - DBA Confirmation Installation steps included DBA confirmation steps for DBMS is installed, DBMS is running, and database is available. Each confirmation step included all correct screen shots. Installation steps included DBA confirmation steps for at least two of the following: DBMS is installed, DBMS is running, or database is available. Each confirmation step included all correct screen shots. Installation steps included DBA confirmation steps for at least one of the following: DBMS is installed, DBMS is running, or database is available OR Not all confirmation steps include correct screen shots. Estimation Filled out 7 to 8 cells in the estimation table Filled out 5 to 6 cells in the estimation table Filled out 4 cells in the estimation table Not completed End of Assignment 1 Module 1: Database Administrators and Design Teams Introduction Database Administration is where technology, data, and business, converge. It is the nuts and bolts of organizations and database administration is becoming a more crucial discipline, as Canada advances towards the digital economy. This course will walk you through the data administrators and how they work with the design teams to deliver high performance, secure, and available databases. It will also discuss database and application design, and how management translates to business' performances. You will explore the tools and practices that keep data useful, and the databases up and running. In this module you will be introduced to the database administrators, their various flavors, and how they work with other members of the design team. Learning Outcomes By the end of this module, you should be able to complete the following: 1. Explain typical responsibilities of a database administrator. 2. Outline current requirements for DBA certifications. Key Terms & Concepts Database Administration: Set of activities performed to ensure that a database is always available as needed by the business. Database Administrator: Maintains the database environment by directing / performing activities to keep the data secure and available. Data Administrator: Controls business data assets and how their processing and interactions is done between different applications and business processes. System Administrator: Works on the upkeep, configuration, and reliable operation of computer infrastructure such as servers and networks. Data Warehouse Administrator: Does day-to-day activities for running and supporting data warehouses. This includes design, archiving, tracking data changes, migration, and
Answered Same DayJan 20, 2023

Answer To: CST2355 – Database Systems: Assignment 1CST8276 - Advanced Database TopicsAssignment 1:...

Robert answered on Jan 21 2023
36 Votes
CST2355 – Database Systems: Assignment 1
CST8276 - Advanced Database Topics
Assignment 1: Designing and Assessing Databases for a Team Environment (10%)
This assignment relates to the following Course Learning Requirements:
CLR 1 - Plan for and install a database management system (including the creation of a database)
CLR 2 - Administer a DBMS using knowledge of SQL, database se
curity features, globalization and database architecture (storage, memory and processes)
CLR 6 - Build database systems that directly support internationalization and globalization CLR 7 - Explore and gain practical experience in current advanced database technology
Objectives of the Assignment
You will draft a design and implementation report for a customer database. The report will also include the steps for implementing Oracle 12c and how the default configuration should be changed.
Instructions
1. Enter your answers directly in this file based on what you have learned from the activities covered in Modules 1, 2, and 3.
a. Use the Oracle manuals for reference.
2. Your report will include the following sections:
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
(
1
)
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 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.
In your report each calculation must include a title for what that calculation is for along with an explanation of how the calculation was done.
Remember to include the total size of the whole database needed based on the assumptions.
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....
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here