Sunday, October 25, 2009

Parts of a Database

A database consists of a collection of tables that stores a specific set of structured data. A table contains a collection of rows (referred to as records or tuples) and columns (referred to as attributes). Each column in the table is designed to store a certain type of information (for example, dates, names, dollar amounts, or numbers). Tables have several types of controls (constraints, rules, triggers, defaults, and customized user data types) that ensure the validity of the data. Tables can have indexes similar to those in books that allow rows to be found quickly. Declarative referential integrity (DRI) constraints can be added to the tables to ensure that interrelated data in different tables remains consistent. A database can also store procedures that use Transact-SQL programming code to perform operations with the data in the database, such as storing views that provide customized access to table data.For example, you create a database named MyCoDB to manage the data in your company. In the MyCoDb database, you create a table named Employees to store information about each employee, and the table contains columns named EmpId, LastName, FirstName, Dept, and Title. To ensure that no two employees share the same EmpId and that the Dept column contains only valid numbers for the departments in your company, you must add constraints to the table. Because you want to be able to quickly find the data for an employee, based on the employee ID or last name, you define indexes. You will have to add a row of data to the Employees table for each employee, so you create a procedure named AddEmployee, which is customized to accept the data values for a new employee and performs the operation of adding the row to the Employees table. You may need a departmental summary of employees, in which case you define a view called DeptEmps that combines data from the Departments and Employees tables and produces the output. This illustration shows the parts of the MyCoDB that is created.

No comments:

Post a Comment