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

Question 1

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

Answer 1

True

Question 2

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

Answer 2

Primary key
Foreign Key
Not Null
Unique
Default
Check
Create Index

Question 3

What is a foreign key?

Answer 3

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

Question 4

Syntax: Create a table with a foreign key using alter

Answer 4

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);

Question 5

Define referential integrity?

Answer 5

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.

Question 6

Syntax: table with foreign key

Answer 6

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
);

Question 7

Describe the use of an alias in a database

Answer 7

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.

Question 8

Advantages of Alias

Answer 8

• 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.

Question 9

Syntax to create alias for column

Answer 9

SELECT column_name AS alias_name FROM table_name

Question 10

Syntax to create alias for table

Answer 10

SELECT column_name1, column_name2 FROM table_name AS alias_name

Question 11

To define and implement MULTIPLICITY

Answer 11

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.

Question 12

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

Answer 12

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

Question 13

define Normalization

Answer 13

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.

Question 14

First Normal Form:

Answer 14

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.

Question 15

Second Normal Form:

Answer 15

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.

Question 16

Third Normal Form:

Answer 16

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.

Question 17

Describe what is an INNER JOIN

Answer 17

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.

Question 18

Syntax: Create an inner join

Answer 18

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

Question 19

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

Answer 19

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

Question 20

What is a UNION query

Answer 20

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

Question 21

Difference between a union query and an inner join

Answer 21

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.

Question 22

Describe what is an OUTER JOIN

Answer 22

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

Question 23

OUTER JOIN is of two types:

Answer 23

LEFT JOIN and RIGHT JOIN.

Question 24

Syntax for Left Outer Join

Answer 24

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

Question 25

Syntax for Right Outer Join

Answer 25

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

Question 26

Describe the use of SELF JOIN in a database

Answer 26

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.

Question 27

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

Answer 27

compare the records within the same table.

Question 28

Syntax for Self Join

Answer 28

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

Question 29

LEFT JOIN returns all records

Answer 29

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

Question 30

RIGHT JOIN returns all records

Answer 30

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

Question 31

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

Answer 31

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.

Question 32

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

Answer 32

DROP

Question 33

Syntax: Create view statement

Answer 33

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

Question 34

Representational State Transfer (REST) is

Answer 34

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

Question 35

Representational State Transfer (REST) is

Answer 35

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.

Question 36

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

Answer 36

uses only HTTP requests.

Question 37

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

Answer 37

True

Question 38

Difference between REST API and JSON

Answer 38

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.

Question 39

In REST, the primary data representation is called

Answer 39

resource

Question 40

API stands for

Answer 40

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

Question 41

How do RESTful APIs perform actions on objects?

Answer 41

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.