DataBase System Ks Domain Types in SQL H char(n): Fixed length character string, with user-specified length n E varchar(n): Variable length character strings, with user specified maximum length n E int: Integer(a finite subset of the integers that is machine dependent) E smallint: Small integer(a machine-dependent subset of the integer domain type) E numeric(p, d): Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point Haichang Gao, Software School, Xidian University 8
DataBase System Haichang Gao , Software School , Xidian University 8 Domain Types in SQL char(n): Fixed length character string, with user-specified length n. varchar(n): Variable length character strings, with userspecified maximum length n. int: Integer (a finite subset of the integers that is machinedependent). smallint: Small integer (a machine-dependent subset of the integer domain type). numeric(p,d): Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point
DataBase System Ks Domain Types in SQL E real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision E float(n): Floating point number, with user-specified precision of at least n digits E date: Dates, containing a(4 digit)year, month and date Example: date 2005-7-27 H time: Time of day, in hours, minutes and seconds Example: time 09: 00: 30 time 09: 00: 30.75 E timestamp: date plus time of day Example: timestamp 2005-7-27 09: 00: 30.75 Haichang Gao, Software School, Xidian University
DataBase System Haichang Gao , Software School , Xidian University 9 Domain Types in SQL real, double precision: Floating point and double-precision floating point numbers, with machine-dependent precision. float(n): Floating point number, with user-specified precision of at least n digits. date: Dates, containing a (4 digit) year, month and date ➢ Example: date ‘2005-7-27’ time: Time of day, in hours, minutes and seconds. ➢ Example: time ‘09:00:30’ time ‘09:00:30.75’ timestamp: date plus time of day ➢ Example: timestamp ‘2005-7-27 09:00:30.75’
DataBase System Ks Domain Types in SQL E blob: binary large object - object is a large collection of uninterpreted binary data( whose interpretation is left to an application outside of the database system) H clob: character large object --object is a large collection of character data A USer -defined domain >Example: create domain money numeric(12, 2) Haichang Gao, Software School, Xidian University 10
DataBase System Haichang Gao , Software School , Xidian University 10 Domain Types in SQL blob: binary large object -- object is a large collection of uninterpreted binary data (whose interpretation is left to an application outside of the database system). clob: character large object -- object is a large collection of character data. User-defined domain: ➢ Example: create domain money numeric(12, 2)
DataBase System Creating DataBase Creating the Banking data base: t Database schema: branch(branch name, branch city, assets customer(customer name, customer street, customer city depositor (customer name, account number account account number, branch name, balance borrower(customer name, loan number) loan(loan number, branch name, amount) H Creating database Banking steps: D)CREATE DATABASE Banking .(Syntax lie on DBMS) 2) Creating referenced tables(被参照关系) 3)Creating referencing tables 4)Creating other object of database Haichang Gao, Software School, Xidian University 11
DataBase System Haichang Gao , Software School , Xidian University 11 Creating DataBase Creating the Banking database: Database Schema: branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) depositor (customer_name, account_number) account (account_number, branch_name, balance) borrower (customer_name, loan_number) loan (loan_number, branch_name, amount) Creating database Banking steps: 1) CREATE DATABASE Banking … (syntax lie on DBMS) 2) Creating referenced tables(被参照关系) 3) Creating referencing tables 4) Creating other object of database
DataBase System Creating Tables Syntax An sql relation is defined using the create table command CREATE TaBLE <table-name>( [<column-name l data type>k<column constraint>] i<table constraint>] Haichang Gao, Software School, Xidian University 12
DataBase System Haichang Gao , Software School , Xidian University 12 Creating Tables An SQL relation is defined using the create table command: CREATE TABLE <table-name> ( [ [ <column-name1 data_type> [<column_constraint>] , [ ...n ] ] [ <table_constraint> ], ] ); Syntax