4-Lecture 4数据库课件

4.1 Data Types in SQL Server
System data types: date
Data Type datetime
Description Represent date and time. Accuracy: 3.33 milliseconds
Table Structure of Course Management System
Description s=2 Integer, -231 to 231-1 63-1 Integer, -263 to p 2=8 Integer, -215 to 215-1 Fixed precision (p) and scale (s, default to 0) number Floating point number.
Student
Attribute ID Name Gender Birthday DeptID Data Type char varchar char datetime char Length 8 20 1 3 Nullability No No Yes Yes Yes
Table Structure of Course Management System
4.0 Example Application
E-R diagram
Relational Schemas
Department (ID, Name, Head) Teacher (ID, Name, Position, DeptID) Student (ID, Name, Gender, Birthday, DeptID) Course (ID, Name, Room, TeacherID) Enroll (StudentID, CourseID, Grade)
2. There may not be any duplicate entries in primary key column
Student
ID Name Birthday Gender DepartmentID
100001
100002 100003
Alice
Bob Carol
1990-1-1
1991-2-20 1990-5-1
4.1 Data Types in SQL Server
System data types: Numerics
Data Type int bigint smallint decimal(p, s) float
Storage 4 bytes 8 bytes 2 bytes Dep on p 8 bytes
Adding an Index in EM
4.3 Constraints on Relations
Constraints on data manipulation
Insert, delete, and modify
Objective
Ensure the correctness of data with entity, relationship and business rules
CREATE TABLE statement Column definition
Column name Data type
Column constraint
Primary key NULL/NOT NULL Foreign key Check
Creating Tables: Department
Chapter 4. Creating and Modifying Tables and Indexes
Outline
4.1 Data types in SQL Server 4.2 Creating/modifying tables and indexes with EM 4.3 Constraints on Relations 4.4 Creating/modifying tables and indexes with T-SQL
Used to maintain the consistency among tuples in the two relations
(2) Referential Integrity
Department
ID 101 102 DepartmentName Computer Science Management & Economics Abbr CS ME Head Boss 1 Boss 2
Teacher
Attribute ID Name Position DeptID Data Type char varchar varchar char Length 8 20 30 3 Nullability No No Yes Yes
Table Structure of Course Management System
102 null
A new student that has not been assigned a department
(3) User Defined Integrity
Represent business rules or constraints Defined by users Examples
Table Structure of Course Management System
Department
Attribute ID Name Head Data Type char varchar varchar Length 3 20 20 Nullability No No Yes
Table Structure of Course Management System
Enroll
Attribute StudentID CourseID Grade Data Type Length char 8 char 4 int Nullability No No Yes
4.2 Creating/modifying tables and indexes with MS
Adding Foreign Keys
A data structure Objective: improve the speed of data retrieval operations Cost: slower writes and increased storage space
A primary key is set as an index
Three Types of Integrity Constraints
Entity integrity Referential integrity User defined integrity
(1) Entity Integrity
1. No primary key value can be “null”
A grade for a student in a course should be between 0 and 100
Enroll
1001
1001 1002
StudentID CourseID
Grade
100001
100002 100001
80
90 100
4.4 Creating/modifying tables and indexes with T-SQL
Modifying Table Structure
ALTER TABLE Teacher ADD Birthday datetime; ALTER TABLE Teacher DROP COLUMN Birthday; ALTER TABLE Teacher ALTER COLUMN Name varchar (50);
123456.78
4.1 Data Types in SQL Server
System data types: Character Strings
Data Type char(n) varchar(n) text
Val of n Description 1~8000 Fixed-length character string of n bytes 1~8000 Variable-length character string of n bytes Variable-length large character string
Female
Male Male
101
102 101
(2) Referential Integrity
A tuple in one relation that refers to another relation must:
Refer to an existing tuple in that relation Or, have the foreign key be “null” value
Primary key value is used to identify individual tuples in a relation Having “null” value for the primary key implies that we cannot identis: Enroll
CREATE TABLE Enroll ( StudentID char (8) NOT NULL REFERENCES Student (ID), CourseID char (4) NOT NULL REFERENCES Course (ID), Grade int NULL CHECK (Grade IS NULL OR (Grade>=0 AND Grade<=100)), PRIMARY KEY (StudentID, CourseID) );