1 Oracle and Structured Query Language (SQL)

What is an Entity-Relationship Diagram (ERD)?

A logical model of a business showing entities and their relationships.

What’s the difference between TRUNCATE and DELETE?

TRUNCATE:

1. requires at least ALTER permission on the table

2. DDL

3. logs only deallocation of data pages

4. faster

5. cannot be rollbacked

6. doesn’t activate triggers

7. no WHERE condition, removes all rows, resets auto-increment value

DELETE:

1. requires DELETE permissions on the table

2. DML

3. logs each row deletion

4. slower

5. can be rollbacked

6. can activate triggers

What’s a relational database?

It’s a physical implementation of ERD. It’s how the ERD’s data, relations, constraints, etc are stored.

What’s the notation for representing relationships in database modeling?

Crow’s Foot notation

What’s the symbol in Crow’s Foot notation for “Exactly one”?

-|-|—————

What’s the symbol in Crow’s Foot notation for “Zero or one”?

-|-0—————

What’s the symbol in Crow’s Foot notation for “Zero, one or more”?

→0————

What’s the symbol in Crow’s Foot notation for “One or more”?

→|—————

How are ERD entities implemented in a relational database?

Entities become tables; entity attributes become columns.

What is a primary key?

A unique and not null identifier for each row in a table.

What is a foreign key?

A column in one table that references the primary or unique key in another table to establish a relationship. Foreign key is a copy of all columns that are part of the constraint in a second table so that the second table can relate to the first. They must have matching data types and order

How are many-to-many relationships handled in relational design?

They are replaced with a third entity (junction/association table).

Is it possible to have a many-to-many relationship in a physical model in database?

No, There is never a many-to-many relationship in a finished physical model.

What is database normalization?

The process of structuring a database to reduce redundancy and improve integrity.

What does First Normal Form (1NF) require?

Tables must not contain repeating groups; all attributes must be atomic.

What does Second Normal Form (2NF) require?

No non-key attribute should depend only on part of a composite key.

What does Third Normal Form (3NF) require?

No transitive dependencies; all non-key attributes depend only on the primary key.

Mostly to divide many-to-many relationships into one-to-many-to-one relationships.

Primary key values are associated only with logically related attributes, and other data such as lookup data is moved to separate tables.

What does BCNF (Boyce-Codd) normal form require?

It’s a slightly modified version of 3NF designed to eliminate structures that might allow some rare logical inconsistencies to appear in the data. It’s in 3NF, X should be a super-key for every functional dependency (FD) X → Y in a given relation. To test whether a relation is in BCNF, we identify all the determinants and make sure that they are candidate keys.

What does Fourth Normal Form (4NF) require?

BCNF plus additional logic to ensure that every multivalued dependency is dependent on a superkey.

What does Fifth Normal Form (5NF) require?

4NF plus every join dependency for the table is a result of the candidate keys.

When are 3NF designs ideal?

When minimizing duplicate data and conflicts during data entry and updates. They are ideal when lots of data entry and updates are involved. Most transaction-based DB apps are in 3NF.

When might denormalized designs (1NF/2NF) be preferred?

For reporting, big data, data warehouses, or read-heavy systems where performance is prioritized. Small or none updates are on the records. 1NF or 2NF are also known as denormalized.

What is a database in the context of applications?

A persistent store of data that exists beyond the execution of an application.

What is SQL?

The industry-standard language for creating and interacting with relational databases.

Can SQL be called from other programming languages?

Yes, languages like Java, PHP, etc., can embed and issue SQL statements.

Which Oracle tools are commonly used to execute SQL?

SQL*Plus and SQL Developer.

How is SQL pronounced?

Either 'ess-cue-ell' or 'sequel'; both are correct.

What does DDL stand for?

Data Definition Language.

What is the purpose of DDL?

To create, alter, and drop database objects, and manage privileges and metadata.

Which SQL statements belong to DDL?

• CREATE - user, table, view, index, synonym, or other object

• ALTER - modifies existing object

• DROP - removes object

• RENAME

• TRUNCATE - removes all rows from table. Gives up recovery options.

• GRANT - provides privileges, rights to user objects to enable them to perform some tasks

• REVOKE - removes privileges that have been granted

• FLASHBACK - restores an earlier version of a table or database

• PURGE - irrevocably removes database objects from the recycle bin

• COMMENT - adds comments to the data dictionary for an existing object

Why is TRUNCATE considered DDL and not DML?

It removes all rows without recovery options, unlike DELETE, and commits immediately.

What does DML stand for?

Data Manipulation Language.

What is the purpose of DML?

Data manipulation language is there to add, modify, view, and delete data in database objects.

Which SQL statements belong to DML (Data manipulation language)?

• SELECT - retrieves data from a table

• INSERT - adds new data to a table

• UPDATE - modifies existing data in a table

• DELETE - removes existing data from a table

• MERGE - combines INSERT, UPDATE, and DELETE into one statement

What is MERGE used for?

To combine INSERT, UPDATE, and DELETE in one statement.

What does TCL stand for?

Transaction Control Language.

Which statements belong to TCL?

COMMIT, ROLLBACK, SAVEPOINT.

What does COMMIT do?

Saves all DML changes made in the current session.

What does ROLLBACK do?

Undoes DML changes made in the current session since the last COMMIT.

What does SAVEPOINT do?

Marks a point in a transaction to roll back to without undoing the entire transaction.

Which SQL statements cause an implicit COMMIT?

Any DDL statement.

What is the purpose of the SELECT statement?

To query and retrieve data from one or more tables.

Can SELECT modify data?

No, SELECT only retrieves data; it does not change the underlying data.

What can SELECT do beyond simple retrieval?

Join multiple tables, filter rows, transform data, aggregate results.