SQL: The Utmost Intermediate Course Guide In Fundamentals And Concept Of SQL Programming by Webber Zach

Author:Webber, Zach [Webber, Zach]
Language: eng
Format: azw3
Published: 2018-06-11T16:00:00+00:00

Chapter 4: Defining Data – Data Definition Language

Defining databases requires using T-SQL statements relating to data definition language. Operations related to creation, modification, and deletion are three segments of data definition language that need attention regarding database creation. The organization of a database comprises various objects. Objects may be physical or logical and respectively they relate to the organization of data on the physical device or the user's view of tables, columns, or databases. In seemingly redundant fashion, a database is part of the organization of a database. That means that the database itself is an object capable of manipulation through T-SQL statements. You cannot have a database without creating a database object first. The two primary methods for creating this first object are, using the statement CREATE DATABASE, or by using SQL management studio if using the Microsoft product SQL Server. Oracle functions slightly differently but is reasonably straightforward. Naming the database provides the database engine with an identifier to call the database in a query, and so the name must follow the same restrictions that all identifiers share. Creating a database defines the creator as the database owner, and also creates a log file that tracks each change made to a database from its creation onward. Even if logging is not correctly defined, the system will still generate a log file to maintain changes for the benefit of the database engine's logic. In a previous section, a discussion of data independence specified that the functionality of a database is not dependent on physical or logical structure. It is vital to impart that the function is not affected by how data is stored and accessed on the physical disk. Physical data organization does affect performance when the database is extensive. In such cases, established methodologies exist for creating filegroups of related databases for distribution among various physical disks to enhance performance. Database creation allows for templates called a model. Model usage occurs when a specific database object or element needs to be present in every database created based on administrator specifications. SQL databases also allow for snapshot views of a database at a given interval-in-time. Snapshots are useful for protecting a database against user error as well as understanding database evolution.

Primary elements of any database are the tables it contains. Tables require consistent data integrity to maintain referential stability. Administrators can manage consistency, but the database engine provides tools to create limitations on the entry of inconsistent data. Database engine applied consistency is a vital feature since administrators are susceptible to human error while the database engine is immune to the limitations of human error. Restrictions can be turned over to the database management system on a per column basis or table wide, which covers multiple columns. Examples of column or table restrictions include requiring unique clauses to identify candidate keys, and one of those potential columns is eligible to become the primary key. The primary key is a column where each row of the column contains a different value.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.