Chapter42 - CSIS0278A Introduction to Database Management...

Info icon This 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
Image of page 1

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

View Full Document Right Arrow Icon
2DB045:2 Overview l SQL data types and schemas l Integrity constraints l Authorization l Integrating SQL in programs
Image of page 2
3DB045:3 Data Types in SQL (Date & Time) 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 : DATE 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.)
Image of page 3

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

View Full Document Right 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.
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.
Image of page 5

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

View Full Document Right 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.
Image of page 6
7DB045:7 Overview l SQL data types and schemas l Integrity constraints l Authorization l Integrating SQL in programs
Image of page 7

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

View Full Document Right 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.
Image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern