DataBase System Creating Tables Creating tables in the Banking database branch account depositor CustoMer brachman accountnumber customername custoimermame branch citt pranIc ane accountnumber cus totier street assets balance custoner-city Loan borrower loan number customername branchiae Loan nuimber amount //Creating table customer in SQL CREATE TABLE cuStomer( customer name char(20) customer street char(30) NOT NULL customer city char(30) ); Haichang Gao, Software School, Xidian University 13
DataBase System Haichang Gao , Software School , Xidian University 13 Creating Tables //Creating table customer in SQL CREATE TABLE customer ( customer_name char(20), customer_street char(30) NOT NULL, customer_city char(30) ) ; Creating Tables in the Banking database:
DataBase System 6 Integrity Constraints in Tables e Integrity constraints Ensure that changes made to the database by authorized do noT result in a loss of data consistency H PRIMARY KEY Constrants(Entity integrity) H FROEIGE KEY Constrants(Referential integrity) H NOTI NULL Constrants E UNIQUE Constrants H DEFAULT Constrants E CHECK Constrants E Assertion Constrants e Syntax CONSTRAINT <constraint name> <constraint Haichang Gao, Software School, Xidian University 14
DataBase System Haichang Gao , Software School , Xidian University 14 Integrity Constraints in Tables Integrity constraints ENSURE that changes made to the database by authorized DO NOT result in a loss of data consistency. PRIMARY KEY Constrants (Entity integrity) FROEIGE KEY Constrants (Referential integrity) [NOT] NULL Constrants UNIQUE Constrants DEFAULT Constrants CHECK Constrants Assertion Constrants Syntax CONSTRAINT <constraint_name> <constraint>
a Integrity Constraints in Tables G Create table with constraints CREATE TABLE account( account number char(10) branch name char(30) NOT NULL balance numeric(12. 2 PRIMARY KEY(account number) fOREiGn KEY (branch name REFerENCES branch( branch name) CONSTRAINT chk balance checK(balance >=0)) A The referenced table must be an existing relation! t Integrity constraints can be added to an existing relation if the relation does not satisfies the constraint, reject H Constraint name make it easy to drop Haichang Gao, Software School, Xidian University 15
DataBase System Haichang Gao , Software School , Xidian University 15 Create table with constraints CREATE TABLE account ( account_number char(10), branch_name char(30) NOT NULL, balance numeric(12.2), PRIMARY KEY (account_number), FOREIGN KEY (branch_name) REFERENCES branch(branch_name), CONSTRAINT chk_balance CHECK (balance >= 0 ) ) ; The referenced table must be an existing relation! Integrity constraints can be added to an existing relation, if the relation does not satisfies the constraint, reject! Constraint name make it easy to drop. Integrity Constraints in Tables
DataBase System Drop and Alter Table e The drop table command deletes all information about the dropped relation from the database H Syntax: DROP TABLE <table name> E Example: DROP TABLE customer e The alter table command is used to add attributes or constraints to an existing relation A Syntax ALTER TABLE <table name> ADDDROPJALTER H Examples ALTER TABLE cuStomer ADD customer id CHAR(IO) ALTER TABLE customer DRoP customer city ALTER TABLE account alter balance numeric(10.2) Haichang Gao, Software School, Xidian University 16
DataBase System Haichang Gao , Software School , Xidian University 16 Drop and Alter Table The drop table command deletes all information about the dropped relation from the database. Syntax: DROP TABLE <table_name>; Example: DROP TABLE customer The alter table command is used to add attributes or constraints to an existing relation. Syntax: ALTER TABLE < table_name > ADD|DROP|ALTER …; Examples: ALTER TABLE customer ADD customer_id CHAR(10); ALTER TABLE customer DROP customer_city; ALTER TABLE account ALTER balance numeric(10.2);
DataBase System More about create tables t After create table statement executed. the defination will been stored in Data Dictionary as metadata. H A foreign key specification is accepted only if it references an existing table E There are more complexed integrity Constrants will be introduced later w Only after tables been created data can be entered into the table in database A Integrity Constrants are important part of table to avoid invalid data into database Haichang Gao, Software School, Xidian University 17
DataBase System Haichang Gao , Software School , Xidian University 17 More about create tables After create table statement executed, the defination will been stored in Data Dictionary as metadata. A foreign key specification is accepted only if it references an existing table. There are more complexed integrity Constrants will be introduced later. Only after tables been created, data can be entered into the table in database. Integrity Constrants are important part of table to avoid invalid data into database