Figure 2.4 E-R diagram of relationships between tables. Data Definition Language Now that we have outlined the basic foundation of SQL, let s write some code to implement our database. The formal name for the language components used to create tables is Data Definition Language, or DDL. The DDL is also used to drop tables and perform a variety of other functions, such as adding and deleting rows (entries) from a table, and adding and deleting columns from a table. I ll show you some of these along the way. Declaring Domains One of the handy shortcuts that the DDL offers is a way to create predefined data objects. Though we haven t really talked about the data types available in SQL, you can probably guess the common ones like integer, character, decimal (floating point), date, etc. Domains allow you to declare a data type of specific length and then give the declared type a name. This can come in handy if you have numerous data columns that are of the same data type and characteristics. Here s the SQL statement you use to declare a domain: CREATE DOMAIN EMP_NUMBER AS CHAR(5) Tip: Smart domain declaration habits. When you are actually creating or altering tables, this domain can be used instead of specifying CHAR(20) each time. There are a number of reasons why this is good practice. Notice that we chose to make EMP_NUMBER a domain. This is a column that appears in several tables. If we mistakenly use the wrong type or length in one of the table definitions where we have employee numbers, it could cause havoc when running SQL queries. You ll have to keep reading to find out the other reason. Performing Checks Predefining a data object is also useful for making sure that a certain entry in a column matches the data we expect to find there. For example, our empno field should contain a number. If it doesn t, performing a check of that data will alert us to the error. These checks can exist in the actual table definition, but it s efficient to localize a check in a domain. Hence, we can add a check to our employee number domain: CREATE DOMAIN EMP_NUMBER AS CHAR(5) CHECK (VALUE IS NOT NULL);
Pay us little and get a lot! We will give you the cheapest web hosting available, trust us and check cheapest web hosting services.