Data Definition and Integrity Constraints 数据的定义和完整性限制

• IEFs allow the DB designer to specify & enforce: — domain constraints — required data — entity integrity — referential integrity — enterprise constraints (business rules)

Creating Tables – Data Definition • CREATE TABLE is used to define relational tables • It defines the data type for each column • Defines rules for how data may be inserted and deleted创建表格用于定义关系表和每列数据类型

Built-in Data Types (Domains) in ANSI SQL • ANSI SQL supports many data types (vendors often also have own dialects): — CHARACTER ( CHAR ), CHARACTER VARYING ( VARCHAR ) — NUMERIC , DECIMAL ( DEC ), INTEGER ( INT ), SMALLINT — FLOAT , REAL , DOUBLE PRECISION — DATE , TIME , TIMESTAMP — BOOLEAN , BIT — BINARY LARGE OBJECT , etc. • Some types have an associated size, e.g., CHAR(5)

用户在ANSI SQL自定义域

可以对数据更多限制条件

添加判断和子句

Dynamic Domain Constraints • Domains may be defined ‘dynamically’ using values that already exist in the database:通过数据库变量动态定义域

• Domains can be deleted:域可以被删除

有关标量函数Scalar Functions

Entity Integrity - Primary Keys • Reminder: the primary key of each row in a table must be unique and non-null • Example: The primary key of the Viewing table is composed of two attributes (composite key):实体的完整性 表的每一行主键必须唯一和非空• SQL will reject operations that would violate primary key uniqueness • Can use UNIQUE(Colname) to enforce uniqueness of alternate keys一一使用UNIQUE（ColName）强制备用键唯一性

Referential Integrity – Foreign Keys • Reminders: • A foreign key links a child table to its parent table • If a foreign key is non-null, it must match an existing row in the parent table • So... SQL has more keywords for this:关系完整性 外键将父子表连接 外键如果非空就必须域父表中的现有记录匹配

Referential Integrity and Referential Actions • Question: if a key attribute in the parent table is modified, what should happen in the child table? – SQL provides 4 alternative referential actions:SQL有四种操作 对父表中的键属性被修改 子表CASCADE将更改应用到子行 SET NULL将子键设为NULL SET DEFAULT将子键设为DEFAULT值 NO ACTION拒绝删除或者更新父表

• Suppose a client is removed from the DreamHome DBMS. What’s the most appropriate action to specify for ClientNo in the Viewing table? CASCADE？

Enterprise Constraints (Business Rules) • Sometimes, real–world business rules involve constraints that refer to more than one table. Its useful to define enterprise constraints just once • Example: A member of staff may manage no more than 100 properties:业务规则设计多个表的约束 只定义一次企业约束

Triggers • Often, real-world business rules cannot be implemented using constraints • Example: The branch manager is notified by e-mail if a client views more than 10 properties • Different DBMSs often provide a trigger mechanism • Triggers may contain procedural code (if/then/else, function calls) • Triggers can implement complex database operations • However, triggers can add to database complexity (hidden rules) • Triggers are not ANSI standard – should they be?触发器 现实世界的业务规则通常不能用约束实现 不同的DBMS通常会提供触发器（if/then/else和函数调用）触发器可以实现复杂数据库操作不过会增加复杂性

Putting It All Together – Schemas • A schema is a collection of named DBMS objects: • Tables, Domains, Constraints, Views (later), Triggers, and more ... • A multi-user DMBS may contain multiple schemas • Each schema is owned by a given user • A Database Administrator (DBA) manages schemas (CREATE, DROP) • Schemas are maintained in special system tables • However, different DBMSs have different ways of managing schemas ...模式是命名DBMS对象的集合 表，域，玉树，试图，触发器 一个多用户DBMS可能多个模式 每个模式由一个指定用户拥有 DBA管理模式 模式存在特殊系统表里 不同的DBMS有不同的模式管理方法

Database Schemas Evolve Over Time • Ideally, a database is created once and then used for many years ... BUT • The data model may be improved (integrity, performance) ... • New features may be added in new releases ... • Enterprise rules may change ... • Therefore, SQL provides many options for changing tables:数据库一般只创建一次 但是会根据完整性和性能改进数据模型 可能会添加新功能改变企业规则 所以SQL有很多更改表的选项

Summary So Far... • DBs are ‘active’ or ‘alive’ – contents always changing • The structure of a DB can also evolve over time... • DB contents should always be consistent – integrity • ANSI SQL provides several Integrity Enhancement Features (IEFs) • IEF → domain constraints, entity/referential integrity, business rules... • IEFs imply additional design choices for new DBs • One DBMS can manage multiple DBs – notion of schemas & privileges数据库随时间变化时应保持一致性和完整性 ANSI SQL提供多个IEF——域约束，实体/参照完整性，业务规则 IEF使新数据库DB有额外设计选择 一个DBMS可以管理多个DB