Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Revature > Enterprise: Week 2 > Flashcards

Enterprise: Week 2 Flashcards

1
Q

What is SQL?

A

SQL Stand for Structured Query Language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does SQL do?

A

t is a domain-specific language for working with certain databases called relational databases.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Is SQL a programming language?

A

SQL is not a programming language, although there are extensions to the specification like PL/SQL that add familiar programming constructs.

Instead, it is an English-like syntax that lets developers and database administrators abstract away the process of manipulating data and focus on the WHAT instead of HOW in the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a Relational Database?

A

A relational database is a type of database that stores information in tables - that is, the data is stored in rows and columns, similar to a spreadsheet.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is each row in the table?

A

Each row in the table is a record, and each record has properties which correspond to the different columns in the table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What type of software is used to manipulate a database?

A

relational database management system, or RDBMS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Is there a difference between relational databases and non-relational databases?

A

There are major differences. Nonrelational databases do not store data within tables that relate to each other, and thus do not use SQL to interact with the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What type of data provides meaning to an enterprise?

A

Related data is what provides meaning and organizes the structure of data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Normally where is relational databases used in?

A

OLTP (online transaction processing) environment, which means the idea of having related data is preferable in a very transactional system and that are normally row-based

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

For non-transactional environments, what is used?

A

OLAP - Online analytic processing: normally columnar-based, which is faster for reading but slower for manipulation

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How many zones does AWS span out to?

A

77 available zones within 24 geographic regions around the world

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What are AWS regions?

A

An AWS region is a geographical location with a collection of availability zones mapped to physical data centers in that region. Every region is physically isolated from and independent of every other region in terms of location, power, water supply, etc.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Why is this level of isolation important for AWS regions?

A

This level of isolation is critical for workloads with compliance and data sovereignty requirements where guarantees must be made that user data does not leave a particular geographic region.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Why is the presence of AWS regions also important?

A

The presence of AWS regions worldwide is also important for workloads that are latency-sensitive and need to be located near users in a particular geographic area.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is an Availability Zone?

A

An availability zone is a logical data center in a region available for use by any AWS customer.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What does each zone contain?

A

Each zone in a region has redundant and separate power, networking and connectivity to reduce the likelihood of two zones failing simultaneously.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is Amazon RDS?

A

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What does Amazon RDS provide?

A

It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What does RDS automate?

A

RDS automates expensive and time consuming tasks such as managing backups, software patching, automatic failure detection, and recovery.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What does RDS help control?

A

You can help control who can access your RDS databases by using AWS Identity and Access Management (IAM) to define users and permissions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

RDS is available on several database instance types. What do instance types comprise of?

A

Instance types comprise varying combinations of CPU, memory, storage, and networking capacity and give you the flexibility to choose the appropriate mix of resources for your database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What 6 database engines does the RDS provide?

A

Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Why use RDS?

A
  • AWS offers 15 database engines including relational, key-value, document, in-memory, graph, time series, and ledger databases.
  • Don’t need to worry about database mgmt tasks such as backups, recovery, configurations. It is managed by RDS
  • RDS is a relational database service, organizes data within tables in rows and columns
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What are availability zones (AZs)?

A

distinct locations in AWS Regions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

How is each availability zone engineered?

A

To be isolated from failures in other Availability Zones. Each is engineered to provide inexpensive, low-latency network connectivity to other Availability Zones in the same AWS Region.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What is Fault Tolerance?

A

Fault-tolerance defines the ability for a system to remain in operation even if some of the components used to build the system fail.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What does the security group do?

A

A security group controls the access to a DB instance. It does so by allowing access to IP address ranges or Amazon EC2 instances that you specify. ou can set security groups when configuring your RDS instance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What are the 3 ways of interacting with Amazon RDS?

A

AWS Management Console, Command Line Interface, Programatically Accessing Amazon RDS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What are some key differences between AWS RDS and AWS EC2 Instance?

A
  • DB services (like RDS) are managed services, meaning that you have limited control over the actual database. EC2 (Elastic Cloud Compute), gives you maximum control over the software stack, db, and operating system
  • EC2 allows you to compute capacity and SUPER privileges, increasing flexibility
  • RDS is more cost-effective and is automated, but limits control over the DB
  • RDS takes care of your DB from end-to-end by managing, maintaining, and securing it, eliminiating overhead costs and the role of a DBA
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What is Cloud computing?

A

Cloud computing is the on-demand delivery of compute power, database storage, applications and other IT resources through a cloud services platform via the Internet with pay-as-you-go pricing.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What does cloud computing provide?

A

Provides a simple way to access servers, storage, databases and a broad set of application services over the Internet.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What are the 6 advantages of cloud computing?

A
  • Trade capital expense for variable expense.
  • Benefit from massive economies of scale.
  • Stop guessing about capacity.
  • Increase speed and agility.
  • Eliminate overhead cost of maintaining data centers
  • Go global in minutes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What is Trade Capital Expense for Variable Expense?

A

Instead of having to invest heavily in data centers and servers before you know how you’re going to use them, you can pay only when you consume computing resources, and pay only for how much you consume.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What is Benefit from Massive Economics of Scale?

A

By using cloud computing, you can achieve a lower variable cost than you can get on your own. Because usage from hundreds of thousands of customers is aggregated in the cloud, providers such as AWS can achieve higher economies of scale, which translates into lower pay as-you-go price.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is Stop guessing about capacity?

A

Eliminate guessing on your infrastructure capacity needs. When you make a capacity decision prior to deploying an application, you often end up either sitting on expensive idle resources or dealing with limited capacity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What is Increase speed and agility?

A

In a cloud computing environment, new IT resources are only a click away, which means that you reduce the time to make those resources available to your developers from weeks to just minutes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What is Eliminate overhead cost of maintaining data centers?

A

Cloud computing lets you focus on your own customers, rather than on the heavy lifting of racking, stacking, and powering servers (infrastructure).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is Go global in minutes?

A

Easily deploy your application in multiple regions around the world with just a few clicks. This means you can provide lower latency and a better experience for your customers at minimal cost.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

What are the 3 models of cloud computing?

A

IaaS, Paas, Saas

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

What is Iaas?

A

Infrastructure as a Service (IaaS) is a self-service model for managing remote data center infrastructures. AWS offers IaaS in the form of data centers.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

What is PaaS?

A

Platform as a Service (PaaS) allows organizations to build, run and manage applications without the IT infrastructure. This makes it easier and faster to develop, test and deploy applications.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What is SaaS?

A

Software as a service (SaaS) replaces the traditional on-device software with software that is licensed on a subscription basis. It is centrally hosted in the cloud. A good example is Salesforce.com.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What are the common data types in SQL?

A

Numeric, Date/Time, Character/String, Binary, Misc.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What is the convention for SQL?

A

SQL is a case-insensitive language, but the convention is to use UPPERCASE to refer to SQL keywords and lowercase for non-SQL specific entities (like table or column names). This helps distinguish between SQL keywords and other words.

Also, for readability purposes we should split long commands or queries into multiple lines.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What is DML?

A

Data Manipulation Language statements are used to perform CRUD operations on the actual data. Operations are normally performed by row in a relational database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

What is INSERT?

A

INSERT, to insert a new row into a table.

INSERT INTO TABLE_NAME VALUES (V1, V2, …, VN)
INSERT INTO TABLE_NAME (C1, C2, C3) VALUES (V1, V2, V3)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

What is UPDATE?

A

UPDATE, to update one or more rows column values of a table that match a specific WHERE clause.

UPDATE TABLE_NAME SET C1 = V1, … , CN = VN WHERE X = Y

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

What is DELETE?

A

DELETE, to delete one or more rows of a table that match a specific WHERE clause.

DELETE TABLE_NAME WHERE [condition]

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

What is SELECT?

A

SELECT, to obtain one or more rows of a table that match a specific WHERE clause. In ORACLE databases this one is considered DML. This is how we perform queries in a database.

SELECT C1, …, CN FROM TABLE_NAME [table] WHERE [condition] GROUP BY [expression]
HAVING [condition] ORDER BY table.field

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

What is DQL?

A

Data Query Language, not really a sub-language within Oracle databases, is the sub language where only the SELECT statement exists.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

What are DQL Clauses?

A

The GROUP BY clause will combine all rows by a column specified in a query and perform any aggregate functions which are stated.

SELECT NAME, COUNT(NAME) FROM STUDENT GROUP BY (NAME)

The HAVING clause will pass another filter similar to the WHERE clause after everything has been filtered and grouped.

SELECT NAME, COUNT(NAME) FROM STUDENT GROUP BY (NAME) HAVING COUNT(NAME) > 5;

**If you try to perform this HAVING clause in a WHERE clause, a SQL error will be thrown, and it makes sense - the RDBMS doesn’t want you to perform an aggregate function combining all rows, per each row. It’s a performance safety measure.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

What are Scalar Functions?

A

Scalar functions operate on individual values and will perform some operation per row, and can be used in the SELECT or WHERE clause.

TO_CHAR(DATE,’DATE_FORMAT’)
TO_DATE(DATE,’DATE_FORMAT’)
UPPER(‘VALUE’)
LOWER(‘VALUE’)

To write them in a query:

SELECT UPPER(NAME) FROM STUDENT;
SELECT NAME FROM STUDENT WHERE UPPER(NAME) LIKE 'P%'.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

What are Aggregate Functions?

A

Aggregate functions operate on multiple values (multiple rows). These functions are used to combine (aggregate) the values existing in one column.

These functions are used in the SELECT clause. They can’t be used in the WHERE clause.

If there is more than one column being selected in the SELECT column section of a query which is not aggregating, a GROUP BY clause is required.

In order to perform similar WHERE clause Boolean operations with aggregate functions, the HAVING clause can be used.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

What is MAX(COLUMN)?

A

Returns the max value on a column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

What is MIN(COLUMN)?

A

Returns the minimum value on a column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

What is AVG(COLUMN)?

A

Returns the average value of the column

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

What is SUM(COLUMN)?

A

Returns the sum of the column

58
Q

What is COUNT(COLUMN)?

A

Returns the count of elements in a column

59
Q

What is TCL?

A

Transaction Control Language statements are utilized to manage transactions within a relational database.

60
Q

What are some of the TCL Commands?

A
  • COMMIT, any DML operations that were executed before the statements will be persisted permanently.
  • ROLLBACK, any DML operations between two COMMIT statements will be completely erased (something like Ctrl + Z that will stop only when it reaches last time you opened the specific file). Committed transactions cannot be rollbacked.
  • SAVEPOINT, utilized to ROLLBACK to a specific point in time.

The general flow of using TCL could be as follows:

[Many DML Operations]
SAVEPOINT A
[Many DML Operations]
ROLLBACK TO A

61
Q

What is a Schema?

A

A database schema refers to the formal structure of data defined in a relational database. This includes the various tables in the database as well as their columns, data types, and the relationship between tables.

62
Q

How are schemas enforced?

A

Schemas are enforced using constraints when defining tables, and we can visualize the schema of relational databases through entity-relationship diagrams, or ERDs.

63
Q

What are some of the constraints in SQL?

A

We can put integrity constraints on specific columns in our database when defining tables, which allow us to enforce the schema by ensuring consistency and integrity of the data in the table. The different constraints are listed below:

PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK
DEFAULT
AUTO INCREMENT
64
Q

What is a PRIMARY KEY?

A

A primary key is a constraint that uniquely identifies a record in a table. Often, this constraint will be enforced on some sort of “ID” field, such as “employee_id”. A primary key is inherently composed of two other constraints - unique and not null. Thus, a primary key MUST be provided when inserting a record into a table, unless the RDBMS system is generating it automatically behind the scenes.

65
Q

What is a FOREIGN KEY?

A

A foreign key constraint signifies that a column represents a reference to the primary key of another table. This allows us to create relationships between tables. For example, if we are modeling cars and the owners of those cars, we might have a Car table with an owner_id foreign key that references the user_id field in the People table. We can then lookup the owner of any car by fetching the owner_id of the car and finding the matching user_id in the People table.

66
Q

What is NOT NULL?

A

A not null constraint simply enforces that all records must have a field for the column on which this constraint is applied. For example, we know that every person has a social security number, so we might want to consider placing a not null constraint on that field in our users table (assuming we want to store the social security numbers). This prevents users of the database from leaving the table in an inconsistent or invalid state.

67
Q

What is UNIQUE?

A

The unique constraint works similarly - records cannot be inserted if another record already has the same value for the column on which this is declared.

68
Q

What is the CHECK constraint?

A

The check constraint provides a way of performing validation on values before records are entered into the table. For example, we may want to ensure that a bank account can never have a negative balance, so we might set a check constraint (CHECK (balance >= 0)).

69
Q

What is the DEFAULT constraint?

A

Finally, a default constraint allows setting default values on columns for records that are inserted into the table

70
Q

What is the AUTO-INCREMENT constraint?

A

Finally Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Very often the primary key of a table needs to be created automatically, and we define that field as AUTO INCREMENT field. Following is the syntax for creating an AUTO INCREMENT field.

CREATE TABLE TableName (
Column1 DataType AUTO_INCREMENT PRIMARY KEY,
Column2 DataType,
);

71
Q

What are Candidate Keys?

A

Usually a primary key consists of a single column; however, sometimes we may have a scenario in which there could be multiple columns that together create a primary key to uniquely identify rows. We call these candidate keys.

72
Q

What are Composite Keys?

A

Once we identify the actual combination of columns to use as the primary key, we call this a composite key.

For example, if you are modeling your CD collection, you might have fields such as track_no, album_id, and genre in the Track table. The track_no itself cannot work as a primary key because many different albums can have a track #1, for example. So we would need to create a composite key here, consisting of the track_no and the album_id columns. Using both of these columns together we can find the specific track we are looking for.

73
Q

What is DDL?

A

Data Definition Language statements are utilized to define the database schema or skeleton. It is how we implement the design structure of it. Some of the keywords in this sublanguage are:

Some keywords are:
CREATE, to create new objects or tables.
CREATE TABLE TABLE_NAME (C_NAME C_TYPE C_SYZE [NULL | NOT NULL], [CONSTRAINT])

ALTER, to modify existing objects or tables.
ALTER TABLE TABLE_NAME [ADD | MODIFY | DROP] C_NAME
ALTER USER IDENTIFIED BY PASSWORD
DROP, to delete existing objects or tables.

DROP TABLE TABLE_NAME [CASCADE]
TRUNCATE, to delete all the data existing within a table leaving the skeleton of the table only.

TRUNCATE TABLE_NAME
This is similar to performing DELETE TABLE with no where clause, the key difference is that TRUNCATE commits at the end of the operation.

All DDL operations cannot be rolled back, which means that any change made by these are permanent.

74
Q

What does normalization refer to?

A

Normalization refers to an optimization process of structuring a relational database in a way that reduces redundancy of data and improves data integrity and consistency.

75
Q

What is the first normal form?

A

1NF - must have a primary key, no repeating groups, and atomic columns

The first normal form enforces that a table must:

  • Have a primary key
  • Each column should be as granular as possible (e.g. “Name” column should be broken up into: “First Name”, “Last Name”, “Middle Name”, etc..)

○ granularizing the table information
○ Needs to have a primary key

76
Q

What is the second normal form?

A

To be in second normal form, a table must also:

  • Cannot have columns that are dependent on only one part of the key
  • If there are no composite primary keys, you are automatically in 2NF

2NF - must already be in 1NF, plus have no partial dependencies
○ Must be in first normal form
○ No partial dependencies - no composite key, no partial dependencies

77
Q

What is the third normal form?

A

To get to third normal form, a table must also:

  • Not have transitive dependencies
  • This means that if column C relates to column B which relates to column A which is the primary key, this is not in 3NF because C is related to the primary key but indirectly (it is a transitive dependency)

3NF - must already be in 2NF, plus have no transitive dependencies
○ Must be in second normal form
○ No transitive dependencies - which means

78
Q

What is a good way to memorize the 3 forms of normalization?

A

A good way of remembering these normal forms in order is to remember the legal proceeding of swearing to tell the truth, the whole truth, and nothing but the truth. In relational databases, we must have the key (1NF), the whole key (2NF), and nothing but the key (3NF) so help me Codd.

79
Q

What is a JDK?

A

JDK includes source files, includes the debugger and the compiler to develop code efficiently,

80
Q

What is a JRE?

A

JRE - it is the surrounding environment and holds the core functionality

81
Q

What is a JVM?

A

JVM - it can be portable and runs in a particular type of way, Scala and Kotlin are other types of systems that use the JVM

82
Q

What is a native?

A

It’s a C or C++ implementation that is implemented in the JVM

83
Q

Is java pass by value or pass by reference?

A

It is pass-by-value

84
Q

What is a table in database management?

A

It is a series of columns and rows

Think of tables as your classes and rows as objects

85
Q

What is DML?

A
  • It is data manipulation language, to manipulate data
  • It is how we are going to use to work with the objects using the CRUD operations
  • CRUD: create, read, update, delete
  • What are the main keywords? Insert, select, update, delete
86
Q

What is DDL?

A
  • Data definition language, for creating and altering objects in the DMS (tables, functions, schemas, databases, users, roles, triggers, etc)
  • Keywords: create, alter, truncate, drop
87
Q

What is DCL?

A
  • Sub-language that controls, Data Control Language

- Keywords: grant, revoke

88
Q

What is BCNF?

A

○ Boyce Codd Normal Form
○ States that every field should rely on the primary key
○ The key, the whole key, nothing but the key, so help me Codd

89
Q

What is the fourth normal form?

A

○ Must be BCNF

○ No multi valued dependencies

90
Q

What is referential integrity?

A

One table cannot point to a non existent entry in another table

91
Q

What is an orphaned (Batman) record?

A
  • No orphans allowed, must eliminate the child first before getting rid of the parent record
  • When a child table points to a parent table, and you want to delete the parent table. The child must either point to a different record in the parent table or be deleted first to avoid orphaned records
92
Q

What is Multiplicity?

A
  • Relationship between tables
  • 1-m or m-1: foreign key goes in the many table
  • M-m - intermediate table, aka junction table, to hold foreign keys of the other two tables
    1-1 - in order to achieve this, make the column unique, no matter the table
93
Q

What is Multiplicity?

A
  • Relationship between tables
  • 1-m or m-1: foreign key goes in the many table
  • M-m - intermediate table, aka junction table, to hold foreign keys of the other two tables
  • 1-1 - in order to achieve this, make the column unique, no matter the table
94
Q

What is TCL?

A

Transaction control language

  • Keywords: begin, end, savepoint, rollback, commit
  • Begin: tells to open transaction block, talks to postgres
  • Commit - tells it ready to be persisted, enter into the database
  • Rollback - don’t like so goes back to the last commit
  • Savepoint - a saving point but not yet committed
95
Q

What does Begin do in TCL?

A
  • Begin will create the transaction block and gather the transaction information from the dbms, tells to open transaction block, talks to postgres
96
Q

What does End do in TCL?

A
  • End will end the transaction block,
97
Q

What does Savepoint do in TCL?

A
  • Savepoint will create savepoints within the transaction that you can rollback to if needed; a saving point but not yet committed
98
Q

What is a Rollback in TCL?

A
  • Rollback will revert the changes back to the original state of the transaction or to a state in a savepoint; don’t like so goes back to the last commit
99
Q

What is a Commit in TCL?

A
  • Commit will commit the changes and end the transaction, sending the chagnes to the db for persistent alterations; tells it ready to be persisted, enter into the database
100
Q

How can table relationships be defined?

A

By using foreign key constraints

101
Q

What are several different kinds of relations that exist in a relational database?

A

1-to-1
1-to-M/M-to-1
M-to-M

102
Q

What is a one-to-one relationship?

A

A one-to-one relationship means that each entity in the table only relates to a single entity in the other table.

Ex:
If we are modeling a school, where each classroom has a single projector in it, we would want to make this relationship a one to one between the Classroom and the Projector tables. In our database, we can provide the classroom table a projector_id foreign key and provide the projector table a classroom_id foreign key.

To enforce the one to one aspect, we should also apply a unique constraint on the foreign key columns. Otherwise, a user could add another projector record with the same classroom_id as an existing record, and then our one to one relationship would be broken.

103
Q

What is a one-to-many relationship?

A

A one-to-many (or vice versa, many to one) relationships is where one entity can belong to, own, or otherwise relate to multiple other entities.

Ex:
A Student could have many books, so this would be a one-to-many relationship. To create this in the database, we add the foreign key only on the many sides of the relationship - so a book entity would have a field such as student_id as a foreign key to identify the owning student.

104
Q

What is a many-to-many relationship?

A

A many-to-many relationship implies a one-to-many relationship in both directions on the entities.

Ex:
A Teacher can have many Students, but a Student could have many Teachers as well. In this case, we cannot provide a direct link between the tables in the database - instead, we need to create what is called a junction table or bridge table to relate the two tables.

So, in our student-teacher example, we could create a Class table which contains two foreign keys - one that refers to the Teacher table’s primary key and one that refers to the Student table’s primary key. This creates a list of unique Teacher-Student mappings that can be used to look up which students a particular teacher teaches, or which teachers a particular student has.

105
Q

What is referential integrity?

A

When we create table relationships, it is important that our data remains in a consistent state throughout the database. For example, we never want a record on our class table to be pointing to a record in either the Teacher or the Student table that does not exist. We call enforcing this property as maintaining referential integrity.

106
Q

What is an orphan record?

A

When we break referential integrity, we will find orphan records in the database - these are records whose foreign keys do not point to an existing record in the other table.

107
Q

What is a way to prevent orphan records, breaking referential integrity?

A

One way of preventing this from occuring is by using a setting called CASCADE DELETE - when we enable this, deleting a record in the table will also cascade that operation and delete any records in tables that reference the that record via foreign keys.

108
Q

What are the three forms of normalization and describe them?

A

Normalization is the process of designing your database in such a way that you have no composite columns, little to no redundant data, and that your data dependencies make sense.

  1. To reach First Normal Form, we must get rid of composite columns, and we must have a primary key
  2. To reach Second Normal Form, we create separate tables to track data that causes duplication of otherwise unique data.
  3. To get to Third Normal Form, you promise that, every column will describe the key, the whole key, and nothing but the key!
109
Q

What are the 4 properties that a transaction has?

A

A - Atomicity
C - Consistency
I - Isolation
D - Durability

110
Q

What is a transaction?

A

Any amount of DML statements before a COMMIT statement is considered a transaction
Transactions are considered to be logical units of work

111
Q

What is ATOMICITY in a transaction?

A

Atomicity - Atomicity means that either all of the transactions will execute successfully or none of them will.

  • “All or nothing”, if any statement on the transaction fails, the whole transaction fails.
  • transactions when all goes in or none goes in, all of the transaction persists, or none
112
Q

What is CONSISTENT in a transaction?

A

Consistency - Consistency means that constraints are enforced for every committed transaction. That indicates that all Keys, Data types, Checks, and Triggers are successful and no constraint violation is triggered.

CONSISTENT - If the database was in a consistent state before the transaction, it should be after it.

  • If the database was in a consistent state before the transaction, it should be after it.
  • the state of the database will change and persist through a transaction
113
Q

What is ISOLATED?

A

Isolation - If two transactions are executing concurrently and working on the same data, then one transaction should not disturb the other transaction. Isolation guarantees that concurrently running transactions should not affect each other.

ISOLATED - One transaction shouldn’t affect other transactions. It can be applied in different levels.
- One transaction shouldn’t affect other transactions. It can be applied in different levels.

114
Q

What is DURABLE?

A

Durability - Durability means that once a transaction is complete, it guarantees that all of the changes are recorded in the database. If our system is suddenly affected by a system crash or a power outage, then all unfinished committed transactions may be replayed.

DURABLE - Persisted data should be saved permanently, even in the case of power loss or catastrophic software or hardware failure.

115
Q

What are the isolation levels?

A
  • serializable
  • repeatable reads
  • read committed
    read uncommitted
116
Q

What is serializable?

A

Serializable

  • Allowed in Oracle
  • Read/Write locks
  • Applies range locks even in the WHERE clauses of a select statement
  • Phantom reads can’t happen because of this
  • Table that is being read can’t be modified until the reading is done (no INSERTS, no UPDATES, no DELETES)
117
Q

What are repeatable reads?

A

Repeatable Reads

  • Not used often
  • Read/Write locks
  • Doesn’t provide range locks, that means phantom reads can happen
  • Doesn’t lock the whole SELECT statement, nor INSERTS, nor UPDATES, nor DELETS
118
Q

What are read committed?

A

Read Committed

  • Oracle default
  • Write only locks
  • Only data that is committed will be seen by other transactions
  • Dirty reads can’t happen, but Phantom reads can
  • This is why it is recommended to not perform very long transactions
119
Q

What is read uncommitted?

A

Read Uncommitted

  • A disaster
  • Dirty reads are normal, any transaction can see any uncommitted data
  • Very inconsistent
120
Q

What are the 3 read phenomena?

A
  • Dirty Read
  • Non-repeatable read
  • Phantom read
121
Q

What is a dirty-read?

A

Dirty Read: reading data that is uncommitted

122
Q

What is a Non-repeatable read?

A

Non-repeatable read: when a row is read twice in a transaction and the values are different

123
Q

What is a phantom read?

A

Phantom Read: reading data that is being added or modified by a running transaction

124
Q

What is DCL?

A

Data Control Language - shows who can control which information; used to manage the security and control of database systems.

Keywords: GRANT, REVOKE:

125
Q

What does the GRANT command do?

A

GRANT, to grant any permissions to an existing user.

GRANT PERMISSION TO USERNAME

126
Q

What does the REVOKE command do?

A

REVOKE, to revoke any permissions of an existing user.

REVOKE PERMISSION TO USERNAME

127
Q

What does PL/SQL stand for?

A

PL/SQL stands for the Procedural Language extension to SQL and it is a complete programming language which also allows SQL statements.

128
Q

What is a sequence?

A

Sequence

A sequence is an object which holds a numeric number that starts from a certain point and it also contains a max. It increments by a specific amount every time NEXTVAL is called.

They can be combined with Triggers to auto increment primary key columns.

CREATE [OR REPLACE] SEQUENCE START WITH 1 INCREMENT BY 1

129
Q

What are triggers?

A

Trigger

A trigger is a block of code that executes when a specific event happens. These events can be INSERT, UPDATE or DELETE statements, and they can happen AFTER or BEFORE.

Syntax:

CREATE [OR REPLACE] TRIGGER 
BEFORE INSERT ON TABLE_NAME
  FOR EACH ROW
  BEGIN
    (PL/SQL code)
  END;
130
Q

What is a cursor?

A

Pointers to a result set. They can be used to loop programmatically on the output of a SELECT statement (similar to iterators in Java).

Oracle provides SYS_REFCURSOR, its own type of REFCURSOR. This means you can create your own type of REFCURSOR

131
Q

What is a Stored Procedure and what are some of its properties?

A

Stored Procedure

PL/SQL code that can be executed in certain ways and has some properties:

  • They don’t return anything.
  • They may or may not contain IN (by value) and OUT (by reference) parameters.
  • They allow any DML statements within.
  • These means transactions can be created in a stored procedure.
  • Stored procedures can call other procedures and functions.
  • Can NOT use stored procedures in DML statements.
    EXEC STORED_PROCEDURE
132
Q

What is a User Defined Function?

A

Function:

User Defined Functions, these are like stored procedures but have some other restrictions or abilities:

  • They must return something.
  • Cursors are allowed.
  • It should be a single value.
  • They may or may not contain IN parameters (by default).
  • Only SELECT statements are allowed.
  • Functions can only call other functions (no stored procedures).
  • They can be used in any DML statement.
  • To call a function, you have to use a DML statement (you can’t EXEC).
  • Use FROM DUAL if you are not selecting from a specific table.
  • DUAL is a dummy table which returns anything your throw at it.
133
Q

What are PL/SQL Types?

A

Types:

Similar to classes, they can be used as your own datatypes for columns. We are not going to use this, because this is a high level technique which doesn’t follow the normal forms.

134
Q

What are PL/SQL VARRAY?

A

VARRAY

Arrays of any Oracle data type or your own type, they can be used for columns.

135
Q

What are Nested Tables in PL/SQL?

A

Nested Table

Infinite arrays, they can be seen as tables in columns. They have to be of a specific data type

136
Q

What are the 5 set operators?

A
  • UNION
  • UNION ALL
  • INTERSECT
  • MINUS
  • EXCEPT
137
Q

What does UNION and UNION ALL do?

A

UNION does not keep duplicates, but UNION ALL will

138
Q

What is an INTERSECT?

A

Only returns records in common between the queries

139
Q

What is a MINUS?

A

Removes from the first result set any rows that appear in the second result set and returns what remains

140
Q

What is an EXCEPT?

A

Same as MINUS, but for SQLServer instead of Oracle, Removes from the first result set any rows that appear in the second result set and returns what remains

141
Q

What are SET OPERATORS?

A

Set operators are different from joins. Instead of combining columns of two tables, set operators combine the rows of different result sets. Essentially, set operators perform some kind of (set) operation on two different queries.
TOP -> DOWN table model instead of a SIDE-TO-SIDE

Revature (3 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions