CHAPTER 1 Understanding Core Database Concepts
1-1 database columnrow record field 不 DBMS Access Paradox SQL Server Linux MySQL Oracle IBM Informix IBM DB2 Sybase 1-2
1 Understanding Core Database Concepts Hierarchical Data Model Pointer Network Data Model Relational Data Model 2 1-3
1-2 Hierarchical Database Root Subtree Network Database Relational Database 1. Tabilar Relation Record 2. Entity Integrity Referential Integrity 3. Closure 1-4
1 Understanding Core Database Concepts Object-Oriented Database 1. 2. 3. 4. Method 5. class entity 6. function method 7. message passing message method 1. 2. 3. lock 4. 5. 1:1 1:m m:n 6. 1-5
( )1. SQL (A) DAL (B) DCL (C) DDL (D) DML ( )2. SELECT SQL (A) DAL (B) DCL (C) DDL (D) DML ( )3. (A) (B) (C) (D) ( )4. : (A) (B) (C) (D) ( )5. (A) (B) (C) (D) 1-14
CHAPTER 2 Creating Database Objects views
2-2 CREATE DATABASE DB1 DB1 CREATE TABLE dbo.products (PID int PRIMARY KEY NOT NULL, PName varchar(20) NOT NULL, Price money NULL, PDesc text NULL) Products dbo.products table_name 128 2-6
( ) 2 Creating Database Objects PID PName Price Product ID Product Name PDesc Product Description column_name 128 999 1 PRIMARY KEY UNIQUE CREATE TABLE Product (ProductID INTEGER, ProductItemID INTEGER, PRIMARY KEY (ProductID, ProductItemID)) Product ProductID ProductItemID ProductID ProductItemID 2-7
CREATE TABLE CREATE TABLE CREATE TABLE [database_name.[schema_name]. schema_name.]table_name ({<column_definition> <computed_column_definition> <column_set_definition>} [<table_constraint>][,...n]) [ON{partition_scheme_name(partition_column_name) filegroup "default"}] [{TEXTIMAGE_ON{filegroup "default"}] [FILESTREAM_ON{partition_scheme_name filegroup "default"}] [WITH(<table_option>[,...n])] [;] <column_definition>::= column_name <data_type> [FILESTREAM] [COLLATE collation_name] [NULL NOT NULL] [ [CONSTRAINT constraint_name]default constant_expression] [IDENTITY[(seed, increment)][not FOR REPLICATION] ] [ROWGUIDCOL][<column_constraint>[...n]] [SPARSE] <data type>::= [type_schema_name.]type_name [(precision[,scale] max [{CONTENT DOCUMENT}]xml_schema_collection)] <column_constraint>::= [CONSTRAINT constraint_name] 2-8
{ {PRIMARY KEY UNIQUE} [CLUSTERED NONCLUSTERED] [ WITH FILLFACTOR=fillfactor WITH(<index_option>[,...n]) ] 2 Creating Database Objects [ON{partition_scheme_name(partition_column_name) filegroup "default"}] [FOREIGN KEY] REFERENCES[schema_name.]referenced_table_name[(ref_ column)] [ON DELETE{NO ACTION CASCADE SET NULL SET DEFAULT}] [ON UPDATE{NO ACTION CASCADE SET NULL SET DEFAULT}] [NOT FOR REPLICATION] CHECK[NOT FOR REPLICATION](logical_expression) } ƼƼ CONSTRAINT NULL NOT NULL PRIMARY KEY UNIQUE PRIMARY KEY NOT NULL UNIQUE FOREIGN KEY CHECK NULL NULL NOT NULL PERSISTED NOT NULL PRIMARY KEY UNIQUE 2-9
CASCADE [ASC DESC] ASC CREATE TABLE CREATE TABLE Constraint 1. 2. 3. 4. Management Studio 2-10
( )15. ( ) (A) (B) (C) (D) ( )16. (A) (B) (C) (D) T-SQL ( )17. (A)INSERT (C)DROP (B)UPDATE (D)DELETE ( )18. tri_name (A)DROP TRIGGER tri_name (B)DROP TRIGGER 'tri_name' (C)DROP tri_name TRIGGER (D)DROP('tri_name') TRIGGER ( )19. (A) (B) (C) (D) ( )20. (A) (B) (C) (D) 2-26