240 likes | 410 Views
Domains. Objectives. To understand the idea of a domain. To understand the idea of a domain constraint in terms of integrity constraints. To develop a means of expressing domain constraints. .
E N D
Domains See scm-intranet
Objectives • To understand the idea of a domain. • To understand the idea of a domain constraint in terms of integrity constraints. • To develop a means of expressing domain constraints. See scm-intranet
Domains represent the set of valid values from which an attribute, or set of attributes, can derive values. • These domains must be defined. • Data types alone are clearly insufficient e.g. an order number is more than simply a string of characters or an integer. See scm-intranet
Sets • Domains are sets. • Sets can only be defined • by enumeration (listing every value i.e. every member of the set) • or by producing a predicate or rule which defines a valid member of the set. See scm-intranet
Definition • The following outlines the definition of domains in SQL-92. • The mechanisms available for definition, and capabilities vary from system to system See scm-intranet
Enumeration • Enumeration can written informally thus- attribute: enumerated set x : [0..1] y: [a..z] d: [U00..U99] colour: [red,yellow,green] See scm-intranet
Predicates Predicates can be defined in many ways including- • English narrative e.g. an order date must be less than a delivery date. • Masks e.g. reg_no: (ch1)(d)(d)(d)(space)(ch2(ch2)(ch2) Where ch1:[a,b,c,d,e,f,g,h,j,k,l,m,n,p,q,r,s,t,u,v,w,x,y,z] ch2:(a..z) d:[0..9] See scm-intranet
Expressions e.g. order_date LT delivery_date • set operations e.g. shipping_post_code : select post_code from valid_post_code (assumes table of valid post code pre-exists). See scm-intranet
Implementation • Ideally, domains should be specified in SQL but this is not always possible, especially if using SQL-89. • However, domains still need to be defined in some manner as they are form important integrity constraints-in addition to primary keys, referential integrity etc.. See scm-intranet
Every domain will have a data type as a minimum part of the definition. • e.g. char, varchar, date, money, integer, decimal etc... See scm-intranet
Data Types and Valid Operations • Care needs to be taken with data types. Selection of a data type implies a selection of valid operations e.g. selecting integer as a data type for a domain or attribute implies that integer arithmetic is valid for that domain/attribute. So, if house number was defined as an integer addition of house numbers would be a valid operation. What does it mean to add two house numbers together?? See scm-intranet
Example Domain Definitions in SQL-92 Create Domain e.g. A domain is defined thus- createdomain title_type varchar(35) check (value is not null); This definition can then be used in a SQL schema thus- createtable movie_title (title title_type,.........); See scm-intranet
e.g. createdomain revenue as decimal(9,2) constraint price_not_negative check (value>=0) notdeferrable; -here the domain constraint has been given a name i.e. price_not_negative See scm-intranet
Using Check constraints • N.B. There is no CREATE DOMAIN command in SQL*Server so check constraints must be used. e.g createtable movie_title (our_cost decimal(9,2) check (our_cost<100.00),........); e.g. createtable movie_title (our_cost decimal(9,2) check (our_cost<100.00 and our_cost>0),........); See scm-intranet
Other examples of Check clauses e.g. check (current_sale_price<=(select (max_price) from competition_prices)) e.g. check ((our_cost between .99 and 100.00) and (regular_rental_price between........) and (rental_date ........)........); See scm-intranet
Check Constraints and Enumeration e.g. Enumeration check movie_type in ('horror','western'........); check (answer_one, answer_two) invalues('no','no'),('yes','no'); See scm-intranet
Constraint Names • Constraints can be defined and given a name. They can then be referred to in any table using this name. e.g. constraint check_movie_type check (movie_type in..........)); createtable movie (movie_type mov_type check_movie_type,…..); the order of items in brackets is – Attribute domain constraint See scm-intranet
Identity Property (AutoNumber) CREATE TABLE new_employees ( id_num int IDENTITY(1,1), fname varchar (20), minit char(1), lname varchar(30) ) Syntax IDENTITY [ (seed ,increment) ] seed Is the value that is used for the very first row loaded into the table. increment Is the incremental value that is added to the identity value of the previous row that was loaded. If neither is specified, the default is (1,1). If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. See scm-intranet
Deferment • Constraints can be deferrable or not deferrable • Default is not deferrable • deferrable- means checking deferred until transaction commited. • not deferrable- means check applied at end of every sql statement. See scm-intranet
Summary • Domains are an important integrity constraint • Data types are not sufficient • Schema level domain implementation removes the need for checking in application programs • This can lead to reduced maintenance costs and reduced threat to database integrity See scm-intranet