HS 2021 Short Answer Question

13. Search index

Advantages - Speed up SELECT query - Helps to make a row unique or without duplicates(primary,unique) - If index is set to fill-text index, then we can search against large string values. for example, to find a word from a sentence etc.

4. Aggregate Functions

Aggregate Functions are all about: - Performing calculations on multiple rows - Of a single column of a table - And returning a single value → AVG - calculates the average of a set of values: → COUNT - counts rows in a specified table or view → MIN - gets the minimum value in a set of values → MAX - gets the maximum value in a set of values → SUM - calculates the sum of values

1. Types of anomalies

- A Data Anomaly is where data inconsistency is introduced into a database because a relational schema is poorly designed. - If a physical database was built using the poorly designed schema one or more of the following anomalies could occur: + Insert Anomaly: → An Insertion Anomaly occurs when "Data values cannot be inserted because the primary key value is not known" → The insertion anomaly occurs as a new row cannot be inserted • Primary key value is Null • Primary keys cannot be null + Update Anomaly: → An Update Anomaly is caused by data redundancy. It occurs when we update one value of an attribute in one row but do not update identical values in other rows. + Deletion Anomaly: → A Deletion Anomaly occurs when a data about an entity is deleted and causes data about another entity to be inadvertently deleted.

19. NoSQL introduction

- A NoSQL originally referring to non SQL or non relational is a database that provides a mechanism for storage and retrieval of data. This data is modeled in means other than the tabular relations used in relational databases.

6. Unique constraints

- A UNIQUE constraint defines a set of columns that uniquely identify rows in a table only if all the key values are not NULL. - If one or more key parts are NULL, duplicate keys are allowed. - A table can have multiple UNIQUE constraints.

5. Constraints (column constraints, table constraints)

- A column-level constraint: Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow. - Column constraints include: → NOT NULL → PRIMARY KEY → UNIQUE → FOREIGN KEY → CHECK - A table-level constraint: Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level check constraints can refer to 0 or more columns in the table. - Table constraints include: → PRIMARY KEY → UNIQUE → FOREIGN KEY → CHECK

2. Foreign key

- A foreign key is a key used to link two tables together. - A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.

10. Relation Schema

- A set of attributes is called a relation schema (or relation scheme). A relation schema is also known as table schema (or table scheme). A relation schema can be thought of as the basic information describing a table or relation. Relation schema defines what the name of the table is. This includes a set of column names, the data types associated with each column.

11. ERD

- An entity relationship diagram (ERD), also known as an entity relationship model, is a graphical representation that depicts relationships among people, objects, places, concepts or events within an information technology system.

3. Inner join and outer join

- An inner join focuses on the commonality between two tables. - When using an inner join, there must be at least some matching data between two (or more) tables that are being compared. - An inner join searches tables for matching or overlapping data. - An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table.

8. Cardinality

- Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. - The lower the cardinality, the more duplicated elements in a column. - There are 3 types of cardinality: high-cardinality, normal-cardinality, low-cardinality.

17. ETL (Extract, Transform, Load)

- Extract data from the source systems - Transform data to meet the design of the Data Warehouse - Load data into the correct tables

20. VIEW

- In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary.

9. Normalization

- Normalization is a database design technique used to generate relational schemas - Normalization is often used as an alternative to: → ERDs (Entity-relationship diagram) - Normalization requires no diagrams - An aim of Normalization is to achieve 3rd Normal Form 3NF - Stages of Normalization: → First normal form: 1NF: Remove Repeating groups → Second normal form: 2NF: Remove Part Key Dependencies → Third normal form: 3NF: Remove Non-Key Dependencies

12. Types of relationship

- One-to-one: Both tables can have only on record on either side of the relationship. Each primary key value relates to only on (or no) record in the related table. - One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. - Many-to-many: Each record in both tables can relate to any number of records (or no records) in the other table.

18. Redundancy

- Poorly designed schemas allow for data redundancy → Where the same data is unnecessarily stored in a database multiple time

7. Count function

- The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. - It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows.

15. String functions

- The SQL ConCat function combines two strings into a single string → Limitation: ConCat can only join 2 strings - The Pipe || operator combines strings → No limit on how many strings - SUBSTR function extracts a substring for a larger string. Specify the starting position and the number of characters required. - SUBSTR function returns a portion (or a sub string) of a string: → Specify the original string → Specify the starting position → Specify the length (number of characters) required - The INSTR function returns the position of a substring in a string - The Cast function converts one datatype to another datatype

14. Big data

- Volume → Volume refers to the amount of data (Size of the data). - Velocity: → The velocity is the speed at which the data is created, stored, analyzed and visualized. - Variety: → Variety refers to the many sources and types of data. - Variability: → Data velocity may be variable and have unplanned peaks and troughs

16. Data warehouse

Within a data warehouse, data is stored in a small collection of tables: - Dimension Tables: → Often de-normalized → Contain redundant data → Easy for end users to query - Fact Tables (or Measure Tables): → Contain the data that users want to quantify, measure and aggregate • Sales • Enrolments • Visits