CS 3550

Question 1

Differences between Conceptual, Logical, and Physical design

Answer 1

The conceptual model is to establish the entities, their attributes, and their relationships. The logical data model defines the structure of the data elements and set the relationships between them. The physical Data Model describes the database-specific implementation of the data model.

Question 2

Normalization

Answer 2

Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity

Question 3

Top down vs Bottom up database design

Answer 3

The top-down method starts from the general and moves to the specific. Basically, you start with a general idea of what is needed for the system and then ask the end-users what data they need to store. … The bottom-up approach begins with the specific details and moves up to the general.

Question 4

Collation

Answer 4

In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the database.

Question 5

Index

Answer 5

An index is a database structure that you can use to improve the performance of database activity. A database table can have one or more indexes associated with it. An index is defined by a field expression that you specify when you create the index.

Question 6

Stored procedure

Answer 6

is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data dictionary.

Question 7

Stored procedure returns

Answer 7

it returns an integer value and this value indicates the execution status of the stored procedure. … The second result set displays the stored procedure return value

Question 8

difference between user defined function and stored procedure

Answer 8

The user defined function will only allow input of parameters but does not support output of the same parameters. Stored procedure, on the contrary, supports both input and output parameters. The UDF also does not allow for the use of try-catch blocks.

Question 9

Scalar vs table return

Answer 9

User-defined Scalar Functions (SFs) return a single scalar data value of the type defined in the RETURNS clause. … Inline Table-valued functions (ITVFs) have no function body; the scalar value that is returned is the result of a single statement without a BEGIN

Question 10

Triggers

Answer 10

A SQL trigger is a database object which fires when an event occurs in a database. We can execute a SQL query that will “do something” in a database when a change occurs on a database table such as a record is inserted or updated or deleted

Question 11

Trigger vs check contraints

Answer 11

Check constraints are fired with each row. Triggers can be written so all inserted rows are checked as a single batch and that is often faster. Also, a trigger can be written so it is only fired if some column(s) of interest are set/modified

Question 12

Cursor

Answer 12

A SQL cursor is a database object that retrieves data from result sets one row at a time. The cursor in SQL can be used when the data needs to be updated row by row.

Question 13

How should NoSQL be used?

Answer 13

Fast-paced Agile development.
Storage of structured and semi-structured data.
Huge volumes of data.
Requirements for scale-out architecture.
Modern application paradigms like microservices and real-time streaming.