Question

1.

A travel agency would like to track the destination of each flight. Given the following business rule, what constraint(s) can you apply to the flight table, given there is also a destination table?

"A flight must be associated with a destination".

Question 1 options:

	Referential integrity
	Check constraint
	Nullability
	Default constraint

2.

Constraint applied to a one-to-many relationship when a referenced record (on the 1 side) is deleted, then the referencing records (on the Many side) should also be deleted.

Question 2 options:

	Set-to-Null on Delete
	Set-to-Default on Delete
	Restrict Delete
	Cascade Delete

3.

Which of the following is the most appropriate data type for a column named "State" that stores abbreviated names of the states in USA like "TN", "VA" etc.

Question 3 options:

	CHAR(3)
	VARCHAR2(2)
	VARCHAR2(3)
	CHAR(2)

4.

How can database performance be improved without changing the logical design of the database? Check all that apply.

Question 4 options:

	Combining tables that are in 1:1 relationships
	Adding indexes & views
	Horizontal Partitioning
	Denormalization
	Vertical Partitioning

5.

Which of the following statements best describe an index?

Question 5 options:

	A mechanism for splitting a table into smaller ones
	A database construct that provides direct access to specific fields in a table thus speeding up queries
	A database construct that stores the values of all the attributes in a table
	A mechanism for improving the design or structure of the database

Answer 1

1)Referential integrity

Explaination:

Here we have to apply the referential integrity to get the required result. Referential integrity is used to uniquely identify a row or a record in another table. It connects one table to another using foreign key. In the given situation, here it connects the flight table to destination table and gives us the result required by the travel agency that is destination of each flight.

2)Cascade Delete

Explaination:

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server. Using Cascade Delete we can delete the referencing records will be deleted if it is one to many relationship record.

3) CHAR(2)

Explaination:

CHAR(2) is the most appropriate data type for a column name "state" that stores abbreviated names of the states in USA like "TN", "VA" etc. as these names of states are having only letters char is enough and given state names are having 2 letters each. So that it is CHAR(2).

4)Combining tables that are in 1:1 relationships

   Adding indexes and views

   Horizontal partitioning

Explaination:

The above will improve the performance of database without changing the logical design of the database. Combinaing two tables will help us to avoid redundancy. By adding indexes and views, we can able to access data in a simple manner and it will be fast. Horizontal partitioning divides a table into multiple tables that contain the same number of columns, but fewer rows.

Why i didn't select the vertical partitioning and denormalization is becuase of these 2 we have redundancy in the tables that leads to increase the memory to store the data which decreses the performance of the database.

5)A database construct that provides direct access to specific fields in a table thus speeding up queries

Explaination:

Index is used to create a separate table that stores the address of the data in tables and that leads to get back our data quickly while retriving when we needed. It speedsup the queries that we give for the data that is required.

                      Hope the above is helpful. Please feel free to comment if any queries in the

                                 comment section. I will try to solve them as soon as possible.

                                            Do an up vote. Thank you...