Chapter42

Chapter42 - CSIS0278A Introduction to Database Management...

Info iconThis preview shows pages 1–9. Sign up to view the full content.

View Full Document Right Arrow Icon
Click to edit Master subtitle style CSIS0278A Introduction to Database Management Systems Lecture 4: SQL Part 2 Dr. Reynold Cheng Based on the Ch. 4 notes of “Database System Concepts” by A. Silberschatz, H. Korth & S. Sudarshan and notes by Dr. Ho Wai Shing
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2DB045:2 Overview l SQL data types and schemas l Integrity constraints l Authorization l Integrating SQL in programs
Background image of page 2
3DB045:3 Data Types in SQL (Date & l DATE : year (2 or 4 digits), month, day l ‘2008-10-20’ l TIME : hour, minute, second l e.g., ‘14:05:20’ ‘14:05:20.187’ l TIMESTAMP : and TIME together l e.g., ‘2008-10-20 14:05:20’ l INTERVAL :period of time. l e.g., 1 DAY l Subtraction of date/time/timestamp values gives an interval value. l Interval values can be added to date/time/timestamp Different DBMSs may support different extra types for date/time. (e.g., MySQL’s DATETIME type.)
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4DB045:4 Built-in Data Types in SQL l Extract values of individual fields from DATE/TIME/TIMESTAMP: l e.g., EXTRACT ( YEAR FROM r.startdate) l Cast string types to DATE/TIME/TIMESTAMP l e.g., CAST ‘1998-01-02’ AS DATE MySQL also support MICROSECOND field in time.
Background image of page 4
5DB045:5 User-Defined Types l CREATE TYPE creates user-defined types: l CREATE TYPE Dollars AS NUMERIC(12, 2) FINAL l CREATE DOMAIN in SQL-92 creates domain types: l CREATE DOMAIN Person_Name CHAR(20) NOT NULL l Types and domains are similar. Domains can have constraints, such as NOT NULL , specified on them.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6DB045:6 Large-Object Types l Big items (photos, videos, CAD files, etc.) are stored as large objects. A query returns a pointer to these objects, not the object itself. l Types of large objects: l BLOB :binary large object – a collection of binary data. The interpretation of these objects is left to the application which uses them. l CLOB :character large object – a collection of character data.
Background image of page 6
7DB045:7 Overview l SQL data types and schemas l Integrity constraints l Authorization l Integrating SQL in programs
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
8DB045:8 Integrity Constraints l Integrity constraints guard against accidental damage to the database, by ensuring that authorized changes to the database do not result in a loss of data consistency . l e.g., A salary of a bank employee must be at least $33 an hour l A customer must have a (non-null) phone number l An account amount should not increase by $1 billion over one night.
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 31

Chapter42 - CSIS0278A Introduction to Database Management...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online