sql data definition
Download
Skip this Video
Download Presentation
SQL Data Definition

Loading in 2 Seconds...

play fullscreen
1 / 25

SQL Data Definition - PowerPoint PPT Presentation


  • 98 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' SQL Data Definition' - tobias


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

slide2
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
    • number floating 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
  • CLOB character large object
    • texts up to 4 gigabytes
  • BLOB binary large object
    • up to 4 gigabytes, pictures, sounds, etc.
  • NCLOB
    • like CLOB, but using 2 bytes pr. character
  • BFILE binary 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

ad