T-SQL Fundamentals Chapter 1

In Itzik Ben-Gan's book, Microsoft® SQL Server® 2008 T-SQL Fundamentals, Chapter 1 teaches some good basics for understanding database architecture:

1. SQL Server can contain multiple databases. The setup program creates several system databases that hold system data serving internal purposes. After installation, you can create user databases that hold application data. (p. 14)

2. Each database must have at least one data file and at least one log file (the default in SQL Server). The data files hold object data, and the log files hold information that SQL Server needs to maintain transactions. (p. 16)

3. SQL Server can write to multiple data files in parallel. It can only write to one log file at a time, sequentially. (p. 16)

4. A database contains schemas, and schemas contain objects, such as tables, views, stored procedures, and more. (p. 17)

5. You can control permissions at the schema level. (p. 18)

6. The schema is a namespace. It is used as a prefix to the object name. For example, if you have a table called Orders in a schema called Sales, the schema-qualified object name (also known as the two-part object name) is Sales.Orders. (p. 18)

7. Remember that tables reside within schemas, and schemas reside within databases. As an example, create a database called testdb and a schema called dbo by using the following code:

IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;

This code is asking if a database called testdb does not exist, this code creates a one. The DB_ID function accepts a database name as input and returns its internal database ID. If a database with the input name does not exist, the function returns a NULL. This is a simple way to check whether a database exists. (p. 18)

Create TestDB

8. The schema called dbo is created automatically in every database and is also used as the default schema for users that were not associated explicitly with another schema. (p. 18)

9. The following code create a table called Employees in the testdb database:

USE testdb;

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
DROP TABLE dob.Employees;

CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary MONEY NOT NULL
);

This function returns the internal object ID if an object with the given input name and type exists, and NULL otherwise. If the function returns a NULL, you known that the object doesn't exist. The code drops the table if it already exists, and then creates a new one.

The USE statement changes the current database context to that of testdb.

The CREATE TABLE statement specifies the name of the table, and in parentheses, the definition of its attributes (columns).

For each attribute you specify the attribute name, datatype, and NULLability. (p. 19)

Create Tables and Attributes

10. ANSI dictates that when a column NULLability is not specified, the assumption should be NULL (allowing NULLs). It is recommended defining a column as NOT NULL unless you have a compelling reason to support NULLs. (p. 20)

11. The practice of using a semicolon to terminate statements is standard and is also a requirement in several other database platforms. (p. 20)

12. Data integrity that is enforced as part of the model, namely as part of the table definitions, is considered declarative data integrity. Data integrity that is enforced with code, such as with stored procedures or triggers, is considered procedural data integrity. (p. 20)

13. A primary key constraint enforces uniqueness of rows and also disallows NULLs in the constraint attributes. Each table can have only one primary key.

ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees
PRIMARY KEY(empid);

(p. 21)

Add Primary Key

14. A unique constraint enforces uniqueness of rows, allowing you to implement the concept of alternate keys from the relationship model in your database. Unlike primary keys, multiple unique constraints can be defined in the same table. Also, a unique constraint is not restricted to columns defined as NOT NULL.

The following code defines a unique constraint on the ssn column in the Employees table:

ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn
UNIQUE(ssn);

(p. 21)

Add Unique Constraint

15. A foreign key enforces referential integrity. This constraint is defined on a set of attributes in what's called the referencing table, and points to a set of candidate key (primary key or unique constraint) attributes in what's called the referenced table. Note that the referencing and referenced tables can be one and the same. The foreign key's purpose is to restrict the domain of values allowed in the foreign key columns to those that exist in the referenced columns.

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL,
orderts DATETIME NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders
PRIMARY KEY(OrderID)
);

(p. 22)

Create Table Orders

16. Say you want to enforce an integrity rule that restricts the domain of values supported by the empid column in the Orders tables to the values that exist in the empid column in the Employees table. You can achieve this by defining a foreign key constraint on the empid column in the Orders table pointing to the empid column in the Employees table like so:

ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);

(p. 22)

Foreign Key on EMPID Attribute

17. Similarly, if you want to restrict the domain of values supported by the mgrid column in the Employees table to the values that exist in the empid column of the same table, you can do so by adding the following foreign key:

ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid)
REFERENCES Employees(empid);

Note that NULLs are allowed in the foreign key columns (mgrid in this example) even if there are no NULLs in the referenced candidate key columns.
(p. 22)

Foreign Key on MGRID Attribute

18. The preceding two examples are basic definitions of foreign keys that enforce a referential action called no action. No action means that attempts to delete rows from the referenced table or update the referenced candidate key attributes will be rejected if related rows exists in the referencing table. (p. 23)

19. A check constraint allows you to define a predicate that a row must meet to enter the table or be modified. For example, the following check constraint ensures that the salary column in the Employees table will support only positive values:

ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0);

An attempt to insert or update a row with a nonpositive salary value will be rejected by the RDBMS. Note that a check constraint rejects an attempt to insert or update a row when the predicate evaluates to FALSE. The modification will be accepted when the predicate evaluates to either TRUE or UNKNOWN. For example, salary -1000 will be rejected, while salaries 50000 and NULL will both be accepted. (p. 23)

Add Check Constraint

20. A default constraint is associated with a particular attribute. It is an expression that is used as the default value when an explicit value is not specified for the attribute when you insert a row. For example, the following code defines a default constraint for the orderts attribute (representing the order's timestamp):

ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_orderts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

The default expression invokes the CURRENT_TIMESTAMP function, which returns the current date and time value. Once this default expression is defined, whenever you insert a row in the Orders table and do not explicitly specify a value in the orderts attribute, SQL Server will set the attribute value to CURRENT_TIMESTAMP. (p. 23-24)

Add Default Constraint

No Comments Yet.

Leave a comment