What is a stored procedure?
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.
What is a trigger?
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.
What two data access models can be used to work with data in a SQL server?
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.
What is Disconnected data architecture?
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.
What are the basic ADO.NET objects in a .NET application used to retrieve data? What is the flow among these objects?
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
What is a data provider?
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.
What are the three hardware components of a Client/Server system?
Client – Web browser
Server – SQL Server 2012 database engine
Network – TCP/IP
What is LAN?
Local area connection, a simple client and server system
What is WAN?
Wide area network, two or more LANs connected as part of a larger network
What is an enterprise system?
CIient/server system and mainframe system in dispersed geographical locations
How does an application communicate with the database management system (DBMS)?
The application software communicates with the DBMS by sending SQL queries through the data access API.
In a file handling system, all the processing is done on what hardware component?
In a file handling system, all processing is done on the clients
How do web applications work? Describe the flow from web browser on a client.
- 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.
What is the difference b/t a column and a row?
Column – represents some attribute of the entity
Row – contains a set of values for a single instance of the entity
What is a composite primary key?
A primary key consists of two or more columns
What is the difference b/t a primary key and a non-primary key?
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.
What is an index? What does it do?
Provides an efficient way of accessing the rows in a table based on the values in one or more columns.
What is a foreign key?
One or more columns in a table that refer to a primary key in another table.
Describe a one-to-many relationship?
e.g. Department vs Employee. Each row in a table relates to one or more rows in another table.
Describe a one-to-one relationship?
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.
What is a many-to-many relationship?
e.g. students vs courses. It usually can be broken down into two one-to-many relationships.
What is an identity column?
An identity column is a numeric column whose value is generated automatically when a row is added to the table.
What are DLM statements?
DML (data manipulation language) Statements SELECT INSERT UPDATE DELETE
What are DDL statements?
DDL (data definition language) Statements
CREATE DATABASE, TABLE, INDEX
ALTER TABLE, INDEX
DROP DATABASE, TABLE, INDEX