1 / 20

Domains

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.  .

emory
Télécharger la présentation

Domains

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Domains See scm-intranet

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

More Related