Week 6 - More SQL & Rest - Referential Integrity with SQL, Intro to REST Flashcards

1
Q

SQL data constraints are used to restrict the values of data which are inserted into a table. T/F?

A

True

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

Constraints help define the integrity constraints of the database schema. Common constraints include

A

Primary key
Foreign Key
Not Null
Unique
Default
Check
Create Index

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

What is a foreign key?

A

is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

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

Syntax: Create a table with a foreign key using alter

A

CREATE TABLE Branch(
branch_id INT PRIMARY KEY,
branch_name VARCHAR(20)
);
ALTER TABLE employee ADD branch_id INT;
ALTER TABLE employee ADD FOREIGN KEY (branch_id) REFERENCES Branch(branch_id);

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

Define referential integrity?

A

is the relationship between tables. Each table in a database has a primary key, this primary key can appear in other tables because of its relationship to data within other tables. When a primary key from one table appears in another table, it is called a foreign key.

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

Syntax: table with foreign key

A

CREATE TABLE InternationalStudent(
country_id INT PRIMARY KEY,
name VARCHAR(20),
student_id INT,
FOREIGN KEY(student_id) REFERENCES Student(student_id)
ON DELETE CASCADE
);

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

Describe the use of an alias in a database

A

Aliases are used to give a temporary name to a table or a column in a table with the intention to support a specific query.

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

Advantages of Alias

A
  • It provides a very useful feature that allows us to achieve complex tasks quickly.
  • It makes the column or table name more readable.
  • It allows us to combine two or more columns
  • It makes the table more user-friendly.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Syntax to create alias for column

A

SELECT column_name AS alias_name FROM table_name

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

Syntax to create alias for table

A

SELECT column_name1, column_name2 FROM table_name AS alias_name

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

To define and implement MULTIPLICITY

A

Multiplicity can be clasified as four types:

one to one relationship
one to many relationship
many to one relationship
many to many relationship
Multiple instances of the same or multiple entities can be associated with one or many instances.

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

The Multiplicity is a constraint on the cardinality, which shall:

A

not be less than the lower bound
and not greater than the upper bound specified

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

define Normalization

A

Normalization is the process of efficiently organizing data in a database. The two main objectives of normalization are, eliminate redundant data ie to make sure that the same data is not stored twice, and to ensure data dependencies make sense, ie to store only relational data in the table. Both of these are important because they reduce the amount of space in a database and ensure that data is logically stored.

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

First Normal Form:

A

A relation is in 1NF if all its attributes have an atomic value.

The first normal form (1NF) is conclusive of a relational database. If we are to consider a database relational, then all relations in the database are in 1NF.

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

Second Normal Form:

A

A relation is in 2NF if it is in 1NF and all non-key attributes are fully functionaly dependent on the candidate key

Second normal form (2NF) deals with the elimination of circular dependencies from a relation. We say a relation is in 2NF if it is in 1NF and if every non-key attribute is completely dependent only on the Primary Key

A non-key attribute is any column that can not be used to uniquely identify the table.

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

Third Normal Form:

A

A relation is in 3NF if it is in 2NF and there is no transitive dependency.

Third Normal Form: Third normal form (3NF) deals with the elimination of non-key attributes that do not describe the Primary Key.For a relation to be in 3NF, the relationship between any two non key attributes, or groups of non-key attributes, must not be in a one to one relation.

The attributes should be mutually independent which means, none of the attributes should be functionally dependent on any combination of attributes. This mutual independence makes sure that any update on the individual attribute will not affect other attributes in a row.

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

Describe what is an INNER JOIN

A

selects all rows from both tables as long as there is a match between the columns. If there are data in the “EMP1” table that do not have matches in “EMP2”, these orders will not be shown.

restricts records retrieval from Table1 and Table2 to those that satisfy the join requirement.

18
Q

Syntax: Create an inner join

A

SELECT select_list FROM t1 INNER JOIN t2 ON join_condition1 INNER JOIN t3 ON join_condition2 …;

19
Q

What is the output of the following SQL statement? SELECT Emp.Emp_ID, First_name, Last_name FROM Emp e1, Emp2 e2 WHERE e1.Emp_ID = e2.Emp_ID

A

Error - If Aliasing is used, it must be used throughout

20
Q

What is a UNION query

A

Merges the output from multiple queries and must include the same number of columns

21
Q

Difference between a union query and an inner join

A

A union query combines the results of two or more SELECT statements into a single result set. The SELECT statements can have different column names, but the data types of the corresponding columns should be the same. The union operation eliminates duplicate rows from the result set.

On the other hand, an inner join is a type of JOIN operation that combines rows from two or more tables based on a related column between them. The result set of an inner join only includes rows that have matching values in both tables.

So, while both union and inner join can combine data from multiple tables, they do so in different ways and have different purposes.

22
Q

Describe what is an OUTER JOIN

A

yields non-matching records as well as matching records.
If rows in a connected table don’t match, the NULL values will be shown.

23
Q

OUTER JOIN is of two types:

A

LEFT JOIN and RIGHT JOIN.

24
Q

Syntax for Left Outer Join

A

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

25
Q

Syntax for Right Outer Join

A

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

26
Q

Describe the use of SELF JOIN in a database

A

SELF JOIN is an SQL statement which is used to intersect or join a table in the database to itself.
It’s like aregular join that allows to combine data withing a table based on a certain conditional expression. This type of joining of data with other data in the same table when a condition is matched is also called as Unary relationship.

27
Q

The SELF JOIN is a different kind of join that is useful to

A

compare the records within the same table.

28
Q

Syntax for Self Join

A

SELECT A.Column_Name, B.Column_Name,…. FROM TableA T1, TableA T2 WHERE A.Common_Field = B.Common_Field;

29
Q

LEFT JOIN returns all records

A

from the left table, and the records that match the condition from the right table.

30
Q

RIGHT JOIN returns all records

A

from the right table, and the records that match the condition from the left table.

31
Q

Views, which are a type of virtual tables allow users to do the following −

A

Structure data in a way that users or classes of users find natural or intuitive.

Restrict access to the data in such a way that a user can see and manipulate exactly what they need.

Summarize data from various tables which can be used to generate documents and reports.

32
Q

A view can be deleted using which of the following command?

A

DROP

33
Q

Syntax: Create view statement

A

CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition;

34
Q

Representational State Transfer (REST) is

A

an architectural style that defines a set of constraints to be used for creating web services.

35
Q

Representational State Transfer (REST) is

A

an architectural style that defines a set of constraints to be used for creating web services.

It uses the HTTP protocol and its methods, such as GET, POST, PUT, and DELETE, to transfer data between a client and a server. RESTful APIs (Application Programming Interfaces) are built using REST principles, and they allow developers to create interfaces for accessing data and services over the web.

36
Q

REST is used to fetch or give some information from a web service. All communication done via REST API

A

uses only HTTP requests.

37
Q

REST is not constrained to XML and can return XML, JSON, YAML or any other format depending on what the client requests. T/F?

A

True

38
Q

Difference between REST API and JSON

A

The main difference between REST and JSON is that REST is an architectural style, while JSON is a data format. RESTful APIs can use a variety of data formats, including XML, CSV, and JSON. JSON is often used with RESTful APIs because it is lightweight, easy to parse, and widely supported by programming languages and platforms.

In summary, REST is a set of principles for building web services, while JSON is a data format that can be used with RESTful APIs (as well as other types of APIs). RESTful APIs can use a variety of data formats, but JSON is often used because of its simplicity and compatibility with many programming languages and platforms.

39
Q

In REST, the primary data representation is called

A

resource

40
Q

API stands for

A

Application Programming Interface
It is a medium that allows different applications to communicate programmatically with one another and return a response in real time

41
Q

How do RESTful APIs perform actions on objects?

A

RESTful APIs perform actions on objects by sending HTTP requests with appropriate verbs (such as GET, POST, PUT, PATCH, DELETE) to the API’s endpoints.