IT 414 Midterm

· You are the lead DBA at amazon.com and are asked to size the amount of compute power you will need for a new web site they are building, briefly explain the information you would gather to accurately estimate the computer power needed (storage, memory, CPU).

Short answer: storage (scalability), memory (connection pool, instances), CPU (number of users)

· Discuss 3 major cloud infrastructure providers (AWS, Azure, Oracle) and what databases they use to hold data in their clouds.

o AWS is a strictly cloud-based storage method released by Amazon. They offer a wide variety of DBMS types, including relational, document, graph, and a few more. They mainly focus on IaaS features. o Azure mainly focuses on Relational databases, with few options for NoSQL. They have many SQL builds, all with varying uses for different users. o Oracle is the oldest and most used in the corporate world. Oracle is like Azure in that they mostly focus on relational types, with few options for NoSQL. Oracle is different, however, in that they offer both IaaS and SaaS as a feature of their cloud infrastructure.

· Discuss Dr. Bondra's GCSS-MC presentation and cite the following key points:

o Backstory of Project - Global Combat Support System, system used by Marines, supplies for missions, old hardware used, Oracle 10g o Description of problem -cold (static, no operations) backup a month, warm (real-time, operations performed) backup a week, more reporting done than usual with average transactions, corrupted database, lost 8 days of data o Lessons Learned - Backups are important, especially for such large projects as this, separate transactions and reporting, closer backup times, more frequent cold backups

· In 2 sentences each, for the following 5 objects, explain how each may be used within an Oracle database and provide quick example. Tables, indexes, sequences, materialized views, and partitions

o Sequences assign values by iterating through a specific series of arithmetic processes. They are most used to assign primary identifiers to new entities. o Views allow admins ensure a user cannot directly access the data in the database. They are used for users to see the information but not be able to alter it. o Indexes make it easier to access rows from the database by using a pointer. It is mainly used in Oracle databases with terabytes of data to scan through the rows quicker than scanning each row. o Triggers occur when a specific procedure is called to the database. It can be used to track what users are accessing, updating, deleting, etc. data to the database for security. o Partitions allow a database to be stored on multiple physical drives. It is like indexes in that they are used to break down a larger database into smaller parts.

· In a paragraph for each Discuss the role of an Oracle Database Administrator in:

o Short Answer: Database Creation and Maintenance, Good Application Design, Security

· List the three compute resources that affect performance with 2-3 sentences on how each can affect performance. Write a paragraph on how the 'cloud' helps with these potential performance problems.

o Short answer: CPU (least memory, fastest), Memory, Disk I/O (most memory, slowest), cloud is scalable, accesibility

· Explain the oracle cost-based optimizer and how it selects the best path to grab data from an oracle database, also, is it possible to influence that path?

o Short answer: Cost-based optimizer finds most efficient path, least number of protocols, record statistics, assign scores

· In 2 paragraphs explain the difference between a Database and an Instance.

o Short answer: Database: collection of data, physical storage, nonvolatile; instance: software to access database, code in memory, volatile

In 2 paragraphs, understanding that this is a very early stage of class, describe the origins of the relational database model and why you believe Oracle is the market leader in this segment in 2019

o Short answer: GE and IBM developed network and hierarchal models in 60s, relational described by Codd in 70, Oracle first relational model released, update model, different levels

· In 2 paragraphs explain the 2 options (1 paragraph each) of how to use a CLOB/BLOB within an Oracle database to store a video:

o Short answer: Large objects, 4 GB, CLOB - characters, convert to character, back to binary; BLOB - binary, no conversion

· For the background memory processes (e.g., LgwR, DbWR, CKPT, PMON, SMON) and in a paragraph explain what the process responsibility is.

o Short answer: LgwR - write redo logs; DbWR - write modified data to DB; CKPT - timestamp when checkpoint occurs SMON - monitors DB, fix crashes; PMON - monitors processes;

· Explain what the Optimal Flexible Architecture is, who created it, why is it an important concept for an Oracle Data Base Administrator to understand.

o Short answer: OFA allows better organization of systems, find files easier

· Discuss in a paragraph each, 3 of the Database Advisors available to you as a DBA.

o Short answer: SQL Tuning Advisor - analyze statements to imrpove them; SQL Access Advisor - system informs DBA of improvement to views, indexes, etc.; Memory Advisor - improve memory

· In a paragraph explain how Transparent data encryption (TDE) option to the database supports the concept of Data at Rest encryption. In a 2ndparagraph, explain how data masking would be used in a Personal Identifiable Information protection situation.

o Short answer: TDE automatically encrypts and decrypts, no secondary application, Data masking - hide user data, but looks like real data, Encryption - illegible data, need algorithm to reverse, data masking more secure

· Explain in 2 paragraphs why a valid backup is important to you as a DBA, explain how archive log mode facilitates the ability to have a point in time recovery.

o Short answer: backups are useful for errors, archive log allows DBA to return to point before redo if errors

· Discuss how the 'consumer groups' feature within oracle may help you limit potential problems with performance? 2 paragraphs

o Short answer: consumer group - group user sessions with similar resources, based on partitions available, allocate to groups rather than individuals, Resource Manager allocates these resources, move sessions between pools if requirements change

· Explain 3 options you as a database administrator could use if you wanted to protect a piece of data inside the database from a non-privileged user.

o Short answer: identity management, security privileges, views

· Explain what access and usage of the database is audited inside the oracle database. Discuss what the Oracle audit vault product would provide.

o Short answer: username, session identifier, terminal identifier, name of schema accessed, operation performed, completion code, date/timestamp

· What are the 3 ways you can launch SQL plus after a successful installation on your laptop/desktop/iPad?

o The 3 main ways to launch SQL Plus are through a desktop shortcut, using the command line, or locating the specific sqlplus.exe file

Discuss the market dynamics of the database market, include a reference to oracle's place and a NoSQL database, graph database... etc...

o The seven main types of DBMS types are NoSQL, ER, Relational, Document, Network, Graph, and Hierarchal Databases. Relational is the most well-known, where tables have relationships to each other with similar data to represent each. NoSQL is the next most popular, which is the opposite of a relational type. Each table within the database can hold any type of data and may or may not be related to each other. o Some of the biggest DBMS leaders include Oracle, Microsoft, and Amazon. Oracle is the forerunner of the relational database type. They were one of the first companies to present a DBMS architecture in the early day of computers and are still relevant to this day. Amazon Web Service is rapidly growing, however, ready to overpass Oracle anytime. o Relational databases are still relevant, but NoSQL is emerging as the preferred method to store data. With so much varying data, people prefer to have non-relational data that is more scalable and accessible comparatively. NoSQL is usually the most preferred method in cloud-storage, which is growing rapidly on its own.

· Discuss whether you believe more data or less data will be stored in the future and what new types of data may become available that are not available today. For Example, smartwatch is now collecting data about you as an individual (heart rate, activity levels, sleep patterns) that were not available prior.

o There will almost certainly be more data gathered in the future. There has been an astronomical increase in data stored since the age of the Internet, where everything can be tracked. Companies can use this data to better target customers and increase profit. I think with the increased of autonomous driving, these technologies could track your every move and use this data, either maliciously or not.