Describe relational concepts

Question 1

Relational data

Answer 1

In a relational database, you model collections of entities from the real world as tables. An entity can be anything for which you want to record information; typically important objects and events. A table contains rows, and each row represents a single instance of an entity.

-Relational tables are a format for structured data, and each row in a table has the same columns
-Each column stores data of a specific datatype

Question 2

Describe normalization and why it is used

Answer 2

Normalization is a database design technique that involves organizing and structuring relational database tables, that minimizes data duplication and enforces data integrity.

While there are many complex rules that define the process of refactoring data into various levels (or forms) of normalization, a simple definition for practical purposes is:

-Separate each entity into its own table.
-Separate each discrete attribute into its own column.
-Uniquely identify each entity instance (row) using a primary key.
-Use foreign key columns to link related entities.

Question 3

Identify common structured query language (SQL) statements

Answer 3

SQL stands for Structured Query Language, and is used to communicate with a relational database. It’s the standard language for relational database management systems.

SQL statements are grouped into three main logical groups:

-Data Definition Language (DDL): You use DDL statements to create, modify, and remove tables and other objects in a database. (CREATE, ALTER, DROP, RENAME)

-Data Control Language (DCL):Database administrators generally use DCL statements to manage access to objects in a database by granting, denying, or revoking permissions to specific users or groups. (GRANT, DENY, REVOKE)

-Data Manipulation Language (DML):You use DML statements to manipulate the rows in tables. These statements enable you to retrieve (query) data, insert new rows, or modify existing rows. You can also delete rows if you don’t need them anymore. (SELECT, INSERT, UPDATE, DELETE)

Question 4

Identify common database objects

Answer 4

In addition to tables, a relational database can contain other structures that help to optimize data organization, encapsulate programmatic actions, and improve the speed of access.

-Views: Are virtual tables that are based on the result of a SELECT query. They do not store data themselves but provide a way to present data from one or more tables in a specific format.

-Stored Procedures: Stored procedures are precompiled sets of one or more SQL statements that can be executed as a single unit. They are stored in the database and can be called by applications or other stored procedures.

-Indexes: are data structures associated with tables to improve the speed of data retrieval operations, such as SELECT queries. They provide a quick reference to the location of data within a table.