Sql Server 2005 – Relational Database Design Concepts: Ramkumar Lakshminarayanan

SQL Server 2005 – Relational Database Design Concepts Ramkumar Lakshminarayanan www.rajaramsystems.com

Question Q…Explain the difference between system objects and user objects. Q…Describe normalization. Q…What is the difference between a DDL trigger and a DML trigger?

Database Basics  A client-server system is made up of two components: an application that is used to present the application's data, and a database system that is used to store it.  An application may be designed in Visual Studio 2005, J2EE, or some other graphical user interface.

 For the purpose of this course, a database is a collection of objects stored on a SQL Server.  This collection of objects includes all the tables, views, stored procedures, functions, and other objects necessary to build a database system.  If tables relate to one another, they should generally be in the same database.

System Versus User Objects  System databases include Master, Model, MSDB, Resource, TempDB, and Distribution. SQL Server creates these databases during the installation process. System objects include databases, tables, stored procedures, functions, and other types of system objects.

User Objects  System objects are part of the SQL Server system, you create user objects.  User objects include the databases, stored procedures, functions, and other database objects that you build.  You can add and drop user objects as necessary. This course is dedicated to showing you how to create and work with user objects, as well as how to administer your SQL Server.

Table Basics  Tables are generally the first thing that you add to a SQL Server database  Each table contains information about a subject. For example, one table may contain information about customers, whereas another table may contain information about orders. Each table in a database must have a unique name.  A table is made up of rows and columns.

Relational Database Design Concepts  Just as a house without a foundation will collapse, a database with poorly designed tables and relationships will fail to meet the needs of the users.

The History of Relational Database Design  Dr. E.F. Codd first introduced formal relational database design in 1969.  He based it on set theory and predicate logic. Relational theory applies to both databases and database applications.  Codd developed 12 rules that determine how well an application and its data adhere to the relational model.  12 rules are now grown further in to 100 of rules.

Rules of Relational Database Design  The Rules of Tables  Each table in a system must store data about a single entity.  Example : customers, employees, and inventory items.

 The Rules of Uniqueness and Keys  Tables are composed of rows and columns.  To adhere to the relational model, each table must contain a unique identifier.  You guarantee uniqueness in a table by designating a primary key.

 Foreign Keys and Domains  A foreign key in a table is the field that relates to the primary key in a second table.  For example, the CustomerID is the primary key in the Customers table. It is the foreign key in the Orders table.

Normalization and Normal Forms  One of the most difficult decisions that you face as a developer is what tables to create and what fields to place in each table, as well as how to relate the tables that you create.

First Normal Form  To achieve first normal form, all columns in a table must be atomic.  This means, for example, that you cannot store the first name and last name in the same field.  Another requirement for first normal form is that the table must not contain repeating values.

Second Normal Form  To achieve second normal form, all nonkey columns must be fully dependent on the primary key.  In other words, each table must store data about only one subject.

Third Normal Form  To attain third normal form, a table must meet all the requirements for first and second normal form, and all nonkey columns must be mutually independent.  This means that you must eliminate any calculations, and you must break out data into lookup tables.

Integrity Rules  The two types of overall integrity rules are  referential integrity rules and  entity integrity rules

Referential Integrity  Child rows cannot be added for parent rows that do not exist.  In other words, an order cannot be added for a nonexistent customer.

Referential Integrity  A primary key value cannot be modified if the value is used as a foreign key in a child table.  This means that a CustomerID cannot be changed if the orders table contains rows with that CustomerID.

Referential Integrity  A parent row cannot be deleted if child rows are found with that foreign key value.  For example, a customer cannot be deleted if the customer has orders in the orders table.

Entity Integrity  Entity integrity dictates that the primary key value cannot be null.  This rule applies not only to singlecolumn primary keys, but also to multicolumn primary keys. In fact, in a multicolumn primary key, no field in the primary key can be null.  SQL Server does not allow a field in a primary key to be null.

Database-Specific Rules  Database-specific rules are as important as overall integrity rules.  They ensure that the user enters only valid data into a database.  An example of a database-specific integrity rule is that the delivery date for an order must fall after the order date.

Database Diagram Basics  Database diagram graphically shows the structure of the database.  It shows how one table is related to another within the database.  Using a database diagram, you can modify the tables, columns, relationships, keys, indexes, and constraints that make up the database.

Database Diagram

View  A view is a virtual table.  Its contents are based on a query.  Like a table, a view is composed of rows and columns

Stored Procedure Basics  A stored procedure is a piece of programming code that can accept input parameters and can return one or more output parameters to the calling procedure or batch  Stored procedures generally perform operations on the database, including the process of calling other stored procedures.

User-Defined Function Basics  User-defined functions are procedures that accept parameters, perform some sort of action, and return the result of that action as a value.  The return value can be either a single scalar value or a result set.  A single scalar value is a simple value such as a number.  A result set refers to something such as an ADO.NET recordset.

Trigger Basics  A trigger is a special type of stored procedure that executes when a language event executes.  There are two kinds of triggers:  DML triggers and DDL triggers.

DDL Triggers  Microsoft introduced DDL triggers with SQL Server 2005.  They execute when changes are made to the structure of the database.  An example is when someone tries to remove a field from a table. You can write a DDL trigger that prevents the removal of the field if the field contains any data.

DML Triggers  DML triggers are invoked when a data manipulation language (DML) event takes place in the database.  DML events include INSERT, UPDATE, and DELETE statements that occur when data is modified in a table or view.

SQL Server Stored Procedures Versus Transact-SQL Statements Stored on Local Computers

 If you need to modify a stored procedure, you modify it on the server. The changes that you make impact all the clients that call the stored procedure.  If you make a change to your business logic, you must modify your application code everywhere that it appears.

Summary  A database is similar to a house. Without the proper foundation it will fall apart.  In this hour you learned the basics of relational database design. You reviewed the various system objects, getting a sense of what each object does along the way.  Finally, we talked about the difference between SQL Server stored procedures and Transact-SQL stored on a user's machine.

Answer 1  SQL Server creates system objects during the installation process. They are part of the system, and most of them are necessary for SQL Server to function properly. Whereas system objects are part of the SQL Server system, you create user objects. User objects include the databases, stored procedures, functions, and other system objects that you build.

Answer2  Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive level of normal form achieves a better database design than the previous one. It is generally sufficient to apply the first three levels of normal forms to your data.

Answer3  A DDL trigger executes in response to a change to the structure of a database (for example, CREATE, ALTER, DROP). A DML trigger executes in response to a change in data (INSERT, UPDATE, DELETE).

Quiz  What is a foreign key?  Name two things that can be returned by a user-defined function.  Name the SQL Server 2005 system databases.  Name two types of overall integrity rules.

Answer  A foreign key in a table is the field that relates to the primary key in a second table.  A single scalar value or a result set.  Master, Model, MSDB, Resource, TempDB, and Distribution.  Referential integrity and entity integrity.

Activities  Practice designing a system. Begin with a real-world scenario such as a Hospital Management system. Determine all the tables necessary for the system and what each table will do. Make sure that the tables meet the rules of first, second, and third normal forms. Finally determine the relationships between the tables in the database.