Chapter 1 - Intro to Relational Databases and SQL

Question 1

What is a stored procedure?

Answer 1

A set of one or more SQL statements that are stored together in a database.

A user will create a stored procedure by using the CREATE PROCEDURE statement, name the procedure then provide the SQL code telling the database what to do. To execute this program later, the user will utilize the EXEC statement.

Question 2

What is a trigger?

Answer 2

A special type of stored procedure that’s automatically when an insert, update, or delete operation is executed on a table or when a DDL statement is executed on a database.
These are used most often to validate data before a row is added or updated, but can also be used to maintain the relationships bt tables or to provide info about changes to the definition of a database.

Question 3

What two data access models can be used to work with data in a SQL server?

Answer 3

1) For a .NET application, which can be written in Visual Basic, C#, C++, or F#, that model is typically ADO.NET.
2) For a Java application, that model is typically JDBC (Java Database Connectivity). Unlike ADO.NET, JDBC requires additional software, called a driver to communicate with SQL server.

Question 4

What is Disconnected data architecture?

Answer 4

Architecture used by ADO.NET in which data retrieved from a database can be accessed even after the connection is closed.

ADO.NET accomplishes this by utilizing a DataSet, which is an in-memory data store that can hold multiple tables at the same time.

Question 5

What are the basic ADO.NET objects in a .NET application used to retrieve data? What is the flow among these objects?

Answer 5

To retrieve data from a database you:

1) execute a command object that contains a SELECT statement
2) The command object uses a connection object to connect to the database and retrieve the data
3) You read the results one row at a time using the data reader object

Question 6

What is a data provider?

Answer 6

Provides the classes that let you create the objects that you use to retrieve data from a database and to store data to a database.

Question 7

What are the three hardware components of a Client/Server system?

Answer 7

Client – Web browser
Server – SQL Server 2012 database engine
Network – TCP/IP

Question 8

What is LAN?

Answer 8

Local area connection, a simple client and server system

Question 9

What is WAN?

Answer 9

Wide area network, two or more LANs connected as part of a larger network

Question 10

What is an enterprise system?

Answer 10

CIient/server system and mainframe system in dispersed geographical locations

Question 11

How does an application communicate with the database management system (DBMS)?

Answer 11

The application software communicates with the DBMS by sending SQL queries through the data access API.

Question 12

In a file handling system, all the processing is done on what hardware component?

Answer 12

In a file handling system, all processing is done on the clients

Question 13

How do web applications work? Describe the flow from web browser on a client.

Answer 13

• A web browser on a client sends a request to a web server.
• The web server processes the request.
• The web server passes any requests for data to the database server.
• The database server returns the results to the web server.
• The web server returns a response to the browser.

Question 14

What is the difference b/t a column and a row?

Answer 14

Column – represents some attribute of the entity

Row – contains a set of values for a single instance of the entity

Question 15

What is a composite primary key?

Answer 15

A primary key consists of two or more columns

Question 16

What is the difference b/t a primary key and a non-primary key?

Answer 16

Primary key – one or more columns that uniquely identify each row in the table (only one per table allowed. No nulls allowed)
Non-primary key (unique key) – makes each row unique. Can have many per table. Nulls allowed.

Question 17

What is an index? What does it do?

Answer 17

Provides an efficient way of accessing the rows in a table based on the values in one or more columns.

Question 18

What is a foreign key?

Answer 18

One or more columns in a table that refer to a primary key in another table.

Question 19

Describe a one-to-many relationship?

Answer 19

e.g. Department vs Employee. Each row in a table relates to one or more rows in another table.

Question 20

Describe a one-to-one relationship?

Answer 20

e.g. PC vs motherboard. If a table has a one-to-one relationship with another table, the data in the two tables could be stored in a single table.

Question 21

What is a many-to-many relationship?

Answer 21

e.g. students vs courses. It usually can be broken down into two one-to-many relationships.

Question 22

What is an identity column?

Answer 22

An identity column is a numeric column whose value is generated automatically when a row is added to the table.

Question 23

What are DLM statements?

Answer 23

DML (data manipulation language) Statements
SELECT
INSERT
UPDATE
DELETE

Question 24

What are DDL statements?

Answer 24

DDL (data definition language) Statements
CREATE DATABASE, TABLE, INDEX
ALTER TABLE, INDEX
DROP DATABASE, TABLE, INDEX