Sql data definition
This presentation is the property of its rightful owner.
Sponsored Links
1 / 25

SQL Data Definition PowerPoint PPT Presentation


  • 52 Views
  • Uploaded on
  • Presentation posted in: General

SQL Data Definition. using Oracle. DDL. SQL = DDL + DML Oracle has its own dialect of SQL DDL (Data definition language) Part of SQL Some DDL statements create table … Fig. 4-4, page 77 alter table … drop table …. Naming conventions, 68.

Download Presentation

SQL Data Definition

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql data definition

SQL Data Definition

using Oracle

SQL data definition using Oracle


Sql data definition

DDL

  • SQL = DDL + DML

    • Oracle has its own dialect of SQL

  • DDL (Data definition language)

    • Part of SQL

  • Some DDL statements

    • create table …

      • Fig. 4-4, page 77

    • alter table …

    • drop table …

SQL data definition using Oracle


Naming conventions 68

Naming conventions, 68

  • Naming conventions for table names and attributes names

    • Illegal

      • spaces

      • hyphens

    • Legal

      • letters [a-z A-Z, not æøå] + digits

      • _, #, $

      • first character must be a letter

      • no reserved words in SQL [no attribute called 'by‘ or ‘table’]

    • Fig. 4-1, page 68

SQL data definition using Oracle


Data types 68

Data types, 68

  • Slightly different from standard SQL

  • Strings

  • Numbers

  • Dates

  • Other

    • no Boolean data type

SQL data definition using Oracle


Data types strings 69

Data types, strings, 69

  • varchar2 (max_length)

    • varying length, like names, etc.

    • Varchar2(10)

      • Max length 10 bytes

    • Varchar2(10 char)

      • Max length 10 chars

      • Normally 1 char ~ 1 byte, but it depends on the character table (like UNICODE)

  • char (fixed_length)

    • fixed length, like cpr, phone, etc

    • Length in bytes or chars, like varchar2

SQL data definition using Oracle


Data types numbers 70

Data types, numbers, 70

  • Numbers

    • number (digits, decimals)

      • fixed number of digits and decimals, like price, salary, etc.

    • number (digits)integer

    • numberfloating point (high precision)

SQL data definition using Oracle


Data types dates 70

Data types, dates, 70

  • date

    • store date and time!

      • no special time data type

    • Oracle SQL has special functions to do arithmetic on dates

      • more on that when we look at "select …"

    • advice

      • don't make an attribute called "age", as it changes frequently.

      • make an attribute called birthday.

SQL data definition using Oracle


Data types other 72

Data types, other, 72

  • CLOBcharacter large object

    • texts up to 4 gigabytes

  • BLOBbinary large object

    • up to 4 gigabytes, pictures, sounds, etc.

  • NCLOB

    • like CLOB, but using 2 bytes pr. character

  • BFILEbinary file

    • reference to a binary file outside the DBMS

  • Summary, fig. 4-2, page 72

SQL data definition using Oracle


Constraints types 72

Constraints, types, 72

  • Types of constraints

    • integrity constraints

      • primary key

      • foreign key

    • value constraints

      • not null

      • unique

SQL data definition using Oracle


Constraints naming 72

Constraints, naming, 72

  • A constraints must have a name to be able to

    • alter the constraints

    • delete the constraint

  • naming convention

    • tableName_attributeName_constraintType

    • student_cpr_pk

    • fig. 4-3, page 73

  • If you don’t name your constraints Oracle will name them. Find the names

    • select * from user_constraints where table_name = 'PERSON';

SQL data definition using Oracle


Constraints defining primary key 73

Constraints, defining + primary key, 73

  • 2 ways to define a constraint

    • column level, applied to one attribute

      create table student (

      cpr char (9) constraint student_cpr_pk primary key,

      )

    • table level, can be applied to more attributes

      create table student_course (

      cpr char(9) not null,

      course_number number(3) not null,

      constraint student_cpr_pk primary key (cpr, course_number)

      )

SQL data definition using Oracle


Constraints foreign key 74

Constraints, foreign key, 74

  • Attributes in one table refers to attributes in [another] table.

    • DBMS checks if the referred attributes exist.

      • referential integrity

    • Example

      create table student_course (

      cpr char(9) not null,

      course_number number(3) not null constraint student_course_number_fk references course (course_number),

      constraint student_cpr_pk primary key (cpr, course_number),

      constraint student_cpr_fk foreign key (cpr) references student (cpr)

      );

SQL data definition using Oracle


Referential triggered actions not in the book

Referential triggered actions, (not in the book)

  • foreign key … on delete cascade

    • if the referred row is deleted the referring row should be deleted as well

      create table employee (

      cpr char(9) primary key,

      boss_cpr char(9),

      foreign key (boss_cpr) references employee (cpr) on delete cascade)

      )

      • If we delete the top boss the whole organization vanishes!!

    • think carefully before writing … on delete cascade

      • useful in weak entity sets

    • no … on update cascade in Oracle!!

      • not necessary if we use surrogate keys (they never change)

SQL data definition using Oracle


Constraints not null 75

Constraints, not null, 75

  • The attribute must no be NULL.

    • syntaxes

      • name varchar2(30) not null no name constraint

      • name varchar2(30) constraint student_name_nnnot null

      • can not be specified at table level [only at attribute level]

    • primary key attributes must be NOT NULL

    • NULL means

      • does not exist

      • is not known at the moment

    • Don't make your own NULL's using 0 or ‘ ‘

    • Don’t write NOT NULL to often.

      • Think before you write!

SQL data definition using Oracle


Constraints unique 75

Constraints, unique, 75

  • The value of an attribute must be unique

    • used when a table has more candidate keys

      • one of the candidates is appointed primary key

      • the rest of the candidates are [only] unique

    • syntaxes

      • attribute level

        • departmentName varchar(12) unique

      • table level

        • constraint student_name_uk unique (firstname, lastname)unlikely constraint!!

SQL data definition using Oracle


Constraint check 75

Constraint, check, 75

  • Checking business rules

  • General constraint that every row must satisfy

    • attribute level

      • salary number(5) check ( salary >= 0 )

      • gender char(1) check (gender in ('M', 'F'))

    • table level

      • check (gender ='M' or salary < '1000')

SQL data definition using Oracle


Default value domains 76

Default value + domains, 76

  • No a constraint, but has similar syntax.

  • Used when no value is supplied

    • country char(2) default 'DK'

    • salary number(5) default 0

  • Oracle has no domain concept

    • No create domain …

    • But there is a create type …

SQL data definition using Oracle


Creating a table 76

Creating a table, 76

  • General syntax in Oracle

    • Page 76

  • Examples

    • Fig. 4-4, page 77

  • Tables are usually created by a DBA (Database administrator)

  • Tables are usually not created from an application (Java program)

SQL data definition using Oracle


Displaying table information

Displaying table information

  • Forgot the name of your tables?

    • Select table_name from user_tables

    • User_tables has information in all your tables

  • Forgot the name + type of attributes?

    • Describe Person

SQL data definition using Oracle


Using an sql editor

Using an SQL editor

  • Use an moderns SQL editor

  • Work with your SQL statements until they are correct.

  • Copy / past the SQL statement to a text file - and save it.

    • It's handy to have the SQL statements for documentation + maintenance.

SQL data definition using Oracle


Alter table 82

Alter table, 82

  • Altering an existing table

    • add / delete attributes

    • add / delete constraints

    • most alternations are restricted [hard to do] so design your tables carefully before creating them!!

    • syntax

      • alter table tableNametheAlternation

      • alter table student add mobilPhone char(8)

SQL data definition using Oracle


Chicken and egg problem

Chicken and egg problem

  • From http://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html

  • Problem

    • If table A refers to table B and vice versa which table should be created first?

  • Solution

    • create table A without foreign key to B

    • create table B with foreign key to A

    • alter table A adding a foreign key to B

SQL data definition using Oracle


Chicken and egg example

Chicken and egg, example

create table chicken (

cID number(3) primary key,

eID number(3)

)

-------------------------

create table egg (

eID number (3) primary key,

cID number (3),

foreign key (cID) refers to chicken (cID) initially deferred deferrable

)

------------------------

alter table chicken

add constraint chicken_eID_fk foreign key (eID) refers to egg (eID)

initially deferred deferrable

SQL data definition using Oracle


Chicken and egg insertion

Chicken and egg, insertion

  • How to insert rows into the tables?

  • Problem

    • an egg row refers to a chicken row, and vice versa

  • Solution (Oracle)

    • create the foreign keys with "initially deferred deferrable"

      • defers constraint checking until transaction commit

      • insert into chicken (1,2)

      • insert into egg (2,1)

      • commit

SQL data definition using Oracle


Drop tables etc 89

Drop tables, etc. 89

  • truncate table tableName

    • remove all data from the table, but the [not empty] table still exists.

    • use carefully!!

  • drop table

    • deletes the table including data

    • use carefully!!

    • Referential integrity

      • You are not allowed to drop a table if another table refer to it

  • rename oldTableName to newTableName

SQL data definition using Oracle


  • Login